Using SQL from Code

November 11 · Parameterize, transact, and make it runnable

MaDS Databases & SQL

One question will organize today

Can a receiver run one useful database task without editing SQL or exposing a password?

Course source and adaptation

Today’s sequence follows Alex Reinhart’s Using SQL from Code. Examples use current Psycopg 3 patterns, environment-based secrets, and the Fall 2026 client databases.

Current reference: Psycopg parameters and pipeline mode.

By the end of class

You should be able to:

• open and close a Psycopg connection safely,
• parameterize values instead of formatting SQL,
• choose a transaction boundary and test rollback,
• batch repeated work without hiding failures,
• expose a small receiver-friendly command.

Keep three concerns separate

configuration → connection
question      → SQL + parameters
result        → validation + output

Mixing all three into one giant script makes failures hard to locate.

Secrets are configuration, not code

import os

conninfo = os.environ["DATABASE_URL"]

Never commit passwords, tokens, or a .env file containing them.

Connections and cursors have lifetimes

import psycopg

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database(), current_user")
        print(cur.fetchone())

Context managers close resources; the connection block also manages the transaction.

Checkpoint 1 · Smallest successful connection

Read DATABASE_URL, connect, and print database, user, and server time. Then deliberately misspell the variable name and describe the failure.

No project tables yet. Prove configuration and connectivity first.

Values are data, not SQL text

Unsafe:

cur.execute(f"SELECT * FROM events WHERE state = '{state}'")

Safe:

cur.execute(
    "SELECT * FROM events WHERE state = %s",
    (state,),
)

Do not quote placeholders

# correct
cur.execute("WHERE event_date >= %s", (start_date,))

# wrong
cur.execute("WHERE event_date >= '%s'", (start_date,))

The driver adapts the value and its type.

Identifiers need a different tool

Table and column names are SQL structure, not values.

from psycopg import sql

query = sql.SQL("SELECT count(*) FROM {}").format(
    sql.Identifier(table_name)
)
cur.execute(query)

Prefer a fixed allowlist when identifiers come from users.

Fetch deliberately

row = cur.fetchone()
rows = cur.fetchmany(100)

Avoid loading an unbounded result just because fetchall() is convenient.

Checkpoint 2 · Parameterize a client report

Convert one project query to accept two values from Python—for example geography and date range—without string formatting.

Test an ordinary value, a value with an apostrophe, and a value that returns zero rows.

Transactions group dependent statements

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO load_runs ...")
        cur.executemany(insert_sql, rows)
        cur.execute("UPDATE load_runs SET status = 'ok' ...")

Clean exit commits. An exception rolls the transaction back.

Catch only what you can handle

try:
    run_refresh(conninfo)
except psycopg.errors.UniqueViolation as exc:
    logger.exception("Duplicate business key")
    raise

Logging an exception is not recovery. Re-raise unless you have a defined safe response.

Savepoints isolate a recoverable unit

with conn.transaction():
    load_batch_a(conn)
    with conn.transaction():
        load_optional_batch_b(conn)

Nested transaction contexts use savepoints. Use them only when continuing is valid.

Batch to reduce round trips

cur.executemany(
    "INSERT INTO stage_events (event_id, event_date) VALUES (%s, %s)",
    rows,
)

Psycopg 3 can use pipeline mode internally for executemany(). Batch size still needs measurement and failure semantics.

Checkpoint 3 · Force rollback

Inside one transaction, insert a valid test row and then a row that violates a constraint. Verify that neither remains.

Record the exception class and the query that proves rollback.

A receiver-friendly entry point is small

python -m project_pipeline.report \
  --region PA \
  --start 2020-01-01 \
  --end 2025-01-01

It should validate inputs, emit useful progress, return a nonzero exit status on failure, and document expected output.

Project transfer

Put one parameterized client report and one transaction test in the builder handoff pack.

The receiver should not edit source code to change a geography or date range.

Homework starts here

Homework 2 asks for:

• a safe Python entry point,
• one unusual-value parameter test,
• a forced failure and rollback check,
• exact runbook commands and expected output.

The pattern to keep

read configuration safely
  → prove connection
  → parameterize values
  → bound result size
  → transact the unit of work
  → force a failure
  → expose one small command

Friday: hand off the system. Monday: learn full-text search by extending the one you receive.