October 13, 2024

Python Tutorials

Python MySQL Performing Transactions

Transactions in a MySQL database allow you to execute a series of SQL operations as a single unit of work. If any operation within the transaction fails, the entire transaction can be rolled back, ensuring data integrity. The mysql-connector-python library provides the tools to perform transactions in Python. This guide will walk you through the process of connecting to a MySQL database, performing a transaction, and handling errors.

1. Prerequisites

Before you can perform transactions in a MySQL database using Python, ensure that you have the following:

  • MySQL server installed and running on your system.
  • Python installed on your system.
  • The mysql-connector-python library installed. You can install it using pip:
pip install mysql-connector-python

2. Connecting to the MySQL Database

To perform a transaction, you first need to establish a connection to the MySQL database.

2.1. Example: Connecting to MySQL Database

import mysql.connector
from mysql.connector import Error

try:
    # Connect to the MySQL database
    connection = mysql.connector.connect(
        host="localhost",         # Database host (e.g., localhost)
        user="root",              # MySQL username
        password="your_password", # MySQL password
        database="test_db"        # Name of the database to connect to
    )

    if connection.is_connected():
        print("Successfully connected to the database")

except Error as e:
    print(f"Error: {e}")

finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

This script connects to the test_db database on the MySQL server running on localhost. If the connection is successful, a confirmation message is printed, and the connection is closed.

3. Performing a Transaction

A transaction typically involves multiple SQL statements that should either all succeed or all fail. The key SQL statements involved in managing a transaction are START TRANSACTION, COMMIT, and ROLLBACK.

3.1. Example: Performing a Transaction with COMMIT

This example demonstrates how to perform a transaction where two SQL INSERT statements are executed. If both are successful, the changes are committed to the database.

# Re-establish the connection
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="test_db"
)

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

try:
    # Start a new transaction
    connection.start_transaction()

    # SQL queries to insert new records into the 'users' and 'orders' tables
    sql_insert_user = "INSERT INTO users (name, email) VALUES (%s, %s)"
    sql_insert_order = "INSERT INTO orders (user_id, order_date, amount) VALUES (%s, %s, %s)"

    # Execute the first INSERT statement
    cursor.execute(sql_insert_user, ("Alice Smith", "alice@example.com"))

    # Get the ID of the inserted user to use in the orders table
    user_id = cursor.lastrowid

    # Execute the second INSERT statement
    cursor.execute(sql_insert_order, (user_id, "2023-01-01", 100.00))

    # Commit the transaction
    connection.commit()

    print("Transaction committed successfully")

except Error as e:
    print(f"Error: {e}")
    connection.rollback()  # Rollback the transaction if an error occurs

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

In this example, the script starts a transaction, inserts a new user into the users table, and then inserts a related order into the orders table. If both INSERT operations are successful, the transaction is committed using connection.commit(). If an error occurs, the transaction is rolled back using connection.rollback().

3.2. Example: Performing a Transaction with ROLLBACK

This example demonstrates how to roll back a transaction if any operation within the transaction fails.

# Re-establish the connection
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="test_db"
)

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

try:
    # Start a new transaction
    connection.start_transaction()

    # SQL queries to insert new records into the 'users' and 'orders' tables
    sql_insert_user = "INSERT INTO users (name, email) VALUES (%s, %s)"
    sql_insert_order = "INSERT INTO orders (user_id, order_date, amount) VALUES (%s, %s, %s)"

    # Execute the first INSERT statement
    cursor.execute(sql_insert_user, ("Bob Johnson", "bob@example.com"))

    # Get the ID of the inserted user to use in the orders table
    user_id = cursor.lastrowid

    # Simulate an error in the second INSERT statement by inserting into a non-existent table
    cursor.execute("INSERT INTO non_existent_table (user_id, order_date, amount) VALUES (%s, %s, %s)", (user_id, "2023-01-01", 200.00))

    # Commit the transaction (this line will not be reached if an error occurs)
    connection.commit()

    print("Transaction committed successfully")

except Error as e:
    print(f"Error: {e}")
    connection.rollback()  # Rollback the transaction if an error occurs

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

In this example, the script attempts to insert a record into a non-existent table, which triggers an error. When the error occurs, the entire transaction is rolled back using connection.rollback(), ensuring that no partial changes are made to the database.

4. Handling Errors

It’s important to handle potential errors during transactions, such as connection issues or SQL syntax errors. Use try-except blocks to catch and handle these errors gracefully.

4.1. Example: Handling Transaction Errors

import mysql.connector
from mysql.connector import Error

try:
    # Re-establish the connection
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="test_db"
    )

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

    # Start a new transaction
    connection.start_transaction()

    # SQL queries to insert new records
    sql_insert_user = "INSERT INTO users (name, email) VALUES (%s, %s)"
    sql_insert_order = "INSERT INTO orders (user_id, order_date, amount) VALUES (%s, %s, %s)"

    # Execute the INSERT statements
    cursor.execute(sql_insert_user, ("Charlie Brown", "charlie@example.com"))
    user_id = cursor.lastrowid
    cursor.execute(sql_insert_order, (user_id, "2023-01-01", 300.00))

    # Commit the transaction
    connection.commit()

    print("Transaction committed successfully")

except mysql.connector.Error as err:
    print(f"Error: {err}")
    connection.rollback()  # Rollback the transaction if an error occurs

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

This script handles errors that might occur during a transaction. If any of the SQL statements fail, the entire transaction is rolled back, and the error is logged.

You May Like