SQLModel Mini Lab

In this lab you will rewrite last Thursday's create_load.py using SQLModel. Your two HW2 tables become two Python classes, and your Faker functions return objects instead of tuples. Keep the demo models.py open as a guide; it is the hotel demo from last Thursday, rewritten the same way.

There is nothing to submit. This lab is practice for HW4, which asks you to do the same thing more carefully.

Step 1: Set up

Use the same lab folder as last Thursday, outside of your planner clone. Activate the planner environment if needed:

source path/to/planner/.venv/bin/activate        # Linux and macOS
source path/to/planner/.venv/Scripts/activate    # Windows (Git Bash)

Copy create_load.py to a new file named models.py, and keep hw2_pg.sql open beside it. You will work in SQLite first, so you don't need the SSH tunnel until Step 6.

Step 2: Define the classes

Delete CONNSTR and create_tables(), and replace them with an engine and two classes, one per table:

from sqlmodel import Field, Session, SQLModel, create_engine

engine = create_engine("sqlite:///lab.db", echo=True)

Write each column as a type hint, using the table below to pick the Python type. A column without | None is NOT NULL.

PostgreSQL	SQLModel
`integer`	`int`
`text`, `varchar`	`str`
`numeric(5, 2)`	`Decimal = Field(max_digits=5, decimal_places=2)`
`date`, `timestamp`	`date`, `datetime` (from the `datetime` module)
`boolean`	`bool`
`REFERENCES t (col)`	`Field(foreign_key="t.col")`
`UNIQUE`	`Field(unique=True)`
`CHECK (...)`	`__table_args__ = (CheckConstraint("..."),)`, as in the demo

An identity key becomes x_id: int | None = Field(default=None, primary_key=True), as in the demo. If a table name has an underscore, set __tablename__ too.

Then make the main block create the tables:

if __name__ == "__main__":
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)

Run the program, and compare the CREATE TABLE statements it prints with your hw2_pg.sql. Write a comment at the top of your file listing three differences you find. For each one, decide whether it matters.

Step 3: Insert the 1st table

Change make_table1(count) so that it returns a list of objects instead of a list of tuples:

hotels.append(Hotel(name=name, street=street, city=city, country=country, year=year))

Keyword arguments mean the order of the columns no longer matters. Then replace insert_table1() with a session in the main block:

    rows = make_table1(20)
    with Session(engine) as session:
        print("before:", rows[0].table1_id)
        session.add_all(rows)
        session.commit()
        print("after:", rows[0].table1_id)

Run it again. Find the INSERT statements in the output. At what line of your code were they sent?

Step 4: Insert the 2nd table

Your second table has a foreign key to the first. Pick one of these two approaches:

Ids, like Thursday. After the commit in Step 3, collect the ids with [row.table1_id for row in rows], pass them to make_table2(ids), and have it return objects. Add those to the session and commit again.
Relationships, like the demo. Add a Relationship attribute to each class, with back_populates naming the other one (see the slides or the demo). Then append child objects to the parent's list before the commit, and commit everything at once.

Check the output: in what order did the INSERT statements run, and where did the foreign key values come from? Open lab.db in DB Browser for SQLite, or run sqlite3 lab.db, and check the data.

Step 5: Break it on purpose

Try each of these, one at a time, and note when the error appears: at the line that creates the object, at commit(), or not at all?

Leave out a required (NOT NULL) attribute when creating an object.
Give a value that violates one of your CHECK constraints.
Give a string where an int belongs, such as year="old".

What does this tell you about where constraints need to be defined?

Step 6: Switch to PostgreSQL (if time)

Start ssh stu, and change only the engine:

engine = create_engine("postgresql+psycopg://username@localhost/sec1", echo=True)

Replace username with your e-ID. The password comes from your password file, just like Thursday.

Warning

drop_all() drops the tables you created on Thursday, since they have the same names. That is fine for this lab; the program creates and loads them again.

Run the program, and refresh your tables in pgAdmin. What changed in the CREATE TABLE output, now that the database is PostgreSQL?

Step 7: Look at the core schema (if time)

In your planner clone, run the build script with echo turned on:

SQL_ECHO=1 python scripts/build.py

That builds the ten core tables in a local SQLite file named planner.db, which git ignores. Compare the CREATE TABLE statements with src/planner/core/models.py. Find one thing in the SQL that you would not have guessed from the Python.