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

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.

The documentation uses literal_binds to print a query q including parameters:

print(q.statement.compile(compile_kwargs={"literal_binds": True}))

the above approach has the caveats that it is only supported for basic types, such as ints and strings, and furthermore if a bindparam() without a pre-set value is used directly, it won’t be able to stringify that either.

The documentation also issues this warning:

Never use this technique with string content received from untrusted input, such as from web forms or other user-input applications. SQLAlchemy’s facilities to coerce Python values into direct SQL string values are not secure against untrusted input and do not validate the type of data being passed. Always use bound parameters when programmatically invoking non-DDL SQL statements against a relational database.

This should work with Sqlalchemy >= 0.6

from sqlalchemy.sql import compilerfrom psycopg2.extensions import adapt as sqlescape# or use the appropiate escape function from your db driverdef compile_query(query):    dialect = query.session.bind.dialect    statement = query.statement    comp = compiler.SQLCompiler(dialect, statement)    comp.compile()    enc = dialect.encoding    params = {}    for k,v in comp.params.iteritems():        if isinstance(v, unicode):            v = v.encode(enc)        params[k] = sqlescape(v)    return (comp.string.encode(enc) % params).decode(enc)