Python communicates with MySQL through its DB API and driver libraries. For Python 3 environments, PyMySQL is commonly used as the connector.

Installing the Driver

Install PyMySQL via pip:

pip3 install PyMySQL

Alternatively, install from source:

git clone https://github.com/PyMySQL/PyMySQL
cd PyMySQL
python3 setup.py install

Establishing a Connection

Connect to a MySQL instance using connection parameters:

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='testuser',
    password='test123',
    database='TESTDB'
)
cur = conn.cursor()

Defining a Table

Create a table named STAFF if it does not exist:

cur.execute('''
    CREATE TABLE IF NOT EXISTS STAFF (
        GIVEN_NAME VARCHAR(20) NOT NULL,
        SURNAME VARCHAR(20),
        YEARS INT,
        GENDER CHAR(1),
        SALARY DOUBLE
    )
''')
conn.commit()

Inserting Records

Add a new entry with parameterized queries to prevent injection risks:

try:
    cur.execute(
        'INSERT INTO STAFF (GIVEN_NAME, SURNAME, YEARS, GENDER, SALARY) VALUES (%s, %s, %s, %s, %s)',
        ('Alice', 'Smith', 29, 'F', 4800)
    )
    conn.commit()
except Exception as err:
    conn.rollback()
    print('Insert failed:', err)

Retrieving Data

Fetch rows where salary exceeds a threshold:

try:
    cur.execute('SELECT * FROM STAFF WHERE SALARY > %s', (3200,))
    rows = cur.fetchall()
    for r in rows:
        print(r)
except Exception as err:
    print('Query failed:', err)

Modifying Existing Rows

Increase salary for matching surname:

try:
    cur.execute('UPDATE STAFF SET SALARY = SALARY + 150 WHERE SURNAME = %s', ('Smith',))
    conn.commit()
except Exception as err:
    conn.rollback()
    print('Update failed:', err)

Removing Rows

Delete records based on age condition:

try:
    cur.execute('DELETE FROM STAFF WHERE YEARS > %s', (55,))
    conn.commit()
except Exception as err:
    conn.rollback()
    print('Deletion failed:', err)

Transaction Management

Group multiple statements into an atomic operation:

conn.autocommit(False)

try:
    cur.execute('UPDATE STAFF SET SALARY = SALARY - 200 WHERE SURNAME = %s', ('Smith',))
    cur.execute(
        'INSERT INTO STAFF (GIVEN_NAME, SURNAME, YEARS, GENDER, SALARY) VALUES (%s, %s, %s, %s, %s)',
        ('Tom', 'Brown', 35, 'M', 5200)
    )
    conn.commit()
except Exception as err:
    conn.rollback()
    print('Transaction error:', err)

Handling Runtime Errors

Wrap database actions in expection hendling blocks to manage connectivity issues, syntax errors, or constraint violations gracefully.