psycopg Basics

Introduction

psycopg (version 3, psycopg3) is the modern PostgreSQL driver for Python. It executes parameterized SQL, returns rows as tuples or dicts, and manages transactions with commit() / rollback(). This chapter connects from a virtual environment, runs CRUD, handles errors, and compares the SQL driver model with PyMongo basics—one maps tables and SQL, the other maps documents and BSON.

Prerequisites

• Python pip and venv
• Insert and COPY and SELECT Queries Basics
• PostgreSQL running—Installation and psql

Install

python -m venv .venv
source .venv/bin/activate
pip install "psycopg[binary]>=3.1"

[binary] bundles libpq for easy local dev.

requirements.txt:

psycopg[binary]==3.2.3

Connect

pg_demo.py:

import os
import psycopg
 
uri = os.environ.get(
    "DATABASE_URL",
    "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo",
)
 
conn = psycopg.connect(uri, autocommit=False)

Code explanation:

• One connection per request in web apps (or pool)—not one global connection per process without pooling
• autocommit=False (default)—you commit() after writes
• Use env vars for secrets—Git gitignore

Context manager closes connection:

with psycopg.connect(uri) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT 1")
        print(cur.fetchone())

Parameterized Queries

Always use %s placeholders—never f-strings for user input:

email = "ada@example.com"
 
with psycopg.connect(uri) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, display_name FROM users WHERE email = %s",
            (email,),
        )
        row = cur.fetchone()
        print(row)

Insert with RETURNING:

with psycopg.connect(uri) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO users (email, display_name)
            VALUES (%s, %s)
            RETURNING id, created_at
            """,
            ("new@example.com", "New User"),
        )
        new_id, created_at = cur.fetchone()
    conn.commit()

Code explanation:

• Tuple (email,) for single param—note trailing comma
• commit() persists insert; omit on read-only paths or use with conn: transaction block

Dict Rows (RealDictCursor Equivalent)

from psycopg.rows import dict_row
 
with psycopg.connect(uri) as conn:
    with conn.cursor(row_factory=dict_row) as cur:
        cur.execute("SELECT id, email FROM users LIMIT 5")
        for user in cur:
            print(user["email"])

Code explanation:

• dict_row maps column names to keys—like PyMongo dict documents but flat SQL rows
• Column names must be unique in SELECT list for clean dict keys

Fetch Many and Executemany

For large bulk loads, COPY from Python or psql \copy beats huge executemany—Insert and COPY.

Transactions

Transaction context (psycopg 3):

with psycopg.connect(uri) as conn:
    with conn.transaction():
        with conn.cursor() as cur:
            cur.execute("DELETE FROM posts WHERE id = %s", (999,))

conn.transaction() commits on success, rolls back on exception.

Error Handling

import psycopg
 
try:
    with psycopg.connect(uri) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO users (email, display_name) VALUES (%s, %s)",
                ("ada@example.com", "Duplicate"),
            )
        conn.commit()
except psycopg.errors.UniqueViolation as e:
    print("Email already exists:", e)

Common errors:

SQL vs MongoDB Driver (Mental Map)

Same Python skills—venv, env vars, try/except—different data models.

Connection Pooling (Concept)

Web servers use a pool instead of opening a connection per request:

from psycopg_pool import ConnectionPool
 
pool = ConnectionPool(uri, min_size=2, max_size=10)
 
with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM users")
        print(cur.fetchone()[0])

PgBouncer pools at the server side—Performance chapter. FastAPI integration: PostgreSQL with FastAPI.

Practice Script

list_published_posts.py:

Run:

python list_published_posts.py

FAQ

psycopg2 vs psycopg3?

New projects use pip install psycopg[binary] (psycopg3). API is similar but improved typing and asyncio support.

Why %s not %d for integers?

psycopg adapts Python types—always %s for safety and consistency.

autocommit True?

Rare for apps—each statement commits immediately. ORMs often manage transactions.

async driver?

psycopg.AsyncConnection or asyncpg for asyncio—FastAPI chapter.

SQLAlchemy instead of raw SQL?

ORM maps classes to tables—still uses psycopg/asyncpg underneath. Learn raw psycopg first to read SQL.

JDBC for Java?

See JDBC first program and Spring JDBC PostgreSQL.