Home > Database > Mysql Tutorial > body text

How Can I Efficiently Insert Multiple Dictionaries into a MySQL Database Using Python's `executemany()` Method?

Mary-Kate Olsen
Release: 2024-11-07 16:30:03
Original
1011 people have browsed it

How Can I Efficiently Insert Multiple Dictionaries into a MySQL Database Using Python's `executemany()` Method?

Inserting Multiple Dictionaries into MySQL Using executemany() in Python

When scraping data from the web and inserting it into a MySQL database, it's crucial to optimize performance to avoid database lock-ups. The provided code demonstrates an inefficient approach that involves executing a separate query for each row of table data. To address this issue, we will explore how to leverage the executemany() method to insert multiple rows simultaneously.

The executemany() method allows you to insert a list of parameters into a prepared statement. It takes two arguments: the SQL statement and the list of parameters. The SQL statement should have placeholders (%s) for the parameters.

To use executemany() in this scenario, we first need to transform the list of dictionaries into a tuple of values for each row. This can be achieved as follows:

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

Next, we prepare the SQL statement with placeholders for the parameters:

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

Finally, we execute the executemany() method, providing the SQL statement and the list of parameters:

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

This approach significantly improves performance by executing a single query for all the rows in the table, avoiding the overhead of multiple individual queries.

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

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template