PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Use Commit and Rollback to Manage MySQL Transactions in Python

Use Commit and Rollback to Manage MySQL Transactions in Python

Updated on: March 9, 2021 | 11 Comments

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.

Further Reading:

  • Solve Python MySQL Exercise
  • Read Python MySQL Tutorial (Complete Guide)

Table of contents

  • What is Database Transaction
  • Python MySQL Commit(), rollback() and setAutoCommit() to manage transactions
    • Methods to manage MySQL Database Transactions in Python
    • Python example to manage MySQL transactions using commit and rollback
  • Next Steps:

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 MySQL transaction management using commit and rollback
python MySQL transaction management using commit and rollback

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python MySQL

  • Python MySQL Connection Guide
  • Python MySQL Insert
  • Python MySQL Select
  • Python MySQL Update
  • Python MySQL Delete
  • Call MySQL Stored Procedure
  • Python MySQL Parameterized Query
  • Python MySQL Transactions
  • Python MySQL Connection Pooling
  • Python MySQL BLOB
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com