CODEWITHSUNDEEP
pythonmysqlsqldictionaryinsert
Xzeion
Xzeion

Reputation: 25

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

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 ?

Upvotes: 1

Views: 1538

Answers (1)

Air
Air

Reputation: 8595

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.

Upvotes: 1

Related Questions