CODEWITHSUNDEEP
pythondictionarymysql-python
peixe
peixe

Reputation: 1292

Python MySQLdb update table from dictionary of lists

I am trying to update a MySQL table through Python, using MySQLdb package.

I would want to update a certain table with data from a dictionary having this format:

muts={'YES': [rice,bread,pasta]
       'NO': [sweets,fat,milk,choco]}

I need that for rows on the mysql table whose column food value corresponding with each of the entries on lists (values for the dictionary above), another column called accept would be updated with its corresponding key; 'YES' or 'NO'.

So far, when I wanted to update entries from a table using a single list, I use to do the following:

food=[sweets,fat,milk,choco]
sql = "UPDATE test2 g SET accept='NO' WHERE g.food IN (%s)"
to_add = ', '.join(list(map(lambda x: '%s', any_list)))
sql = sql % to_add
cursor.execute(sql, any_list)

But now that I want to simultaneously update with key-values from a dictionary; I got lost.

Upvotes: 1

Views: 1612

Answers (1)

aneroid
aneroid

Reputation: 16112

Define the sql with two %s's and loop through your dictionary for the to_add line, so you place the k and the to_add:

>>> muts={'YES': ['rice','bread','pasta'],
...        'NO': ['sweets', 'fat', 'milk', 'choco']}
>>> muts
{'YES': ['rice', 'bread', 'pasta'], 'NO': ['sweets', 'fat', 'milk', 'choco']}
>>> sql = "UPDATE test2 g SET accept='%s' WHERE g.food IN (%s)"
>>> for k in muts:
...     to_add = ', '.join(muts[k])
...     ex_sql = sql % (k, to_add)
...     print ex_sql
...     # cursor.execute(ex_sql) here
... 
UPDATE test2 g SET accept='YES' WHERE g.food IN (rice, bread, pasta)
UPDATE test2 g SET accept='NO' WHERE g.food IN (sweets, fat, milk, choco)
>>>

You can put that in a function and pass it 'any_list'. Unless execute takes a list of statements, you can't use the .execute( , any_list) in one line.

Btw, the join line should actually be: 

to_add = "'%s'" % "', '".join(muts[k])

so that you end up with ' quotes around each food type:

... # that gives...
UPDATE test2 g SET accept='YES' WHERE g.food IN ('rice', 'bread', 'pasta')
UPDATE test2 g SET accept='NO' WHERE g.food IN ('sweets', 'fat', 'milk', 'choco')

You may also want to use the ? placeholder syntax instead of %s to avoid sql injection attacks.

Upvotes: 2

Related Questions