Python Psycopg2 - Insert multiple rows with one query
Last Updated :
26 Oct, 2022
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:

Example:
Python3
# importing packages
import psycopg2
# forming connection
conn = psycopg2.connect(
database="Classroom",
user='postgres',
password='pass',
host='127.0.0.1',
port='5432'
)
conn.autocommit = True
# creating a cursor
cursor = conn.cursor()
# sql statement to be executed
sql = '''insert into classroom(enrollment_no, name , science_marks)
values(12, 'sarah', 90),(13,'Ray',81); '''
# executing the sql statement
cursor.execute(sql)
# select statement to display output
sql1 = '''select * from classroom;'''
# executing sql statement
cursor.execute(sql1)
# fetching rows
for i in cursor.fetchall():
print(i)
# committing changes
conn.commit()
# closing connection
conn.close()
Output:


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:
Python3
# importing packages
import psycopg2
# forming connection
conn = psycopg2.connect(
database="Classroom",
user='postgres',
password='pass',
host='127.0.0.1',
port='5432'
)
conn.autocommit = True
# creating a cursor
cursor = conn.cursor()
# list of rows to be inserted
values = [(14, 'Ian', 78), (15, 'John', 88), (16, 'Peter', 92)]
# cursor.mogrify() to insert multiple values
args = ','.join(cursor.mogrify("(%s,%s,%s)", i).decode('utf-8')
for i in values)
# executing the sql statement
cursor.execute("INSERT INTO classroom VALUES " + (args))
# select statement to display output
sql1 = '''select * from classroom;'''
# executing sql statement
cursor.execute(sql1)
# fetching rows
for i in cursor.fetchall():
print(i)
# committing changes
conn.commit()
# closing connection
conn.close()
Output:


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:
Python3
# importing packages
import psycopg2
# forming connection
conn = psycopg2.connect(
database="Classroom",
user='postgres',
password='pass',
host='127.0.0.1',
port='5432'
)
conn.autocommit = True
# creating a cursor
cursor = conn.cursor()
# list of rows to be inserted
values = [(17, 'rachel', 67), (18, 'ross', 79), (19, 'nick', 95)]
# executing the sql statement
cursor.executemany("INSERT INTO classroom VALUES(%s,%s,%s)", values)
# select statement to display output
sql1 = '''select * from classroom;'''
# executing sql statement
cursor.execute(sql1)
# fetching rows
for i in cursor.fetchall():
print(i)
# committing changes
conn.commit()
# closing connection
conn.close()
Output:


Similar Reads
Executing SQL query with Psycopg2 in Python
In this article, we are going to see how to execute SQL queries in PostgreSQL using Psycopg2 in Python. Psycopg2 is a PostgreSQL database driver, it is used to perform operations on PostgreSQL using python, it is designed for multi-threaded applications. SQL queries are executed with psycopg2 with t
2 min read
Update multiple rows in same query in PostgreSQL using Pyscopg2-Python
In this article, we are going to update multiple rows in the same query in PostgreSQL using Pyscopg2in Python. We can update multiple values at once by using the update clause from PostgreSQL. First, we import the psycopg2 package and establish a connection to a PostgreSQL database using the pyscopg
1 min read
SQL Query to Insert Multiple Rows
In SQL, the INSERT statement is used to add new records to a database table. When you need to insert multiple rows in a single query, the INSERT statement becomes efficient.In this article, We will learn different methods such as using basic INSERT statements, utilizing INSERT INTO ... SELECT for bu
4 min read
Perform Insert Operations with psycopg2 in Python
psycopg2 is a widely used Python library designed to facilitate communication with PostgreSQL databases, offering a robust and efficient way to perform various database operations. It is a powerful and flexible connector, which allows Python applications to execute SQL commands and handle data seaml
9 min read
Insert Multiple Rows in MySQL Table in Python
MySQL is an open-source, Relational Database Management System (RDBMS) that stores data in a structured format using rows and columns. It is software that enables users to create, manage, and manipulate databases. So this is used in various applications across a wide range of industries and domains.
5 min read
How to Fetch All Rows with psycopg2.fetchall in Python
PostgreSQL, commonly known as Postgres, is a highly capable, open-source relational database management system (RDBMS). Renowned for its robustness, scalability, and adherence to SQL standards, PostgreSQL is used in a variety of applications, from small projects to large-scale enterprise systems, du
9 min read
Chain multiple statements within Psycopg2
In this tutorial, we will see how to chain multiple statements with psycopg2 which is a PostgreSQL database adapter for Python. Chaining is supported by most SQL databases and can be used in various types of SQL clients, such as command-line interfaces, web-based management interfaces, and programmi
8 min read
Format SQL in Python with Psycopg's Mogrify
Psycopg, the Python PostgreSQL driver, includes a very useful mechanism for formatting SQL in python, which is mogrify. After parameters binding, returns a query string. The string returned is the same as what SQLwas sent to the database if you used the execute() function or anything similar. One m
2 min read
Python Database Optimization with Psycopg2 and Multiprocessing
This article will provide a succinct overview of the fundamental utilization of the Psycopg2 module and the Multiprocessing module in Python. It will cover how Psycopg2 operates independently and in conjunction with multiprocessing. Psycopg2 and Its Role in Database OperationsAccording to PyPi's doc
6 min read
Python MongoDB - insert_one Query
MongoDB is a cross-platform document-oriented and a non relational (i.e NoSQL) database program. It is an open-source document database, that stores the data in the form of key-value pairs. MongoDB is developed by MongoDB Inc. and was initially released on 11 February 2009. It is written in C++, Go,
3 min read