Python DB Drivers Cheat Sheet

1Install & connectasyncio

pip install asyncpg★
Async-only PostgreSQL driver (no sync API). Python 3.9+, PostgreSQL 9.5–18. Everything is awaited inside an event loop.
import asyncpg, asyncio conn = await asyncpg.connect("postgresql://user:pw@host/db")★
Connect with a DSN (or host=/user=/database= kwargs). Must run inside async def / asyncio.run(...).
await conn.close()★
Close when done. In practice you'll use a pool (card 3) rather than raw connections.
# asyncpg has NO sync API — you must use asyncionote
Every call is a coroutine. To call from sync code, wrap in asyncio.run(). For a sync Postgres driver, use psycopg instead.

2Query & params$1, $2 …

rows = await conn.fetch( "SELECT id, name FROM t WHERE age > $1", 21)★
fetch returns a list of Record objects. ⚠️ Placeholders are $1, $2, … (positional), passed as extra args — NOT %s.
r = await conn.fetchrow("SELECT * FROM t WHERE id=$1", 5) r["name"] · r[0] · dict(r)★
fetchrow = one row (or None). Record supports index and key access; dict(r) to convert.
val = await conn.fetchval("SELECT count(*) FROM t")★
fetchval = a single scalar from the first row/column.
await conn.execute("INSERT INTO t(name) VALUES($1)", "amy")★
execute runs a statement with no result set (returns the status string). asyncpg auto-uses prepared statements & binary encoding under the hood.

3Connection poolsthe prod pattern

pool = await asyncpg.create_pool(dsn, min_size=5, max_size=20)★
Create a pool once at app startup — reusing connections is far cheaper than connecting per request (essential in web apps).
async with pool.acquire() as conn: await conn.fetch("SELECT ...")★
Borrow a connection for the block; it returns to the pool automatically. This is the everyday pattern in FastAPI/Starlette handlers.
await pool.execute("INSERT ... VALUES($1)", x) await pool.fetch("SELECT ...")
The pool proxies execute/fetch — acquire+run+release in one call for simple queries.
await pool.close() # graceful shutdown
Close the pool on app shutdown to drain connections cleanly.

4Transactions, COPY & typespower features

async with conn.transaction(): await conn.execute("UPDATE ... ") await conn.execute("INSERT ...")★
The transaction() context commits on success, rolls back on exception. Nest for savepoints.
await conn.executemany( "INSERT INTO t(a,b) VALUES($1,$2)", [(1,2),(3,4)])★
executemany for batched writes.
await conn.copy_records_to_table("t", records=rows, columns=["a","b"])★
COPY is the fastest bulk-load path (orders of magnitude over inserts). Also copy_to_table (from a file) and copy_from_query.
# JSON/arrays/composites auto-decode to Python; register custom codecs
asyncpg decodes PG types to native Python (arrays→list, json→dict via set_type_codec). conn.cursor() for server-side streaming of huge results.

5Install & connectDB-API 2.0

pip install PyMySQL★
Pure-Python MySQL/MariaDB client — no C build needed. Import is import pymysql. (Latest fixes CVE-2024-36039; upgrade.)
import pymysql conn = pymysql.connect(host="localhost", user="u", password="pw", database="db", charset="utf8mb4")★
Synchronous connect. Use charset="utf8mb4" for full Unicode/emoji. autocommit=True to skip manual commits.
with pymysql.connect(...) as conn: with conn.cursor() as cur: cur.execute("SELECT 1")★
Both connection and cursor are context managers. Closing the connection context commits (or rolls back on error) & closes.
conn.ping(reconnect=True)
MySQL drops idle connections; ping(reconnect=True) revives one (or use a pool / SQLAlchemy for real apps).

6Cursor & execute%s params

cur = conn.cursor() cur.execute("SELECT * FROM t WHERE age > %s", (21,))★
⚠️ Placeholders are %s for every type (not %d), and values go in a tuple/list as the second arg — the driver quotes them safely.
cur.execute("... WHERE name = %s" % name) # NEVERinjection
Never build SQL with %/f-strings/+. Always pass values as the second argument so PyMySQL escapes them. (Dict params in execute are now rejected as unsafe.)
cur.execute("... WHERE id=%(id)s", {"id": 5})
Named placeholders %(name)s with a dict of values — readable for many params.
cur.executemany( "INSERT INTO t(a,b) VALUES(%s,%s)", [(1,2),(3,4)])★
executemany batches inserts (it optimizes multi-row INSERT).

7Fetch & cursorsreading rows

cur.execute("SELECT id, name FROM t") cur.fetchone() # (1, 'amy') or None cur.fetchall() # [(1,'amy'), (2,'bob')]★
Default cursor returns tuples. fetchmany(n) for a chunk; iterate the cursor directly for row-by-row.
import pymysql.cursors cur = conn.cursor(pymysql.cursors.DictCursor) cur.fetchall() # [{'id':1,'name':'amy'}, ...]★
DictCursor returns each row as a dict — far more readable. Set per-cursor or via connect(cursorclass=...).
cur = conn.cursor(pymysql.cursors.SSCursor) # server-side, streaming
SSCursor/SSDictCursor stream rows from the server — use for huge result sets so you don't buffer everything in memory.
cur.lastrowid · cur.rowcount
lastrowid = last AUTO_INCREMENT id; rowcount = rows affected/returned.

8Transactions & bulkcommit!

cur.execute("INSERT ...") conn.commit() # REQUIRED — autocommit is OFF by default★gotcha
The #1 PyMySQL surprise: writes are not saved until conn.commit() (InnoDB default). Forgetting it silently loses data on close/rollback.
conn.rollback()
Undo the current transaction on error. A try/except around commit with a rollback is the safe pattern.
conn = pymysql.connect(..., autocommit=True)
Turn on autocommit if you don't need multi-statement transactions — each statement commits immediately.
conn.begin() # explicit transaction start
Explicitly begin a transaction when autocommit is on and you need to group a few statements.

9asyncpg vs PyMySQLside by side

# placeholders: asyncpg -> $1, $2 | PyMySQL -> %s★
The most common porting mistake. asyncpg uses PG's native numbered params; PyMySQL uses DB-API %s (pyformat).
# model: asyncpg -> async/await | PyMySQL -> sync (blocking)★
asyncpg needs an event loop; PyMySQL blocks. For async MySQL use aiomysql (wraps PyMySQL) or asyncmy.
# API: asyncpg -> custom (fetch/fetchrow) | PyMySQL -> DB-API 2.0 (cursor)
asyncpg is not DB-API compliant (its own faster API + Records). PyMySQL follows PEP 249 (cursor/execute/fetch).
# commit: asyncpg -> transaction() ctx | PyMySQL -> conn.commit()
asyncpg auto-commits single statements & uses a transaction context; PyMySQL requires an explicit commit() (autocommit off by default).

10Driver landscapewhat to pick

# PostgreSQL: psycopg (v3, sync+async, DB-API) · asyncpg (async, fastest)★
psycopg 3 is the general-purpose PG driver (sync and async, DB-API). Choose asyncpg for max async throughput; psycopg for DB-API compatibility/simplicity.
# MySQL: PyMySQL (pure-python) · mysqlclient (C, fastest) · aiomysql/asyncmy (async)
mysqlclient (C extension) is faster but needs build tools; PyMySQL is pure-Python & portable; aiomysql/asyncmy for asyncio.
# most apps: use SQLAlchemy on top of a driver
SQLAlchemy 2.x gives connection pooling, an ORM, and portable SQL — it drives asyncpg (postgresql+asyncpg://) and PyMySQL (mysql+pymysql://) as engines. Reach for a raw driver only when you need the speed/control.
# pandas: pd.read_sql(query, conn) works with a DB-API conn (PyMySQL)
pandas.read_sql accepts a PyMySQL connection (or a SQLAlchemy engine, which is preferred). For asyncpg, fetch then build the DataFrame yourself.

Python DB Drivers cheat sheet