How to retrieve executed SQL code from SQLAlchemy

Question

I am using SQLAlchemy and would like to log executed SQL code (i.e. the code with all bind parameters already quoted and replaced). In case of psycopg2 it was possible using the query attribute of the Cursor object (see psycopg documentation). In case of MySQLdb it is possible using the _last_executed attribute of the Cursor object.

My question is: How can I retrieve the just executed query string using SQLAlchemy interfaces? Does it provide such functionality or should I write my own helper function?

Thanks in advance for any help.

Answer 1

SQLAlchemy uses the standard Python logging library. To log queries to a file named db.log:

import logging

logging.basicConfig(filename='db.log')
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

When using Python logging, ensure all echo flags are set to False, to avoid duplicate logging. Now add something to the db:

>>> Movie(title=u"Blade Runner", year=1982)
>>> session.commit()

Which will log something like:

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO models_movie (title, year, description) VALUES (%(title)s, %(year)s, %(description)s) RETURNING models_movie.id
INFO:sqlalchemy.engine.base.Engine:{'title': u'Blade Runner', 'description': None, 'year': 1982}
INFO:sqlalchemy.engine.base.Engine:COMMIT