About this Document
This document describes changes between SQLAlchemy version 0.4, last released October 12, 2008, and SQLAlchemy version 0.5, last released January 16, 2010.
Document date: August 4, 2009
This guide documents API changes which affect users migrating their applications from the 0.4 series of SQLAlchemy to 0.5. It’s also recommended for those working from Essential SQLAlchemy, which only covers 0.4 and seems to even have some old 0.3isms in it. Note that SQLAlchemy 0.5 removes many behaviors which were deprecated throughout the span of the 0.4 series, and also deprecates more behaviors specific to 0.4.
Some sections of the documentation have been completely
rewritten and can serve as an introduction to new ORM
features. The Query and Session objects in
particular have some distinct differences in API and
behavior which fundamentally change many of the basic ways
things are done, particularly with regards to constructing
highly customized ORM queries and dealing with stale session
state, commits and rollbacks.
Another source of information is documented within a series
of unit tests illustrating up to date usages of some common
Query patterns; this file can be viewed at
[source:sqlalchemy/trunk/test/orm/test_deprecations.py].
Column level expressions within Query. - as detailed
in the tutorial,
Query has the capability to create specific SELECT
statements, not just those against full rows:
session.query(User.name, func.count(Address.id).label("numaddresses")).join(Address).group_by(User.name)The tuples returned by any multi-column/entity query are named’ tuples:
for row in session.query(User.name, func.count(Address.id).label('numaddresses')).join(Address).group_by(User.name):
print("name", row.name, "number", row.numaddresses)Query has a statement accessor, as well as a
subquery() method which allow Query to be used to
create more complex combinations:
subq = session.query(Keyword.id.label('keyword_id')).filter(Keyword.name.in_(['beans', 'carrots'])).subquery()
recipes = session.query(Recipe).filter(exists().
where(Recipe.id==recipe_keywords.c.recipe_id).
where(recipe_keywords.c.keyword_id==subq.c.keyword_id)
)Explicit ORM aliases are recommended for aliased joins
- The aliased() function produces an “alias” of a
class, which allows fine-grained control of aliases in
conjunction with ORM queries. While a table-level alias
(i.e. table.alias()) is still usable, an ORM level
alias retains the semantics of the ORM mapped object which
is significant for inheritance mappings, options, and
other scenarios. E.g.:
Friend = aliased(Person)
session.query(Person, Friend).join((Friend, Person.friends)).all()query.join() greatly enhanced. - You can now specify
the target and ON clause for a join in multiple ways. A
target class alone can be provided where SQLA will attempt
to form a join to it via foreign key in the same way as
table.join(someothertable). A target and an explicit
ON condition can be provided, where the ON condition can
be a relation() name, an actual class descriptor, or a
SQL expression. Or the old way of just a relation()
name or class descriptor works too. See the ORM tutorial
which has several examples.
Declarative is recommended for applications which don’t
require (and don’t prefer) abstraction between tables and
mappers - The [/docs/05/reference/ext/declarative.html
Declarative] module, which is used to combine the
expression of Table, mapper(), and user defined
class objects together, is highly recommended as it
simplifies application configuration, ensures the “one
mapper per class” pattern, and allows the full range of
configuration available to distinct mapper() calls.
Separate mapper() and Table usage is now referred
to as “classical SQLAlchemy usage” and of course is freely
mixable with declarative.
The .c. attribute has been removed from classes (i.e.
MyClass.c.somecolumn). As is the case in 0.4, class-
level properties are usable as query elements, i.e.
Class.c.propname is now superseded by
Class.propname, and the c attribute continues to
remain on Table objects where they indicate the
namespace of Column objects present on the table.
To get at the Table for a mapped class (if you didn’t keep it around already):
table = class_mapper(someclass).mapped_tableIterate through columns:
for col in table.c:
print(col)Work with a specific column:
table.c.somecolumnThe class-bound descriptors support the full set of Column
operators as well as the documented relation-oriented
operators like has(), any(), contains(), etc.
The reason for the hard removal of .c. is that in 0.5,
class-bound descriptors carry potentially different
meaning, as well as information regarding class mappings,
versus plain Column objects - and there are use cases
where you’d specifically want to use one or the other.
Generally, using class-bound descriptors invokes a set of
mapping/polymorphic aware translations, and using table-
bound columns does not. In 0.4, these translations were
applied across the board to all expressions, but 0.5
differentiates completely between columns and mapped
descriptors, only applying translations to the latter. So
in many cases, particularly when dealing with joined table
inheritance configurations as well as when using
query(<columns>), Class.propname and
table.c.colname are not interchangeable.
For example, session.query(users.c.id, users.c.name)
is different versus session.query(User.id, User.name);
in the latter case, the Query is aware of the mapper
in use and further mapper-specific operations like
query.join(<propname>), query.with_parent() etc.
may be used, but in the former case cannot. Additionally,
in polymorphic inheritance scenarios, the class-bound
descriptors refer to the columns present in the
polymorphic selectable in use, not necessarily the table
column which directly corresponds to the descriptor. For
example, a set of classes related by joined-table
inheritance to the person table along the
person_id column of each table will all have their
Class.person_id attribute mapped to the person_id
column in person, and not their subclass table.
Version 0.4 would map this behavior onto table-bound
Column objects automatically. In 0.5, this automatic
conversion has been removed, so that you in fact can use
table-bound columns as a means to override the
translations which occur with polymorphic querying; this
allows Query to be able to create optimized selects
among joined-table or concrete-table inheritance setups,
as well as portable subqueries, etc.
Session Now Synchronizes Automatically with
Transactions. Session now synchronizes against the
transaction automatically by default, including autoflush
and autoexpire. A transaction is present at all times
unless disabled using the autocommit option. When all
three flags are set to their default, the Session recovers
gracefully after rollbacks and it’s very difficult to get
stale data into the session. See the new Session
documentation for details.
Implicit Order By Is Removed. This will impact ORM
users who rely upon SA’s “implicit ordering” behavior,
which states that all Query objects which don’t have an
order_by() will ORDER BY the “id” or “oid” column of
the primary mapped table, and all lazy/eagerly loaded
collections apply a similar ordering. In 0.5, automatic
ordering must be explicitly configured on mapper() and
relation() objects (if desired), or otherwise when
using Query.
To convert an 0.4 mapping to 0.5, such that its ordering
behavior will be extremely similar to 0.4 or previous, use
the order_by setting on mapper() and
relation():
mapper(User, users, properties={
'addresses':relation(Address, order_by=addresses.c.id)
}, order_by=users.c.id)To set ordering on a backref, use the backref()
function:
'keywords':relation(Keyword, secondary=item_keywords,
order_by=keywords.c.name, backref=backref('items', order_by=items.c.id))Using declarative ? To help with the new order_by
requirement, order_by and friends can now be set using
strings which are evaluated in Python later on (this works
only with declarative, not plain mappers):
class MyClass(MyDeclarativeBase):
...
'addresses':relation("Address", order_by="Address.id")It’s generally a good idea to set order_by on
relation()s which load list-based collections of
items, since that ordering cannot otherwise be affected.
Other than that, the best practice is to use
Query.order_by() to control ordering of the primary
entities being loaded.
Session is now
autoflush=True/autoexpire=True/autocommit=False. - To
set it up, just call sessionmaker() with no arguments.
The name transactional=True is now
autocommit=False. Flushes occur upon each query
issued (disable with autoflush=False), within each
commit() (as always), and before each
begin_nested() (so rolling back to the SAVEPOINT is
meaningful). All objects are expired after each
commit() and after each rollback(). After
rollback, pending objects are expunged, deleted objects
move back to persistent. These defaults work together
very nicely and there’s really no more need for old
techniques like clear() (which is renamed to
expunge_all() as well).
P.S.: sessions are now reusable after a rollback().
Scalar and collection attribute changes, adds and deletes
are all rolled back.
session.add() replaces session.save(), session.update(),
session.save_or_update(). - the
session.add(someitem) and session.add_all([list of
items]) methods replace save(), update(), and
save_or_update(). Those methods will remain
deprecated throughout 0.5.
backref configuration made less verbose. - The
backref() function now uses the primaryjoin and
secondaryjoin arguments of the forwards-facing
relation() when they are not explicitly stated. It’s
no longer necessary to specify
primaryjoin/secondaryjoin in both directions
separately.
Simplified polymorphic options. - The ORM’s
“polymorphic load” behavior has been simplified. In 0.4,
mapper() had an argument called polymorphic_fetch
which could be configured as select or deferred.
This option is removed; the mapper will now just defer any
columns which were not present in the SELECT statement.
The actual SELECT statement used is controlled by the
with_polymorphic mapper argument (which is also in 0.4
and replaces select_table), as well as the
with_polymorphic() method on Query (also in 0.4).
An improvement to the deferred loading of inheriting classes is that the mapper now produces the “optimized” version of the SELECT statement in all cases; that is, if class B inherits from A, and several attributes only present on class B have been expired, the refresh operation will only include B’s table in the SELECT statement and will not JOIN to A.
The execute() method on Session converts plain
strings into text() constructs, so that bind
parameters may all be specified as “:bindname” without
needing to call text() explicitly. If “raw” SQL is
desired here, use session.connection().execute("raw
text").
session.Query().iterate_instances() has been renamed
to just instances(). The old instances() method
returning a list instead of an iterator no longer exists.
If you were relying on that behavior, you should use
list(your_query.instances()).
In 0.5 we’re moving forward with more ways to modify and extend the ORM. Heres a summary:
create_instance() and
populate_instance(). To control the initialization of
an object when it’s loaded from the database, use the
reconstruct_instance() method, or more easily the
@reconstructor decorator described in the
documentation.before_flush(), after_flush() and
after_flush_postexec() methods. This usage is
recommended over MapperExtension.before_XXX in many
cases since within before_flush() you can modify the
flush plan of the session freely, something which cannot
be done from within MapperExtension.@validates decorator, described in the
documentation, provides a quick way to mark any mapped
attributes as being “validated” by a particular class
method./examples/custom_attributes directory.String with no length no longer generates TEXT, it
generates VARCHAR - The String type no longer
magically converts into a Text type when specified
with no length. This only has an effect when CREATE TABLE
is issued, as it will issue VARCHAR with no length
parameter, which is not valid on many (but not all)
databases. To create a TEXT (or CLOB, i.e. unbounded
string) column, use the Text type.
PickleType() with mutable=True requires an __eq__()
method - The PickleType type needs to compare values
when mutable=True. The method of comparing
pickle.dumps() is inefficient and unreliable. If an
incoming object does not implement __eq__() and is
also not None, the dumps() comparison is used but
a warning is raised. For types which implement
__eq__() which includes all dictionaries, lists, etc.,
comparison will use == and is now reliable by default.
convert_bind_param() and convert_result_value() methods
of TypeEngine/TypeDecorator are removed. - The O’Reilly
book unfortunately documented these methods even though
they were deprecated post 0.3. For a user-defined type
which subclasses TypeEngine, the bind_processor()
and result_processor() methods should be used for
bind/result processing. Any user defined type, whether
extending TypeEngine or TypeDecorator, which uses
the old 0.3 style can be easily adapted to the new style
using the following adapter:
class AdaptOldConvertMethods(object):
"""A mixin which adapts 0.3-style convert_bind_param and
convert_result_value methods
"""
def bind_processor(self, dialect):
def convert(value):
return self.convert_bind_param(value, dialect)
return convert
def result_processor(self, dialect):
def convert(value):
return self.convert_result_value(value, dialect)
return convert
def convert_result_value(self, value, dialect):
return value
def convert_bind_param(self, value, dialect):
return valueTo use the above mixin:
class MyType(AdaptOldConvertMethods, TypeEngine):
# ...The quote flag on Column and Table as well as
the quote_schema flag on Table now control quoting
both positively and negatively. The default is None,
meaning let regular quoting rules take effect. When
True, quoting is forced on. When False, quoting
is forced off.
Column DEFAULT value DDL can now be more conveniently
specified with Column(..., server_default='val'),
deprecating Column(..., PassiveDefault('val')).
default= is now exclusively for Python-initiated
default values, and can coexist with server_default. A
new server_default=FetchedValue() replaces the
PassiveDefault('') idiom for marking columns as
subject to influence from external triggers and has no DDL
side effects.
SQLite’s DateTime, Time and Date types now
only accept datetime objects, not strings as bind
parameter input. If you’d like to create your own
“hybrid” type which accepts strings and returns results as
date objects (from whatever format you’d like), create a
TypeDecorator that builds on String. If you only
want string-based dates, just use String.
Additionally, the DateTime and Time types, when
used with SQLite, now represent the “microseconds” field
of the Python datetime.datetime object in the same
manner as str(datetime) - as fractional seconds, not a
count of microseconds. That is:
dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125) # 125 usec
# old way
'2008-06-27 12:00:00.125'
# new way
'2008-06-27 12:00:00.000125'So if an existing SQLite file-based database intends to be used across 0.4 and 0.5, you either have to upgrade the datetime columns to store the new format (NOTE: please test this, I’m pretty sure its correct):
UPDATE mytable SET somedatecol =
substr(somedatecol, 0, 19) || '.' || substr((substr(somedatecol, 21, -1) / 1000000), 3, -1);or, enable “legacy” mode as follows:
from sqlalchemy.databases.sqlite import DateTimeMixin
DateTimeMixin.__legacy_microseconds__ = True0.4 has an unfortunate default setting of
“pool_threadlocal=True”, leading to surprise behavior when,
for example, using multiple Sessions within a single thread.
This flag is now off in 0.5. To re-enable 0.4’s behavior,
specify pool_threadlocal=True to create_engine(), or
alternatively use the “threadlocal” strategy via
strategy="threadlocal".
The policy with method(\*args) vs. method([args])
is, if the method accepts a variable-length set of items
which represent a fixed structure, it takes \*args. If
the method accepts a variable-length set of items that are
data-driven, it takes [args].
The various Query.options() functions eagerload(),
eagerload_all(), lazyload(), contains_eager(),
defer(), undefer() all accept variable-length
\*keys as their argument now, which allows a path to
be formulated using descriptors, ie.:
query.options(eagerload_all(User.orders, Order.items, Item.keywords))A single array argument is still accepted for backwards compatibility.
Similarly, the Query.join() and Query.outerjoin()
methods accept a variable length *args, with a single
array accepted for backwards compatibility:
query.join('orders', 'items')
query.join(User.orders, Order.items)the in_() method on columns and similar only accepts a
list argument now. It no longer accepts \*args.
entity_name - This feature was always problematic and
rarely used. 0.5’s more deeply fleshed out use cases
revealed further issues with entity_name which led to
its removal. If different mappings are required for a
single class, break the class into separate subclasses and
map them separately. An example of this is at
[wiki:UsageRecipes/EntityName]. More information
regarding rationale is described at http://groups.google.c
om/group/sqlalchemy/browse_thread/thread/9e23a0641a88b96d?
hl=en .
get()/load() cleanup
The load() method has been removed. Its
functionality was kind of arbitrary and basically copied
from Hibernate, where it’s also not a particularly
meaningful method.
To get equivalent functionality:
x = session.query(SomeClass).populate_existing().get(7)Session.get(cls, id) and Session.load(cls, id)
have been removed. Session.get() is redundant vs.
session.query(cls).get(id).
MapperExtension.get() is also removed (as is
MapperExtension.load()). To override the
functionality of Query.get(), use a subclass:
class MyQuery(Query):
def get(self, ident):
# ...
session = sessionmaker(query_cls=MyQuery)()
ad1 = session.query(Address).get(1)sqlalchemy.orm.relation()
The following deprecated keyword arguments have been removed:
foreignkey, association, private, attributeext, is_backref
In particular, attributeext is replaced with
extension - the AttributeExtension class is now in
the public API.
session.Query()
The following deprecated functions have been removed:
list, scalar, count_by, select_whereclause, get_by, select_by, join_by, selectfirst, selectone, select, execute, select_statement, select_text, join_to, join_via, selectfirst_by, selectone_by, apply_max, apply_min, apply_avg, apply_sum
Additionally, the id keyword argument to join(),
outerjoin(), add_entity() and add_column() has
been removed. To target table aliases in Query to
result columns, use the aliased construct:
from sqlalchemy.orm import aliased
address_alias = aliased(Address)
print(session.query(User, address_alias).join((address_alias, User.addresses)).all())sqlalchemy.orm.Mapper
scoped_session() or the old SessionContextExt
was used. It’s possible that some applications which
relied upon this behavior will no longer work as
expected; but the better programming practice here is
to always ensure objects are present within sessions if
database access from their attributes are required.mapper(MyClass, mytable)
Mapped classes no are longer instrumented with a “c” class
attribute; e.g. MyClass.c
sqlalchemy.orm.collections
The _prepare_instrumentation alias for prepare_instrumentation has been removed.
sqlalchemy.orm
Removed the EXT_PASS alias of EXT_CONTINUE.
sqlalchemy.engine
The alias from DefaultDialect.preexecute_sequences to
.preexecute_pk_sequences has been removed.
The deprecated engine_descriptors() function has been removed.
sqlalchemy.ext.activemapper
Module removed.
sqlalchemy.ext.assignmapper
Module removed.
sqlalchemy.ext.associationproxy
Pass-through of keyword args on the proxy’s
.append(item, \**kw) has been removed and is now
simply .append(item)
sqlalchemy.ext.selectresults,
sqlalchemy.mods.selectresults
Modules removed.
sqlalchemy.ext.declarative
declared_synonym() removed.
sqlalchemy.ext.sessioncontext
Module removed.
sqlalchemy.log
The SADeprecationWarning alias to
sqlalchemy.exc.SADeprecationWarning has been removed.
sqlalchemy.exc
exc.AssertionError has been removed and usage replaced
by the Python built-in of the same name.
sqlalchemy.databases.mysql
The deprecated get_version_info dialect method has
been removed.
sqlalchemy.exceptions is now sqlalchemy.exc
The module may still be imported under the old name until 0.6.
FlushError, ConcurrentModificationError,
UnmappedColumnError -> sqlalchemy.orm.exc
These exceptions moved to the orm package. Importing ‘sqlalchemy.orm’ will install aliases in sqlalchemy.exc for compatibility until 0.6.
sqlalchemy.logging -> sqlalchemy.log
This internal module was renamed. No longer needs to be special cased when packaging SA with py2app and similar tools that scan imports.
session.Query().iterate_instances() ->
session.Query().instances().
Session.save(), Session.update(),
Session.save_or_update()
All three replaced by Session.add()
sqlalchemy.PassiveDefault
Use Column(server_default=...) Translates to
sqlalchemy.DefaultClause() under the hood.
session.Query().iterate_instances(). It has been
renamed to instances().