DataFrames and Postgres

What you will be able to do

• Insert DataFrame rows with executemany
• Read query results into a DataFrame
• Keep the load path explicit without an ORM

Why this matters for data work

Explore in pandas, land in Postgres. Many teams avoid hiding SQL behind heavy ORMs in pipelines — explicit inserts and queries are easier to debug. We stay on psycopg only (no SQLAlchemy), so DataFrame.to_sql is out of scope on purpose.

Concepts

DataFrame → DB — iterate rows (itertuples is fine at lab scale) or build sequences for executemany.

DB → DataFrame — cur.fetchall() plus column names from cur.description, or build a DataFrame from the result.

dtypes vs SQL types — map intentionally: int → INTEGER, float → NUMERIC, str → TEXT, timestamps → TIMESTAMPTZ.

Idempotent loads — TRUNCATE before reload in labs; production uses staging tables and swaps (later DE track).

Bring Postgres up again if you stopped it (~/dcubes/python-lab/pg from the previous lesson).

Practice on your machine

cd ~/dcubes/python-lab/pg
docker compose up -d
cd ~/dcubes/python-lab
source .venv/bin/activate

cat > pandas_pg_lab.py << 'EOF'
import os
from pathlib import Path

import pandas as pd
import psycopg

root = Path(__file__).resolve().parent
df = pd.read_csv(root / "data" / "raw" / "orders-frames.csv")
df = df.dropna(subset=["quantity"]).copy()
df["quantity"] = df["quantity"].astype(int)
df["line_total"] = df["quantity"] * df["unit_price"]

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"),
)

with psycopg.connect(**conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS order_lines (
              order_id TEXT PRIMARY KEY,
              sku TEXT NOT NULL,
              quantity INTEGER NOT NULL,
              unit_price NUMERIC(10, 2) NOT NULL,
              store TEXT,
              line_total NUMERIC(12, 2) NOT NULL
            )
            """
        )
        cur.execute("TRUNCATE order_lines")
        payload = [
            (
                r.order_id,
                r.sku,
                int(r.quantity),
                float(r.unit_price),
                r.store,
                float(r.line_total),
            )
            for r in df.itertuples(index=False)
        ]
        cur.executemany(
            """
            INSERT INTO order_lines
              (order_id, sku, quantity, unit_price, store, line_total)
            VALUES (%s, %s, %s, %s, %s, %s)
            """,
            payload,
        )
        cur.execute(
            """
            SELECT store, count(*) AS n, sum(line_total) AS revenue
            FROM order_lines
            GROUP BY store
            ORDER BY store
            """
        )
        columns = [d.name for d in cur.description]
        summary = pd.DataFrame(cur.fetchall(), columns=columns)
    conn.commit()

print(summary)
out = root / "data" / "out" / "store_summary.csv"
summary.to_csv(out, index=False)
print("wrote", out)
EOF
python pandas_pg_lab.py

What you should see

• A small DataFrame of store, count, revenue printed.
• data/out/store_summary.csv written.

Common mistakes

• itertuples on huge frames — fine for labs; bulk copy paths exist later for scale.
• Inserting NaN into NOT NULL columns — clean in pandas first.
• Leaving Compose down — connection errors look like code bugs.

Next

Scripts, modules, typing