Psycopg 3 Cheat Sheet

1Install & connect3.3

pip install "psycopg[binary]" # prebuilt libpq pip install "psycopg[binary,pool]" # + connection pool★
The binary extra ships a compiled libpq — no system Postgres headers needed. Plain psycopg links a system libpq.
import psycopg with psycopg.connect("dbname=app user=me host=localhost") as conn: with conn.cursor() as cur: cur.execute("SELECT 1"); cur.fetchone()★
The canonical shape. Conninfo is a keyword string or a URL "postgresql://me@localhost/app". Exiting the with commits AND closes the connection (card 4).
row = conn.execute("SELECT * FROM t WHERE id=%s", (1,)).fetchone()★
conn.execute() shortcut (new in v3) creates a temporary cursor — skip the explicit cursor() for one-off queries.

2Execute & parameters%s, always

cur.execute("SELECT * FROM t WHERE id=%s AND name=%s", (id, name))★
Placeholders are %s for every type (never %d, never ?). Pass a tuple of values — psycopg adapts and quotes them safely.
cur.execute("... WHERE name=%(name)s", {"name": n})★
Named placeholders %(name)s take a dict — clearer with many params.
cur.executemany("INSERT INTO t(a) VALUES(%s)", [(1,), (2,), (3,)])★
Batch the same statement over many param sets. For very large loads, prefer COPY (card 7).
cur.execute(t"SELECT * FROM t WHERE id={id}")3.3 + Py3.14 t-strings
Psycopg 3.3 accepts template strings: interpolated values become parameters, not raw text — safe by construction. Needs Python 3.14 t-strings.

3Fetch & row factoriesshape results

cur.fetchone() · cur.fetchall() · cur.fetchmany(100) for record in cur: # cursors are iterable ...★
Default rows are tuples. Iterate the cursor to stream results lazily.
from psycopg.rows import dict_row with psycopg.connect(conninfo, row_factory=dict_row) as conn: conn.execute("SELECT ...").fetchone()["name"]★
Row factories change row shape: dict_row, namedtuple_row, class_row(MyModel). Set per-connection or per-cursor.
with conn.cursor(name="big") as cur: # server-side cursor cur.execute("SELECT * FROM huge") for row in cur: ...
Named = server-side cursor: Postgres streams rows in batches instead of buffering the whole result client-side. Essential for large tables.

4Transactions & autocommitcommit / block / auto

with psycopg.connect(conninfo) as conn: conn.execute("INSERT ...") # block exit: COMMIT (or ROLLBACK on error), then CLOSE★
Different from psycopg2: leaving the connection with block commits and closes. One connection = one with.
with conn.transaction(): conn.execute("...") with conn.transaction(): # nested = SAVEPOINT conn.execute("...")★
Explicit transaction blocks — the way to run several transactions on one connection. Nesting creates savepoints (partial rollback).
conn.commit() · conn.rollback()
Manual control when you're not using a with/transaction() block.
conn.autocommit = True # or connect(autocommit=True)★
Each statement commits immediately — required for CREATE DATABASE, VACUUM, and other statements that can't run inside a transaction.

5Connection poolpsycopg_pool

from psycopg_pool import ConnectionPool with ConnectionPool(conninfo, min_size=4, max_size=10) as pool: ... # pool opens here, closes at block end★
Reuse connections instead of reconnecting per request — the standard for web apps. The pool warms min_size connections up front.
pool = ConnectionPool(conninfo, open=False) pool.open() # at app startup pool.close() # at app shutdown★
Explicit lifecycle for frameworks (FastAPI/Django startup hooks). Prefer this or the context manager — opening implicitly in the constructor is discouraged.
with pool.connection() as conn: conn.execute("SELECT ...") # returned to the pool; transaction committed/rolled back★
Borrow a connection for the block, then it's recycled — not closed. Commit/rollback happens on exit as usual.

6Dynamic SQLpsycopg.sql

from psycopg import sql q = sql.SQL("SELECT {} FROM {}").format( sql.Identifier("name"), sql.Identifier("user")) cur.execute(q)★
Safely inject identifiers (table/column names) — which %s placeholders cannot do. sql.Identifier quotes them correctly.
cols = sql.SQL(", ").join(map(sql.Identifier, ["a", "b"])) sql.SQL("INSERT INTO t ({}) VALUES ({})").format( cols, sql.SQL(", ").join(sql.Placeholder() * 2))
Compose column lists and placeholder lists programmatically. sql.Literal embeds a value literally when a placeholder won't do.
# NEVER build identifiers with f-strings/%/.format on raw SQL
String-built table/column names are an injection hole just like values. Always route them through sql.Identifier.

7COPY & pipelinebulk + fewer round-trips

with cur.copy("COPY t (a, b) FROM STDIN") as copy: for rec in records: copy.write_row(rec)★
COPY is the fastest bulk insert — far quicker than executemany. write_row takes a tuple; psycopg handles adaptation and encoding.
with cur.copy("COPY (SELECT * FROM t) TO STDOUT") as copy: for row in copy.rows(): ...
COPY out for fast export. copy.rows() yields parsed tuples; copy.read() gives raw bytes.
with conn.pipeline(): conn.execute("INSERT ...") conn.execute("INSERT ...") # sent togetherpipeline
Pipeline mode batches statements to the server, cutting network round-trips on high-latency links. Results sync at block end.

8Asyncasyncio, same shapes

import psycopg async with await psycopg.AsyncConnection.connect(conninfo) as aconn: async with aconn.cursor() as cur: await cur.execute("SELECT %s", (1,)) await cur.fetchone()★
Same API, awaited. Note await psycopg.AsyncConnection.connect(...) (the connect itself is a coroutine), then async with the result.
from psycopg_pool import AsyncConnectionPool async with AsyncConnectionPool(conninfo) as pool: async with pool.connection() as aconn: await aconn.execute("SELECT ...")★
The async pool mirrors the sync one — the go-to for async web frameworks.
await aconn.set_autocommit(True) # awaitable setter
Some property sets become awaitable methods in async (autocommit, isolation). COPY/pipeline have async forms too.

!Common gotchasread before shipping

%s for every param — not ? or %d
psycopg uses %s (and %(name)s) for all types. ? is sqlite/qmark. Never %/f-string the query text — injection.
placeholders can't name tables/columns
%s is for values only. Inject identifiers via psycopg.sql.Identifier (card 6), never string-building.
with connect() COMMITS and CLOSES
Unlike psycopg2 (reusable) and unlike sqlite3 (no close). For multiple transactions on one connection, use conn.transaction() blocks.
it's `import psycopg`, not psycopg2
Psycopg 3 is the package psycopg. psycopg2 is a separate legacy library with a different API (no conn.execute, different COPY/pool). Don't mix docs.
open the pool explicitly
Use with ConnectionPool(...) or open=False + pool.open(). Relying on the constructor to open is discouraged and warns.
stream big results server-side
A default cursor buffers the whole result client-side. Use a named cursor (conn.cursor(name=...)) or COPY for large data.
install a libpq
Use the [binary] extra for a bundled libpq, or install system libpq for the pure/C build. Missing libpq is the classic install error.

Psycopg 3 cheat sheet