Source code for examples.generic_associations.discriminator_on_association
"""Illustrates a mixin which provides a generic associationusing a single target table and a single association table,referred to by all parent tables. The association tablecontains a "discriminator" column which determines what type ofparent object associates to each particular row in the associationtable.SQLAlchemy's single-table-inheritance feature is usedto target different association types.This configuration attempts to simulate a so-called "generic foreign key"as closely as possible without actually foregoing the use of realforeign keys. Unlike table-per-related and table-per-association,it uses a fixed number of tables to serve any number of potential parentobjects, but is also slightly more complex."""fromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportForeignKeyfromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemy.ext.associationproxyimportassociation_proxyfromsqlalchemy.ext.declarativeimportas_declarativefromsqlalchemy.ext.declarativeimportdeclared_attrfromsqlalchemy.ormimportbackreffromsqlalchemy.ormimportrelationshipfromsqlalchemy.ormimportSession@as_declarative()classBase(object):"""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)classAddressAssociation(Base):"""Associates a collection of Address objects with a particular parent. """__tablename__="address_association"discriminator=Column(String)"""Refers to the type of parent."""__mapper_args__={"polymorphic_on":discriminator}classAddress(Base):"""The Address class. This represents all address records in a single table. """association_id=Column(Integer,ForeignKey("address_association.id"))street=Column(String)city=Column(String)zip=Column(String)association=relationship("AddressAssociation",backref="addresses")parent=association_proxy("association","parent")def__repr__(self):return"%s(street=%r, city=%r, zip=%r)"%(self.__class__.__name__,self.street,self.city,self.zip,)classHasAddresses(object):"""HasAddresses mixin, creates a relationship to the address_association table for each parent. """@declared_attrdefaddress_association_id(cls):returnColumn(Integer,ForeignKey("address_association.id"))@declared_attrdefaddress_association(cls):name=cls.__name__discriminator=name.lower()assoc_cls=type("%sAddressAssociation"%name,(AddressAssociation,),dict(__tablename__=None,__mapper_args__={"polymorphic_identity":discriminator},),)cls.addresses=association_proxy("address_association","addresses",creator=lambdaaddresses:assoc_cls(addresses=addresses),)returnrelationship(assoc_cls,backref=backref("parent",uselist=False))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)print(address.parent)