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.
Sergey :
You can register a Pyramid event handler which would set the parameter on any new request:\n\nfrom pyramid import events\n\ndef on_new_request(event):\n \"\"\"\n you can access request as event.request \n and the current registry settings as event.request.registry.settings\n \"\"\"\n session = DBSession()\n session.execute(\"SET blah TO 'foo'\")\n\n\ndef app(global_config, **settings):\n\n config = Configurator(...)\n config.add_subscriber(on_new_request, events.NewRequest)\n\n\nSee Deployment Settings and events.NewRequest for more details.\n\nOne 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. ",
2014-10-08T01:03:44