PyPIDataupdated 06 Aug 2026

PyMySQL

Item: PyMySQL
Rating: 4
Author: Keyoor

PyMySQL is a MySQL and MariaDB client written entirely in Python. It speaks the MySQL client/server protocol over a socket itself, with no C extension and no libmysqlclient to link against, which is why pip install PyMySQL works identically on Linux, macOS, Windows, Alpine, and PyPy with nothing else installed. The interface is standard DB-API 2.0: connect() gives you a Connection, connection.cursor() gives you a Cursor, and you call execute() with %s placeholders and a tuple of parameters, then fetchone(), fetchall(), or iterate the cursor. Swap in DictCursor and rows come back as dicts instead of tuples; swap in SSCursor and rows stream from the server instead of buffering in memory. Because it implements the same DB-API surface as the C-based drivers, SQLAlchemy, Django, and pandas all talk to it through the same code paths they use for anything else. It needs Python 3.9 or later and targets MySQL and MariaDB LTS releases.

PyMySQL · v1.2.0 · 22.7M/wk · ★ 7,844 · pushed 05 Aug 2026 · PyMySQL/PyMySQL · docs · reviewed by Keyoor

Verdict

The safe default for talking to MySQL from Python: nothing to compile, standard DB-API, and it works the same everywhere. Reach for mysqlclient when profiling shows row decoding is your bottleneck, and read the 1.2.0 TLS and ping() changes before you bump the pin.

API stability4/5It implements DB-API 2.0, so the core surface cannot drift much and code from years ago still runs, but 1.2.0 changed TLS defaults, changed ping() to stop reconnecting, and deprecated the db and passwd keywords, and 1.1.3 changed how callproc quotes procedure names in a way that breaks dbname.funcname calls.

Docs3/5The ReadTheDocs site documents connect(), Connection, and the cursor classes accurately and the README has a working example, but there is little guidance on pooling, timeouts, TLS configuration, or migrating between versions, and the changelog is where the important operational details actually live.

Maintenance4/5Repo pushed 5 August 2026 with only 12 open issues (17 counting PRs), and security fixes ship promptly, including CVE-2024-36039 in 1.1.1 and the callproc escaping fix in 1.1.3; feature releases are roughly annual, which suits a driver but means new server features arrive slowly.

Ecosystem5/5About 22.7M weekly downloads and 7.8k stars, it is the driver behind SQLAlchemy's mysql+pymysql dialect and the one most Django and pandas MySQL instructions assume, and aiomysql is built directly on top of its protocol code.

Use it if

You cannot or will not compile a C extension: no build toolchain on the box, an Alpine container without musl dev headers, or a Lambda-style deployment where a manylinux wheel is inconvenient
You run on PyPy, where a pure Python driver is often faster than the CPython C extension it cannot use well
You want a driver you can actually read and debug, because when a connection dies mid-query the stack trace goes into Python source instead of into a compiled object
You need a standard DB-API 2.0 driver for SQLAlchemy or Django and want the mysql+pymysql:// dialect that every tutorial and every hosting guide already documents
You want the boring, well-understood default: this is the driver most MySQL-on-Python instructions assume, so the answers exist

Skip it if

Throughput matters and you can compile. mysqlclient wraps the C library and is meaningfully faster for large result sets, because decoding every row in Python is the cost you pay for portability
Your application is asyncio. PyMySQL is fully synchronous and will block the event loop on every query; aiomysql and asyncmy exist for exactly this reason
You expect connection pooling. There is none, and each connect() opens a fresh socket, so you need SQLAlchemy's pool, DBUtils, or your own
You are sharing connections between threads. The DB-API thread safety level is 1, meaning the module is safe but a Connection is not, so one connection per thread or a pool is mandatory
You are upgrading blindly to 1.2.0. It is a breaking release: TLS is now used by default when the server supports it, any SSL option makes TLS required and raises OperationalError if the server cannot do it, ping() no longer reconnects by default, and the db and passwd keyword arguments now emit DeprecationWarning
You want a fast-moving project. Releases are roughly annual (1.1.1 in May 2024, 1.1.2 in August 2025, then 1.1.3 and 1.2.0 in May 2026); it is stable rather than active, and the 12 open issues (17 counting PRs) reflect a small maintainer team keeping scope tight

Setup reality

pip install PyMySQL installs one pure Python package with no dependencies, which is the entire selling point, and then MySQL 8's default authentication plugin trips people on the first connect. caching_sha2_password and sha256_password both need RSA support, so the real command is pip install 'PyMySQL[rsa]', which pulls in cryptography; MariaDB's ed25519 needs pip install 'PyMySQL[ed25519]' and PyNaCl instead. Without those you get a plugin error that does not say 'install an extra'. Second: autocommit is off, so an INSERT without connection.commit() disappears when the connection closes, and this is the single most common PyMySQL question on the internet. Third: pass charset='utf8mb4' explicitly, because anything less than utf8mb4 cannot store emoji or many CJK characters and you will find out in production. Fourth, new in 1.2.0: TLS is negotiated by default when the server offers it, and setting any SSL option at all now makes TLS mandatory, so a self-hosted server with no certificate configured will start failing with OperationalError after an upgrade that used to be silent. Finally, connect_timeout defaults to 10 seconds but read_timeout and write_timeout default to None, so a stalled server can hang a worker indefinitely until you set them.

Patterns

Connect, query, and clean upconnect-and-query

import pymysql.cursors

connection = pymysql.connect(
    host='localhost',
    user='app',
    password='secret',
    database='shop',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor,
)

with connection:
    with connection.cursor() as cursor:
        cursor.execute('SELECT id, email FROM users WHERE id = %s', (1,))
        row = cursor.fetchone()  # {'id': 1, 'email': '...'}

Use database= and password=, not the older db= and passwd= which emit DeprecationWarning as of 1.2.0. Closing the connection with the with block does not commit; see the transaction pattern.

Pass parameters safelyparameterized-sql

email = request.args['email']

# correct: driver escapes the value
cursor.execute('SELECT * FROM users WHERE email = %s', (email,))

# also correct for named parameters
cursor.execute(
    'SELECT * FROM users WHERE email = %(email)s',
    {'email': email},
)

# WRONG: SQL injection
cursor.execute(f'SELECT * FROM users WHERE email = {email}')

The %s is PyMySQL's placeholder, not Python string formatting, so never use f-strings or the % operator to build SQL. A single-element tuple needs the trailing comma: (email,) not (email).

Actually persist your writescommit-transaction

try:
    with connection.cursor() as cursor:
        cursor.execute(
            'INSERT INTO users (email, password) VALUES (%s, %s)',
            ('webmaster@python.org', 'very-secret'),
        )
        new_id = cursor.lastrowid
    connection.commit()
except Exception:
    connection.rollback()
    raise

autocommit is False by default, so an INSERT without commit() vanishes on close. Pass autocommit=True to connect() only if you have no multi-statement invariants, because it also turns off your ability to roll back.

Insert many rows in one round tripbulk-insert

rows = [
    ('a@example.com', 'hash1'),
    ('b@example.com', 'hash2'),
]

with connection.cursor() as cursor:
    affected = cursor.executemany(
        'INSERT INTO users (email, password) VALUES (%s, %s)',
        rows,
    )
connection.commit()

executemany only batches when the query matches its INSERT ... VALUES pattern; anything else silently loops execute() one row at a time. Batches are also capped by max_stmt_length and by the server's max_allowed_packet, so very wide rows still split into several statements.

Get dicts instead of tuplesdict-rows

import pymysql.cursors

# per connection
conn = pymysql.connect(..., cursorclass=pymysql.cursors.DictCursor)

# or per cursor
with conn.cursor(pymysql.cursors.DictCursor) as cursor:
    cursor.execute('SELECT u.id, o.id FROM users u JOIN orders o ON o.user_id = u.id')
    rows = cursor.fetchall()

DictCursor keys on the column alias, so a join selecting two columns both called id collapses to one key. Alias them in SQL (u.id AS user_id) rather than trying to fix it in Python.

Iterate a huge table without buffering itstream-large-results

import pymysql.cursors

with connection.cursor(pymysql.cursors.SSDictCursor) as cursor:
    cursor.execute('SELECT * FROM events')
    for row in cursor:
        handle(row)

The default Cursor pulls the entire result set into memory before you touch it, which kills a worker on a large table. SSCursor and SSDictCursor stream, but the connection is unusable for other queries until you finish reading, and rowcount is not meaningful until then.

Catch the specific MySQL errorhandle-errors

import pymysql

try:
    cursor.execute('INSERT INTO users (email) VALUES (%s)', (email,))
except pymysql.err.IntegrityError as exc:
    code, message = exc.args
    if code == 1062:
        raise Conflict('email already registered') from exc
    raise
except pymysql.err.OperationalError as exc:
    log.warning('connection problem: %s', exc.args)
    raise

Exceptions carry (errno, message) in .args, so branch on the numeric code rather than matching message text. IntegrityError covers duplicate keys and foreign key failures; OperationalError covers lost connections, timeouts, and TLS negotiation failures.

Connect to a MySQL 8 serverauth-plugins

# caching_sha2_password (the MySQL 8 default) needs the rsa extra:
#   pip install 'PyMySQL[rsa]'
# MariaDB ed25519 needs:
#   pip install 'PyMySQL[ed25519]'

conn = pymysql.connect(
    host='db.internal',
    user='app',
    password='secret',
    database='shop',
    charset='utf8mb4',
)

Without the rsa extra you get an authentication plugin error that does not mention pip. Over a plain TCP connection the server may also refuse to send the public key, in which case pass server_public_key or use TLS.

Control TLS explicitly after 1.2.0tls-connection

# require TLS and verify the server certificate
conn = pymysql.connect(
    host='db.example.com',
    user='app',
    password='secret',
    database='shop',
    ssl_ca='/etc/ssl/certs/ca.pem',
    ssl_verify_cert=True,
    ssl_verify_identity=True,
)

# opt out entirely (local socket, sidecar proxy)
conn = pymysql.connect(..., ssl_disabled=True)

As of 1.2.0 TLS is used automatically whenever the server supports it, and setting any SSL option makes TLS mandatory: a server without TLS then raises OperationalError with CR_SSL_CONNECTION_ERROR. Use ssl_disabled=True for a local unix socket or a proxy that already encrypts.

Stop a dead server from hanging a workertimeouts-and-liveness

conn = pymysql.connect(
    host='db.internal',
    user='app',
    password='secret',
    database='shop',
    connect_timeout=5,
    read_timeout=30,
    write_timeout=30,
)

# check liveness without reconnecting
try:
    conn.ping()
except pymysql.err.Error:
    conn = pymysql.connect(...)  # build a fresh one

read_timeout and write_timeout default to None, so only connect_timeout is set out of the box. As of 1.2.0 ping() no longer reconnects by default and the reconnect argument is deprecated, so long-lived connections need explicit replacement logic or a pool.

Use it behind SQLAlchemy with a real poolsqlalchemy-dialect

from sqlalchemy import create_engine, text

engine = create_engine(
    'mysql+pymysql://app:secret@db.internal/shop?charset=utf8mb4',
    pool_size=5,
    max_overflow=10,
    pool_recycle=280,
    pool_pre_ping=True,
)

with engine.connect() as conn:
    rows = conn.execute(text('SELECT id FROM users WHERE id = :id'), {'id': 1}).all()

PyMySQL has no pooling of its own, so this is the usual production shape. Set pool_recycle below the server's wait_timeout (often 28800, but managed hosts use much less) or you will collect 'MySQL server has gone away' errors.

See the SQL that will actually be sentdebug-generated-sql

with connection.cursor() as cursor:
    sql = cursor.mogrify(
        'SELECT * FROM users WHERE email = %s AND active = %s',
        ('a@example.com', True),
    )
    print(sql)
    cursor.execute('SELECT * FROM users WHERE email = %s AND active = %s',
                   ('a@example.com', True))

mogrify returns the interpolated string without executing it, which is the fastest way to settle an argument about quoting or placeholder count. Log it in development only, since it contains the parameter values.

Alternatives

Package	Registry	Pick it when
mysqlclient	PyPI	You can install a C toolchain or use its wheels and want the fastest DB-API driver for large result sets
aiomysql	PyPI	Your service is asyncio and you need a non-blocking driver; it is built on PyMySQL, so the API and quirks carry over
mysql-connector-python	PyPI	You want Oracle's own driver with a supported C extension option, X DevAPI, and vendor documentation