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 packagesimport psycopg2import pandas as pdfrom sqlalchemy import create_engine# establish connectionsdb = 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 = Truecursor = conn1.cursor()# drop table if it already existscursor.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 dataframedata = pd.read_csv("Sales.csv")# converting data to sqldata.to_sql('sales', conn, if_exists='replace')# fetching all rowssql1 = '''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 packagesimport psycopg2try: # 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 packagesimport psycopg2try: # 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’
^
