How can executemany optimize MySQL insertion of dictionary lists in Python?

Utilizing executemany for Efficient MySQL Insertion of Dictionary Lists in Python

Data scraping often involves extracting information from tables and storing it in a database. While the conventional approach works, it can become inefficient, potentially crashing the database if not optimized.

Consider the following code that attempts to insert data from a list of dictionaries into a MySQL database:

itemBank = []
for row in rows:
    itemBank.append((tempRow2, tempRow1, tempRow3, tempRow4))

# itemBank contains dictionaries representing data from each table row
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()

The issue here lies in manually constructing and executing individual queries for each dictionary in itemBank. This approach is cumbersome and prone to errors.

Leveraging executemany

The solution to this inefficiency is to utilize the executemany method, which allows you to execute the same query multiple times using a list of parameters.

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

In this modified code, we iterate over the rows, extracting values from the dictionaries and creating a list of tuples to pass to executemany. The query itself remains the same, but we now use placeholders %s for the values.

Using executemany drastically improves efficiency, as it sends all the data in one go, reducing the number of database interactions and minimizing the risk of deadlocks.

The above is the detailed content of How can executemany optimize MySQL insertion of dictionary lists in Python?. For more information, please follow other related articles on the PHP Chinese website!