1
=============================
2
What's new in SQLAlchemy 0.5?
3
=============================
5
.. admonition:: About this Document
7
This document describes changes between SQLAlchemy version 0.4,
8
last released October 12, 2008, and SQLAlchemy version 0.5,
9
last released January 16, 2010.
11
Document date: August 4, 2009
14
This guide documents API changes which affect users
15
migrating their applications from the 0.4 series of
16
SQLAlchemy to 0.5. It's also recommended for those working
17
from `Essential SQLAlchemy
18
<http://oreilly.com/catalog/9780596516147/>`_, which only
19
covers 0.4 and seems to even have some old 0.3isms in it.
20
Note that SQLAlchemy 0.5 removes many behaviors which were
21
deprecated throughout the span of the 0.4 series, and also
22
deprecates more behaviors specific to 0.4.
24
Major Documentation Changes
25
===========================
27
Some sections of the documentation have been completely
28
rewritten and can serve as an introduction to new ORM
29
features. The ``Query`` and ``Session`` objects in
30
particular have some distinct differences in API and
31
behavior which fundamentally change many of the basic ways
32
things are done, particularly with regards to constructing
33
highly customized ORM queries and dealing with stale session
34
state, commits and rollbacks.
37
<http://www.sqlalchemy.org/docs/05/ormtutorial.html>`_
39
* `Session Documentation
40
<http://www.sqlalchemy.org/docs/05/session.html>`_
45
Another source of information is documented within a series
46
of unit tests illustrating up to date usages of some common
47
``Query`` patterns; this file can be viewed at
48
[source:sqlalchemy/trunk/test/orm/test_deprecations.py].
53
* Python 2.4 or higher is required. The SQLAlchemy 0.4 line
54
is the last version with Python 2.3 support.
56
Object Relational Mapping
57
=========================
59
* **Column level expressions within Query.** - as detailed
61
<http://www.sqlalchemy.org/docs/05/ormtutorial.html>`_,
62
``Query`` has the capability to create specific SELECT
63
statements, not just those against full rows:
67
session.query(User.name, func.count(Address.id).label("numaddresses")).join(Address).group_by(User.name)
69
The tuples returned by any multi-column/entity query are
74
for row in session.query(User.name, func.count(Address.id).label('numaddresses')).join(Address).group_by(User.name):
75
print "name", row.name, "number", row.numaddresses
77
``Query`` has a ``statement`` accessor, as well as a
78
``subquery()`` method which allow ``Query`` to be used to
79
create more complex combinations:
83
subq = session.query(Keyword.id.label('keyword_id')).filter(Keyword.name.in_(['beans', 'carrots'])).subquery()
84
recipes = session.query(Recipe).filter(exists().
85
where(Recipe.id==recipe_keywords.c.recipe_id).
86
where(recipe_keywords.c.keyword_id==subq.c.keyword_id)
89
* **Explicit ORM aliases are recommended for aliased joins**
90
- The ``aliased()`` function produces an "alias" of a
91
class, which allows fine-grained control of aliases in
92
conjunction with ORM queries. While a table-level alias
93
(i.e. ``table.alias()``) is still usable, an ORM level
94
alias retains the semantics of the ORM mapped object which
95
is significant for inheritance mappings, options, and
96
other scenarios. E.g.:
100
Friend = aliased(Person)
101
session.query(Person, Friend).join((Friend, Person.friends)).all()
103
* **query.join() greatly enhanced.** - You can now specify
104
the target and ON clause for a join in multiple ways. A
105
target class alone can be provided where SQLA will attempt
106
to form a join to it via foreign key in the same way as
107
``table.join(someothertable)``. A target and an explicit
108
ON condition can be provided, where the ON condition can
109
be a ``relation()`` name, an actual class descriptor, or a
110
SQL expression. Or the old way of just a ``relation()``
111
name or class descriptor works too. See the ORM tutorial
112
which has several examples.
114
* **Declarative is recommended for applications which don't
115
require (and don't prefer) abstraction between tables and
116
mappers** - The [/docs/05/reference/ext/declarative.html
117
Declarative] module, which is used to combine the
118
expression of ``Table``, ``mapper()``, and user defined
119
class objects together, is highly recommended as it
120
simplifies application configuration, ensures the "one
121
mapper per class" pattern, and allows the full range of
122
configuration available to distinct ``mapper()`` calls.
123
Separate ``mapper()`` and ``Table`` usage is now referred
124
to as "classical SQLAlchemy usage" and of course is freely
125
mixable with declarative.
127
* **The .c. attribute has been removed** from classes (i.e.
128
``MyClass.c.somecolumn``). As is the case in 0.4, class-
129
level properties are usable as query elements, i.e.
130
``Class.c.propname`` is now superseded by
131
``Class.propname``, and the ``c`` attribute continues to
132
remain on ``Table`` objects where they indicate the
133
namespace of ``Column`` objects present on the table.
135
To get at the Table for a mapped class (if you didn't keep
140
table = class_mapper(someclass).mapped_table
142
Iterate through columns:
149
Work with a specific column:
155
The class-bound descriptors support the full set of Column
156
operators as well as the documented relation-oriented
157
operators like ``has()``, ``any()``, ``contains()``, etc.
159
The reason for the hard removal of ``.c.`` is that in 0.5,
160
class-bound descriptors carry potentially different
161
meaning, as well as information regarding class mappings,
162
versus plain ``Column`` objects - and there are use cases
163
where you'd specifically want to use one or the other.
164
Generally, using class-bound descriptors invokes a set of
165
mapping/polymorphic aware translations, and using table-
166
bound columns does not. In 0.4, these translations were
167
applied across the board to all expressions, but 0.5
168
differentiates completely between columns and mapped
169
descriptors, only applying translations to the latter. So
170
in many cases, particularly when dealing with joined table
171
inheritance configurations as well as when using
172
``query(<columns>)``, ``Class.propname`` and
173
``table.c.colname`` are not interchangeable.
175
For example, ``session.query(users.c.id, users.c.name)``
176
is different versus ``session.query(User.id, User.name)``;
177
in the latter case, the ``Query`` is aware of the mapper
178
in use and further mapper-specific operations like
179
``query.join(<propname>)``, ``query.with_parent()`` etc.
180
may be used, but in the former case cannot. Additionally,
181
in polymorphic inheritance scenarios, the class-bound
182
descriptors refer to the columns present in the
183
polymorphic selectable in use, not necessarily the table
184
column which directly corresponds to the descriptor. For
185
example, a set of classes related by joined-table
186
inheritance to the ``person`` table along the
187
``person_id`` column of each table will all have their
188
``Class.person_id`` attribute mapped to the ``person_id``
189
column in ``person``, and not their subclass table.
190
Version 0.4 would map this behavior onto table-bound
191
``Column`` objects automatically. In 0.5, this automatic
192
conversion has been removed, so that you in fact *can* use
193
table-bound columns as a means to override the
194
translations which occur with polymorphic querying; this
195
allows ``Query`` to be able to create optimized selects
196
among joined-table or concrete-table inheritance setups,
197
as well as portable subqueries, etc.
199
* **Session Now Synchronizes Automatically with
200
Transactions.** Session now synchronizes against the
201
transaction automatically by default, including autoflush
202
and autoexpire. A transaction is present at all times
203
unless disabled using the ``autocommit`` option. When all
204
three flags are set to their default, the Session recovers
205
gracefully after rollbacks and it's very difficult to get
206
stale data into the session. See the new Session
207
documentation for details.
209
* **Implicit Order By Is Removed**. This will impact ORM
210
users who rely upon SA's "implicit ordering" behavior,
211
which states that all Query objects which don't have an
212
``order_by()`` will ORDER BY the "id" or "oid" column of
213
the primary mapped table, and all lazy/eagerly loaded
214
collections apply a similar ordering. In 0.5, automatic
215
ordering must be explicitly configured on ``mapper()`` and
216
``relation()`` objects (if desired), or otherwise when
219
To convert an 0.4 mapping to 0.5, such that its ordering
220
behavior will be extremely similar to 0.4 or previous, use
221
the ``order_by`` setting on ``mapper()`` and
226
mapper(User, users, properties={
227
'addresses':relation(Address, order_by=addresses.c.id)
228
}, order_by=users.c.id)
230
To set ordering on a backref, use the ``backref()``
235
'keywords':relation(Keyword, secondary=item_keywords,
236
order_by=keywords.c.name, backref=backref('items', order_by=items.c.id))
238
Using declarative ? To help with the new ``order_by``
239
requirement, ``order_by`` and friends can now be set using
240
strings which are evaluated in Python later on (this works
241
**only** with declarative, not plain mappers):
245
class MyClass(MyDeclarativeBase):
247
'addresses':relation("Address", order_by="Address.id")
249
It's generally a good idea to set ``order_by`` on
250
``relation()s`` which load list-based collections of
251
items, since that ordering cannot otherwise be affected.
252
Other than that, the best practice is to use
253
``Query.order_by()`` to control ordering of the primary
254
entities being loaded.
257
autoflush=True/autoexpire=True/autocommit=False.** - To
258
set it up, just call ``sessionmaker()`` with no arguments.
259
The name ``transactional=True`` is now
260
``autocommit=False``. Flushes occur upon each query
261
issued (disable with ``autoflush=False``), within each
262
``commit()`` (as always), and before each
263
``begin_nested()`` (so rolling back to the SAVEPOINT is
264
meaningful). All objects are expired after each
265
``commit()`` and after each ``rollback()``. After
266
rollback, pending objects are expunged, deleted objects
267
move back to persistent. These defaults work together
268
very nicely and there's really no more need for old
269
techniques like ``clear()`` (which is renamed to
270
``expunge_all()`` as well).
272
P.S.: sessions are now reusable after a ``rollback()``.
273
Scalar and collection attribute changes, adds and deletes
276
* **session.add() replaces session.save(), session.update(),
277
session.save_or_update().** - the
278
``session.add(someitem)`` and ``session.add_all([list of
279
items])`` methods replace ``save()``, ``update()``, and
280
``save_or_update()``. Those methods will remain
281
deprecated throughout 0.5.
283
* **backref configuration made less verbose.** - The
284
``backref()`` function now uses the ``primaryjoin`` and
285
``secondaryjoin`` arguments of the forwards-facing
286
``relation()`` when they are not explicitly stated. It's
287
no longer necessary to specify
288
``primaryjoin``/``secondaryjoin`` in both directions
291
* **Simplified polymorphic options.** - The ORM's
292
"polymorphic load" behavior has been simplified. In 0.4,
293
mapper() had an argument called ``polymorphic_fetch``
294
which could be configured as ``select`` or ``deferred``.
295
This option is removed; the mapper will now just defer any
296
columns which were not present in the SELECT statement.
297
The actual SELECT statement used is controlled by the
298
``with_polymorphic`` mapper argument (which is also in 0.4
299
and replaces ``select_table``), as well as the
300
``with_polymorphic()`` method on ``Query`` (also in 0.4).
302
An improvement to the deferred loading of inheriting
303
classes is that the mapper now produces the "optimized"
304
version of the SELECT statement in all cases; that is, if
305
class B inherits from A, and several attributes only
306
present on class B have been expired, the refresh
307
operation will only include B's table in the SELECT
308
statement and will not JOIN to A.
310
* The ``execute()`` method on ``Session`` converts plain
311
strings into ``text()`` constructs, so that bind
312
parameters may all be specified as ":bindname" without
313
needing to call ``text()`` explicitly. If "raw" SQL is
314
desired here, use ``session.connection().execute("raw
317
* ``session.Query().iterate_instances()`` has been renamed
318
to just ``instances()``. The old ``instances()`` method
319
returning a list instead of an iterator no longer exists.
320
If you were relying on that behavior, you should use
321
``list(your_query.instances())``.
326
In 0.5 we're moving forward with more ways to modify and
327
extend the ORM. Heres a summary:
329
* **MapperExtension.** - This is the classic extension
330
class, which remains. Methods which should rarely be
331
needed are ``create_instance()`` and
332
``populate_instance()``. To control the initialization of
333
an object when it's loaded from the database, use the
334
``reconstruct_instance()`` method, or more easily the
335
``@reconstructor`` decorator described in the
338
* **SessionExtension.** - This is an easy to use extension
339
class for session events. In particular, it provides
340
``before_flush()``, ``after_flush()`` and
341
``after_flush_postexec()`` methods. It's usage is
342
recommended over ``MapperExtension.before_XXX`` in many
343
cases since within ``before_flush()`` you can modify the
344
flush plan of the session freely, something which cannot
345
be done from within ``MapperExtension``.
347
* **AttributeExtension.** - This class is now part of the
348
public API, and allows the interception of userland events
349
on attributes, including attribute set and delete
350
operations, and collection appends and removes. It also
351
allows the value to be set or appended to be modified.
352
The ``@validates`` decorator, described in the
353
documentation, provides a quick way to mark any mapped
354
attributes as being "validated" by a particular class
357
* **Attribute Instrumentation Customization.** - An API is
358
provided for ambitious efforts to entirely replace
359
SQLAlchemy's attribute instrumentation, or just to augment
360
it in some cases. This API was produced for the purposes
361
of the Trellis toolkit, but is available as a public API.
362
Some examples are provided in the distribution in the
363
``/examples/custom_attributes`` directory.
368
* **String with no length no longer generates TEXT, it
369
generates VARCHAR** - The ``String`` type no longer
370
magically converts into a ``Text`` type when specified
371
with no length. This only has an effect when CREATE TABLE
372
is issued, as it will issue ``VARCHAR`` with no length
373
parameter, which is not valid on many (but not all)
374
databases. To create a TEXT (or CLOB, i.e. unbounded
375
string) column, use the ``Text`` type.
377
* **PickleType() with mutable=True requires an __eq__()
378
method** - The ``PickleType`` type needs to compare values
379
when mutable=True. The method of comparing
380
``pickle.dumps()`` is inefficient and unreliable. If an
381
incoming object does not implement ``__eq__()`` and is
382
also not ``None``, the ``dumps()`` comparison is used but
383
a warning is raised. For types which implement
384
``__eq__()`` which includes all dictionaries, lists, etc.,
385
comparison will use ``==`` and is now reliable by default.
387
* **convert_bind_param() and convert_result_value() methods
388
of TypeEngine/TypeDecorator are removed.** - The O'Reilly
389
book unfortunately documented these methods even though
390
they were deprecated post 0.3. For a user-defined type
391
which subclasses ``TypeEngine``, the ``bind_processor()``
392
and ``result_processor()`` methods should be used for
393
bind/result processing. Any user defined type, whether
394
extending ``TypeEngine`` or ``TypeDecorator``, which uses
395
the old 0.3 style can be easily adapted to the new style
396
using the following adapter:
400
class AdaptOldConvertMethods(object):
401
"""A mixin which adapts 0.3-style convert_bind_param and
402
convert_result_value methods
405
def bind_processor(self, dialect):
407
return self.convert_bind_param(value, dialect)
410
def result_processor(self, dialect):
412
return self.convert_result_value(value, dialect)
415
def convert_result_value(self, value, dialect):
418
def convert_bind_param(self, value, dialect):
421
To use the above mixin:
425
class MyType(AdaptOldConvertMethods, TypeEngine):
428
* The ``quote`` flag on ``Column`` and ``Table`` as well as
429
the ``quote_schema`` flag on ``Table`` now control quoting
430
both positively and negatively. The default is ``None``,
431
meaning let regular quoting rules take effect. When
432
``True``, quoting is forced on. When ``False``, quoting
435
* Column ``DEFAULT`` value DDL can now be more conveniently
436
specified with ``Column(..., server_default='val')``,
437
deprecating ``Column(..., PassiveDefault('val'))``.
438
``default=`` is now exclusively for Python-initiated
439
default values, and can coexist with server_default. A
440
new ``server_default=FetchedValue()`` replaces the
441
``PassiveDefault('')`` idiom for marking columns as
442
subject to influence from external triggers and has no DDL
445
* SQLite's ``DateTime``, ``Time`` and ``Date`` types now
446
**only accept datetime objects, not strings** as bind
447
parameter input. If you'd like to create your own
448
"hybrid" type which accepts strings and returns results as
449
date objects (from whatever format you'd like), create a
450
``TypeDecorator`` that builds on ``String``. If you only
451
want string-based dates, just use ``String``.
453
* Additionally, the ``DateTime`` and ``Time`` types, when
454
used with SQLite, now represent the "microseconds" field
455
of the Python ``datetime.datetime`` object in the same
456
manner as ``str(datetime)`` - as fractional seconds, not a
457
count of microseconds. That is:
461
dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125) # 125 usec
464
'2008-06-27 12:00:00.125'
467
'2008-06-27 12:00:00.000125'
469
So if an existing SQLite file-based database intends to be
470
used across 0.4 and 0.5, you either have to upgrade the
471
datetime columns to store the new format (NOTE: please
472
test this, I'm pretty sure its correct):
476
UPDATE mytable SET somedatecol =
477
substr(somedatecol, 0, 19) || '.' || substr((substr(somedatecol, 21, -1) / 1000000), 3, -1);
479
or, enable "legacy" mode as follows:
483
from sqlalchemy.databases.sqlite import DateTimeMixin
484
DateTimeMixin.__legacy_microseconds__ = True
486
Connection Pool no longer threadlocal by default
487
================================================
489
0.4 has an unfortunate default setting of
490
"pool_threadlocal=True", leading to surprise behavior when,
491
for example, using multiple Sessions within a single thread.
492
This flag is now off in 0.5. To re-enable 0.4's behavior,
493
specify ``pool_threadlocal=True`` to ``create_engine()``, or
494
alternatively use the "threadlocal" strategy via
495
``strategy="threadlocal"``.
497
\*args Accepted, \*args No Longer Accepted
498
==========================================
500
The policy with ``method(\*args)`` vs. ``method([args])``
501
is, if the method accepts a variable-length set of items
502
which represent a fixed structure, it takes ``\*args``. If
503
the method accepts a variable-length set of items that are
504
data-driven, it takes ``[args]``.
506
* The various Query.options() functions ``eagerload()``,
507
``eagerload_all()``, ``lazyload()``, ``contains_eager()``,
508
``defer()``, ``undefer()`` all accept variable-length
509
``\*keys`` as their argument now, which allows a path to
510
be formulated using descriptors, ie.:
514
query.options(eagerload_all(User.orders, Order.items, Item.keywords))
516
A single array argument is still accepted for backwards
519
* Similarly, the ``Query.join()`` and ``Query.outerjoin()``
520
methods accept a variable length \*args, with a single
521
array accepted for backwards compatibility:
525
query.join('orders', 'items')
526
query.join(User.orders, Order.items)
528
* the ``in_()`` method on columns and similar only accepts a
529
list argument now. It no longer accepts ``\*args``.
534
* **entity_name** - This feature was always problematic and
535
rarely used. 0.5's more deeply fleshed out use cases
536
revealed further issues with ``entity_name`` which led to
537
its removal. If different mappings are required for a
538
single class, break the class into separate subclasses and
539
map them separately. An example of this is at
540
[wiki:UsageRecipes/EntityName]. More information
541
regarding rationale is described at http://groups.google.c
542
om/group/sqlalchemy/browse_thread/thread/9e23a0641a88b96d?
545
* **get()/load() cleanup**
548
The ``load()`` method has been removed. It's
549
functionality was kind of arbitrary and basically copied
550
from Hibernate, where it's also not a particularly
553
To get equivalent functionality:
557
x = session.query(SomeClass).populate_existing().get(7)
559
``Session.get(cls, id)`` and ``Session.load(cls, id)``
560
have been removed. ``Session.get()`` is redundant vs.
561
``session.query(cls).get(id)``.
563
``MapperExtension.get()`` is also removed (as is
564
``MapperExtension.load()``). To override the
565
functionality of ``Query.get()``, use a subclass:
569
class MyQuery(Query):
570
def get(self, ident):
573
session = sessionmaker(query_cls=MyQuery)()
575
ad1 = session.query(Address).get(1)
577
* ``sqlalchemy.orm.relation()``
580
The following deprecated keyword arguments have been
583
foreignkey, association, private, attributeext, is_backref
585
In particular, ``attributeext`` is replaced with
586
``extension`` - the ``AttributeExtension`` class is now in
589
* ``session.Query()``
592
The following deprecated functions have been removed:
594
list, scalar, count_by, select_whereclause, get_by,
595
select_by, join_by, selectfirst, selectone, select,
596
execute, select_statement, select_text, join_to, join_via,
597
selectfirst_by, selectone_by, apply_max, apply_min,
600
Additionally, the ``id`` keyword argument to ``join()``,
601
``outerjoin()``, ``add_entity()`` and ``add_column()`` has
602
been removed. To target table aliases in ``Query`` to
603
result columns, use the ``aliased`` construct:
607
from sqlalchemy.orm import aliased
608
address_alias = aliased(Address)
609
print session.query(User, address_alias).join((address_alias, User.addresses)).all()
611
* ``sqlalchemy.orm.Mapper``
617
* get_session() - this method was not very noticeable, but
618
had the effect of associating lazy loads with a
619
particular session even if the parent object was
620
entirely detached, when an extension such as
621
``scoped_session()`` or the old ``SessionContextExt``
622
was used. It's possible that some applications which
623
relied upon this behavior will no longer work as
624
expected; but the better programming practice here is
625
to always ensure objects are present within sessions if
626
database access from their attributes are required.
628
* ``mapper(MyClass, mytable)``
631
Mapped classes no are longer instrumented with a "c" class
632
attribute; e.g. ``MyClass.c``
634
* ``sqlalchemy.orm.collections``
637
The _prepare_instrumentation alias for
638
prepare_instrumentation has been removed.
643
Removed the ``EXT_PASS`` alias of ``EXT_CONTINUE``.
645
* ``sqlalchemy.engine``
648
The alias from ``DefaultDialect.preexecute_sequences`` to
649
``.preexecute_pk_sequences`` has been removed.
651
The deprecated engine_descriptors() function has been
654
* ``sqlalchemy.ext.activemapper``
659
* ``sqlalchemy.ext.assignmapper``
664
* ``sqlalchemy.ext.associationproxy``
667
Pass-through of keyword args on the proxy's
668
``.append(item, \**kw)`` has been removed and is now
669
simply ``.append(item)``
671
* ``sqlalchemy.ext.selectresults``,
672
``sqlalchemy.mods.selectresults``
676
* ``sqlalchemy.ext.declarative``
679
``declared_synonym()`` removed.
681
* ``sqlalchemy.ext.sessioncontext``
689
The ``SADeprecationWarning`` alias to
690
``sqlalchemy.exc.SADeprecationWarning`` has been removed.
695
``exc.AssertionError`` has been removed and usage replaced
696
by the Python built-in of the same name.
698
* ``sqlalchemy.databases.mysql``
701
The deprecated ``get_version_info`` dialect method has
707
* ``sqlalchemy.exceptions`` is now ``sqlalchemy.exc``
710
The module may still be imported under the old name until
713
* ``FlushError``, ``ConcurrentModificationError``,
714
``UnmappedColumnError`` -> sqlalchemy.orm.exc
716
These exceptions moved to the orm package. Importing
717
'sqlalchemy.orm' will install aliases in sqlalchemy.exc
718
for compatibility until 0.6.
720
* ``sqlalchemy.logging`` -> ``sqlalchemy.log``
723
This internal module was renamed. No longer needs to be
724
special cased when packaging SA with py2app and similar
725
tools that scan imports.
727
* ``session.Query().iterate_instances()`` ->
728
``session.Query().instances()``.
733
* ``Session.save()``, ``Session.update()``,
734
``Session.save_or_update()``
736
All three replaced by ``Session.add()``
738
* ``sqlalchemy.PassiveDefault``
741
Use ``Column(server_default=...)`` Translates to
742
sqlalchemy.DefaultClause() under the hood.
744
* ``session.Query().iterate_instances()``. It has been
745
renamed to ``instances()``.
added
removed
doc/_sources/changelog/migration_05.txt
