How to update mysql using python where fields and records are taken from a dictionary?

Question

I am trying to create a reusable mysql statement for updating from a dictionary, where the keys are database fields, and the data you need to enter into this field is the value associated with it in the dictionary. This was easy when creating a function to insert into mysql, as it included only two lists. Now I need to break the lists.

This is what I need to work with.

fields = self.dictionary.keys() vals = self.dictionary.values() stmt = "UPDATE TABLE table_name SET %s = '%s'" %(.join(fields), .join(vals))" 

Here's a statement like:

 UPDATE TABLE table_name SET column1, column2 = ('value1','value2') 

I need this to output to the standard format for updating the table, for example:

 UPDATE table_name SET column1=value1, column2=value2 

Answer 1

You do not want to enter literal values ​​when using string interpolation - SQL injection attacks are not good (tm) . Instead, you use the placeholder syntax that matches your database (I think MySQL is "% s").

Note. I use .format here, change the use of% if you want, but avoid any%

 d = {'col1': 'val1', 'col2': 'val2'} sql = 'UPDATE table SET {}'.format(', '.join('{}=%s'.format(k) for k in d)) print sql # 'UPDATE table SET col2=%s, col1=%s' 

Assuming cur is a DB cursor, the correct way to execute a query is:

 cur.execute(sql, d.values()) 

This works because although the ordering of the dictionary is actually arbitrary order, the order of the dict keys / values ​​will be consistent, so dict(zip(d.keys(), d.values())) == d .

Answer 2

how to try

 stmt = "UPDATE TABLE table_name SET " for k,v in di.items(): stmt += "%s = %s, " %(k,v) stmt = stmt[-2:]