psycopg2

2.9.11 verified Tue May 12 auth: no python install: verified quickstart: stale

The most widely used PostgreSQL adapter for Python. Sync only. Two packages on PyPI: psycopg2 (source, requires libpq dev headers) and psycopg2-binary (pre-compiled, no system deps). Official docs explicitly warn against psycopg2-binary in production. Current version: 2.9.11 (Mar 2026). For async PostgreSQL use asyncpg or psycopg (v3). For new projects consider migrating to psycopg (v3) which has both sync and async.

pip install psycopg2-binary

Common errors

error ModuleNotFoundError: No module named 'psycopg2' ↓

cause The `psycopg2` package is not installed in your Python environment or the Python interpreter cannot find it in its `sys.path`.

fix

Install the package using pip: pip install psycopg2-binary. If building from source is preferred (e.g., for production), ensure development headers for PostgreSQL (libpq-dev) are installed on your system before running pip install psycopg2.

error psycopg2.OperationalError: could not connect to server: Connection refused ↓

cause This error indicates that the PostgreSQL server is either not running, not listening on the specified host/port, or a firewall is blocking the connection.

fix

Ensure the PostgreSQL server is running, verify the connection parameters (host, port, user, password, dbname) are correct, check postgresql.conf for listen_addresses and port settings, and confirm that no firewall is blocking port 5432 (or your custom port).

error TypeError: not all arguments converted during string formatting ↓

cause This typically occurs when passing parameters to `cursor.execute()`: either the number of placeholders (`%s`) in the SQL query does not match the number of values provided, or the parameters are not passed as a sequence (e.g., a tuple or list), even for a single parameter.

fix

Always pass parameters as a tuple (or list) as the second argument to cursor.execute(). For a single parameter, remember the trailing comma to create a tuple: cursor.execute('SELECT * FROM my_table WHERE id = %s', (my_id,)). Ensure the number of %s placeholders matches the number of items in your parameter sequence.

error psycopg2.ProgrammingError: column "<column_name>" does not exist ↓

cause This error often arises due to case sensitivity issues in PostgreSQL column names (unquoted identifiers are folded to lowercase) or a mismatch between the column name in your SQL query and the actual table schema.

fix

Verify that the column name in your SQL query exactly matches the column name in your PostgreSQL table, paying close attention to case. If the column was created with mixed-case or uppercase letters and double-quoted (e.g., "MyColumn"), you must consistently double-quote it in your queries. Alternatively, rename columns to all lowercase for simpler handling.

error psycopg2.ProgrammingError: can't adapt type '<Python_type>' ↓

cause Psycopg2 does not know how to convert a specific Python object type (e.g., custom classes, `numpy` types, `DictRow`) into a PostgreSQL-compatible SQL type.

fix

Convert the problematic Python object to a basic Python type (like string, int, float, or datetime object) that psycopg2 can natively adapt before passing it to the query. For custom types, you can register a custom adapter using psycopg2.extensions.register_adapter to define how your object should be converted to a SQL string.

Warnings

gotcha psycopg2-binary is NOT recommended for production. Official docs: 'The binary package is a practical choice for development and testing but in production it is advised to use the package built from source.' It bundles its own libpq which can conflict with system libraries. ↓

fix Use psycopg2-binary for development. In production use psycopg2 with system libpq: apt-get install libpq-dev && pip install psycopg2

gotcha Parameter placeholder is %s for ALL types — not ? (sqlite3) or :param (SQLAlchemy). Using ? raises ProgrammingError. Using f-strings creates SQL injection risk. ↓

fix Always: cur.execute('SELECT * FROM t WHERE id = %s', (value,)) — note the trailing comma to make a tuple.

gotcha Single-value parameterized queries need a tuple: cur.execute('SELECT %s', (value,)) — not cur.execute('SELECT %s', value). Passing a non-tuple raises TypeError or treats string as iterable of chars. ↓

fix Always wrap single values in a tuple: (value,) not value.

gotcha Default cursor returns rows as tuples. Accessing by column name raises TypeError. Use RealDictCursor or DictCursor from psycopg2.extras for dict access. ↓

fix cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

gotcha psycopg2 is sync only. Cannot be used in async contexts (FastAPI, asyncio) without blocking the event loop. Use asyncpg or psycopg (v3) for async. ↓

fix For async: pip install asyncpg or pip install psycopg[binary] (psycopg v3)

gotcha psycopg2 ≠ psycopg (v3). They are different packages with different APIs. psycopg (without the 2) is the newer version — different install, different import patterns. ↓

fix psycopg2: 'import psycopg2'. psycopg v3: 'import psycopg'. Do not mix them.

gotcha Connection is not thread-safe. Do not share a single psycopg2 connection across threads. Use a connection pool (psycopg2.pool or SQLAlchemy's pool). ↓

fix Use psycopg2.pool.ThreadedConnectionPool or let SQLAlchemy manage the pool.

Install

pip install psycopg2

Install compatibility verified last tested: 2026-05-12

python os / libc variant status wheel install import disk

3.10 alpine (musl) psycopg2 - - - -

3.10 alpine (musl) psycopg2-binary - - 0.04s 77.4M

3.10 slim (glibc) psycopg2 - - - -

3.10 slim (glibc) psycopg2-binary - - 0.04s 152M

3.11 alpine (musl) psycopg2 - - - -

3.11 alpine (musl) psycopg2-binary - - 0.07s 83.7M

3.11 slim (glibc) psycopg2 - - - -

3.11 slim (glibc) psycopg2-binary - - 0.06s 158M

3.12 alpine (musl) psycopg2 - - - -

3.12 alpine (musl) psycopg2-binary - - 0.05s 74.2M

3.12 slim (glibc) psycopg2 - - - -

3.12 slim (glibc) psycopg2-binary - - 0.05s 149M

3.13 alpine (musl) psycopg2 - - - -

3.13 alpine (musl) psycopg2-binary - - 0.05s 70.7M

3.13 slim (glibc) psycopg2 - - - -

3.13 slim (glibc) psycopg2-binary - - 0.04s 147M

3.9 alpine (musl) psycopg2 - - - -

3.9 alpine (musl) psycopg2-binary - - 0.04s 76.6M

3.9 slim (glibc) psycopg2 - - - -

3.9 slim (glibc) psycopg2-binary - - 0.04s 151M

Imports

connect

wrong

import psycopg2

conn = psycopg2.connect('...')
cur = conn.cursor()
cur.execute(f'SELECT * FROM users WHERE id = {user_id}')  # SQL injection

correct

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=5432,
    dbname='mydb',
    user='myuser',
    password='mypassword'
)

cur = conn.cursor()
cur.execute('SELECT * FROM users WHERE id = %s', (user_id,))  # parameterized
rows = cur.fetchall()
cur.close()
conn.close()

Always use parameterized queries with %s placeholders — never f-strings or string formatting. psycopg2 uses %s for ALL types, not ? like sqlite3.

RealDictCursor

wrong

cur = conn.cursor()
cur.execute('SELECT id, name FROM users')
print(cur.fetchall()[0]['name'])  # KeyError — default cursor returns tuples

correct

import psycopg2
import psycopg2.extras

conn = psycopg2.connect(...)
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute('SELECT id, name FROM users')
rows = cur.fetchall()
print(rows[0]['name'])  # dict access not index

Default cursor returns tuples. Use RealDictCursor from psycopg2.extras for dict-style row access.

Quickstart stale last tested: 2026-04-23

Minimal psycopg2 connection with RealDictCursor and parameterized query.

# Development: pip install psycopg2-binary
# Production: pip install psycopg2 (requires libpq-dev)
import psycopg2
import psycopg2.extras

conn = psycopg2.connect(
    host='localhost',
    dbname='mydb',
    user='myuser',
    password='mypassword'
)

with conn:
    with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
        # Parameterized query — always use %s
        cur.execute(
            'SELECT * FROM users WHERE active = %s',
            (True,)  # note: tuple, not single value
        )
        users = cur.fetchall()
        for user in users:
            print(user['name'])

conn.close()