In Python, the commit() and rollback() methods play an essential role in database transactions, ensuring atomicity, consistency, isolation, and durability (ACID properties). These methods help manage changes to the database in a reliable way, ensuring data integrity even in case of errors or failures.

In this article, we will focus on the use of commit() and rollback() methods for handling database transactions.

MySQL Table in used in this article:  

1. commit() method

The commit() method is used to save changes made to the database permanently. It confirms that all the changes executed during the current session, such as INSERT, UPDATE, or DELETE queries, should be saved in the database. Without commit(), any changes would be lost once the connection is closed.

Syntax 

comm.commit()

• comm: refers to the database connection object
• commit() method ensures that changes are persistent in the database.

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: Updating a Record and Committing the Changes

The following example demonstrates how to update a student’s age and commit the change to the database:

import mysql.connector

# Connecting to the Database
mydb = mysql.connector.connect(
    host='localhost',
    database='College',
    user='root',
    password='your_password'  # Use your actual password
)

cs = mydb.cursor()

# SQL query to update the age of a student named 'Rishi Kumar'
statement = "UPDATE STUDENT SET AGE = 25 WHERE Name = 'Rishi Kumar'"

cs.execute(statement)

# Commit the changes to make them permanent in the database
mydb.commit()

# Disconnecting from the database
mydb.close()

Output: 

Explanation:

• program updates the AGE field for the student Rishi Kumar.
• commit() method ensures that this update is saved permanently to the College database.

2. rollback() method

The rollback() method is used to undo changes made during the current transaction. 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
• rollback() method discards all changes since the last commit.

Example: Handling Errors with rollback()

The following example demonstrates how to use the rollback() method to revert changes if a database operation fails:

import mysql.connector
from mysql.connector import Error

try:
    # Connecting to the Database
    mydb = mysql.connector.connect(
        host='localhost',
        database='College',
        user='root',
        password='your_password'  # Use your actual password
    )

    cs = mydb.cursor()

    # SQL query to update the age of a student named 'Rishi Kumar'
    statement = "UPDATE STUDENT SET AGE = 25 WHERE Name = 'Rishi Kumar'"

    cs.execute(statement)

    # Commit the changes to make them permanent
    mydb.commit()

    # Success message
    print("Database Updated!")

except mysql.connector.Error as error:
    # Error message if the transaction fails
    print(f"Database Update Failed! Error: {error}")
    
    # Rollback changes if there's an error
    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!