SQLAlchemy & SQLModel

SQLAlchemy Core – Engine, Connections & Queries

1. Engine Creation

• Syntax:

  from sqlalchemy import create_engine
  # SQLite (in-memory DB)
  engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)
  # PostgreSQL
  engine = create_engine("postgresql+psycopg2://user:password@hostname/database_name", echo=True)

• Parameters:

  • Connection string: Informs SQLAlchemy which database and driver to use.

  • echo=True: Prints all SQL commands executed (useful for debugging).

• Return Value: An Engine object, the primary access point to the database.

• Examples Explained:

  • The first example creates a temporary SQLite database in memory, which is lost after the program ends.

  • The second example connects to an actual PostgreSQL database using username, password, and database name.

2. Connecting & Running SQL

• Syntax:

  from sqlalchemy import text
  with engine.connect() as conn:
      result = conn.execute(text("select 'hello world'"))
      print(result.all())

• Parameters:

  • text("…"): Wraps raw SQL commands.

  • conn.execute(…): Executes the SQL command.

• Return Value: A Result object. Calling .all() retrieves all rows.

• Example Explained: This example prints "hello world" by executing a straightforward SQL query, confirming successful connection.

3. Insert Data & Commit

• Commit as you go:

  with engine.connect() as conn:
      conn.execute(text("CREATE TABLE some_table (x int, y int)"))
      conn.execute(  
          text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),  
          [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
      )
      conn.commit()

• Explanation:

  1. Creates a table called some_table with columns x and y.

  2. Inserts two rows into the table.

  3. commit() saves changes permanently to the database.

  • Transaction style commit:

  with engine.begin() as conn:
      conn.execute(
          text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
          [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
      )

• Explanation:

  • engine.begin() automatically commits on successful completion of the transaction.

  • Inserts additional rows into the same table.

4. Fetching Data

• Basic fetch:

  with engine.connect() as conn:
      result = conn.execute(text("SELECT x, y FROM some_table"))
      for row in result:
          print(f"x: {row.x}, y: {row.y}")

• Example Output:

  • x: 1, y: 1

  • x: 2, y: 4

  • x: 6, y: 8

  • x: 9, y: 10

  • Fetching with filters:

  with engine.connect() as conn:
      result = conn.execute(
          text("SELECT x, y FROM some_table WHERE y > :y"),
          {"y": 2}
      )
      for row in result:
          print(f"x: {row.x}, y: {row.y}")

• Explanation:

  • Uses :y as a placeholder, returning rows where y > 2.

  • Protects against SQL injection by binding parameters safely.

SQLAlchemy Core – MetaData & Tables

1. MetaData

• Syntax:

  from sqlalchemy import MetaData
  metadata_obj = MetaData()

• What it does:

  • MetaData is a container holding information about tables, functioning like a notebook for SQLAlchemy.

2. Defining a Table

• Syntax:

  from sqlalchemy import Table, Column, Integer, String
  user_table = Table(
      "user_account",
      metadata_obj,
      Column("id", Integer, primary_key=True),
      Column("name", String(30)),
      Column("fullname", String),
  )

• Parameters:

  • "user_account": Name of the actual table.

  • metadata_obj: Binds this table definition to the created MetaData.

  • Column definitions: Details attributes of each column including data types and constraints, e.g., primary_key=True makes that column a primary key.

• Return Value: Returns a Table object usable in queries.

3. Accessing Table Columns

• Syntax:

  user_table.c.name
  user_table.c.keys()
  user_table.primary_key

• Explanation:

  • .c: Provides access to table columns.

  • .keys(): Returns a list of all column names.

  • .primary_key: Displays primary key columns of the table.

4. Foreign Keys (Relationships Between Tables)

• Syntax:

  from sqlalchemy import ForeignKey
  address_table = Table(
      "address",  
      metadata_obj,
      Column("id", Integer, primary_key=True),
      Column("user_id", ForeignKey("user_account.id"), nullable=False),
      Column("email_address", String, nullable=False),
  )

• Parameters:

  • ForeignKey: Links user_id in address_table to id in user_account, ensuring that an address must reference an existing user.

  • nullable=False: Prohibits empty values in this column.

5. Create Tables in the Database

• Syntax:

  metadata_obj.create_all(engine)

• Explanation:

  • Reads all table definitions from metadata_obj and creates these tables in the connected database.

📌 Summary of Section 2:

• MetaData = notebook of table definitions.

• Table = blueprint for database tables.

• Column = defines fields within the tables.

• ForeignKey = establishes relationships between tables.

• create_all() = function to build actual tables within the database.

SQLAlchemy ORM – Mapped Classes

Overview of ORM

• The Object Relational Mapper (ORM) allows treating database tables as Python classes, abstracting the SQL writing process. This method enables the creation of Python classes representing database tables, allowing SQLAlchemy to handle the SQL operations automatically behind the scenes.

1. Setting Up the ORM

• Syntax:

  from sqlalchemy.orm import DeclarativeBase
  class Base(DeclarativeBase):
      pass

• Explanation:

  • DeclarativeBase: A specialized SQLAlchemy class which tracks all ORM models.

  • Base serves as the parent class for all table definitions in the program.

2. Defining a User Class

• Syntax:

  from typing import List, Optional
  from sqlalchemy import Integer, String, ForeignKey
  from sqlalchemy.orm import Mapped, mapped_column, relationship
  class User(Base):
      __tablename__ = "user_account"
      id: Mapped[int] = mapped_column(primary_key=True)
      name: Mapped[str] = mapped_column(String(30))
      fullname: Mapped[Optional[str]]
      addresses: Mapped[List["Address"]] = relationship(back_populates="user")
      def __repr__(self) -> str:
          return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

• Parameters:

  • __tablename__: Sets the name of the table corresponding to this object in the database.

  • mapped_column(): Indicates columns in the table alongside their types.

  • relationship(): Establishes a link between this class and another class, allowing for easy relationship management in the ORM context.

• Return Value: A mapped User class represents a user in the user_account table, capable of working with multiple related Address objects.

3. Defining an Address Class

• Syntax:

  class Address(Base):
      __tablename__ = "address"
      id: Mapped[int] = mapped_column(primary_key=True)
      email_address: Mapped[str]
      user_id = mapped_column(ForeignKey("user_account.id"))
      user: Mapped[User] = relationship(back_populates="addresses")
      def __repr__(self) -> str:  
          return f"Address(id={self.id!r}, email_address={self.email_address!r})"

• Parameters:

  • ForeignKey("user_account.id"): Associates each address to a specific user, requiring that the user exists.

  • relationship(...): Establishes a two-way relationship between the User and Address classes.

• Explanation: This class maps the address table, portraying addresses which belong to one user, while enabling a user to have multiple addresses.

4. Creating Tables from ORM Models

• Syntax:

  from app.core.db import engine
  Base.metadata.create_all(engine)

• Explanation:

  • Scans through all ORM classes inheriting from Base, generating the corresponding tables within the database.

📌 Summary of Section 3:

• ORM: Facilitates using Python classes rather than raw SQL queries.

• Models define relationships (User ↔ Address shows one-to-many).

• Base.metadata.create_all(engine) builds the defined tables in the database.

SQLAlchemy Core – DML

1. Insert Data

• Basic Insert:

  from sqlalchemy import insert
  stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")
  print(stmt)
  with engine.connect() as conn:
      result = conn.execute(stmt)
      conn.commit()
      print(result.inserted_primary_key)

• Explanation:

  • Adds a new row to the user_account table.

  • .values(...) specifies the row to insert.

  • inserted_primary_key shows the ID for the recently added row.

• Example Output: Inserts new user: Spongebob.

2. Insert Multiple Rows (easier way)

• Syntax:

  with engine.connect() as conn:
      result = conn.execute(
          insert(user_table),
          [
              {"name": "sandy", "fullname": "Sandy Cheeks"},
              {"name": "patrick", "fullname": "Patrick Star"},
          ],
      )
      conn.commit()

• Explanation:

  • Allows inserting two rows simultaneously (sandy, patrick).

3. Advanced Insert with Subquery

Scalar Subquery Example

• Syntax:

  from sqlalchemy import select, bindparam
  scalar_subq = (  
      select(user_table.c.id)  
      .where(user_table.c.name == bindparam("username"))  
      .scalar_subquery()  
  )

• Explanation:

  • Crafts a subquery that retrieves a user's ID based on their name.

  • bindparam(...) enables safe variable input.

Insert using subquery

• Syntax:

  with engine.connect() as conn:
      result = conn.execute(
          insert(address_table).values(user_id=scalar_subq),
          [
              {"username": "spongebob", "email_address": "spongebob@sqlalchemy.org"},
              {"username": "sandy", "email_address": "sandy@sqlalchemy.org"},
              {"username": "sandy", "email_address": "sandy@squirrelpower.org"},
          ],
      )
      conn.commit()

• Explanation:

  • Retrieves each user's ID by name while inserting associated addresses.

4. Insert with Select

• Syntax:

  select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
  insert_stmt = insert(address_table).from_select([
      "user_id", "email_address"], select_stmt)
  print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))

• Explanation:

  • Selects users and generatively produces email addresses.

  • from_select(...) copies the results directly into another table.

  • returning(...) shows inserted items.

• Why it matters:

  • This method is effective for transferring data between tables, enhancing performance with fewer operations.

5. Select Data

• Basic Select:

  stmt = select(user_table).where(user_table.c.name == "spongebob")
  print(stmt)
  with engine.connect() as conn:
      for row in conn.execute(stmt):
          print(row)

• Explanation:

  • Retrieves all rows where name equals "spongebob".

6. Select Specific Columns

• Syntax:

  from sqlalchemy import func
  stmt = select(("Username: " + user_table.c.name).label("username")).order_by(user_table.c.name)
  with engine.connect() as conn:
      for row in conn.execute(stmt):
          print(f"{row.username}")

• Explanation:

  • Labels output as username and organizes results in alphabetical order.

7. Select with Filters

• Using where:

  print(select(user_table).where(user_table.c.name == "squidward"))

• Filter across tables:

  print(select(address_table.c.email_address)
      .where(user_table.c.name == "squidward")
      .where(address_table.c.user_id == user_table.c.id))

• Explanation:

  • Retrieves the email address by linking two tables, focused on Squidward’s records.

8. Select with Joins

• Join From:

  print(select(user_table.c.name, address_table.c.email_address)
      .join_from(user_table, address_table))

• Join:

  print(select(user_table.c.name, address_table.c.email_address)
      .join(address_table))

• Explanation:

  • The first variation specifies which tables to join explicitly, while the latter allows automatic detection based on foreign keys.

📌 Summary of Section 4:

• insert(): Adds data (single or multiple).

• Subqueries + from_select(): Enable efficient bulk inserts.

• select(): Queries data from the database.

• .where() and .join(): Filters and connects tables for comprehensive data retrieval.

SQLAlchemy ORM – DML (Insert, Select, Update, Delete)

1. Select with ORM

• Select All Users:

  print(select(User))
  row = session.execute(select(User)).first()

• Explanation:

  • Utilizes ORM to build queries fetching complete User objects from the database.

2. Get a User Instance Directly

• Syntax:

  user = session.scalars(select(User)).first()

• Return Value:

  • Instead of fetching a tuple, retrieving direct User instances is streamlined.

3. Select Some Fields

• Syntax:

  row = session.scalars(select(User.name, User.fullname)).first()

• Return Value:

  • Returns a tuple containing (name, fullname).

4. Select with Join

• Combined Select:

  result = session.execute(select(User.name, Address)
      .where(User.id == Address.user_id)
      .order_by(Address.id)).all()

• Explanation:

  • Implements a join operation between the User and Address classes, yielding a collection of tuples (username, Address object).

5. Select with Conditions

• Using and_ / or_:

  from sqlalchemy import and_, or_
  print(select(Address.email_address).where(
      and_(or_(User.name == "squidward", User.name == "sandy"),
      Address.user_id == User.id, )
  ))

• Explanation:

  • Finds email addresses based on conditions regarding user names.

6. Filter By (shortcut)

• Syntax:

  print(select(User).filter_by(name="spongebob", fullname="Spongebob Squarepants"))

• Explanation:

  • filter_by() offers a cleaner method for filtering using keyword arguments.

7. Aggregates (Count, Group By, Having)

• Syntax:

  from sqlalchemy import func
  with engine.connect() as conn:
      result = conn.execute(
          select(User.name, func.count(Address.id).label("count"))
          .join(Address)
          .group_by(User.name)
          .having(func.count(Address.id) > 1)
      )
      print(result.all())

• Explanation:

  • Joins User and Address, groups by user's name, and only displays those with more than one address in the database.

📌 Summary of Section 5:

• ORM queries: Utilize class names and attributes instead of writing raw SQL.

• .scalars(): Provides direct object customization.

• .filter_by(): A user-friendly filtering alternative.

• .join() + group_by() + having(): Simplified query relationships and counting mechanisms.

SQLModel – Overview & Basics

• Overview: SQLModel is a library built using SQLAlchemy and Pydantic, combining their functionalities and simplifying the database interaction through type hints while eliminating excessive boilerplate coding.

1. Defining a Table with SQLModel

• Syntax:

  from sqlmodel import Field, SQLModel
  class Hero(SQLModel, table=True):
      id: int | None = Field(default=None, primary_key=True)
      name: str
      secret_name: str
      age: int | None = None

• Parameters:

  • table=True: Indicates that this class represents a table in the database.

  • Field(...): Defines columns along with their respective attributes such as primary_key=True to establish the primary key.

• Return Value: A mapped class that aligns with both a Python object and a row within a database table.

2. Creating Instances

• Syntax:

  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)

• Explanation: Each instance directly represents one row in the associated database table.

3. Creating the Database & Tables

• Syntax:

  from sqlmodel import SQLModel, Session, create_engine
  from app.core.db import engine
  SQLModel.metadata.create_all(engine)

• Explanation:

  • create_all(engine) facilitates the creation of multiple tables in a single operation by reading definitions established in SQLModel.

4. Adding Data with a Session

• Syntax:

  with Session(engine) as session:
      session.add(hero_1)
      session.add(hero_2)
      session.add(hero_3)
      session.commit()

• Explanation:

  • The Session acts as a workspace where modifications take place.

  • commit() saves the adjustments permanently in the database.

5. Refreshing Instances

• Syntax:

  session.refresh(hero_1)
  session.refresh(hero_2)
  session.refresh(hero_3)

• Explanation:

  • Updates the instance with newly fetched data from the database, such as newly assigned IDs.

6. Preventing SQL Injection

• Bad Example (unsafe):

  user_id = input("Type the user ID: ")
  statement = f"SELECT * FROM hero WHERE id = {user_id};"
  results = database.execute(statement)

• Risk: If the input is malicious, it could lead to disastrous outcomes like database tampering.

  • Safe Example with SQLModel:

  user_id = input("Type the user ID: ")
  session.exec(select(Hero).where(Hero.id == user_id)).all()

• Explanation: Uses parameter binding to evade injection challenges, ensuring malicious inputs aren't executed.

7. Selecting Data

• Select with Filter:

  with Session(engine) as session:
      statement = select(Hero).where(Hero.name == "Spider-Boy")
      hero = session.exec(statement).first()
      print(hero)

• Explanation: Retrieves the primary instance where the Hero's name is "Spider-Boy".

• Select All Rows:

  def select_heroes():
      with Session(engine) as session:
          statement = select(Hero)
          results = session.exec(statement)
          for hero in results:
              print(hero)

• Shortcut Version:

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

• Explanation:

  • The .all() fetches everything right away in a list format.

📌 Summary of Section 6:

• SQLModel integrates SQLAlchemy and Pydantic.

• Tables take the form of Python classes enhanced with type annotations.

• The Session serves as a central handler for inserts, selects, updates, and deletes.

• Built-in defenses against SQL injection are incorporated.

SQLModel – Read, Update, Delete

1. Reading Data

• Read First Row:

  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)

• Explanation: .first() fetches the first matching row, returning None if there are no such entries.

• Read One Row (strict):

  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)

• Explanation: .one() expects a singular match, raising an error on zero or multiple matches.

• Select with WHERE:

  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)

• Explanation: Retrieves the hero record directly based on an ID of 1.

• Select with get (shortcut):

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

• Explanation: This command is a more concise way to achieve the same outcome as a .where() clause.

2. Adding More Data

• Syntax:

  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()

• Explanation: Adds seven new heroes to the database.

3. Reading with Limit & Offset

• Limit:

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

• Explanation: Returns a maximum of three heroes.

• Offset + Limit:

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

• Explanation: Skips the first three heroes and selects the next three in sequence.

• Combining Where + Offset + Limit:

  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)

• Explanation: Filters for heroes older than 32, skips the first match, and limits the output to two results.

4. Updating Rows

• Syntax:

  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)

• Explanation:

  • Locates the record for Spider-Boy.

  • Changes the age to 16, and both saves and refreshes the entry from the database to reflect any updates.

5. Deleting Rows

• Delete Example:

  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)

• Explanation:

  • Finds the specified hero and removes the corresponding row from the database.

• Checking After Deletion:

  def delete_heroes():
      with Session(engine) as session:
          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")

• Explanation: Confirms whether the record has been successfully removed from the database.

📌 Summary of Section 7:

• .first(): Retrieves the first row or None if it doesn't exist.

• .one(): Confirms one row exists or raises an error otherwise.

• .get(): Direct access by primary key for speedy retrieval.

• .limit() and .offset() facilitate pagination options.

• Updating: Locate object → edit attributes → save changes.

• Deleting: Locate object → delete via session → commit changes.

SQLModel – Related Tables & Relationships

1. One-to-Many Relationship (Hero → Team)

• Define Team Table:

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

• Define Hero Table with 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")

• Explanation: Each Hero belongs to one Team via team_id, while a Team can have multiple Heroes associated.

2. Creating Data

• Syntax:

  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()

• Explanation: Two teams are created and initialized in the database.

• Inserting Heroes:

  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()

• Explanation: Associates two heroes under corresponding teams while allowing Spider-Boy to remain unassigned initially.

3. Selecting with Joins

• Syntax:

  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)

• Explanation: Displays the corresponding team for each hero through an established join condition.

4. Relationship Attributes

• Add relationships to Team:

  from sqlmodel import Relationship
  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")

• Add relationship to Hero:

  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")
      team: Team | None = Relationship(back_populates="heroes")

• Explanation: The Relationship(back_populates="...") keeps both sides of the two-way relationship in sync within the ORM layer.

5. Using Relationships

• Creating Heroes with Relationships:

  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")
      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()

• Explanation: Instead of assigning IDs manually, full team objects are linked directly to the hero objects, while SQLModel automatically manages foreign key associations.

6. Many-to-Many Relationship

• Understanding Many-to-Many:

  • Illustrating the concept, a hero may belong to multiple teams, and teams can be composed of multiple heroes. To realize this in the database schema, a link table is essential.

Link Table

• Syntax:

  class HeroTeamLink(SQLModel, table=True):
      team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)
      hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)

• Explanation: This table serves the role of the association table that facilitates the many-to-many relationship between heroes and teams, with both fields constituting a composite primary key.

Create Data for Many-to-Many

• Syntax:

  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")
      hero_deadpond = Hero(name="Deadpond", secret_name="Dive Wilson", teams=[team_z_force, team_preventers])
      hero_rusty_man = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, teams=[team_preventers])
      hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", teams=[team_preventers])
      session.add(hero_deadpond)
      session.add(hero_rusty_man)
      session.add(hero_spider_boy)
      session.commit()

• Explanation: Each hero is associated with their respective teams during instantiation, showcasing multi-team membership capabilities.

7. Updating Many-to-Many

• Add Spider-Boy to Z-Force:

  with Session(engine) as session:
      hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one()
      team_z_force = session.exec(select(Team).where(Team.name == "Z-Force")).one()
      team_z_force.heroes.append(hero_spider_boy)
      session.add(team_z_force)
      session.commit()
      print("Updated Spider-Boy's Teams:", hero_spider_boy.teams)
      print("Z-Force heroes:", team_z_force.heroes)

• Explanation: This code snippet shows how to manage the relationship by appending the hero to Z-Force's list of heroes.

• Remove Spider-Boy from Z-Force:

  with Session(engine) as session:
      hero_spider_boy = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).one()
      team_z_force = session.exec(select(Team).where(Team.name == "Z-Force")).one()
      hero_spider_boy.teams.remove(team_z_force)
      session.add(team_z_force)
      session.commit()
      print("Reverted Z-Force's heroes:", team_z_force.heroes)
      print("Reverted Spider-Boy's teams:", hero_spider_boy.teams)

• Explanation: This process details how to detach the hero from Z-Force, reaffirming that although no longer on Z-Force's team list, Spider-Boy still belongs to the Preventers.

📌 Summary of Section 10:

• Many-to-Many: Requires a linking table (illustrated by HeroTeamLink).

• Relationships established through Relationship(..., link_model=...) connect the two tables.

• Utilization of .append() and .remove() allows for object management regarding their relationships, with SQLModel autonomously managing updates in the linking table.