Interacting with MySQL using Python

MySQL utilizes a client-server architecture. While it provides its own client (mysql.exe), Python applications can act as clients to interact with the MySQL server using librarise like pymysql.

Workflow:

1. Establish a connection (host, port, credentials, database, charset).
2. Construct SQL statements within Python.
3. Execute the statements and retrieve results.
4. Process the data in Python.

Installation:

pip install pymysql

Example: Connecting and Querying

import pymysql

# 1. Establish connection
connection = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1234',
    database='db10',
    charset='utf8mb4',
    autocommit=True
)

# 2. Create a cursor (dictionary cursor returns results as dicts)
with connection:
    cursor = connection.cursor(pymysql.cursors.DictCursor)

    # 3. Define SQL query
    query = "SELECT * FROM employees"

    # 4. Execute and fetch results
    cursor.execute(query)
    results = cursor.fetchall()

    for row in results:
        print(f"ID: {row['id']}, Name: {row['name']}")

Preventing SQL Injection

SQL injection occurs when user input is concatenated directly into SQL queries, allowing malicious SQL to be executed.

Vulnerable Code:

# UNSAFE: Direct string formatting
user_input = input("Enter username: ")
unsafe_sql = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(unsafe_sql)

Safe Code (Parameterized Queries): Always use parameterized queries where the database driver handles escaping.

# SAFE: Using placeholders
username = input("Enter username: ").strip()
password = input("Enter password: ").strip()

# Use %s as a placeholder for pymysql
safe_sql = "SELECT * FROM users WHERE username = %s AND password_hash = %s"
cursor.execute(safe_sql, (username, password))

user_data = cursor.fetchall()
if user_data:
    print("Login successful")
else:
    print("Invalid credentials")

Note: autocommit=True in the connection parameters automatically commits INSERT, UPDATE, and DELETE operations.

Database Views

A view is a virtual table based on the result-set of an SQL statement. It contains no data itself but acts as a stored query.

Creation:

CREATE VIEW active_employees AS
SELECT id, name, department
FROM employees
WHERE status = 'active';

Usage:

SELECT * FROM active_employees;

Characteristics:

• Views only store the query logic (table structure), not the data.
• They are best used for frequent, complex read operations.
• Modifying data through views is generally discouraged.
• Drop a view using DROP VIEW view_name;.

In Practice: Views are often avoided in application development because they tie logic to the database layer, making application scaling and modification more complex.

Triggers

Triggers are automatically executed SQL blocks in response to specific events (INSERT, UPDATE, DELETE) on a table.

Syntax:

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
    -- Use NEW for new values, OLD for old values
END$$
DELIMITER ;

Example: Logging Failed Commands

-- Tables
CREATE TABLE system_commands (
    id INT PRIMARY KEY AUTO_INCREMENT,
    command_text VARCHAR(255),
    executed_at DATETIME,
    status ENUM('success', 'fail')
);

CREATE TABLE error_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    failed_command VARCHAR(255),
    error_time DATETIME
);

-- Trigger
DELIMITER $$
CREATE TRIGGER log_failed_commands
AFTER INSERT ON system_commands
FOR EACH ROW
BEGIN
    IF NEW.status = 'fail' THEN
        INSERT INTO error_logs (failed_command, error_time)
        VALUES (NEW.command_text, NEW.executed_at);
    END IF;
END$$
DELIMITER ;

Transactions (ACID)

Transactions group multiple SQL operations into a single unit of work that either fully succeeds or fully fails.

ACID Properties:

• Atomicity: All operations complete or none do.
• Consistency: Database state changes from one valid state to another.
• Isolation: Concurrent transactions do not interfere.
• Durability: Committed changes are permanent.

Usage in SQL:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If no errors:
COMMIT;
-- If error occurs:
-- ROLLBACK;

Conceptual Python Logic:

try:
    cursor.execute("START TRANSACTION")
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    connection.commit()
except Exception:
    connection.rollback()

Stored Procedures

Stored procedures are pre-compiled collections of SQL statements stored in the database.

Simple Procedure:

DELIMITER $$
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END$$
DELIMITER ;

-- Call it
CALL GetAllUsers();

Procedure with Parameters:

DELIMITER $$
CREATE PROCEDURE UpdateSalary(
    IN emp_id INT,
    IN increase DECIMAL(10,2),
    OUT status_code INT
)
BEGIN
    UPDATE employees SET salary = salary + increase WHERE id = emp_id;
    SET status_code = 0; -- Success
END$$
DELIMITER ;

-- Usage
CALL UpdateSalary(101, 500.00, @result);
SELECT @result;

Development Models:

1. Heavy DB Logic: Application calls stored procedures. (High performance, low flexibility).
2. Manual SQL: Application writes all SQL. (High flexibility, more development work).
3. ORM: Application uses Object-Relational Mapping tools. (High productivity, slightly lower performance).

Built-in Functions

MySQL provides functions for data manipulation within queries.

Example: Date Extraction

SELECT
    post_title,
    DATE_FORMAT(publication_date, '%Y-%m') AS month_year
FROM blog_posts
GROUP BY month_year;

Flow Control in SQL

IF Statement in Procedures:

DELIMITER $$
CREATE PROCEDURE CheckValue(IN val INT)
BEGIN
    IF val > 100 THEN
        SELECT 'High';
    ELSEIF val > 50 THEN
        SELECT 'Medium';
    ELSE
        SELECT 'Low';
    END IF;
END$$
DELIMITER ;

WHILE Loop in Procedures:

DELIMITER $$
CREATE PROCEDURE CountDown(IN start_num INT)
BEGIN
    DECLARE counter INT DEFAULT start_num;
    WHILE counter > 0 DO
        SELECT counter;
        SET counter = counter - 1;
    END WHILE;
END$$
DELIMITER ;

Indexes

Indexes are data structures (like a book's index) that improve the speed of data retrieval operations.

Types:

• PRIMARY KEY: Unique and not null.
• UNIQUE KEY: Unique values.
• INDEX (or KEY): Purely for performance, no constraints.

Impact:

• Reads: Significantly faster.
• Writes: Slightly slower (index must be updated).
• Creation: Slow on large, existing tables.

B+ Tree Structure: Most MySQL indexes use B+ trees. Data is stored in leaf nodes, and the tree depth determines search speed. Integer primary keys are often fastest because they allow for a shallow tree.

Clustered vs. Non-Clustered (Secondary):

• Clustered (Primary Key): Data rows are stored with the key in the leaf nodes (InnoDB).
• Secondary: Leaf nodes store the primary key value, requiring a second lookup (unless covered).

Covering Index: If a query only selects columns that are part of an index, the database can return results with out touching the main table data.

Testing Index Performance:

-- Create a large test table
CREATE TABLE test_data (id INT, info VARCHAR(100));

-- Insert 1,000,000 rows (using a procedure or script)

-- Without index (Slow)
SELECT COUNT(*) FROM test_data WHERE id = 500000;

-- Add Primary Key (Fast)
ALTER TABLE test_data ADD PRIMARY KEY (id);
SELECT COUNT(*) FROM test_data WHERE id = 500000;

Best Practices:

• Index columns used in WHERE, JOIN, and ORDER BY clauses.
• High cardinality (unique values) columns are best (e.g., email, not gender).
• Avoid indexing columns with many NULLs or low variation.
• Be cautious with functions on indexed columns (WHERE id + 1 = 5 prevents index use).
• Use Composite (Joint) Indexes for queries filtering on multiple columns. Order matters (leftmost prefix rule).

Composite Index Example:

CREATE INDEX idx_email_name ON users(email, name);
-- Efficient for: WHERE email = 'x' AND name = 'y'
-- Efficient for: WHERE email = 'x'
-- Inefficient for: WHERE name = 'y' (usually)

Slow Query Log

Enable the slow query log to record SQL statements that take longer than a specified threshold (e.g., 1 second). This is critical for identifying queries that need optimization or indexing.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;