Using pythons dictionary's to create a generic MySQL insert string

Question

I would like to write a generic SQL command that could take in its data from a Dictionary. This way I can simply pass a dictionary into a function and get that data on to a MySQL server.

Example:

dict = {'table':'test_table','dataA':dataValue1,'dataB':dataValue2, ect}

cursor.execute("""INSERT INTO %(table)s (%(columName1)s,%(columName2)s,ect)
VALUES (%(dataA)s,%(dataB)s)""", dict)

Something like that. I also need the command to support dictionary's of different length. The idea is that as long as said tables exist all of my data gets inserted without having to write out a pile of SQL querys.

Any one have a clean way of going about this or perhaps some reading I could do to solve this problem ?

yes, I could write a bit of code to create the tables if they dont exist but thats not what I am getting at here so I didnt want to complicate the issue. for the sake of example lets say the database is all setup and the column names are 'dataA and 'dataB' — Xzeion, Apr 07 '14 at 19:18
I would like to be able to pass in the column names too. so suppose the next dict only had 'dataC' and 'dataD' I would want this to still work. (supposing that dataC and D also already exist) — Xzeion, Apr 07 '14 at 19:20
Okay, I understand that you want to build that query programmatically? What have you tried so far? — univerio, Apr 07 '14 at 19:36
several variations of what you see above. I am having trouble nailing down the proper syntax for passing in all the variables so that SQL will accept it. I can build the string but nothing I try seems to work. — Xzeion, Apr 07 '14 at 19:43

score 1 · Accepted Answer · edited May 23 '17 at 12:06

The documentation for the MySQLdb module is located here on sourceforge. Under the "Functions and attributes" subheading, while discussing the paramstyle attribute, you can find:

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

So, you won't be able to use the module's execute method in the way that you showed in your example.

Another limitation of the execute method is that you need a %s placeholder everywhere you want to insert a parameterized column value. So, if you want an indefinite number of values, you need an indefinite number of placeholders. You could write a function like this:

def prepareinsert(mydict):
    query = """
    INSERT INTO {0} ({1}) VALUES ({2});
    """
    d = dict(mydict)  # don't modify the input dictionary if you want to reuse it
    table = d.pop('table')
    columns = ','.join(d.keys())
    placeholders = ','.join(['%s'] * len(d))
    values = d.values()
    return (query.format(table, columns, placeholders), values)

This gives you the two elements that execute wants - a query at minimum, and if the query has %s placeholders, a sequence or mapping object that contains the desired parameters.

Note that mydict is guaranteed to return the keys and values in the same order, as long as it's not altered in the meantime (see: Python dictionary: are keys() and values() always the same order?)

You might use this function in the following manner:

input = {'table':'favorite_actors', 'columnA':'Brent', 'columnB':'Spiner'}
query, params = prepareinsert(input)
cursor.execute(query, params)

Of course, you still need to know that your input is correct.

Thank you. This is setting me up with the right line of thinking. Just use a function to build the string bit by bit until I have a proper query. I do have complete control over my inputs so I can get those sorted too. — Xzeion, Apr 07 '14 at 20:09
@user3482234 It'll do what you asked for, but I make no claims about whether this is really the best approach for whatever you're doing more generally. ;) — Air, Apr 07 '14 at 20:14
for the moment I just need it to work. I can worry about coming up with clever ways to optimize the code later. — Xzeion, Apr 07 '14 at 20:16

Using pythons dictionary's to create a generic MySQL insert string

1 Answers1