---
title: "Using SQL from Code"
subtitle: "November 11 · Parameterize, transact, and make it runnable"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
Can a receiver run one useful database task without editing SQL or exposing a password?
:::

## Course source and adaptation

::: {.source-note}
Today's sequence follows Alex Reinhart's [Using SQL from Code](https://www.refsmmat.com/courses/msp-computing/data-engineering/sql-code.html). Examples use current Psycopg 3 patterns, environment-based secrets, and the Fall 2026 client databases.

Current reference: [Psycopg parameters](https://www.psycopg.org/psycopg3/docs/basic/params.html) and [pipeline mode](https://www.psycopg.org/psycopg3/docs/advanced/pipeline.html).
:::

## 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

```text
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

```python
import os

conninfo = os.environ["DATABASE_URL"]
```

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

## Connections and cursors have lifetimes

```python
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

::: {.checkpoint}
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:

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

Safe:

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

## Do not quote placeholders

```python
# 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.

```python
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

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

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

## Checkpoint 2 · Parameterize a client report

::: {.checkpoint}
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

```python
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

```python
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

```python
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

```python
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

::: {.checkpoint}
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

```bash
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

::: {.project-prompt}
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

```text
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.