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
– Xzeion, Commented Apr 7, 2014 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
– Xzeion, Commented Apr 7, 2014 at 19:20
Okay, I understand that you want to build that query programmatically? What have you tried so far? — univerio
– univerio, Commented Apr 7, 2014 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
– Xzeion, Commented Apr 7, 2014 at 19:43

Community · Accepted Answer · 2017-05-23 12:06:10Z

1

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.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Apr 7, 2014 at 20:04

Air

8,6852 gold badges56 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Xzeion Over a year ago

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.

Air Over a year ago

@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. ;)

Xzeion Over a year ago

for the moment I just need it to work. I can worry about coming up with clever ways to optimize the code later.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related