fromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:int|None=Field(default=None,index=True)team_id:int|None=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Team|None=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
π€ Other versions and variants
fromtypingimportOptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
fromtypingimportList,OptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:List["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
With this configuration, when we delete a team, SQLModel (actually SQLAlchemy) will:
Make sure the objects for the related records are loaded, in this case, the heroes. If they are not loaded, it will send a SELECT query to the database to get them.
Send a DELETE query to the database including each related record (each hero).
Finally, delete the initial record (the team) with another DELETE query.
This way, the internal Python code will take care of deleting the related records, by emitting the necessary SQL queries for each of them.
Tip
The cascade_delete parameter is set in the Relationship(), on the model that doesn't have a foreign key.
Technical Details
Setting cascade_delete=True in the Relationship() will configure SQLAlchemy to use cascade="all, delete-orphan", which is the most common and useful configuration when wanting to cascade deletes.
fromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:int|None=Field(default=None,index=True)team_id:int|None=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Team|None=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
π€ Other versions and variants
fromtypingimportOptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
fromtypingimportList,OptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:List["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
Now, when we create the tables in the database, the team_id column in the Hero table will have an ON DELETE CASCADE in its definition at the database level.
This will configure the database to automatically delete the records (heroes) when the related record (team) is deleted.
Tip
The ondelete parameter is set in the Field(), on the model that has a foreign key.
Some databases don't support foreign key constraints.
For example, SQLite doesn't support them by default. They have to be manually enabled with a custom SQL command:
PRAGMA foreign_keys = ON;
So, in general is a good idea to have both cascade_delete and ondelete configured.
Tip
You will learn more about how to disable the default automatic SQLModel (SQLAlchemy) behavior and only rely on the database down below, in the section about passive_deletes.
cascade_delete on Relationship() and ondelete on Field()ΒΆ
Just a note to remember... π€
ondelete is put on the Field() with a foreign key. On the "many" side in "one-to-many" relationships.
We can confirm that after deleting the teamWakaland, the heroes Black Lion and Princess Sure-E are also deleted.
If we try to select them from the database, we will no longer find them.
# Code above omitted πdefselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)# Code below omitted π
π Full file preview
fromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:int|None=Field(default=None,index=True)team_id:int|None=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Team|None=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
π€ Other versions and variants
fromtypingimportOptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
fromtypingimportList,OptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:List["Hero"]=Relationship(back_populates="team",cascade_delete=True)classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="CASCADE")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion not found:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E not found:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
Run the Program with cascade_delete=True and ondelete="CASCADE"ΒΆ
We can confirm everything is working by running the program.
fast βpython app.py π¬ Some boilerplate and previous output omitted π π¬ The team table is created as beforeCREATE TABLE team ( id INTEGER NOT NULL, name VARCHAR NOT NULL, headquarters VARCHAR NOT NULL, PRIMARY KEY (id) )
π¬ The hero table is created with the ON DELETE CASCADE ππ¬ In SQLite, it also includes REFERENCES team (id), this is needed by SQLite to work with the ON DELETE CASCADE properly.π¬ SQLAlchemy takes care of setting it up for us to make sure it works π€CREATE TABLE hero ( id INTEGER NOT NULL, name VARCHAR NOT NULL, secret_name VARCHAR NOT NULL, age INTEGER, team_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(team_id) REFERENCES team (id) ON DELETE CASCADE )
π¬ We select the team WakalandINFO Engine SELECT team.id, team.name, team.headquarters FROM team WHERE team.name = ? INFO Engine [generated in 0.00014s] ('Wakaland',)
π¬ Then, because of cascade_delete, right before deleting Wakaland, SQLAlchemy loads the heroesINFO Engine SELECT hero.id AS hero_id, hero.name AS hero_name, hero.secret_name AS hero_secret_name, hero.age AS hero_age, hero.team_id AS hero_team_id FROM hero WHERE ? = hero.team_id INFO Engine [generated in 0.00020s] (3,)
π¬ Next, before deleting the Wakaland team, it sends a DELETE statement including each related hero: Black Lion and Princess Sure-E, with IDs 4 and 5INFO Engine DELETE FROM hero WHERE hero.id = ? INFO Engine [generated in 0.00022s] [(4,), (5,)]
π¬ After that, it will send the delete for the team Wakaland with ID 3INFO Engine DELETE FROM team WHERE team.id = ? INFO Engine [generated in 0.00017s] (3,)
π¬ Print the deleted teamDeleted team: name='Wakaland' id=3 headquarters='Wakaland Capital City'
π¬ Finally, we try to select the heroes from Wakaland, Black Lion and Princess Sure-E and print them, but they are now deletedBlack Lion not found: None Princess Sure-E not found: None
fromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team")classHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:int|None=Field(default=None,index=True)team_id:int|None=Field(default=None,foreign_key="team.id",ondelete="SET NULL")team:Team|None=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion has no team:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E has no team:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
π€ Other versions and variants
fromtypingimportOptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team")classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="SET NULL")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion has no team:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E has no team:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
fromtypingimportList,OptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,selectclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:List["Hero"]=Relationship(back_populates="team")classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="SET NULL")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)defcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion has no team:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E has no team:",hero)defmain():create_db_and_tables()create_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
The configuration above is setting the team_id column from the Hero table to have an ON DELETE SET NULL.
This way, when someone deletes a team from the database using SQL directly, the database will go to the heroes for that team and set team_id to NULL (if the database supports it).
Tip
The foreign key should allow None values (NULL in the database), otherwise you would end up having an Integrity Error by violating the NOT NULL constraint.
What happens if you don't use ondelete="SET NULL", don't set anything on cascade_delete, and delete a team?
The default behavior is that SQLModel (actually SQLAlchemy) will go to the heroes and set their team_id to NULL from the Python code.
So, by default, those team_id fields will be set to NULL.
But if someone goes to the database and manually deletes a team, the heroes could end up with a team_id pointing to a non-existing team.
Adding the ondelete="SET NULL" configures the database itself to also set those fields to NULL.
But if you delete a team from code, by default, SQLModel (actually SQLAlchemy) will update those team_id fields to NULL even before the database SET NULL takes effect.
Let's confirm it all works by running the program now:
fast βpython app.py π¬ Some boilerplate and previous output omitted π π¬ The hero table is created with the ON DELETE SET NULL ππ¬ In SQLite, it also includes: REFERENCES team (id). This REFERENCES is needed by SQLite to work with the ON DELETE CASCADE properly.π¬ SQLModel with SQLAlchemy takes care of setting it up for us to make sure it works π€CREATE TABLE hero ( id INTEGER NOT NULL, name VARCHAR NOT NULL, secret_name VARCHAR NOT NULL, age INTEGER, team_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(team_id) REFERENCES team (id) ON DELETE SET NULL )
π¬ We select the team WakalandINFO Engine SELECT team.id, team.name, team.headquarters FROM team WHERE team.id = ? INFO Engine [generated in 0.00010s] (3,) Team Wakaland: id=3 name='Wakaland' headquarters='Wakaland Capital City'
π¬ Then, right before deleting Wakaland, the heroes are loaded automaticallyINFO Engine SELECT hero.id AS hero_id, hero.name AS hero_name, hero.secret_name AS hero_secret_name, hero.age AS hero_age, hero.team_id AS hero_team_id FROM hero WHERE ? = hero.team_id INFO Engine [generated in 0.00020s] (3,)
π¬ Next, before deleting the Wakaland team, it sends an UPDATE statement including each related hero: Black Lion and Princess Sure-E, with IDs 4 and 5, to set their team_id to NULL. This is not the SET NULL we added, this is just the default SQLModel (SQLAlchemy) behavior.INFO Engine UPDATE hero SET team_id=? WHERE hero.id = ? INFO Engine [generated in 0.00009s] [(None, 4), (None, 5)]
π¬ After that, it will send the delete for the team Wakaland with ID 3INFO Engine DELETE FROM team WHERE team.id = ? INFO Engine [generated in 0.00017s] (3,)
π¬ Print the deleted teamDeleted team: name='Wakaland' id=3 headquarters='Wakaland Capital City'
π¬ Finally, we select the heroes Black Lion and Princess Sure-E and print them, they no longer have a teamBlack Lion has no team: age=35 id=4 name='Black Lion' secret_name='Trevor Challa' team_id=None Princess Sure-E has no team: age=None id=5 name='Princess Sure-E' secret_name='Sure-E' team_id=None
The team Wakaland was deleted and all of its heroes were left without a team, or in other words, with their team_id set to NULL, but still kept in the database! π€
In the previous examples we configured ondelete with CASCADE and SET NULL to configure the database to handle the deletion of related records automatically. But we actually never used that functionality ourselves, because SQLModel (SQLAlchemy) by default loads the related records and deletes them or updates them with NULL before sending the DELETE for the team.
If you know your database would be able to correctly handle the deletes or updates on its own, just with ondelete="CASCADE" or ondelete="SET NULL", you can use passive_deletes="all" in the Relationship() to tell SQLModel (actually SQLAlchemy) to not delete or update those records (for heroes) before sending the DELETE for the team.
Now let's update the table model for Team to use passive_deletes="all" in the Relationship() for heroes.
We will also use ondelete="SET NULL" in the Hero model table, in the foreign key Field() for the team_id to make the database set those fields to NULL automatically.
Now, if we run the program, we will see that SQLModel (SQLAlchemy) is no longer loading and updating the heroes, it just sends the DELETE for the team.
fast βpython app.py π¬ Some boilerplate and previous output omitted π π¬ The hero table is created with the ON DELETE SET NULL as beforeCREATE TABLE hero ( id INTEGER NOT NULL, name VARCHAR NOT NULL, secret_name VARCHAR NOT NULL, age INTEGER, team_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(team_id) REFERENCES team (id) ON DELETE SET NULL )
π¬ For SQLite, we also send the custom command to enable foreign key supportINFO Engine PRAGMA foreign_keys=ON
π¬ We select and print the team WakalandTeam Wakaland: id=3 name='Wakaland' headquarters='Wakaland Capital City'
π¬ We won't see another SELECT for the heroes, nor an UPDATE or DELETE. SQLModel (with SQLAlchemy) won't try to load and update (or delete) the related records for heroes, it will just send the DELETE for the team right away.INFO Engine DELETE FROM team WHERE team.id = ? INFO Engine [generated in 0.00013s] (3,)
π¬ At this point, because we enabled foreign key support for SQLite, the database will take care of updating the records for heroes automatically, setting their team_id to NULL π¬ Print the deleted teamDeleted team: name='Wakaland' id=3 headquarters='Wakaland Capital City'
π¬ Finally, we select the heroes Black Lion and Princess Sure-E and print them, they no longer have a teamBlack Lion has no team: age=35 id=4 name='Black Lion' secret_name='Trevor Challa' team_id=None Princess Sure-E has no team: age=None id=5 name='Princess Sure-E' secret_name='Sure-E' team_id=None
We can also configure the database to prevent the deletion of a record (a team) if there are related records (heroes).
In this case, when someone attempts to delete a team with heroes in it, the database will raise an error.
And because this is configured in the database, it will happen even if someone interacts with the database directly using SQL (if the database supports it).
Tip
For SQLite, this also needs enabling foreign key support.
Enable Foreign Key Support in SQLite for RESTRICTΒΆ
As ondelete="RESTRICT" is mainly a database-level constraint, let's enable foreign key support in SQLite first to be able to test it.
Let's set ondelete="RESTRICT" in the foreign key Field() for the team_id in the Hero model table.
And in the Team model table, we will use passive_deletes="all" in the Relationship() for heroes, this way the default behavior of setting foreign keys from deleted models to NULL will be disabled, and when we try to delete a team with heroes, the database will raise an error.
Tip
Notice that we don't set cascade_delete in the Team model table.
Now, if we run the program and try to delete a team with heroes, we will see an error.
fast βpython app.py π¬ Some boilerplate and previous output omitted π π¬ The hero table is created with the ON DELETE RESTRICTCREATE TABLE hero ( id INTEGER NOT NULL, name VARCHAR NOT NULL, secret_name VARCHAR NOT NULL, age INTEGER, team_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(team_id) REFERENCES team (id) ON DELETE RESTRICT )
π¬ Now, when we reach the point of deleting a team with heroes, we will see an errorTraceback (most recent call last): File "/home/user/code...
Great! The database didn't let us commit the mistake of deleting a team with heroes. π€
Tip
If you want to test if the PRAGMA foreign_keys=ON is necessary, comment that line and run it again, you will not see an error. π±
The same with passive_deletes="all", if you comment that line, SQLModel (SQLAlchemy) will load and update the heroes before deleting the team, set their foreign key team_id to NULL and the constraint won't work as expected, you will not see an error. π
After having the ondelete="RESTRICT" in place, SQLite configured to support foreign keys, and passive_deletes="all" in the Relationship(), if we try to delete a team with heroes, we will see an error.
If we want to delete the team, we need to update the heroes first and set their team_id to None (or NULL in the database).
By calling the method .clear() from a list, we remove all its items. So, by calling team.heroes.clear() and saving that to the database, we disassociate the heroes from the team, that will set their team_id to None.
Tip
Calling team.heroes.clear() is very similar to what SQLModel (actually SQLAlchemy) would have done if we didn't have passive_deletes="all" configured.
fromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,select,textclassTeam(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",passive_deletes="all")classHero(SQLModel,table=True):id:int|None=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:int|None=Field(default=None,index=True)team_id:int|None=Field(default=None,foreign_key="team.id",ondelete="RESTRICT")team:Team|None=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)withengine.connect()asconnection:connection.execute(text("PRAGMA foreign_keys=ON"))# for SQLite onlydefcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defremove_team_heroes():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()team.heroes.clear()session.add(team)session.commit()session.refresh(team)print("Team with removed heroes:",team)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion has no team:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E has no team:",hero)defmain():create_db_and_tables()create_heroes()remove_team_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
π€ Other versions and variants
fromtypingimportOptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,select,textclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:list["Hero"]=Relationship(back_populates="team",passive_deletes="all")classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="RESTRICT")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)withengine.connect()asconnection:connection.execute(text("PRAGMA foreign_keys=ON"))# for SQLite onlydefcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defremove_team_heroes():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()team.heroes.clear()session.add(team)session.commit()session.refresh(team)print("Team with removed heroes:",team)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion has no team:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E has no team:",hero)defmain():create_db_and_tables()create_heroes()remove_team_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
fromtypingimportList,OptionalfromsqlmodelimportField,Relationship,Session,SQLModel,create_engine,select,textclassTeam(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)headquarters:strheroes:List["Hero"]=Relationship(back_populates="team",passive_deletes="all")classHero(SQLModel,table=True):id:Optional[int]=Field(default=None,primary_key=True)name:str=Field(index=True)secret_name:strage:Optional[int]=Field(default=None,index=True)team_id:Optional[int]=Field(default=None,foreign_key="team.id",ondelete="RESTRICT")team:Optional[Team]=Relationship(back_populates="heroes")sqlite_file_name="database.db"sqlite_url=f"sqlite:///{sqlite_file_name}"engine=create_engine(sqlite_url,echo=True)defcreate_db_and_tables():SQLModel.metadata.create_all(engine)withengine.connect()asconnection:connection.execute(text("PRAGMA foreign_keys=ON"))# for SQLite onlydefcreate_heroes():withSession(engine)assession: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()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=team_preventerssession.add(hero_spider_boy)session.commit()session.refresh(hero_spider_boy)print("Updated hero:",hero_spider_boy)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],)session.add(team_wakaland)session.commit()session.refresh(team_wakaland)print("Team Wakaland:",team_wakaland)defremove_team_heroes():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()team.heroes.clear()session.add(team)session.commit()session.refresh(team)print("Team with removed heroes:",team)defdelete_team():withSession(engine)assession:statement=select(Team).where(Team.name=="Wakaland")team=session.exec(statement).one()session.delete(team)session.commit()print("Deleted team:",team)defselect_deleted_heroes():withSession(engine)assession:statement=select(Hero).where(Hero.name=="Black Lion")result=session.exec(statement)hero=result.first()print("Black Lion has no team:",hero)statement=select(Hero).where(Hero.name=="Princess Sure-E")result=session.exec(statement)hero=result.first()print("Princess Sure-E has no team:",hero)defmain():create_db_and_tables()create_heroes()remove_team_heroes()delete_team()select_deleted_heroes()if__name__=="__main__":main()
Now, if we run the program and delete the heroes first, we will be able to delete the team without any issues.
fast βpython app.py π¬ Some boilerplate and previous output omitted π π¬ The hero table is created with the ON DELETE RESTRICTCREATE TABLE hero ( id INTEGER NOT NULL, name VARCHAR NOT NULL, secret_name VARCHAR NOT NULL, age INTEGER, team_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(team_id) REFERENCES team (id) ON DELETE RESTRICT )
π¬ We manually disassociate the heroes from the teamINFO Engine UPDATE hero SET team_id=? WHERE hero.id = ? INFO Engine [generated in 0.00008s] [(None, 4), (None, 5)]
π¬ We print the team from which we removed heroesTeam with removed heroes: name='Wakaland' id=3 headquarters='Wakaland Capital City'
π¬ Now we can delete the teamINFO Engine DELETE FROM team WHERE team.id = ? INFO Engine [generated in 0.00008s] (3,) INFO Engine COMMIT Deleted team: name='Wakaland' id=3 headquarters='Wakaland Capital City'
π¬ The heroes Black Lion and Princess Sure-E are no longer associated with the teamBlack Lion has no team: secret_name='Trevor Challa' name='Black Lion' team_id=None age=35 id=4 Princess Sure-E has no team: secret_name='Sure-E' name='Princess Sure-E' team_id=None age=None id=5