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