Formatted SQL in Python with Psycopg’s Mogrify

In this Compose Write Stuff Addon, Lucero Del Alba takes a look at the problem of viewing queries sent to a server, and how to solve this problem by using Psycopg's mogrify. Do you want to shed light on a favorite feature in your preferred database? Why not write a short "Addon" for Write Stuff?

When using templates and variables within your script, it's hard to tell what the final query that was sent to the server was, which makes debugging tricky. This is where mogrify can help you.

The Problem

If you have a buggy query, the Python interpreter is going to tell you where the syntax error is, and it will do so by passing you back the DB response. Sometimes, however, it’s not quite a syntax error that's bugging you, but just an empty result set that makes you wonder what went wrong, like:

Let’s take a step backwards for a moment.

If you use the Python DB API v2 properly you don’t need to worry about input sanitation because the driver does it transparently for you. For example, consider the following query:

SELECT * FROM brokers WHERE broker_name LIKE 'A%' AND broker_id > 10  

If A% and 10 need to be variables, you can do the following:

SQL = 'SELECT * FROM brokers WHERE broker_name LIKE %s AND broker_id > %s'

broker_name = 'A%'  
broker_id = 10

cursor.execute(SQL, (broker_name, broker_id))  

In that snippet, execute takes a string with the query as a mandatory argument and a tuple as an optional one. The driver will sanitize the tuple’s content for you — in this case broker_name and broker_id — to prevent some SQL injection tricks, and it will treat them as string and integer, accordingly.

The problem is that you just can’t print out cursor.execute() to see what the query was, and even if you turn that line into a print() statement, it’ll most likely not return the actual query unless you’re only dealing with integers:

print(SQL % (broker_name, broker_id))  
SELECT * FROM brokers WHERE broker_name LIKE A% AND broker_id > 10  

That is not the query that’s sent to the server — there are no string escaping characters (no apostrophes around A%), let alone any sanitation. If you try to run that query on the DB, you’ll receive an error.

Mogrify It

The PostgreSQL driver for Python, Psycopg, comes with a very handy method for dealing with these situations: mogrify. From the Psycopg documentation, mogrify:

Return[s] a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar

You can run mogrify() with the same arguments you would use for execute(), and the result will be as expected:

print(cursor.mogrify(SQL, (broker_name, broker_id)))  
b"SELECT * FROM brokers WHERE broker_name LIKE 'A%' AND broker_id > 10"  

Now this was a simple enough query, but that complexity may escalate very quickly as you start joining tables using Python’s templates, such as {table_name}, to format later with the format() method when building your SQL code, and adding lots of variable substitutions … yes, things will get messy.

Take It Further

In fact, you can implement mogrify() in your workflow and use it as an intermediate step when querying the database, so that you can benefit from easier debugging later:

SQL = "SELECT broker_id, broker_name FROM brokers WHERE broker_name LIKE %s"

query = cursor.mogrify(SQL, ('A%', ))  
cursor.execute(query)

fetchall_brokers = cursor.fetchall()  

Notice that instead of doing the substitutions on execute(), you first mogrify the query and use that output for execution. Now you have the SQL template on a SQL constant, and the actual query in a query variable. Should you have a problem later, you just check that variable.

Not a Python DB API Feature … But There’s a Workaround

Unfortunately, mogrify is not a method defined by the Python DB API, but instead an add-on of the Psycopg driver.

If you're using MySQL, you have a workaround to this problem so that you can see the actual query:

import MySQLdb

conn = MySQLdb.connect()  
cursor = conn.cursor()

cursor.execute('SELECT %s, %s', ('bar', 1))  
cursor._executed  
b"SELECT 'bar', 1"  

However, this is a post hoc analysis (after the fact) will not allow you to see the full query if it provoked an exception in the program (e.g. a syntax error).

In PostgreSQL, you can also do this with the Psycopg driver by replacing _executed with query in the last line, like this:

import psycopg2

conn = psycopg2.connect()  
cursor = conn.cursor()

cursor.execute('SELECT %s, %s', ('bar', 1))  
cursor.query  
b"SELECT 'bar', 1"  

Conclusions

We've shown ways in Python to see what’s the actual query that’s been sent to the server, and this will save you quality time when debugging. If you want to go further, consider that Psycopg's mogrify for PostgreSQL allows you to cache the actual executed statement so you can reuse it whenever you need it.

Lucero dances, plays music, writes about random topics, leads projects to varying and doubtful degrees of success, and keeps trying to be real and he keeps failing.

This article is licensed with CC-BY-NC-SA 4.0 by Compose.