10

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.

Dariusz Walczak
  • 4,848
  • 5
  • 36
  • 39

1 Answers1

25

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
Zach Kelling
  • 52,505
  • 13
  • 109
  • 108
  • 1
    True, but (a) this may also send messages to stdout, (b) you can set a similar effect by just setting a specific engine's `echo` attribute to True, and (c) SQLA's logging doesn't show the query with placeholders replaced, though it does log the paramterized SQL and the parameters separately. – Vinay Sajip Jun 15 '11 at 11:43
  • 1
    @Vinay Those are good points, I've updated my answer to illustrate how to use python logging to log specifically to a file, and show an example of logging output – Zach Kelling Jun 15 '11 at 14:04
  • 1
    Important note from the documentation: *"It only checks this level when a new connection is procured from the connection pool. Therefore when changing the logging configuration for an already-running application, any Connection that’s currently active, or more commonly a Session object that’s active in a transaction, won’t log any SQL according to the new configuration until a new Connection is procured (in the case of Session, this is after the current transaction ends and a new one begins)."* – Dr. Jan-Philip Gehrcke Jun 08 '15 at 13:27