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?

Python PostgreSQL Statement Problem psycopg2 cursor.execute(Table Union) 160. Psycopg2: insert multiple rows with one query. How do I get a list of column names from a psycopg2 cursor? Django.db connection return different values from psycopg2. Michael Fuhr wrote: Right - when you open a cursor PostgreSQL doesn't know how many rows it will return. PostgreSQL selects a query plan based on an.estimate. of how many rows the query will return, but until you fetch all the rows you can't know for sure how many rows there will be. So if i make a but data set as result of a cursor I only 'pay' for the rows I actually fetch? A SQL cursor in PostgreSQL is a read-only pointer to a fully executed SELECT statement's result set. Cursors are typically used within applications that maintain a persistent connection to the PostgreSQL.

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:

• did you query the right tables?
• was there some typo on the conditional clauses?
• did you use a lot of substitutions or build the query in such a way that you lost track of it?

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:

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

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:

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:

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:

Python Postgresql Cursor

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:

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:

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.

Postgresql Cursor With Hold