Use Commit and Rollback to Manage MySQL Transactions in Python

This lesson mainly focuses on how to manage Database transactions while working with the MySQL database in Python. Learn Python MySQL transaction management using commit and rollback using ‘Mysql connector python’ module.

What is Database Transaction

The database transaction represents a single unit of work. Any operation which modifies the state of the MySQL database is a transaction. Let see in detail what is database transaction. For example, take a sample of a Bank amount transfer, which involves two significant transactions.

• Withdrawal of money from account A
• Deposit Money to Account B

If the first Transaction is executed successfully but the second failed, in this case, we need to re-deposit money back to account A. To manage such instances, we need transaction management.

Using ACID properties, we can study transaction management well. ACID stands for Atomicity, Consistency, isolation, and durability.

• Atomicity: means all or nothing. Either all transactions are successful or none. You can group SQL statements as one logical unit, and if any query fails, the whole transaction fails.
• Consistency: It ensures that the database remains in a consistent state after performing a transaction.
• Isolation: It ensures that the transaction is isolated from other transactions.
• Durability: It means once a transaction has been committed, it persists in the database irrespective of power loss, error, or restart system.

Python MySQL Commit(), rollback() and setAutoCommit() to manage transactions

Please follow the below steps to manage MySQL transactions in Python: –

• Create MySQL database connections in Python.
• Prepare the SQL queries that you want to run as a part of a transaction. For example, we can combine two SQL queries(withdrawal money and deposit money query) in a single transaction.
• Set an auto-commit property of MySQL connection to false.
• Execute all queries one by one using the cursor.execute()
• If all queries execute successfully, commit the changes to the database
• If one of the queries failed to execute, then rollback all the changes.
• Catch any SQL exceptions that may occur during this process
• Close the cursor object and MySQL database connection

Methods to manage MySQL Database Transactions in Python

Python MySQL Connector provides the following method to manage database transactions.

• commit(): MySQLConnection.commit() method sends a COMMIT statement to the MySQL server, committing the current transaction. After the successful execution of a query make changes persistent into a database using the commit() of a connection class.
• rollback(): MySQLConnection.rollback revert the changes made by the current transaction. When one of the transactions fails to execute, and you want to revert or undo all your changes, call a rollback method of MySQL connection object.
• autoCommit() : MySQLConnection.autocommit value can be as True or False to enable or disable the auto-commit feature of MySQL. By default its value is False.

Python example to manage MySQL transactions using commit and rollback

import mysql.connector

try:
    conn = mysql.connector.connect(host='localhost',
                                   database='python_db',
                                   user='pynative',
                                   password='pynative@#29')

    conn.autocommit = False
    cursor = conn.cursor()
    # withdraw from account A 
    sql_update_query = """Update account_A set balance = 1000 where id = 1"""
    cursor.execute(sql_update_query)

    # Deposit to account B 
    sql_update_query = """Update account_B set balance = 1500 where id = 2"""
    cursor.execute(sql_update_query)
    print("Record Updated successfully ")

    # Commit your changes
    conn.commit()

except mysql.connector.Error as error:
    print("Failed to update record to database rollback: {}".format(error))
    # reverting changes because of exception
    conn.rollback()
finally:
    # closing database connection.
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("connection is closed")

Output if the query executes successfully.

Record Updated successfully

You should get the following output if a query fails to execute.

Failed to update record to database rollback

Let’s understand the above code: –  

• We imported the MySQL connector python module so we can use its API to communicate with MySQL Database.
• After a successful MySQL connection, we set auto-commit to False, i.e., we need to commit the transaction only when both the transactions complete successfully.
• We prepared two update SQL queries as a part of a single transaction to deposit money to account B from account A.
• We executed both the queries one by one using a cursor.execute() method.
• After successful execution of both the queries, we committed our changes to the database using a conn.commit().
• In case of an exception or failure of one of the queries, we can revert our changes using a conn.rollback().
• We placed all our code in the try-except block to catch the database exceptions that may occur during the process.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.