How to quote a string value explicitly (Python DB API/Psycopg2)


For some reasons, I would like to do an explicit quoting of a string value (becoming a part of constructed SQL query) instead of waiting for implicit quotation performed by cursor.execute method on contents of its second parameter.

By "implicit quotation" I mean:

value = "Unsafe string"
query = "SELECT * FROM some_table WHERE some_char_field = %s;"
cursor.execute( query, (value,) ) # value will be correctly quoted

I would prefer something like that:

value = "Unsafe string"
query = "SELECT * FROM some_table WHERE some_char_field = %s;" % \
cursor.execute( query ) # value will be correctly quoted, too

Is such low level READY_TO_USE_QUOTING_FUNCTION expected by Python DB API specification (I couldn't find such functionality in PEP 249 document). If not, maybe Psycopg2 provides such function? If not, maybe Django provides such function? I would prefer not to write such function myself...

11/21/2008 10:43:05 PM

Accepted Answer

Ok, so I was curious and went and looked at the source of psycopg2. Turns out I didn't have to go further than the examples folder :)

And yes, this is psycopg2-specific. Basically, if you just want to quote a string you'd do this:

from psycopg2.extensions import adapt

print adapt("Hello World'; DROP DATABASE World;")

But what you probably want to do is to write and register your own adapter;

In the examples folder of psycopg2 you find the file '' there is an example of how to cast and quote a specific type in a special way.

If you have objects for the stuff you want to do, you can just create an adapter that conforms to the 'IPsycopgSQLQuote' protocol (see pydocs for the that's the only reference I can find to that name) that quotes your object and then registering it like so:

from psycopg2.extensions import register_adapter

register_adapter(mytype, myadapter)

Also, the other examples are interesting; esp. '' and ''.

7/13/2012 8:00:05 PM

I guess you're looking for the mogrify function.


>>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
"INSERT INTO test (num, data) VALUES (42, E'bar')"

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow