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

Answer 1

For robustness, I recommend using prepared statements to send user-entered values, no matter what language you use. :-)

Answer 2

http://stackoverflow.com/questions/309945/how-to-quote-a-string-value-explicitly-python-db-api-psycopg2

Answer 3

The python db api 2.0 has a ".execute" method for connection objects. You can specify parameters (use a comma NOT a % sign to separate params from the query string) with this function.

Answer 4

try sqlalchemy

Answer 5

SQLAlchemy provides a robust expression language for generating SQL from Python.

Like every other well-designed abstraction layer, however, the queries it generates insert data through bind variables rather than through attempting to mix the query language and the data being inserted into a single string. This approach avoids massive security vulnerabilities and is otherwise The Right Thing.

Answer 6

Quoting parameters manually in general is a bad idea. What if there is a mistake in escaping rules? What if escape doesn't match used version of DB? What if you just forget to escape some parameter or erroneously assumed it can't contain data requiring escaping? That all may cause SQL injection vulnerability. Also, DB can have some restrictions on SQL statement length while you need to pass large data chunk for LOB column. That's why Python DB API and most databases (Python DB API module will transparently escape parameters, if database doesn't support this, as early MySQLdb did) allow passing parameters separated from statement:

.execute(operation[,parameters])

Answer 7

Another option for an ORM is storm. It's good if you want something simple. See the tutorial here.