Setting Up MySQL Connectivity

To interact with MySQL databases from Python 3, the mysqlclient library serves as the standard interface. Ensure your system environment has the necesary development headers before installation:

# Debian/Ubuntu systems
sudo apt-get update
sudo apt-get install python3-dev libmysqlclient-dev

# Install the driver
pip install mysqlclient

You can implement a programmatic check to install the dependency automatically if its missing during execution:

import subprocess
import sys

try:
    import MySQLdb
except ImportError:
    subprocess.check_call([sys.executable, '-m', 'pip', 'install', 'mysqlclient'])
    import MySQLdb

Executing SQL Commands

Standard database interactions follow a consistent pattern: establish a connection, create a cursor, perform operations, handle transactions, and close the session.

import MySQLdb

# Initialize connection
connection = MySQLdb.connect(host='localhost', user='admin', passwd='password', db='inventory', charset='utf8')
cursor = connection.cursor()

# Perform a query
cursor.execute('SELECT VERSION()')
version_info = cursor.fetchone()
print(f'System Version: {version_info[0]}')

connection.close()

Transaction Management

For operations that modify the state of the database (INSERT, UPDATE, DELETE), explicit commits are mandatory to persist changes, while rollbacks should be used to handle exceptions.

query = "INSERT INTO staff (name, department) VALUES ('John Doe', 'Engineering')"
try:
    cursor.execute(query)
    connection.commit()
except MySQLdb.Error as e:
    connection.rollback()
    print(f'Operation failed: {e}')

Retrieving Query Results

The cursor provides several methods to fetch data based on your specific requirements:

• fetchone(): Retrieves the next row of a query result set as a single tuple. Returns None if exhausted.
• fetchall(): Returns all remaining rows as a nested tuple of tuples.
• fetchmany(size): Fetches a specified number of rows.

cursor.execute('SELECT id, name FROM staff')
# Fetching results
row = cursor.fetchone()
while row:
    print(f'User {row[0]}: {row[1]}')
    row = cursor.fetchone()

Accessing Metadata and Last Inserted IDs

To retrieve the primary key generated by the most recent INSERT operation, access the lastrowid property of the cursor. This value must be retrieved prior to calling commit() to ensure accuracy.

cursor.execute('INSERT INTO log (action) VALUES ("login")')
primary_key = cursor.lastrowid
connection.commit()
print(f'Inserted record ID: {primary_key}')

Note that NULL values in MySQL are represented as None within Python objects, allowing for seamless integration with Python's data handling logic.