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

Efficient Insertion of Multiple Rows with psycopg2

When handling bulk inserts into a database, it becomes essential to optimize performance. While psycopg2 offers the executemany method for this task, a slightly simpler and often faster approach exists.

This approach involves utilizing the mogrify() function and concatenating the generated SQL statements into a single query. It requires the creation of a tuple containing the data to be inserted. Let's consider an example:

args = [(1, 2), (3, 4), (5, 6)]
args_str = ','.join(cursor.mogrify("(%s,%s)", (x, )) for x in args)
cursor.execute("INSERT INTO t (a, b) VALUES " + args_str)

This method has been empirically shown to significantly improve performance, especially for large batches of data. In one instance, it took only 10 seconds to insert 2000 rows using this method, compared to 2 minutes using executemany.

By utilizing this technique, developers can achieve faster bulk inserts with psycopg2, thereby optimizing database write operations.

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