10
10
with mappers. It assumes you've worked through :ref:`ormtutorial_toplevel` and
11
11
know how to construct and use rudimentary mappers and relationships.
13
Note that all patterns here apply both to the usage of explicit
14
:func:`~.orm.mapper` and :class:`.Table` objects as well as when using the
15
:mod:`sqlalchemy.ext.declarative` extension. Any example in this section which
16
takes a form such as::
18
mapper(User, users_table, primary_key=[users_table.c.id])
13
.. _classical_mapping:
18
Recall from :ref:`ormtutorial_toplevel` that we normally use the :ref:`declarative_toplevel`
19
system to define mappings. The "Classical Mapping" refers to the process
20
of defining :class:`.Table` metadata and mapped class via :func:`.mapper` separately.
21
While direct usage of :func:`.mapper` is not prominent in modern SQLAlchemy,
22
the function can be used
23
to create alternative mappings for an already mapped class, offers greater configurational
24
flexibility in certain highly circular configurations, and is also at the base of
25
alternative configurational systems not based upon Declarative. Many SQLAlchemy
26
applications in fact use classical mappings directly for configuration.
28
The ``User`` example in the tutorial, using classical mapping, defines the
29
:class:`.Table`, class, and :func:`.mapper` of the class separately. Below we illustrate
30
the full ``User``/``Address`` example using this style::
32
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, String
33
from sqlalchemy.orm import mapper, relationship
37
user = Table('user', metadata,
38
Column('id', Integer, primary_key=True),
39
Column('name', String(50)),
40
Column('fullname', String(50)),
41
Column('password', String(12))
44
address = Table('address', metadata,
45
Column('id', Integer, primary_key=True),
46
Column('user_id', Integer, ForeignKey('user.id')),
47
Column('email_address', String(50))
51
def __init__(self, name, fullname, password):
53
self.fullname = fullname
54
self.password = password
56
class Address(object):
57
def __init__(self, email_address):
58
self.email_address = email_address
61
mapper(User, user, properties={
62
'addresses':relationship(Address, order_by=address.c.id, backref="user")
64
mapper(Address, address)
66
When the above is complete we now have a :class:`.Table`/:func:`.mapper` setup the same as that
67
set up using Declarative in the tutorial. Note that the mappings do not have the
68
benefit of the instrumented ``User`` and ``Address`` classes available, nor is the "string" argument
69
system of :func:`.relationship` available, as this is a feature of Declarative. The ``order_by``
70
argument of the ``User.addresses`` relationship is defined in terms of the actual ``address``
71
table instead of the ``Address`` class.
73
It's also worth noting that the "Classical" and "Declarative" mapping systems are not in
74
any way exclusive of each other. The two can be mixed freely - below we can
75
define a new class ``Order`` using a declarative base, which links back to ``User``-
76
no problem, except that we can't specify ``User`` as a string since it's not available
77
in the "base" registry::
79
from sqlalchemy.ext.declarative import declarative_base
81
Base = declarative_base()
84
__tablename__ = 'order'
86
id = Column(Integer, primary_key=True)
87
user_id = Column(ForeignKey('user.id'))
88
order_number = Column(String(50))
89
user = relationship(User, backref="orders")
91
This reference document uses a mix of Declarative and classical mappings for
92
examples. However, all patterns here apply both to the usage of explicit
93
:func:`~.orm.mapper` and :class:`.Table` objects as well as when using
94
Declarative, where options that are specific to the :func:`.mapper` function
95
can be specified with Declarative via the ``__mapper__`` attribute. Any
96
example in this section which takes a form such as::
98
mapper(User, user_table, primary_key=[user_table.c.id])
20
100
Would translate into declarative as::
23
__table__ = users_table
103
__table__ = user_table
24
104
__mapper_args__ = {
25
'primary_key':[users_table.c.id]
105
'primary_key':[user_table.c.id]
28
Or if using ``__tablename__``, :class:`.Column` objects are declared inline
29
with the class definition. These are usable as is within ``__mapper_args__``::
108
:class:`.Column` objects which are declared inline can also
109
be used directly in ``__mapper_args__``::
32
__tablename__ = 'users'
112
__tablename__ = 'user'
34
114
id = Column(Integer)
45
125
the mapped :class:`.Table` into mapped object attributes. This behavior can be
46
126
modified in several ways, as well as enhanced by SQL expressions.
128
Naming Columns Distinctly from Attribute Names
129
----------------------------------------------
131
A mapping by default shares the same name for a
132
:class:`.Column` as that of the mapped attribute.
133
The name assigned to the :class:`.Column` can be different,
134
as we illustrate here in a Declarative mapping::
137
__tablename__ = 'user'
138
id = Column('user_id', Integer, primary_key=True)
139
name = Column('user_name', String(50))
141
Where above ``User.id`` resolves to a column named ``user_id``
142
and ``User.name`` resolves to a column named ``user_name``.
144
In a classical mapping, the :class:`.Column` objects
145
can be placed directly in the ``properties`` dictionary
146
using an alternate key::
148
mapper(User, user_table, properties={
149
'id': user_table.c.user_id,
150
'name': user_table.c.user_name,
153
When mapping to an already constructed :class:`.Table`,
154
a prefix can be specified using the ``column_prefix``
155
option, which will cause the automated mapping of
156
each :class:`.Column` to name the attribute starting
157
with the given prefix, prepended to the actual :class:`.Column`
161
__table__ = user_table
162
__mapper_args__ = {'column_prefix':'_'}
164
The above will place attribute names such as ``_user_id``, ``_user_name``,
165
``_password`` etc. on the mapped ``User`` class.
167
The classical version of the above::
169
mapper(User, user_table, column_prefix='_')
171
Mapping Multiple Columns to a Single Attribute
172
----------------------------------------------
174
To place multiple columns which are known to be "synonymous" based on foreign
175
key relationship or join condition into the same mapped attribute,
176
they can be mapped as a list. Below we map to a :func:`~.expression.join`::
178
from sqlalchemy import join, Table, Column, String, Integer, ForeignKey
179
from sqlalchemy.ext.declarative import declarative_base
181
Base = declarative_base()
183
user_table = Table('user', Base.metadata,
184
Column('id', Integer, primary_key=True),
185
Column('name', String(50)),
186
Column('fullname', String(50)),
187
Column('password', String(12))
190
address_table = Table('address', Base.metadata,
191
Column('id', Integer, primary_key=True),
192
Column('user_id', Integer, ForeignKey('user.id')),
193
Column('email_address', String(50))
196
# "user JOIN address ON user.id=address.user_id"
197
useraddress = join(user_table, address_table, \
198
user_table.c.id == address_table.c.user_id)
201
__table__ = useraddress
203
# assign "user.id", "address.user_id" to the
205
id = [user_table.c.id, address_table.c.user_id]
207
# assign "address.id" to the "address_id"
208
# attribute, to avoid name conflicts
209
address_id = address_table.c.id
211
In the above mapping, the value assigned to ``user.id`` will
212
also be persisted to the ``address.user_id`` column during a
213
flush. The two columns are also not independently queryable
214
from the perspective of the mapped class (they of course are
215
still available from their original tables).
219
mapper(User, useraddress, properties={
220
'id':[user_table.c.id, address_table.c.user_id],
221
'address_id':address_table.c.id
224
For further examples on this particular use case, see :ref:`maptojoin`.
226
Using column_property for column level options
227
-----------------------------------------------
229
The mapping of a :class:`.Column` with a particular :func:`.mapper` can be
230
customized using the :func:`.orm.column_property` function. This function
231
explicitly creates the :class:`.ColumnProperty` object which handles the job of
232
mapping a :class:`.Column`, instead of relying upon the :func:`.mapper`
233
function to create it automatically. Used with Declarative,
234
the :class:`.Column` can be embedded directly into the
237
from sqlalchemy.orm import column_property
240
__tablename__ = 'user'
242
id = Column(Integer, primary_key=True)
243
name = column_property(Column(String(50)), active_history=True)
245
Or with a classical mapping, in the ``properties`` dictionary::
247
from sqlalchemy.orm import column_property
249
mapper(User, user, properties={
250
'name':column_property(user.c.name, active_history=True)
253
Further examples of :func:`.orm.column_property` are at :ref:`mapper_sql_expressions`.
255
.. autofunction:: column_property
48
257
Mapping a Subset of Table Columns
49
258
---------------------------------
52
261
use the ``include_properties`` or ``exclude_properties`` arguments. For
55
mapper(User, users_table, include_properties=['user_id', 'user_name'])
264
mapper(User, user_table, include_properties=['user_id', 'user_name'])
57
...will map the ``User`` class to the ``users_table`` table, only including
266
...will map the ``User`` class to the ``user_table`` table, only including
58
267
the "user_id" and "user_name" columns - the rest are not refererenced.
61
mapper(Address, addresses_table,
270
mapper(Address, address_table,
62
271
exclude_properties=['street', 'city', 'state', 'zip'])
64
...will map the ``Address`` class to the ``addresses_table`` table, including
273
...will map the ``Address`` class to the ``address_table`` table, including
65
274
all columns present except "street", "city", "state", and "zip".
67
276
When this mapping is used, the columns that are not included will not be
88
297
This functionality is part of the SQL expression and execution system and
89
298
occurs below the level of the ORM.
92
Attribute Names for Mapped Columns
93
----------------------------------
95
To change the name of the attribute mapped to a particular column, place the
96
:class:`~sqlalchemy.schema.Column` object in the ``properties`` dictionary
97
with the desired key::
99
mapper(User, users_table, properties={
100
'id': users_table.c.user_id,
101
'name': users_table.c.user_name,
104
When using :mod:`~sqlalchemy.ext.declarative`, the above configuration is more
105
succinct - place the full column name in the :class:`.Column` definition,
106
using the desired attribute name in the class definition::
108
from sqlalchemy.ext.declarative import declarative_base
109
Base = declarative_base()
112
__tablename__ = 'user'
113
id = Column('user_id', Integer, primary_key=True)
114
name = Column('user_name', String(50))
116
To change the names of all attributes using a prefix, use the
117
``column_prefix`` option. This is useful for some schemes that would like
118
to declare alternate attributes::
120
mapper(User, users_table, column_prefix='_')
122
The above will place attribute names such as ``_user_id``, ``_user_name``,
123
``_password`` etc. on the mapped ``User`` class.
126
Mapping Multiple Columns to a Single Attribute
127
----------------------------------------------
129
To place multiple columns which are known to be "synonymous" based on foreign
130
key relationship or join condition into the same mapped attribute, put them
131
together using a list, as below where we map to a :func:`~.expression.join`::
133
from sqlalchemy.sql import join
135
# join users and addresses
136
usersaddresses = join(users_table, addresses_table, \
137
users_table.c.user_id == addresses_table.c.user_id)
139
# user_id columns are equated under the 'user_id' attribute
140
mapper(User, usersaddresses, properties={
141
'id':[users_table.c.user_id, addresses_table.c.user_id],
144
For further examples on this particular use case, see :ref:`maptojoin`.
146
Using column_property for column level options
147
-----------------------------------------------
149
The establishment of a :class:`.Column` on a :func:`.mapper` can be further
150
customized using the :func:`.column_property` function, as specified
151
to the ``properties`` dictionary. This function is
152
usually invoked implicitly for each mapped :class:`.Column`. Explicit usage
155
from sqlalchemy.orm import mapper, column_property
157
mapper(User, users, properties={
158
'name':column_property(users.c.name, active_history=True)
161
or with declarative::
164
__tablename__ = 'users'
166
id = Column(Integer, primary_key=True)
167
name = column_property(Column(String(50)), active_history=True)
169
Further examples of :func:`.column_property` are at :ref:`mapper_sql_expressions`.
171
.. autofunction:: column_property
175
302
Deferred Column Loading
176
303
========================
178
This feature allows particular columns of a table to not be loaded by default,
179
instead being loaded later on when first referenced. It is essentially
180
"column-level lazy loading". This feature is useful when one wants to avoid
305
This feature allows particular columns of a table be loaded only
306
upon direct access, instead of when the entity is queried using
307
:class:`.Query`. This feature is useful when one wants to avoid
181
308
loading a large text or binary field into memory when it's not needed.
182
309
Individual columns can be lazy loaded by themselves or placed into groups that
185
book_excerpts = Table('books', metadata,
186
Column('book_id', Integer, primary_key=True),
187
Column('title', String(200), nullable=False),
188
Column('summary', String(2000)),
189
Column('excerpt', Text),
190
Column('photo', Binary)
196
# define a mapper that will load each of 'excerpt' and 'photo' in
197
# separate, individual-row SELECT statements when each attribute
198
# is first referenced on the individual object instance
199
mapper(Book, book_excerpts, properties={
200
'excerpt': deferred(book_excerpts.c.excerpt),
201
'photo': deferred(book_excerpts.c.photo)
204
With declarative, :class:`.Column` objects can be declared directly inside of :func:`deferred`::
310
lazy-load together, using the :func:`.orm.deferred` function to
311
mark them as "deferred". In the example below, we define a mapping that will load each of
312
``.excerpt`` and ``.photo`` in separate, individual-row SELECT statements when each
313
attribute is first referenced on the individual object instance::
315
from sqlalchemy.orm import deferred
316
from sqlalchemy import Integer, String, Text, Binary, Column
206
318
class Book(Base):
207
__tablename__ = 'books'
319
__tablename__ = 'book'
209
321
book_id = Column(Integer, primary_key=True)
210
322
title = Column(String(200), nullable=False)
212
324
excerpt = deferred(Column(Text))
213
325
photo = deferred(Column(Binary))
215
Deferred columns can be associted with a "group" name, so that they load
216
together when any of them are first accessed::
218
book_excerpts = Table('books', metadata,
219
Column('book_id', Integer, primary_key=True),
220
Column('title', String(200), nullable=False),
221
Column('summary', String(2000)),
222
Column('excerpt', Text),
223
Column('photo1', Binary),
224
Column('photo2', Binary),
225
Column('photo3', Binary)
231
# define a mapper with a 'photos' deferred group. when one photo is referenced,
232
# all three photos will be loaded in one SELECT statement. The 'excerpt' will
233
# be loaded separately when it is first referenced.
234
mapper(Book, book_excerpts, properties = {
235
'excerpt': deferred(book_excerpts.c.excerpt),
236
'photo1': deferred(book_excerpts.c.photo1, group='photos'),
237
'photo2': deferred(book_excerpts.c.photo2, group='photos'),
238
'photo3': deferred(book_excerpts.c.photo3, group='photos')
327
Classical mappings as always place the usage of :func:`.orm.deferred` in the
328
``properties`` dictionary against the table-bound :class:`.Column`::
330
mapper(Book, book_table, properties={
331
'photo':deferred(book_table.c.photo)
334
Deferred columns can be associated with a "group" name, so that they load
335
together when any of them are first accessed. The example below defines a
336
mapping with a ``photos`` deferred group. When one ``.photo`` is accessed, all three
337
photos will be loaded in one SELECT statement. The ``.excerpt`` will be loaded
338
separately when it is accessed::
341
__tablename__ = 'book'
343
book_id = Column(Integer, primary_key=True)
344
title = Column(String(200), nullable=False)
345
summary = Column(String(2000))
346
excerpt = deferred(Column(Text))
347
photo1 = deferred(Column(Binary), group='photos')
348
photo2 = deferred(Column(Binary), group='photos')
349
photo3 = deferred(Column(Binary), group='photos')
241
351
You can defer or undefer columns at the :class:`~sqlalchemy.orm.query.Query`
242
level using the :func:`.defer` and :func:`.undefer` query options::
352
level using the :func:`.orm.defer` and :func:`.orm.undefer` query options::
354
from sqlalchemy.orm import defer, undefer
244
356
query = session.query(Book)
245
357
query.options(defer('summary')).all()
246
358
query.options(undefer('excerpt')).all()
248
360
And an entire "deferred group", i.e. which uses the ``group`` keyword argument
249
to :func:`~sqlalchemy.orm.deferred()`, can be undeferred using
250
:func:`.undefer_group()`, sending in the group name::
361
to :func:`.orm.deferred`, can be undeferred using
362
:func:`.orm.undefer_group`, sending in the group name::
364
from sqlalchemy.orm import undefer_group
252
366
query = session.query(Book)
253
367
query.options(undefer_group('photos')).all()
268
382
SQL Expressions as Mapped Attributes
269
383
=====================================
271
Any SQL expression that relates to the primary mapped selectable can be mapped as a
272
read-only attribute which will be bundled into the SELECT emitted
273
for the target mapper when rows are loaded. This effect is achieved
274
using the :func:`.column_property` function. Any
276
:class:`.ClauseElement` may be
277
used. Unlike older versions of SQLAlchemy, there is no :func:`~.sql.expression.label` requirement::
279
from sqlalchemy.orm import column_property
281
mapper(User, users_table, properties={
282
'fullname': column_property(
283
users_table.c.firstname + " " + users_table.c.lastname
385
Any SQL expression that relates to the primary mapped selectable can be mapped
386
as a read-only attribute which will be bundled into the SELECT emitted for the
387
target mapper when rows are loaded. This effect is achieved using the
388
:func:`.orm.column_property` function. Any scalar-returning
389
:class:`.ClauseElement` may be used::
391
from sqlalchemy.orm import column_property
394
__tablename__ = 'user'
395
id = Column(Integer, primary_key=True)
396
firstname = Column(String(50))
397
lastname = Column(String(50))
398
fullname = column_property(firstname + " " + lastname)
400
Correlated subqueries may be used as well. Below we use the :func:`.select`
401
construct to create a SELECT that links together the count of ``Address``
402
objects available for a particular ``User``::
404
from sqlalchemy.orm import column_property
405
from sqlalchemy import select, func
406
from sqlalchemy import Column, Integer, String, ForeignKey
408
from sqlalchemy.ext.declarative import declarative_base
410
Base = declarative_base()
413
__tablename__ = 'address'
414
id = Column(Integer, primary_key=True)
415
user_id = Column(Integer, ForeignKey('user.id'))
418
__tablename__ = 'user'
419
id = Column(Integer, primary_key=True)
420
address_count = column_property(
421
select([func.count(Address.id)]).\
422
where(Address.user_id==id)
287
Correlated subqueries may be used as well::
289
from sqlalchemy.orm import column_property
290
from sqlalchemy import select, func
292
mapper(User, users_table, properties={
293
'address_count': column_property(
294
select([func.count(addresses_table.c.address_id)]).\
295
where(addresses_table.c.user_id==users_table.c.user_id)
425
If import issues prevent the :func:`.column_property` from being defined
426
inline with the class, it can be assigned to the class after both
427
are configured. In Declarative this has the effect of calling :meth:`.Mapper.add_property`
428
to add an additional property after the fact::
430
User.address_count = column_property(
431
select([func.count(Address.id)]).\
432
where(Address.user_id==User.id)
299
435
For many-to-many relationships, use :func:`.and_` to join the fields of the
300
association table to both tables in a relation::
436
association table to both tables in a relation, illustrated
437
here with a classical mapping::
302
439
from sqlalchemy import and_
313
For examples of :func:`.column_property` using Declarative, see
314
:ref:`declarative_sql_expressions`.
316
Note that :func:`.column_property` is used to provide the effect of a SQL
317
expression that is actively rendered into the SELECT generated for a
318
particular mapped class. Alternatively, for the typical attribute that
319
represents a composed value, its usually simpler to define it as a Python
320
property which is evaluated as it is invoked on instances after they've been
450
Alternatives to column_property()
451
---------------------------------
453
:func:`.orm.column_property` is used to provide the effect of a SQL expression
454
that is actively rendered into the SELECT generated for a particular mapped
455
class. For the typical attribute that represents a composed value, it's often
456
simpler and more efficient to just define it as a Python property, which is
457
evaluated as it is invoked on instances after they've been loaded::
460
__tablename__ = 'user'
461
id = Column(Integer, primary_key=True)
462
firstname = Column(String(50))
463
lastname = Column(String(50))
325
466
def fullname(self):
326
467
return self.firstname + " " + self.lastname
328
To invoke a SQL statement from an instance that's already been loaded, the
329
session associated with the instance can be acquired using
330
:func:`~.session.object_session` which will provide the appropriate
469
To emit SQL queries from within a @property, the
470
:class:`.Session` associated with the instance can be acquired using
471
:func:`~.session.object_session`, which will provide the appropriate
331
472
transactional context from which to emit a statement::
333
474
from sqlalchemy.orm import object_session
334
475
from sqlalchemy import select, func
478
__tablename__ = 'user'
479
id = Column(Integer, primary_key=True)
480
firstname = Column(String(50))
481
lastname = Column(String(50))
338
484
def address_count(self):
339
485
return object_session(self).\
341
select([func.count(addresses_table.c.address_id)]).\
342
where(addresses_table.c.user_id==self.user_id)
487
select([func.count(Address.id)]).\
488
where(Address.user_id==self.id)
345
See also :ref:`synonyms` for details on building expression-enabled
346
descriptors on mapped classes, which are invoked independently of the
491
For more information on using descriptors, including how they can
492
be smoothly integrated into SQL expressions, see :ref:`synonyms`.
349
494
Changing Attribute Behavior
350
495
============================
497
.. _simple_validators:
352
499
Simple Validators
353
500
-----------------
357
504
an exception, halting the process of mutating the attribute's value, or can
358
505
change the given value into something different. Validators, like all
359
506
attribute extensions, are only called by normal userland code; they are not
360
issued when the ORM is populating the object.
362
.. sourcecode:: python+sql
507
issued when the ORM is populating the object::
364
509
from sqlalchemy.orm import validates
366
addresses_table = Table('addresses', metadata,
367
Column('id', Integer, primary_key=True),
368
Column('email', String)
371
class EmailAddress(object):
511
class EmailAddress(Base):
512
__tablename__ = 'address'
514
id = Column(Integer, primary_key=True)
515
email = Column(String)
372
517
@validates('email')
373
518
def validate_email(self, key, address):
374
519
assert '@' in address
377
mapper(EmailAddress, addresses_table)
379
Validators also receive collection events, when items are added to a collection:
381
.. sourcecode:: python+sql
522
Validators also receive collection events, when items are added to a
525
from sqlalchemy.orm import validates
530
addresses = relationship("Address")
384
532
@validates('addresses')
385
533
def validate_address(self, key, address):
386
534
assert '@' in address.email
439
595
def email(self, email):
440
596
self._email = email
442
The ``email`` attribute now provides a SQL expression when used at the class level:
598
The ``.email`` attribute, in addition to providing getter/setter behavior when we have an
599
instance of ``EmailAddress``, also provides a SQL expression when used at the class level,
600
that is, from the ``EmailAddress`` class directly:
444
602
.. sourcecode:: python+sql
446
604
from sqlalchemy.orm import Session
447
605
session = Session()
449
{sql}address = session.query(EmailAddress).filter(EmailAddress.email == 'address@example.com').one()
450
SELECT addresses.email AS addresses_email, addresses.id AS addresses_id
452
WHERE addresses.email = ?
607
{sql}address = session.query(EmailAddress).\
608
filter(EmailAddress.email == 'address@example.com').\
610
SELECT address.email AS address_email, address.id AS address_id
612
WHERE address.email = ?
453
613
('address@example.com',)
456
616
address.email = 'otheraddress@example.com'
457
617
{sql}session.commit()
458
UPDATE addresses SET email=? WHERE addresses.id = ?
618
UPDATE address SET email=? WHERE address.id = ?
459
619
('otheraddress@example.com', 1)
463
The :class:`~.hybrid_property` also allows us to change the behavior of the attribute, including
464
defining separate behaviors when the attribute is accessed at the instance level versus at
465
the class/expression level, using the :meth:`.hybrid_property.expression` modifier. Such
466
as, if we wanted to add a host name automatically, we might define two sets of string manipulation
623
The :class:`~.hybrid_property` also allows us to change the behavior of the
624
attribute, including defining separate behaviors when the attribute is
625
accessed at the instance level versus at the class/expression level, using the
626
:meth:`.hybrid_property.expression` modifier. Such as, if we wanted to add a
627
host name automatically, we might define two sets of string manipulation
469
class EmailAddress(object):
630
class EmailAddress(Base):
631
__tablename__ = 'email_address'
633
id = Column(Integer, primary_key=True)
635
_email = Column("email", String)
472
639
"""Return the value of _email up until the last twelve
489
656
return func.substr(cls._email, 0, func.length(cls._email) - 12)
491
Above, accessing the ``email`` property of an instance of ``EmailAddress`` will return the value of
492
the ``_email`` attribute, removing
493
or adding the hostname ``@example.com`` from the value. When we query against the ``email`` attribute,
494
a SQL function is rendered which produces the same effect:
658
Above, accessing the ``email`` property of an instance of ``EmailAddress``
659
will return the value of the ``_email`` attribute, removing or adding the
660
hostname ``@example.com`` from the value. When we query against the ``email``
661
attribute, a SQL function is rendered which produces the same effect:
496
663
.. sourcecode:: python+sql
498
665
{sql}address = session.query(EmailAddress).filter(EmailAddress.email == 'address').one()
499
SELECT addresses.email AS addresses_email, addresses.id AS addresses_id
501
WHERE substr(addresses.email, ?, length(addresses.email) - ?) = ?
666
SELECT address.email AS address_email, address.id AS address_id
668
WHERE substr(address.email, ?, length(address.email) - ?) = ?
502
669
(0, 12, 'address')
507
672
Read more about Hybrids at :ref:`hybrids_toplevel`.
614
769
def __ne__(self, other):
615
770
return not self.__eq__(other)
617
The requirements for the custom datatype class are that it have a
618
constructor which accepts positional arguments corresponding to its column
619
format, and also provides a method ``__composite_values__()`` which
620
returns the state of the object as a list or tuple, in order of its
621
column-based attributes. It also should supply adequate ``__eq__()`` and
622
``__ne__()`` methods which test the equality of two instances.
624
The :func:`.composite` function is then used in the mapping::
772
The requirements for the custom datatype class are that it have a constructor
773
which accepts positional arguments corresponding to its column format, and
774
also provides a method ``__composite_values__()`` which returns the state of
775
the object as a list or tuple, in order of its column-based attributes. It
776
also should supply adequate ``__eq__()`` and ``__ne__()`` methods which test
777
the equality of two instances.
779
We will create a mapping to a table ``vertice``, which represents two points
780
as ``x1/y1`` and ``x2/y2``. These are created normally as :class:`.Column`
781
objects. Then, the :func:`.composite` function is used to assign new
782
attributes that will represent sets of columns via the ``Point`` class::
784
from sqlalchemy import Column, Integer
626
785
from sqlalchemy.orm import composite
628
class Vertex(object):
631
mapper(Vertex, vertices, properties={
632
'start': composite(Point, vertices.c.x1, vertices.c.y1),
633
'end': composite(Point, vertices.c.x2, vertices.c.y2)
636
When using :mod:`sqlalchemy.ext.declarative`, the individual
637
:class:`.Column` objects may optionally be bundled into the
638
:func:`.composite` call, ensuring that they are named::
640
786
from sqlalchemy.ext.declarative import declarative_base
641
788
Base = declarative_base()
643
790
class Vertex(Base):
644
__tablename__ = 'vertices'
791
__tablename__ = 'vertice'
645
793
id = Column(Integer, primary_key=True)
646
start = composite(Point, Column('x1', Integer), Column('y1', Integer))
647
end = composite(Point, Column('x2', Integer), Column('y2', Integer))
649
Using either configurational approach, we can now use the ``Vertex`` instances
650
as well as querying as though the ``start`` and ``end`` attributes are regular
654
v = Vertex(Point(3, 4), Point(5, 6))
657
v2 = session.query(Vertex).filter(Vertex.start == Point(3, 4))
799
start = composite(Point, x1, y1)
800
end = composite(Point, x2, y2)
802
A classical mapping above would define each :func:`.composite`
803
against the existing table::
805
mapper(Vertex, vertice_table, properties={
806
'start':composite(Point, vertice_table.c.x1, vertice_table.c.y1),
807
'end':composite(Point, vertice_table.c.x2, vertice_table.c.y2),
810
We can now persist and use ``Vertex`` instances, as well as query for them,
811
using the ``.start`` and ``.end`` attributes against ad-hoc ``Point`` instances:
813
.. sourcecode:: python+sql
815
>>> v = Vertex(start=Point(3, 4), end=Point(5, 6))
817
>>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
818
{sql}>>> print q.first().start
820
INSERT INTO vertice (x1, y1, x2, y2) VALUES (?, ?, ?, ?)
822
SELECT vertice.id AS vertice_id,
823
vertice.x1 AS vertice_x1,
824
vertice.y1 AS vertice_y1,
825
vertice.x2 AS vertice_x2,
826
vertice.y2 AS vertice_y2
828
WHERE vertice.x1 = ? AND vertice.y1 = ?
831
{stop}Point(x=3, y=4)
659
833
.. autofunction:: composite
718
j = join(users_table, addresses_table)
899
j = join(user_table, address_table)
720
901
# map to it - the identity of an AddressUser object will be
721
902
# based on (user_id, address_id) since those are the primary keys involved
722
903
mapper(AddressUser, j, properties={
723
'user_id': [users_table.c.user_id, addresses_table.c.user_id]
904
'user_id': [user_table.c.user_id, address_table.c.user_id]
726
Note that the list of columns is equivalent to the usage of :func:`.column_property`
907
Note that the list of columns is equivalent to the usage of :func:`.orm.column_property`
727
908
with multiple columns::
729
910
from sqlalchemy.orm import mapper, column_property
731
912
mapper(AddressUser, j, properties={
732
'user_id': column_property(users_table.c.user_id, addresses_table.c.user_id)
913
'user_id': column_property(user_table.c.user_id, address_table.c.user_id)
735
The usage of :func:`.column_property` is required when using declarative to map
916
The usage of :func:`.orm.column_property` is required when using declarative to map
736
917
to multiple columns, since the declarative class parser won't recognize a plain
737
918
list of columns::
774
955
Mapping a Class against Arbitrary Selects
775
956
=========================================
777
Similar to mapping against a join, a plain select() object can be used with a mapper as well. Below, an example select which contains two aggregate functions and a group_by is mapped to a class:
779
.. sourcecode:: python+sql
781
from sqlalchemy.sql import select
783
s = select([customers,
784
func.count(orders).label('order_count'),
785
func.max(orders.price).label('highest_order')],
786
customers.c.customer_id==orders.c.customer_id,
787
group_by=[c for c in customers.c]
958
Similar to mapping against a join, a plain :func:`~.expression.select` object can be used with a
959
mapper as well. Below, an example select which contains two aggregate
960
functions and a group_by is mapped to a class::
962
from sqlalchemy import select, func
965
func.count(orders.c.id).label('order_count'),
966
func.max(orders.c.price).label('highest_order'),
968
]).group_by(orders.c.customer_id).alias()
970
s = select([customers,subq]).\
971
where(customers.c.customer_id==subq.c.customer_id)
789
972
class Customer(object):
792
975
mapper(Customer, s)
794
Above, the "customers" table is joined against the "orders" table to produce a full row for each customer row, the total count of related rows in the "orders" table, and the highest price in the "orders" table, grouped against the full set of columns in the "customers" table. That query is then mapped against the Customer class. New instances of Customer will contain attributes for each column in the "customers" table as well as an "order_count" and "highest_order" attribute. Updates to the Customer object will only be reflected in the "customers" table and not the "orders" table. This is because the primary key columns of the "orders" table are not represented in this mapper and therefore the table is not affected by save or delete operations.
977
Above, the "customers" table is joined against the "orders" table to produce a
978
full row for each customer row, the total count of related rows in the
979
"orders" table, and the highest price in the "orders" table. That query is then mapped
980
against the Customer class. New instances of Customer will contain attributes
981
for each column in the "customers" table as well as an "order_count" and
982
"highest_order" attribute. Updates to the Customer object will only be
983
reflected in the "customers" table and not the "orders" table. This is because
984
the primary key columns of the "orders" table are not represented in this
985
mapper and therefore the table is not affected by save or delete operations.
796
987
Multiple Mappers for One Class
797
988
==============================
799
The first mapper created for a certain class is known as that class's "primary mapper." Other mappers can be created as well on the "load side" - these are called **secondary mappers**. This is a mapper that must be constructed with the keyword argument ``non_primary=True``, and represents a load-only mapper. Objects that are loaded with a secondary mapper will have their save operation processed by the primary mapper. It is also invalid to add new :func:`~sqlalchemy.orm.relationship` objects to a non-primary mapper. To use this mapper with the Session, specify it to the :class:`~sqlalchemy.orm.session.Session.query` method:
990
The first mapper created for a certain class is known as that class's "primary
991
mapper." Other mappers can be created as well on the "load side" - these are
992
called **secondary mappers**. This is a mapper that must be constructed with
993
the keyword argument ``non_primary=True``, and represents a load-only mapper.
994
Objects that are loaded with a secondary mapper will have their save operation
995
processed by the primary mapper. It is also invalid to add new
996
:func:`~sqlalchemy.orm.relationship` objects to a non-primary mapper. To use
997
this mapper with the Session, specify it to the
998
:class:`~sqlalchemy.orm.session.Session.query` method:
803
1002
.. sourcecode:: python+sql
805
1004
# primary mapper
806
mapper(User, users_table)
1005
mapper(User, user_table)
808
1007
# make a secondary mapper to load User against a join
809
othermapper = mapper(User, users_table.join(someothertable), non_primary=True)
1008
othermapper = mapper(User, user_table.join(someothertable), non_primary=True)
812
1011
result = session.query(othermapper).select()
814
The "non primary mapper" is a rarely needed feature of SQLAlchemy; in most cases, the :class:`~sqlalchemy.orm.query.Query` object can produce any kind of query that's desired. It's recommended that a straight :class:`~sqlalchemy.orm.query.Query` be used in place of a non-primary mapper unless the mapper approach is absolutely needed. Current use cases for the "non primary mapper" are when you want to map the class to a particular select statement or view to which additional query criterion can be added, and for when the particular mapped select statement or view is to be placed in a :func:`~sqlalchemy.orm.relationship` of a parent mapper.
1013
The "non primary mapper" is a rarely needed feature of SQLAlchemy; in most
1014
cases, the :class:`~sqlalchemy.orm.query.Query` object can produce any kind of
1015
query that's desired. It's recommended that a straight
1016
:class:`~sqlalchemy.orm.query.Query` be used in place of a non-primary mapper
1017
unless the mapper approach is absolutely needed. Current use cases for the
1018
"non primary mapper" are when you want to map the class to a particular select
1019
statement or view to which additional query criterion can be added, and for
1020
when the particular mapped select statement or view is to be placed in a
1021
:func:`~sqlalchemy.orm.relationship` of a parent mapper.
816
1023
Multiple "Persistence" Mappers for One Class
817
1024
=============================================
819
The non_primary mapper defines alternate mappers for the purposes of loading objects. What if we want the same class to be *persisted* differently, such as to different tables ? SQLAlchemy
820
refers to this as the "entity name" pattern, and in Python one can use a recipe which creates
821
anonymous subclasses which are distinctly mapped. See the recipe at `Entity Name <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName>`_.
1026
The non_primary mapper defines alternate mappers for the purposes of loading
1027
objects. What if we want the same class to be *persisted* differently, such as
1028
to different tables ? SQLAlchemy refers to this as the "entity name" pattern,
1029
and in Python one can use a recipe which creates anonymous subclasses which
1030
are distinctly mapped. See the recipe at `Entity Name
1031
<http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName>`_.
823
1033
Constructors and Object Initialization
824
1034
=======================================
826
Mapping imposes no restrictions or requirements on the constructor (``__init__``) method for the class. You are free to require any arguments for the function
827
that you wish, assign attributes to the instance that are unknown to the ORM, and generally do anything else you would normally do when writing a constructor
830
The SQLAlchemy ORM does not call ``__init__`` when recreating objects from database rows. The ORM's process is somewhat akin to the Python standard library's
831
``pickle`` module, invoking the low level ``__new__`` method and then quietly restoring attributes directly on the instance rather than calling ``__init__``.
833
If you need to do some setup on database-loaded instances before they're ready to use, you can use the ``@reconstructor`` decorator to tag a method as the ORM
834
counterpart to ``__init__``. SQLAlchemy will call this method with no arguments every time it loads or reconstructs one of your instances. This is useful for
835
recreating transient properties that are normally assigned in your ``__init__``::
1036
Mapping imposes no restrictions or requirements on the constructor
1037
(``__init__``) method for the class. You are free to require any arguments for
1038
the function that you wish, assign attributes to the instance that are unknown
1039
to the ORM, and generally do anything else you would normally do when writing
1040
a constructor for a Python class.
1042
The SQLAlchemy ORM does not call ``__init__`` when recreating objects from
1043
database rows. The ORM's process is somewhat akin to the Python standard
1044
library's ``pickle`` module, invoking the low level ``__new__`` method and
1045
then quietly restoring attributes directly on the instance rather than calling
1048
If you need to do some setup on database-loaded instances before they're ready
1049
to use, you can use the ``@reconstructor`` decorator to tag a method as the
1050
ORM counterpart to ``__init__``. SQLAlchemy will call this method with no
1051
arguments every time it loads or reconstructs one of your instances. This is
1052
useful for recreating transient properties that are normally assigned in your
837
1055
from sqlalchemy import orm
846
1064
def init_on_load(self):
849
When ``obj = MyMappedClass()`` is executed, Python calls the ``__init__`` method as normal and the ``data`` argument is required. When instances are loaded
850
during a :class:`~sqlalchemy.orm.query.Query` operation as in ``query(MyMappedClass).one()``, ``init_on_load`` is called instead.
852
Any method may be tagged as the :func:`~sqlalchemy.orm.reconstructor`, even the ``__init__`` method. SQLAlchemy will call the reconstructor method with no arguments. Scalar
853
(non-collection) database-mapped attributes of the instance will be available for use within the function. Eagerly-loaded collections are generally not yet
854
available and will usually only contain the first element. ORM state changes made to objects at this stage will not be recorded for the next flush()
855
operation, so the activity within a reconstructor should be conservative.
857
While the ORM does not call your ``__init__`` method, it will modify the class's ``__init__`` slightly. The method is lightly wrapped to act as a trigger for
858
the ORM, allowing mappers to be compiled automatically and will fire a :func:`~sqlalchemy.orm.interfaces.MapperExtension.init_instance` event that :class:`~sqlalchemy.orm.interfaces.MapperExtension` objects may listen for.
859
:class:`~sqlalchemy.orm.interfaces.MapperExtension` objects can also listen for a ``reconstruct_instance`` event, analogous to the :func:`~sqlalchemy.orm.reconstructor` decorator above.
1067
When ``obj = MyMappedClass()`` is executed, Python calls the ``__init__``
1068
method as normal and the ``data`` argument is required. When instances are
1069
loaded during a :class:`~sqlalchemy.orm.query.Query` operation as in
1070
``query(MyMappedClass).one()``, ``init_on_load`` is called instead.
1072
Any method may be tagged as the :func:`~sqlalchemy.orm.reconstructor`, even
1073
the ``__init__`` method. SQLAlchemy will call the reconstructor method with no
1074
arguments. Scalar (non-collection) database-mapped attributes of the instance
1075
will be available for use within the function. Eagerly-loaded collections are
1076
generally not yet available and will usually only contain the first element.
1077
ORM state changes made to objects at this stage will not be recorded for the
1078
next flush() operation, so the activity within a reconstructor should be
1081
While the ORM does not call your ``__init__`` method, it will modify the
1082
class's ``__init__`` slightly. The method is lightly wrapped to act as a
1083
trigger for the ORM, allowing mappers to be compiled automatically and will
1084
fire a :func:`~sqlalchemy.orm.interfaces.MapperExtension.init_instance` event
1085
that :class:`~sqlalchemy.orm.interfaces.MapperExtension` objects may listen
1086
for. :class:`~sqlalchemy.orm.interfaces.MapperExtension` objects can also
1087
listen for a ``reconstruct_instance`` event, analogous to the
1088
:func:`~sqlalchemy.orm.reconstructor` decorator above.
861
1090
.. autofunction:: reconstructor