Python Psycopg2 - Insert multiple rows with one query

This article is about inserting multiple rows in our table of a specified database with one query. There are multiple ways of executing this task, let's see how we can do it from the below approaches.

Method 1: Inserting Values through Naive method

In this method, we import the psycopg2 package and form a connection using the psycopg2.connect() method, we connect to the 'Classroom' database. after forming a connection we create a cursor using the connect().cursor() method, it'll help us fetch rows. after that we execute the insert SQL statement, which is of the form :

insert into table_name ( column1, column2, .... column_n) values (...) (...) (...) ;

The SQL statement is executed by cursor.execute() method. we fetch rows using the cursor.fetchall() method.

CSV Used:

👁 Image

Example: 

Output:

👁 Image
👁 Image

Output in Python:

(4, 'Linnett', 79)
(5, 'Jayden', 45)
(6, 'Sam', 63)
(7, 'Zooey', 82)
(8, 'Robb', 97)
(9, 'Jon', 38)
(10, 'Sansa', 54)
(11, 'Arya', 78)
(12, 'sarah', 90)
(13, 'Ray', 81)

 Method 2: Inserting Values through cursor.mogrify()

The code is the same as the previous example, but the difference is cursor.mogrify() method.

cursor.mogrify() method: 

After the arguments have been bound, return a query string. The string returned is the same as what would be sent to the database if you used the execute() method or anything similar. The string that is returned is always a bytes string and this is faster than executemany() method.

Syntax:

cur.mogrify("INSERT INTO test (col) VALUES (%s, %s....)", (val1, val2,...)(......))

cursor.mogrify() returns a bytes string but we want it to be in string format so we just need to decode the result of mogrify back to a string by using the decode('utf-8') hack.

Example:

Output:

👁 Image
👁 Image

Method 3: Inserting Values through executemany() method

The approach of this example is the same as before but instead of using cursor.mogrify() we use cursor.executemany() method. executemany() is slower compared to mogrify() method.

executemany(): 

It is used to Apply a database action (query or command) to all parameter tuples or mappings in the vars list sequence. The function is especially useful for database update instructions because it discards any result set produced by the query. The query's parameters are bound using the same principles as the execute() function.

Syntax

executemany(query, variable_list)

Example:

Output:

👁 Image
👁 Image