Access to the PostgreSQL's NOTIFY/ LISTEN feature?

[Dimitris Karteris]

I will be using SQLAlchemy on top of PostgreSQL and I plan to take
advantage of this NOTIFY / LISTEN mechanism offered by PostgreSQL. In
the psycopg2 distro, I came across an example snippet that shows how
to obtain notifications (http://www.initd.org/tracker/psycopg/browser/
psycopg2/tags/2_0_5/examples/notify.py), but it seems that I need a
reference to a psycopg.cursor object. Is there anyway to get that from
sqlalchemy?

Thanks in advance,

Dimitris

[Michael Bayer]

with a regular SA engine-level Connection, you can get at the psycopg2
connection via conn.connection. the connection and cursor returned by
cursor() are proxied for garbage collection/connection pool purposes
but call upon the psycopg2 API for their behavior and methods.

you also want to make sure you revert any isolation level settings on
the connection back to normal before returning it to the pool.

or, as an alternative to that, you can call the detach() method on
either Connection or the proxied connection, which will remove it from
the pool; it gets closed for real when you close() it and whatever
changes you've made on it don't pollute the overall pool of
connections.

[Michael Bayer]

added FAQ entry:

http://www.sqlalchemy.org/trac/wiki/FAQ#HowdoIgetattherawDBAPIconnectionwhenusinganEngine