Bulk update of rows in Postgres DB using psycopg2

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 the required psycopg2 library
import  psycopg2

#Method to create a connection object to the database.
#It creates a pointer cursor to the database and returns it along with  Connection object
def create_connection():
      #Connect to the Postgresql database using the psycopg2 adapter.
    #Pass your database name , username , password , hostname and port number
    conn = psycopg2.connect("dbname='EmployeeDB' user='postgres' password='admin' host='localhost' port='5432'")
    #Get the cursor object from the connection object
    curr = conn.cursor()
    return conn,curr


# Method to create a table using the create query.
def create_table():
    try:
        # Get the cursor object from the connection object
        conn, curr = create_connection()
        try:
            # Fire the CREATE query
            curr.execute("CREATE TABLE IF NOT EXISTS employee(employeeName TEXT, departmentName TEXT, couponPoints REAL)")
        except(Exception, psycopg2.Error) as error:
            # Print exception
            print("Error while creating PostgreSQL table", error)
        finally:
            # Close the connection object
            conn.commit()
            conn.close()
    finally:
        #Since we do not have to do anything in this finally block we will pass
        pass


# Insert data in the employee table using the INSERT query
def insert_values(employeeName, departmentName, couponPoints):
    try:
        # Get the cursor object from the connection object
        conn, curr = create_connection()
        try:
            # Fire the INSERT query
            curr.execute("INSERT INTO employee(employeeName,departmentName,couponPoints) values(%s,%s,%s)",
                     (employeeName, departmentName, couponPoints))
        except(Exception, psycopg2.Error) as error:
            # Print exception
            print("Error while inserting PostgreSQL table", error)
        finally:
            # Close the connection object
            conn.commit()
            conn.close()
    finally:
      #Since we do not have to do anything in this finally block we will pass
        pass

#Call the create table method
create_table()
#Insert data by calling insert method. One example is shown below.
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 the required psycopg2 library
import  psycopg2

#Method to create a connection object to the database.
#It creates a pointer cursor to the database and returns it along with  Connection object
def create_connection():
      #Connect to the Postgresql database using the psycopg2 adapter.
    #Pass your database name , username , password , hostname and port number
    conn = psycopg2.connect("dbname='EmployeeDB' user='postgres' password='admin' host='localhost' port='5432'")
    #Get the cursor object from the connection object
    curr = conn.cursor()
    return conn,curr


# Method returns a list of all records present in the Employee table.
def select_values():
    try:
        # Call the create_connection method to get Connection and Cursor objects.
        conn, curr = create_connection()
        try:
            # Use the cursor object to pass the wrapped SQL query
            curr.execute("SELECT * FROM employee")
            # The fetchall method will get all table rows in a list of tuples
            rows = curr.fetchall()
            # Display the table data
            print(rows)
        except(Exception, psycopg2.Error) as error:
            # Print the exception in case of any error
            print("Error while selecting Employees from table", error)
        finally:
           # Close the connection object
            conn.close()
    finally:
        #Since we do not have to do anything in this finally block we will pass
        pass

# Method used to update the Coupon points based on Department Name.
def updateDeptPoints(deptPoints):
    try:
        # Call the create_connection method to get Connection and Cursor objects.
        conn, curr = create_connection()
        try:
            # Prepare the update query as per the columns required
            deptpoints_update_query = """Update employee set couponPoints = couponPoints * %s where  departmentName = %s"""
            # Pass the new values and update query to the executemany() method of Cursor
            curr.executemany(deptpoints_update_query, deptPoints)
        except(Exception, psycopg2.Error) as error:
            # Print Exception in case of error
            print("Error while updating PostgreSQL table", error)
        finally:
            # Close the connection object
            conn.commit()
            conn.close()
    finally:
        #Since we do not have to do anything in this finally block we will pass
           pass

#Prepare, a list, of tuples, of values to be updated
#Since we have to update according to the department we will pass
#the tuple as (correction_value_coupon, department_name)
#Here HR department values are to be increased by 2 and Finance by 3
tuple_dept = [(2,'HR'),(3,'Finance')]
#Call the update method by passing list of updates
updateDeptPoints(tuple_dept)
#Call the select method to display all updated records
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.