How Can I Efficiently Insert Multiple Rows into a PostgreSQL Database Using Psycopg2?

Inserting Multiple Rows Efficiently with Psycopg2

When dealing with large datasets, it becomes crucial to insert data into a database in an efficient manner. Psycopg2 provides a convenient way to perform bulk inserts using a single query.

To insert multiple rows with one query statement, you can use the following simple approach:

import psycopg2

# Connect to the database
connection = psycopg2.connect(...)

# Prepare the cursor
cursor = connection.cursor()

# Create the query template
query = "INSERT INTO t (a, b) VALUES (%s, %s)"

# Prepare the data to insert
data = [(1, 2), (3, 4), (5, 6)]

# Execute the query using Psycopg2's "executemany" method
cursor.executemany(query, data)

# Commit the changes to the database
connection.commit()

The executemany() method takes two arguments: the query template and a list of tuples containing the values to be inserted. Each tuple corresponds to one row of data.

By leveraging this method, you can insert multiple rows into your database with a single query, significantly reducing the number of round-trips to the database and improving performance.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into a PostgreSQL Database Using Psycopg2?. For more information, please follow other related articles on the PHP Chinese website!