psycopg - Get formatted sql instead of executing

问题:

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?

回答1:

you wold use function curs.mogrify():

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

回答2:

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.