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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
.. module:: sqlalchemy.orm
.. _mapper_sql_expressions:
SQL Expressions as Mapped Attributes
=====================================
Attributes on a mapped class can be linked to SQL expressions, which can
be used in queries.
Using a Hybrid
--------------
The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called
"hybrid attribute",
described in the section :ref:`hybrids_toplevel`. The hybrid provides
for an expression that works at both the Python level as well as at the
SQL expression level. For example, below we map a class ``User``,
containing attributes ``firstname`` and ``lastname``, and include a hybrid that
will provide for us the ``fullname``, which is the string concatenation of the two::
from sqlalchemy.ext.hybrid import hybrid_property
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
@hybrid_property
def fullname(self):
return self.firstname + " " + self.lastname
Above, the ``fullname`` attribute is interpreted at both the instance and
class level, so that it is available from an instance::
some_user = session.query(User).first()
print some_user.fullname
as well as usable within queries::
some_user = session.query(User).filter(User.fullname == "John Smith").first()
The string concatenation example is a simple one, where the Python expression
can be dual purposed at the instance and class level. Often, the SQL expression
must be distinguished from the Python expression, which can be achieved using
:meth:`.hybrid_property.expression`. Below we illustrate the case where a conditional
needs to be present inside the hybrid, using the ``if`` statement in Python and the
:func:`.sql.expression.case` construct for SQL expressions::
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
@hybrid_property
def fullname(self):
if self.firstname is not None:
return self.firstname + " " + self.lastname
else:
return self.lastname
@fullname.expression
def fullname(cls):
return case([
(cls.firstname != None, cls.firstname + " " + cls.lastname),
], else_ = cls.lastname)
.. _mapper_column_property_sql_expressions:
Using column_property
---------------------
The :func:`.orm.column_property` function can be used to map an SQL
expression in a manner similar to a regularly mapped :class:`.Column`.
With this technique, the attribute is loaded
along with all other column-mapped attributes at load time. This is in some
cases an advantage over the usage of hybrids, as the value can be loaded
up front at the same time as the parent row of the object, particularly if
the expression is one which links to other tables (typically as a correlated
subquery) to access data that wouldn't normally be
available on an already loaded object.
Disadvantages to using :func:`.orm.column_property` for SQL expressions include that
the expression must be compatible with the SELECT statement emitted for the class
as a whole, and there are also some configurational quirks which can occur
when using :func:`.orm.column_property` from declarative mixins.
Our "fullname" example can be expressed using :func:`.orm.column_property` as
follows::
from sqlalchemy.orm import column_property
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
fullname = column_property(firstname + " " + lastname)
Correlated subqueries may be used as well. Below we use the :func:`.select`
construct to create a SELECT that links together the count of ``Address``
objects available for a particular ``User``::
from sqlalchemy.orm import column_property
from sqlalchemy import select, func
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user.id'))
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
address_count = column_property(
select([func.count(Address.id)]).\
where(Address.user_id==id).\
correlate_except(Address)
)
In the above example, we define a :func:`.select` construct like the following::
select([func.count(Address.id)]).\
where(Address.user_id==id).\
correlate_except(Address)
The meaning of the above statement is, select the count of ``Address.id`` rows
where the ``Address.user_id`` column is equated to ``id``, which in the context
of the ``User`` class is the :class:`.Column` named ``id`` (note that ``id`` is
also the name of a Python built in function, which is not what we want to use
here - if we were outside of the ``User`` class definition, we'd use ``User.id``).
The :meth:`.select.correlate_except` directive indicates that each element in the
FROM clause of this :func:`.select` may be omitted from the FROM list (that is, correlated
to the enclosing SELECT statement against ``User``) except for the one corresponding
to ``Address``. This isn't strictly necessary, but prevents ``Address`` from
being inadvertently omitted from the FROM list in the case of a long string
of joins between ``User`` and ``Address`` tables where SELECT statements against
``Address`` are nested.
If import issues prevent the :func:`.column_property` from being defined
inline with the class, it can be assigned to the class after both
are configured. In Declarative this has the effect of calling :meth:`.Mapper.add_property`
to add an additional property after the fact::
User.address_count = column_property(
select([func.count(Address.id)]).\
where(Address.user_id==User.id)
)
For many-to-many relationships, use :func:`.and_` to join the fields of the
association table to both tables in a relation, illustrated
here with a classical mapping::
from sqlalchemy import and_
mapper(Author, authors, properties={
'book_count': column_property(
select([func.count(books.c.id)],
and_(
book_authors.c.author_id==authors.c.id,
book_authors.c.book_id==books.c.id
)))
})
Using a plain descriptor
-------------------------
In cases where an SQL query more elaborate than what :func:`.orm.column_property`
or :class:`.hybrid_property` can provide must be emitted, a regular Python
function accessed as an attribute can be used, assuming the expression
only needs to be available on an already-loaded instance. The function
is decorated with Python's own ``@property`` decorator to mark it as a read-only
attribute. Within the function, :func:`.object_session`
is used to locate the :class:`.Session` corresponding to the current object,
which is then used to emit a query::
from sqlalchemy.orm import object_session
from sqlalchemy import select, func
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
firstname = Column(String(50))
lastname = Column(String(50))
@property
def address_count(self):
return object_session(self).\
scalar(
select([func.count(Address.id)]).\
where(Address.user_id==self.id)
)
The plain descriptor approach is useful as a last resort, but is less performant
in the usual case than both the hybrid and column property approaches, in that
it needs to emit an SQL query upon each access.
|
