In this tutorial, we will learn how to perform PostgreSQL Insert, Update, Delete operations from Python. It is also known as DML operations. Also, learn how to pass parameters to SQL queries i.e., use Python variables in the PostgreSQL query to insert, update, and delete table data.
In the end, we will see the use of cursor.executemany()
to insert, update, and delete multiple rows using a single query.
Also, Read:
Table of contents
Prerequisites
Before executing the following programs, please make sure you have the following details in place
- Username and password that you need to connect PostgreSQL
- PostgreSQL database table for CRUD operations.
For this article, I am using a ‘mobile’ table created in my PostgreSQL database.
If a table is not present, you can refer to create a PostgreSQL table from Python.

Python PostgreSQL INSERT into database Table
In this section, We learn how to execute INSERT Query from a Python application to insert rows to the PostgreSQL table using Psycopg2.
To perform a SQL INSERT query from Python, you need to follow these simple steps: –
- Install psycopg2 using pip.
- Second, Establish a PostgreSQL database connection in Python.
- Next, Define the Insert query. All you need to know is the table’s column details.
- Execute the INSERT query using
cursor.execute()
. In return, you will get the number of rows affected. - After the successful execution of the query, commit your changes to the database.
- Close the cursor and PostgreSQL database connection.
- Most important, Catch SQL exceptions if any.
- At last, verify the result by selecting data from the PostgreSQL table.
import psycopg2
try:
connection = psycopg2.connect(user="sysadmin",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE) VALUES (%s,%s,%s)"""
record_to_insert = (5, 'One Plus 6', 950)
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
print("Failed to insert record into mobile table", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
Output:
1 Record inserted successfully into mobile table PostgreSQL connection is closed

We used a parameterized query to use Python variables as parameter values at execution time. In the end, we used commit() to make our changes persistent into the database
Using a parameterized query, we can pass python variables as a query parameter using placeholders (%s)
.
Python PostgreSQL UPDATE Table Data
This section will learn how to update a PostgreSQL table’s data from a Python application using Psycopg2.
You will learn how to update a single and rows, single column, and multiple columns of the PostgreSQL table.
To perform a PostgreSQL UPDATE query from Python, you need to follow these steps: –
- Establish a PostgreSQL database connection in Python.
- Define the UPDATE statement query to update the data of the PostgreSQL table.
- Execute the UPDATE query using a
cursor.execute()
- Close the cursor and database connection.
Now, Let see the example to update a single row of the database table.
import psycopg2
def updateTable(mobileId, price):
try:
connection = psycopg2.connect(user="sysadmin",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
print("Table Before updating record ")
sql_select_query = """select * from mobile where id = %s"""
cursor.execute(sql_select_query, (mobileId,))
record = cursor.fetchone()
print(record)
# Update single record now
sql_update_query = """Update mobile set price = %s where id = %s"""
cursor.execute(sql_update_query, (price, mobileId))
connection.commit()
count = cursor.rowcount
print(count, "Record Updated successfully ")
print("Table After updating record ")
sql_select_query = """select * from mobile where id = %s"""
cursor.execute(sql_select_query, (mobileId,))
record = cursor.fetchone()
print(record)
except (Exception, psycopg2.Error) as error:
print("Error in update operation", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
id = 3
price = 970
updateTable(id, price)
Output:
Table Before updating record (3, 'Google Pixel', 700.0) 1 Record Updated successfully Table After updating record (3, 'Google Pixel', 970.0) PostgreSQL connection is closed

Verify the result of the above update operation by Selecting data from the PostgreSQL table using Python.
Python PostgreSQL Delete Table row and columns
This section will learn how to Delete a PostgreSQL table’s data from Python using Psycopg2.
Let see this with an example program. In this Python example, we prepared a query to delete a single row from the PostgreSQL table.
import psycopg2
def deleteData(mobileId):
try:
connection = psycopg2.connect(user="sysadmin",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
# Update single record now
sql_delete_query = """Delete from mobile where id = %s"""
cursor.execute(sql_delete_query, (mobileId,))
connection.commit()
count = cursor.rowcount
print(count, "Record deleted successfully ")
except (Exception, psycopg2.Error) as error:
print("Error in Delete operation", error)
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
id4 = 4
id5 = 5
deleteData(id4)
deleteData(id5)
Output:
1 Record deleted successfully PostgreSQL connection is closed 1 Record deleted successfully PostgreSQL connection is closed

Verify the result of the above delete operation by Selecting data from the PostgreSQL table using Python.
Insert, update and delete multiple rows from the PostgreSQL table using executemany()
Note: Use cursor.executemany()
method to insert, update, delete multiple rows of a table using a single query.
The cursor.executemany()
method executes the database query against all the parameters.
Most of the time, you need to run the same query multiple times but with different data. Like insert each student’s attendance, attendance percentage is different, but the SQL query is the same.
Syntax of executemany()
executemany(query, vars_list)
- Here query can be any SQL query (Insert, update, delete)
- The
vars_list
is nothing but the list of tuples as an input to the query. - Each tuple in this list contains a single row of data to be inserted or updated into a table.
Now, Let see how to use this method.
Python Insert multiple rows into the PostgreSQL table
Use parameterized query and cursor’s executemany()
method to add multiple rows into a table. Using placeholders in the parameterized query, we can pass column values at runtime.
import psycopg2
def bulkInsert(records):
try:
connection = psycopg2.connect(user="sysadmin",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
sql_insert_query = """ INSERT INTO mobile (id, model, price)
VALUES (%s,%s,%s) """
# executemany() to insert multiple rows
result = cursor.executemany(sql_insert_query, records)
connection.commit()
print(cursor.rowcount, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
print("Failed inserting record into mobile table {}".format(error))
finally:
# closing database connection.
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
records_to_insert = [(4, 'LG', 800), (5, 'One Plus 6', 950)]
bulkInsert(records_to_insert)
Output:
2 Record inserted successfully into mobile table PostgreSQL connection is closed

Update multiple rows of PostgreSQL table using a single Query in Python
For example, you want to update the salary of the employees. Now the pay is different for each employee, but the update query remains the same.
We can update multiple rows of a table using a cursor.executemany()
and parameterized query
import psycopg2
def updateInBulk(records):
try:
ps_connection = psycopg2.connect(user="sysadmin",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = ps_connection.cursor()
# Update multiple records
sql_update_query = """Update mobile set price = %s where id = %s"""
cursor.executemany(sql_update_query, records)
ps_connection.commit()
row_count = cursor.rowcount
print(row_count, "Records Updated")
except (Exception, psycopg2.Error) as error:
print("Error while updating PostgreSQL table", error)
finally:
# closing database connection.
if ps_connection:
cursor.close()
ps_connection.close()
print("PostgreSQL connection is closed")
tuples = [(750, 4), (950, 5)]
updateInBulk(tuples)
Output:
2 Records Updated PostgreSQL connection is closed

Note: Use cursor.rowcount
to get the total number of rows affected by the executemany()
method.
Python PostgreSQL Delete multiple rows from a table
In this example, We defined SQL Delete query with a placeholder that contains customer IDs to delete. Also, prepared a list of records to be deleted. This List contains a tuple for each row. Here we created two tuples, to delete two rows.
import psycopg2
def deleteInBulk(records):
try:
ps_connection = psycopg2.connect(user="postgres",
password="vishal@#29",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = ps_connection.cursor()
ps_delete_query = """Delete from mobile where id = %s"""
cursor.executemany(ps_delete_query, records)
ps_connection.commit()
row_count = cursor.rowcount
print(row_count, "Record Deleted")
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if ps_connection:
cursor.close()
ps_connection.close()
print("PostgreSQL connection is closed")
# list of tuples contains database IDs
tuples = [(5,), (4,), (3,)]
deleteInBulk(tuples)
Output:
2 Records Deleted PostgreSQL connection is closed
Next Steps:
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.