如何使用 executemany 将 Python 中的字典列表插入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18244565/
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 can I use executemany to insert into MySQL a list of dictionaries in Python
提问by Alex Ketay
I'm currently using MySQL and Python to scrape data from the web. Specifically, I am scraping table data and inserting it into my database. My current solution works, but I feel it is extremely inefficient and will most likely lock up my database if I don't rewrite the code. Here is what I currently use (partial code):
我目前正在使用 MySQL 和 Python 从网络上抓取数据。具体来说，我正在抓取表数据并将其插入到我的数据库中。我当前的解决方案有效，但我觉得它效率极低，如果我不重写代码，很可能会锁定我的数据库。这是我目前使用的（部分代码）：
itemBank = []
for row in rows:
itemBank.append((tempRow2,tempRow1,tempRow3,tempRow4)) #append data
#itemBank List of dictionaries representing data from each
row of the table. i.e.
('Item_Name':"Tomatoes",'Item_Price':"10",'Item_In_Stock':"10",'Item_Max':"30")
for item in itemBank:
tempDict1 = item[0]
tempDict2 = item[1]
tempDict3 = item[2]
tempDict4 = item[3]
q = """ INSERT IGNORE INTO
TABLE1
(
Item_Name,
Item_Price,
Item_In_Stock,
Item_Max,
Observation_Date
) VALUES (
"{0}",
"{1}",
"{2}",
"{3}",
"{4}"
)
""".format(tempDict1['Item_Name'],tempDict2['Item_Price'],tempDict3['Item_In_Stock'],
tempDict4['Item_Max'],getTimeExtra)
try:
x.execute(q)
conn.commit()
except:
conn.rollback()
Executing each row of the table is cumbersome. I've tried using executemany, but I can't seem to figure out how to access the values of the dictionaries correctly.
So, how can I use executemanyhere to insert into the database given the structure of my data?
执行表的每一行都很麻烦。我试过使用executemany，但我似乎无法弄清楚如何正确访问字典的值。那么，executemany鉴于我的数据结构，我如何使用此处插入到数据库中？
采纳答案by David.Zheng
itemBank = []
for row in rows:
itemBank.append((
tempRow2['Item_Name'],
tempRow1['Item_Price'],
tempRow3['Item_In_Stock'],
tempRow4['Item_Max'],
getTimeExtra
)) #append data
q = """ insert ignore into TABLE1 (
Item_Name, Item_Price, Item_In_Stock, Item_Max, Observation_Date )
values (%s,%s,%s,%s,%s)
"""
try:
x.executemany(q, itemBank)
conn.commit()
except:
conn.rollback()
Hope it will help you
希望它会帮助你
回答by shamila
For anyone who needs to use "insert or update" instead of "insert ignore" below query can be used
对于需要使用“插入或更新”而不是“插入忽略”的任何人，可以使用下面的查询
q = """ INSERT INTO TABLE1 (Item_Name, Item_Price, Item_In_Stock, Item_Max, Observation_Date )
values (%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE Item_Name = VALUES(Item_Name),
Item_Price = VALUES(Item_Price), Item_In_Stock = VALUES(Item_In_Stock), Item_Max = VALUES(Item_Max),
Observation_Date = VALUES(Observation_Date)
"""
If Item_Name is the primary key of the TABLE1, then remove it from update part as below, since in update, primary key need not be updated
如果 Item_Name 是 TABLE1 的主键，则将其从更新部分中删除，如下所示，因为在更新中，不需要更新主键
q = """ INSERT INTO TABLE1 (Item_Name, Item_Price, Item_In_Stock, Item_Max, Observation_Date )
values (%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE Item_Price = VALUES(Item_Price), Item_In_Stock = VALUES(Item_In_Stock), Item_Max = VALUES(Item_Max),
Observation_Date = VALUES(Observation_Date)
"""
