Of course! Here’s a comprehensive guide on how to commit transactions in Python using the mysql-connector-python library.

The Core Concept: Transactions and commit()

In database management, a transaction is a sequence of operations performed as a single logical unit of work. All operations in a transaction must succeed; if any single operation fails, the entire transaction is rolled back, meaning no changes are saved to the database.

The commit() method is the command that finalizes a transaction. It permanently saves all the changes made since the last commit() or rollback() to the database.

The Essential commit() Method

To use transactions, you must first disable the "autocommit" mode. By default, mysql-connector-python is in autocommit mode, meaning every single INSERT, UPDATE, or DELETE statement is automatically committed.

To take control, you set autocommit=False and then manually call connection.commit() when you are ready to save your changes.

Basic Syntax

import mysql.connector
from mysql.connector import Error
try:
    # Establish the connection with autocommit=False
    connection = mysql.connector.connect(
        host='localhost',
        database='your_database',
        user='your_username',
        password='your_password',
        autocommit=False  # This is crucial!
    )
    if connection.is_connected():
        cursor = connection.cursor()
        # --- Your database operations go here ---
        # For example, inserting a new record
        sql_insert_query = "INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)"
        employee_data = ('John Doe', 'Software Engineer', 90000)
        cursor.execute(sql_insert_query, employee_data)
        print(f"{cursor.rowcount} record inserted into employees table.")
        # --- At the end, commit the transaction ---
        connection.commit()
        print("Transaction committed successfully.")
except Error as e:
    # --- If an error occurs, roll back the transaction ---
    print(f"Error: {e}")
    if connection.is_connected():
        connection.rollback()
        print("Transaction rolled back.")
finally:
    # --- Clean up ---
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

The try...except...finally Pattern (Best Practice)

The example above uses the best practice pattern for database transactions. Let's break down why each part is important.

try Block

This is where you perform your database operations.

• autocommit=False: You must set this on the connection object to enable manual transaction control.
• cursor.execute(): You execute your SQL statements. At this point, the changes are only made in memory, not on the disk. They are "pending."

except Error as e Block

This block catches any exception that occurs during the try block.

• connection.rollback(): This is the safety net. If any SQL statement fails (e.g., you violate a primary key constraint, a syntax error occurs, etc.), the rollback() method is called. It undoes all the pending changes from the start of the transaction, leaving your database in a consistent state.

finally Block

This block always executes, whether an error occurred or not. It's perfect for cleanup.

• cursor.close() and connection.close(): These lines ensure that your database resources are properly released, preventing memory leaks and connection issues.

Committing Multiple Operations

The real power of transactions is ensuring that a series of related operations succeed or fail together.

Scenario: You want to add a new department and then add an employee to that new department. If adding the employee fails, you don't want the new department to exist.

import mysql.connector
from mysql.connector import Error
try:
    connection = mysql.connector.connect(
        host='localhost',
        database='company_db',
        user='your_user',
        password='your_pass',
        autocommit=False
    )
    cursor = connection.cursor()
    # Operation 1: Insert a new department
    dept_sql = "INSERT INTO departments (dept_name) VALUES (%s)"
    dept_data = ('Research & Development',)
    cursor.execute(dept_sql, dept_data)
    print("Department inserted.")
    # Get the ID of the newly inserted department
    dept_id = cursor.lastrowid
    # Operation 2: Insert a new employee into that department
    emp_sql = "INSERT INTO employees (name, dept_id) VALUES (%s, %s)"
    emp_data = ('Jane Smith', dept_id)
    cursor.execute(emp_sql, emp_data)
    print("Employee inserted.")
    # If both operations are successful, commit them together
    connection.commit()
    print("Transaction successfully committed!")
except Error as e:
    print(f"Failed to insert record into table: {e}")
    # Roll back in case of any error
    if connection.is_connected():
        connection.rollback()
        print("Transaction rolled back.")
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

In this example, if the INSERT for the employee fails, the rollback() will undo both the department and the employee insert, maintaining data integrity.

The Alternative: autocommit=True

If you set autocommit=True (or don't set it at all, as it's the default), you don't need to call connection.commit().

Use Case: This is suitable for simple, single-statement operations where you don't need the "all-or-nothing" guarantee of a transaction.

import mysql.connector
# No autocommit=False, so it defaults to True
connection = mysql.connector.connect(
    host='localhost',
    database='your_database',
    user='your_username',
    password='your_password'
)
cursor = connection.cursor()
# This statement is committed automatically
sql = "UPDATE products SET stock = stock - 1 WHERE id = 101"
cursor.execute(sql)
# No need for connection.commit() here!
# The change is already saved.
cursor.close()
connection.close()

Summary Table: commit() vs. autocommit

Final Recommendation: For any non-trivial application, always use autocommit=False and explicitly call commit(). It gives you control, ensures data integrity, and is a standard, safe practice in database programming.