如何使用python更新mysql,其中字段和条目来自字典? [英] How to update mysql with python where fields and entries are from a dictionary?
问题描述
我正在尝试创建一个可重用的mysql语句,用于从一个字典更新密钥是数据库字段,并且进入该字段的数据是与字典关联的值。当创建插入到mysql的函数时,这很容易,因为它只涉及两个列表。现在,我需要分开列表。
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.
这是我必须使用的。
fields = self.dictionary.keys()
vals = self.dictionary.values()
stmt = "UPDATE TABLE table_name SET %s = '%s'" %(.join(fields), .join(vals))"
这将输出如下语句:
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
推荐答案
您不希望使用字符串插入字面值 - SQL注入攻击是不是一件好事(tm),而是使用与数据库相关的占位符语法(我认为MySQL是'%s')。
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').
注意:我在这里使用 .format
,更改为使用%如果你想,但逃避任何%的
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'
假设 cur
是一个DB游标,执行查询的正确方法是:
Assuming cur
is a DB cursor the correct way to perform the query is:
cur.execute(sql, d.values())
这是有效的,因为尽管字典的顺序有效任意顺序,一个dict的键/值的顺序将是一致的,使得 dict(zip(d.keys(),d.values()))== d
。
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
.
这篇关于如何使用python更新mysql,其中字段和条目来自字典?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!