psycopg2

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-binary Python (development/quick start — no system deps)
• pip install psycopg2 Python (production — requires libpq-dev)

Imports

• connect

  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

  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

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()