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'
^
Similar Reads
Transactions management in PostgreSQL Python Psycopg is a PostgreSQL database adapter package for Python. It is a medium to communicate with PostgreSQL databases from Python applications. Transactions are a very essential feature of any database management system, including PostgreSQL. Psycopg helps with transactions, which allows to execution
9 min read
Using Atomic Transactions to Power an Idempotent API In the world of software development, building reliable and efficient APIs is essential for seamless communication between different systems. One critical aspect of API design is ensuring that operations are idempotent, meaning that performing the same operation multiple times has the same result as
6 min read
Python | Database management in PostgreSQL PostgreSQL is an open source object-relational database management system. It is well known for its reliability, robustness, and performance. PostgreSQL has a variety of libraries of API (Application programmable interface) that are available for a variety of popular programming languages such as Py
6 min read
How to Define an Auto Increment Primary Key in PostgreSQL using Python? Prerequisite: PostgreSQL Python has various database drivers for PostgreSQL. Currently, most used version is psycopg2 because it fully implements the Python DB-API 2.0 specification. Â The psycopg2 provides many useful features such as client-side and server-side cursors, asynchronous notification an
3 min read
Programmatic Transaction Management in Spring Programmatic Transaction Management in Spring provides a more flexible and customizable approach compared to declarative transaction management. Instead of using annotations or XML configurations, programmatic transaction management involves managing transactions explicitly in the code. This approac
6 min read
Dynamically Update Multiple Rows with PostgreSQL and Python In this tutorial, we will explore how to dynamically update multiple rows in a PostgreSQL database using Python. By the end of this tutorial, you'll have a solid understanding of how to write efficient Python code that interacts with PostgreSQL to update multiple rows in a database. We'll cover topi
3 min read