如何在python中使用单个MySQL查询更新多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27743447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update multiple rows with single MySQL query in python?
提问by TechJhola
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
con = mdb.connect('localhost', 'root', 'root', 'kuis')
with con:
cur = con.cursor()
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s ",
("new_value" , "3"))
print "Number of rows updated:", cur.rowcount
With above code the third row's value of the table Writers in the database kuis gets updated with new_value and the output will be Number od rows updated: 1
How am I supposed to update multiple rows at the same time?
使用上面的代码，数据库 kuis 中表 Writers 的第三行值将使用 new_value 进行更新，并且输出将是Number od rows updated: 1
我应该如何同时更新多行？
采纳答案by PeterMmm
Probably you are looking for cursor.executemany.
可能您正在寻找cursor.executemany。
cur.executemany("UPDATE Writers SET Name = %s WHERE Id = %s ",
[("new_value" , "3"),("new_value" , "6")])
回答by Westly White
I don't think mysqldb has a way of handling multiple UPDATE queries at one time.
我不认为 mysqldb 有一种方法可以一次处理多个 UPDATE 查询。
But you can use an INSERT query with ON DUPLICATE KEY UPDATE condition at the end.
但是您可以在最后使用带有 ON DUPLICATE KEY UPDATE 条件的 INSERT 查询。
I written the following example for ease of use and readability.
为了便于使用和可读性，我编写了以下示例。
import MySQLdb
def update_many(data_list=None, mysql_table=None):
"""
Updates a mysql table with the data provided. If the key is not unique, the
data will be inserted into the table.
The dictionaries must have all the same keys due to how the query is built.
Param:
data_list (List):
A list of dictionaries where the keys are the mysql table
column names, and the values are the update values
mysql_table (String):
The mysql table to be updated.
"""
# Connection and Cursor
conn = MySQLdb.connect('localhost', 'jeff', 'atwood', 'stackoverflow')
cur = conn.cursor()
query = ""
values = []
for data_dict in data_list:
if not query:
columns = ', '.join('`{0}`'.format(k) for k in data_dict)
duplicates = ', '.join('{0}=VALUES({0})'.format(k) for k in data_dict)
place_holders = ', '.join('%s'.format(k) for k in data_dict)
query = "INSERT INTO {0} ({1}) VALUES ({2})".format(mysql_table, columns, place_holders)
query = "{0} ON DUPLICATE KEY UPDATE {1}".format(query, duplicates)
v = data_dict.values()
values.append(v)
try:
cur.executemany(query, values)
except MySQLdb.Error, e:
try:
print"MySQL Error [%d]: %s" % (e.args[0], e.args[1])
except IndexError:
print "MySQL Error: %s" % str(e)
conn.rollback()
return False
conn.commit()
cur.close()
conn.close()
Explanation of one liners
一个班轮的解释
columns = ', '.join('`{}`'.format(k) for k in data_dict)
is the same as
是相同的
column_list = []
for k in data_dict:
column_list.append(k)
columns = ", ".join(columns)
Here's an example of usage
这是一个使用示例
test_data_list = []
test_data_list.append( {'id' : 1, 'name' : 'Tech', 'articles' : 1 } )
test_data_list.append( {'id' : 2, 'name' : 'Jhola', 'articles' : 8 } )
test_data_list.append( {'id' : 3, 'name' : 'Wes', 'articles' : 0 } )
update_many(data_list=test_data_list, mysql_table='writers')
Query output
查询输出
INSERT INTO writers (`articles`, `id`, `name`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE articles=VALUES(articles), id=VALUES(id), name=VALUES(name)
Values output
值输出
[[1, 1, 'Tech'], [8, 2, 'Jhola'], [0, 3, 'Wes']]
回答by Kapil Tiwari
The simple one I have to write for my use is.
我必须写一个简单的供我使用的是。
sql='''INSERT INTO <Tabel Name> (column 1, column 2, ... , column N) VALUES (%s, %s, ..., %s) ON DUPLICATE KEY UPDATE column1=VALUES(column 1), column3=VALUES(column N)'''mycursor.executemany(sql, data)
sql='''INSERT INTO <Tabel Name> (column 1, column 2, ... , column N) VALUES (%s, %s, ..., %s) ON DUPLICATE KEY UPDATE column1=VALUES(column 1), column3=VALUES(column N)'''mycursor.executemany(sql, 数据)
data = ['value of column 1', 'value of column 2' , ... ,'value of column N']
data = ['第 1 列的值'，'第 2 列的值'，...，'第 N 列的值']
