How can I use executemany to insert into MySQL a list of dictionaries in Python
Solution 1:
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
Solution 2:
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
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)
"""