Database Environment Setup

While Python includes built-in support for SQLite via the sqlite3 module, interacting with a MySQL server requires an external connector like MySQLdb. Before writing code, verify that the database server is operational. The server process is typically named mysqld, whereas mysql refers to the command-line client tool used for administrative tasks.

Connecting and Executing Queries

Database interactions follow a standard pattern: establish a connection, create a cursor object, execute SQL statements, and process results. For data retrieval, methods like fetchone() or fetchall() are used to read the dataset returned by the server.

import MySQLdb

# Establish a connection to the database
try:
    conn = MySQLdb.connect(
        host="localhost",
        user="root",
        passwd="secure_password",
        db="company_db"
    )
    cursor = conn.cursor()

    # Verify connection by checking the version
    cursor.execute("SELECT VERSION()")
    db_version = cursor.fetchone()
    print(f"Connected to MySQL version: {db_version[0]}")

    # Clean up previous data
    cursor.execute("DROP TABLE IF EXISTS StaffMembers")

    # Create a new table structure
    create_table_sql = """
    CREATE TABLE StaffMembers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30),
        age INT,
        gender CHAR(1),
        salary DECIMAL(10, 2)
    )
    """
    cursor.execute(create_table_sql)

    # Insert a new record
    insert_sql = "INSERT INTO StaffMembers(first_name, last_name, age, gender, salary) VALUES (%s, %s, %s, %s, %s)"
    data_tuple = ('Alice', 'Smith', 28, 'F', 4500.00)
    
    try:
        cursor.execute(insert_sql, data_tuple)
        conn.commit()
        print("Record inserted successfully.")
    except MySQLdb.Error as e:
        conn.rollback()
        print(f"Insertion error: {e}")

    # Query the data
    query_sql = "SELECT * FROM StaffMembers WHERE salary > %s"
    cursor.execute(query_sql, (3000,))
    records = cursor.fetchall()

    for row in records:
        print(f"ID: {row[0]}, Name: {row[1]} {row[2]}, Age: {row[3]}")

finally:
    if 'conn' in locals() and conn:
        conn.close()

Transaction Management

Transactions ensure data integrity through the ACID properties: Atomicity, Consistency, Isolation, and Durability. In Python's DB API 2.0, a transaction begins implicitly when a cursor is created. Changes must be explicitly committed to be saved, or rolled back in case of an error.

• Atomicity: Ensures that all operations within a work unit are completed successfully; otherwise, the transaction is aborted.
• Consistency: Guarantees that the database transitions between valid states following a transaction.
• Isolation: Ensures that concurrent transactions do not interfere with one another.
• Durability: Guarantees that once a transaction is committed, it remains saved even in the event of a system failure.

The following example demonstrates handling a deletion operation within a transaction block:

# SQL statement to remove records
delete_sql = "DELETE FROM StaffMembers WHERE age < %s"

try:
    # Execute the command
    cursor.execute(delete_sql, (25,))
    # Save changes permanently
    conn.commit()
    print(f"Deleted {cursor.rowcount} rows.")
except MySQLdb.Error as e:
    # Revert changes on failure
    conn.rollback()
    print(f"Deletion failed, transaction rolled back. Error: {e}")