Using PyMySQL to Connect and Operate MySQL Databases in Python

In the Python ecosystem, pymysql is a widely used pure‑Python MySQL client library that implements the DB‑API v2.0, offering functions for connecting to MySQL, executing queries, and managing transactions.

1. Introduction to PyMySQL

pymysql provides a flexible interface for performing CRUD operations, transaction control, and other database tasks directly from Python code.

2. Basic Usage

Installation : Use pip to install the library.

pip install pymysql

Establishing a Connection : Call pymysql.connect() with host, port, user, password, and database parameters.

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

Creating a Cursor : Use conn.cursor() to obtain a cursor object.

cursor = conn.cursor()

Executing SQL Statements : Use cursor.execute() and fetch results with fetchone() , fetchall() , etc.

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

Committing Transactions : Call conn.commit() after INSERT/UPDATE/DELETE operations.

conn.commit()

Closing Resources : Close cursor and connection explicitly or use a with statement.

cursor.close()
conn.close()

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

3. Advanced Features

Parameterized Queries : Use %s placeholders to prevent SQL injection.

Transaction Management : Commit with conn.commit() or rollback with conn.rollback() ; enable autocommit via autocommit=True .

Batch Operations : Use cursor.executemany() for bulk inserts.

data = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]
cursor.executemany("INSERT INTO users (name, age) VALUES (%s, %s)", data)
conn.commit()

Setting Charset and Timezone : Specify charset and init_command in the connection.

conn = pymysql.connect(host='localhost', port=3306, user='root', password='password', database='testdb', charset='utf8mb4', init_command='SET time_zone = "+08:00"')

Error Handling : Wrap database operations in try‑except blocks to catch pymysql.MySQLError .

try:
    # execute database operations
except pymysql.MySQLError as e:
    print("Error occurred:", e)

Connection Pooling : PyMySQL itself lacks pooling; integrate with third‑party libraries such as DBUtils for reusable connections.

SQL Injection Protection : Besides parameterized queries, limit user input, use stored procedures, and keep dependencies up‑to‑date.

Performance Optimization : Design proper indexes, optimize queries, use pagination or batch processing, and consider caching.

Logging and Monitoring : Employ Python’s logging module or third‑party tools like loguru , and monitor metrics with Prometheus, Grafana, etc.

4. Conclusion

PyMySQL is a powerful, easy‑to‑use MySQL client for Python, offering a rich set of features that enable developers to perform reliable database operations and adopt best practices for security, performance, and maintainability.