C

SQLModel Operations & Relationships

SQLModel: Read, Update, Delete & Related Tables

Read Operations

Reading the First Result

  • The results.first() method is used to retrieve the first item from a query result.

  • If the database query returns no results, first() will return None.

  • Example Code:
    python 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)

Reading Exactly One Result

  • The results.one() method is used when you expect an exact single item to be returned from the database.

  • If the query returns less than one item (zero items) or more than one item, results.one() will raise an error.

  • Example Code:
    python 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 where Clause

  • The where() method is used to filter query results based on specified conditions.

  • Example Code (filtering by ID):
    python 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)

Selecting with get() (Shorter Version for Primary Key)

  • The session.get(Model, primary_key_value) method provides a shorthand for retrieving a model instance by its primary key.

  • It implicitly uses a where clause on the primary key.

  • If no row is found with the given primary key, get() returns None.

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

Selecting All Results

  • The results.all() method retrieves all items matching the query statement.

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

Selecting with limit()

  • The limit(N) method restricts the number of results returned by the query to a maximum of N items.

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

Selecting with offset() and limit()

  • The offset(N) method skips the first N results before returning the remaining ones.

  • It is often used in conjunction with limit() for pagination.

  • Example Code (skipping 3, then taking the next 3):
    python 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 Code (getting the next 3 after the previous offset):
    python def select_heroes(): with Session(engine) as session: statement = select(Hero).offset(6).limit(3) results = session.exec(statement) heroes = results.all() print(heroes)

Combining limit(), offset(), and where()

  • These methods can be chained together to apply multiple filtering and pagination criteria.

  • The order of where(), offset(), and limit() matters:

    • where() applies first to filter the total set.

    • offset() then skips records from the filtered set.

    • limit() finally restricts the number of remaining records.

  • Example Code (finding heroes older than 32, skipping the first, then taking the next 2):
    python 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)

Create (Add More Data)

  • New instances of a SQLModel class are created and then added to the session using session.add().

  • session.commit() persists 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()

Update Operations

  • To update a row:

    1. Retrieve the desired object(s) from the database.

    2. Modify the attributes of the retrieved object(s).

    3. Add the modified object(s) back to the session using session.add() (even if it's already in the session, this signals it as dirty).

    4. Commit the session using session.commit().

    5. Optionally, session.refresh(obj) reloads the object's attributes from the database to reflect any changes made by the database (e.g., auto-updated timestamps, default values).

  • Example Code (updating a hero'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
    session.add(hero)
    session.commit()
    session.refresh(hero)
    print("Updated hero:", hero)

Delete Operations

  • To delete a row:

    1. Retrieve the object to be deleted from the database.

    2. Call session.delete(obj) with the retrieved object.

    3. Commit the session using session.commit().

  • After session.commit(), the object is marked as deleted in the DB. Attempting to query it again will return None (if using first() or get()) or raise an error (if using one()).

  • Example Code (deleting a hero):

    def delete_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Youngster")
        results = session.exec(statement)
        hero = results.one()
        print("Hero: ", hero)
        session.delete(hero)
        session.commit()
        print("Deleted hero:", hero)
    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

Defining Models with Foreign Keys

  • Related tables are established using ForeignKey fields in SQLModel classes.

  • The foreign_key argument in Field() specifies the target table and its primary key (e.g., "team.id").

  • Important: The foreign_key string uses the actual database table name (usually lowercase and pluralized by default, or explicitly defined with __tablename__), not the Python class name.

  • Team Model Example:

    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
# It's name "Team" here but is "team" in database

  • Hero Model Example (with team_id as Foreign Key):

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")
Creating Database Tables

  • The SQLModel.metadata.create_all(engine) method generates all defined tables in the database.

  • Example Code:

    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()
Creating Data for Related Tables

  • First, create and commit instances of the