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?

score 23 · Accepted Answer · answered Jul 21 '11 at 12:27

23

you wold use function curs.mogrify():

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

answered Jul 21 '11 at 12:27

Jan Marek

10,390
3
21
19

2

Just to note, psycopg's author has [stated](http://permalink.gmane.org/gmane.comp.python.db.psycopg.devel/4775) that mogrify should only be used for debugging purposes. – Eli Collins Jul 21 '11 at 12:41
This mogrify function needs DB API 2.0, http://initd.org/psycopg/docs/cursor.html#cursor.mogrify. – Erik Ninn-Hansen Jul 21 '11 at 12:57
Presumably the output of this is good for prepending 'EXPLAIN ' and executing to see the execution plan? – Jonathan Hartley Nov 11 '13 at 08:56
@ErikNinn-Hansen pyscopg2 implements DB API 2.0. `mogrify` is a psycopg2 extension. DB API 2.0 is not a dependency. – jwg May 27 '15 at 09:03
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. – Arpad Horvath -- Слава Україні May 08 '23 at 12:17

Eli Collins · Answer 2 · 2011-07-21T13:12:49.070

1

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.

edited Jul 21 '11 at 13:12

answered Jul 21 '11 at 11:51

Eli Collins

8,375
2
34
38

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? – Erik Ninn-Hansen Jul 21 '11 at 13:10
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](https://dndg.it/cgi-bin/gitweb.cgi?p=public/psycopg2.git;a=blob;f=psycopg/cursor_type.c;h=717cf9ccad879a2aa33b71db9a0029be4932ce4c;hb=HEAD#l388)). Though it does look like they are working towards using PQexecParams where possible - see [this](http://archives.postgresql.org/psycopg/2011-02/msg00076.php) thread. – Eli Collins Jul 21 '11 at 13:10
1

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). – fog Jul 22 '11 at 09:24

score 1 · Answer 3 · answered May 08 '23 at 12:07

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

psycopg - Get formatted sql instead of executing

3 Answers3

Linked