Postgres from Python

What you will be able to do

• Start Postgres with Compose using the Docker lab conventions
• Connect with psycopg from the host on port 15432
• Create a table and insert/select with parameterized queries

Why this matters for data work

Python is often the loader: open a connection, create a staging table, insert rows, commit. psql is great for exploration; jobs use a driver. Deep SQL lives in the next roadmap module — here you only need enough to move data safely.

Concepts

psycopg 3 — PostgreSQL driver. Install psycopg[binary] in the venv.

Host connection — from your laptop/WSL, host is 127.0.0.1 and port 15432 when Compose publishes 15432:5432. The hostname db works only inside the Compose network (from another service), not from your host Python.

Parameterized queries — pass values separately (%s placeholders). Never build SQL with f-strings for user or file data.

Context managers — with psycopg.connect(...) as conn: commits on clean exit in psycopg 3 when you use transactions properly; still be explicit about commits for clarity in labs.

Reuse the Docker pattern from Compose a data stack: postgres:16-alpine, user/password dcubes, healthcheck, named volume.

Practice on your machine

Docker must be running.

mkdir -p ~/dcubes/python-lab/pg
cd ~/dcubes/python-lab/pg

cat > compose.yaml << 'EOF'
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: dcubes
      POSTGRES_PASSWORD: dcubes
      POSTGRES_DB: lab
    ports:
      - "15432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U dcubes -d lab"]
      interval: 3s
      timeout: 3s
      retries: 20

volumes:
  pgdata:
EOF
docker compose up -d
docker compose ps

Wait until healthy. Then:

cd ~/dcubes/python-lab
source .venv/bin/activate
python -m pip install "psycopg[binary]"

cat > pg_lab.py << 'EOF'
import os

import psycopg

conninfo = dict(
    host=os.environ.get("PGHOST", "127.0.0.1"),
    port=int(os.environ.get("PGPORT", "15432")),
    dbname=os.environ.get("PGDATABASE", "lab"),
    user=os.environ.get("PGUSER", "dcubes"),
    password=os.environ.get("PGPASSWORD", "dcubes"),
)

rows = [
    ("ORD-1", "SKU-100", 2, 9.99),
    ("ORD-2", "SKU-101", 1, 15.00),
    ("ORD-3", "SKU-100", 4, 9.99),
]

with psycopg.connect(**conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS events (
              order_id TEXT PRIMARY KEY,
              sku TEXT NOT NULL,
              quantity INTEGER NOT NULL,
              unit_price NUMERIC(10, 2) NOT NULL
            )
            """
        )
        cur.execute("TRUNCATE events")
        cur.executemany(
            """
            INSERT INTO events (order_id, sku, quantity, unit_price)
            VALUES (%s, %s, %s, %s)
            """,
            rows,
        )
        cur.execute("SELECT count(*) FROM events")
        print("count", cur.fetchone()[0])
        cur.execute(
            "SELECT sku, sum(quantity) FROM events GROUP BY sku ORDER BY sku"
        )
        for sku, qty in cur.fetchall():
            print(sku, int(qty))
    conn.commit()
EOF
python pg_lab.py

count 3
SKU-100 6
SKU-101 1

Stop when finished (data keeps unless you down -v):

cd ~/dcubes/python-lab/pg
docker compose down

What you should see

• Compose service healthy on 15432.
• Script prints count 3 and per-sku quantities.
• Re-running the script truncates and reloads cleanly.

Common mistakes

• Connecting to port 5432 — nothing listening if you only published 15432.
• Using host db from host Python — that DNS name is Compose-internal.
• f-string SQL — injection and quoting bugs; use %s and parameters.
• Forgetting Docker is running — connection refused.

Next

DataFrames and Postgres