Mastering PyMySQL: Complete Guide to Python‑MySQL Integration

1. Introduction to PyMySQL

PyMySQL is a pure‑Python MySQL client library that implements the Python DB‑API v2.0, allowing developers to connect to MySQL, execute queries, and manage transactions.

2. Basic Usage

Install PyMySQL : pip install pymysql Create a connection :

import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root', password='password', database='testdb')

Create a cursor : cursor = conn.cursor() Execute SQL :

cursor.execute("SELECT * FROM users WHERE age > %s", (25,))
rows = cursor.fetchall()
for row in rows:
    print(row)

Commit transactions : conn.commit() Close resources :

cursor.close()
conn.close()

Using context manager :

with pymysql.connect(host='localhost', port=3306, user='root', password='password', database='testdb') as conn:
    with conn.cursor() as cursor:
        # perform operations
        pass

3. Advanced Features

Parameterized queries : use %s placeholders to prevent SQL injection.

Transaction management : conn.commit(), conn.rollback(), or enable autocommit=True.

Batch operations with cursor.executemany() for bulk inserts.

Charset and timezone settings in the connection: charset='utf8mb4', init_command='SET time_zone = "+08:00"'.

Error handling using try‑except blocks catching pymysql.MySQLError.

Connection pooling : integrate third‑party libraries such as DBUtils.

SQL injection protection and other security best practices.

Performance optimization : proper indexing, query tuning, pagination, caching.

Logging and monitoring : use Python’s logging module or tools like Loguru, Prometheus, Grafana.

4. Conclusion

PyMySQL offers a powerful, easy‑to‑use interface for Python developers to work with MySQL databases, supporting a wide range of features from basic CRUD to advanced transaction and performance optimizations.