When handcrafting a query based on SQLAlchemy I find it quite often very useful to inspect the resulting query, including the parameters being expanded. Here are two options which I found the most useful so far:

• Switch on detailed logging in your database server. I have this on for my local PostgreSQL instance. It also helps when trouble has to be tracked down.
• Compile a query with parameter expansion and print the result. I like to use this from inside of a small test case.

This post is about the second option, printing the compiled query on the Python side.

How it works

There are three steps involved:

• First construct the query object in a regular way.
• Then compile it in your database dialect
• and print the result.

In my case I use py.test as a test runner. Ending the test with an assert 0 will result in a solid failure and it will dump the captured stdout.

See the code

First of all, assume a model class called Item which we want to query:

def test_database_query(db_session):
    from my.models import Item
    from sqlalchemy import func
    from sqlalchemy.dialects import postgresql
    from sqlalchemy.orm import load_only

    query = db_session.query(Item)
    query = query.options(load_only('id'))

In my case I am currently exploring how to call a SQL function via SQLAlchemy. The current way does not yet render an ARRAY type, which I can see in the output when running this test case:

query = query.order_by(
    func.array_position((1, 2), Item.id),
)

Just converting the query to a str is already useful, often sufficient:

print("By default we don't see parameters, still useful sometimes:")
print(str(query))

But in this case I need to see more to be sure that I got the types right:

print("Looking at the query with parameter values being replaced:")
print(str(query.statement.compile(
    dialect=postgresql.dialect(),
    compile_kwargs={"literal_binds": True})))

I like to know that I won't commit this experiment by accident. This is my reminder to either clean up and turn it into a useful test case to stay or to wipe the experiment.

assert 0