How do I get a raw, compiled SQL query from a SQLAlchemy expression?

Question

I have a SQLAlchemy query object and want to get the text of the compiled SQL statement, with all its parameters bound (e.g. no %s or other variables waiting to be bound by the statement compiler or MySQLdb dialect engine, etc).

Calling str() on the query reveals something like this:

SELECT id WHERE date_added <= %s AND date_added >= %s ORDER BY count DESC 

I've tried looking in query._params but it's an empty dict. I wrote my own compiler using this example of the sqlalchemy.ext.compiler.compiles decorator but even the statement there still has %s where I want data.

I can't quite figure out when my parameters get mixed in to create the query; when examining the query object they're always an empty dictionary (though the query executes fine and the engine prints it out when you turn echo logging on).

I'm starting to get the message that SQLAlchemy doesn't want me to know the underlying query, as it breaks the general nature of the expression API's interface all the different DB-APIs. I don't mind if the query gets executed before I found out what it was; I just want to know!

Answer 1

This blog provides an updated answer.

Quoting from the blog post, this is suggested and worked for me.

>>> from sqlalchemy.dialects import postgresql >>> print str(q.statement.compile(dialect=postgresql.dialect())) 

Where q is defined as:

>>> q = DBSession.query(model.Name).distinct(model.Name.value) \              .order_by(model.Name.value) 

Or just any kind of session.query().

Thanks to Nicolas Cadou for the answer! I hope it helps others who come searching here.