Python PostgreSQL - Transaction management using Commit and Rollback

Last Updated : 25 Oct, 2022

In this article, we are going to see how to transaction management using Commit and Rollback.

In pyscopg, the connection class in psycopg is in charge of processing transactions. When you use a cursor object to issue your first SQL statement to the PostgreSQL database, psycopg generates a new transaction. Psycopg executes all following statements in the same transaction from that point forward. Psycopg will stop the transaction if any statement fails. Commit() and rollback() are two methods of the connection class that may be used to stop a transaction. The commit() function is used to permanently commit all changes to the PostgreSQL database. You may also use the rollback() function to undo any modifications you've made.

Commit:

connection.commit()

Rollback:

connection.rollback()

The following is an example of how to handle a transaction in psycopg, the most basic way to handle it.

The table from the database we are working on looks like this:

Click here to view and download the CSV file.

Create the sales table in PostgreSQL and import the CSV file:

Python3

# import packages
import psycopg2
import pandas as pd
from sqlalchemy import create_engine

# establish connections
conn_string = 'postgres://postgres:[email protected]/SuperMart'

db = create_engine(conn_string)
conn = db.connect()
conn1 = psycopg2.connect(
  database="SuperMart", user='postgres', 
  password='pass', host='127.0.0.1', port='5432'
)

conn1.autocommit = True
cursor = conn1.cursor()

# drop table if it already exists
cursor.execute('drop table if exists sales')

sql = '''CREATE TABLE sales(Order_Line int,\
Order_ID char(20),Order_Date Date,Ship_Date Date,\
Ship_Mode char(20) ,Customer_ID char(20),Product_ID char(20),\
Sales decimal,Quantity int,Discount decimal,Profit decimal);'''

cursor.execute(sql)

# import the csv file to create a dataframe
data = pd.read_csv("Sales.csv")

# converting data to sql
data.to_sql('sales', conn, if_exists='replace')

# fetching all rows
sql1 = '''select * from sales;'''
cursor.execute(sql1)
for i in cursor.fetchall():
    print(i)

conn1.commit()
conn1.close()

Example 1: Example of a successful transaction

The code starts with importing packages, establishing a connection to the database and we must make sure that connection.autocommit is false because by default it is True, it commits all changes beforehand and we cannot use rollback() to go back to the previous state. a cursor is created with connection.cursor() method. data is queried and fetched. the sales of a particular order_id are calculated. as the SQL statement doesn't have any error, the transaction is finished successfully.

Python3

# import packages
import psycopg2

try:
    # establish a connection to the database
    connection = psycopg2.connect(
        database="SuperMart", user='postgres',
        password='PASS', host='127.0.0.1', port='5432')
    
    # disable autocommit mode
    connection.autocommit = False
    
    # creating a cursor object
    cursor = connection.cursor()

    # querying data
    query = """select sales from sales where\
    Order_ID = 'CA-2016-152156'"""
    cursor.execute(query)
    record = cursor.fetchall()
    sum = 0
    for i in record:
        sum = sum+i[0]
    print("total sales from the order id CA-2016-152156 is : " + str(sum))

    # committing changes
    connection.commit()
    print("successfully finished the transaction ")

except (Exception, psycopg2.DatabaseError) as error:
    print("Error in transaction, reverting all changes using rollback ", error)
    connection.rollback()

finally:

    # closing database connection.
    if connection:
      
        # closing connections
        cursor.close()
        connection.close()
        print("PostgreSQL database connection is closed")

Output:

total sales from the order id CA-2016-152156 is : 993.9000000000001

successfully finished the transaction

PostgreSQL database connection is closed

Example 2: Example of an unsuccessful transaction

The code is similar to the before one except that the wrong table name is given in the SQL statement, as it's incorrect, all changes are reverted or undone using the rollback() method and the connection is closed.

Python3

# import packages
import psycopg2

try:
    # establish a connection to the database
    connection = psycopg2.connect(database="SuperMart",
                                  user='postgres',
                                  password='sherlockedisi',
                                  host='127.0.0.1',
                                  port='5432')
    # disable autocommit mode
    connection.autocommit = False
    
    # creating a cursor object
    cursor = connection.cursor()

    # querying data
    query = """select sales from sales1 \
    where Order_ID = 'CA-2016-1521591'"""
    cursor.execute(query)
    
    # fetching data
    record = cursor.fetchall()
    sum = 0
    for i in record:
        sum = sum+i[0]
    print("total sales from the order\
    id CA-2016-152159 is : " + str(sum))

    # committing changes
    connection.commit()
    print("successfully finished the transaction ")

except (Exception, psycopg2.DatabaseError) as error:
    print("Error in transaction, reverting all\
    changes using rollback, error is : ", error)
    connection.rollback()

finally:

    # closing database connection.
    if connection:
      
        # closing connections
        cursor.close()
        connection.close()
        print("PostgreSQL database connection is closed")

Output:

Error in transaction, reverting all changes using rollback, error is : relation "sales1" does not exist

LINE 1: select sales from sales1 where order_id = 'CA-2016-1521591'

^