What is the rollback() method in Python MySQL?

The rollback() method in Python MySQL is used to revert database changes when a transaction fails. It restores the database to its previous state before any modifications were made, ensuring data integrity during failed operations.

When working with database transactions, you may encounter situations where operations fail due to errors, constraints, or unexpected conditions. The rollback() method provides a safety mechanism to undo any changes and maintain database consistency.

Syntax

db.rollback()

Where db refers to the database connection object created using mysql.connector.connect().

How rollback() Works

The rollback process follows these steps:

• Import MySQL connector and establish database connection

• Create a cursor object for executing SQL commands

• Execute database operations within a try-except block

• If successful, commit the changes using commit()

• If an error occurs, use rollback() to revert changes

• Close the database connection

Example

Here's a complete example that demonstrates using rollback() to handle failed transactions when updating student data ?

import mysql.connector
from mysql.connector import Error

try:
    # Establish database connection
    db = mysql.connector.connect(
        host='localhost',
        database='school_db',
        user='your_username',
        password='your_password'
    )
    
    cursor = db.cursor()
    
    # Execute update query
    query = "UPDATE STUDENT SET AGE = 23 WHERE Name = 'Inder'"
    cursor.execute(query)
    
    # Commit changes to database
    db.commit()
    print("Database Updated Successfully!")
    
except mysql.connector.Error as error:
    # Handle any database errors
    print(f"Database Update Failed: {error}")
    
    # Rollback changes to restore previous state
    if db.is_connected():
        db.rollback()
        print("Transaction rolled back due to error")

finally:
    # Clean up resources
    if db.is_connected():
        cursor.close()
        db.close()
        print("Database connection closed")

Output

If the transaction is successful ?

Database Updated Successfully!
Database connection closed

If the transaction fails ?

Database Update Failed: 1146 (42S02): Table 'school_db.STUDENT' doesn't exist
Transaction rolled back due to error
Database connection closed

Key Benefits

• Data Integrity: Prevents partial updates that could corrupt data

• Error Recovery: Automatically restores database to consistent state

• Transaction Safety: Ensures all-or-nothing execution of related operations

Conclusion

The rollback() method is essential for maintaining database integrity during transaction failures. Always use it within exception handling blocks to ensure your database remains in a consistent state when operations fail.