What’s New in SQLAlchemy 1.0?

About this Document

This document describes changes between SQLAlchemy version 0.9,undergoing maintenance releases as of May, 2014,and SQLAlchemy version 1.0, released in April, 2015.

Document last updated: June 9, 2015

Introduction

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

Please carefully review the sections on behavioral changes forpotentially backwards-incompatible changes in behavior.

New Features and Improvements - ORM

New Session Bulk INSERT/UPDATE API

A new series of Session methods which provide hooks directlyinto the unit of work’s facility for emitting INSERT and UPDATEstatements has been created. When used correctly, this expert-oriented systemcan allow ORM-mappings to be used to generate bulk insert and updatestatements batched into executemany groups, allowing the statementsto proceed at speeds that rival direct use of the Core.

See also

Bulk Operations - introduction and full documentation

#3100

New Performance Example Suite

Inspired by the benchmarking done for the Bulk Operations featureas well as for the How can I profile a SQLAlchemy powered application? section of the FAQ, a newexample section has been added which features several scripts designedto illustrate the relative performance profile of various Core and ORMtechniques. The scripts are organized into use cases, and are packagedunder a single console interface such that any combination of demonstrationscan be run, dumping out timings, Python profile results and/or RunSnake profiledisplays.

See also

Performance

“Baked” Queries

The “baked” query feature is an unusual new approach which allows forstraightforward construction an invocation of Query objectsusing caching, which upon successive calls features vastly reducedPython function call overhead (over 75%). By specifying aQuery object as a series of lambdas which are only invokedonce, a query as a pre-compiled unit begins to be feasible:

  1. from sqlalchemy.ext import baked
  2. from sqlalchemy import bindparam
  3.  
  4. bakery = baked.bakery()
  5.  
  6. def search_for_user(session, username, email=None):
  7.  
  8.     baked_query = bakery(lambda session: session.query(User))
  9.     baked_query += lambda q: q.filter(User.name == bindparam('username'))
  10.  
  11.     baked_query += lambda q: q.order_by(User.id)
  12.  
  13.     if email:
  14.         baked_query += lambda q: q.filter(User.email == bindparam('email'))
  15.  
  16.     result = baked_query(session).params(username=username, email=email).all()
  17.  
  18.     return result

See also

Baked Queries

#3054

The declarative system in conjunction with declared_attr has beenoverhauled to support new capabilities.

A function decorated with declared_attr is now called only afterany mixin-based column copies are generated. This means the function cancall upon mixin-established columns and will receive a reference to the correctColumn object:

  1. class HasFooBar(object):
  2.     foobar = Column(Integer)
  3.  
  4.     @declared_attr
  5.     def foobar_prop(cls):
  6.         return column_property('foobar: ' + cls.foobar)
  7.  
  8. class SomeClass(HasFooBar, Base):
  9.     __tablename__ = 'some_table'
  10.     id = Column(Integer, primary_key=True)

Above, SomeClass.foobar_prop will be invoked against SomeClass,and SomeClass.foobar will be the final Column object that isto be mapped to SomeClass, as opposed to the non-copied object presentdirectly on HasFooBar, even though the columns aren’t mapped yet.

The declared_attr function now memoizes the valuethat’s returned on a per-class basis, so that repeated calls to the sameattribute will return the same value. We can alter the example to illustratethis:

  1. class HasFooBar(object):
  2.     @declared_attr
  3.     def foobar(cls):
  4.         return Column(Integer)
  5.  
  6.     @declared_attr
  7.     def foobar_prop(cls):
  8.         return column_property('foobar: ' + cls.foobar)
  9.  
  10. class SomeClass(HasFooBar, Base):
  11.     __tablename__ = 'some_table'
  12.     id = Column(Integer, primary_key=True)

Previously, SomeClass would be mapped with one particular copy ofthe foobar column, but the foobar_prop by calling upon foobara second time would produce a different column. The value ofSomeClass.foobar is now memoized during declarative setup time, so thateven before the attribute is mapped by the mapper, the interim columnvalue will remain consistent no matter how many times thedeclared_attr is called upon.

The two behaviors above should help considerably with declarative definitionof many types of mapper properties that derive from other attributes, wherethe declared_attr function is called upon from otherdeclared_attr functions locally present before the class isactually mapped.

For a pretty slim edge case where one wishes to build a declarative mixinthat establishes distinct columns per subclass, a new modifierdeclaredattr.cascading is added. With this modifier, thedecorated function will be invoked individually for each class in themapped inheritance hierarchy. While this is already the behavior forspecial attributes such as table_args and _mapper_args,for columns and other properties the behavior by default assumes that attributeis affixed to the base class only, and just inherited from subclasses.With declared_attr.cascading, individual behaviors can beapplied:

  1. class HasIdMixin(object):
  2.     @declared_attr.cascading
  3.     def id(cls):
  4.         if has_inherited_table(cls):
  5.             return Column(ForeignKey('myclass.id'), primary_key=True)
  6.         else:
  7.             return Column(Integer, primary_key=True)
  8.  
  9. class MyClass(HasIdMixin, Base):
  10.     __tablename__ = 'myclass'
  11.     # ...
  12.  
  13. class MySubClass(MyClass):
  14.     ""
  15.     # ...

See also

Mixing in Columns in Inheritance Scenarios

Finally, the AbstractConcreteBase class has been reworkedso that a relationship or other mapper property can be set up inlineon the abstract base:

  1. from sqlalchemy import Column, Integer, ForeignKey
  2. from sqlalchemy.orm import relationship
  3. from sqlalchemy.ext.declarative import (declarative_base, declared_attr,
  4.     AbstractConcreteBase)
  5.  
  6. Base = declarative_base()
  7.  
  8. class Something(Base):
  9.     __tablename__ = u'something'
  10.     id = Column(Integer, primary_key=True)
  11.  
  12.  
  13. class Abstract(AbstractConcreteBase, Base):
  14.     id = Column(Integer, primary_key=True)
  15.  
  16.     @declared_attr
  17.     def something_id(cls):
  18.         return Column(ForeignKey(Something.id))
  19.  
  20.     @declared_attr
  21.     def something(cls):
  22.         return relationship(Something)
  23.  
  24.  
  25. class Concrete(Abstract):
  26.     __tablename__ = u'cca'
  27.     __mapper_args__ = {'polymorphic_identity': 'cca', 'concrete': True}

The above mapping will set up a table cca with both an id anda something_id column, and Concrete will also have a relationshipsomething. The new feature is that Abstract will also have anindependently configured relationship something that builds againstthe polymorphic union of the base.

#3150#2670#3149#2952#3050

ORM full object fetches 25% faster

The mechanics of the loading.py module as well as the identity maphave undergone several passes of inlining, refactoring, and pruning, sothat a raw load of rows now populates ORM-based objects around 25% faster.Assuming a 1M row table, a script like the following illustrates the typeof load that’s improved the most:

  1. import time
  2. from sqlalchemy import Integer, Column, create_engine, Table
  3. from sqlalchemy.orm import Session
  4. from sqlalchemy.ext.declarative import declarative_base
  5.  
  6. Base = declarative_base()
  7.  
  8. class Foo(Base):
  9.     __table__ = Table(
  10.         'foo', Base.metadata,
  11.         Column('id', Integer, primary_key=True),
  12.         Column('a', Integer(), nullable=False),
  13.         Column('b', Integer(), nullable=False),
  14.         Column('c', Integer(), nullable=False),
  15.     )
  16.  
  17. engine = create_engine(
  18.     'mysql+mysqldb://scott:tiger@localhost/test', echo=True)
  19.  
  20. sess = Session(engine)
  21.  
  22. now = time.time()
  23.  
  24. # avoid using all() so that we don't have the overhead of building
  25. # a large list of full objects in memory
  26. for obj in sess.query(Foo).yield_per(100).limit(1000000):
  27.     pass
  28.  
  29. print("Total time: %d" % (time.time() - now))

Local MacBookPro results bench from 19 seconds for 0.9 down to 14 seconds for1.0. The Query.yield_per() call is always a good idea when batchinghuge numbers of rows, as it prevents the Python interpreter from havingto allocate a huge amount of memory for all objects and their instrumentationat once. Without the Query.yield_per(), the above script on theMacBookPro is 31 seconds on 0.9 and 26 seconds on 1.0, the extra time spentsetting up very large memory buffers.

New KeyedTuple implementation dramatically faster

We took a look into the KeyedTuple implementation in the hopesof improving queries like this:

  1. rows = sess.query(Foo.a, Foo.b, Foo.c).all()

The KeyedTuple class is used rather than Python’scollections.namedtuple(), because the latter has a very complextype-creation routine that benchmarks much slower than KeyedTuple.However, when fetching hundreds of thousands of rows,collections.namedtuple() quickly overtakes KeyedTuple whichbecomes dramatically slower as instance invocation goes up. What to do?A new type that hedges between the approaches of both. Benchingall three types for “size” (number of rows returned) and “num”(number of distinct queries), the new “lightweight keyed tuple” eitheroutperforms both, or lags very slightly behind the faster object, based onwhich scenario. In the “sweet spot”, where we are both creating a good numberof new types as well as fetching a good number of rows, the lightweightobject totally smokes both namedtuple and KeyedTuple:

  1. -----------------
  2. size=10 num=10000                 # few rows, lots of queries
  3. namedtuple: 3.60302400589         # namedtuple falls over
  4. keyedtuple: 0.255059957504        # KeyedTuple very fast
  5. lw keyed tuple: 0.582715034485    # lw keyed trails right on KeyedTuple
  6. -----------------
  7. size=100 num=1000                 # <--- sweet spot
  8. namedtuple: 0.365247011185
  9. keyedtuple: 0.24896979332
  10. lw keyed tuple: 0.0889317989349   # lw keyed blows both away!
  11. -----------------
  12. size=10000 num=100
  13. namedtuple: 0.572599887848
  14. keyedtuple: 2.54251694679
  15. lw keyed tuple: 0.613876104355
  16. -----------------
  17. size=1000000 num=10               # few queries, lots of rows
  18. namedtuple: 5.79669594765         # namedtuple very fast
  19. keyedtuple: 28.856498003          # KeyedTuple falls over
  20. lw keyed tuple: 6.74346804619     # lw keyed trails right on namedtuple

#3176

Significant Improvements in Structural Memory Use

Structural memory use has been improved via much more significant useof slots for many internal objects. This optimization isparticularly geared towards the base memory size of large applicationsthat have lots of tables and columns, and reduces memorysize for a variety of high-volume objects including event listeninginternals, comparator objects and parts of the ORM attribute andloader strategy system.

A bench that makes use of heapy measure the startup size of Novaillustrates a difference of about 3.7 fewer megs, or 46%,taken up by SQLAlchemy’s objects, associated dictionaries, aswell as weakrefs, within a basic import of “nova.db.sqlalchemy.models”:

  1. # reported by heapy, summation of SQLAlchemy objects +
  2. # associated dicts + weakref-related objects with core of Nova imported:
  3.  
  4.     Before: total count 26477 total bytes 7975712
  5.     After: total count 18181 total bytes 4236456
  6.  
  7. # reported for the Python module space overall with the
  8. # core of Nova imported:
  9.  
  10.     Before: Partition of a set of 355558 objects. Total size = 61661760 bytes.
  11.     After: Partition of a set of 346034 objects. Total size = 57808016 bytes.

UPDATE statements are now batched with executemany() in a flush

UPDATE statements can now be batched within an ORM flushinto more performant executemany() call, similarly to how INSERTstatements can be batched; this will be invoked within flushbased on the following criteria:

  • two or more UPDATE statements in sequence involve the identical set ofcolumns to be modified.

  • The statement has no embedded SQL expressions in the SET clause.

  • The mapping does not use a version_id_col, orthe backend dialect supports a “sane” rowcount for an executemany()operation; most DBAPIs support this correctly now.

Session.get_bind() handles a wider variety of inheritance scenarios

The Session.get_bind() method is invoked whenever a query or unitof work flush process seeks to locate the database engine that correspondsto a particular class. The method has been improved to handle a varietyof inheritance-oriented scenarios, including:

  • Binding to a Mixin or Abstract Class:
  1. class MyClass(SomeMixin, Base):
  2.     __tablename__ = 'my_table'
  3.     # ...
  4.  
  5. session = Session(binds={SomeMixin: some_engine})
  • Binding to inherited concrete subclasses individually based on table:
  1. class BaseClass(Base):
  2.     __tablename__ = 'base'
  3.  
  4.     # ...
  5.  
  6. class ConcreteSubClass(BaseClass):
  7.     __tablename__ = 'concrete'
  8.  
  9.     # ...
  10.  
  11.     __mapper_args__ = {'concrete': True}
  12.  
  13.  
  14. session = Session(binds={
  15.     base_table: some_engine,
  16.     concrete_table: some_other_engine
  17. })

#3035

Session.get_bind() will receive the Mapper in all relevant Query cases

A series of issues were repaired where the Session.get_bind()would not receive the primary Mapper of the Query,even though this mapper was readily available (the primary mapper is thesingle mapper, or alternatively the first mapper, that is associated witha Query object).

The Mapper object, when passed to Session.get_bind(),is typically used by sessions that make use of theSession.binds parameter to associate mappers with aseries of engines (although in this use case, things frequently“worked” in most cases anyway as the bind would be located via themapped table object), or more specifically implement a user-definedSession.get_bind() method that provies some pattern ofselecting engines based on mappers, such as horizontal sharding or aso-called “routing” session that routes queries to different backends.

These scenarios include:

  1. session.query(User).count()
  1. session.query(User).filter(User.id == 15).update(
  2.         {"name": "foob"}, synchronize_session='fetch')
  3.  
  4. session.query(User).filter(User.id == 15).delete(
  5.         synchronize_session='fetch')
  • Queries against individual columns:
  1. session.query(User.id, User.name).all()
  • SQL functions and other expressions against indirect mappings such ascolumn_property:
  1. class User(Base):
  2.     # ...
  3.  
  4.     score = column_property(func.coalesce(self.tables.users.c.name, None)))
  5.  
  6. session.query(func.max(User.score)).scalar()

#3227#3242#1326

.info dictionary improvements

The InspectionAttr.info collection is now available on every kindof object that one would retrieve from the Mapper.all_orm_descriptorscollection. This includes hybrid_property and association_proxy().However, as these objects are class-bound descriptors, they must be accessedseparately from the class to which they are attached in order to getat the attribute. Below this is illustrated using theMapper.all_orm_descriptors namespace:

  1. class SomeObject(Base):
  2.     # ...
  3.  
  4.     @hybrid_property
  5.     def some_prop(self):
  6.         return self.value + 5
  7.  
  8.  
  9. inspect(SomeObject).all_orm_descriptors.some_prop.info['foo'] = 'bar'

It is also available as a constructor argument for all SchemaItemobjects (e.g. ForeignKey, UniqueConstraint etc.) as wellas remaining ORM constructs such as orm.synonym().

#2971

#2963

ColumnProperty constructs work a lot better with aliases, order_by

A variety of issues regarding column_property() have been fixed,most specifically with regards to the aliased() construct as wellas the “order by label” logic introduced in 0.9 (see Label constructs can now render as their name alone in an ORDER BY).

Given a mapping like the following:

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.  
  6. class B(Base):
  7.     __tablename__ = 'b'
  8.  
  9.     id = Column(Integer, primary_key=True)
  10.     a_id = Column(ForeignKey('a.id'))
  11.  
  12.  
  13. A.b = column_property(
  14.         select([func.max(B.id)]).where(B.a_id == A.id).correlate(A)
  15.     )

A simple scenario that included “A.b” twice would fail to rendercorrectly:

  1. print(sess.query(A, a1).order_by(a1.b))

This would order by the wrong column:

  1. SELECT a.id AS a_id, (SELECT max(b.id) AS max_1 FROM b
  2. WHERE b.a_id = a.id) AS anon_1, a_1.id AS a_1_id,
  3. (SELECT max(b.id) AS max_2
  4. FROM b WHERE b.a_id = a_1.id) AS anon_2
  5. FROM a, a AS a_1 ORDER BY anon_1

New output:

  1. SELECT a.id AS a_id, (SELECT max(b.id) AS max_1
  2. FROM b WHERE b.a_id = a.id) AS anon_1, a_1.id AS a_1_id,
  3. (SELECT max(b.id) AS max_2
  4. FROM b WHERE b.a_id = a_1.id) AS anon_2
  5. FROM a, a AS a_1 ORDER BY anon_2

There were also many scenarios where the “order by” logic would failto order by label, for example if the mapping were “polymorphic”:

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     type = Column(String)
  6.  
  7.     __mapper_args__ = {'polymorphic_on': type, 'with_polymorphic': '*'}

The order_by would fail to use the label, as it would be anonymized dueto the polymorphic loading:

  1. SELECT a.id AS a_id, a.type AS a_type, (SELECT max(b.id) AS max_1
  2. FROM b WHERE b.a_id = a.id) AS anon_1
  3. FROM a ORDER BY (SELECT max(b.id) AS max_2
  4. FROM b WHERE b.a_id = a.id)

Now that the order by label tracks the anonymized label, this now works:

  1. SELECT a.id AS a_id, a.type AS a_type, (SELECT max(b.id) AS max_1
  2. FROM b WHERE b.a_id = a.id) AS anon_1
  3. FROM a ORDER BY anon_1

Included in these fixes are a variety of heisenbugs that could corruptthe state of an aliased() construct such that the labeling logicwould again fail; these have also been fixed.

#3148#3188

New Features and Improvements - Core

Select/Query LIMIT / OFFSET may be specified as an arbitrary SQL expression

The Select.limit() and Select.offset() methods now acceptany SQL expression, in addition to integer values, as arguments. The ORMQuery object also passes through any expression to the underlyingSelect object. Typicallythis is used to allow a bound parameter to be passed, which can be substitutedwith a value later:

  1. sel = select([table]).limit(bindparam('mylimit')).offset(bindparam('myoffset'))

Dialects which don’t support non-integer LIMIT or OFFSET expressions may continueto not support this behavior; third party dialects may also need modificationin order to take advantage of the new behavior. A dialect which currentlyuses the ._limit or ._offset attributes will continue to functionfor those cases where the limit/offset was specified as a simple integer value.However, when a SQL expression is specified, these two attributes willinstead raise a CompileError on access. A third-party dialect whichwishes to support the new feature should now call upon the ._limit_clauseand ._offset_clause attributes to receive the full SQL expression, ratherthan the integer value.

The use_alter flag on ForeignKeyConstraint is (usually) no longer needed

The MetaData.create_all() and MetaData.drop_all() methods willnow make use of a system that automatically renders an ALTER statementfor foreign key constraints that are involved in mutually-dependent cyclesbetween tables, without theneed to specify ForeignKeyConstraint.use_alter. Additionally,the foreign key constraints no longer need to have a name in order to becreated via ALTER; only the DROP operation requires a name. In the caseof a DROP, the feature will ensure that only constraints which haveexplicit names are actually included as ALTER statements. In thecase of an unresolvable cycle within a DROP, the system emitsa succinct and clear error message now if the DROP cannot proceed.

The ForeignKeyConstraint.use_alter andForeignKey.use_alter flags remain in place, and continue to havethe same effect of establishing those constraints for which ALTER isrequired during a CREATE/DROP scenario.

As of version 1.0.1, special logic takes over in the case of SQLite, whichdoes not support ALTER, in the case that during a DROP, the given tables havean unresolvable cycle; in this case a warning is emitted, and the tablesare dropped with no ordering, which is usually fine on SQLite unlessconstraints are enabled. To resolve the warning and proceed with at leasta partial ordering on a SQLite database, particularly one where constraintsare enabled, re-apply “use_alter” flags to thoseForeignKey and ForeignKeyConstraint objects which shouldbe explicitly omitted from the sort.

See also

Creating/Dropping Foreign Key Constraints via ALTER - full description of the new behavior.

#3282

ResultProxy “auto close” is now a “soft” close

For many releases, the ResultProxy object has always beenautomatically closed out at the point at which all result rows have beenfetched. This was to allow usage of the object without the need to callupon ResultProxy.close() explicitly; as all DBAPI resources had beenfreed, the object was safe to discard. However, the object maintaineda strict “closed” behavior, which meant that any subsequent calls toResultProxy.fetchone(), ResultProxy.fetchmany() orResultProxy.fetchall() would now raise a ResourceClosedError:

  1. >>> result = connection.execute(stmt)
  2. >>> result.fetchone()
  3. (1, 'x')
  4. >>> result.fetchone()
  5. None  # indicates no more rows
  6. >>> result.fetchone()
  7. exception: ResourceClosedError

This behavior is inconsistent vs. what pep-249 states, which isthat you can call upon the fetch methods repeatedly even after resultsare exhausted. It also interferes with behavior for some implementations ofresult proxy, such as the BufferedColumnResultProxy used by thecx_oracle dialect for certain datatypes.

To solve this, the “closed” state of the ResultProxy has beenbroken into two states; a “soft close” which does the majority of what“close” does, in that it releases the DBAPI cursor and in the case of a“close with result” object will also release the connection, and a“closed” state which is everything included by “soft close” as well asestablishing the fetch methods as “closed”. The ResultProxy.close()method is now never called implicitly, only the ResultProxy._soft_close()method which is non-public:

  1. >>> result = connection.execute(stmt)
  2. >>> result.fetchone()
  3. (1, 'x')
  4. >>> result.fetchone()
  5. None  # indicates no more rows
  6. >>> result.fetchone()
  7. None  # still None
  8. >>> result.fetchall()
  9. []
  10. >>> result.close()
  11. >>> result.fetchone()
  12. exception: ResourceClosedError  # *now* it raises

#3330#3329

CHECK Constraints now support the %(column_0_name)s token in naming conventions

The %(column_0_name)s will derive from the first column found in theexpression of a CheckConstraint:

  1. metadata = MetaData(
  2.     naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
  3. )
  4.  
  5. foo = Table('foo', metadata,
  6.     Column('value', Integer),
  7. )
  8.  
  9. CheckConstraint(foo.c.value > 5)

Will render:

  1. CREATE TABLE foo (
  2.     value INTEGER,
  3.     CONSTRAINT ck_foo_value CHECK (value > 5)
  4. )

The combination of naming conventions with the constraint produced by aSchemaType such as Boolean or Enum will alsonow make use of all CHECK constraint conventions.

See also

Naming CHECK Constraints

Configuring Naming for Boolean, Enum, and other schema types

#3299

Constraints referring to unattached Columns can auto-attach to the Table when their referred columns are attached

Since at least version 0.8, a Constraint has had the ability to“auto-attach” itself to a Table based on being passed table-attached columns:

  1. from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint
  2.  
  3. m = MetaData()
  4.  
  5. t = Table('t', m,
  6.     Column('a', Integer),
  7.     Column('b', Integer)
  8. )
  9.  
  10. uq = UniqueConstraint(t.c.a, t.c.b)  # will auto-attach to Table
  11.  
  12. assert uq in t.constraints

In order to assist with some cases that tend to come up with declarative,this same auto-attachment logic can now function even if the Columnobjects are not yet associated with the Table; additional eventsare established such that when those Column objects are associated,the Constraint is also added:

  1. from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint
  2.  
  3. m = MetaData()
  4.  
  5. a = Column('a', Integer)
  6. b = Column('b', Integer)
  7.  
  8. uq = UniqueConstraint(a, b)
  9.  
  10. t = Table('t', m, a, b)
  11.  
  12. assert uq in t.constraints  # constraint auto-attached

The above feature was a late add as of version 1.0.0b3. A fix as ofversion 1.0.4 for #3411 ensures that this logicdoes not occur if the Constraint refers to a mixture ofColumn objects and string column names; as we do not yet havetracking for the addition of names to a Table:

  1. from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint
  2.  
  3. m = MetaData()
  4.  
  5. a = Column('a', Integer)
  6. b = Column('b', Integer)
  7.  
  8. uq = UniqueConstraint(a, 'b')
  9.  
  10. t = Table('t', m, a, b)
  11.  
  12. # constraint *not* auto-attached, as we do not have tracking
  13. # to locate when a name 'b' becomes available on the table
  14. assert uq not in t.constraints

Above, the attachment event for column “a” to table “t” will fire off beforecolumn “b” is attached (as “a” is stated in the Table constructorbefore “b”), and the constraint will fail to locate “b” if it were to attemptan attachment. For consistency, if the constraint refers to any string names,the autoattach-on-column-attach logic is skipped.

The original auto-attach logic of course remains in place, if the Tablealready contains all the target Column objects at the timethe Constraint is constructed:

  1. from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint
  2.  
  3. m = MetaData()
  4.  
  5. a = Column('a', Integer)
  6. b = Column('b', Integer)
  7.  
  8.  
  9. t = Table('t', m, a, b)
  10.  
  11. uq = UniqueConstraint(a, 'b')
  12.  
  13. # constraint auto-attached normally as in older versions
  14. assert uq in t.constraints

#3341#3411

INSERT FROM SELECT now includes Python and SQL-expression defaults

Insert.from_select() now includes Python and SQL-expression defaults ifotherwise unspecified; the limitation where non-server column defaultsaren’t included in an INSERT FROM SELECT is now lifted and theseexpressions are rendered as constants into the SELECT statement:

  1. from sqlalchemy import Table, Column, MetaData, Integer, select, func
  2.  
  3. m = MetaData()
  4.  
  5. t = Table(
  6.     't', m,
  7.     Column('x', Integer),
  8.     Column('y', Integer, default=func.somefunction()))
  9.  
  10. stmt = select([t.c.x])
  11. print(t.insert().from_select(['x'], stmt))

Will render:

  1. INSERT INTO t (x, y) SELECT t.x, somefunction() AS somefunction_1
  2. FROM t

The feature can be disabled usingInsert.from_select.include_defaults.

Column server defaults now render literal values

The “literal binds” compiler flag is switched on when aDefaultClause, set up by Column.server_defaultis present as a SQL expression to be compiled. This allows literalsembedded in SQL to render correctly, such as:

  1. from sqlalchemy import Table, Column, MetaData, Text
  2. from sqlalchemy.schema import CreateTable
  3. from sqlalchemy.dialects.postgresql import ARRAY, array
  4. from sqlalchemy.dialects import postgresql
  5.  
  6. metadata = MetaData()
  7.  
  8. tbl = Table("derp", metadata,
  9.     Column("arr", ARRAY(Text),
  10.                 server_default=array(["foo", "bar", "baz"])),
  11. )
  12.  
  13. print(CreateTable(tbl).compile(dialect=postgresql.dialect()))

Now renders:

  1. CREATE TABLE derp (
  2.     arr TEXT[] DEFAULT ARRAY['foo', 'bar', 'baz']
  3. )

Previously, the literal values "foo", "bar", "baz" would render asbound parameters, which are useless in DDL.

#3087

UniqueConstraint is now part of the Table reflection process

A Table object populated using autoload=True will nowinclude UniqueConstraint constructs as well asIndex constructs. This logic has a few caveats forPostgreSQL and MySQL:

PostgreSQL

PostgreSQL has the behavior such that when a UNIQUE constraint iscreated, it implicitly creates a UNIQUE INDEX corresponding to thatconstraint as well. The Inspector.get_indexes() and theInspector.get_unique_constraints() methods will continue toboth return these entries distinctly, whereInspector.get_indexes() now features a tokenduplicates_constraint within the index entry indicating thecorresponding constraint when detected. However, when performingfull table reflection using Table(…, autoload=True), theIndex construct is detected as being linked to theUniqueConstraint, and is not present within theTable.indexes collection; only the UniqueConstraintwill be present in the Table.constraints collection. Thisdeduplication logic works by joining to the pg_constraint tablewhen querying pg_index to see if the two constructs are linked.

MySQL

MySQL does not have separate concepts for a UNIQUE INDEX and a UNIQUEconstraint. While it supports both syntaxes when creating tables and indexes,it does not store them any differently. TheInspector.get_indexes()and the Inspector.get_unique_constraints() methods will continue toboth return an entry for a UNIQUE index in MySQL,where Inspector.get_unique_constraints() features a new tokenduplicates_index within the constraint entry indicating that this is adupe entry corresponding to that index. However, when performingfull table reflection using Table(…, autoload=True),the UniqueConstraint construct isnot part of the fully reflected Table construct under anycircumstances; this construct is always represented by a Indexwith the unique=True setting present in the Table.indexescollection.

See also

PostgreSQL Index Reflection

MySQL Unique Constraints and Reflection

#3184

New systems to safely emit parameterized warnings

For a long time, there has been a restriction that warning messages could notrefer to data elements, such that a particular function might emit aninfinite number of unique warnings. The key place this occurs is in theUnicode type received non-unicode bind param value warning. Placingthe data value in this message would mean that the Python warningregistryfor that module, or in some cases the Python-global warnings.onceregistry,would grow unbounded, as in most warning scenarios, one of these two collectionsis populated with every distinct warning message.

The change here is that by using a special string type that purposelychanges how the string is hashed, we can control that a large number ofparameterized messages are hashed only on a small set of possible hashvalues, such that a warning such as Unicode type received non-unicodebind param value can be tailored to be emitted only a specific numberof times; beyond that, the Python warnings registry will begin recordingthem as duplicates.

To illustrate, the following test script will show only ten warnings beingemitted for ten of the parameter sets, out of a total of 1000:

  1. from sqlalchemy import create_engine, Unicode, select, cast
  2. import random
  3. import warnings
  4.  
  5. e = create_engine("sqlite://")
  6.  
  7. # Use the "once" filter (which is also the default for Python
  8. # warnings).  Exactly ten of these warnings will
  9. # be emitted; beyond that, the Python warnings registry will accumulate
  10. # new values as dupes of one of the ten existing.
  11. warnings.filterwarnings("once")
  12.  
  13. for i in range(1000):
  14.     e.execute(select([cast(
  15.         ('foo_%d' % random.randint(0, 1000000)).encode('ascii'), Unicode)]))

The format of the warning here is:

  1. /path/lib/sqlalchemy/sql/sqltypes.py:186: SAWarning: Unicode type received
  2.   non-unicode bind param value 'foo_4852'. (this warning may be
  3.   suppressed after 10 occurrences)

#3178

Key Behavioral Changes - ORM

query.update() now resolves string names into mapped attribute names

The documentation for Query.update() states that the givenvalues dictionary is “a dictionary with attributes names as keys”,implying that these are mapped attribute names. Unfortunately, the functionwas designed more in mind to receive attributes and SQL expressions andnot as much strings; when stringswere passed, these strings would be passed through straight to the coreupdate statement without any resolution as far as how these names arerepresented on the mapped class, meaning the name would have to match thatof a table column exactly, not how an attribute of that name was mappedonto the class.

The string names are now resolved as attribute names in earnest:

  1. class User(Base):
  2.     __tablename__ = 'user'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     name = Column('user_name', String(50))

Above, the column user_name is mapped as name. Previously,a call to Query.update() that was passed strings would have tohave been called as follows:

  1. session.query(User).update({'user_name': 'moonbeam'})

The given string is now resolved against the entity:

  1. session.query(User).update({'name': 'moonbeam'})

It is typically preferable to use the attribute directly, to avoid anyambiguity:

  1. session.query(User).update({User.name: 'moonbeam'})

The change also indicates that synonyms and hybrid attributes can be referredto by string name as well:

  1. class User(Base):
  2.     __tablename__ = 'user'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     name = Column('user_name', String(50))
  6.  
  7.     @hybrid_property
  8.     def fullname(self):
  9.         return self.name
  10.  
  11. session.query(User).update({'fullname': 'moonbeam'})

#3228

Warnings emitted when comparing objects with None values to relationships

This change is new as of 1.0.1. Some users are performingqueries that are essentially of this form:

  1. session.query(Address).filter(Address.user == User(id=None))

This pattern is not currently supported in SQLAlchemy. For all versions,it emits SQL resembling:

  1. SELECT address.id AS address_id, address.user_id AS address_user_id,
  2. address.email_address AS address_email_address
  3. FROM address WHERE ? = address.user_id
  4. (None,)

Note above, there is a comparison WHERE ? = address.user_id where thebound value ? is receiving None, or NULL in SQL. This willalways return False in SQL. The comparison here would in theorygenerate SQL as follows:

  1. SELECT address.id AS address_id, address.user_id AS address_user_id,
  2. address.email_address AS address_email_address
  3. FROM address WHERE address.user_id IS NULL

But right now, it does not. Applications which are relying upon thefact that “NULL = NULL” produces False in all cases run the risk thatsomeday, SQLAlchemy might fix this issue to generate “IS NULL”, and the querieswill then produce different results. Therefore with this kind of operation,you will see a warning:

  1. SAWarning: Got None for value of column user.id; this is unsupported
  2. for a relationship comparison and will not currently produce an
  3. IS comparison (but may in a future release)

Note that this pattern was broken in most cases for release 1.0.0 includingall of the betas; a value like SYMBOL('NEVER_SET') would be generated.This issue has been fixed, but as a result of identifying this pattern,the warning is now there so that we can more safely repair this brokenbehavior (now captured in #3373) in a future release.

#3371

A “negated contains or equals” relationship comparison will use the current value of attributes, not the database value

This change is new as of 1.0.1; while we would have preferred for this to be in 1.0.0,it only became apparent as a result of #3371.

Given a mapping:

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.     id = Column(Integer, primary_key=True)
  4.  
  5. class B(Base):
  6.     __tablename__ = 'b'
  7.     id = Column(Integer, primary_key=True)
  8.     a_id = Column(ForeignKey('a.id'))
  9.     a = relationship("A")

Given A, with primary key of 7, but which we changed to be 10without flushing:

  1. s = Session(autoflush=False)
  2. a1 = A(id=7)
  3. s.add(a1)
  4. s.commit()
  5.  
  6. a1.id = 10

A query against a many-to-one relationship with this object as the targetwill use the value 10 in the bound parameters:

  1. s.query(B).filter(B.a == a1)

Produces:

  1. SELECT b.id AS b_id, b.a_id AS b_a_id
  2. FROM b
  3. WHERE ? = b.a_id
  4. (10,)

However, before this change, the negation of this criteria would not use10, it would use 7, unless the object were flushed first:

  1. s.query(B).filter(B.a != a1)

Produces (in 0.9 and all versions prior to 1.0.1):

  1. SELECT b.id AS b_id, b.a_id AS b_a_id
  2. FROM b
  3. WHERE b.a_id != ? OR b.a_id IS NULL
  4. (7,)

For a transient object, it would produce a broken query:

  1. SELECT b.id, b.a_id
  2. FROM b
  3. WHERE b.a_id != :a_id_1 OR b.a_id IS NULL
  4. {u'a_id_1': symbol('NEVER_SET')}

This inconsistency has been repaired, and in all queries the current attributevalue, in this example 10, will now be used.

#3374

Changes to attribute events and other operations regarding attributes that have no pre-existing value

In this change, the default return value of None when accessing an objectis now returned dynamically on each access, rather than implicitly setting theattribute’s state with a special “set” operation when it is first accessed.The visible result of this change is that obj.dict is not implicitlymodified on get, and there are also some minor behavioral changesfor attributes.get_history() and related functions.

Given an object with no state:

  1. >>> obj = Foo()

It has always been SQLAlchemy’s behavior such that if we access a scalaror many-to-one attribute that was never set, it is returned as None:

  1. >>> obj.someattr
  2. None

This value of None is in fact now part of the state of obj, and isnot unlike as though we had set the attribute explicitly, e.g.obj.someattr = None. However, the “set on get” here would behavedifferently as far as history and events. It would not emit any attributeevent, and additionally if we view history, we see this:

  1. >>> inspect(obj).attrs.someattr.history
  2. History(added=(), unchanged=[None], deleted=())   # 0.9 and below

That is, it’s as though the attribute were always None and werenever changed. This is explicitly different from if we had set theattribute first instead:

  1. >>> obj = Foo()
  2. >>> obj.someattr = None
  3. >>> inspect(obj).attrs.someattr.history
  4. History(added=[None], unchanged=(), deleted=())  # all versions

The above means that the behavior of our “set” operation can be corruptedby the fact that the value was accessed via “get” earlier. In 1.0, thisinconsistency has been resolved, by no longer actually setting anythingwhen the default “getter” is used.

  1. >>> obj = Foo()
  2. >>> obj.someattr
  3. None
  4. >>> inspect(obj).attrs.someattr.history
  5. History(added=(), unchanged=(), deleted=())  # 1.0
  6. >>> obj.someattr = None
  7. >>> inspect(obj).attrs.someattr.history
  8. History(added=[None], unchanged=(), deleted=())

The reason the above behavior hasn’t had much impact is because theINSERT statement in relational databases considers a missing value to bethe same as NULL in most cases. Whether SQLAlchemy received a historyevent for a particular attribute set to None or not would usually not matter;as the difference between sending None/NULL or not wouldn’t have an impact.However, as #3060 (described here in Priority of attribute changes on relationship-bound attributes vs. FK-bound may appear to change)illustrates, there are some seldom edge caseswhere we do in fact want to positively have None set. Also, allowingthe attribute event here means it’s now possible to create “default value”functions for ORM mapped attributes.

As part of this change, the generation of the implicit “None” is now disabledfor other situations where this used to occur; this includes when anattribute set operation on a many-to-one is received; previously, the “old” valuewould be “None” if it had been not set otherwise; it now will send thevalue orm.attributes.NEVER_SET, which is a value that may be sentto an attribute listener now. This symbol may also be received whencalling on mapper utility functions such as Mapper.primary_key_from_instance();if the primary key attributes have no setting at all, whereas the valuewould be None before, it will now be the orm.attributes.NEVER_SETsymbol, and no change to the object’s state occurs.

#3061

Priority of attribute changes on relationship-bound attributes vs. FK-bound may appear to change

As a side effect of #3060, setting a relationship-bound attribute to Noneis now a tracked history event which refers to the intention of persistingNone to that attribute. As it has always been the case that setting arelationship-bound attribute will trump direct assignment to the foreign keyattributes, a change in behavior can be seen here when assigning None.Given a mapping:

  1. class A(Base):
  2.     __tablename__ = 'table_a'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.  
  6. class B(Base):
  7.     __tablename__ = 'table_b'
  8.  
  9.     id = Column(Integer, primary_key=True)
  10.     a_id = Column(ForeignKey('table_a.id'))
  11.     a = relationship(A)

In 1.0, the relationship-bound attribute takes precedence over the FK-boundattribute in all cases, whether or notthe value we assign is a reference to an A object or is None.In 0.9, the behavior is inconsistent andonly takes effect if a value is assigned; the None is not considered:

  1. a1 = A(id=1)
  2. a2 = A(id=2)
  3. session.add_all([a1, a2])
  4. session.flush()
  5.  
  6. b1 = B()
  7. b1.a = a1   # we expect a_id to be '1'; takes precedence in 0.9 and 1.0
  8.  
  9. b2 = B()
  10. b2.a = None  # we expect a_id to be None; takes precedence only in 1.0
  11.  
  12. b1.a_id = 2
  13. b2.a_id = 2
  14.  
  15. session.add_all([b1, b2])
  16. session.commit()
  17.  
  18. assert b1.a is a1  # passes in both 0.9 and 1.0
  19. assert b2.a is None  # passes in 1.0, in 0.9 it's a2

#3060

session.expunge() will fully detach an object that’s been deleted

The behavior of Session.expunge() had a bug that caused aninconsistency in behavior regarding deleted objects. Theobject_session() function as well as the InstanceState.sessionattribute would still report object as belonging to the Sessionsubsequent to the expunge:

  1. u1 = sess.query(User).first()
  2. sess.delete(u1)
  3.  
  4. sess.flush()
  5.  
  6. assert u1 not in sess
  7. assert inspect(u1).session is sess  # this is normal before commit
  8.  
  9. sess.expunge(u1)
  10.  
  11. assert u1 not in sess
  12. assert inspect(u1).session is None  # would fail

Note that it is normal for u1 not in sess to be True whileinspect(u1).session still refers to the session, while the transactionis ongoing subsequent to the delete operation and Session.expunge()has not been called; the full detachment normally completes once thetransaction is committed. This issue would also impact functionsthat rely on Session.expunge() such as make_transient().

#3139

Joined/Subquery eager loading explicitly disallowed with yield_per

In order to make the Query.yield_per() method easier to use,an exception is raised if any subquery eager loaders, or joinedeager loaders that would use collections, areto take effect when yield_per is used, as these are currently not compatiblewith yield-per (subquery loading could be in theory, however).When this error is raised, the lazyload() option can be sent withan asterisk:

  1. q = sess.query(Object).options(lazyload('*')).yield_per(100)

or use Query.enable_eagerloads():

  1. q = sess.query(Object).enable_eagerloads(False).yield_per(100)

The lazyload() option has the advantage that additional many-to-onejoined loader options can still be used:

  1. q = sess.query(Object).options(
  2.     lazyload('*'), joinedload("some_manytoone")).yield_per(100)

Changes and fixes in handling of duplicate join targets

Changes here encompass bugs where an unexpected and inconsistentbehavior would occur in some scenarios when joining to an entitytwice, or to multiple single-table entities against the same table,without using a relationship-based ON clause, as well as when joiningmultiple times to the same target relationship.

Starting with a mapping as:

  1. from sqlalchemy import Integer, Column, String, ForeignKey
  2. from sqlalchemy.orm import Session, relationship
  3. from sqlalchemy.ext.declarative import declarative_base
  4.  
  5. Base = declarative_base()
  6.  
  7. class A(Base):
  8.     __tablename__ = 'a'
  9.     id = Column(Integer, primary_key=True)
  10.     bs = relationship("B")
  11.  
  12. class B(Base):
  13.     __tablename__ = 'b'
  14.     id = Column(Integer, primary_key=True)
  15.     a_id = Column(ForeignKey('a.id'))

A query that joins to A.bs twice:

  1. print(s.query(A).join(A.bs).join(A.bs))

Will render:

  1. SELECT a.id AS a_id
  2. FROM a JOIN b ON a.id = b.a_id

The query deduplicates the redundant A.bs because it is attemptingto support a case like the following:

  1. s.query(A).join(A.bs).\
  2.     filter(B.foo == 'bar').\
  3.     reset_joinpoint().join(A.bs, B.cs).filter(C.bar == 'bat')

That is, the A.bs is part of a “path”. As part of #3367,arriving at the same endpoint twice without it being part of alarger path will now emit a warning:

  1. SAWarning: Pathed join target A.bs has already been joined to; skipping

The bigger change involves when joining to an entity without using arelationship-bound path. If we join to B twice:

  1. print(s.query(A).join(B, B.a_id == A.id).join(B, B.a_id == A.id))

In 0.9, this would render as follows:

  1. SELECT a.id AS a_id
  2. FROM a JOIN b ON b.a_id = a.id JOIN b AS b_1 ON b_1.a_id = a.id

This is problematic since the aliasing is implicit and in the case of differentON clauses can lead to unpredictable results.

In 1.0, no automatic aliasing is applied and we get:

  1. SELECT a.id AS a_id
  2. FROM a JOIN b ON b.a_id = a.id JOIN b ON b.a_id = a.id

This will raise an error from the database. While it might be nice ifthe “duplicate join target” acted identically if we joined both fromredundant relationships vs. redundant non-relationship based targets,for now we are only changing the behavior in the more serious case whereimplicit aliasing would have occurred previously, and only emitting a warningin the relationship case. Ultimately, joining to the same thing twice withoutany aliasing to disambiguate should raise an error in all cases.

The change also has an impact on single-table inheritance targets. Usinga mapping as follows:

  1. from sqlalchemy import Integer, Column, String, ForeignKey
  2. from sqlalchemy.orm import Session, relationship
  3. from sqlalchemy.ext.declarative import declarative_base
  4.  
  5. Base = declarative_base()
  6.  
  7. class A(Base):
  8.     __tablename__ = "a"
  9.  
  10.     id = Column(Integer, primary_key=True)
  11.     type = Column(String)
  12.  
  13.     __mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity': 'a'}
  14.  
  15.  
  16. class ASub1(A):
  17.     __mapper_args__ = {'polymorphic_identity': 'asub1'}
  18.  
  19.  
  20. class ASub2(A):
  21.     __mapper_args__ = {'polymorphic_identity': 'asub2'}
  22.  
  23.  
  24. class B(Base):
  25.     __tablename__ = 'b'
  26.  
  27.     id = Column(Integer, primary_key=True)
  28.  
  29.     a_id = Column(Integer, ForeignKey("a.id"))
  30.  
  31.     a = relationship("A", primaryjoin="B.a_id == A.id", backref='b')
  32.  
  33. s = Session()
  34.  
  35. print(s.query(ASub1).join(B, ASub1.b).join(ASub2, B.a))
  36.  
  37. print(s.query(ASub1).join(B, ASub1.b).join(ASub2, ASub2.id == B.a_id))

The two queries at the bottom are equivalent, and should both renderthe identical SQL:

  1. SELECT a.id AS a_id, a.type AS a_type
  2. FROM a JOIN b ON b.a_id = a.id JOIN a ON b.a_id = a.id AND a.type IN (:type_1)
  3. WHERE a.type IN (:type_2)

The above SQL is invalid, as it renders “a” within the FROM list twice.However, the implicit aliasing bug would occur with the second query onlyand render this instead:

  1. SELECT a.id AS a_id, a.type AS a_type
  2. FROM a JOIN b ON b.a_id = a.id JOIN a AS a_1
  3. ON a_1.id = b.a_id AND a_1.type IN (:type_1)
  4. WHERE a_1.type IN (:type_2)

Where above, the second join to “a” is aliased. While this seems convenient,it’s not how single-inheritance queries work in general and is misleadingand inconsistent.

The net effect is that applications which were relying on this bug will nowhave an error raised by the database. The solution is to use the expectedform. When referring to multiple subclasses of a single-inheritanceentity in a query, you must manually use aliases to disambiguate the table,as all the subclasses normally refer to the same table:

  1. asub2_alias = aliased(ASub2)
  2.  
  3. print(s.query(ASub1).join(B, ASub1.b).join(asub2_alias, B.a.of_type(asub2_alias)))

#3233#3367

Deferred Columns No Longer Implicitly Undefer

Mapped attributes marked as deferred without explicit undeferralwill now remain “deferred” even if their column is otherwisepresent in the result set in some way. This is a performanceenhancement in that an ORM load no longer spends time searchingfor each deferred column when the result set is obtained. However,for an application that has been relying upon this, an explicitundefer() or similar option should now be used, in orderto prevent a SELECT from being emitted when the attribute is accessed.

Deprecated ORM Event Hooks Removed

The following ORM event hooks, some of which have been deprecated since0.5, have been removed: translate_row, populate_instance,append_result, create_instance. The use cases for these hooksoriginated in the very early 0.1 / 0.2 series of SQLAlchemy and have longsince been unnecessary. In particular, the hooks were largely unusableas the behavioral contracts within these events was strongly linked tothe surrounding internals, such as how an instance needs to be createdand initialized as well as how columns are located within an ORM-generatedrow. The removal of these hooks greatly simplifies the mechanics of ORMobject loading.

API Change for new Bundle feature when custom row loaders are used

The new Bundle object of 0.9 has a small change in API,when the create_row_processor() method is overridden on a custom class.Previously, the sample code looked like:

  1. from sqlalchemy.orm import Bundle
  2.  
  3. class DictBundle(Bundle):
  4.     def create_row_processor(self, query, procs, labels):
  5.         """Override create_row_processor to return values as dictionaries"""
  6.         def proc(row, result):
  7.             return dict(
  8.                         zip(labels, (proc(row, result) for proc in procs))
  9.                     )
  10.         return proc

The unused result member is now removed:

  1. from sqlalchemy.orm import Bundle
  2.  
  3. class DictBundle(Bundle):
  4.     def create_row_processor(self, query, procs, labels):
  5.         """Override create_row_processor to return values as dictionaries"""
  6.         def proc(row):
  7.             return dict(
  8.                         zip(labels, (proc(row) for proc in procs))
  9.                     )
  10.         return proc

See also

Column Bundles

Right inner join nesting now the default for joinedload with innerjoin=True

The behavior of joinedload.innerjoin as well asrelationship.innerjoin is now to use “nested”inner joins, that is, right-nested, as the default behavior when aninner join joined eager load is chained to an outer join eager load. Inorder to get the old behavior of chaining all joined eager loads asouter join when an outer join is present, use innerjoin="unnested".

As introduced in Right-nested inner joins available in joined eager loads from version 0.9, the behavior ofinnerjoin="nested" is that an inner join eager load chained to an outerjoin eager load will use a right-nested join. "nested" is now impliedwhen using innerjoin=True:

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

With the new default, this will render the FROM clause in the form:

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

That is, using a right-nested join for the INNER join so that the fullresult of users can be returned. The use of an INNER join is more efficientthan using an OUTER join, and allows the joinedload.innerjoinoptimization parameter to take effect in all cases.

To get the older behavior, use innerjoin="unnested":

  1. query(User).options(
  2.     joinedload("orders", innerjoin=False).joinedload("items", innerjoin="unnested"))

This will avoid right-nested joins and chain the joins together using allOUTER joins despite the innerjoin directive:

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

As noted in the 0.9 notes, the only database backend that has difficultywith right-nested joins is SQLite; SQLAlchemy as of 0.9 converts a right-nestedjoin into a subquery as a join target on SQLite.

See also

Right-nested inner joins available in joined eager loads - description of the feature as introduced in 0.9.4.

#3008

Subqueries no longer applied to uselist=False joined eager loads

Given a joined eager load like the following:

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.     id = Column(Integer, primary_key=True)
  4.     b = relationship("B", uselist=False)
  5.  
  6.  
  7. class B(Base):
  8.     __tablename__ = 'b'
  9.     id = Column(Integer, primary_key=True)
  10.     a_id = Column(ForeignKey('a.id'))
  11.  
  12. s = Session()
  13. print(s.query(A).options(joinedload(A.b)).limit(5))

SQLAlchemy considers the relationship A.b to be a “one to many,loaded as a single value”, which is essentially a “one to one”relationship. However, joined eager loading has always treated theabove as a situation where the main query needs to be inside asubquery, as would normally be needed for a collection of B objectswhere the main query has a LIMIT applied:

  1. SELECT anon_1.a_id AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
  2. FROM (SELECT a.id AS a_id
  3. FROM a LIMIT :param_1) AS anon_1
  4. LEFT OUTER JOIN b AS b_1 ON anon_1.a_id = b_1.a_id

However, since the relationship of the inner query to the outer one isthat at most only one row is shared in the case of uselist=False(in the same way as a many-to-one), the “subquery” used with LIMIT +joined eager loading is now dropped in this case:

  1. SELECT a.id AS a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
  2. FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id
  3. LIMIT :param_1

In the case that the LEFT OUTER JOIN returns more than one row, the ORMhas always emitted a warning here and ignored additional results foruselist=False, so the results in that error situation should not change.

#3249

query.update() / query.delete() raises if used with join(), select_from(), from_self()

A warning is emitted in SQLAlchemy 0.9.10 (not yet released as ofJune 9, 2015) when the Query.update() or Query.delete() methodsare invoked against a query which has also called upon Query.join(),Query.outerjoin(),Query.select_from() or Query.from_self(). These are unsupporteduse cases which silently fail in the 0.9 series up until 0.9.10 where it emitsa warning. In 1.0, these cases raise an exception.

#3349

query.update() with synchronize_session='evaluate' raises on multi-table update

The “evaluator” for Query.update() won’t work with multi-tableupdates, and needs to be set to synchronize_session=False orsynchronize_session='fetch' when multiple tables are present.The new behavior is that an explicit exception is now raised, with a messageto change the synchronize setting.This is upgraded from a warning emitted as of 0.9.7.

#3117

Resurrect Event has been Removed

The “resurrect” ORM event has been removed entirely. This event ceased tohave any function since version 0.8 removed the older “mutable” systemfrom the unit of work.

Change to single-table-inheritance criteria when using from_self(), count()

Given a single-table inheritance mapping, such as:

  1. class Widget(Base):
  2.     __table__ = 'widget_table'
  3.  
  4. class FooWidget(Widget):
  5.     pass

Using Query.from_self() or Query.count() against a subclasswould produce a subquery, but then add the “WHERE” criteria for subtypesto the outside:

  1. sess.query(FooWidget).from_self().all()

rendering:

  1. SELECT
  2.     anon_1.widgets_id AS anon_1_widgets_id,
  3.     anon_1.widgets_type AS anon_1_widgets_type
  4. FROM (SELECT widgets.id AS widgets_id, widgets.type AS widgets_type,
  5. FROM widgets) AS anon_1
  6. WHERE anon_1.widgets_type IN (?)

The issue with this is that if the inner query does not specify allcolumns, then we can’t add the WHERE clause on the outside (it actually tries,and produces a bad query). This decisionapparently goes way back to 0.6.5 with the note “may need to make moreadjustments to this”. Well, those adjustments have arrived! So now theabove query will render:

  1. SELECT
  2.     anon_1.widgets_id AS anon_1_widgets_id,
  3.     anon_1.widgets_type AS anon_1_widgets_type
  4. FROM (SELECT widgets.id AS widgets_id, widgets.type AS widgets_type,
  5. FROM widgets
  6. WHERE widgets.type IN (?)) AS anon_1

So that queries that don’t include “type” will still work!:

  1. sess.query(FooWidget.id).count()

Renders:

  1. SELECT count(*) AS count_1
  2. FROM (SELECT widgets.id AS widgets_id
  3. FROM widgets
  4. WHERE widgets.type IN (?)) AS anon_1

#3177

single-table-inheritance criteria added to all ON clauses unconditionally

When joining to a single-table inheritance subclass target, the ORM always addsthe “single table criteria” when joining on a relationship. Given amapping as:

  1. class Widget(Base):
  2.     __tablename__ = 'widget'
  3.     id = Column(Integer, primary_key=True)
  4.     type = Column(String)
  5.     related_id = Column(ForeignKey('related.id'))
  6.     related = relationship("Related", backref="widget")
  7.     __mapper_args__ = {'polymorphic_on': type}
  8.  
  9.  
  10. class FooWidget(Widget):
  11.     __mapper_args__ = {'polymorphic_identity': 'foo'}
  12.  
  13.  
  14. class Related(Base):
  15.     __tablename__ = 'related'
  16.     id = Column(Integer, primary_key=True)

It’s been the behavior for quite some time that a JOIN on the relationshipwill render a “single inheritance” clause for the type:

  1. s.query(Related).join(FooWidget, Related.widget).all()

SQL output:

  1. SELECT related.id AS related_id
  2. FROM related JOIN widget ON related.id = widget.related_id AND widget.type IN (:type_1)

Above, because we joined to a subclass FooWidget, Query.join()knew to add the AND widget.type IN ('foo') criteria to the ON clause.

The change here is that the AND widget.type IN() criteria is now appendedto any ON clause, not just those generated from a relationship,including one that is explicitly stated:

  1. # ON clause will now render as
  2. # related.id = widget.related_id AND widget.type IN (:type_1)
  3. s.query(Related).join(FooWidget, FooWidget.related_id == Related.id).all()

As well as the “implicit” join when no ON clause of any kind is stated:

  1. # ON clause will now render as
  2. # related.id = widget.related_id AND widget.type IN (:type_1)
  3. s.query(Related).join(FooWidget).all()

Previously, the ON clause for these would not include the single-inheritancecriteria. Applications that are already adding this criteria to work aroundthis will want to remove its explicit use, though it should continue to workfine if the criteria happens to be rendered twice in the meantime.

See also

Changes and fixes in handling of duplicate join targets

#3222

Key Behavioral Changes - Core

Warnings emitted when coercing full SQL fragments into text()

Since SQLAlchemy’s inception, there has always been an emphasis on not gettingin the way of the usage of plain text. The Core and ORM expression systemswere intended to allow any number of points at which the user can justuse plain text SQL expressions, not just in the sense that you can send afull SQL string to Connection.execute(), but that you can send stringswith SQL expressions into many functions, such as Select.where(),Query.filter(), and Select.order_by().

Note that by “SQL expressions” we mean a full fragment of a SQL string,such as:

  1. # the argument sent to where() is a full SQL expression
  2. stmt = select([sometable]).where("somecolumn = 'value'")

and we are not talking about string arguments, that is, the normalbehavior of passing string values that become parameterized:

  1. # This is a normal Core expression with a string argument -
  2. # we aren't talking about this!!
  3. stmt = select([sometable]).where(sometable.c.somecolumn == 'value')

The Core tutorial has long featured an example of the use of this technique,using a select() construct where virtually all components of itare specified as straight strings. However, despite this long-standingbehavior and example, users are apparently surprised that this behaviorexists, and when asking around the community, I was unable to find any userthat was in fact not surprised that you can send a full string into a methodlike Query.filter().

So the change here is to encourage the user to qualify textual strings whencomposing SQL that is partially or fully composed from textual fragments.When composing a select as below:

  1. stmt = select(["a", "b"]).where("a = b").select_from("sometable")

The statement is built up normally, with all the same coercions as before.However, one will see the following warnings emitted:

  1. SAWarning: Textual column expression 'a' should be explicitly declared
  2. with text('a'), or use column('a') for more specificity
  3. (this warning may be suppressed after 10 occurrences)
  4.  
  5. SAWarning: Textual column expression 'b' should be explicitly declared
  6. with text('b'), or use column('b') for more specificity
  7. (this warning may be suppressed after 10 occurrences)
  8.  
  9. SAWarning: Textual SQL expression 'a = b' should be explicitly declared
  10. as text('a = b') (this warning may be suppressed after 10 occurrences)
  11.  
  12. SAWarning: Textual SQL FROM expression 'sometable' should be explicitly
  13. declared as text('sometable'), or use table('sometable') for more
  14. specificity (this warning may be suppressed after 10 occurrences)

These warnings attempt to show exactly where the issue is by displayingthe parameters as well as where the string was received.The warnings make use of the Session.get_bind() handles a wider variety of inheritance scenarios so that parameterized warningscan be emitted safely without running out of memory, and as always, ifone wishes the warnings to be exceptions, thePython Warnings Filtershould be used:

  1. import warnings
  2. warnings.simplefilter("error")   # all warnings raise an exception

Given the above warnings, our statement works just fine, butto get rid of the warnings we would rewrite our statement as follows:

  1. from sqlalchemy import select, text
  2. stmt = select([
  3.         text("a"),
  4.         text("b")
  5.     ]).where(text("a = b")).select_from(text("sometable"))

and as the warnings suggest, we can give our statement more specificityabout the text if we use column() and table():

  1. from sqlalchemy import select, text, column, table
  2.  
  3. stmt = select([column("a"), column("b")]).\
  4.     where(text("a = b")).select_from(table("sometable"))

Where note also that table() and column() can nowbe imported from “sqlalchemy” without the “sql” part.

The behavior here applies to select() as well as to key methodson Query, including Query.filter(),Query.from_statement() and Query.having().

ORDER BY and GROUP BY are special cases

There is one case where usage of a string has special meaning, and as partof this change we have enhanced its functionality. When we have aselect() or Query that refers to some column name or namedlabel, we might want to GROUP BY and/or ORDER BY known columns or labels:

  1. stmt = select([
  2.     user.c.name,
  3.     func.count(user.c.id).label("id_count")
  4. ]).group_by("name").order_by("id_count")

In the above statement we expect to see “ORDER BY id_count”, as opposed to are-statement of the function. The string argument given is activelymatched to an entry in the columns clause during compilation, so the abovestatement would produce as we expect, without warnings (though note thatthe "name" expression has been resolved to users.name!):

  1. SELECT users.name, count(users.id) AS id_count
  2. FROM users GROUP BY users.name ORDER BY id_count

However, if we refer to a name that cannot be located, then we getthe warning again, as below:

  1. stmt = select([
  2.         user.c.name,
  3.         func.count(user.c.id).label("id_count")
  4.     ]).order_by("some_label")

The output does what we say, but again it warns us:

  1. SAWarning: Can't resolve label reference 'some_label'; converting to
  2. text() (this warning may be suppressed after 10 occurrences)
  3.  
  4. SELECT users.name, count(users.id) AS id_count
  5. FROM users ORDER BY some_label

The above behavior applies to all those places where we might want to referto a so-called “label reference”; ORDER BY and GROUP BY, but also within anOVER clause as well as a DISTINCT ON clause that refers to columns (e.g. thePostgreSQL syntax).

We can still specify any arbitrary expression for ORDER BY or others usingtext():

  1. stmt = select([users]).order_by(text("some special expression"))

The upshot of the whole change is that SQLAlchemy now would like usto tell it when a string is sent that this string is explicitlya text() construct, or a column, table, etc., and if we use it as alabel name in an order by, group by, or other expression, SQLAlchemy expectsthat the string resolves to something known, else it should againbe qualified with text() or similar.

#2992

Python-side defaults invoked for each row individually when using a multivalued insert

Support for Python-side column defaults when using the multi-valuedversion of Insert.values() were essentially not implemented, andwould only work “by accident” in specific situations, when the dialect inuse was using a non-positional (e.g. named) style of bound parameter, andwhen it was not necessary that a Python-side callable be invoked for eachrow.

The feature has been overhauled so that it works more similarly tothat of an “executemany” style of invocation:

  1. import itertools
  2.  
  3. counter = itertools.count(1)
  4. t = Table(
  5.     'my_table', metadata,
  6.     Column('id', Integer, default=lambda: next(counter)),
  7.     Column('data', String)
  8. )
  9.  
  10. conn.execute(t.insert().values([
  11.     {"data": "d1"},
  12.     {"data": "d2"},
  13.     {"data": "d3"},
  14. ]))

The above example will invoke next(counter) for each row individuallyas would be expected:

  1. INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)
  2. (1, 'd1', 2, 'd2', 3, 'd3')

Previously, a positional dialect would fail as a bind would not be generatedfor additional positions:

  1. Incorrect number of bindings supplied. The current statement uses 6,
  2. and there are 4 supplied.
  3. [SQL: u'INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)']
  4. [parameters: (1, 'd1', 'd2', 'd3')]

And with a “named” dialect, the same value for “id” would be re-used ineach row (hence this change is backwards-incompatible with a system thatrelied on this):

  1. INSERT INTO my_table (id, data) VALUES (:id, :data_0), (:id, :data_1), (:id, :data_2)
  2. {u'data_2': 'd3', u'data_1': 'd2', u'data_0': 'd1', 'id': 1}

The system will also refuse to invoke a “server side” default as inline-renderedSQL, since it cannot be guaranteed that a server side default is compatiblewith this. If the VALUES clause renders for a specific column, then a Python-sidevalue is required; if an omitted value only refers to a server-side default,an exception is raised:

  1. t = Table(
  2.     'my_table', metadata,
  3.     Column('id', Integer, primary_key=True),
  4.     Column('data', String, server_default='some default')
  5. )
  6.  
  7. conn.execute(t.insert().values([
  8.     {"data": "d1"},
  9.     {"data": "d2"},
  10.     {},
  11. ]))

will raise:

  1. sqlalchemy.exc.CompileError: INSERT value for column my_table.data is
  2. explicitly rendered as a boundparameter in the VALUES clause; a
  3. Python-side value or SQL expression is required

Previously, the value “d1” would be copied into that of the thirdrow (but again, only with named format!):

  1. INSERT INTO my_table (data) VALUES (:data_0), (:data_1), (:data_0)
  2. {u'data_1': 'd2', u'data_0': 'd1'}

#3288

Event listeners can not be added or removed from within that event’s runner

Removal of an event listener from inside that same event itself wouldmodify the elements of a list during iteration, which would causestill-attached event listeners to silently fail to fire. To preventthis while still maintaining performance, the lists have been replacedwith collections.deque(), which does not allow any additions orremovals during iteration, and instead raises RuntimeError.

#3163

The INSERT…FROM SELECT construct now implies inline=True

Using Insert.from_select() now implies inline=Trueon insert(). This helps to fix a bug where anINSERT…FROM SELECT construct would inadvertently be compiledas “implicit returning” on supporting backends, which wouldcause breakage in the case of an INSERT that inserts zero rows(as implicit returning expects a row), as well as arbitraryreturn data in the case of an INSERT that inserts multiplerows (e.g. only the first row of many).A similar change is also applied to an INSERT..VALUESwith multiple parameter sets; implicit RETURNING will no longer emitfor this statement either. As both of these constructs dealwith variable numbers of rows, theResultProxy.inserted_primary_key accessor does notapply. Previously, there was a documentation note that onemay prefer inline=True with INSERT..FROM SELECT as some databasesdon’t support returning and therefore can’t do “implicit” returning,but there’s no reason an INSERT…FROM SELECT needs implicit returningin any case. Regular explicit Insert.returning() shouldbe used to return variable numbers of result rows if inserteddata is needed.

#3169

autoload_with now implies autoload=True

A Table can be set up for reflection by passingTable.autoload_with alone:

  1. my_table = Table('my_table', metadata, autoload_with=some_engine)

#3027

DBAPI exception wrapping and handle_error() event improvements

SQLAlchemy’s wrapping of DBAPI exceptions was not taking place in thecase where a Connection object was invalidated, and then triedto reconnect and encountered an error; this has been resolved.

Additionally, the recently added ConnectionEvents.handle_error()event is now invoked for errors that occur upon initial connect, uponreconnect, and when create_engine() is used given a custom connectionfunction via create_engine.creator.

The ExceptionContext object has a new datamemberExceptionContext.engine that will always refer to the Enginein use, in those cases when the Connection object is not available(e.g. on initial connect).

#3266

ForeignKeyConstraint.columns is now a ColumnCollection

ForeignKeyConstraint.columns was previously a plain listcontaining either strings or Column objects, depending onhow the ForeignKeyConstraint was constructed and whether it wasassociated with a table. The collection is now a ColumnCollection,and is only initialized after the ForeignKeyConstraint isassociated with a Table. A new accessorForeignKeyConstraint.column_keysis added to unconditionally return string keys for the local set ofcolumns regardless of how the object was constructed or its currentstate.

MetaData.sorted_tables accessor is “deterministic”

The sorting of tables resulting from the MetaData.sorted_tablesaccessor is “deterministic”; the ordering should be the same in all casesregardless of Python hashing. This is done by first sorting the tablesby name before passing them to the topological algorithm, which maintainsthat ordering as it iterates.

Note that this change does not yet apply to the ordering appliedwhen emitting MetaData.create_all() or MetaData.drop_all().

#3084

null(), false() and true() constants are no longer singletons

These three constants were changed to return a “singleton” valuein 0.9; unfortunately, that would lead to a query like the followingto not render as expected:

  1. select([null(), null()])

rendering only SELECT NULL AS anon_1, because the two null()constructs would come out as the same NULL object, andSQLAlchemy’s Core model is based on object identity in order todetermine lexical significance. The change in 0.9 had noimportance other than the desire to save on object overhead; in general,an unnamed construct needs to stay lexically unique so that it getslabeled uniquely.

#3170

SQLite/Oracle have distinct methods for temporary table/view name reporting

The Inspector.get_table_names() and Inspector.get_view_names()methods in the case of SQLite/Oracle would also return the names of temporarytables and views, which is not provided by any other dialect (in the caseof MySQL at least it is not even possible). This logic has been movedout to two new methods Inspector.get_temp_table_names() andInspector.get_temp_view_names().

Note that reflection of a specific named temporary table or temporary view,either by Table('name', autoload=True) or via methods likeInspector.get_columns() continues to function for most if not alldialects. For SQLite specifically, there is a bug fix for UNIQUE constraintreflection from temp tables as well, which is #3203.

#3204

Dialect Improvements and Changes - PostgreSQL

Overhaul of ENUM type create/drop rules

The rules for PostgreSQL postgresql.ENUM have been made more strictwith regards to creating and dropping of the TYPE.

An postgresql.ENUM that is created without being explicitlyassociated with a MetaData object will be created and droppedcorresponding to Table.create() and Table.drop():

  1. table = Table('sometable', metadata,
  2.     Column('some_enum', ENUM('a', 'b', 'c', name='myenum'))
  3. )
  4.  
  5. table.create(engine)  # will emit CREATE TYPE and CREATE TABLE
  6. table.drop(engine)  # will emit DROP TABLE and DROP TYPE - new for 1.0

This means that if a second table also has an enum named ‘myenum’, theabove DROP operation will now fail. In order to accommodate the use caseof a common shared enumerated type, the behavior of a metadata-associatedenumeration has been enhanced.

An postgresql.ENUM that is created with being explicitlyassociated with a MetaData object will not be created or droppedcorresponding to Table.create() and Table.drop(), withthe exception of Table.create() called with the checkfirst=Trueflag:

  1. my_enum = ENUM('a', 'b', 'c', name='myenum', metadata=metadata)
  2.  
  3. table = Table('sometable', metadata,
  4.     Column('some_enum', my_enum)
  5. )
  6.  
  7. # will fail: ENUM 'my_enum' does not exist
  8. table.create(engine)
  9.  
  10. # will check for enum and emit CREATE TYPE
  11. table.create(engine, checkfirst=True)
  12.  
  13. table.drop(engine)  # will emit DROP TABLE, *not* DROP TYPE
  14.  
  15. metadata.drop_all(engine) # will emit DROP TYPE
  16.  
  17. metadata.create_all(engine) # will emit CREATE TYPE

#3319

New PostgreSQL Table options

Added support for PG table options TABLESPACE, ON COMMIT,WITH(OUT) OIDS, and INHERITS, when rendering DDL viathe Table construct.

See also

PostgreSQL Table Options

#2051

New get_enums() method with PostgreSQL Dialect

The inspect() method returns a PGInspector object in thecase of PostgreSQL, which includes a new PGInspector.get_enums()method that returns information on all available ENUM types:

  1. from sqlalchemy import inspect, create_engine
  2.  
  3. engine = create_engine("postgresql+psycopg2://host/dbname")
  4. insp = inspect(engine)
  5. print(insp.get_enums())

See also

PGInspector.get_enums()

PostgreSQL Dialect reflects Materialized Views, Foreign Tables

Changes are as follows:

  • the Table construct with autoload=True will now match a namethat exists in the database as a materialized view or foreign table.

  • Inspector.get_view_names() will return plain and materialized viewnames.

  • Inspector.get_table_names() does not change for PostgreSQL, itcontinues to return only the names of plain tables.

  • A new method PGInspector.get_foreign_table_names() is added whichwill return the names of tables that are specifically marked as “foreign”in the PostgreSQL schema tables.

The change to reflection involves adding 'm' and 'f' to the listof qualifiers we use when querying pg_class.relkind, but this changeis new in 1.0.0 to avoid any backwards-incompatible surprises for thoserunning 0.9 in production.

#2891

PostgreSQL has_table() now works for temporary tables

This is a simple fix such that “has table” for temporary tables now works,so that code like the following may proceed:

  1. from sqlalchemy import *
  2.  
  3. metadata = MetaData()
  4. user_tmp = Table(
  5.     "user_tmp", metadata,
  6.     Column("id", INT, primary_key=True),
  7.     Column('name', VARCHAR(50)),
  8.     prefixes=['TEMPORARY']
  9. )
  10.  
  11. e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
  12. with e.begin() as conn:
  13.     user_tmp.create(conn, checkfirst=True)
  14.  
  15.     # checkfirst will succeed
  16.     user_tmp.create(conn, checkfirst=True)

The very unlikely case that this behavior will cause a non-failing applicationto behave differently, is because PostgreSQL allows a non-temporary tableto silently overwrite a temporary table. So code like the following willnow act completely differently, no longer creating the real table followingthe temporary table:

  1. from sqlalchemy import *
  2.  
  3. metadata = MetaData()
  4. user_tmp = Table(
  5.     "user_tmp", metadata,
  6.     Column("id", INT, primary_key=True),
  7.     Column('name', VARCHAR(50)),
  8.     prefixes=['TEMPORARY']
  9. )
  10.  
  11. e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
  12. with e.begin() as conn:
  13.     user_tmp.create(conn, checkfirst=True)
  14.  
  15.     m2 = MetaData()
  16.     user = Table(
  17.         "user_tmp", m2,
  18.         Column("id", INT, primary_key=True),
  19.         Column('name', VARCHAR(50)),
  20.     )
  21.  
  22.     # in 0.9, *will create* the new table, overwriting the old one.
  23.     # in 1.0, *will not create* the new table
  24.     user.create(conn, checkfirst=True)

#3264

PostgreSQL FILTER keyword

The SQL standard FILTER keyword for aggregate functions is now supportedby PostgreSQL as of 9.4. SQLAlchemy allows this usingFunctionElement.filter():

  1. func.count(1).filter(True)

See also

FunctionElement.filter()

FunctionFilter

PG8000 dialect supports client side encoding

The create_engine.encoding parameter is now honoredby the pg8000 dialect, using on connect handler whichemits SET CLIENT_ENCODING matching the selected encoding.

PG8000 native JSONB support

Support for PG8000 versions greater than 1.10.1 has been added, whereJSONB is supported natively.

Support for psycopg2cffi Dialect on PyPy

Support for the pypy psycopg2cffi dialect is added.

See also

sqlalchemy.dialects.postgresql.psycopg2cffi

Dialect Improvements and Changes - MySQL

MySQL TIMESTAMP Type now renders NULL / NOT NULL in all cases

The MySQL dialect has always worked around MySQL’s implicit NOT NULLdefault associated with TIMESTAMP columns by emitting NULL forsuch a type, if the column is set up with nullable=True. However,MySQL 5.6.6 and above features a new flagexplicit_defaults_for_timestamp which repairs MySQL’s non-standardbehavior to make it behave like any other type; to accommodate this,SQLAlchemy now emits NULL/NOT NULL unconditionally for all TIMESTAMPcolumns.

See also

TIMESTAMP Columns and NULL

#3155

MySQL SET Type Overhauled to support empty sets, unicode, blank value handling

The mysql.SET type historically not included a system of handlingblank sets and empty values separately; as different drivers had differentbehaviors for treatment of empty strings and empty-string-set representations,the SET type tried only to hedge between these behaviors, opting to treat theempty set as set(['']) as is still the current behavior for theMySQL-Connector-Python DBAPI.Part of the rationale here was that it was otherwise impossible to actuallystore a blank string within a MySQL SET, as the driver gives us back stringswith no way to discern between set(['']) and set(). It was leftto the user to determine if set(['']) actually meant “empty set” or not.

The new behavior moves the use case for the blank string, which is an unusualcase that isn’t even documented in MySQL’s documentation, into a specialcase, and the default behavior of mysql.SET is now:

  • to treat the empty string '' as returned by MySQL-python into the emptyset set();

  • to convert the single-blank value set set(['']) returned byMySQL-Connector-Python into the empty set set();

  • To handle the case of a set type that actually wishes includes the blankvalue '' in its list of possible values,a new feature (required in this use case) is implemented whereby the setvalue is persisted and loaded as a bitwise integer value; theflag mysql.SET.retrieve_as_bitwise is added in order toenable this.

Using the mysql.SET.retrieve_as_bitwise flag allows the setto be persisted and retrieved with no ambiguity of values. Theoreticallythis flag can be turned on in all cases, as long as the given list ofvalues to the type matches the ordering exactly as declared in thedatabase; it only makes the SQL echo output a bit more unusual.

The default behavior of mysql.SET otherwise remains the same,roundtripping values using strings. The string-based behavior nowsupports unicode fully including MySQL-python with use_unicode=0.

#3283

MySQL internal “no such table” exceptions not passed to event handlers

The MySQL dialect will now disable ConnectionEvents.handle_error()events from firing for those statements which it uses internallyto detect if a table exists or not. This is achieved using anexecution option skip_user_error_events that disables the handleerror event for the scope of that execution. In this way, user codethat rewrites exceptions doesn’t need to worry about the MySQLdialect or other dialects that occasionally need to catchSQLAlchemy specific exceptions.

Changed the default value of raise_on_warnings for MySQL-Connector

Changed the default value of “raise_on_warnings” to False forMySQL-Connector. This was set at True for some reason. The “buffered”flag unfortunately must stay at True as MySQLconnector does not allowa cursor to be closed unless all results are fully fetched.

#2515

MySQL boolean symbols “true”, “false” work again

0.9’s overhaul of the IS/IS NOT operators as well as boolean types in#2682 disallowed the MySQL dialect from making use of the“true” and “false” symbols in the context of “IS” / “IS NOT”. Apparently,even though MySQL has no “boolean” type, it supports IS / IS NOT when thespecial “true” and “false” symbols are used, even though these are otherwisesynonymous with “1” and “0” (and IS/IS NOT don’t work with the numerics).

So the change here is that the MySQL dialect remains “non native boolean”,but the true() and false() symbols again produce thekeywords “true” and “false”, so that an expression like column.is_(true())again works on MySQL.

#3186

The match() operator now returns an agnostic MatchType compatible with MySQL’s floating point return value

The return type of a ColumnOperators.match() expression is now a new typecalled MatchType. This is a subclass of Boolean,that can be intercepted by the dialect in order to produce a differentresult type at SQL execution time.

Code like the following will now function correctly and return floating pointson MySQL:

  1. >>> connection.execute(
  2. ...    select([
  3. ...        matchtable.c.title.match('Agile Ruby Programming').label('ruby'),
  4. ...        matchtable.c.title.match('Dive Python').label('python'),
  5. ...        matchtable.c.title
  6. ...    ]).order_by(matchtable.c.id)
  7. ... )
  8. [
  9.     (2.0, 0.0, 'Agile Web Development with Ruby On Rails'),
  10.     (0.0, 2.0, 'Dive Into Python'),
  11.     (2.0, 0.0, "Programming Matz's Ruby"),
  12.     (0.0, 0.0, 'The Definitive Guide to Django'),
  13.     (0.0, 1.0, 'Python in a Nutshell')
  14. ]

#3263

Drizzle Dialect is now an External Dialect

The dialect for Drizzle is now an externaldialect, available at https://bitbucket.org/zzzeek/sqlalchemy-drizzle.This dialect was added to SQLAlchemy right before SQLAlchemy was able toaccommodate third party dialects well; going forward, all databases that aren’twithin the “ubiquitous use” category are third party dialects.The dialect’s implementation hasn’t changed and is still based on theMySQL + MySQLdb dialects within SQLAlchemy. The dialect is as of yetunreleased and in “attic” status; however it passes the majority of testsand is generally in decent working order, if someone wants to pick upon polishing it.

Dialect Improvements and Changes - SQLite

SQLite named and unnamed UNIQUE and FOREIGN KEY constraints will inspect and reflect

UNIQUE and FOREIGN KEY constraints are now fully reflected onSQLite both with and without names. Previously, foreign keynames were ignored and unnamed unique constraints were skipped. In particularthis will help with Alembic’s new SQLite migration features.

To achieve this, for both foreign keys and unique constraints, the resultof PRAGMA foreign_keys, index_list, and index_info is combined with regularexpression parsing of the CREATE TABLE statement overall to form a completepicture of the names of constraints, as well as differentiating UNIQUEconstraints that were created as UNIQUE vs. unnamed INDEXes.

#3244

#3261

Dialect Improvements and Changes - SQL Server

PyODBC driver name is required with hostname-based SQL Server connections

Connecting to SQL Server with PyODBC using a DSN-less connection, e.g.with an explicit hostname, now requires a driver name - SQLAlchemy will nolonger attempt to guess a default:

  1. engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")

SQLAlchemy’s previously hardcoded default of “SQL Server” is obsolete onWindows, and SQLAlchemy cannot be tasked with guessing the best driverbased on operation system/driver detection. Using a DSN is always preferredwhen using ODBC to avoid this issue entirely.

#3182

SQL Server 2012 large text / binary types render as VARCHAR, NVARCHAR, VARBINARY

The rendering of the Text, UnicodeText, and LargeBinarytypes has been changed for SQL Server 2012 and greater, with optionsto control the behavior completely, based on deprecation guidelines fromMicrosoft. See Large Text/Binary Type Deprecation for details.

Dialect Improvements and Changes - Oracle

Improved support for CTEs in Oracle

CTE support has been fixed up for Oracle, and there is also a new featureCTE.with_suffixes() that can assist with Oracle’s special directives:

  1. included_parts = select([
  2.     part.c.sub_part, part.c.part, part.c.quantity
  3. ]).where(part.c.part == "p1").\
  4.     cte(name="included_parts", recursive=True).\
  5.     suffix_with(
  6.         "search depth first by part set ord1",
  7.         "cycle part set y_cycle to 1 default 0", dialect='oracle')

#3220

New Oracle Keywords for DDL

Keywords such as COMPRESS, ON COMMIT, BITMAP:

Oracle Table Options

Oracle Specific Index Options