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

I guess you're looking for the mogrify function.

Example:

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

You should try to avoid doing your own quoting. Not only will it be DB-specific as people have pointed out, but flaws in quoting are the source of SQL injection bugs.

If you don't want to pass around queries and values separately, then pass around a list of the parameters:

def make_my_query():
    # ...
    return sql, (value1, value2)

def do_it():
    query = make_my_query()
    cursor.execute(*query)

(I probably have the syntax of cursor.execute wrong) The point here is that just because cursor.execute takes a number of arguments, that doesn't mean you have to handle them all separately. You can deal with them as one list.

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 'myfirstrecipe.py' 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 myfirstrecipe.py-example...actually 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. 'dialtone.py' and 'simple.py'.