"""Single-table (table-per-hierarchy) inheritance example."""from__future__importannotationsfromtypingimportAnnotatedfromsqlalchemyimportcreate_enginefromsqlalchemyimportForeignKeyfromsqlalchemyimportFromClausefromsqlalchemyimportor_fromsqlalchemyimportselectfromsqlalchemyimportStringfromsqlalchemy.ormimportDeclarativeBasefromsqlalchemy.ormimportdeclared_attrfromsqlalchemy.ormimportMappedfromsqlalchemy.ormimportmapped_columnfromsqlalchemy.ormimportrelationshipfromsqlalchemy.ormimportSessionfromsqlalchemy.ormimportwith_polymorphicintpk=Annotated[int,mapped_column(primary_key=True)]str50=Annotated[str,mapped_column(String(50))]# columns that are local to subclasses must be nullable.# we can still use a non-optional type, howeverstr50subclass=Annotated[str,mapped_column(String(50),nullable=True)]classBase(DeclarativeBase):passclassCompany(Base):__tablename__="company"id:Mapped[intpk]name:Mapped[str50]employees:Mapped[list[Person]]=relationship(back_populates="company",cascade="all, delete-orphan")def__repr__(self):returnf"Company {self.name}"classPerson(Base):__tablename__="person"__table__:FromClauseid:Mapped[intpk]company_id:Mapped[int]=mapped_column(ForeignKey("company.id"))name:Mapped[str50]type:Mapped[str50]company:Mapped[Company]=relationship(back_populates="employees")__mapper_args__={"polymorphic_identity":"person","polymorphic_on":"type",}def__repr__(self):returnf"Ordinary person {self.name}"classEngineer(Person):# illustrate a single-inh "conflicting" mapped_column declaration,# where both subclasses want to share the same column that is nonetheless# not "local" to the base class@declared_attrdefstatus(cls)->Mapped[str50]:returnPerson.__table__.c.get("status",mapped_column(String(30))# type: ignore)engineer_name:Mapped[str50subclass]primary_language:Mapped[str50subclass]__mapper_args__={"polymorphic_identity":"engineer"}def__repr__(self):return(f"Engineer {self.name}, status {self.status}, "f"engineer_name {self.engineer_name}, "f"primary_language {self.primary_language}")classManager(Person):manager_name:Mapped[str50subclass]# illustrate a single-inh "conflicting" mapped_column declaration,# where both subclasses want to share the same column that is nonetheless# not "local" to the base class@declared_attrdefstatus(cls)->Mapped[str50]:returnPerson.__table__.c.get("status",mapped_column(String(30))# type: ignore)__mapper_args__={"polymorphic_identity":"manager"}def__repr__(self):return(f"Manager {self.name}, status {self.status}, "f"manager_name {self.manager_name}")engine=create_engine("sqlite://",echo=True)Base.metadata.create_all(engine)withSession(engine)assession:c=Company(name="company1",employees=[Manager(name="mr krabs",status="AAB",manager_name="manager1",),Engineer(name="spongebob",status="BBA",engineer_name="engineer1",primary_language="java",),Person(name="joesmith"),Engineer(name="patrick",status="CGG",engineer_name="engineer2",primary_language="python",),Manager(name="jsmith",status="ABA",manager_name="manager2"),],)session.add(c)session.commit()foreinc.employees:print(e)spongebob=session.scalars(select(Person).filter_by(name="spongebob")).one()spongebob2=session.scalars(select(Engineer).filter_by(name="spongebob")).one()assertspongebobisspongebob2spongebob2.engineer_name="hes spongebob!"session.commit()# query using with_polymorphic.eng_manager=with_polymorphic(Person,[Engineer,Manager])print(session.scalars(select(eng_manager).filter(or_(eng_manager.Engineer.engineer_name=="engineer1",eng_manager.Manager.manager_name=="manager2",))).all())# illustrate join from Company.print(session.scalars(select(Company).join(Company.employees.of_type(eng_manager)).filter(or_(eng_manager.Engineer.engineer_name=="engineer1",eng_manager.Manager.manager_name=="manager2",))).all())