Environment Preparation

Before executing database commands, install the required connector package in your Python workspace.

pip install pymysql

Establishing a Connection

Database interactions begin with an active session. The following pattern initializes a connection and retrieves server metadata.

import pymysql

db_credentials = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "admin_account",
    "password": "s3cur3_p@ss"
}

session = pymysql.connect(**db_credentials)
print(f"Database Server Version: {session.get_server_info()}")

Once connected, a handler object must be instantiated to route SQL commands and fetch results. It serves as the primary execution bridge. Always release it after use.

query_handler = session.cursor()

If your workflow targets a single schema, bind it during initialization to bypass repeated selection calls.

db_credentials["database"] = "app_schema"
session = pymysql.connect(**db_credentials)

# Dynamic schema switching
session.select_db("app_schema")

Schema Management

Administrative tasks follow a consistent pattern using standard SQL statements executed through the cursor.

Creating a New Database

init_sql = "CREATE DATABASE IF NOT EXISTS inventory_system;"
query_handler.execute(init_sql)

Listing All Existing Databases

list_sql = "SHOW DATABASES;"
query_handler.execute(list_sql)
database_list = query_handler.fetchall()

for db_name in database_list:
    print(db_name)

Selecting and Verifying the Active Schema

session.select_db("inventory_system")
verify_sql = "SELECT DATABASE();"
query_handler.execute(verify_sql)
print(f"Active Schema: {query_handler.fetchone()}")

Removing a Databace

remove_sql = "DROP DATABASE IF EXISTS legacy_data;"
query_handler.execute(remove_sql)

Table Operations

Defining table structures requires careful syntax, particularly when using reserved keywords. Enclosing identifiers in backticks prevents parsing conflicts.

Creating a Table

table_sql = """
CREATE TABLE `staff_records` (
    `staff_id` INT AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(50),
    `role` VARCHAR(30),
    `join_date` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
# Enable automatic commit for DDL statements
session.autocommit(True)
query_handler.execute(table_sql)

Dropping a Table

drop_table_sql = "DROP TABLE IF EXISTS `staff_records`;"
query_handler.execute(drop_table_sql)

Data Retrieval and Modification

Interacting with table rows involves executing DML statements. Read and write operations handle transactions differently.

Fetching Records

Assume a table named student_profiles exists with columns for full_name, age, and enrollment_id.

fetch_sql = "SELECT full_name, age, enrollment_id FROM student_profiles;"
query_handler.execute(fetch_sql)
retrieved_rows = query_handler.fetchall()

for row in retrieved_rows:
    print(row)

Inserting New Entries

Modification queries require explicit transaction management. Failing to commit will discard changes upon connection termination.

insert_sql = "INSERT INTO student_profiles (full_name, age, enrollment_id) VALUES (%s, %s, %s);"
new_entry = ("Elena Martinez", 24, 5042)

query_handler.execute(insert_sql, new_entry)
session.commit()  # Permanently saves the transaction
print("Record successfully persisted.")