psycopg2-binary
psycopg2-binary is the precompiled wheel distribution of psycopg2, the long-dominant PostgreSQL adapter for Python. It implements the DB API 2.0 spec in C on top of libpq, is thread-safe at the connection level, and powers a decade of Django and SQLAlchemy deployments. The -binary package exists so you can pip install it without a C compiler or Postgres dev headers; it bundles its own libpq and OpenSSL inside the wheel.
Rock solid for the enormous installed base it already serves, and there is no urgency to migrate working systems. But its own README tells new projects to start with Psycopg 3, and you should listen to it.
Use it if
- You maintain an existing codebase, Django project, or SQLAlchemy 1.x stack that already speaks psycopg2; it is stable, fast, and not going anywhere
- You need painless installs in CI, containers, and developer laptops without apt-get install libpq-dev and a compiler
- You depend on the mature psycopg2 extension surface: RealDictCursor, execute_values, sql.Identifier composition, COPY support, LISTEN/NOTIFY
- You need a synchronous driver with well-understood transaction semantics and thousands of documented answers for every edge case
- You are starting a new project: the README itself says psycopg2 is not expected to receive new features and points new projects to Psycopg 3, which adds native async, pipeline mode, and a saner parameter binding model
- You need asyncio: psycopg2 is synchronous only, so async web frameworks need asyncpg or psycopg 3 instead
- You are shipping the -binary wheel to production against upstream advice: the docs recommend building psycopg2 from source for production because the wheel's bundled libpq and OpenSSL can conflict with other libraries in the same process (the classic case is SSL errors alongside other ssl users)
- You want server-side prepared statements or modern Postgres features to keep arriving; feature development stopped here years ago by design
Setup reality
pip install psycopg2-binary is genuinely one command on Linux, macOS, and Windows, which is the entire point of the package. The traps are subtle: the non-binary psycopg2 package needs pg_config, libpq-dev, and a compiler, so people mix the two in requirements files and get different libpq behavior per environment; the bundled OpenSSL in the wheel can clash with other C extensions in the same process; and upstream explicitly labels the binary package as for development and testing, advising source builds for production. Autocommit is off by default, so forgotten commits and idle-in-transaction sessions are a rite of passage.
Patterns
Connect and run a queryconnect-and-query
import psycopg2
conn = psycopg2.connect(
'postgresql://user:pass@localhost:5432/mydb'
)
with conn.cursor() as cur:
cur.execute('SELECT id, name FROM users WHERE active = %s', (True,))
rows = cur.fetchall()
conn.commit()Parameters are always %s regardless of column type; never use f-strings or .format to build SQL.
Get rows as dictionariesdict-rows
from psycopg2.extras import RealDictCursor
with conn.cursor(cursor_factory=RealDictCursor) as cur:
cur.execute('SELECT id, name FROM users')
for row in cur.fetchall():
print(row['name'])Default cursors return plain tuples; RealDictCursor costs a little memory but saves index-counting bugs.
Commit or roll back automaticallytransaction-context
with conn:
with conn.cursor() as cur:
cur.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s', (100, 1))
cur.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s', (100, 2))
# committed on success, rolled back on exceptionwith conn commits the transaction but does NOT close the connection; that asymmetry bites everyone once.
Insert many rows fastbulk-insert
from psycopg2.extras import execute_values
execute_values(
cur,
'INSERT INTO events (ts, kind, payload) VALUES %s',
rows, # list of tuples
page_size=1000,
)execute_values is dramatically faster than executemany, which sends one statement per row.
Bulk load with COPYcopy-bulk-load
with open('data.csv') as f:
cur.copy_expert(
"COPY events (ts, kind, payload) FROM STDIN WITH (FORMAT csv, HEADER true)",
f,
)
conn.commit()COPY is the fastest ingestion path by far; copy_expert gives you full COPY syntax unlike the older copy_from.
Build SQL with dynamic table or column namesdynamic-identifiers
from psycopg2 import sql
query = sql.SQL('SELECT {fields} FROM {table} WHERE id = %s').format(
fields=sql.SQL(', ').join(map(sql.Identifier, ['id', 'name'])),
table=sql.Identifier('users'),
)
cur.execute(query, (42,))%s placeholders cannot carry identifiers; sql.Identifier quotes them correctly and blocks injection.
Stream a huge result setserver-side-cursor
with conn.cursor(name='big_scan') as cur: # named = server-side
cur.itersize = 10000
cur.execute('SELECT * FROM events')
for row in cur:
process(row)An unnamed cursor fetches the entire result into memory; naming the cursor keeps rows on the server and streams them.
Pool connections in a threaded appconnection-pool
from psycopg2.pool import ThreadedConnectionPool
pool = ThreadedConnectionPool(minconn=1, maxconn=10, dsn=DSN)
conn = pool.getconn()
try:
with conn, conn.cursor() as cur:
cur.execute('SELECT 1')
finally:
pool.putconn(conn)The built-in pool is basic (no health checks or timeouts); most production stacks pool via SQLAlchemy or pgbouncer instead.
Run statements outside a transactionautocommit-ddl
conn.autocommit = True
with conn.cursor() as cur:
cur.execute('CREATE DATABASE analytics')
cur.execute('VACUUM events')CREATE DATABASE and VACUUM refuse to run inside a transaction block, which is exactly where psycopg2 puts you by default.
Write and read jsonbjson-columns
from psycopg2.extras import Json
cur.execute(
'INSERT INTO docs (meta) VALUES (%s)',
(Json({'tags': ['a', 'b'], 'v': 2}),),
)
cur.execute('SELECT meta FROM docs')
meta = cur.fetchone()[0] # already a dictWrap dicts in Json() on the way in; jsonb comes back as parsed Python objects automatically.
React to LISTEN/NOTIFY eventslisten-notify
import select
conn.autocommit = True
cur = conn.cursor()
cur.execute('LISTEN jobs')
while True:
if select.select([conn], [], [], 5) != ([], [], []):
conn.poll()
while conn.notifies:
note = conn.notifies.pop(0)
print(note.channel, note.payload)Requires autocommit; notifications only arrive after poll() and queue on conn.notifies.
Handle Postgres errors by classerror-handling
import psycopg2
from psycopg2 import errors
try:
cur.execute('INSERT INTO users (email) VALUES (%s)', (email,))
conn.commit()
except errors.UniqueViolation:
conn.rollback()
raise DuplicateEmail(email)
except psycopg2.OperationalError:
conn.rollback()
reconnect()After any error the connection is in a failed transaction; every statement raises InFailedSqlTransaction until you rollback().
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| psycopg | PyPI | Psycopg 3, the successor where development happens: new projects, async support, pipeline mode |
| asyncpg | PyPI | Pure-asyncio applications that want the fastest Postgres driver and do not need DB API compatibility |
| pg8000 | PyPI | You need a pure-Python driver with no C or libpq dependency at all, for example in restricted build environments |