A Model That Saves Itself

Time to start the library. By the end of this lesson Post(title="Hello").save(connection) writes a row, Post.get(connection, 1) reads it back as a Post, and you will have met the first design decision with real consequences.

What the course supplies

db.py is given to you and stays unchanged all week: a SQLite connection with two tables already created, and rows that behave like dictionaries.

import sqlite3

SCHEMA = """
create table if not exists post (
    id    integer primary key autoincrement,
    title text    not null,
    views integer not null default 0
);
create table if not exists user (
    id    integer primary key autoincrement,
    email text    not null
);
"""


def connect(path=":memory:"):
    connection = sqlite3.connect(path)
    connection.row_factory = sqlite3.Row
    connection.executescript(SCHEMA)
    return connection

row_factory = sqlite3.Row is what lets dict(row) give you {"id": 1, "title": "Hello", ...}, which is how a row becomes keyword arguments for a model.

One base class, a subclass per table

The shape is the one every ORM uses: a base class with the behaviour, and a subclass per table carrying only what differs.

class Post(Model):
    table = "post"
    columns = ("title", "views")

Those two are class attributes, so self.table finds "post" through the lookup you learned about in the last lesson, and there is exactly one copy of each string no matter how many posts exist. They are also immutable — a string and a tuple — which is what makes sharing them safe. Tomorrow, columns is replaced by field objects that can validate; today it’s a tuple of names.

__init__ takes the columns as keyword arguments, and refuses anything else:

    def __init__(self, id=None, **values):
        self.id = id
        for name in self.columns:
            setattr(self, name, values.pop(name, None))
        if values:
            raise TypeError(f"{type(self).__name__} has no column {sorted(values)[0]!r}")

setattr(self, name, ...) writes to the instance, so each post gets its own title and views. Popping as it goes leaves values holding only the names that aren’t columns, and a library that says Post has no column 'author' costs one line and saves its callers an afternoon.

Values are parameters; names are code

save builds SQL. Two different rules apply to the two kinds of things going into that string.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("create table post (id integer primary key, title text)")

title = "Robert'); drop table post; --"
connection.execute("insert into post (title) values (?)", (title,))

print("stored safely:", connection.execute("select title from post").fetchone()[0])
print("table still here:", connection.execute("select count(*) from post").fetchone()[0])

try:
    connection.execute("select * from ? where id = 1", ("post",))
except sqlite3.OperationalError as error:
    print("OperationalError:", error)

A ? parameter is never parsed as SQL, so the injection attempt is stored as a title and nothing else happens. But a parameter can’t stand in for a table or column name — SQLite has to know what it’s selecting from before it binds anything.

So identifiers have to be interpolated, and the only safe source for them is your own code:

        names = ", ".join(self.columns)          # from the class
        marks = ", ".join("?" for _ in self.columns)
        connection.execute(f"insert into {self.table} ({names}) values ({marks})", values)

self.table and self.columns are written in a class body by the developer using your library, and the values are bound as parameters. That distinction — identifiers from code, data from parameters — is what keeps this f-string from being the mistake it looks like. Day 3 makes it a documented rule, and shows what happens when a library lets a caller supply a column name.

The checkpoint: write save and get

Everything else is in place. models.py has the class, the tests, and two methods raising NotImplementedError.

    def save(self, connection):
        values = [getattr(self, name) for name in self.columns]
        names = ", ".join(self.columns)
        if self.id is None:
            marks = ", ".join("?" for _ in self.columns)
            cursor = connection.execute(
                f"insert into {self.table} ({names}) values ({marks})", values
            )
            self.id = cursor.lastrowid
        else:
            assignments = ", ".join(f"{name} = ?" for name in self.columns)
            connection.execute(
                f"update {self.table} set {assignments} where id = ?", [*values, self.id]
            )
        connection.commit()
        self.cache()[(type(self), self.id)] = self
        return self

    @classmethod
    def get(cls, connection, id):
        cached = cls.cache().get((cls, id))
        if cached is not None:
            return cached
        row = connection.execute(
            f"select * from {cls.table} where id = ?", (id,)
        ).fetchone()
        if row is None:
            raise LookupError(f"no {cls.__name__} with id {id}")
        found = cls(**dict(row))
        cls.cache()[(cls, id)] = found
        return found

Bug hunt: two models, one cache

Your get() keeps loaded objects in a dictionary, so asking twice costs one query. That dictionary is where today’s bug lives.

Run the playground below. Six tests pass and one fails.

Reproduce. The failing test saves a post and a user, then loads each by id:

Post(title="Hello", views=0).save(self.connection)
User(email="someone@example.com").save(self.connection)
self.assertIsInstance(Post.get(self.connection, 1), Post)

Inspect. The message is specific: User(id=1, email='someone@example.com') is not an instance of <class 'models.Post'>. Post.get returned a User. Not a wrong title — a wrong class.

Narrow. Both rows have id 1; both tables number from 1 independently. Add a line to the failing test to see the cache itself:

print(Model._instances)

It holds {1: User(id=1, ...)}. One entry, because the user overwrote the post, and one dictionary, because _instances = {} is written once in Model’s class body. Every subclass inherits the same dictionary object — exactly the sharing from the last two lessons, with a twist: there is no shared list in sight at the call site, and Post._instances is User._instances is True.

Pin. The test that just failed is the regression test. It says what callers need: Post.get returns a Post, whatever else is in the database.

Fix. The key has to identify the row and the class:

        cached = cls.cache().get((cls, id))
        ...
        self.cache()[(type(self), self.id)] = self

Use type(self) when saving and cls when loading, and note they’re the same object: for post.save(...), type(post) is Post.

What the cache commits you to

An identity map is a real design decision, not an optimisation you can slip in:

• Two get() calls for one row return the same object, so a change through one name is visible through the other. Callers rely on this once they notice it.
• Nothing ever leaves the cache, so a long-running process keeps every object it has loaded alive. That’s a memory leak with a pleasant name, and you will measure it with tracemalloc on Day 6.
• A row changed by someone else won’t be re-read while a copy is cached.

Keeping it for now is a deliberate choice: it gives Day 6 something real to measure, and Day 7 something real to argue about in the design write-up.

Where Day 1 leaves the library

Working on your own machine? Download the Day 1 files and run python -m unittest in that directory — no run_tests.py needed outside the browser.

What you learned

• A subclass carries what differs (table, columns); the base class carries the behaviour.
• Values go into SQL as parameters; identifiers are interpolated, and must come from your code rather than your caller’s input.
• A mutable class attribute is one object shared by every subclass — the same trap as a mutable default, with nothing at the call site to warn you.
• Returning the same object for the same row is a promise about identity, with costs you will measure later this week.

Tomorrow: columns = ("title", "views") becomes real field objects that validate what you assign, and __init_subclass__ registers each model as it’s defined.