Generate SQL statements with python [duplicate]

Question

This question already has an answer here:

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

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

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 3

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 4

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 5

I know this is an old question, but I've often wanted what it seems the OP wants: A VERY simple library for generating basic SQL.

The below functions do just that. You give them a table name and a dictionary containing the data you want to use and they return the SQL query for the operation you need.

The key/value pairs represent field names and values in the database rows.

def read(table, **kwargs):
    """ Generates SQL for a SELECT statement matching the kwargs passed. """
    sql = list()
    sql.append("SELECT * FROM %s " % table)
    if kwargs:
        sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)


def upsert(table, **kwargs):
    """ update/insert rows into objects table (update if the row already exists)
        given the key-value pairs in kwargs """
    keys = ["%s" % k for k in kwargs]
    values = ["'%s'" % v for v in kwargs.values()]
    sql = list()
    sql.append("INSERT INTO %s (" % table)
    sql.append(", ".join(keys))
    sql.append(") VALUES (")
    sql.append(", ".join(values))
    sql.append(") ON DUPLICATE KEY UPDATE ")
    sql.append(", ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)


def delete(table, **kwargs):
    """ deletes rows from table where **kwargs match """
    sql = list()
    sql.append("DELETE FROM %s " % table)
    sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)

You use it like so. Just give it a table name and a dictionary (or use the **kwargs feature of python):

>>> upsert("tbl", LogID=500, LoggedValue=5)
"INSERT INTO tbl (LogID, LoggedValue) VALUES ('500', '5') ON DUPLICATE KEY UPDATE LogID = '500', LoggedValue = '5';"

>>> read("tbl", **{"username": "morten"})
"SELECT * FROM tbl WHERE username = 'morten';"

>>> read("tbl", **{"user_type": 1, "user_group": "admin"})
"SELECT * FROM tbl WHERE user_type = '1' AND user_group = 'admin';"

But BEWARE OF SQL INJECTION ATTACKS

Look what happens when a malicious user of your code does this:

>>> read("tbl", **{"user_group": "admin'; DROP TABLE tbl; --"})
"SELECT * FROM tbl WHERE user_group = 'admin'; DROP TABLE tbl; --';"

It's easy to make your own makeshift ORM but you only get what you see -- you have to escape the input yourself :)