How can I use executemany to insert into MySQL a list of dictionaries in Python

Question

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):

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 executemany here to insert into the database given the structure of my data?

The code is vulnerable to SQL injection attacks. What do you think it would do if there were quotes in the values? It needs to be pointed out. — doug65536
– doug65536, Commented May 31, 2022 at 9:13

David.Zheng · Accepted Answer · 2013-08-15 02:14:52Z

50

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

answered Aug 15, 2013 at 2:14

David.Zheng

9138 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Volatil3 Over a year ago

Does executemany sends the entire list in a go or run individual queries?

snakecharmerb Over a year ago

@Volatil3 dev.mysql.com/doc/connector-python/en/…

shamila · Accepted Answer · 2019-12-31 05:13:07Z

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)       
    """

Collectives™ on Stack Overflow

How can I use executemany to insert into MySQL a list of dictionaries in Python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related