Python generate sql insert query

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 :]

EDIT:

I'm still using my old library. I've updated it a bit lately: //github.com/kokke/nano-ORM-py

There is a neater, more Pythonic way of doing this using the str.format function. However, it is not the way you should do it.

Currently, the way you are building your query, you are vulnerable to SQL injection. This is the point @Mat's Mug was getting at in his comment. What if a malicious user decides to give the following as a user ID:

user_id = "1,1,99,'today'];DROP TABLES;INSERT INTO words [word, user_id, game_id, score, time_stamp] VALUES ['Goodbye', 1"

If this happens, a disaster beyond your imagination will occur*!

To prevent this, the cursor from the MySQLdb module [which I assume you are using] allows you to pass in a tuple or dict of the parameters to the execute function:

time_stamp = datetime.datetime.now[].isoformat[]
sql = "INSERT INTO words [word, user_id, game_id, score, time_stamp] VALUES {}"

# Create the query
sql = sql.format[','.join[["['%s', %s, %s, %s, '%s']" for _ in words]]]

# Create the parameter tuple.
params = []
for word in words:
    params += [word['str'], user_id, game_id, word['score'], time_stamp,]

# Sending a tuple is easiest in this situation
cur.execute[sql, params]

If you want to look at another database solution for Python, take a look at SQLAlchemy. Its a pretty powerful framework that makes working with databases much simpler.

*=Sorry for the Phantom of the Opera quote. I'm feeling oddly dramatic right now.

Update

To answer your question about removing the for-loop, the best you can hope for is to use a list comprehension. In many situations, list comprehensions are typically the Pythonic way of iterating over a collection. However, in your situation, this probably is not the case.

The comprehension for your current code would look like this:

','.join[[str[tuple[[word['str'], user_id, game_id, word['score'], time_stamp]]]
          for word in words]]

This is really only effective creating the end to your query. Unfortunately, in addition to being hard to read, it is still vulnerable to SQL injection.

In your case, I think a for-loop would be the cleanest way to implement what you want.

How write SQL insert query in Python?

How do you insert multiple rows in SQL using Python?

How do I create a .SQL file in Python?

How do you add user input to a database in Python?