SQLModel List Trouble

I recently was working on a project that uses the great work of sqlmodel.

SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness. Documentation: https://sqlmodel.tiangolo.com Source Code: https://github.com/tiangolo/sqlmodel SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.

https://sqlmodel.tiangolo.com/

I ran into a funny issue though!

Take the following example:


from typing import List
from sqlmodel import Field, Session, select, SQLModel, Column, JSON, create_engine
from rich import print

sqlite_file_name = "test.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url)
SQLModel.metadata.create_all(engine)


class Actor(SQLModel, table=True):
    name: str = Field(primary_key=True)
    movies: List[str] = Field(sa_column=Column(JSON))

    def __init__(self, name: str, movies: List[str]):
        self.name = name
        self.movies = movies


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_actors():
    """Creates two actors and commits them to the database"""
    actor_one = Actor(
        name="Bob Dylan", movies=["Pat Garrett and Billy the Kid", "Renaldo and Clara"]
    )
    actor_two = Actor(name="Lady Gaga", movies=["A Star Is Born", "House of Gucci"])
    session = Session(engine)
    session.add(actor_one)
    session.add(actor_two)
    session.commit()


def update_actors():
    with Session(engine) as session:
        # Get actor one
        statement = select(Actor).where(Actor.name == "Bob Dylan")
        results = session.exec(statement)
        actor_one = results.one()
        print("Actor one: ", actor_one.json())
        actor_one.movies.append("The Godfather")
        print("--------- Movie Appended ---------")
        print("Actor one: ", actor_one.json())
        session.add(actor_one)

        print()

        # Get actor two
        statement = select(Actor).where(Actor.name == "Lady Gaga")
        results = session.exec(statement)
        actor_two = results.one()
        print("Actor two: ", actor_two.json())
        actor_two.movies.append("The Many Saints of Newark")
        print("--------- Movie Appended ---------")
        print("Actor two: ", actor_two.json())
        session.add(actor_two)

        session.commit()
        session.refresh(actor_one)
        session.refresh(actor_two)
        print("-------------- Done! --------------")


create_db_and_tables()
create_actors()
update_actors()

This script does a few things, let's break it down:

Sets up a test database:


sqlite_file_name = "test.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url)
SQLModel.metadata.create_all(engine)

Creates the model for an Actor:


class Actor(SQLModel, table=True):
    name: str = Field(primary_key=True)
    movies: List[str] = Field(sa_column=Column(JSON))

    def __init__(self, name: str, movies: List[str]):
        self.name = name
        self.movies = movies

This is an SQLModel, which creates a table on the sqlite3 Database.

Actor.name is required, and is the primary key of the database table.

These are unique values.

Actor.movies is an explicit List of strings.


def __init__(self, name: str, movies: List[str]):
        self.name = name
        self.movies = movies

The __init__ method of Actor requires the same types as described above.

We now create two Actors and commit them to the database by running the create_actors() function.


def create_actors():
    """Creates two actors and commits them to the database"""
    actor_one = Actor(
        name="Bob Dylan", movies=["Pat Garrett and Billy the Kid", "Renaldo and Clara"]
    )
    actor_two = Actor(name="Lady Gaga", movies=["A Star Is Born", "House of Gucci"])
    session = Session(engine)
    session.add(actor_one)
    session.add(actor_two)
    session.commit()

Now we define a function to Select and Update the actors from the Database.


def update_actors():
    with Session(engine) as session:
        # Get actor one
        statement = select(Actor).where(Actor.name == "Bob Dylan")
        results = session.exec(statement)
        actor_one = results.one()
        print("Actor one: ", actor_one.json())
        actor_one.movies.append("The Godfather")
        print("--------- Movie Appended ---------")
        print("Actor one: ", actor_one.json())
        session.add(actor_one)
				...

Here we're also going to print two things!

The first actor, outputted as json

The first actor, after appending the movie "The Godfather" to the list of movies.

Then we're going to add the actor_one Actor object to the session.

Next, we're going to do the same thing to actor_two :


				...
				# Get actor two
        statement = select(Actor).where(Actor.name == "Lady Gaga")
        results = session.exec(statement)
        actor_two = results.one()
        print("Actor two: ", actor_two.json())
        actor_two.movies.append("The Many Saints of Newark")
        print("--------- Movie Appended ---------")
        print("Actor two: ", actor_two.json())
        session.add(actor_two)

        session.commit()
        session.refresh(actor_one)
        session.refresh(actor_two)
        print("-------------- Done! --------------")

So now let's run it!

Output:


gorm@gorm.dev python main.py
Actor one:  {"movies": ["Pat Garrett and Billy the Kid", "Renaldo and Clara"], "name": "Bob Dylan"}
--------- Movie Appended ---------
Actor one:  {"movies": ["Pat Garrett and Billy the Kid", "Renaldo and Clara", "The Godfather"], "name": "Bob Dylan"}

Actor two:  {"movies": ["A Star Is Born", "House of Gucci"], "name": "Lady Gaga"}
--------- Movie Appended ---------
Actor two:  {"movies": ["A Star Is Born", "House of Gucci", "The Many Saints of Newark"], "name": "Lady Gaga"}
-------------- Done! --------------

Interesting!

It looks like we got our update!

Now let's fire up DB Browser for SQLite.


brew install --cask db-browser-for-sqlite

Huh. Well that's odd.

It turns out, we didn't update those database entries.

Where did this happen exactly?

Let's get a little more verbose.

I'm going to remove the print statements in update_actors and then turn on echo=True on the engine.


sqlite_file_name = "test.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True) #<---------------
SQLModel.metadata.create_all(engine)

Now let's remove the database:


gorm@gorm.dev rm test.db

Run the program again:


gorm@gorm.dev python main.py
2021-11-10 21:07:07,554 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:07:07,554 INFO sqlalchemy.engine.Engine COMMIT
2021-11-10 21:07:07,556 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:07:07,556 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("actor")
2021-11-10 21:07:07,557 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-11-10 21:07:07,557 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("actor")
2021-11-10 21:07:07,557 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-11-10 21:07:07,557 INFO sqlalchemy.engine.Engine
CREATE TABLE actor (
	movies JSON,
	name VARCHAR NOT NULL,
	PRIMARY KEY (name)
)


2021-11-10 21:07:07,557 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2021-11-10 21:07:07,559 INFO sqlalchemy.engine.Engine CREATE INDEX ix_actor_name ON actor (name)
2021-11-10 21:07:07,559 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2021-11-10 21:07:07,560 INFO sqlalchemy.engine.Engine COMMIT
2021-11-10 21:07:07,560 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:07:07,561 INFO sqlalchemy.engine.Engine INSERT INTO actor (movies, name) VALUES (?, ?)
2021-11-10 21:07:07,561 INFO sqlalchemy.engine.Engine [generated in 0.00013s] (('["Pat Garrett and Billy the Kid", "Renaldo and Clara"]', 'Bob Dylan'), ('["A Star Is Born", "House of Gucci"]', 'Lady Gaga'))
2021-11-10 21:07:07,562 INFO sqlalchemy.engine.Engine COMMIT
2021-11-10 21:07:07,563 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:07:07,563 INFO sqlalchemy.engine.Engine SELECT actor.movies, actor.name
FROM actor
WHERE actor.name = ?
2021-11-10 21:07:07,563 INFO sqlalchemy.engine.Engine [no key 0.00007s] ('Bob Dylan',)
2021-11-10 21:07:07,564 INFO sqlalchemy.engine.Engine SELECT actor.movies, actor.name
FROM actor
WHERE actor.name = ?
2021-11-10 21:07:07,564 INFO sqlalchemy.engine.Engine [no key 0.00008s] ('Lady Gaga',)
2021-11-10 21:07:07,565 INFO sqlalchemy.engine.Engine COMMIT
2021-11-10 21:07:07,565 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:07:07,566 INFO sqlalchemy.engine.Engine SELECT actor.movies, actor.name
FROM actor
WHERE actor.name = ?
2021-11-10 21:07:07,566 INFO sqlalchemy.engine.Engine [generated in 0.00008s] ('Bob Dylan',)
2021-11-10 21:07:07,567 INFO sqlalchemy.engine.Engine SELECT actor.movies, actor.name
FROM actor
WHERE actor.name = ?
2021-11-10 21:07:07,567 INFO sqlalchemy.engine.Engine [cached since 0.0006238s ago] ('Lady Gaga',)
2021-11-10 21:07:07,567 INFO sqlalchemy.engine.Engine ROLLBACK

Notice that there's no updates!

Just to make sure we're sane, let's open up an interactive shell:

Okay...

Now let's add this to the database...

What.. is happening?

Let's break it down to the basics.

Quick python refresher.

Next, let's try with some lists...

Ok, so what's going on here?

It seems that actor_one.movies hasn't... really changed?

Let's try to switch up our code.

This didn't seem to work:

What now?..

Ok, let's try making a new object.

Output:


2021-11-10 21:37:11,580 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:37:11,582 INFO sqlalchemy.engine.Engine UPDATE actor SET movies=? WHERE actor.name = ?
2021-11-10 21:37:11,582 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('["Pat Garrett and Billy the Kid", "Renaldo and Clara", "The Godfather"]', 'Bob Dylan')
2021-11-10 21:37:11,583 INFO sqlalchemy.engine.Engine COMMIT
2021-11-10 21:37:11,584 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-11-10 21:37:11,585 INFO sqlalchemy.engine.Engine SELECT actor.movies, actor.name
FROM actor
WHERE actor.name = ?
2021-11-10 21:37:11,585 INFO sqlalchemy.engine.Engine [cached since 1597s ago] ('Bob Dylan',)
2021-11-10 21:37:11,585 INFO sqlalchemy.engine.Engine ROLLBACK

Notice these lines:


UPDATE actor SET movies=? WHERE actor.name = ?
[generated in 0.00015s] ('["Pat Garrett and Billy the Kid", "Renaldo and Clara", "The Godfather"]', 'Bob Dylan')

✨ Nice! ✨

Now, taking what we've learned, let's update our program.


from typing import List
from sqlmodel import Field, Session, select, SQLModel, Column, JSON, create_engine
from rich import print

sqlite_file_name = "test.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url)
SQLModel.metadata.create_all(engine)


class Actor(SQLModel, table=True):
    name: str = Field(primary_key=True)
    movies: List[str] = Field(sa_column=Column(JSON))

    def __init__(self, name: str, movies: List[str]):
        self.name = name
        self.movies = movies


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_actors():
    """Creates two actors and commits them to the database"""
    actor_one = Actor(
        name="Bob Dylan", movies=["Pat Garrett and Billy the Kid", "Renaldo and Clara"]
    )
    actor_two = Actor(name="Lady Gaga", movies=["A Star Is Born", "House of Gucci"])
    session = Session(engine)
    session.add(actor_one)
    session.add(actor_two)
    session.commit()


def update_actors():
    with Session(engine) as session:
        # Get actor one
        statement = select(Actor).where(Actor.name == "Bob Dylan")
        results = session.exec(statement)
        actor_one = results.one()
        print("Actor one: ", actor_one.json())
        movies = []
        movies += actor_one.movies
        movies += ["The Godfather"]
        actor_one.movies = movies
        print("--------- Movie Appended ---------")
        print("Actor one: ", actor_one.json())
        session.add(actor_one)

        print()

        # Get actor two
        statement = select(Actor).where(Actor.name == "Lady Gaga")
        results = session.exec(statement)
        actor_two = results.one()
        print("Actor two: ", actor_two.json())
        movies = []
        movies += actor_two.movies
        movies += ["The Many Saints of Newark"]
        actor_two.movies = movies
        print("--------- Movie Appended ---------")
        print("Actor two: ", actor_two.json())
        session.add(actor_two)

        session.commit()
        session.refresh(actor_one)
        session.refresh(actor_two)
        print("-------------- Done! --------------")


create_db_and_tables()
create_actors()
update_actors()

Output:


gorm@gorm.dev python main.py
Actor one:  {"movies": ["Pat Garrett and Billy the Kid", "Renaldo and Clara"], "name": "Bob Dylan"}
--------- Movie Appended ---------
Actor one:  {"movies": ["Pat Garrett and Billy the Kid", "Renaldo and Clara", "The Godfather"], "name":
"Bob Dylan"}

Actor two:  {"movies": ["A Star Is Born", "House of Gucci"], "name": "Lady Gaga"}
--------- Movie Appended ---------
Actor two:  {"movies": ["A Star Is Born", "House of Gucci", "The Many Saints of Newark"], "name": "Lady
Gaga"}
-------------- Done! --------------

And just to be sure, let's open up the database!

Sure enough!

Hope you learned something new!

Maybe I'll even save you a few hours of debugging. 🥴