Bulk update of rows in Postgres DB using psycopg2
Last Updated :
04 Dec, 2022
PostgreSQL or Postgres is an open-source, relational, SQL complaint, database management system. It allows, the developers, to create complex applications, supporting SQL and JSON querying. It safely stores, and, scales workloads.
Psycopg2 is a driver, that is used, for interacting, with Postgres data, using the Python scripting language. It is, used to perform, CRUD operations on Postgres data.
In this article, we will learn, to update many records, present in Postgres table, using the Python script. Let us understand, the steps involved here –
1) To perform CRUD operations, on Postgres data, using Python code, we need the psycopg2 library. Hence, install it, using the command – ‘pip install psycopg2’ at the terminal.
2) In the example, we will refer, to the Employee database, having an Employee table. The Employee table, has the fields, Employee Name, Department name, and Coupon Points.
3) The psycopg2 adapter, has ‘connect()’ method, that allows connection to the database, bypassing the Database Name, username, password, and hostname as parameters. It returns a valid connection object, on a successful connection.
4) Using the connection object, we can create a cursor object, that acts as a pointer to the database. We can use, the cursor object, to fire SQL queries, on the table data. Using it, we will Create and Insert a few records, in the table. The code for the same is as mentioned below –
Python3
import psycopg2
def create_connection():
conn = psycopg2.connect( "dbname='EmployeeDB' user='postgres' password='admin' host='localhost' port='5432'" )
curr = conn.cursor()
return conn,curr
def create_table():
try :
conn, curr = create_connection()
try :
curr.execute( "CREATE TABLE IF NOT EXISTS employee(employeeName TEXT, departmentName TEXT, couponPoints REAL)" )
except (Exception, psycopg2.Error) as error:
print ( "Error while creating PostgreSQL table" , error)
finally :
conn.commit()
conn.close()
finally :
pass
def insert_values(employeeName, departmentName, couponPoints):
try :
conn, curr = create_connection()
try :
curr.execute( "INSERT INTO employee(employeeName,departmentName,couponPoints) values(%s,%s,%s)" ,
(employeeName, departmentName, couponPoints))
except (Exception, psycopg2.Error) as error:
print ( "Error while inserting PostgreSQL table" , error)
finally :
conn.commit()
conn.close()
finally :
pass
create_table()
insert_values( "Rohan Shetty" , "HR" , "250" )
|
To view the Postgres data, we can use the PgAdmin4 tool. The table data, looks like this, as shown below –
The initial Employee table data as seen using PgAdmin4 tool
5) Now, suppose a requirement comes, wherein, we need to update the coupon points value, of all the employees, according to the Department, in which they are present. Let’s assume. the organization decides, to increase the coupon points, of all HR department employees, by 2, Finance department employees, by 3.
6) Here, we can bulk update the points data, based on the department, in which the Employee, is present. The executemany() method, is a useful method, for executing, a database query, bypassing mappings, in the tuple structure.
7) The executemany() method, will take a list of tuples, and, modify the values, which will be reflected, across all employees. The code for the same will look as below –
Python3
import psycopg2
def create_connection():
conn = psycopg2.connect( "dbname='EmployeeDB' user='postgres' password='admin' host='localhost' port='5432'" )
curr = conn.cursor()
return conn,curr
def select_values():
try :
conn, curr = create_connection()
try :
curr.execute( "SELECT * FROM employee" )
rows = curr.fetchall()
print (rows)
except (Exception, psycopg2.Error) as error:
print ( "Error while selecting Employees from table" , error)
finally :
conn.close()
finally :
pass
def updateDeptPoints(deptPoints):
try :
conn, curr = create_connection()
try :
deptpoints_update_query =
curr.executemany(deptpoints_update_query, deptPoints)
except (Exception, psycopg2.Error) as error:
print ( "Error while updating PostgreSQL table" , error)
finally :
conn.commit()
conn.close()
finally :
pass
tuple_dept = [( 2 , 'HR' ),( 3 , 'Finance' )]
updateDeptPoints(tuple_dept)
select_values()
|
The output of the same in PyCharm is as shown below –
Output in PyCharm after Bulk update
8) Instead of updating records individually, we have done the operation using a single statement. After updating the records the Employee data, in PgAdmin4, looks as shown below –
Employee data after using executemany() method for updating coupon points
Conclusion: The executemany() method can also be used to insert bulk data in the table. Thus, we have studied how to update data in bulk.
Share your thoughts in the comments
Please Login to comment...