Commit & RollBack Operation in Python

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() method


import 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 database
mydb.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 Error
from mysql.connector import errorcode

try:
    # 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!