MySQL Dynamic Query Statement in Python

Question

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?

After posting my answer it occurred to me that your lists might be of variable length. Is that the case? — Rob Cowie
– Rob Cowie, Commented Jul 5, 2011 at 22:47

Clodoaldo Neto · Accepted Answer · 2011-07-05 23:29:01Z

3

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)

edited Jul 5, 2011 at 23:29

answered Jul 5, 2011 at 22:32

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

5 Comments

Trent Seed Over a year ago

To make sure im understanding correctly, there should only be one '%s' for columns even if there are multiple fields in fieldlist and there should be one '%%s' for each value that is in valuelist? Something like this, assuming 4 columns and values? cursor.execute('INSERT INTO media_files (%s) VALUES (%%s, %%s, %%s, %%s)' % ','.join(fieldlist), (valuelist,))

Rob Cowie Over a year ago

If you use string interpolation, you lose the sql escaping that MySQLdb does for you if you pass a parameter sequence

Clodoaldo Neto Over a year ago

@Trent I edited the syntax. @Rob Cowie The adapter will not substitute the column names, or better, it will substitute they wrapped in quotes which will result in a syntax error. So the column names will have to be passed before the adapter gets the sql string.

Rob Cowie Over a year ago

@Trent You're absolutely right. The erroneous escaping of the field names didn't occur to me. Nice one.

Trent Seed Over a year ago

thanks everyone for the feedback. @Clodoaldo, the code isn't quite executing as expected. When i break it into two steps as you describe in the second "clearer" solution, the cursor.execute(..) fails. The sql variable looks correct (has columns filled in but no values yet). I even tried adding a middle step where I interpolate sql a second time with % ','.join(valuefields). Any other thoughts?

Rob Cowie · Accepted Answer · 2011-07-06 12:46:17Z

2

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.

edited Jul 6, 2011 at 12:46

answered Jul 5, 2011 at 22:42

Rob Cowie

22.6k6 gold badges65 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySQL Dynamic Query Statement in Python

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related