1"""Illustrates a mixin which provides a generic association 2via a individually generated association tables for each parent class. 3The associated objects themselves are persisted in a single table 4shared among all parents. 5 6This configuration has the advantage that all Address 7rows are in one table, so that the definition of "Address" 8can be maintained in one place. The association table 9contains the foreign key to Address so that Address 10has no dependency on the system. 11 12 13""" 14from sqlalchemy import Column 15from sqlalchemy import create_engine 16from sqlalchemy import ForeignKey 17from sqlalchemy import Integer 18from sqlalchemy import String 19from sqlalchemy import Table 20from sqlalchemy.ext.declarative import as_declarative 21from sqlalchemy.ext.declarative import declared_attr 22from sqlalchemy.orm import relationship 23from sqlalchemy.orm import Session 24 25 26@as_declarative() 27class Base(object): 28 """Base class which provides automated table name 29 and surrogate primary key column. 30 31 """ 32 33 @declared_attr 34 def __tablename__(cls): 35 return cls.__name__.lower() 36 37 id = Column(Integer, primary_key=True) 38 39 40class Address(Base): 41 """The Address class. 42 43 This represents all address records in a 44 single table. 45 46 """ 47 48 street = Column(String) 49 city = Column(String) 50 zip = Column(String) 51 52 def __repr__(self): 53 return "%s(street=%r, city=%r, zip=%r)" % ( 54 self.__class__.__name__, 55 self.street, 56 self.city, 57 self.zip, 58 ) 59 60 61class HasAddresses(object): 62 """HasAddresses mixin, creates a new address_association 63 table for each parent. 64 65 """ 66 67 @declared_attr 68 def addresses(cls): 69 address_association = Table( 70 "%s_addresses" % cls.__tablename__, 71 cls.metadata, 72 Column("address_id", ForeignKey("address.id"), primary_key=True), 73 Column( 74 "%s_id" % cls.__tablename__, 75 ForeignKey("%s.id" % cls.__tablename__), 76 primary_key=True, 77 ), 78 ) 79 return relationship(Address, secondary=address_association) 80 81 82class Customer(HasAddresses, Base): 83 name = Column(String) 84 85 86class Supplier(HasAddresses, Base): 87 company_name = Column(String) 88 89 90engine = create_engine("sqlite://", echo=True) 91Base.metadata.create_all(engine) 92 93session = Session(engine) 94 95session.add_all( 96 [ 97 Customer( 98 name="customer 1", 99 addresses=[ 100 Address( 101 street="123 anywhere street", city="New York", zip="10110" 102 ), 103 Address( 104 street="40 main street", city="San Francisco", zip="95732" 105 ), 106 ], 107 ), 108 Supplier( 109 company_name="Ace Hammers", 110 addresses=[ 111 Address(street="2569 west elm", city="Detroit", zip="56785") 112 ], 113 ), 114 ] 115) 116 117session.commit() 118 119for customer in session.query(Customer): 120 for address in customer.addresses: 121 print(address) 122 # no parent here 123