https://blog.csdn.net/Yellow_python/article/details/82216982
table_name = 'student'
d = {'name': 'ArYe', 'age': 15} # 更新的字段及对应值
t = ('id', 307) # 更新所需条件
自动构造update语句
sentence = 'UPDATE %s SET ' % table_name + ','.join(['%s=%r' % (k, d[k]) for k in d]) + ' WHERE %s=%r;' % (t[0], t[1])
打印
print(sentence)
UPDATE student SET name=‘ArYe’,age=15 WHERE id=307;
table_name = 'student'
d1 = {'name': 'ArYe', 'age': 15} # 更新的字段及对应值
d2 = {'sex': 'girl', 'id': 307} # 更新所需条件
自动构造update语句
sentence1 = 'UPDATE %s SET ' % table_name
sentence2 = ','.join(['%s=%r' % (k, d1[k]) for k in d1])
sentence3 = ' WHERE '
sentence4 = ' AND '.join(['%s=%r' % (k, d2[k]) for k in d2])
sentence5 = sentence1 + sentence2 + sentence3 + sentence4 + ';'
打印
print(sentence1)
print(sentence2)
print(sentence3)
print(sentence4)
print(sentence5)
打印结果
UPDATE student SET
name=‘ArYe’,age=15
WHERE
sex=‘girl’ and id=307
UPDATE student SET name=‘ArYe’,age=15 WHERE sex=‘girl’ AND id=307;
备注
应用场景
爬虫数据写入数据库
优点：
通用性好，直接复制可用，不用费脑写sql
知识补充
任意字典生成insert语句
知识进阶
字典写入数据库
https://blog.csdn.net/weixin_42509766/article/details/118817601
MySQL如何通过字典表update,如何使用python更新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.
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
解决方案
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.
https://www.modb.pro/db/245152
根据传入的字典，生成MySQL中updqte..set ...where
的更新sql，解决日常开发中不断写sql并频繁出错的烦恼！
提示：
根据返回的结果调用 pymysql.cursor.execute(query, args)
插入即可
注意:
传入的字典key，是MySQL表中对应的字段名。
def update_where_sql_data(table_name: str, update_content: dict, where_content: dict):
"""
根据传入的字典生成 UPDATE...SET...WHERE条件的sql
字典的key，是MySQL表中对应的字段名
:param table_name: 保存的表名
:param update_content: 需要更新的数据 dict
:param where_content: 更新的条件 dict
:return: update_sql:<str> 更新的update sql, dat:<tuple>保存的内容
"""
# sql 生成
sql = 'UPDATE `{}` SET {} WHERE {};'
update_content_sql = ''.join(['`{}`=%s, '.format(val) for val in list(update_content.keys())])[:-2]
where_content_sql = ''.join(['`{}`=%s, '.format(val) for val in list(where_content.keys())])[:-2]
update_sql = sql.format(table_name, update_content_sql, where_content_sql)
# 保存的内容生成
update_data_li = list(update_content.values())
where_data_li = list(where_content.values())
data = tuple(update_data_li + where_data_li)
# print(update_sql)
# print(data)
return update_sql, data
if name == 'main':
# 调用参考
__update_content = {"name": "hello world"}
__where_content = {"id": 1}
sql, data = update_where_sql_data('your_table_name', update_content=__update_content, where_content=__where_content)
print(sql)
print(data)
# 执行 pymysql 插入即可, 此处省略
# pymysql.cursor.execute(sql, data)
# pymysql.conn.commit()
输出结果：
UPDATE your_table_name SET name=%s WHERE id=%s;
('hello world', 1)
网友评论