How to Efficiently Insert a List of Dictionaries into a MySQL Database Using Python's executemany?

Using executemany to Insert a List of Dictionaries into MySQL from Python

In Python, executemany is a valuable tool for efficiently inserting multiple rows into a MySQL database. Consider the following scenario: You have a list of dictionaries named itemBank, with each dictionary representing data from a row in a table. To insert this data into the TABLE1 table, you're currently using a laborious method that involves executing separate SQL queries for each row.

To optimize this process, let's explore how to use executemany. The first step is to restructure itemBank into a list of tuples. Each tuple will contain the values you want to insert into the database, extracted from the corresponding dictionaries.

itemBank = [] 
for row in rows:
    itemBank.append((
        tempRow2['Item_Name'],
        tempRow1['Item_Price'],
        tempRow3['Item_In_Stock'],
        tempRow4['Item_Max'], 
        getTimeExtra
        ))

Next, construct an SQL query template (q) that defines the table columns and placeholders for the values to be inserted.

q = """ insert ignore into TABLE1 (
        Item_Name, Item_Price, Item_In_Stock, Item_Max, Observation_Date ) 
        values (%s,%s,%s,%s,%s)           
    """

Finally, use the executemany method to execute the query with the list of tuples (itemBank) as the parameter.

try:
    x.executemany(q, itemBank)
    conn.commit()
except:
    conn.rollback()

This approach significantly reduces the number of SQL queries executed, thereby improving the efficiency of the data insertion process.

The above is the detailed content of How to Efficiently Insert a List of Dictionaries into a MySQL Database Using Python's executemany?. For more information, please follow other related articles on the PHP Chinese website!