What is the rollback() method in Python MySQL?

The rollback() method is one among the various methods in Python which is used to make the database transactions.

Here, we will discuss about the rollback() method.

The rollback() method is used to revert the last change or commit made to the database. If a condition arises such that the user is not satisfied with the changes made to the database, or if the transaction fails, then the rollback() method is operated to bring the database to its original state which was before committing the changes. This is a very important method as it helps in maintaing the integrity of the database in case of any transaction failure.

Syntax

db.rollback()

db refers to the database connection object.

Given below is an example to show the use of rollback() to revert the changes in case of transaction failure.

Steps to rollback() the failed transaction in a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• try to execute the update query and commit the changes

• If the transaction fails, then rollback the traansaction

• close the connection

Example

The below code tries to update the AGE of a student named Inder in the STUDENT table. If the transaction is successful, the updation is made else the transaction is rolled back and the database is restored to its prior state.

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode

try:
   db = mysql.connector.connect(
      host ='localhost',
      database ='database_name',
      user ='user_name'
      password='your_password',
   )

   cs = db.cursor()
   query ="UPDATE STUDENT SET AGE = 23 WHERE Name ='Inder'"
   cs.execute(query)

   # commit changes to the database
   db.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
   db.rollback()

# Disconnecting from the database
db.close()

Output

If the transaction is succesfull

Database Updated!

If the transaction fails

Database Update Failed!