SQLModel Operations: CRUD and Related Tables

SQLModel: Read, Update, Delete Operations

1. Reading Data

Retrieving the First Result (`.first()`)

The results.first() method is used to retrieve only the first item returned by a database query.
If the database query yields no results, first() will return None.
Example (Reading a Hero younger than 25):

def select_heroes(): 
    with Session(engine) as session: 
        statement = select(Hero).where(Hero.age < 25) 
        results = session.exec(statement) 
        hero = results.first() 
        print("Hero:", hero)

Retrieving Exactly One Result (`.one()`)

The results.one() method is used when you expect your query to return exactly one item from the database.
It will raise an error if the query returns zero items or more than one item.
Example (Expecting one hero younger than 25):

def select_heroes():
    with Session(engine) as session: 
        statement = select(Hero).where(Hero.age < 25) 
        results = session.exec(statement) 
        hero = results.one() print("Hero:", hero)

Selecting with a Condition (`.where()`)

The .where() clause is used to filter results based on specified conditions.
Example (Selecting a Hero by ID):

def select_heroes(): 
    with Session(engine) as session: 
        statement = select(Hero).where(Hero.id == 1) 
        results = session.exec(statement) 
        hero = results.first() print("Hero:", hero)

Shorter Selection by Primary Key (`session.get()`)

The session.get(Model, primary_key_value) method provides a concise way to retrieve a single item directly by its primary key.
If no row matches the given primary key, get() will return None.
Example (Getting Hero with ID 1):

def select_heroes(): 
    with Session(engine) as session: 
        hero = session.get(Hero, 1)
        print("Hero:", hero)

Retrieving All Results (`.all()`)

The results.all() method retrieves all items returned by the database query.
Example (Selecting all Heroes):

def select_heroes(): 
    with Session(engine) as session: 
        statement = select(Hero) 
        results = session.exec(statement) 
        heroes = results.all() 
        print(heroes)

Limiting Results (`.limit()`)

The .limit(N) clause restricts the number of rows returned by the query to a maximum of N rows.
Example (Selecting the first 3 Heroes):

def select_heroes(): 
    with Session(engine) as session: 
        statement = select(Hero).limit(3)
        results = session.exec(statement)
        heroes = results.all() print(heroes)

Offsetting Results (`.offset()`)

The .offset(N) clause skips the first N rows of the result set.
It is often used in conjunction with .limit() for pagination.
Example (Skipping 3 heroes and then selecting the next 3):

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).offset(3).limit(3)
        results = session.exec(statement)
        heroes = results.all() print(heroes)

Example (Getting the next 3 heroes after the previous offset):

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).offset(6).limit(3) # Continues from a previous offset results = session.exec(statement) heroes = results.all() print(heroes)

Combining `limit`, `offset`, and `where`

These clauses can be combined to create complex queries for filtering and pagination.
Example (Heroes older than 32, offset 1, limit 2):

def select_heroes():
    with Session(engine) as session:
    statement = select(Hero).where(Hero.age > 32).offset(1).limit(2)
    results = session.exec(statement)
    heroes = results.all()
    print(heroes)

2. Adding More Data

To add new data, you create instances of your SQLModel classes, add them to the session, and then commit the session.
Steps:
1. Create instances of your model (e.g., Hero objects).
2. Use session.add(instance) for each instance to mark it for insertion.
3. Call session.commit() to persist these changes to the database.

Example (Creating multiple Hero instances):

def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
    hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
    hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
    hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
    hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)

with Session(engine) as session:
    session.add(hero_1)
    session.add(hero_2)
    session.add(hero_3)
    session.add(hero_4)
    session.add(hero_5)
    session.add(hero_6)
    session.add(hero_7)
    session.commit()

3. Updating Data

Updating involves retrieving an existing item, modifying its attributes, and then committing the changes.
Steps:
1. Select the item you wish to update from the database.
2. Modify the desired attribute(s) of the retrieved object.
3. Call session.add(modified_object) to mark the object in the session as modified.
4. Call session.commit() to save the changes to the database.
5. Call session.refresh(modified_object) to load any updated or generated values from the database back into your Python object, ensuring it reflects the current DB state.

Example (Updating Spider-Boy's age):

def update_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Boy")
        results = session.exec(statement)
        hero = results.one()
        print("Hero:", hero)

    hero.age = 16 # Modify the attribute
    session.add(hero) # Add the modified object to the session
    session.commit() # Commit the changes
    session.refresh(hero) # Refresh to get latest DB state
    print("Updated hero:", hero)

4. Deleting Data

Deleting data involves selecting an item, marking it for deletion, and then committing the session.
Steps:
1. Select the row(s) you intend to delete.
2. Use session.delete(object) to mark the object for deletion from the database.
3. Call session.commit() to execute the deletion.
4. (Optional but recommended for verification) Attempt to read the deleted row; it should return None.

Example (Deleting Spider-Youngster):

def delete_heroes():
    with Session(engine) as session:
        # Get the row from DB
        statement = select(Hero).where(Hero.name == "Spider-Youngster")
        results = session.exec(statement)
        hero = results.one()
        print("Hero: ", hero)

    # Delete the row from DB
    session.delete(hero)
    session.commit()
    print("Deleted hero:", hero)

    # Try reading the deleted row
    statement = select(Hero).where(Hero.name == "Spider-Youngster")
    results = session.exec(statement)
    hero = results.first()
    if hero is None:
        print("There's no hero named Spider-Youngster")

SQLModel: Related Tables

1. Defining Models with Foreign Keys

When working with related tables, you define separate SQLModel classes for each table and establish connections using Foreign Key constraints.
A Foreign Key in one table (the child table) refers to the Primary Key of another table (the parent table), creating a link between them.

Team Model Definition:

id: Primary key, auto-incrementing integer.
name: String, indexed for faster lookups.
headquarters: String.

from sqlmodel import Field, SQLModel, create_engine

class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

Hero Model Definition:

id, name, secret_name, age: Standard fields.
team_id: This field acts as the foreign key.

team_id: int | None = Field(default=None, foreign_key="team.id")
The foreign_key argument's value is a string, specifically the database table name ("team") followed by . and the primary key column name ("id") of the parent table (Team). It is not the name of your Python class.
The type hint int | None indicates that a hero might not belong to a team yet.

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
    team_id: int | None = Field(default=None, foreign_key="team.id")

2. Creating Database Tables

After defining your models, you can create the corresponding tables in the database.
SQLModel.metadata.create_all(engine) inspects all SQLModel classes marked with table=True and creates tables for them if they don't already exist.

Example:

from app.core.db import engine # Assuming 'engine' is defined elsewhere

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

def main():
    create_db_and_tables()

if __name__ == "__main__":
    main()

3. Creating Data with Foreign Keys

When creating related data, you typically first create the parent objects (e.g., Teams), commit them to get their primary keys, and then use those primary keys for the foreign key fields in child objects (e.g., Heroes).

Example (Creating Teams and Heroes with team_id):

def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret's Bar")

    session.add(team_preventers)
    session.add(team_z_force)
    session.commit()

    # At this point, team_preventers.id and team_z_force.id will be populated by the DB
    hero_deadpond = Hero(
        name="Deadpond", secret_name="Dive Wilson", team_id=team_z_force.id
    )
    hero_rusty_man = Hero(
        name="Rusty-Man",
        secret_name="Tommy Sharp",
        age=48,
        team_id=team_preventers.id,
    )
    hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")

    session.add(hero_deadpond)
    session.add(hero_rusty_man)
    session.add(hero_spider_boy)
    session.commit()

    # Refresh objects to get latest DB values including allocated IDs and relationships
    session.refresh(hero_deadpond)
    session.refresh(hero_rusty_man)
    session.refresh(hero_spider_boy)

    print("Created hero:", hero_deadpond)
    print("Created hero:", hero_rusty_man)
    print("Created hero:", hero_spider_boy)

4. Selecting Data from Related Tables

To retrieve data that spans across related tables, you use the .where() clause to join them based on their foreign key relationship.
When you select(Hero, Team), results will yield tuples where each tuple contains a Hero object and a Team object.
Example (Selecting Heroes and their Teams):

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero, Team).where(Hero.team_id == Team.id)                results = session.exec(statement)
        for hero, team in results:
            print("Hero:", hero, "Team:", team)

5. Updating Related Data

To update the relationship of an object (e.g., moving a hero to a different team), you simply modify its foreign key field, add the object to the session, and commit.
Example (Changing Spider-Boy's team):


def create_heroes(): # This example seems to be reusing the create function name for an update operation     
with Session(engine) as session:         # Assuming hero_spider_boy and team_preventers are already in context or re-fetched       
    hero_spider_boy.team_id = team_preventers.id # Assign new team's ID        
    session.add(hero_spider_boy)
    session.commit()
    session.refresh(hero_spider_boy)
    print("Updated hero:", hero_spider_boy)

6. Deleting Related Data

Deletion of a row follows the same procedure as basic deletion. If there are foreign key constraints set to ON DELETE CASCADE, deleting a parent record might automatically delete child records. In SQLModel, you explicitly delete objects.

Example (Deleting a Hero):

def delete_heroes():
    with Session(engine) as session:
        # Get the row from DB
        statement = select(Hero).where(Hero.name == "Spider-Youngster")
        results = session.exec(statement)
        hero = results.one()
        print("Hero: ", hero)

    # Delete the row from DB
    session.delete(hero)
    session.commit()
    print("Deleted hero:", hero)

    # Try reading the deleted row from DB (should return None)
    statement = select(Hero).where(Hero.name == "Spider-Youngster")
    results = session.exec(statement)
    hero = results.first()
    # ... (implied check for None)

7. Relationship Attributes (Advanced Linking)

Beyond simple foreign keys, SQLModel (via SQLAlchemy) allows you to define relationship attributes that enable direct object access between related models without manually joining tables in queries.
Key Characteristics:
- Relationship attributes exist only in Python objects; they do not correspond to columns in the database table.
- Their value can be a single related object, a list of related objects, or None.
- They facilitate navigation between linked objects.

Team Model with Relationship:

heroes: list["Hero"] = Relationship(back_populates="team")
- This creates a list attribute heroes on the Team object, containing all Hero objects associated with that team.
- back_populates="team" links this relationship to the team attribute defined in the Hero model.

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine

class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str
    heroes: list["Hero"] = Relationship(back_populates="team")

Hero Model with Relationship:

team_id: This remains the actual foreign key column in the database.
team: Team | None = Relationship(back_populates="heroes")

This creates a team attribute on the Hero object, which will hold the associated Team object.
back_populates="heroes" links this to the heroes attribute in the Team model.

class Hero(SQLModel, table=True): 
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
    team_id: int | None = Field(default=None, foreign_key="team.id") 
# Actual DB foreign key team: Team | None = Relationship(back_populates="heroes") # Python-only relationship

8. Creating Instances with Relationship Attributes

When Relationship attributes are defined, you can assign related objects directly instead of individual foreign key IDs.
SQLAlchemy (underlying SQLModel) is smart enough to figure out the correct order of insertion and automatically commit dependent objects.

Example (Assigning a Team object directly to hero.team):

def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret's Bar")

    # Use relationship instead of foreign key (team=team_z_force)
    hero_deadpond = Hero(
        name="Deadpond", secret_name="Dive Wilson", team=team_z_force
    )
    hero_rusty_man = Hero(
        name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers
    )
    hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")

    session.add(hero_deadpond)
    session.add(hero_rusty_man)
    session.add(hero_spider_boy)
    session.commit()

    session.refresh(hero_deadpond)
    session.refresh(hero_rusty_man)
    session.refresh(hero_spider_boy)

    print("Created hero:", hero_deadpond)
    print("Created hero:", hero_rusty_man)
    print("Created hero:", hero_spider_boy)

Note on Implicit Commit: You don't explicitly session.add() and session.commit() the team_preventers or team_z_force objects separately before using them with heroes. SQLAlchemy automatically detects that these related team objects also need to be committed and handles them in the correct order.

9. Creating Heroes First and Assigning to a Team

You can also create child objects (Heroes) first and then assign them to a parent's (Team's) relationship list.
Similarly, SQLAlchemy will manage the implicit commits.

Example (Assigning heroes to team_wakaland.heroes list):

def create_heroes():
    with Session(engine) as session:
        hero_black_lion = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
        hero_sure_e = Hero(name="Princess Sure-E", secret_name="Sure-E")

    team_wakaland = Team(
        name="Wakaland",
        headquarters="Wakaland Capital City",
        heroes=[hero_black_lion, hero_sure_e], # Assign heroes directly
    )

    session.add(team_wakaland)
    session.commit()
    session.refresh(team_wakaland)
    print("Team Wakaland:", team_wakaland)

Note on Implicit Commit: Here, hero_black_lion and hero_sure_e are committed implicitly when team_wakaland is added and committed, and their team_id will be correctly populated.

10. Adding More Heroes to an Existing Team

To add more heroes to a team after it's been created, you can append new Hero instances to the team's heroes relationship list.
After modifying the relationship collection, ensure to add the parent object (team_preventers) back to the session and commit. Then refresh the newly added heroes.

Example:

def create_heroes(): # Continues from a scenario where team_preventers exists
    with Session(engine) as session:
        # Assuming team_preventers is already loaded or fetched from the session
        hero_tarantula = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
        hero_dr_weird = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
        hero_cap = Hero(
            name="Captain North America", secret_name="Esteban Rogelios", age=93
        )

    team_preventers.heroes.append(hero_tarantula)
    team_preventers.heroes.append(hero_dr_weird)
    team_preventers.heroes.append(hero_cap)

    session.add(team_preventers) # Add the modified team object
    session.commit()

    # Refresh new heroes to get their team_id and other DB-generated values
    session.refresh(hero_tarantula)
    session.refresh(hero_dr_weird)
    session.refresh(hero_cap)

    print("Preventers new hero:", hero_tarantula)
    print("Preventers new hero:", hero_dr_weird)
    print("Preventers new hero:", hero_cap)

Committing the parent team_preventers will cause the newly appended hero objects to be committed, and their team_id attributes will be automatically populated by the database.

SQLModel Operations: CRUD and Related Tables

SQLModel: Read, Update, Delete Operations

1. Reading Data

Retrieving the First Result (.first())

Retrieving Exactly One Result (.one())

Selecting with a Condition (.where())

Shorter Selection by Primary Key (session.get())

Retrieving All Results (.all())

Limiting Results (.limit())

Offsetting Results (.offset())

Combining limit, offset, and where

2. Adding More Data

3. Updating Data

4. Deleting Data

SQLModel: Related Tables

1. Defining Models with Foreign Keys

2. Creating Database Tables

3. Creating Data with Foreign Keys

4. Selecting Data from Related Tables

5. Updating Related Data

6. Deleting Related Data

7. Relationship Attributes (Advanced Linking)

8. Creating Instances with Relationship Attributes

9. Creating Heroes First and Assigning to a Team

10. Adding More Heroes to an Existing Team

Retrieving the First Result (`.first()`)

Retrieving Exactly One Result (`.one()`)

Selecting with a Condition (`.where()`)

Shorter Selection by Primary Key (`session.get()`)

Retrieving All Results (`.all()`)

Limiting Results (`.limit()`)

Offsetting Results (`.offset()`)

Combining `limit`, `offset`, and `where`