CODEWITHSUNDEEP
pythonmysqlmysql-python
JChao
JChao

Reputation: 2319

Is it possible to create a query command that takes in a list of variables in python-mysql

I am trying to do a multiquery which utilizes executemany in MySQLDb library. After searching around, I found that I'll have to create a command that uses INSERT INTO along with ON DUPLICATE KEY instead of UPDATE in order to use executemany

All is good so far, but then I run into a problem which I can't set the SET part efficiently. My table has about 20 columns (whether you want to criticize the fatness of the table is up to you. It works for me so far) and I want to form the command string efficiently if possible.

Right now I have

update_query = """
               INSERT INTO `my_table` 
               ({all_columns}) VALUES({vals})
               ON DUPLICATE KEY SET <should-have-each-individual-column-set-to-value-here>
               """.format(all_columns=all_columns, vals=vals)

Where all_columns covers all the columns, and vals cover bunch of %s as I'm going to use executemany later.

However I have no idea how to form the SET part of string. I thought about using comma-split to separate them into elements in a list, but I'm not sure if I can iterate them.

Overall, the goal of this is to only call the db once for update, and that's the only way I can think of right now. If you happen to have a better idea, please let me know as well.

EDIT: adding more info

all_columns is something like 'id, id2, num1, num2'

vals right now is set to be '%s, %s, %s, %s'

and of course there are more columns than just 4

Upvotes: 0

Views: 24

Answers (1)

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

Assuming that you have a list of tuples for the set piece of your command:

listUpdate = [('f1', 'i'), ('f2', '2')]

setCommand = ', '.join([' %s = %s' % x for x in listUpdate])
all_columns = 'id, id2, num1, num2'
vals = '%s, %s, %s, %s'

update_query = """
               INSERT INTO `my_table`
               ({all_columns}) VALUES({vals})
               ON DUPLICATE KEY SET {set}
               """.format(all_columns=all_columns, vals=vals, set=setCommand)

print(update_query)

Upvotes: 1

Related Questions