What’s New in SQLAlchemy 0.9?

About this Document

This document describes changes between SQLAlchemy version 0.8,undergoing maintenance releases as of May, 2013,and SQLAlchemy version 0.9, which had its first productionrelease on December 30, 2013.

Document last updated: June 10, 2015

Introduction

This guide introduces what’s new in SQLAlchemy version 0.9,and also documents changes which affect users migratingtheir applications from the 0.8 series of SQLAlchemy to 0.9.

Please carefully reviewBehavioral Changes - ORM and Behavioral Changes - Core forpotentially backwards-incompatible changes.

Platform Support

Targeting Python 2.6 and Up Now, Python 3 without 2to3

The first achievement of the 0.9 release is to remove the dependencyon the 2to3 tool for Python 3 compatibility. To make thismore straightforward, the lowest Python release targeted nowis 2.6, which features a wide degree of cross-compatibility withPython 3. All SQLAlchemy modules and unit tests are now interpretedequally well with any Python interpreter from 2.6 forward, includingthe 3.1 and 3.2 interpreters.

#2671

C Extensions Supported on Python 3

The C extensions have been ported to support Python 3 and now buildin both Python 2 and Python 3 environments.

#2161

Behavioral Changes - ORM

Composite attributes are now returned as their object form when queried on a per-attribute basis

Using a Query in conjunction with a composite attribute now returns the objecttype maintained by that composite, rather than being broken out into individualcolumns. Using the mapping setup at Composite Column Types:

>>> session.query(Vertex.start, Vertex.end).\
...     filter(Vertex.start == Point(3, 4)).all()
[(Point(x=3, y=4), Point(x=5, y=6))]

This change is backwards-incompatible with code that expects the individual attributeto be expanded into individual columns. To get that behavior, use the .clausesaccessor:

>>> session.query(Vertex.start.clauses, Vertex.end.clauses).\
...     filter(Vertex.start == Point(3, 4)).all()
[(3, 4, 5, 6)]

See also

Column Bundles for ORM queries

#2824

Query.select_from() no longer applies the clause to corresponding entities

The Query.select_from() method has been popularized in recent versionsas a means of controlling the first thing that a Query object“selects from”, typically for the purposes of controlling how a JOIN willrender.

Consider the following example against the usual User mapping:

select_stmt = select([User]).where(User.id == 7).alias()
 
q = session.query(User).\
           join(select_stmt, User.id == select_stmt.c.id).\
           filter(User.name == 'ed')

The above statement predictably renders SQL like the following:

SELECT "user".id AS user_id, "user".name AS user_name
FROM "user" JOIN (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1 ON "user".id = anon_1.id
WHERE "user".name = :name_1

If we wanted to reverse the order of the left and right elements of theJOIN, the documentation would lead us to believe we could useQuery.select_from() to do so:

q = session.query(User).\
        select_from(select_stmt).\
        join(User, User.id == select_stmt.c.id).\
        filter(User.name == 'ed')

However, in version 0.8 and earlier, the above use of Query.select_from()would apply the select_stmt to replace the User entity, as itselects from the user table which is compatible with User:

-- SQLAlchemy 0.8 and earlier...
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1 JOIN "user" ON anon_1.id = anon_1.id
WHERE anon_1.name = :name_1

The above statement is a mess, the ON clause refers anon_1.id = anon_1.id,our WHERE clause has been replaced with anon_1 as well.

This behavior is quite intentional, but has a different use case from thatwhich has become popular for Query.select_from(). The above behavioris now available by a new method known as Query.select_entity_from().This is a lesser used behavior that in modern SQLAlchemy is roughly equivalentto selecting from a customized aliased() construct:

select_stmt = select([User]).where(User.id == 7)
user_from_stmt = aliased(User, select_stmt.alias())
 
q = session.query(user_from_stmt).filter(user_from_stmt.name == 'ed')

So with SQLAlchemy 0.9, our query that selects from select_stmt producesthe SQL we expect:

-- SQLAlchemy 0.9
SELECT "user".id AS user_id, "user".name AS user_name
FROM (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1 JOIN "user" ON "user".id = id
WHERE "user".name = :name_1

The Query.select_entity_from() method will be available in SQLAlchemy0.8.2, so applications which rely on the old behavior can transitionto this method first, ensure all tests continue to function, then upgradeto 0.9 without issue.

#2736

viewonly=True on relationship() prevents history from taking effect

The viewonly flag on relationship() is applied to prevent changesto the target attribute from having any effect within the flush process.This is achieved by eliminating the attribute from being considered duringthe flush. However, up until now, changes to the attribute would stillregister the parent object as “dirty” and trigger a potential flush. The changeis that the viewonly flag now prevents history from being set for thetarget attribute as well. Attribute events like backrefs and user-defined eventsstill continue to function normally.

The change is illustrated as follows:

from sqlalchemy import Column, Integer, ForeignKey, create_engine
from sqlalchemy.orm import backref, relationship, Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import inspect
 
Base = declarative_base()
 
class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
 
class B(Base):
    __tablename__ = 'b'
 
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
    a = relationship("A", backref=backref("bs", viewonly=True))
 
e = create_engine("sqlite://")
Base.metadata.create_all(e)
 
a = A()
b = B()
 
sess = Session(e)
sess.add_all([a, b])
sess.commit()
 
b.a = a
 
assert b in sess.dirty
 
# before 0.9.0
# assert a in sess.dirty
# assert inspect(a).attrs.bs.history.has_changes()
 
# after 0.9.0
assert a not in sess.dirty
assert not inspect(a).attrs.bs.history.has_changes()

#2833

Association Proxy SQL Expression Improvements and Fixes

The == and != operators as implemented by an association proxythat refers to a scalar value on a scalar relationship now producesa more complete SQL expression, intended to take into accountthe “association” row being present or not when the comparison is againstNone.

Consider this mapping:

class A(Base):
    __tablename__ = 'a'
 
    id = Column(Integer, primary_key=True)
 
    b_id = Column(Integer, ForeignKey('b.id'), primary_key=True)
    b = relationship("B")
    b_value = association_proxy("b", "value")
 
class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    value = Column(String)

Up through 0.8, a query like the following:

s.query(A).filter(A.b_value == None).all()

would produce:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NULL)

In 0.9, it now produces:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE (EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NULL)) OR a.b_id IS NULL

The difference being, it not only checks b.value, it also checksif a refers to no b row at all. This will return differentresults versus prior versions, for a system that uses this type ofcomparison where some parent rows have no association row.

More critically, a correct expression is emitted for A.b_value != None.In 0.8, this would return True for A rows that had no b:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE NOT (EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NULL))

Now in 0.9, the check has been reworked so that it ensuresthe A.b_id row is present, in addition to B.value beingnon-NULL:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NOT NULL)

In addition, the has() operator is enhanced such that you cancall it against a scalar column value with no criterion only,and it will produce criteria that checks for the association rowbeing present or not:

s.query(A).filter(A.b_value.has()).all()

output:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id)

This is equivalent to A.b.has(), but allows one to queryagainst b_value directly.

#2751

Association Proxy Missing Scalar returns None

An association proxy from a scalar attribute to a scalar will now returnNone if the proxied object isn’t present. This is consistent with thefact that missing many-to-ones return None in SQLAlchemy, so should theproxied value. E.g.:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
 
Base = declarative_base()
 
class A(Base):
    __tablename__ = 'a'
 
    id = Column(Integer, primary_key=True)
    b = relationship("B", uselist=False)
 
    bname = association_proxy("b", "name")
 
class B(Base):
    __tablename__ = 'b'
 
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
    name = Column(String)
 
a1 = A()
 
# this is how m2o's always have worked
assert a1.b is None
 
# but prior to 0.9, this would raise AttributeError,
# now returns None just like the proxied value.
assert a1.bname is None

#2810

attributes.get_history() will query from the DB by default if value not present

A bugfix regarding attributes.get_history() allows a column-based attributeto query out to the database for an unloaded value, assuming the passiveflag is left at its default of PASSIVE_OFF. Previously, this flag wouldnot be honored. Additionally, a new method AttributeState.load_history()is added to complement the AttributeState.history attribute, whichwill emit loader callables for an unloaded attribute.

This is a small change demonstrated as follows:

from sqlalchemy import Column, Integer, String, create_engine, inspect
from sqlalchemy.orm import Session, attributes
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(String)
 
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
 
sess = Session(e)
 
a1 = A(data='a1')
sess.add(a1)
sess.commit()  # a1 is now expired
 
# history doesn't emit loader callables
assert inspect(a1).attrs.data.history == (None, None, None)
 
# in 0.8, this would fail to load the unloaded state.
assert attributes.get_history(a1, 'data') == ((), ['a1',], ())
 
# load_history() is now equivalent to get_history() with
# passive=PASSIVE_OFF ^ INIT_OK
assert inspect(a1).attrs.data.load_history() == ((), ['a1',], ())

#2787

Behavioral Changes - Core

Type objects no longer accept ignored keyword arguments

Up through the 0.8 series, most type objects accepted arbitrary keywordarguments which were silently ignored:

from sqlalchemy import Date, Integer
 
# storage_format argument here has no effect on any backend;
# it needs to be on the SQLite-specific type
d = Date(storage_format="%(day)02d.%(month)02d.%(year)04d")
 
# display_width argument here has no effect on any backend;
# it needs to be on the MySQL-specific type
i = Integer(display_width=5)

This was a very old bug for which a deprecation warning was added to the0.8 series, but because nobody ever runs Python with the “-W” flag, itwas mostly never seen:

$ python -W always::DeprecationWarning ~/dev/sqlalchemy/test.py
/Users/classic/dev/sqlalchemy/test.py:5: SADeprecationWarning: Passing arguments to
type object constructor <class 'sqlalchemy.types.Date'> is deprecated
  d = Date(storage_format="%(day)02d.%(month)02d.%(year)04d")
/Users/classic/dev/sqlalchemy/test.py:9: SADeprecationWarning: Passing arguments to
type object constructor <class 'sqlalchemy.types.Integer'> is deprecated
  i = Integer(display_width=5)

As of the 0.9 series the “catch all” constructor is removed fromTypeEngine, and these meaningless arguments are no longer accepted.

The correct way to make use of dialect-specific arguments such asstorage_format and display_width is to use the appropriatedialect-specific types:

from sqlalchemy.dialects.sqlite import DATE
from sqlalchemy.dialects.mysql import INTEGER
 
d = DATE(storage_format="%(day)02d.%(month)02d.%(year)04d")
 
i = INTEGER(display_width=5)

What about the case where we want the dialect-agnostic type also? Weuse the TypeEngine.with_variant() method:

from sqlalchemy import Date, Integer
from sqlalchemy.dialects.sqlite import DATE
from sqlalchemy.dialects.mysql import INTEGER
 
d = Date().with_variant(
        DATE(storage_format="%(day)02d.%(month)02d.%(year)04d"),
        "sqlite"
    )
 
i = Integer().with_variant(
        INTEGER(display_width=5),
        "mysql"
    )

TypeEngine.with_variant() isn’t new, it was added in SQLAlchemy0.7.2. So code that is running on the 0.8 series can be corrected to usethis approach and tested before upgrading to 0.9.

None can no longer be used as a “partial AND” constructor

None can no longer be used as the “backstop” to form an AND condition piecemeal.This pattern was not a documented pattern even though some SQLAlchemy internalsmade use of it:

condition = None
 
for cond in conditions:
    condition = condition & cond
 
if condition is not None:
    stmt = stmt.where(condition)

The above sequence, when conditions is non-empty, will on 0.9 produceSELECT .. WHERE <condition> AND NULL. The None is no longer implicitlyignored, and is instead consistent with when None is interpreted in othercontexts besides that of a conjunction.

The correct code for both 0.8 and 0.9 should read:

from sqlalchemy.sql import and_
 
if conditions:
    stmt = stmt.where(and_(*conditions))

Another variant that works on all backends on 0.9, but on 0.8 only works onbackends that support boolean constants:

from sqlalchemy.sql import true
 
condition = true()
 
for cond in conditions:
    condition = cond & condition
 
stmt = stmt.where(condition)

On 0.8, this will produce a SELECT statement that always has AND truein the WHERE clause, which is not accepted by backends that don’t supportboolean constants (MySQL, MSSQL). On 0.9, the true constant will be droppedwithin an and_() conjunction.

See also

Improved rendering of Boolean constants, NULL constants, conjunctions

The “password” portion of a create_engine() no longer considers the + sign as an encoded space

For whatever reason, the Python function unquote_plus() was applied to the“password” field of a URL, which is an incorrect application of theencoding rules described in RFC 1738in that it escaped spaces as plus signs. The stringification of a URLnow only encodes “:”, “@”, or “/” and nothing else, and is now applied to both theusername and password fields (previously it only applied to thepassword). On parsing, encoded characters are converted, but plus signs andspaces are passed through as is:

# password: "pass word + other:words"
dbtype://user:pass word + other%3Awords@host/dbname
 
# password: "apples/oranges"
dbtype://username:apples%2Foranges@hostspec/database
 
# password: "apples@oranges@@"
dbtype://username:apples%40oranges%40%40@hostspec/database
 
# password: '', username is "username@"
dbtype://username%40:@hostspec/database

#2873

The precedence rules for COLLATE have been changed

Previously, an expression like the following:

print((column('x') == 'somevalue').collate("en_EN"))

would produce an expression like this:

-- 0.8 behavior
(x = :x_1) COLLATE en_EN

The above is misunderstood by MSSQL and is generally not the syntax suggestedfor any database. The expression will now produce the syntax illustratedby that of most database documentation:

-- 0.9 behavior
x = :x_1 COLLATE en_EN

The potentially backwards incompatible change arises if the collate()operator is being applied to the right-hand column, as follows:

print(column('x') == literal('somevalue').collate("en_EN"))

In 0.8, this produces:

x = :param_1 COLLATE en_EN

However in 0.9, will now produce the more accurate, but probably not what youwant, form of:

x = (:param_1 COLLATE en_EN)

The ColumnOperators.collate() operator now works more appropriately within anORDER BY expression as well, as a specific precedence has been given to theASC and DESC operators which will again ensure no parentheses aregenerated:

>>> # 0.8
>>> print(column('x').collate('en_EN').desc())
(x COLLATE en_EN) DESC
 
>>> # 0.9
>>> print(column('x').collate('en_EN').desc())
x COLLATE en_EN DESC

#2879

PostgreSQL CREATE TYPE <x> AS ENUM now applies quoting to values

The postgresql.ENUM type will now apply escaping to single quotesigns within the enumerated values:

>>> from sqlalchemy.dialects import postgresql
>>> type = postgresql.ENUM('one', 'two', "three's", name="myenum")
>>> from sqlalchemy.dialects.postgresql import base
>>> print(base.CreateEnumType(type).compile(dialect=postgresql.dialect()))
CREATE TYPE myenum AS ENUM ('one','two','three''s')

Existing workarounds which already escape single quote signs will need to bemodified, else they will now double-escape.

#2878

New Features

Event Removal API

Events established using event.listen() or event.listens_for()can now be removed using the new event.remove() function. The target,identifier and fn arguments sent to event.remove() need to matchexactly those which were sent for listening, and the event will be removedfrom all locations in which it had been established:

@event.listens_for(MyClass, "before_insert", propagate=True)def my_before_insert(mapper, connection, target):    """listen for before_insert"""
# ...
event.remove(MyClass, "before_insert", my_before_insert)

In the example above, the propagate=True flag is set. Thismeans my_before_insert() is established as a listener for MyClassas well as all subclasses of MyClass.The system tracks everywhere that the my_before_insert()listener function had been placed as a result of this call and removes it asa result of calling event.remove().

The removal system uses a registry to associate arguments passed toevent.listen() with collections of event listeners, which are in manycases wrapped versions of the original user-supplied function. This registrymakes heavy use of weak references in order to allow all the contained contents,such as listener targets, to be garbage collected when they go out of scope.

#2268

New Query Options API; load_only() option

The system of loader options such as orm.joinedload(),orm.subqueryload(), orm.lazyload(), orm.defer(), etc.all build upon a new system known as Load. Load providesa “method chained” (a.k.a. generative) approach to loader options, so thatinstead of joining together long paths using dots or multiple attribute names,an explicit loader style is given for each path.

While the new way is slightly more verbose, it is simpler to understandin that there is no ambiguity in what options are being applied to which paths;it simplifies the method signatures of the options and provides greater flexibilityparticularly for column-based options. The old systems are to remain functionalindefinitely as well and all styles can be mixed.

Old Way

To set a certain style of loading along every link in a multi-element path, the _all()option has to be used:

query(User).options(joinedload_all("orders.items.keywords"))

New Way

Loader options are now chainable, so the same joinedload(x) method is appliedequally to each link, without the need to keep straight betweenjoinedload() and joinedload_all():

query(User).options(joinedload("orders").joinedload("items").joinedload("keywords"))

Old Way

Setting an option on path that is based on a subclass requires that alllinks in the path be spelled out as class bound attributes, since thePropComparator.of_type() method needs to be called:

session.query(Company).\
    options(
        subqueryload_all(
            Company.employees.of_type(Engineer),
            Engineer.machines
        )
    )

New Way

Only those elements in the path that actually need PropComparator.of_type()need to be set as a class-bound attribute, string-based names can be resumedafterwards:

session.query(Company).\
    options(
        subqueryload(Company.employees.of_type(Engineer)).
        subqueryload("machines")
        )
    )

Old Way

Setting the loader option on the last link in a long path uses a syntaxthat looks a lot like it should be setting the option for all links in thepath, causing confusion:

query(User).options(subqueryload("orders.items.keywords"))

New Way

A path can now be spelled out using defaultload() for entries in thepath where the existing loader style should be unchanged. More verbosebut the intent is clearer:

query(User).options(defaultload("orders").defaultload("items").subqueryload("keywords"))

The dotted style can still be taken advantage of, particularly in the caseof skipping over several path elements:

query(User).options(defaultload("orders.items").subqueryload("keywords"))

Old Way

The defer() option on a path needed to be spelled out with the fullpath for each column:

query(User).options(defer("orders.description"), defer("orders.isopen"))

New Way

A single Load object that arrives at the target path can haveLoad.defer() called upon it repeatedly:

query(User).options(defaultload("orders").defer("description").defer("isopen"))

The Load Class

The Load class can be used directly to provide a “bound” target,especially when multiple parent entities are present:

from sqlalchemy.orm import Load
 
query(User, Address).options(Load(Address).joinedload("entries"))

Load Only

A new option load_only() achieves a “defer everything but” style of load,loading only the given columns and deferring the rest:

from sqlalchemy.orm import load_only
 
query(User).options(load_only("name", "fullname"))
 
# specify explicit parent entity
query(User, Address).options(Load(User).load_only("name", "fullname"))
 
# specify path
query(User).options(joinedload(User.addresses).load_only("email_address"))

Class-specific Wildcards

Using Load, a wildcard may be used to set the loading for allrelationships (or perhaps columns) on a given entity, without affecting anyothers:

# lazyload all User relationships
query(User).options(Load(User).lazyload("*"))
 
# undefer all User columns
query(User).options(Load(User).undefer("*"))
 
# lazyload all Address relationships
query(User).options(defaultload(User.addresses).lazyload("*"))
 
# undefer all Address columns
query(User).options(defaultload(User.addresses).undefer("*"))

#1418

New text() Capabilities

The text() construct gains new methods:

• TextClause.bindparams() allows bound parameter types and valuesto be set flexibly:

# setup values
stmt = text("SELECT id, name FROM user "
      "WHERE name=:name AND timestamp=:timestamp").\
      bindparams(name="ed", timestamp=datetime(2012, 11, 10, 15, 12, 35))
 
# setup types and/or values
stmt = text("SELECT id, name FROM user "
      "WHERE name=:name AND timestamp=:timestamp").\
      bindparams(
          bindparam("name", value="ed"),
          bindparam("timestamp", type_=DateTime()
      ).bindparam(timestamp=datetime(2012, 11, 10, 15, 12, 35))

• TextClause.columns() supersedes the typemap optionof text(), returning a new construct TextAsFrom:

# turn a text() into an alias(), with a .c. collection:
stmt = text("SELECT id, name FROM user").columns(id=Integer, name=String)
stmt = stmt.alias()
 
stmt = select([addresses]).select_from(
              addresses.join(stmt), addresses.c.user_id == stmt.c.id)
 
 
# or into a cte():
stmt = text("SELECT id, name FROM user").columns(id=Integer, name=String)
stmt = stmt.cte("x")
 
stmt = select([addresses]).select_from(
              addresses.join(stmt), addresses.c.user_id == stmt.c.id)

#2877

INSERT from SELECT

After literally years of pointless procrastination this relatively minorsyntactical feature has been added, and is also backported to 0.8.3,so technically isn’t “new” in 0.9. A select() construct or othercompatible construct can be passed to the new method Insert.from_select()where it will be used to render an INSERT .. SELECT construct:

>>> from sqlalchemy.sql import table, column
>>> t1 = table('t1', column('a'), column('b'))
>>> t2 = table('t2', column('x'), column('y'))
>>> print(t1.insert().from_select(['a', 'b'], t2.select().where(t2.c.y == 5)))
INSERT INTO t1 (a, b) SELECT t2.x, t2.y
FROM t2
WHERE t2.y = :y_1

The construct is smart enough to also accommodate ORM objects such as classesand Query objects:

s = Session()
q = s.query(User.id, User.name).filter_by(name='ed')
ins = insert(Address).from_select((Address.id, Address.email_address), q)

rendering:

INSERT INTO addresses (id, email_address)
SELECT users.id AS users_id, users.name AS users_name
FROM users WHERE users.name = :name_1

#722

New FOR UPDATE support on select(), Query()

An attempt is made to simplify the specification of the FOR UPDATEclause on SELECT statements made within Core and ORM, and support is addedfor the FOR UPDATE OF SQL supported by PostgreSQL and Oracle.

Using the core GenerativeSelect.with_for_update(), options like FOR SHARE andNOWAIT can be specified individually, rather than linking to arbitrarystring codes:

stmt = select([table]).with_for_update(read=True, nowait=True, of=table)

On Posgtresql the above statement might render like:

SELECT table.a, table.b FROM table FOR SHARE OF table NOWAIT

The Query object gains a similar method Query.with_for_update()which behaves in the same way. This method supersedes the existingQuery.with_lockmode() method, which translated FOR UPDATE clausesusing a different system. At the moment, the “lockmode” string argument is stillaccepted by the Session.refresh() method.

Floating Point String-Conversion Precision Configurable for Native Floating Point Types

The conversion which SQLAlchemy does whenever a DBAPI returns a Pythonfloating point type which is to be converted into a Python Decimal()necessarily involves an intermediary step which converts the floating pointvalue to a string. The scale used for this string conversion was previouslyhardcoded to 10, and is now configurable. The setting is available onboth the Numeric as well as the Floattype, as well as all SQL- and dialect-specific descendant types, using theparameter decimal_return_scale. If the type supports a .scale parameter,as is the case with Numeric and some float types such asmysql.DOUBLE, the value of .scale is used as the defaultfor .decimal_return_scale if it is not otherwise specified. If both.scale and .decimal_return_scale are absent, then the default of10 takes place. E.g.:

from sqlalchemy.dialects.mysql import DOUBLE
import decimal
 
data = Table('data', metadata,
    Column('double_value',
                mysql.DOUBLE(decimal_return_scale=12, asdecimal=True))
)
 
conn.execute(
    data.insert(),
    double_value=45.768392065789,
)
result = conn.scalar(select([data.c.double_value]))
 
# previously, this would typically be Decimal("45.7683920658"),
# e.g. trimmed to 10 decimal places
 
# now we get 12, as requested, as MySQL can support this
# much precision for DOUBLE
assert result == decimal.Decimal("45.768392065789")

#2867

Column Bundles for ORM queries

The Bundle allows for querying of sets of columns, which are thengrouped into one name under the tuple returned by the query. The initialpurposes of Bundle are 1. to allow “composite” ORM columns to bereturned as a single value in a column-based result set, rather than expandingthem out into individual columns and 2. to allow the creation of custom result-setconstructs within the ORM, using ad-hoc columns and return types, without involvingthe more heavyweight mechanics of mapped classes.

See also

Composite attributes are now returned as their object form when queried on a per-attribute basis

Column Bundles

#2824

Server Side Version Counting

The versioning feature of the ORM (now also documented at Configuring a Version Counter)can now make use of server-side version counting schemes, such as those producedby triggers or database system columns, as well as conditional programmatic schemes outsideof the version_id_counter function itself. By providing the value Falseto the version_id_generator parameter, the ORM will use the already-set versionidentifier, or alternatively fetch the version identifierfrom each row at the same time the INSERT or UPDATE is emitted. When using aserver-generated version identifier, it is stronglyrecommended that this feature be used only on a backend with strong RETURNINGsupport (PostgreSQL, SQL Server; Oracle also supports RETURNING but the cx_oracledriver has only limited support), else the additional SELECT statements willadd significant performanceoverhead. The example provided at Server Side Version Counters illustratesthe usage of the PostgreSQL xmin system column in order to integrate it withthe ORM’s versioning feature.

See also

Server Side Version Counters

#2793

include_backrefs=False option for @validates

The validates() function now accepts an option include_backrefs=True,which will bypass firing the validator for the case where the event initiatedfrom a backref:

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, validates
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
class A(Base):
    __tablename__ = 'a'
 
    id = Column(Integer, primary_key=True)
    bs = relationship("B", backref="a")
 
    @validates("bs")
    def validate_bs(self, key, item):
        print("A.bs validator")
        return item
 
class B(Base):
    __tablename__ = 'b'
 
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
 
    @validates("a", include_backrefs=False)
    def validate_a(self, key, item):
        print("B.a validator")
        return item
 
a1 = A()
a1.bs.append(B())  # prints only "A.bs validator"

#1535

PostgreSQL JSON Type

The PostgreSQL dialect now features a postgresql.JSON type tocomplement the postgresql.HSTORE type.

See also

postgresql.JSON

#2581

Automap Extension

A new extension is added in 0.9.1 known as sqlalchemy.ext.automap. This is anexperimental extension which expands upon the functionality of Declarativeas well as the DeferredReflection class. Essentially, the extensionprovides a base class AutomapBase which automatically generatesmapped classes and relationships between them based on given table metadata.

The MetaData in use normally might be produced via reflection, butthere is no requirement that reflection is used. The most basic usageillustrates how sqlalchemy.ext.automap is able to deliver mappedclasses, including relationships, based on a reflected schema:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
 
Base = automap_base()
 
# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///mydatabase.db")
 
# reflect the tables
Base.prepare(engine, reflect=True)
 
# mapped classes are now created with names matching that of the table
# name.
User = Base.classes.user
Address = Base.classes.address
 
session = Session(engine)
 
# rudimentary relationships are produced
session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
session.commit()
 
# collection-based relationships are by default named "<classname>_collection"
print(u1.address_collection)

Beyond that, the AutomapBase class is a declarative base, and supportsall the features that declarative does. The “automapping” feature can be usedwith an existing, explicitly declared schema to generate relationships andmissing classes only. Naming schemes and relationship-production routinescan be dropped in using callable functions.

It is hoped that the AutomapBase system provides a quickand modernized solution to the problem that the very famousSQLSoupalso tries to solve, that of generating a quick and rudimentary objectmodel from an existing database on the fly. By addressing the issue strictlyat the mapper configuration level, and integrating fully with existingDeclarative class techniques, AutomapBase seeks to providea well-integrated approach to the issue of expediently auto-generating ad-hocmappings.

See also

Automap

Behavioral Improvements

Improvements that should produce no compatibility issues except in exceedinglyrare and unusual hypothetical cases, but are good to be aware of in case there areunexpected issues.

Many JOIN and LEFT OUTER JOIN expressions will no longer be wrapped in (SELECT * FROM ..) AS ANON_1

For many years, the SQLAlchemy ORM has been held back from being able to nesta JOIN inside the right side of an existing JOIN (typically a LEFT OUTER JOIN,as INNER JOINs could always be flattened):

SELECT a.*, b.*, c.* FROM a LEFT OUTER JOIN (b JOIN c ON b.id = c.id) ON a.id

This was due to the fact that SQLite up until version 3.7.16 cannot parse a statement of the above format:

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(id integer);
sqlite> create table b(id integer);
sqlite> create table c(id integer);
sqlite> select a.id, b.id, c.id from a left outer join (b join c on b.id=c.id) on b.id=a.id;
Error: no such column: b.id

Right-outer-joins are of course another way to work around right-sideparenthesization; this would be significantly complicated and visually unpleasantto implement, but fortunately SQLite doesn’t support RIGHT OUTER JOIN either :):

sqlite> select a.id, b.id, c.id from b join c on b.id=c.id
   ...> right outer join a on b.id=a.id;
Error: RIGHT and FULL OUTER JOINs are not currently supported

Back in 2005, it wasn’t clear if other databases had trouble with this form,but today it seems clear every database tested except SQLite now supports it(Oracle 8, a very old database, doesn’t support the JOIN keyword at all,but SQLAlchemy has always had a simple rewriting scheme in place for Oracle’s syntax).To make matters worse, SQLAlchemy’s usual workaround of applying aSELECT often degrades performance on platforms like PostgreSQL and MySQL:

SELECT a.*, anon_1.* FROM a LEFT OUTER JOIN (
                SELECT b.id AS b_id, c.id AS c_id
                FROM b JOIN c ON b.id = c.id
            ) AS anon_1 ON a.id=anon_1.b_id

A JOIN like the above form is commonplace when working with joined-table inheritance structures;any time Query.join() is used to join from some parent to a joined-table subclass, orwhen joinedload() is used similarly, SQLAlchemy’s ORM would always make sure a nestedJOIN was never rendered, lest the query wouldn’t be able to run on SQLite. Even thoughthe Core has always supported a JOIN of the more compact form, the ORM had to avoid it.

An additional issue would arise when producing joins across many-to-many relationshipswhere special criteria is present in the ON clause. Consider an eager load join like the following:

session.query(Order).outerjoin(Order.items)

Assuming a many-to-many from Order to Item which actually refers to a subclasslike Subitem, the SQL for the above would look like:

SELECT order.id, order.name
FROM order LEFT OUTER JOIN order_item ON order.id = order_item.order_id
LEFT OUTER JOIN item ON order_item.item_id = item.id AND item.type = 'subitem'

What’s wrong with the above query? Basically, that it will load many order /order_item rows where the criteria of item.type == 'subitem' is not true.

As of SQLAlchemy 0.9, an entirely new approach has been taken. The ORM no longerworries about nesting JOINs in the right side of an enclosing JOIN, and it now willrender these as often as possible while still returning the correct results. Whenthe SQL statement is passed to be compiled, the dialect compiler will rewrite the jointo suit the target backend, if that backend is known to not support a right-nestedJOIN (which currently is only SQLite - if other backends have this issue pleaselet us know!).

So a regular query(Parent).join(Subclass) will now usually produce a simplerexpression:

SELECT parent.id AS parent_id
FROM parent JOIN (
        base_table JOIN subclass_table
        ON base_table.id = subclass_table.id) ON parent.id = base_table.parent_id

Joined eager loads like query(Parent).options(joinedload(Parent.subclasses))will alias the individual tables instead of wrapping in an ANON_1:

SELECT parent.*, base_table_1.*, subclass_table_1.* FROM parent
    LEFT OUTER JOIN (
        base_table AS base_table_1 JOIN subclass_table AS subclass_table_1
        ON base_table_1.id = subclass_table_1.id)
        ON parent.id = base_table_1.parent_id

Many-to-many joins and eagerloads will right nest the “secondary” and “right” tables:

SELECT order.id, order.name
FROM order LEFT OUTER JOIN
(order_item JOIN item ON order_item.item_id = item.id AND item.type = 'subitem')
ON order_item.order_id = order.id

All of these joins, when rendered with a Select statement that specificallyspecifies use_labels=True, which is true for all the queries the ORM emits,are candidates for “join rewriting”, which is the process of rewriting all those right-nestedjoins into nested SELECT statements, while maintaining the identical labeling used bythe Select. So SQLite, the one database that won’t support this verycommon SQL syntax even in 2013, shoulders the extra complexity itself,with the above queries rewritten as:

-- sqlite only!
SELECT parent.id AS parent_id
    FROM parent JOIN (
        SELECT base_table.id AS base_table_id,
                base_table.parent_id AS base_table_parent_id,
                subclass_table.id AS subclass_table_id
        FROM base_table JOIN subclass_table ON base_table.id = subclass_table.id
    ) AS anon_1 ON parent.id = anon_1.base_table_parent_id
 
-- sqlite only!
SELECT parent.id AS parent_id, anon_1.subclass_table_1_id AS subclass_table_1_id,
        anon_1.base_table_1_id AS base_table_1_id,
        anon_1.base_table_1_parent_id AS base_table_1_parent_id
FROM parent LEFT OUTER JOIN (
    SELECT base_table_1.id AS base_table_1_id,
        base_table_1.parent_id AS base_table_1_parent_id,
        subclass_table_1.id AS subclass_table_1_id
    FROM base_table AS base_table_1
    JOIN subclass_table AS subclass_table_1 ON base_table_1.id = subclass_table_1.id
) AS anon_1 ON parent.id = anon_1.base_table_1_parent_id
 
-- sqlite only!
SELECT "order".id AS order_id
FROM "order" LEFT OUTER JOIN (
        SELECT order_item_1.order_id AS order_item_1_order_id,
            order_item_1.item_id AS order_item_1_item_id,
            item.id AS item_id, item.type AS item_type
FROM order_item AS order_item_1
    JOIN item ON item.id = order_item_1.item_id AND item.type IN (?)
) AS anon_1 ON "order".id = anon_1.order_item_1_order_id

Note

As of SQLAlchemy 1.1, the workarounds present in this feature for SQLitewill automatically disable themselves when SQLite version 3.7.16or greater is detected, as SQLite has repaired support for right-nested joins.

The Join.alias(), aliased() and with_polymorphic() functions nowsupport a new argument, flat=True, which is used to construct aliases of joined-tableentities without embedding into a SELECT. This flag is not on by default, to help withbackwards compatibility - but now a “polymorphic” selectable can be joined as a targetwithout any subqueries generated:

employee_alias = with_polymorphic(Person, [Engineer, Manager], flat=True)
 
session.query(Company).join(
                    Company.employees.of_type(employee_alias)
                ).filter(
                    or_(
                        Engineer.primary_language == 'python',
                        Manager.manager_name == 'dilbert'
                    )
                )

Generates (everywhere except SQLite):

SELECT companies.company_id AS companies_company_id, companies.name AS companies_name
FROM companies JOIN (
    people AS people_1
    LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id
    LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id
) ON companies.company_id = people_1.company_id
WHERE engineers.primary_language = %(primary_language_1)s
    OR managers.manager_name = %(manager_name_1)s

#2369#2587

Right-nested inner joins available in joined eager loads

As of version 0.9.4, the above mentioned right-nested joining can be enabledin the case of a joined eager load where an “outer” join is linked to an “inner”on the right side.

Normally, a joined eager load chain like the following:

query(User).options(joinedload("orders", innerjoin=False).joinedload("items", innerjoin=True))

Would not produce an inner join; because of the LEFT OUTER JOIN from user->order,joined eager loading could not use an INNER join from order->items without changingthe user rows that are returned, and would instead ignore the “chained” innerjoin=Truedirective. How 0.9.0 should have delivered this would be that instead of:

FROM users LEFT OUTER JOIN orders ON <onclause> LEFT OUTER JOIN items ON <onclause>

the new “right-nested joins are OK” logic would kick in, and we’d get:

FROM users LEFT OUTER JOIN (orders JOIN items ON <onclause>) ON <onclause>

Since we missed the boat on that, to avoid further regressions we’ve added the abovefunctionality by specifying the string "nested" to joinedload.innerjoin:

query(User).options(joinedload("orders", innerjoin=False).joinedload("items", innerjoin="nested"))

This feature is new in 0.9.4.

#2976

ORM can efficiently fetch just-generated INSERT/UPDATE defaults using RETURNING

The Mapper has long supported an undocumented flag known aseager_defaults=True. The effect of this flag is that when an INSERT or UPDATEproceeds, and the row is known to have server-generated default values,a SELECT would immediately follow it in order to “eagerly” load those new values.Normally, the server-generated columns are marked as “expired” on the object,so that no overhead is incurred unless the application actually accesses thesecolumns soon after the flush. The eager_defaults flag was therefore notof much use as it could only decrease performance, and was present only to supportexotic event schemes where users needed default values to be availableimmediately within the flush process.

In 0.9, as a result of the version id enhancements, eager_defaults can nowemit a RETURNING clause for these values, so on a backend with strong RETURNINGsupport in particular PostgreSQL, the ORM can fetch newly generated defaultand SQL expression values inline with the INSERT or UPDATE. eager_defaults,when enabled, makes use of RETURNING automatically when the target backendand Table supports “implicit returning”.

Subquery Eager Loading will apply DISTINCT to the innermost SELECT for some queries

In an effort to reduce the number of duplicate rows that can be generatedby subquery eager loading when a many-to-one relationship is involved, aDISTINCT keyword will be applied to the innermost SELECT when the join istargeting columns that do not comprise the primary key, as in when loadingalong a many to one.

That is, when subquery loading on a many-to-one from A->B:

SELECT b.id AS b_id, b.name AS b_name, anon_1.b_id AS a_b_id
FROM (SELECT DISTINCT a_b_id FROM a) AS anon_1
JOIN b ON b.id = anon_1.a_b_id

Since a.b_id is a non-distinct foreign key, DISTINCT is applied so thatredundant a.b_id are eliminated. The behavior can be turned on or offunconditionally for a particular relationship() using the flagdistinct_target_key, setting the value to True for unconditionallyon, False for unconditionally off, and None for the feature to takeeffect when the target SELECT is against columns that do not comprise a fullprimary key. In 0.9, None is the default.

The option is also backported to 0.8 where the distinct_target_keyoption defaults to False.

While the feature here is designed to help performance by eliminatingduplicate rows, the DISTINCT keyword in SQL itself can have a negativeperformance impact. If columns in the SELECT are not indexed, DISTINCTwill likely perform an ORDER BY on the rowset which can be expensive.By keeping the feature limited just to foreign keys which are hopefullyindexed in any case, it’s expected that the new defaults are reasonable.

The feature also does not eliminate every possible dupe-row scenario; ifa many-to-one is present elsewhere in the chain of joins, dupe rows may stillbe present.

#2836

Backref handlers can now propagate more than one level deep

The mechanism by which attribute events pass along their “initiator”, that isthe object associated with the start of the event, has been changed; insteadof a AttributeImpl being passed, a new object attributes.Eventis passed instead; this object refers to the AttributeImpl as well asto an “operation token”, representing if the operation is an append, remove,or replace operation.

The attribute event system no longer looks at this “initiator” object in order to halt arecursive series of attribute events. Instead, the system of preventing endlessrecursion due to mutually-dependent backref handlers has been movedto the ORM backref event handlers specifically, which now take over the roleof ensuring that a chain of mutually-dependent events (such as append to collectionA.bs, set many-to-one attribute B.a in response) doesn’t go into an endless recursionstream. The rationale here is that the backref system, given more detail and controlover event propagation, can finally allow operations more than one level deepto occur; the typical scenario is when a collection append results in a many-to-onereplacement operation, which in turn should cause the item to be removed from aprevious collection:

class Parent(Base):
    __tablename__ = 'parent'
 
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent")
 
class Child(Base):
    __tablename__ = 'child'
 
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey('parent.id'))
 
p1 = Parent()
p2 = Parent()
c1 = Child()
 
p1.children.append(c1)
 
assert c1.parent is p1  # backref event establishes c1.parent as p1
 
p2.children.append(c1)
 
assert c1.parent is p2  # backref event establishes c1.parent as p2
assert c1 not in p1.children  # second backref event removes c1 from p1.children

Above, prior to this change, the c1 object would still have been presentin p1.children, even though it is also present in p2.children at thesame time; the backref handlers would have stopped at replacing c1.parent withp2 instead of p1. In 0.9, using the more detailed Eventobject as well as letting the backref handlers make more detailed decisions aboutthese objects, the propagation can continue onto removing c1 from p1.childrenwhile maintaining a check against the propagation from going into an endlessrecursive loop.

End-user code which a. makes use of the AttributeEvents.set(),AttributeEvents.append(), or AttributeEvents.remove() events,and b. initiates further attribute modification operations as a result of theseevents may need to be modified to prevent recursive loops, as the attribute systemno longer stops a chain of events from propagating endlessly in the absence of the backrefevent handlers. Additionally, code which depends upon the value of the initiatorwill need to be adjusted to the new API, and furthermore must be ready for thevalue of initiator to change from its original value within a string ofbackref-initiated events, as the backref handlers may now swap in anew initiator value for some operations.

#2789

The typing system now handles the task of rendering “literal bind” values

A new method is added to TypeEngineTypeEngine.literal_processor()as well as TypeDecorator.process_literal_param() for TypeDecoratorwhich take on the task of rendering so-called “inline literal parameters” - parametersthat normally render as “bound” values, but are instead being rendered inlineinto the SQL statement due to the compiler configuration. This feature is usedwhen generating DDL for constructs such as CheckConstraint, as wellas by Alembic when using constructs such as op.inline_literal(). Previously,a simple “isinstance” check checked for a few basic types, and the “bind processor”was used unconditionally, leading to such issues as strings being encoded into utf-8prematurely.

Custom types written with TypeDecorator should continue to work in“inline literal” scenarios, as the TypeDecorator.process_literal_param()falls back to TypeDecorator.process_bind_param() by default, as these methodsusually handle a data manipulation, not as much how the data is presented to thedatabase. TypeDecorator.process_literal_param() can be specified tospecifically produce a string representing how a value should be renderedinto an inline DDL statement.

#2838

Schema identifiers now carry along their own quoting information

This change simplifies the Core’s usage of so-called “quote” flags, suchas the quote flag passed to Table and Column. The flagis now internalized within the string name itself, which is now representedas an instance of quoted_name, a string subclass. TheIdentifierPreparer now relies solely on the quoting preferencesreported by the quoted_name object rather than checking for anyexplicit quote flags in most cases. The issue resolved here includesthat various case-sensitive methods such as Engine.has_table() as wellas similar methods within dialects now function with explicitly quoted names,without the need to complicate or introduce backwards-incompatible changesto those APIs (many of which are 3rd party) with the details of quoting flags -in particular, a wider range of identifiers now function correctly with theso-called “uppercase” backends like Oracle, Firebird, and DB2 (backends thatstore and report upon table and column names using all uppercase for caseinsensitive names).

The quoted_name object is used internally as needed; however ifother keywords require fixed quoting preferences, the class is availablepublicly.

#2812

Improved rendering of Boolean constants, NULL constants, conjunctions

New capabilities have been added to the true() and false()constants, in particular in conjunction with and_() and or_()functions as well as the behavior of the WHERE/HAVING clauses in conjunctionwith these types, boolean types overall, and the null() constant.

Starting with a table such as this:

from sqlalchemy import Table, Boolean, Integer, Column, MetaData
 
t1 = Table('t', MetaData(), Column('x', Boolean()), Column('y', Integer))

A select construct will now render the boolean column as a binary expressionon backends that don’t feature true/false constant behavior:

>>> from sqlalchemy import select, and_, false, true
>>> from sqlalchemy.dialects import mysql, postgresql
 
>>> print(select([t1]).where(t1.c.x).compile(dialect=mysql.dialect()))
SELECT t.x, t.y  FROM t WHERE t.x = 1

The and_() and or_() constructs will now exhibit quasi“short circuit” behavior, that is truncating a rendered expression, when atrue() or false() constant is present:

>>> print(select([t1]).where(and_(t1.c.y > 5, false())).compile(
...     dialect=postgresql.dialect()))
SELECT t.x, t.y FROM t WHERE false

true() can be used as the base to build up an expression:

>>> expr = true()
>>> expr = expr & (t1.c.y > 5)
>>> print(select([t1]).where(expr))
SELECT t.x, t.y FROM t WHERE t.y > :y_1

The boolean constants true() and false() themselves render as0 = 1 and 1 = 1 for a backend with no boolean constants:

>>> print(select([t1]).where(and_(t1.c.y > 5, false())).compile(
...     dialect=mysql.dialect()))
SELECT t.x, t.y FROM t WHERE 0 = 1

Interpretation of None, while not particularly valid SQL, is at leastnow consistent:

>>> print(select([t1.c.x]).where(None))
SELECT t.x FROM t WHERE NULL
 
>>> print(select([t1.c.x]).where(None).where(None))
SELECT t.x FROM t WHERE NULL AND NULL
 
>>> print(select([t1.c.x]).where(and_(None, None)))
SELECT t.x FROM t WHERE NULL AND NULL

#2804

Label constructs can now render as their name alone in an ORDER BY

For the case where a Label is used in both the columns clauseas well as the ORDER BY clause of a SELECT, the label will render asjust its name in the ORDER BY clause, assuming the underlying dialectreports support of this feature.

E.g. an example like:

from sqlalchemy.sql import table, column, select, func
 
t = table('t', column('c1'), column('c2'))
expr = (func.foo(t.c.c1) + t.c.c2).label("expr")
 
stmt = select([expr]).order_by(expr)
 
print(stmt)

Prior to 0.9 would render as:

SELECT foo(t.c1) + t.c2 AS expr
FROM t ORDER BY foo(t.c1) + t.c2

And now renders as:

SELECT foo(t.c1) + t.c2 AS expr
FROM t ORDER BY expr

The ORDER BY only renders the label if the label isn’t furtherembedded into an expression within the ORDER BY, other than a simpleASC or DESC.

The above format works on all databases tested, but might havecompatibility issues with older database versions (MySQL 4? Oracle 8?etc.). Based on user reports we can add rules that will disable thefeature based on database version detection.

#1068

RowProxy now has tuple-sorting behavior

The RowProxy object acts much like a tuple, but up until nowwould not sort as a tuple if a list of them were sorted using sorted().The eq() method now compares both sides as a tuple and alsoan lt() method has been added:

users.insert().execute(
        dict(user_id=1, user_name='foo'),
        dict(user_id=2, user_name='bar'),
        dict(user_id=3, user_name='def'),
    )
 
rows = users.select().order_by(users.c.user_name).execute().fetchall()
 
eq_(rows, [(2, 'bar'), (3, 'def'), (1, 'foo')])
 
eq_(sorted(rows), [(1, 'foo'), (2, 'bar'), (3, 'def')])

#2848

A bindparam() construct with no type gets upgraded via copy when a type is available

The logic which “upgrades” a bindparam() construct to take on thetype of the enclosing expression has been improved in two ways. First, thebindparam() object is copied before the new type is assigned, so thatthe given bindparam() is not mutated in place. Secondly, this sameoperation occurs when an Insert or Update construct is compiled,regarding the “values” that were set in the statement via the ValuesBase.values()method.

If given an untyped bindparam():

bp = bindparam("some_col")

If we use this parameter as follows:

expr = mytable.c.col == bp

The type for bp remains as NullType, however if mytable.c.colis of type String, then expr.right, that is the right side of thebinary expression, will take on the String type. Previously, bp itselfwould have been changed in place to have String as its type.

Similarly, this operation occurs in an Insert or Update:

stmt = mytable.update().values(col=bp)

Above, bp remains unchanged, but the String type will be used whenthe statement is executed, which we can see by examining the binds dictionary:

>>> compiled = stmt.compile()
>>> compiled.binds['some_col'].type
String

The feature allows custom types to take their expected effect within INSERT/UPDATEstatements without needing to explicitly specify those types within everybindparam() expression.

The potentially backwards-compatible changes involve two unlikelyscenarios. Since the bound parameter iscloned, users should not be relying upon making in-place changes to abindparam() construct once created. Additionally, code which usesbindparam() within an Insert or Update statementwhich is relying on the fact that the bindparam() is not typed accordingto the column being assigned towards will no longer function in that way.

#2850

Columns can reliably get their type from a column referred to via ForeignKey

There’s a long standing behavior which says that a Column can bedeclared without a type, as long as that Column is referred toby a ForeignKeyConstraint, and the type from the referenced columnwill be copied into this one. The problem has been that this feature neverworked very well and wasn’t maintained. The core issue was that theForeignKey object doesn’t know what target Column itrefers to until it is asked, typically the first time the foreign key is usedto construct a Join. So until that time, the parent Columnwould not have a type, or more specifically, it would have a default typeof NullType.

While it’s taken a long time, the work to reorganize the initialization ofForeignKey objects has been completed such that this feature canfinally work acceptably. At the core of the change is that the ForeignKey.columnattribute no longer lazily initializes the location of the target Column;the issue with this system was that the owning Column would be stuckwith NullType as its type until the ForeignKey happened tobe used.

In the new version, the ForeignKey coordinates with the eventualColumn it will refer to using internal attachment events, so that themoment the referencing Column is associated with theMetaData, all ForeignKey objects thatrefer to it will be sent a message that they need to initialize their parentcolumn. This system is more complicated but works more solidly; as a bonus,there are now tests in place for a wide variety of Column /ForeignKey configuration scenarios and error messages have beenimproved to be very specific to no less than seven different error conditions.

Scenarios which now work correctly include:

• The type on a Column is immediately present as soon as thetarget Column becomes associated with the same MetaData;this works no matter which side is configured first:

>>> from sqlalchemy import Table, MetaData, Column, Integer, ForeignKey
>>> metadata = MetaData()
>>> t2 = Table('t2', metadata, Column('t1id', ForeignKey('t1.id')))
>>> t2.c.t1id.type
NullType()
>>> t1 = Table('t1', metadata, Column('id', Integer, primary_key=True))
>>> t2.c.t1id.type
Integer()

• The system now works with ForeignKeyConstraint as well:

>>> from sqlalchemy import Table, MetaData, Column, Integer, ForeignKeyConstraint
>>> metadata = MetaData()
>>> t2 = Table('t2', metadata,
...     Column('t1a'), Column('t1b'),
...     ForeignKeyConstraint(['t1a', 't1b'], ['t1.a', 't1.b']))
>>> t2.c.t1a.type
NullType()
>>> t2.c.t1b.type
NullType()
>>> t1 = Table('t1', metadata,
...     Column('a', Integer, primary_key=True),
...     Column('b', Integer, primary_key=True))
>>> t2.c.t1a.type
Integer()
>>> t2.c.t1b.type
Integer()

• It even works for “multiple hops” - that is, a ForeignKey that refers to aColumn that refers to another Column:

>>> from sqlalchemy import Table, MetaData, Column, Integer, ForeignKey
>>> metadata = MetaData()
>>> t2 = Table('t2', metadata, Column('t1id', ForeignKey('t1.id')))
>>> t3 = Table('t3', metadata, Column('t2t1id', ForeignKey('t2.t1id')))
>>> t2.c.t1id.type
NullType()
>>> t3.c.t2t1id.type
NullType()
>>> t1 = Table('t1', metadata, Column('id', Integer, primary_key=True))
>>> t2.c.t1id.type
Integer()
>>> t3.c.t2t1id.type
Integer()

#1765

Dialect Changes

Firebird fdb is now the default Firebird dialect.

The fdb dialect is now used if an engine is created without a dialectspecifier, i.e. firebird://. fdb is a kinterbasdb compatibleDBAPI which per the Firebird project is now their official Python driver.

#2504

Firebird fdb and kinterbasdb set retaining=False by default

Both the fdb and kinterbasdb DBAPIs support a flag retaining=Truewhich can be passed to the commit() and rollback() methods of itsconnection. The documented rationale for this flag is so that the DBAPIcan re-use internal transaction state for subsequent transactions, for thepurposes of improving performance. However, newer documentation refersto analyses of Firebird’s “garbage collection” which expresses that this flagcan have a negative effect on the database’s ability to process cleanuptasks, and has been reported as lowering performance as a result.

It’s not clear how this flag is actually usable given this information,and as it appears to be only a performance enhancing feature, it now defaultsto False. The value can be controlled by passing the flag retaining=Trueto the create_engine() call. This is a new flag which is added as of0.8.2, so applications on 0.8.2 can begin setting this to True or Falseas desired.

See also

sqlalchemy.dialects.firebird.fdb

sqlalchemy.dialects.firebird.kinterbasdb

http://pythonhosted.org/fdb/usage-guide.html#retaining-transactions - informationon the “retaining” flag.

#2763