MySQL Dynamic Query Statement in Python

I 开发者_如何转开发am trying to accomplish something like the following:

cursor = db.cursor() 
cursor.execute('INSERT INTO media_files (%s, %s, %s, %s ... ) VALUES (%s, %s, %s, %s,   ...)', (fieldlist, valuelist))
cursor.commit()

I have 2 lists, fieldlist and valuelist which each contain the same number of items. What is the best way to generate a dynamic MySQL query statement where the collumns are stored in fieldlist and the values are stored in valuelist?

cursor.execute('INSERT INTO media_files (%s) VALUES (%%s, %%s, %%s, %%s,   ...)' % ','.join(fieldlist), valuelist)

To make it clearer:

sql = 'INSERT INTO media_files (%s) VALUES (%%s, %%s, %%s, %%s,   ...)' % ','.join(fieldlist)
cursor.execute(sql, valuelist)

The cursor expects parameters to be passed as a single sequence, so you need to combine - in order - the field and value lists.

itertools.chain() does exactly that however it returns a generator and I'm not sure if cursor.execute() will accept that as it's param sequence. Try it. If it fails, wrap it with list()

import itertools
sql = 'INSERT INTO media_files (%s, %s, %s, %s ... ) VALUES (%s, %s, %s, %s,   ...)'
cursor.execute(sql, itertools.chain(fieldlist, valuelist))

EDIT:

This solution will not work. This would cause the field names to be escaped and wrapped with quotes which would cause an sql syntax error.
I'll leave this answer as it might serve as a useful example but look to @Trent's answer for the solution.