Generate SQL statements with python


Question

I need to generate a list of insert statements (for postgresql) from html files, is there a library available for python to help me properly escape and quote the names/values? in PHP i use PDO to do the escaping and quoting, is there any equivalent library for python?

Edit: I need to generate a file with sql statements for execution later

1
10
10/14/2009 4:32:05 AM

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 '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'.

30
7/13/2012 8:00:05 PM

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')"

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