Performing Transactions

When a person performs transactions in MySQL with Python, it must go through a series of steps to ensure the consistency and integrity of the database. Transactions are an inherent concept of database management systems through which multiple operations can be carried out as a singular unit known as an atomicity; when one of the operations fails, the entire transaction is rolled back, meaning it’s returned to its consistent state.

Steps to Perform Transactions in Python with MySQL

1. Understand Transactions:

• A transaction is a sequence of operations that are performed as a single unit of work.
• Transactions follow the ACID properties:
  • Atomicity: All the operations within a transaction should be completed; otherwise, none of them.
  • Consistency: The database remains in a valid state before and after the transaction.
  • Isolation: Intermediate states of a transaction should not be visible to other transactions.
  • Durability: It ensures that once a transaction is committed, it stays in the database even in case of a system failure.

2. Requirements:

• Install MySQL Connector for Python:

pip install mysql-connector-python

• A MySQL database and a table for the transaction.

3. Enable Transactions:

• Most MySQL databases, by default, use the InnoDB storage engine, supporting transactions.
• Verify the table you are using is on an InnoDB tablespace.

4. Performing a Transaction:

• Use the commit() and rollback() methods of the connection object of the MySQL
• Step in code:
  • Open a connection
  • Start a transaction
  • Execute the SQLs
  • Commit or rollback according to success or failure.

Example Code: Performing Transactions

import mysql.connector
from mysql.connector import Error

try:
    # Connect to the MySQL database
    connection = mysql.connector.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database'
    )
    if connection.is_connected():
        print("Connected to MySQL database")

    # Create a cursor object
    cursor = connection.cursor()

    # Start a transaction
    connection.start_transaction()

    # Perform SQL operations
    cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)")
    cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Bob', 1500)")

    # Commit the transaction
    connection.commit()
    print("Transaction committed successfully")

except Error as e:
    # Rollback the transaction in case of an error
    print("Error occurred, rolling back the transaction:", e)
    if connection.is_connected():
        connection.rollback()

finally:
    # Close the connection
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection closed")

Explanation of the Code

1. Connect to the Database:

• Do connect via mysql.connector.connect.
• Also, replace your_username, your_password, and your_database with your own credentials and actual database name.

2. Start a Transaction:

• You’ve to use connection.start_transaction() to initiate the actual transaction explicitly.

3. Execute SQL Commands:

• You have to execute your SQL commands in that transaction using the cursor.execute() method.

4. Commit or Rollback:

• You need to commit the connection using connection.commit().
• In the event of an error, handle it with the help of the try-except block and roll back with connection.rollback().

5. Close the Connection:

• You have to make sure that whatever connections are opened with the database are closed and cleaned away.

Key Points to Remember

• Always handle errors gracefully using a try-except block.
• Use transactions only when multiple operations have to be executed together.
• Avoid long-running transactions since they lock database resources.
• Ensure that the MySQL table uses a transaction-capable storage engine, such as InnoDB.

Practical Use Cases of Transactions

• Banking Systems: Fund transfers between accounts.
• Order Management: Order where updates to the inventory, customer balance, and order are involved.
• Data Import: Multiple rows are inserted into a table, where either all rows are inserted or none are.