psycopg - Get formatted sql instead of executing

Question

I have a piece of Python code, that interacts with a PostgreSQL database via psycopg.

All literature warns against doing sql formatting by oneself, and recommends letting the driver do it. E.g.:

cur.execute('select name, age from people where name = %s;', ('ann',) )

The driver then formats the sql string. Let's say I don't want to execute anything, but I just want the fully formatted sql string. Is there any functionality for getting this formatted sql in the psycopg module?

Over

Answer 1

you wold use function curs.mogrify():

SQLstring = curs.mogrify('select name, age from people where name = %s;', ('ann',) )

Tsingyuan

Just to note, psycopg's author has stated that mogrify should only be used for debugging purposes. – Rademacher

This mogrify function needs DB API 2.0, initd.org/psycopg/docs/cursor.html#cursor.mogrify. – Over

Presumably the output of this is good for prepending 'EXPLAIN ' and executing to see the execution plan? – Friar

@ErikNinn-Hansen pyscopg2 implements DB API 2.0. mogrify is a psycopg2 extension. DB API 2.0 is not a dependency. – Kessiah

In psycopg 3 you need to use (Async)ClientCursor to be able to call mogrify method. I've created a separate community wiki answer with more details, as the edit queue is full for this answer. Feel free to extend it. – Greenhead

Answer 2

In psycopg 3 there are several cursor classes, and only ClientCursor and AsyncClientCursor have mogrify method. If you want to use mogrify, you need to either use cursor_factory=ClientCursor in the connect function, or you need to instantiate (Async)ClientCursor similar to this:

psycopg.ClientCursor(conn).mogrify(query, (param1, param2))

Further details

Corker

Answer 3

edit: it looks like the following is not quite correct, psycopg doesn't use PQexecParams, but is planning to (See my comment below). Leaving answer because it's a useful abstraction, and true for most parameterized queries, just apparently not psycopg2 just yet.

---

Actually, the driver doesn't format the string. What you're using there is called a parameterized query: the sql string and the parameters are sent "across the wire" to postgres exactly as you specified them, postgres parses the template string, and then inserts the parameters into the parse tree. That way the parameters never have to be encoded or decoded, so there's no chance of any encoding errors, glitches, or injection attacks. OTOH, that means at no point in the code is there anything like the formatting routine you're looking for.

For more details, see the "PQexecParams" method in the libpq documentation - libpq is Postgres's C-level client interface library.

Rademacher

Wow, interesting. That must mean that with parameterized queries, if getting a sql syntax error it must be the code itself, and not some odd characters in string value? – Over

That was how I'd always assumed things worked - and you can safely treat it as if that's how it works. However, I just finished examining the psycopg source, and it looks like they are handling template formatting & string escaping on the client-side (see git repo). Though it does look like they are working towards using PQexecParams where possible - see this thread. – Rademacher

Just to shed a little bit of light.. PQexecParams has some limitations so client-side formatting of the query is there to stay. The main reason for mogrify to be used only for debug is because we can't guarantee its result will always be the exact query sent to the backend (for example if we switch to preparing the query and using PQexecParams). – Clot