Loading a Database

CS 374, Fall 2026

Last Week: Build the Tables

def build(engine):
    """Drop and create the tables and the appt_bill view."""
    with engine.begin() as conn:
        conn.execute(text("DROP VIEW IF EXISTS appt_bill"))
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)
    ...

• HW4: classes become tables, and Faker fills them
• The data lived for one run of hw4.py

Today: data worth keeping. In GP3, every team's queries and endpoints run on what your load.py produces, for the rest of the semester.

Drop, Create, Populate

python scripts/load.py              # build, then load: needs empty tables
python scripts/load.py --drop       # drop everything first, then load from nothing
python scripts/load.py --scratch    # a temporary SQLite file: the third check

1. Drop every table, so a second run starts from nothing
2. Create every table from every team's models.py
3. Populate the core first, then each team's load(session)

• Loading twice into the same tables inserts every row twice, so the script refuses unless you --drop
• --drop throws away everything in the database: never the default

Real or Generated?

• Student records are protected by FERPA; preferences are personnel data
• Real data makes the database believable; generated data keeps it legal

Where Real Data Comes From

src/planner/core/data/
  catalog/
    scrape_web.py         a web page
    extract_pdf.py        a PDF
    2026/  2025/  ...     the files they wrote
  scheduling/
    sections.py           a spreadsheet export
  employees.py            a public data set
  README.md               where each file came from

• Every file names the script that made it
• The source stays out of the repository; the script and its output go in

A Web Page

import requests
from bs4 import BeautifulSoup

page = requests.get("https://catalog.jmu.edu/courses/cs/", headers=HEADERS)
soup = BeautifulSoup(page.text, "html.parser")

for block in soup.find_all("div", class_="courseblock"):
    code = block["data-coursecode"]                       # "CS 374"
    title = block.find("span", class_="detail-title").get_text(strip=True)
    print(code, title)

• Find the HTML elements that hold what you want, then read their text
• Be polite: wait between requests, and save a copy of every page
• requests and Beautiful Soup are both installed by pip install -e ".[dev]"

A PDF

Choose one introductory discrete math course: 3 Credit
Hours
CS 227. Discrete Structures I Credits: 3.00
or

187 | J a m e s M a d i s o n U n i v e r s i t y 2 0 2 5 - 2 0 2 6 U n d e r g r a d u a t e ...

MATH 227. Discrete Structures I Credits: 3.00

• pdftotext gives you the words, in roughly the right order, and no structure
• A script fixes what is mechanical: page footers, wrapped lines, 3.00
• A person fixes the rest: columns out of order, bullets torn from their text
• Then a second script parses the corrected file into rows

A Spreadsheet Export

• The export repeats a section once per room, time, and instructor
• Loading it as is would store each fact twice: GP2's repeating group
• So sections.py writes one file per kind of row: sections, meetings, instructors

A Public Data Set

• The Breeze publishes every JMU salary, obtained through a FOIA request
• employees.py keeps only one department, and only names and titles
• The salary column never enters the repository
• Email, office, and roles are filled in by hand, and a second run keeps them

Keep only what your tables need. Data you do not store cannot leak, and cannot go stale.

Generated Data

rng = random.Random(374)        # one seeded generator for every choice
fake = Faker("en_US")
fake.seed_instance(374)

COHORTS = {1238: 60, 1241: 8, 1248: 70, 1251: 8, ...}   # students per admit term

for admit_term, count in COHORTS.items():
    for _ in range(count):
        status = rng.choices(["active", "inactive", "left_major"], weights=[90, 4, 6])[0]

• Same seed, same data: a teammate can reproduce exactly what you see
• Believable means realistic proportions, not just realistic names
• Some rules no constraint can check: an advisor must have is_advisor

Find Rows by What They Mean

courses = {(c.subject_code, c.number): c for c in session.exec(select(Course))}
cs374 = courses["CS", "374"]

advisors = session.exec(select(Employee).where(Employee.is_advisor)).all()

• An id depends on the order rows were loaded: never type one in
• Look rows up by their natural keys: subject and number, term code, email
• Build each lookup once, at the start, not once per row

Order Matters

for row in read("term.csv"):
    session.add(Term(...))
session.flush()                     # send the terms now
for row in read("catalog_year.csv"):
    session.add(CatalogYear(start_term=...))

• The session orders inserts by your Relationships, not by every foreign key
• This exact bug was in the core loader, and SQLite never noticed
• PostgreSQL did, so SQLite now checks foreign keys in this project too

Using AI in GP3

• You may use AI to help write your model classes, load scripts, and scrapers
• That is how the core's data files and scripts were made: drafted with an AI assistant, then read, run, checked against the real data, and revised, one commit at a time
• Give it what it needs: your schema.dbml, the core's models.py, and the README for your data

The rule: you must be able to explain and modify any part of your code during a live code review.
If you accepted code you do not understand, go back and learn it, or rewrite it yourself.

AI and Your Team

• Agree as a team, before anyone starts, who owns which tables and files
• An assistant will happily rewrite a whole file, including a teammate's work
• Read every change before you commit it; it often changes more than you asked
• Commit small and often: your commit history is part of the grade
• Run python scripts/load.py --scratch before you push, every time

Your load.py

def load(session: Session) -> None:
    """Add this team's rows to the session; scripts/load.py commits them."""

• Natural keys, not ids
• One seed for every random choice
• flush() before rows that point at earlier rows
• Real data from your own data/ folder; nothing downloaded while loading
• A README section that says what is real, what is generated, and how much

GP3 is due Monday, Oct 26.