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