PyMySQL
Pure Python MySQL/MariaDB client implementing DB-API 2.0 — provides synchronous MySQL database connections without C extensions. PyMySQL features: connect() for connections, cursor() for queries, execute()/executemany() with parameterized queries, fetchone()/fetchall()/fetchmany(), DictCursor for dict row results, SSCursor for server-side streaming cursors, autocommit control, transaction management (commit/rollback), connection pooling via DBUtils, charset/collation configuration, SSL/TLS support, and compatibility mode for MySQLdb. Drop-in replacement for MySQLdb where C extension is unavailable.
Score Breakdown
⚙ Agent Friendliness
🔒 Security
ALWAYS use parameterized queries (%s placeholders) — never string format SQL. Use TLS (ssl parameter) for network MySQL connections. Store credentials in environment variables not code. Principle of least privilege: create MySQL user with only needed permissions. PyMySQL pure Python is auditable for security review.
⚡ Reliability
Best When
Pure Python MySQL access without C extension compilation — Docker environments, minimal containers, or when mysqlclient compilation fails; also used as the sync backend for aiomysql.
Avoid When
Async code (use aiomysql), performance-critical apps (use mysqlclient), PostgreSQL (use psycopg2).
Use Cases
- • Agent MySQL query — import pymysql; conn = pymysql.connect(host='localhost', user='agent', password='pass', database='db', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor); with conn.cursor() as cursor: cursor.execute('SELECT * FROM tasks WHERE status=%s', ('pending',)); rows = cursor.fetchall(); conn.close() — parameterized query; agent reads tasks from MySQL; DictCursor returns dicts not tuples
- • Agent write with transaction — with conn.cursor() as cursor: cursor.execute('INSERT INTO events (type, data) VALUES (%s, %s)', (event_type, json.dumps(data))); conn.commit() — explicit commit required; agent writes event with transaction; autocommit=False by default; rollback() on exception
- • Agent bulk insert — with conn.cursor() as cursor: cursor.executemany('INSERT INTO records (key, value) VALUES (%s, %s)', [(k, v) for k, v in items.items()]); conn.commit() — batch insert; agent bulk-loads data via executemany; single round-trip for many rows
- • Agent streaming large results — with conn.cursor(pymysql.cursors.SSCursor) as cursor: cursor.execute('SELECT * FROM large_table'); while (row := cursor.fetchone()): process(row) — server-side cursor; agent processes large result sets without loading all into memory; SSCursor fetches row-by-row from server
- • Agent connection pool — from dbutils.pooled_db import PooledDB; pool = PooledDB(creator=pymysql, maxconnections=10, host='localhost', user='agent', database='db'); conn = pool.connection(); cursor = conn.cursor() — DBUtils connection pool; agent reuses connections in multi-threaded server; PooledDB manages connect/reconnect
Not For
- • Async code — PyMySQL is synchronous only; for async MySQL use aiomysql (which wraps PyMySQL)
- • High-performance production — mysqlclient (C extension) is 3-5x faster than PyMySQL; for performance-critical apps use mysqlclient
- • PostgreSQL/SQLite — PyMySQL is MySQL/MariaDB only; for PostgreSQL use psycopg2, for SQLite use built-in sqlite3
Interface
Authentication
MySQL user/password authentication. SSL/TLS via ssl parameter. No OAuth — MySQL native auth (mysql_native_password, caching_sha2_password). IAM auth for cloud MySQL via token in password field.
Pricing
PyMySQL is MIT licensed. Free for all use.
Agent Metadata
Known Gotchas
- ⚠ autocommit=False by default — PyMySQL does NOT auto-commit; agent code inserting/updating data must call conn.commit(); without commit(), changes are rolled back on connection close; set autocommit=True at connect() for auto-commit per statement (not recommended for multi-statement transactions)
- ⚠ Use %s placeholder not ? or format strings — cursor.execute('SELECT * FROM t WHERE id=%s', (id,)) is correct; cursor.execute(f'SELECT * FROM t WHERE id={id}') is SQL injection; PyMySQL uses %s for all types including integers; the second arg must be a tuple: (value,) not just value
- ⚠ DictCursor must be specified per cursor — pymysql.connect(cursorclass=DictCursor) sets default; individual cursors: conn.cursor(DictCursor) overrides; without DictCursor: fetchall() returns list of tuples not dicts; agent code expecting dict access fails silently with tuple index errors
- ⚠ Connection not thread-safe — single PyMySQL connection must not be shared across threads; agent multi-threaded server must use connection pool (DBUtils.PooledDB) or create connection per thread; sharing connection causes 'Commands out of sync' MySQL errors or data corruption
- ⚠ SSCursor requires complete iteration — server-side cursor (SSCursor) holds server result set; calling cursor.close() before fetching all rows cancels query but next query may fail; agent code using SSCursor must fully iterate or explicitly close cursor; use regular cursor for queries where you want fetchall() or early exit
- ⚠ Reconnect not automatic — if MySQL connection drops (timeout, restart), pymysql.err.OperationalError 2006 'MySQL server has gone away'; agent code must handle reconnect: close old connection and create new one; or use ping(reconnect=True) before queries in long-running agents; connection pools handle this automatically
Alternatives
Full Evaluation Report
Detailed scoring breakdown, competitive positioning, security analysis, and improvement recommendations for PyMySQL.
Scores are editorial opinions as of 2026-03-06.