What’s new in SQLAlchemy 0.5?

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 usersmigrating their applications from the 0.4 series ofSQLAlchemy to 0.5. It’s also recommended for those workingfrom Essential SQLAlchemy, which onlycovers 0.4 and seems to even have some old 0.3isms in it.Note that SQLAlchemy 0.5 removes many behaviors which weredeprecated throughout the span of the 0.4 series, and alsodeprecates more behaviors specific to 0.4.

Major Documentation Changes

Some sections of the documentation have been completelyrewritten and can serve as an introduction to new ORMfeatures. The Query and Session objects inparticular have some distinct differences in API andbehavior which fundamentally change many of the basic waysthings are done, particularly with regards to constructinghighly customized ORM queries and dealing with stale sessionstate, commits and rollbacks.

Deprecations Source

Another source of information is documented within a seriesof unit tests illustrating up to date usages of some commonQuery patterns; this file can be viewed at[source:sqlalchemy/trunk/test/orm/test_deprecations.py].

Requirements Changes

  • Python 2.4 or higher is required. The SQLAlchemy 0.4 lineis the last version with Python 2.3 support.

Object Relational Mapping

  • Column level expressions within Query. - as detailedin the tutorial,Query has the capability to create specific SELECTstatements, not just those against full rows:
  1. 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 arenamed’ tuples:

  1. for row in session.query(User.name, func.count(Address.id).label('numaddresses')).join(Address).group_by(User.name):
  2.    print("name", row.name, "number", row.numaddresses)

Query has a statement accessor, as well as asubquery() method which allow Query to be used tocreate more complex combinations:

  1. subq = session.query(Keyword.id.label('keyword_id')).filter(Keyword.name.in_(['beans', 'carrots'])).subquery()
  2. recipes = session.query(Recipe).filter(exists().
  3.    where(Recipe.id==recipe_keywords.c.recipe_id).
  4.    where(recipe_keywords.c.keyword_id==subq.c.keyword_id)
  5. )
  • Explicit ORM aliases are recommended for aliased joins- The aliased() function produces an “alias” of aclass, which allows fine-grained control of aliases inconjunction with ORM queries. While a table-level alias(i.e. table.alias()) is still usable, an ORM levelalias retains the semantics of the ORM mapped object whichis significant for inheritance mappings, options, andother scenarios. E.g.:
  1. Friend = aliased(Person)
  2. session.query(Person, Friend).join((Friend, Person.friends)).all()

  • query.join() greatly enhanced. - You can now specifythe target and ON clause for a join in multiple ways. Atarget class alone can be provided where SQLA will attemptto form a join to it via foreign key in the same way astable.join(someothertable). A target and an explicitON condition can be provided, where the ON condition canbe a relation() name, an actual class descriptor, or aSQL expression. Or the old way of just a relation()name or class descriptor works too. See the ORM tutorialwhich has several examples.

  • Declarative is recommended for applications which don’trequire (and don’t prefer) abstraction between tables andmappers - The [/docs/05/reference/ext/declarative.htmlDeclarative] module, which is used to combine theexpression of Table, mapper(), and user definedclass objects together, is highly recommended as itsimplifies application configuration, ensures the “onemapper per class” pattern, and allows the full range ofconfiguration available to distinct mapper() calls.Separate mapper() and Table usage is now referredto as “classical SQLAlchemy usage” and of course is freelymixable 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 byClass.propname, and the c attribute continues toremain on Table objects where they indicate thenamespace of Column objects present on the table.

To get at the Table for a mapped class (if you didn’t keepit around already):

  1. table = class_mapper(someclass).mapped_table

Iterate through columns:

  1. for col in table.c:
  2.     print(col)

Work with a specific column:

  1. table.c.somecolumn

The class-bound descriptors support the full set of Columnoperators as well as the documented relation-orientedoperators like has(), any(), contains(), etc.

The reason for the hard removal of .c. is that in 0.5,class-bound descriptors carry potentially differentmeaning, as well as information regarding class mappings,versus plain Column objects - and there are use caseswhere you’d specifically want to use one or the other.Generally, using class-bound descriptors invokes a set ofmapping/polymorphic aware translations, and using table-bound columns does not. In 0.4, these translations wereapplied across the board to all expressions, but 0.5differentiates completely between columns and mappeddescriptors, only applying translations to the latter. Soin many cases, particularly when dealing with joined tableinheritance configurations as well as when usingquery(<columns>), Class.propname andtable.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 mapperin use and further mapper-specific operations likequery.join(<propname>), query.withparent() etc.may be used, but in the former case cannot. Additionally,in polymorphic inheritance scenarios, the class-bounddescriptors refer to the columns present in thepolymorphic selectable in use, not necessarily the tablecolumn which directly corresponds to the descriptor. Forexample, a set of classes related by joined-tableinheritance to the person table along theperson_id column of each table will all have theirClass.person_id attribute mapped to the person_idcolumn in person, and not their subclass table.Version 0.4 would map this behavior onto table-boundColumn objects automatically. In 0.5, this automaticconversion has been removed, so that you in fact _can usetable-bound columns as a means to override thetranslations which occur with polymorphic querying; thisallows Query to be able to create optimized selectsamong joined-table or concrete-table inheritance setups,as well as portable subqueries, etc.

  • Session Now Synchronizes Automatically withTransactions. Session now synchronizes against thetransaction automatically by default, including autoflushand autoexpire. A transaction is present at all timesunless disabled using the autocommit option. When allthree flags are set to their default, the Session recoversgracefully after rollbacks and it’s very difficult to getstale data into the session. See the new Sessiondocumentation for details.

  • Implicit Order By Is Removed. This will impact ORMusers who rely upon SA’s “implicit ordering” behavior,which states that all Query objects which don’t have anorder_by() will ORDER BY the “id” or “oid” column ofthe primary mapped table, and all lazy/eagerly loadedcollections apply a similar ordering. In 0.5, automaticordering must be explicitly configured on mapper() andrelation() objects (if desired), or otherwise whenusing Query.

To convert an 0.4 mapping to 0.5, such that its orderingbehavior will be extremely similar to 0.4 or previous, usethe order_by setting on mapper() andrelation():

  1. mapper(User, users, properties={
  2.     'addresses':relation(Address, order_by=addresses.c.id)
  3. }, order_by=users.c.id)

To set ordering on a backref, use the backref()function:

  1. 'keywords':relation(Keyword, secondary=item_keywords,
  2.       order_by=keywords.c.name, backref=backref('items', order_by=items.c.id))

Using declarative ? To help with the new order_byrequirement, order_by and friends can now be set usingstrings which are evaluated in Python later on (this worksonly with declarative, not plain mappers):

  1. class MyClass(MyDeclarativeBase):
  2.     ...
  3.     'addresses':relation("Address", order_by="Address.id")

It’s generally a good idea to set order_by onrelation()s which load list-based collections ofitems, since that ordering cannot otherwise be affected.Other than that, the best practice is to useQuery.order_by() to control ordering of the primaryentities being loaded.

  • Session is nowautoflush=True/autoexpire=True/autocommit=False. - Toset it up, just call sessionmaker() with no arguments.The name transactional=True is nowautocommit=False. Flushes occur upon each queryissued (disable with autoflush=False), within eachcommit() (as always), and before eachbegin_nested() (so rolling back to the SAVEPOINT ismeaningful). All objects are expired after eachcommit() and after each rollback(). Afterrollback, pending objects are expunged, deleted objectsmove back to persistent. These defaults work togethervery nicely and there’s really no more need for oldtechniques like clear() (which is renamed toexpunge_all() as well).

P.S.: sessions are now reusable after a rollback().Scalar and collection attribute changes, adds and deletesare all rolled back.

  • session.add() replaces session.save(), session.update(),session.save_or_update(). - thesession.add(someitem) and session.add_all([list ofitems]) methods replace save(), update(), andsave_or_update(). Those methods will remaindeprecated throughout 0.5.

  • backref configuration made less verbose. - Thebackref() function now uses the primaryjoin andsecondaryjoin arguments of the forwards-facingrelation() when they are not explicitly stated. It’sno longer necessary to specifyprimaryjoin/secondaryjoin in both directionsseparately.

  • Simplified polymorphic options. - The ORM’s“polymorphic load” behavior has been simplified. In 0.4,mapper() had an argument called polymorphic_fetchwhich could be configured as select or deferred.This option is removed; the mapper will now just defer anycolumns which were not present in the SELECT statement.The actual SELECT statement used is controlled by thewith_polymorphic mapper argument (which is also in 0.4and replaces select_table), as well as thewith_polymorphic() method on Query (also in 0.4).

An improvement to the deferred loading of inheritingclasses is that the mapper now produces the “optimized”version of the SELECT statement in all cases; that is, ifclass B inherits from A, and several attributes onlypresent on class B have been expired, the refreshoperation will only include B’s table in the SELECTstatement and will not JOIN to A.

  • The execute() method on Session converts plainstrings into text() constructs, so that bindparameters may all be specified as “:bindname” withoutneeding to call text() explicitly. If “raw” SQL isdesired here, use session.connection().execute("rawtext").

  • session.Query().iterate_instances() has been renamedto just instances(). The old instances() methodreturning a list instead of an iterator no longer exists.If you were relying on that behavior, you should uselist(your_query.instances()).

Extending the ORM

In 0.5 we’re moving forward with more ways to modify andextend the ORM. Heres a summary:

  • MapperExtension. - This is the classic extensionclass, which remains. Methods which should rarely beneeded are create_instance() andpopulate_instance(). To control the initialization ofan object when it’s loaded from the database, use thereconstruct_instance() method, or more easily the@reconstructor decorator described in thedocumentation.

  • SessionExtension. - This is an easy to use extensionclass for session events. In particular, it providesbefore_flush(), after_flush() andafter_flush_postexec() methods. This usage isrecommended over MapperExtension.before_XXX in manycases since within before_flush() you can modify theflush plan of the session freely, something which cannotbe done from within MapperExtension.

  • AttributeExtension. - This class is now part of thepublic API, and allows the interception of userland eventson attributes, including attribute set and deleteoperations, and collection appends and removes. It alsoallows the value to be set or appended to be modified.The @validates decorator, described in thedocumentation, provides a quick way to mark any mappedattributes as being “validated” by a particular classmethod.

  • Attribute Instrumentation Customization. - An API isprovided for ambitious efforts to entirely replaceSQLAlchemy’s attribute instrumentation, or just to augmentit in some cases. This API was produced for the purposesof the Trellis toolkit, but is available as a public API.Some examples are provided in the distribution in the/examples/custom_attributes directory.

Schema/Types

  • String with no length no longer generates TEXT, itgenerates VARCHAR - The String type no longermagically converts into a Text type when specifiedwith no length. This only has an effect when CREATE TABLEis issued, as it will issue VARCHAR with no lengthparameter, which is not valid on many (but not all)databases. To create a TEXT (or CLOB, i.e. unboundedstring) column, use the Text type.

  • PickleType() with mutable=True requires an eq()method - The PickleType type needs to compare valueswhen mutable=True. The method of comparingpickle.dumps() is inefficient and unreliable. If anincoming object does not implement eq() and isalso not None, the dumps() comparison is used buta warning is raised. For types which implementeq() which includes all dictionaries, lists, etc.,comparison will use == and is now reliable by default.

  • convert_bind_param() and convert_result_value() methodsof TypeEngine/TypeDecorator are removed. - The O’Reillybook unfortunately documented these methods even thoughthey were deprecated post 0.3. For a user-defined typewhich subclasses TypeEngine, the bind_processor()and result_processor() methods should be used forbind/result processing. Any user defined type, whetherextending TypeEngine or TypeDecorator, which usesthe old 0.3 style can be easily adapted to the new styleusing the following adapter:

  1. class AdaptOldConvertMethods(object):
  2.     """A mixin which adapts 0.3-style convert_bind_param and
  3.     convert_result_value methods
  4.  
  5.     """
  6.     def bind_processor(self, dialect):
  7.         def convert(value):
  8.             return self.convert_bind_param(value, dialect)
  9.         return convert
  10.  
  11.     def result_processor(self, dialect):
  12.         def convert(value):
  13.             return self.convert_result_value(value, dialect)
  14.         return convert
  15.  
  16.     def convert_result_value(self, value, dialect):
  17.         return value
  18.  
  19.     def convert_bind_param(self, value, dialect):
  20.         return value

To use the above mixin:

  1. class MyType(AdaptOldConvertMethods, TypeEngine):
  2.    # ...

  • The quote flag on Column and Table as well asthe quote_schema flag on Table now control quotingboth positively and negatively. The default is None,meaning let regular quoting rules take effect. WhenTrue, quoting is forced on. When False, quotingis forced off.

  • Column DEFAULT value DDL can now be more convenientlyspecified with Column(…, server_default='val'),deprecating Column(…, PassiveDefault('val')).default= is now exclusively for Python-initiateddefault values, and can coexist with server_default. Anew server_default=FetchedValue() replaces thePassiveDefault('') idiom for marking columns assubject to influence from external triggers and has no DDLside effects.

  • SQLite’s DateTime, Time and Date types nowonly accept datetime objects, not strings as bindparameter input. If you’d like to create your own“hybrid” type which accepts strings and returns results asdate objects (from whatever format you’d like), create aTypeDecorator that builds on String. If you onlywant string-based dates, just use String.

  • Additionally, the DateTime and Time types, whenused with SQLite, now represent the “microseconds” fieldof the Python datetime.datetime object in the samemanner as str(datetime) - as fractional seconds, not acount of microseconds. That is:

  1. dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125)  # 125 usec
  2.  
  3. # old way
  4. '2008-06-27 12:00:00.125'
  5.  
  6. # new way
  7. '2008-06-27 12:00:00.000125'

So if an existing SQLite file-based database intends to beused across 0.4 and 0.5, you either have to upgrade thedatetime columns to store the new format (NOTE: pleasetest this, I’m pretty sure its correct):

  1. UPDATE mytable SET somedatecol =
  2.   substr(somedatecol, 0, 19) || '.' || substr((substr(somedatecol, 21, -1) / 1000000), 3, -1);

or, enable “legacy” mode as follows:

  1. from sqlalchemy.databases.sqlite import DateTimeMixin
  2. DateTimeMixin.__legacy_microseconds__ = True

Connection Pool no longer threadlocal by default

0.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(), oralternatively use the “threadlocal” strategy viastrategy="threadlocal".

args Accepted, args No Longer Accepted

The policy with method(*args) vs. method([args])is, if the method accepts a variable-length set of itemswhich represent a fixed structure, it takes *args. Ifthe method accepts a variable-length set of items that aredata-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 tobe formulated using descriptors, ie.:
  1. query.options(eagerload_all(User.orders, Order.items, Item.keywords))

A single array argument is still accepted for backwardscompatibility.

  • Similarly, the Query.join() and Query.outerjoin()methods accept a variable length *args, with a singlearray accepted for backwards compatibility:
  1. query.join('orders', 'items')
  2. query.join(User.orders, Order.items)
  • the in_() method on columns and similar only accepts alist argument now. It no longer accepts *args.

Removed

  • entity_name - This feature was always problematic andrarely used. 0.5’s more deeply fleshed out use casesrevealed further issues with entity_name which led toits removal. If different mappings are required for asingle class, break the class into separate subclasses andmap them separately. An example of this is at[wiki:UsageRecipes/EntityName]. More informationregarding rationale is described at http://groups.google.com/group/sqlalchemy/browse_thread/thread/9e23a0641a88b96d?hl=en .

  • get()/load() cleanup

The load() method has been removed. Itsfunctionality was kind of arbitrary and basically copiedfrom Hibernate, where it’s also not a particularlymeaningful method.

To get equivalent functionality:

  1. 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 isMapperExtension.load()). To override thefunctionality of Query.get(), use a subclass:

  1. class MyQuery(Query):
  2.     def get(self, ident):
  3.         # ...
  4.  
  5. session = sessionmaker(query_cls=MyQuery)()
  6.  
  7. ad1 = session.query(Address).get(1)
  • sqlalchemy.orm.relation()

The following deprecated keyword arguments have beenremoved:

foreignkey, association, private, attributeext, is_backref

In particular, attributeext is replaced withextension - the AttributeExtension class is now inthe 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() hasbeen removed. To target table aliases in Query toresult columns, use the aliased construct:

  1. from sqlalchemy.orm import aliased
  2. address_alias = aliased(Address)
  3. print(session.query(User, address_alias).join((address_alias, User.addresses)).all())

  • sqlalchemy.orm.Mapper

    • instances()

    • get_session() - this method was not very noticeable, buthad the effect of associating lazy loads with aparticular session even if the parent object wasentirely detached, when an extension such asscoped_session() or the old SessionContextExtwas used. It’s possible that some applications whichrelied upon this behavior will no longer work asexpected; but the better programming practice here isto always ensure objects are present within sessions ifdatabase access from their attributes are required.

  • mapper(MyClass, mytable)

Mapped classes no are longer instrumented with a “c” classattribute; e.g. MyClass.c

  • sqlalchemy.orm.collections

The _prepare_instrumentation alias forprepare_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 beenremoved.

  • 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 nowsimply .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 tosqlalchemy.exc.SADeprecationWarning has been removed.

  • sqlalchemy.exc

exc.AssertionError has been removed and usage replacedby the Python built-in of the same name.

  • sqlalchemy.databases.mysql

The deprecated get_version_info dialect method hasbeen removed.

Renamed or Moved

  • sqlalchemy.exceptions is now sqlalchemy.exc

The module may still be imported under the old name until0.6.

  • FlushError, ConcurrentModificationError,UnmappedColumnError -> sqlalchemy.orm.exc

These exceptions moved to the orm package. Importing‘sqlalchemy.orm’ will install aliases in sqlalchemy.excfor compatibility until 0.6.

  • sqlalchemy.logging -> sqlalchemy.log

This internal module was renamed. No longer needs to bespecial cased when packaging SA with py2app and similartools that scan imports.

  • session.Query().iterate_instances() ->session.Query().instances().

Deprecated

  • Session.save(), Session.update(),Session.save_or_update()

All three replaced by Session.add()

  • sqlalchemy.PassiveDefault

Use Column(server_default=…) Translates tosqlalchemy.DefaultClause() under the hood.

  • session.Query().iterate_instances(). It has beenrenamed to instances().