1

I'm using Pyramid and SQLAlchemy for my REST server. For the logging purposes I need some way how to determine user name in postgresql after update trigger function. One way is setting some runtime configuration parameter before update execution and using this value in trigger function.

In psql terminal it can be done using 

set myapp.user_name = 'User Name';

This parameter will be subsequently used in postgresql trigger.

Is it possible to set this parameter in Pyramid / SQLAlchemy application? I suppose I can use SQLAlchemy Events. But I'm not sure which event is correct for this case.

Improve this question

2 Answers 2

Reset to default
1

You can register a Pyramid event handler which would set the parameter on any new request:

from pyramid import events

def on_new_request(event):
    """
    you can access request as event.request 
    and the current registry settings as event.request.registry.settings
    """
    session = DBSession()
    session.execute("SET blah TO 'foo'")


def app(global_config, **settings):

    config = Configurator(...)
    config.add_subscriber(on_new_request, events.NewRequest)

See Deployment Settings and events.NewRequest for more details.

One thing to watch out is to make sure you're always using the same session object - SQLAlchemy maintains a pool of connections and, if you commit your session, all subsequent operations will use a brand-new session which is not pre-configured with your settings. In other words, you should not do session.commit(), session.rollback(), transaction.commit() etc. in your code and instead rely on ZopeTransactionExtension committing/rolling back the transaction at the end of the request/response cycle. Which is a recommended practice anyway.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is exactly what I need. Thanks. I'm using ZopeTransactionExtension. No commit(), in some cases only I call session.flush().
Yes, Sergey is correct this pattern will work well. I prefer to use the subscriber syntax personally: @subscriber(NewResponse) docs.pylonsproject.org/projects/pyramid/en/latest/narr/…
1

I'm not sure what the exact command you're running with set myapp.user = "User Name".

When you say "In psql this can be done using..." I'm assuming you are running some sort of SQL command, although the command you listed is not a valid command, at least not typically.

In any event, arbitrary SQL can be executed in SQLAlchemy using the .execute() method.

session = Session()
session.execute("select 1;")

http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=execute#sqlalchemy.orm.session.Session.execute

Improve this answer

6 Comments

And you run that from the PSQL prompt? => set myapp.user_name = 'User Name'; ? Is that what you mean?
Yes. I need some way how to determine user name in postgresql trigger function. One way is setting some configuration parameter before update statement and using this value in after update trigger function.
If you just need to execute that command my answer will do that, I'm not sure what exactly you're trying to do... Can you clarify if it's something more than running that one command?
Yes, I just need execute that command. I'm finding the right place to do it in my Pyramid application. Maybe it can be done in pyramid login view method after successfully login.
do you need it once per user session or on every request? Every request I'd recommend adding a tween to automatically handle it for you
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.