CODEWITHSUNDEEP
pythonmysqlsql
Trent Seed
Trent Seed

Reputation: 344

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?

Upvotes: 1

Views: 3859

Answers (2)

Rob Cowie
Rob Cowie

Reputation: 22619

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.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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)

Upvotes: 3

Related Questions