Source code for examples.generic_associations.table_per_association
"""Illustrates a mixin which provides a generic associationvia a individually generated association tables for each parent class.The associated objects themselves are persisted in a single tableshared among all parents.This configuration has the advantage that all Addressrows are in one table, so that the definition of "Address"can be maintained in one place. The association tablecontains the foreign key to Address so that Addresshas no dependency on the system."""fromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportForeignKeyfromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemyimportTablefromsqlalchemy.ext.declarativeimportas_declarativefromsqlalchemy.ext.declarativeimportdeclared_attrfromsqlalchemy.ormimportrelationshipfromsqlalchemy.ormimportSession@as_declarative()classBase:"""Base class which provides automated table name and surrogate primary key column. """@declared_attrdef__tablename__(cls):returncls.__name__.lower()id=Column(Integer,primary_key=True)classAddress(Base):"""The Address class. This represents all address records in a single table. """street=Column(String)city=Column(String)zip=Column(String)def__repr__(self):return"%s(street=%r, city=%r, zip=%r)"%(self.__class__.__name__,self.street,self.city,self.zip,)classHasAddresses:"""HasAddresses mixin, creates a new address_association table for each parent. """@declared_attrdefaddresses(cls):address_association=Table("%s_addresses"%cls.__tablename__,cls.metadata,Column("address_id",ForeignKey("address.id"),primary_key=True),Column("%s_id"%cls.__tablename__,ForeignKey("%s.id"%cls.__tablename__),primary_key=True,),)returnrelationship(Address,secondary=address_association)classCustomer(HasAddresses,Base):name=Column(String)classSupplier(HasAddresses,Base):company_name=Column(String)engine=create_engine("sqlite://",echo=True)Base.metadata.create_all(engine)session=Session(engine)session.add_all([Customer(name="customer 1",addresses=[Address(street="123 anywhere street",city="New York",zip="10110"),Address(street="40 main street",city="San Francisco",zip="95732"),],),Supplier(company_name="Ace Hammers",addresses=[Address(street="2569 west elm",city="Detroit",zip="56785")],),])session.commit()forcustomerinsession.query(Customer):foraddressincustomer.addresses:print(address)# no parent here