Remove Data Connections¶
We currently have a team table:
| id | name | headquarters |
|---|---|---|
| 1 | Preventers | Sharp Tower |
| 2 | Z-Force | Sister Margaret's Bar |
And a hero table:
| id | name | secret_name | age | team_id |
|---|---|---|---|---|
| 1 | Deadpond | Dive Wilson | null | 2 |
| 2 | Rusty-Man | Tommy Sharp | 48 | 1 |
| 3 | Spider-Boy | Pedro Parqueador | null | 1 |
Let's see how to remove connections between rows in tables.
We will continue with the code from the previous chapter.
👀 Full file preview
from sqlmodel import Field, 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 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") sqlite_file_name = "database.db" sqlite_url = f"sqlite:///{sqlite_file_name}" engine = create_engine(sqlite_url, echo=True) def create_db_and_tables(): SQLModel.metadata.create_all(engine) 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() 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() 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) hero_spider_boy.team_id = team_preventers.id session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("Updated hero:", hero_spider_boy) def main(): create_db_and_tables() create_heroes() if __name__ == "__main__": main() 🤓 Other versions and variants
from typing import Optional from sqlmodel import Field, Session, SQLModel, create_engine class Team(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) name: str = Field(index=True) headquarters: str class Hero(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) name: str = Field(index=True) secret_name: str age: Optional[int] = Field(default=None, index=True) team_id: Optional[int] = Field(default=None, foreign_key="team.id") sqlite_file_name = "database.db" sqlite_url = f"sqlite:///{sqlite_file_name}" engine = create_engine(sqlite_url, echo=True) def create_db_and_tables(): SQLModel.metadata.create_all(engine) 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() 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() 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) hero_spider_boy.team_id = team_preventers.id session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("Updated hero:", hero_spider_boy) def main(): create_db_and_tables() create_heroes() if __name__ == "__main__": main() Break a Connection¶
We don't really have to delete anything to break a connection. We can just assign None to the foreign key, in this case, to the team_id.
Let's say Spider-Boy is tired of the lack of friendly neighbors and wants to get out of the Preventers.
We can simply set the team_id to None, and now it doesn't have a connection with the team:
# Code above omitted 👆 def create_heroes(): with Session(engine) as session: # Code here omitted 👈 hero_spider_boy.team_id = None session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("No longer Preventer:", hero_spider_boy) # Code below omitted 👇 👀 Full file preview
from sqlmodel import Field, 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 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") sqlite_file_name = "database.db" sqlite_url = f"sqlite:///{sqlite_file_name}" engine = create_engine(sqlite_url, echo=True) def create_db_and_tables(): SQLModel.metadata.create_all(engine) 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() 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() 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) hero_spider_boy.team_id = team_preventers.id session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("Updated hero:", hero_spider_boy) hero_spider_boy.team_id = None session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("No longer Preventer:", hero_spider_boy) def main(): create_db_and_tables() create_heroes() if __name__ == "__main__": main() 🤓 Other versions and variants
from typing import Optional from sqlmodel import Field, Session, SQLModel, create_engine class Team(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) name: str = Field(index=True) headquarters: str class Hero(SQLModel, table=True): id: Optional[int] = Field(default=None, primary_key=True) name: str = Field(index=True) secret_name: str age: Optional[int] = Field(default=None, index=True) team_id: Optional[int] = Field(default=None, foreign_key="team.id") sqlite_file_name = "database.db" sqlite_url = f"sqlite:///{sqlite_file_name}" engine = create_engine(sqlite_url, echo=True) def create_db_and_tables(): SQLModel.metadata.create_all(engine) 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() 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() 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) hero_spider_boy.team_id = team_preventers.id session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("Updated hero:", hero_spider_boy) hero_spider_boy.team_id = None session.add(hero_spider_boy) session.commit() session.refresh(hero_spider_boy) print("No longer Preventer:", hero_spider_boy) def main(): create_db_and_tables() create_heroes() if __name__ == "__main__": main() Again, we just assign a value to that field attribute team_id, now the value is None, which means NULL in the database. Then we add() the hero to the session, and then commit().
Next we refresh() it to get the recent data, and we print it.
Running that in the command line will output:
$ python app.py // Previous output omitted 😉 // Update the hero INFO Engine UPDATE hero SET team_id=? WHERE hero.id = ? INFO Engine [cached since 0.07753s ago] (None, 3) // Commit the session INFO Engine COMMIT // Automatically start a new transaction INFO Engine BEGIN (implicit) // Refresh the hero INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.team_id FROM hero WHERE hero.id = ? INFO Engine [cached since 0.1661s ago] (3,) // Print the hero without a team No longer Preventer: id=3 secret_name='Pedro Parqueador' team_id=None name='Spider-Boy' age=None That's it, we now removed a connection between rows in different tables by unsetting the foreign key column. 💥