1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
Generic relationships
=====================
Generic relationship is a form of relationship that supports creating a 1 to many relationship to any target model.
::
from sqlalchemy_utils import generic_relationship
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
class Event(Base):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
# This is used to discriminate between the linked tables.
object_type = sa.Column(sa.Unicode(255))
# This is used to point to the primary key of the linked row.
object_id = sa.Column(sa.Integer)
object = generic_relationship(object_type, object_id)
# Some general usage to attach an event to a user.
user = User()
customer = Customer()
session.add_all([user, customer])
session.commit()
ev = Event()
ev.object = user
session.add(ev)
session.commit()
# Find the event we just made.
session.query(Event).filter_by(object=user).first()
# Find any events that are bound to users.
session.query(Event).filter(Event.object.is_type(User)).all()
Inheritance
-----------
::
class Employee(Base):
__tablename__ = 'employee'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(50))
type = sa.Column(sa.String(20))
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'employee'
}
class Manager(Employee):
__mapper_args__ = {
'polymorphic_identity': 'manager'
}
class Engineer(Employee):
__mapper_args__ = {
'polymorphic_identity': 'engineer'
}
class Activity(Base):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
object_type = sa.Column(sa.Unicode(255))
object_id = sa.Column(sa.Integer, nullable=False)
object = generic_relationship(object_type, object_id)
Now same as before we can add some objects::
manager = Manager()
session.add(manager)
session.commit()
activity = Activity()
activity.object = manager
session.add(activity)
session.commit()
# Find the activity we just made.
session.query(Event).filter_by(object=manager).first()
We can even test super types::
session.query(Activity).filter(Event.object.is_type(Employee)).all()
Abstract base classes
---------------------
Generic relationships also allows using string arguments. When using generic_relationship with abstract base classes you need to set up the relationship using declared_attr decorator and string arguments.
::
class Building(Base):
__tablename__ = 'building'
id = sa.Column(sa.Integer, primary_key=True)
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
class EventBase(Base):
__abstract__ = True
object_type = sa.Column(sa.Unicode(255))
object_id = sa.Column(sa.Integer, nullable=False)
@declared_attr
def object(cls):
return generic_relationship('object_type', 'object_id')
class Event(EventBase):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
Composite keys
--------------
For some very rare cases you may need to use generic_relationships with composite primary keys. There is a limitation here though: you can only set up generic_relationship for similar composite primary key types. In other words you can't mix generic relationship to both composite keyed objects and single keyed objects.
::
from sqlalchemy_utils import generic_relationship
class Customer(Base):
__tablename__ = 'customer'
code1 = sa.Column(sa.Integer, primary_key=True)
code2 = sa.Column(sa.Integer, primary_key=True)
class Event(Base):
__tablename__ = 'event'
id = sa.Column(sa.Integer, primary_key=True)
# This is used to discriminate between the linked tables.
object_type = sa.Column(sa.Unicode(255))
object_code1 = sa.Column(sa.Integer)
object_code2 = sa.Column(sa.Integer)
object = generic_relationship(
object_type, (object_code1, object_code2)
)
|
