Python’s commit() method and rollback() method are among the various methods used for making database transactions. Database transactions are necessary as they ensure the atomicity, consistency, isolation and durability of the database.
In this article, we will focus on the use of commit() and rollback() method in detail.
1. The commit() method:
The commit() method is used to make sure the changes made to the database are consistent. It basically provides the database confirmation regarding the changes made by a user or an application in the database.
Syntax:
comm.commit() #comm refers to the database connection object
For a better understanding of the concept look into the code below followed by the code explanation. The below demonstration of the commit() method is performed on a MySQL database.
Example:
Program to update the age of a student named Rishi Kumar and commit it to the database.
MySQL Table in use:
Python3
# Python program to demonstrate # commit() methodimport mysql.connector # Connecting to the Database mydb = mysql.connector.connect( host ='localhost', database ='College', user ='root', ) cs = mydb.cursor() # drop clause statement ="UPDATE STUDENT SET AGE = 23 WHERE Name ='Rishi Kumar'"cs.execute(statement) # commit changes to the databasemydb.commit() # Disconnecting from the database mydb.close() |
Output:
2. The rollback() method:
The rollback() method is used to revert the last changes made to the database. If a condition arises where one is not satisfied with the changes made to the database or a database transaction fails, the rollback() method can be used to retrieve the original data that was changed through the commit() method.
Syntax:
comm.rollback() #comm refers to the database connection object
The below code shows the use of rollback() method to revert changes if a database transaction fails:
Python3
# Python program to demonstrate # rollback() method import mysql.connector from mysql.connector import Errorfrom mysql.connector import errorcodetry: # Connecting to the Database mydb = mysql.connector.connect( host ='localhost', database ='College', user ='root', ) cs = mydb.cursor() # drop clause statement ="UPDATE STUDENT SET AGE = 23 WHERE Name ='Rishi Kumar'" cs.execute(statement) # commit changes to the database mydb.commit() # update successful message print("Database Updated !") except mysql.connector.Error as error : # update failed message as an error print("Database Update Failed !: {}".format(error)) # reverting changes because of exception mydb.rollback() # Disconnecting from the database mydb.close() |
Output:
If the database transaction is successful the output will be,
Database Updated!
If the database transaction fails the output is an error raised as,
Database Update Failed!
