CODEWITHSUNDEEP
pythonmysqldictionary
ccdpowell
ccdpowell

Reputation: 659

How to update mysql with python where fields and entries are from a dictionary?

I am trying to create a re-usable mysql statement for updating from a dictionary where the keys are the database fields and the data to go into that field is the value associated with it in the dictionary. This was easy when creating a function for inserting into mysql because it just involved two lists. Now, I need to break apart the lists.

Here is what I have to work with.

fields = self.dictionary.keys()
vals = self.dictionary.values()

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

This outputs a statement like:

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

I need it to output to standard format for updating a table like:

UPDATE table_name SET column1=value1, column2=value2

Upvotes: 10

Views: 12361

Answers (3)

tree em
tree em

Reputation: 21781

def dict2sql(table,dt,cond):
    return 'UPDATE `{}` SET {} WHERE {}'.format(table,', '.join('{}="{}"'.format(k,v) for k, v in dt.items()),cond)

d = {'col1': 'val1', 'col2': 'val2'}
print(dict2sql("table", d, "name=1"))

Upvotes: 0

Jon Clements
Jon Clements

Reputation: 142256

You don't want to be putting literal values in using string interpolation - SQL injection attacks are not a Good Thing(tm). Instead, you use the placeholder syntax relevant for your database (I think MySQL's is '%s').

Note: I'm using .format here, change to use % if you want, but escape any %'s

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 perform the query is:

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

This works because although the ordering of a dictionary is effectively arbitrary order, the order of keys/values of a dict will be consistent such that dict(zip(d.keys(), d.values())) == d.

Upvotes: 21

acid_crucifix
acid_crucifix

Reputation: 362

how about trying

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

stmt = stmt[-2:]

Upvotes: 1

Related Questions