aiomysql

aiomysql is an asyncio-native driver for talking to MySQL and MariaDB from Python. Rather than reinventing the MySQL wire protocol, it reuses PyMySQL’s packet parsing, converters, and authentication code and swaps the blocking socket I/O for asyncio streams, so the querying API, error hierarchy, and cursor semantics feel familiar to anyone who has used PyMySQL directly.

On top of the connection layer it ships a lightweight async connection pool modeled deliberately on aiopg’s pool (both projects share the aio-libs umbrella and API conventions), plus an optional aiomysql.sa subpackage that ports aiopg’s SQLAlchemy Core integration so applications can build queries with SQLAlchemy’s expression language without adopting a full ORM.

It’s a maintenance-mode, API-stable driver rather than a fast-moving project: releases are infrequent, but the library backs a large number of asyncio web frameworks and background-job systems that need a straightforward, well-tested MySQL client without hand-rolling protocol code.

What You Get

• An asyncio-based connect()/Connection implementing the MySQL client-server wire protocol directly over asyncio streams, reusing PyMySQL’s packet, charset, and authentication logic
• create_pool()/Pool, a minimal async connection pool with configurable min/max size and connection recycling, modeled on aiopg’s pool API
• Multiple cursor classes — Cursor, SSCursor (unbuffered/server-side), DictCursor, SSDictCursor, and deserialization cursor mixins — for different result-fetching strategies
• An optional aiomysql.sa package providing SQLAlchemy Core create_engine/engine.acquire() integration, transactions, and result-proxy objects for query building without a full ORM
• PyMySQL’s typed exception hierarchy re-exported directly (OperationalError, IntegrityError, ProgrammingError, etc.) so error handling code ports over unchanged
• SSL/TLS connection support, local-infile loading, and RSA auth (via the PyMySQL[rsa] optional extra) for MySQL 8’s caching_sha2_password

Common Use Cases

• Backing asyncio web frameworks (aiohttp, Sanic-style apps) with a MySQL/MariaDB data layer without blocking the event loop
• Running pooled, concurrent queries from async background workers or task queues that talk to a MySQL database
• Migrating existing PyMySQL-based synchronous code to asyncio while keeping the same cursor/exception API surface
• Building lightweight, ORM-free query layers with SQLAlchemy Core’s expression language via aiomysql.sa
• Bulk data pipelines that stream large result sets through SSCursor without buffering the entire result set in memory

Under The Hood

Architecture aiomysql wraps PyMySQL’s synchronous protocol implementation with asyncio in three layers: connection.py (~1,400 lines) implements the MySQL wire protocol directly against asyncio streams via a custom _StreamReader that exposes EOF state the pool needs; cursors.py layers buffered, unbuffered, and dict-style result handling on top of a connection; and pool.py is a thin async connection pool built on asyncio.Condition, explicitly modeled on aiopg’s pool for API consistency across the aio-libs ecosystem. An optional sa/ subpackage wraps Connection to provide SQLAlchemy Core integration (engine, connection, result, transaction) ported from aiopg. There’s no dependency injection — everything is constructed through connect()/create_pool() factory functions returning context-manager wrappers — and layering is one-directional: pool depends on connection, cursors and sa/ both depend directly on connection internals, so changes to Connection’s internal reader/writer state would ripple into both.

Tech Stack Python 3.9+, with PyMySQL>=1.0 as the only hard dependency (protocol constants, converters, and auth code are reused directly rather than reimplemented) and an optional sqlalchemy>=1.3,<1.4 extra (aiomysql[sa]) for the Core-integration layer, plus a PyMySQL[rsa] extra for RSA-based auth. The package builds with setuptools + setuptools_scm for version derivation, is tested with pytest against real MySQL/MariaDB service containers in CI (not mocks), lints with flake8 (88-col limit) and mypy via pre-commit, measures coverage with coverage.py/Codecov, and publishes docs to Read the Docs via Sphinx.

Code Quality The test suite spans 22 files under tests/ (connection, cursor, pool, SSL, SHA-auth, bulk inserts, sa/ integration, and more), run against live MySQL/MariaDB instances in GitHub Actions rather than mocked sockets, which gives strong confidence in real protocol behavior at the cost of requiring services to run locally. Error handling relies entirely on PyMySQL’s typed exception hierarchy re-exported as-is, with no swallowed exceptions observed. Naming is consistent snake_case with underscore-prefixed private state, and mypy is configured in pre-commit, though much of the core connection.py/pool.py code predates full type-hint coverage and remains largely untyped.

What Makes It Unique aiomysql isn’t attempting to be a novel driver — it’s a deliberate, faithful async port of PyMySQL with the pool API copied from aiopg specifically so aio-libs users get a consistent async database experience across Postgres and MySQL. The value is predictability and API familiarity for teams already using PyMySQL or aiopg, not new protocol or query capabilities.