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!

Solution 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.

Solution 2

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.

Solution 3

This should work with Sqlalchemy >= 0.6

from sqlalchemy.sql import compiler

from psycopg2.extensions import adapt as sqlescape
# or use the appropiate escape function from your db driver

def 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)

Solution 4

Thing is, sqlalchemy never mixes the data with your query. The query and the data are passed separately to your underlying database driver - the interpolation of data happens in your database.

Sqlalchemy passes the query as you've seen in str(myquery) to the database, and the values will go in a separate tuple.

You could use some approach where you interpolate the data with the query yourself (as albertov suggested below), but that's not the same thing that sqlalchemy is executing.

Solution 5

For the MySQLdb backend I modified albertov's awesome answer (thanks so much!) a bit. I'm sure they could be merged to check if comp.positional was True but that's slightly beyond the scope of this question.

def compile_query(query):
    from sqlalchemy.sql import compiler
    from MySQLdb.converters import conversions, escape

    dialect = query.session.bind.dialect
    statement = query.statement
    comp = compiler.SQLCompiler(dialect, statement)
    comp.compile()
    enc = dialect.encoding
    params = []
    for k in comp.positiontup:
        v = comp.params[k]
        if isinstance(v, unicode):
            v = v.encode(enc)
        params.append( escape(v, conversions) )
    return (comp.string.encode(enc) % tuple(params)).decode(enc)

View more solutions

Comments

[cce]

Thanks for this! Sadly I'm using MySQL so my dialect is "positional" and needs to have a params list rather than a dictionary. Currently trying to get your example to work with that..

[cce]

why isn't it the same thing? I understand the DB-API is doing transactions, possibly re-ordering queries, etc, but could it be modifying my query more than this?

[nosklo]

@cce: you're trying to find the final query. SELECT id WHERE date_added <= %s AND date_added >= %s ORDER BY count DESC IS the final query. Those %s are sent to the database by sqlalchemy -- sqlalchemy NEVER puts the actual data in place of the %s

[nosklo]

@cce: Some dbapi modules don't do that either - that is often done by the database itself

[Ed_WFerreira]

Please don't use adapt in this manner. At a minimum call prepare() on the return value from it each time, providing the connection as an argument, so it can do proper quoting.

[albertov]

@Alex: What would be the correct way to do proper quoting with psycopg? (besides calling prepare() on the return value, which you seem to imply is not optimal)

[cce]

aha I see what you're saying, thanks — digging further in sqlalchemy.dialects.mysql.mysqldb, do_executemany() passes the statement & parameters separately to the MySQLdb cursor. yay indirection!

[Ed_WFerreira]

Sorry I think my phrasing was bad, as long as you do call obj.prepare(connection) you should be ok. This is because "good" APIs that libpq provides for quoting require the connection (and it provides things like encoding for unicode strings).

[albertov]

Thanks. I've tried calling prepare on the return value but is seems that it doesn't have that method: AttributeError: 'psycopg2._psycopg.AsIs' object has no attribute 'prepare'. I'm using psycopg2 2.2.1 BTW

[albertov]

Ok, it seems prepare() isn't available in my old psycopg2 version, 2.3.1 seems to have it: initd.org/psycopg/docs/…

[Damien]

Is there an easy way to get the values as a dictionary ?

[tourdownunder]

The port of this function to python 3 is stackoverflow.com/a/28227450/567606

[TankorSmash]

@cce did you figure out the MySQL bit?

[cce]

HI @TankorSmash I did and shared it in a different answer below stackoverflow.com/a/4618647/112380

[horcle_buzz]

Awesome! I just needed the bound parameter list being sent to MySQL and modifying the above to just return tuple(params) worked like a charm! You saved me countless hours of having to go down an extremely painful road.

[Hannele]

@Damien given c = q.statement.compile(...), you can just get c.params

[Piotr Dobrogost]

How is this answer better/different than AndyBarr's one posted 2 years earlier?

[eric]

AndyBarr's answer includes an example of generating a query statement with a DBSession whereas this answer includes an example​ using the declarative API and select method. With respect to compiling the query statement with a certain dialect, the answers are the same. I use SQLAlchemy to generate raw queries and then execute them with Twister's adbapi. For this use case, knowing how to compile the query without a session and to extract the query string and parameters is useful.

[Hannele]

The post is tagged with mysql, so the postgresql details in this answer aren't really relevant.

[Hannele]

Statement doesn't show you what the parameters are, if you have some kinds of filters set up.

[Patrick B.]

If I understand the OP correctly, he wants the final query. Printing with specifying a dialect (here postgres) still gives me the placeholders instead of the literal values. @Matt's answer does the job. Getting the SQL with placeholders can be simplier achieved with the as_scalar()-method of Query.

[Ben]

From within the PyCharm debugger, the following worked for me... qry.compile().params

[Hannele]

Interesting, could be SQLAlchemy has changed a bit since I wrote this answer.

[André C. Andersen]

@PatrickB. I agree. Matt's answer should be considered the "correct" answer. I get the same result as this by just doing str(q).

[Justin Palmer]

Thank you! This was extremely helpful, allowed me to use pandas read_sql function painlessly!

[nirvana-msu]

This is the best answer for Postgres! Unlike the method that uses literal_binds, this works with any parameter types.

[cikatomo]

how do we do it for Core expression?

[cikatomo]

@JustinPalmer does pandas not accept the query? Does it accept Core expression?

[cikatomo]

how can we do it for Core expression?

[cikatomo]

@eric and how come you are not using just raw SQL?

[Ham]

Unfortunately , the literal_binds approach is not able to bind python bytes data type with compiled SQL statement. In such case you may need to convert bytes to hex string in advance then figure out how the hex string can be passed with some built-in functions supported in your database

[Deepam Gupta]

doing q.statement.compile(compile_kwargs={"literal_binds": True}).string will return the string without calling str()