3
This example will emulate key aspects of the system used by popular
4
frameworks such as Django, ROR, etc.
6
It approaches the issue by bypassing standard referential integrity
7
practices, and producing a so-called "generic foreign key", which means
8
a database column that is not constrained to refer to any particular table.
9
In-application logic is used to determine which table is referenced.
11
This approach is not in line with SQLAlchemy's usual style, as foregoing
12
foreign key integrity means that the tables can easily contain invalid
13
references and also have no ability to use in-database cascade functionality.
15
However, due to the popularity of these systems, as well as that it uses
16
the fewest number of tables (which doesn't really offer any "advantage",
17
though seems to be comforting to many) this recipe remains in
18
high demand, so in the interests of having an easy StackOverflow answer
19
queued up, here it is. The author recommends "table_per_related"
20
or "table_per_association" instead of this approach.
22
.. versionadded:: 0.8.3
25
from sqlalchemy.ext.declarative import as_declarative, declared_attr
26
from sqlalchemy import create_engine, Integer, Column, \
28
from sqlalchemy.orm import Session, relationship, foreign, remote, backref
29
from sqlalchemy import event
34
"""Base class which provides automated table name
35
and surrogate primary key column.
39
def __tablename__(cls):
40
return cls.__name__.lower()
41
id = Column(Integer, primary_key=True)
46
This represents all address records in a
50
street = Column(String)
54
discriminator = Column(String)
55
"""Refers to the type of parent."""
57
parent_id = Column(Integer)
58
"""Refers to the primary key of the parent.
60
This could refer to any table.
65
"""Provides in-Python access to the "parent" by choosing
66
the appropriate relationship.
69
return getattr(self, "parent_%s" % self.discriminator)
72
return "%s(street=%r, city=%r, zip=%r)" % \
73
(self.__class__.__name__, self.street,
76
class HasAddresses(object):
77
"""HasAddresses mixin, creates a relationship to
78
the address_association table for each parent.
82
@event.listens_for(HasAddresses, "mapper_configured", propagate=True)
83
def setup_listener(mapper, class_):
84
name = class_.__name__
85
discriminator = name.lower()
86
class_.addresses = relationship(Address,
88
class_.id == foreign(remote(Address.parent_id)),
89
Address.discriminator == discriminator
92
"parent_%s" % discriminator,
93
primaryjoin=remote(class_.id) == foreign(Address.parent_id)
96
@event.listens_for(class_.addresses, "append")
97
def append_address(target, value, initiator):
98
value.discriminator = discriminator
100
class Customer(HasAddresses, Base):
101
name = Column(String)
103
class Supplier(HasAddresses, Base):
104
company_name = Column(String)
106
engine = create_engine('sqlite://', echo=True)
107
Base.metadata.create_all(engine)
109
session = Session(engine)
116
street='123 anywhere street',
120
street='40 main street',
121
city="San Francisco",
126
company_name="Ace Hammers",
129
street='2569 west elm',
138
for customer in session.query(Customer):
139
for address in customer.addresses:
141
print(address.parent)
b'\\ No newline at end of file'
added
removed
examples/generic_associations/generic_fk.py
