# SQLAlchemy 2.0 Documentation

# SQLAlchemy 2.0 Documentation

# 0.8 Changelog

## 0.8.7

Released: July 22, 2014

### orm

- Fixed bug in subquery eager loading where a long chain of
  eager loads across a polymorphic-subclass boundary in conjunction
  with polymorphic loading would fail to locate the subclass-link in the
  chain, erroring out with a missing property name on an
  [AliasedClass](https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.util.AliasedClass).
  References: [#3055](https://www.sqlalchemy.org/trac/ticket/3055)
- Fixed ORM bug where the [class_mapper()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.class_mapper) function would mask
  AttributeErrors or KeyErrors that should raise during mapper
  configuration due to user errors.  The catch for attribute/keyerror
  has been made more specific to not include the configuration step.
  References: [#3047](https://www.sqlalchemy.org/trac/ticket/3047)

### sql

- Fixed bug in [Enum](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Enum) and other [SchemaType](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.SchemaType)
  subclasses where direct association of the type with a
  [MetaData](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData) would lead to a hang when events
  (like create events) were emitted on the [MetaData](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData).
  References: [#3124](https://www.sqlalchemy.org/trac/ticket/3124)
- Fixed a bug within the custom operator plus [TypeEngine.with_variant()](https://docs.sqlalchemy.org/en/20/core/type_api.html#sqlalchemy.types.TypeEngine.with_variant)
  system, whereby using a [TypeDecorator](https://docs.sqlalchemy.org/en/20/core/custom_types.html#sqlalchemy.types.TypeDecorator) in conjunction with
  variant would fail with an MRO error when a comparison operator was used.
  References: [#3102](https://www.sqlalchemy.org/trac/ticket/3102)
- Fixed bug in INSERT..FROM SELECT construct where selecting from a
  UNION would wrap the union in an anonymous (e.g. unlabeled) subquery.
  References: [#3044](https://www.sqlalchemy.org/trac/ticket/3044)
- Fixed bug where [Table.update()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.update) and [Table.delete()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.delete)
  would produce an empty WHERE clause when an empty [and_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.and_)
  or [or_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.or_) or other blank expression were applied.  This is
  now consistent with that of [select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select).
  References: [#3045](https://www.sqlalchemy.org/trac/ticket/3045)

### postgresql

- Added the `hashable=False` flag to the PG [HSTORE](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.HSTORE) type, which
  is needed to allow the ORM to skip over trying to “hash” an ORM-mapped
  HSTORE column when requesting it in a mixed column/entity list.
  Patch courtesy Gunnlaugur Þór Briem.
  References: [#3053](https://www.sqlalchemy.org/trac/ticket/3053)
- Added a new “disconnect” message “connection has been closed unexpectedly”.
  This appears to be related to newer versions of SSL.
  Pull request courtesy Antti Haapala.

### mysql

- MySQL error 2014 “commands out of sync” appears to be raised as a
  ProgrammingError, not OperationalError, in modern MySQL-Python versions;
  all MySQL error codes that are tested for “is disconnect” are now
  checked within OperationalError and ProgrammingError regardless.
  References: [#3101](https://www.sqlalchemy.org/trac/ticket/3101)
- Fixed bug where column names added to `mysql_length` parameter
  on an index needed to have the same quoting for quoted names in
  order to be recognized.  The fix makes the quotes optional but
  also provides the old behavior for backwards compatibility with those
  using the workaround.
  References: [#3085](https://www.sqlalchemy.org/trac/ticket/3085)
- Added support for reflecting tables where an index includes
  KEY_BLOCK_SIZE using an equal sign.  Pull request courtesy
  Sean McGivern.

### mssql

- Added statement encoding to the “SET IDENTITY_INSERT”
  statements which operate when an explicit INSERT is being
  interjected into an IDENTITY column, to support non-ascii table
  identifiers on drivers such as pyodbc + unix + py2k that don’t
  support unicode statements.
- In the SQL Server pyodbc dialect, repaired the implementation
  for the `description_encoding` dialect parameter, which when
  not explicitly set was preventing  cursor.description from
  being parsed correctly in the case of result sets that
  contained names in alternate encodings.  This parameter
  shouldn’t be needed going forward.
  References: [#3091](https://www.sqlalchemy.org/trac/ticket/3091)

### misc

- The `__mapper_args__` dictionary is copied from a declarative
  mixin or abstract class when accessed, so that modifications made
  to this dictionary by declarative itself won’t conflict with that
  of other mappings.  The dictionary is modified regarding the
  `version_id_col` and `polymorphic_on` arguments, replacing the
  column within with the one that is officially mapped to the local
  class/table.
  References: [#3062](https://www.sqlalchemy.org/trac/ticket/3062)
- Fixed bug in mutable extension where [MutableDict](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableDict) did not
  report change events for the `setdefault()` dictionary operation.
  References: [#3051](https://www.sqlalchemy.org/trac/ticket/3051), [#3093](https://www.sqlalchemy.org/trac/ticket/3093)
- Fixed bug where [MutableDict.setdefault()](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableDict.setdefault) didn’t return the
  existing or new value (this bug was not released in any 0.8 version).
  Pull request courtesy Thomas Hervé.
  References: [#3051](https://www.sqlalchemy.org/trac/ticket/3051), [#3093](https://www.sqlalchemy.org/trac/ticket/3093)

## 0.8.6

Released: March 28, 2014

### general

- Adjusted `setup.py` file to support the possible future
  removal of the `setuptools.Feature` extension from setuptools.
  If this keyword isn’t present, the setup will still succeed
  with setuptools rather than falling back to distutils.  C extension
  building can be disabled now also by setting the
  DISABLE_SQLALCHEMY_CEXT environment variable.  This variable works
  whether or not setuptools is even available.
  References: [#2986](https://www.sqlalchemy.org/trac/ticket/2986)

### orm

- Fixed ORM bug where changing the primary key of an object, then marking
  it for DELETE would fail to target the correct row for DELETE.
  References: [#3006](https://www.sqlalchemy.org/trac/ticket/3006)
- Fixed regression from 0.8.3 as a result of [#2818](https://www.sqlalchemy.org/trac/ticket/2818)
  where [Query.exists()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.exists) wouldn’t work on a query that only
  had a [Query.select_from()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.select_from) entry but no other entities.
  References: [#2995](https://www.sqlalchemy.org/trac/ticket/2995)
- Improved an error message which would occur if a query() were made
  against a non-selectable, such as a [literal_column()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.literal_column), and then
  an attempt was made to use [Query.join()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.join) such that the “left”
  side would be determined as `None` and then fail.  This condition
  is now detected explicitly.
- Removed stale names from `sqlalchemy.orm.interfaces.__all__` and
  refreshed with current names, so that an `import *` from this
  module again works.
  References: [#2975](https://www.sqlalchemy.org/trac/ticket/2975)

### sql

- Fixed bug in [tuple_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.tuple_) construct where the “type” of essentially
  the first SQL expression would be applied as the “comparison type”
  to a compared tuple value; this has the effect in some cases of an
  inappropriate “type coercion” occurring, such as when a tuple that
  has a mix of String and Binary values improperly coerces target
  values to Binary even though that’s not what they are on the left
  side.  [tuple_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.tuple_) now expects heterogeneous types within its
  list of values.
  References: [#2977](https://www.sqlalchemy.org/trac/ticket/2977)

### postgresql

- Enabled “sane multi-row count” checking for the psycopg2 DBAPI, as
  this seems to be supported as of psycopg2 2.0.9.
- Fixed regression caused by release 0.8.5 / 0.9.3’s compatibility
  enhancements where index reflection on PostgreSQL versions specific
  to only the 8.1, 8.2 series again
  broke, surrounding the ever problematic int2vector type.  While
  int2vector supports array operations as of 8.1, apparently it only
  supports CAST to a varchar as of 8.3.
  References: [#3000](https://www.sqlalchemy.org/trac/ticket/3000)

### misc

- Fixed bug in mutable extension as well as
  [flag_modified()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.attributes.flag_modified) where the change event would not be
  propagated if the attribute had been reassigned to itself.
  References: [#2997](https://www.sqlalchemy.org/trac/ticket/2997)

## 0.8.5

Released: February 19, 2014

### orm

- Fixed bug where [Query.get()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.get) would fail to consistently
  raise the [InvalidRequestError](https://docs.sqlalchemy.org/en/20/core/exceptions.html#sqlalchemy.exc.InvalidRequestError) that invokes when called
  on a query with existing criterion, when the given identity is
  already present in the identity map.
  References: [#2951](https://www.sqlalchemy.org/trac/ticket/2951)
- Fixed error message when an iterator object is passed to
  [class_mapper()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.class_mapper) or similar, where the error would fail to
  render on string formatting.  Pullreq courtesy Kyle Stark.
- An adjustment to the [subqueryload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.subqueryload) strategy which ensures that
  the query runs after the loading process has begun; this is so that
  the subqueryload takes precedence over other loaders that may be
  hitting the same attribute due to other eager/noload situations
  at the wrong time.
  References: [#2887](https://www.sqlalchemy.org/trac/ticket/2887)
- Fixed bug when using joined table inheritance from a table to a
  select/alias on the base, where the PK columns were also not same
  named; the persistence system would fail to copy primary key values
  from the base table to the inherited table upon INSERT.
  References: [#2885](https://www.sqlalchemy.org/trac/ticket/2885)
- [composite()](https://docs.sqlalchemy.org/en/20/orm/composites.html#sqlalchemy.orm.composite) will raise an informative error message when the
  columns/attribute (names) passed don’t resolve to a Column or mapped
  attribute (such as an erroneous tuple); previously raised an unbound
  local.
  References: [#2889](https://www.sqlalchemy.org/trac/ticket/2889)

### engine

- Fixed a critical regression caused by [#2880](https://www.sqlalchemy.org/trac/ticket/2880) where the newly
  concurrent ability to return connections from the pool means that the
  “first_connect” event is now no longer synchronized either, thus leading
  to dialect mis-configurations under even minimal concurrency situations.
  References: [#2880](https://www.sqlalchemy.org/trac/ticket/2880), [#2964](https://www.sqlalchemy.org/trac/ticket/2964)

### sql

- Fixed bug where calling [Insert.values()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.Insert.values) with an empty list
  or tuple would raise an IndexError.   It now produces an empty
  insert construct as would be the case with an empty dictionary.
  References: [#2944](https://www.sqlalchemy.org/trac/ticket/2944)
- Fixed bug where [ColumnOperators.in_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.in_) would go into an endless
  loop if erroneously passed a column expression whose comparator
  included the `__getitem__()` method, such as a column that uses the
  [ARRAY](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY) type.
  References: [#2957](https://www.sqlalchemy.org/trac/ticket/2957)
- Fixed issue where a primary key column that has a Sequence on it,
  yet the column is not the “auto increment” column, either because
  it has a foreign key constraint or `autoincrement=False` set,
  would attempt to fire the Sequence on INSERT for backends that don’t
  support sequences, when presented with an INSERT missing the primary
  key value.  This would take place on non-sequence backends like
  SQLite, MySQL.
  References: [#2896](https://www.sqlalchemy.org/trac/ticket/2896)
- Fixed bug with [Insert.from_select()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.Insert.from_select) method where the order
  of the given names would not be taken into account when generating
  the INSERT statement, thus producing a mismatch versus the column
  names in the given SELECT statement.  Also noted that
  [Insert.from_select()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.Insert.from_select) implies that Python-side insert defaults
  cannot be used, since the statement has no VALUES clause.
  References: [#2895](https://www.sqlalchemy.org/trac/ticket/2895)
- The exception raised when a [BindParameter](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.BindParameter) is present
  in a compiled statement without a value now includes the key name
  of the bound parameter in the error message.

### postgresql

- Added an additional message to psycopg2 disconnect detection,
  “could not send data to server”, which complements the existing
  “could not receive data from server” and has been observed by users.
  References: [#2936](https://www.sqlalchemy.org/trac/ticket/2936)
- > Support has been improved for PostgreSQL reflection behavior on very old
  > (pre 8.1) versions of PostgreSQL, and potentially other PG engines
  > such as Redshift (assuming Redshift reports the version as < 8.1).
  > The query for “indexes” as well as “primary keys” relies upon inspecting
  > a so-called “int2vector” datatype, which refuses to coerce to an array
  > prior to 8.1 causing failures regarding the “ANY()” operator used
  > in the query.  Extensive googling has located the very hacky, but
  > recommended-by-PG-core-developer query to use when PG version < 8.1
  > is in use, so index and primary key constraint reflection now work
  > on these versions.
- Revised this very old issue where the PostgreSQL “get primary key”
  reflection query were updated to take into account primary key constraints
  that were renamed; the newer query fails on very old versions of
  PostgreSQL such as version 7, so the old query is restored in those cases
  when server_version_info < (8, 0) is detected.
  References: [#2291](https://www.sqlalchemy.org/trac/ticket/2291)

### mysql

- Added new MySQL-specific [DATETIME](https://docs.sqlalchemy.org/en/20/dialects/mysql.html#sqlalchemy.dialects.mysql.DATETIME) which includes
  fractional seconds support; also added fractional seconds support
  to [TIMESTAMP](https://docs.sqlalchemy.org/en/20/dialects/mysql.html#sqlalchemy.dialects.mysql.TIMESTAMP).  DBAPI support is limited, though
  fractional seconds are known to be supported by MySQL Connector/Python.
  Patch courtesy Geert JM Vanderkelen.
  References: [#2941](https://www.sqlalchemy.org/trac/ticket/2941)
- Added support for the `PARTITION BY` and `PARTITIONS`
  MySQL table keywords, specified as `mysql_partition_by='value'` and
  `mysql_partitions='value'` to [Table](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table).  Pull request
  courtesy Marcus McCurdy.
  References: [#2966](https://www.sqlalchemy.org/trac/ticket/2966)
- Fixed bug which prevented MySQLdb-based dialects (e.g.
  pymysql) from working in Py3K, where a check for “connection
  charset” would fail due to Py3K’s more strict value comparison
  rules.  The call in question  wasn’t taking the database
  version into account in any case as the server version was
  still None at that point, so the method overall has been
  simplified to rely upon connection.character_set_name().
  References: [#2933](https://www.sqlalchemy.org/trac/ticket/2933)
- Some missing methods added to the cymysql dialect, including
  _get_server_version_info() and _detect_charset().  Pullreq
  courtesy Hajime Nakagami.

### sqlite

- Restored a change that was missed in the backport of unique
  constraint reflection to 0.8, where [UniqueConstraint](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.UniqueConstraint)
  with SQLite would fail if reserved keywords were included in the
  names of columns.  Pull request courtesy Roman Podolyaka.

### mssql

- The “asdecimal” flag used with the [Float](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Float) type will now
  work with Firebird as well as the mssql+pyodbc dialects; previously the
  decimal conversion was not occurring.
- Added “Net-Lib error during Connection reset by peer” message
  to the list of messages checked for “disconnect” within the
  pymssql dialect.  Courtesy John Anderson.

### misc

- Fixed Py3K bug where a missing import would cause “literal binary”
  mode to fail to import “util.binary_type” when rendering a bound
  parameter.  0.9 handles this differently. Pull request courtesy
  Andreas Zeidler.
- The firebird dialect will quote identifiers which begin with an
  underscore.  Courtesy Treeve Jelbert.
  References: [#2897](https://www.sqlalchemy.org/trac/ticket/2897)
- Fixed bug in Firebird index reflection where the columns within the
  index were not sorted correctly; they are now sorted
  in order of RDB$FIELD_POSITION.
- Error message when a string arg sent to [relationship()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship) which
  doesn’t resolve to a class or mapper has been corrected to work
  the same way as when a non-string arg is received, which indicates
  the name of the relationship which had the configurational error.
  References: [#2888](https://www.sqlalchemy.org/trac/ticket/2888)

## 0.8.4

Released: December 8, 2013

### orm

- Fixed a regression introduced by [#2818](https://www.sqlalchemy.org/trac/ticket/2818) where the EXISTS
  query being generated would produce a “columns being replaced”
  warning for a statement with two same-named columns,
  as the internal SELECT wouldn’t have use_labels set.
  References: [#2818](https://www.sqlalchemy.org/trac/ticket/2818)

### engine

- A DBAPI that raises an error on `connect()` which is not a subclass
  of dbapi.Error (such as `TypeError`, `NotImplementedError`, etc.)
  will propagate the exception unchanged.  Previously,
  the error handling specific to the `connect()` routine would both
  inappropriately run the exception through the dialect’s
  [Dialect.is_disconnect()](https://docs.sqlalchemy.org/en/20/core/internals.html#sqlalchemy.engine.Dialect.is_disconnect) routine as well as wrap it in
  a [sqlalchemy.exc.DBAPIError](https://docs.sqlalchemy.org/en/20/core/exceptions.html#sqlalchemy.exc.DBAPIError).  It is now propagated unchanged
  in the same way as occurs within the execute process.
  References: [#2881](https://www.sqlalchemy.org/trac/ticket/2881)
- The [QueuePool](https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.QueuePool) has been enhanced to not block new connection
  attempts when an existing connection attempt is blocking.  Previously,
  the production of new connections was serialized within the block
  that monitored overflow; the overflow counter is now altered within
  its own critical section outside of the connection process itself.
  References: [#2880](https://www.sqlalchemy.org/trac/ticket/2880)
- Made a slight adjustment to the logic which waits for a pooled
  connection to be available, such that for a connection pool
  with no timeout specified, it will every half a second break out of
  the wait to check for the so-called “abort” flag, which allows the
  waiter to break out in case the whole connection pool was dumped;
  normally the waiter should break out due to a notify_all() but it’s
  possible this notify_all() is missed in very slim cases.
  This is an extension of logic first introduced in 0.8.0, and the
  issue has only been observed occasionally in stress tests.
  References: [#2522](https://www.sqlalchemy.org/trac/ticket/2522)
- Fixed bug where SQL statement would be improperly ASCII-encoded
  when a pre-DBAPI [StatementError](https://docs.sqlalchemy.org/en/20/core/exceptions.html#sqlalchemy.exc.StatementError) were raised within
  [Connection.execute()](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.execute), causing encoding errors for
  non-ASCII statements.  The stringification now remains within
  Python unicode thus avoiding encoding errors.
  References: [#2871](https://www.sqlalchemy.org/trac/ticket/2871)

### sql

- Added support for “unique constraint” reflection, via the
  [Inspector.get_unique_constraints()](https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_unique_constraints) method.
  Thanks for Roman Podolyaka for the patch.
  References: [#1443](https://www.sqlalchemy.org/trac/ticket/1443)

### postgresql

- Fixed bug where index reflection would mis-interpret indkey values
  when using the pypostgresql adapter, which returns these values
  as lists vs. psycopg2’s return type of string.
  References: [#2855](https://www.sqlalchemy.org/trac/ticket/2855)

### mssql

- Fixed bug introduced in 0.8.0 where the `DROP INDEX`
  statement for an index in MSSQL would render incorrectly if the
  index were in an alternate schema; the schemaname/tablename
  would be reversed.  The format has been also been revised to
  match current MSSQL documentation.  Courtesy Derek Harland.

### oracle

- Added ORA-02396 “maximum idle time” error code to list of
  “is disconnect” codes with cx_oracle.
  References: [#2864](https://www.sqlalchemy.org/trac/ticket/2864)
- Fixed bug where Oracle `VARCHAR` types given with no length
  (e.g. for a `CAST` or similar) would incorrectly render `None CHAR`
  or similar.
  References: [#2870](https://www.sqlalchemy.org/trac/ticket/2870)

### misc

- Fixed bug which prevented the `serializer` extension from working
  correctly with table or column names that contain non-ASCII
  characters.
  References: [#2869](https://www.sqlalchemy.org/trac/ticket/2869)

## 0.8.3

Released: October 26, 2013

### orm

- Added new option to [relationship()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship) `distinct_target_key`.
  This enables the subquery eager loader strategy to apply a DISTINCT
  to the innermost SELECT subquery, to assist in the case where
  duplicate rows are generated by the innermost query which corresponds
  to this relationship (there’s not yet a general solution to the issue
  of dupe rows within subquery eager loading, however, when joins outside
  of the innermost subquery produce dupes).  When the flag
  is set to `True`, the DISTINCT is rendered unconditionally, and when
  it is set to `None`, DISTINCT is rendered if the innermost relationship
  targets columns that do not comprise a full primary key.
  The option defaults to False in 0.8 (e.g. off by default in all cases),
  None in 0.9 (e.g. automatic by default).   Thanks to Alexander Koval
  for help with this.
  See also
  [Subquery Eager Loading will apply DISTINCT to the innermost SELECT for some queries](https://docs.sqlalchemy.org/en/20/changelog/migration_09.html#change-2836)
  References: [#2836](https://www.sqlalchemy.org/trac/ticket/2836)
- Fixed bug where list instrumentation would fail to represent a
  setslice of `[0:0]` correctly, which in particular could occur
  when using `insert(0, item)` with the association proxy.  Due
  to some quirk in Python collections, the issue was much more likely
  with Python 3 rather than 2.
  This change is also **backported** to: 0.7.11
  References: [#2807](https://www.sqlalchemy.org/trac/ticket/2807)
- Fixed bug where using an annotation such as [remote()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.remote) or
  [foreign()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.foreign) on a [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column) before association with a parent
  [Table](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table) could produce issues related to the parent table not
  rendering within joins, due to the inherent copy operation performed
  by an annotation.
  References: [#2813](https://www.sqlalchemy.org/trac/ticket/2813)
- Fixed bug where [Query.exists()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.exists) failed to work correctly
  without any WHERE criterion.  Courtesy Vladimir Magamedov.
  References: [#2818](https://www.sqlalchemy.org/trac/ticket/2818)
- Backported a change from 0.9 whereby the iteration of a hierarchy
  of mappers used in polymorphic inheritance loads is sorted,
  which allows the SELECT statements generated for polymorphic queries
  to have deterministic rendering, which in turn helps with caching
  schemes that cache on the SQL string itself.
  References: [#2779](https://www.sqlalchemy.org/trac/ticket/2779)
- Fixed a potential issue in an ordered sequence implementation used
  by the ORM to iterate mapper hierarchies; under the Jython interpreter
  this implementation wasn’t ordered, even though cPython and PyPy
  maintained ordering.
  References: [#2794](https://www.sqlalchemy.org/trac/ticket/2794)
- Fixed bug in ORM-level event registration where the “raw” or
  “propagate” flags could potentially be mis-configured in some
  “unmapped base class” configurations.
  References: [#2786](https://www.sqlalchemy.org/trac/ticket/2786)
- A performance fix related to the usage of the [defer()](https://docs.sqlalchemy.org/en/20/orm/queryguide/columns.html#sqlalchemy.orm.defer) option
  when loading mapped entities.   The function overhead of applying
  a per-object deferred callable to an instance at load time was
  significantly higher than that of just loading the data from the row
  (note that `defer()` is meant to reduce DB/network overhead, not
  necessarily function call count); the function call overhead is now
  less than that of loading data from the column in all cases.  There
  is also a reduction in the number of “lazy callable” objects created
  per load from N (total deferred values in the result) to 1 (total
  number of deferred cols).
  References: [#2778](https://www.sqlalchemy.org/trac/ticket/2778)
- Fixed bug whereby attribute history functions would fail
  when an object we moved from “persistent” to “pending”
  using the [make_transient()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.make_transient) function, for operations
  involving collection-based backrefs.
  References: [#2773](https://www.sqlalchemy.org/trac/ticket/2773)

### orm declarative

- Added a convenience class decorator [as_declarative()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.as_declarative), is
  a wrapper for [declarative_base()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.declarative_base) which allows an existing base
  class to be applied using a nifty class-decorated approach.

### examples

- Improved the examples in `examples/generic_associations`, including
  that `discriminator_on_association.py` makes use of single table
  inheritance do the work with the “discriminator”.  Also
  added a true “generic foreign key” example, which works similarly
  to other popular frameworks in that it uses an open-ended integer
  to point to any other table, foregoing traditional referential
  integrity.  While we don’t recommend this pattern, information wants
  to be free.
- Added “autoincrement=False” to the history table created in the
  versioning example, as this table shouldn’t have autoinc on it
  in any case, courtesy Patrick Schmid.

### engine

- `repr()` for the [URL](https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.engine.URL) of an [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine)
  will now conceal the password using asterisks.
  Courtesy Gunnlaugur Þór Briem.
  References: [#2821](https://www.sqlalchemy.org/trac/ticket/2821)
- The regexp used by the `make_url()` function now parses
  ipv6 addresses, e.g. surrounded by brackets.
  This change is also **backported** to: 0.7.11
  References: [#2851](https://www.sqlalchemy.org/trac/ticket/2851)
- Dialect.initialize() is not called a second time if an [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine)
  is recreated, due to a disconnect error.   This fixes a particular
  issue in the Oracle 8 dialect, but in general the dialect.initialize()
  phase should only be once per dialect.
  References: [#2776](https://www.sqlalchemy.org/trac/ticket/2776)
- Fixed bug where [QueuePool](https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.QueuePool) would lose the correct
  checked out count if an existing pooled connection failed to reconnect
  after an invalidate or recycle event.
  References: [#2772](https://www.sqlalchemy.org/trac/ticket/2772)

### sql

- Added new method to the [insert()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.insert) construct
  [Insert.from_select()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.Insert.from_select).  Given a list of columns and
  a selectable, renders `INSERT INTO (table) (columns) SELECT ..`.
  References: [#722](https://www.sqlalchemy.org/trac/ticket/722)
- The [update()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.update), [insert()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.insert), and [delete()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.delete) constructs
  will now interpret ORM entities as target tables to be operated upon,
  e.g.:
  ```
  from sqlalchemy import insert, update, delete
  ins = insert(SomeMappedClass).values(x=5)
  del_ = delete(SomeMappedClass).where(SomeMappedClass.id == 5)
  upd = update(SomeMappedClass).where(SomeMappedClass.id == 5).values(name="ed")
  ```
- Fixed regression dating back to 0.7.9 whereby the name of a CTE might
  not be properly quoted if it was referred to in multiple FROM clauses.
  This change is also **backported** to: 0.7.11
  References: [#2801](https://www.sqlalchemy.org/trac/ticket/2801)
- Fixed bug in common table expression system where if the CTE were
  used only as an `alias()` construct, it would not render using the
  WITH keyword.
  This change is also **backported** to: 0.7.11
  References: [#2783](https://www.sqlalchemy.org/trac/ticket/2783)
- Fixed bug in [CheckConstraint](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.CheckConstraint) DDL where the “quote” flag from a
  [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column) object would not be propagated.
  This change is also **backported** to: 0.7.11
  References: [#2784](https://www.sqlalchemy.org/trac/ticket/2784)
- Fixed bug where [type_coerce()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.type_coerce) would not interpret ORM
  elements with a `__clause_element__()` method properly.
  References: [#2849](https://www.sqlalchemy.org/trac/ticket/2849)
- The [Enum](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Enum) and [Boolean](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Boolean) types now bypass
  any custom (e.g. TypeDecorator) type in use when producing the
  CHECK constraint for the “non native” type.  This so that the custom type
  isn’t involved in the expression within the CHECK, since this
  expression is against the “impl” value and not the “decorated” value.
  References: [#2842](https://www.sqlalchemy.org/trac/ticket/2842)
- The `.unique` flag on [Index](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.Index) could be produced as `None`
  if it was generated from a [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column) that didn’t specify `unique`
  (where it defaults to `None`).  The flag will now always be `True` or
  `False`.
  References: [#2825](https://www.sqlalchemy.org/trac/ticket/2825)
- Fixed bug in default compiler plus those of postgresql, mysql, and
  mssql to ensure that any literal SQL expression values are
  rendered directly as literals, instead of as bound parameters,
  within a CREATE INDEX statement.  This also changes the rendering
  scheme for other DDL such as constraints.
  References: [#2742](https://www.sqlalchemy.org/trac/ticket/2742)
- A [select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select) that is made to refer to itself in its FROM clause,
  typically via in-place mutation, will raise an informative error
  message rather than causing a recursion overflow.
  References: [#2815](https://www.sqlalchemy.org/trac/ticket/2815)
- Non-working “schema” argument on [ForeignKey](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.ForeignKey) is deprecated;
  raises a warning.  Removed in 0.9.
  References: [#2831](https://www.sqlalchemy.org/trac/ticket/2831)
- Fixed bug where using the `column_reflect` event to change the `.key`
  of the incoming [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column) would prevent primary key constraints,
  indexes, and foreign key constraints from being correctly reflected.
  References: [#2811](https://www.sqlalchemy.org/trac/ticket/2811)
- The [ColumnOperators.notin_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.notin_) operator added in 0.8 now properly
  produces the negation of the expression “IN” returns
  when used against an empty collection.
- Fixed bug where the expression system relied upon the `str()`
  form of a some expressions when referring to the `.c` collection
  on a `select()` construct, but the `str()` form isn’t available
  since the element relies on dialect-specific compilation constructs,
  notably the `__getitem__()` operator as used with a PostgreSQL
  `ARRAY` element.  The fix also adds a new exception class
  [UnsupportedCompilationError](https://docs.sqlalchemy.org/en/20/core/exceptions.html#sqlalchemy.exc.UnsupportedCompilationError) which is raised in those cases
  where a compiler is asked to compile something it doesn’t know
  how to.
  References: [#2780](https://www.sqlalchemy.org/trac/ticket/2780)

### postgresql

- Removed a 128-character truncation from the reflection of the
  server default for a column; this code was original from
  PG system views which truncated the string for readability.
  References: [#2844](https://www.sqlalchemy.org/trac/ticket/2844)
- Parenthesis will be applied to a compound SQL expression as
  rendered in the column list of a CREATE INDEX statement.
  References: [#2742](https://www.sqlalchemy.org/trac/ticket/2742)
- Fixed bug where PostgreSQL version strings that had a prefix preceding
  the words “PostgreSQL” or “EnterpriseDB” would not parse.
  Courtesy Scott Schaefer.
  References: [#2819](https://www.sqlalchemy.org/trac/ticket/2819)

### mysql

- Updates to MySQL reserved words for versions 5.5, 5.6, courtesy
  Hanno Schlichting.
  This change is also **backported** to: 0.7.11
  References: [#2791](https://www.sqlalchemy.org/trac/ticket/2791)
- The change in [#2721](https://www.sqlalchemy.org/trac/ticket/2721), which is that the `deferrable` keyword
  of [ForeignKeyConstraint](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint) is silently ignored on the MySQL
  backend, will be reverted as of 0.9; this keyword will now render again, raising
  errors on MySQL as it is not understood - the same behavior will also
  apply to the `initially` keyword.  In 0.8, the keywords will remain
  ignored but a warning is emitted.   Additionally, the `match` keyword
  now raises a [CompileError](https://docs.sqlalchemy.org/en/20/core/exceptions.html#sqlalchemy.exc.CompileError) on 0.9 and emits a warning on 0.8;
  this keyword is not only silently ignored by MySQL but also breaks
  the ON UPDATE/ON DELETE options.
  To use a [ForeignKeyConstraint](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint)
  that does not render or renders differently on MySQL, use a custom
  compilation option.  An example of this usage has been added to the
  documentation, see [MySQL / MariaDB Foreign Keys](https://docs.sqlalchemy.org/en/20/dialects/mysql.html#mysql-foreign-keys).
  References: [#2721](https://www.sqlalchemy.org/trac/ticket/2721), [#2839](https://www.sqlalchemy.org/trac/ticket/2839)
- MySQL-connector dialect now allows options in the create_engine
  query string to override those defaults set up in the connect,
  including “buffered” and “raise_on_warnings”.
  References: [#2515](https://www.sqlalchemy.org/trac/ticket/2515)

### sqlite

- The newly added SQLite DATETIME arguments storage_format and
  regexp apparently were not fully implemented correctly; while the
  arguments were accepted, in practice they would have no effect;
  this has been fixed.
  References: [#2781](https://www.sqlalchemy.org/trac/ticket/2781)

### oracle

- Fixed bug where Oracle table reflection using synonyms would fail
  if the synonym and the table were in different remote schemas.
  Patch to fix courtesy Kyle Derr.
  References: [#2853](https://www.sqlalchemy.org/trac/ticket/2853)

### misc

- Added a new flag `system=True` to [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column), which marks
  the column as a “system” column which is automatically made present
  by the database (such as PostgreSQL `oid` or `xmin`).  The
  column will be omitted from the `CREATE TABLE` statement but will
  otherwise be available for querying.   In addition, the
  [CreateColumn](https://docs.sqlalchemy.org/en/20/core/ddl.html#sqlalchemy.schema.CreateColumn) construct can be applied to a custom
  compilation rule which allows skipping of columns, by producing
  a rule that returns `None`.

## 0.8.2

Released: July 3, 2013

### orm

- Added a new method `Query.select_entity_from()` which
  will in 0.9 replace part of the functionality of
  [Query.select_from()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.select_from).  In 0.8, the two methods perform
  the same function, so that code can be migrated to use the
  `Query.select_entity_from()` method as appropriate.
  See the 0.9 migration guide for details.
  References: [#2736](https://www.sqlalchemy.org/trac/ticket/2736)
- A warning is emitted when trying to flush an object of an inherited
  class where the polymorphic discriminator has been assigned
  to a value that is invalid for the class.
  References: [#2750](https://www.sqlalchemy.org/trac/ticket/2750)
- Fixed bug in polymorphic SQL generation where multiple joined-inheritance
  entities against the same base class joined to each other as well
  would not track columns on the base table independently of each other if
  the string of joins were more than two entities long.
  References: [#2759](https://www.sqlalchemy.org/trac/ticket/2759)
- Fixed bug where sending a composite attribute into [Query.order_by()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.order_by)
  would produce a parenthesized expression not accepted by some databases.
  References: [#2754](https://www.sqlalchemy.org/trac/ticket/2754)
- Fixed the interaction between composite attributes and
  the [aliased()](https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.aliased) function.  Previously, composite attributes
  wouldn’t work correctly in comparison operations when aliasing
  was applied.
  References: [#2755](https://www.sqlalchemy.org/trac/ticket/2755)
- Fixed bug where [MutableDict](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableDict) didn’t report a change event
  when `clear()` was called.
  References: [#2730](https://www.sqlalchemy.org/trac/ticket/2730)
- Fixed a regression caused by [#2682](https://www.sqlalchemy.org/trac/ticket/2682) whereby the
  evaluation invoked by [Query.update()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.update) and [Query.delete()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.delete)
  would hit upon unsupported `True` and `False` symbols
  which now appear due to the usage of `IS`.
  References: [#2737](https://www.sqlalchemy.org/trac/ticket/2737)
- Fixed a regression from 0.7 caused by this ticket, which
  made the check for recursion overflow in self-referential
  eager joining too loose, missing a particular circumstance
  where a subclass had lazy=”joined” or “subquery” configured
  and the load was a “with_polymorphic” against the base.
  References: [#2481](https://www.sqlalchemy.org/trac/ticket/2481)
- Fixed a regression from 0.7 where the contextmanager feature
  of [Session.begin_nested()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session.begin_nested) would fail to correctly
  roll back the transaction when a flush error occurred, instead
  raising its own exception while leaving the session still
  pending a rollback.
  References: [#2718](https://www.sqlalchemy.org/trac/ticket/2718)

### orm declarative

- ORM descriptors such as hybrid properties can now be referenced
  by name in a string argument used with `order_by`,
  `primaryjoin`, or similar in [relationship()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship),
  in addition to column-bound attributes.
  References: [#2761](https://www.sqlalchemy.org/trac/ticket/2761)

### examples

- Fixed an issue with the “versioning” recipe whereby a many-to-one
  reference could produce a meaningless version for the target,
  even though it was not changed, when backrefs were present.
  Patch courtesy Matt Chisholm.
- Fixed a small bug in the dogpile example where the generation
  of SQL cache keys wasn’t applying deduping labels to the
  statement the same way [Query](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query) normally does.

### engine

- Fixed bug where the `reset_on_return` argument to various [Pool](https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.Pool)
  implementations would not be propagated when the pool was regenerated.
  Courtesy Eevee.
- Fixed a bug where the routine to detect the correct kwargs
  being sent to [create_engine()](https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine) would fail in some cases,
  such as with the Sybase dialect.
  References: [#2732](https://www.sqlalchemy.org/trac/ticket/2732)

### sql

- Provided a new attribute for [TypeDecorator](https://docs.sqlalchemy.org/en/20/core/custom_types.html#sqlalchemy.types.TypeDecorator)
  called [TypeDecorator.coerce_to_is_types](https://docs.sqlalchemy.org/en/20/core/custom_types.html#sqlalchemy.types.TypeDecorator.coerce_to_is_types),
  to make it easier to control how comparisons using
  `==` or `!=` to `None` and boolean types goes
  about producing an `IS` expression, or a plain
  equality expression with a bound parameter.
  References: [#2734](https://www.sqlalchemy.org/trac/ticket/2734), [#2744](https://www.sqlalchemy.org/trac/ticket/2744)
- Multiple fixes to the correlation behavior of
  [Select](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select) constructs, first introduced in 0.8.0:
  - To satisfy the use case where FROM entries should be
    correlated outwards to a SELECT that encloses another,
    which then encloses this one, correlation now works
    across multiple levels when explicit correlation is
    established via [Select.correlate()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.correlate), provided
    that the target select is somewhere along the chain
    contained by a WHERE/ORDER BY/columns clause, not
    just nested FROM clauses. This makes
    [Select.correlate()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.correlate) act more compatibly to
    that of 0.7 again while still maintaining the new
    “smart” correlation.
  - When explicit correlation is not used, the usual
    “implicit” correlation limits its behavior to just
    the immediate enclosing SELECT, to maximize compatibility
    with 0.7 applications, and also prevents correlation
    across nested FROMs in this case, maintaining compatibility
    with 0.8.0/0.8.1.
  - The [Select.correlate_except()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.correlate_except) method was not
    preventing the given FROM clauses from correlation in
    all cases, and also would cause FROM clauses to be incorrectly
    omitted entirely (more like what 0.7 would do),
    this has been fixed.
  - Calling select.correlate_except(None) will enter
    all FROM clauses into correlation as would be expected.
  References: [#2668](https://www.sqlalchemy.org/trac/ticket/2668), [#2746](https://www.sqlalchemy.org/trac/ticket/2746)
- Fixed bug whereby joining a select() of a table “A” with multiple
  foreign key paths to a table “B”, to that table “B”, would fail
  to produce the “ambiguous join condition” error that would be
  reported if you join table “A” directly to “B”; it would instead
  produce a join condition with multiple criteria.
  References: [#2738](https://www.sqlalchemy.org/trac/ticket/2738)
- Fixed bug whereby using [MetaData.reflect()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.reflect) across a remote
  schema as well as a local schema could produce wrong results
  in the case where both schemas had a table of the same name.
  References: [#2728](https://www.sqlalchemy.org/trac/ticket/2728)
- Removed the “not implemented” `__iter__()` call from the base
  [ColumnOperators](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators) class, while this was introduced
  in 0.8.0 to prevent an endless, memory-growing loop when one also
  implements a `__getitem__()` method on a custom
  operator and then calls erroneously `list()` on that object,
  it had the effect of causing column elements to report that they
  were in fact iterable types which then throw an error when you try
  to iterate.   There’s no real way to have both sides here so we
  stick with Python best practices.  Careful with implementing
  `__getitem__()` on your custom operators!
  References: [#2726](https://www.sqlalchemy.org/trac/ticket/2726)
- Regression from this ticket caused the unsupported keyword
  “true” to render, added logic to convert this to 1/0
  for SQL server.
  References: [#2682](https://www.sqlalchemy.org/trac/ticket/2682)

### postgresql

- Support for PostgreSQL 9.2 range types has been added.
  Currently, no type translation is provided, so works
  directly with strings or psycopg2 2.5 range extension types
  at the moment.  Patch courtesy Chris Withers.
- Added support for “AUTOCOMMIT” isolation when using the psycopg2
  DBAPI.   The keyword is available via the `isolation_level`
  execution option.  Patch courtesy Roman Podolyaka.
  References: [#2072](https://www.sqlalchemy.org/trac/ticket/2072)
- The behavior of [extract()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.extract) has been simplified on the
  PostgreSQL dialect to no longer inject a hardcoded `::timestamp`
  or similar cast into the given expression, as this interfered
  with types such as timezone-aware datetimes, but also
  does not appear to be at all necessary with modern versions
  of psycopg2.
  References: [#2740](https://www.sqlalchemy.org/trac/ticket/2740)
- Fixed bug in HSTORE type where keys/values that contained
  backslashed quotes would not be escaped correctly when
  using the “non native” (i.e. non-psycopg2) means
  of translating HSTORE data.  Patch courtesy Ryan Kelly.
  References: [#2766](https://www.sqlalchemy.org/trac/ticket/2766)
- Fixed bug where the order of columns in a multi-column
  PostgreSQL index would be reflected in the wrong order.
  Courtesy Roman Podolyaka.
  References: [#2767](https://www.sqlalchemy.org/trac/ticket/2767)
- Fixed the HSTORE type to correctly encode/decode for unicode.
  This is always on, as the hstore is a textual type, and
  matches the behavior of psycopg2 when using Python 3.
  Courtesy Dmitry Mugtasimov.
  References: [#2735](https://www.sqlalchemy.org/trac/ticket/2735)

### mysql

- The `mysql_length` parameter used with [Index](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.Index) can now
  be passed as a dictionary of column names/lengths, for use
  with composite indexes.  Big thanks to Roman Podolyaka for the
  patch.
  References: [#2704](https://www.sqlalchemy.org/trac/ticket/2704)
- Fixed bug when using multi-table UPDATE where a supplemental
  table is a SELECT with its own bound parameters, where the positioning
  of the bound parameters would be reversed versus the statement
  itself when using MySQL’s special syntax.
  References: [#2768](https://www.sqlalchemy.org/trac/ticket/2768)
- Added another conditional to the `mysql+gaerdbms` dialect to
  detect so-called “development” mode, where we should use the
  `rdbms_mysqldb` DBAPI.  Patch courtesy Brett Slatkin.
  References: [#2715](https://www.sqlalchemy.org/trac/ticket/2715)
- The `deferrable` keyword argument on [ForeignKey](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.ForeignKey) and
  [ForeignKeyConstraint](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint) will not render the `DEFERRABLE` keyword
  on the MySQL dialect.  For a long time we left this in place because
  a non-deferrable foreign key would act very differently than a deferrable
  one, but some environments just disable FKs on MySQL, so we’ll be less
  opinionated here.
  References: [#2721](https://www.sqlalchemy.org/trac/ticket/2721)
- Updated mysqlconnector dialect to check for disconnect based
  on the apparent string message sent in the exception; tested
  against mysqlconnector 1.0.9.

### sqlite

- Added [sqlalchemy.types.BIGINT](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.BIGINT) to the list of type names that can be
  reflected by the SQLite dialect; courtesy Russell Stuart.
  References: [#2764](https://www.sqlalchemy.org/trac/ticket/2764)

### mssql

- When querying the information schema on SQL Server 2000, removed
  a CAST call that was added in 0.8.1 to help with driver issues,
  which apparently is not compatible on 2000.
  The CAST remains in place for SQL Server 2005 and greater.
  References: [#2747](https://www.sqlalchemy.org/trac/ticket/2747)

### misc

- Added new flag `retaining=True` to the kinterbasdb and fdb dialects.
  This controls the value of the `retaining` flag sent to the
  `commit()` and `rollback()` methods of the DBAPI connection.
  Due to historical concerns, this flag defaults to `True` in 0.8.2,
  however in 0.9.0b1 this flag defaults to `False`.
  References: [#2763](https://www.sqlalchemy.org/trac/ticket/2763)
- Type lookup when reflecting the Firebird types LONG and
  INT64 has been fixed so that LONG is treated as INTEGER,
  INT64 treated as BIGINT, unless the type has a “precision”
  in which case it’s treated as NUMERIC.  Patch courtesy
  Russell Stuart.
  References: [#2757](https://www.sqlalchemy.org/trac/ticket/2757)
- Fixed bug whereby if a composite type were set up
  with a function instead of a class, the mutable extension
  would trip up when it tried to check that column
  for being a [MutableComposite](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableComposite) (which it isn’t).
  Courtesy asldevi.
- The Python [mock](https://pypi.org/project/mock) library
  is now required in order to run the unit test suite.  While part
  of the standard library as of Python 3.3, previous Python installations
  will need to install this in order to run unit tests or to
  use the `sqlalchemy.testing` package for external dialects.

## 0.8.1

Released: April 27, 2013

### orm

- Added a convenience method to Query that turns a query into an
  EXISTS subquery of the form
  `EXISTS (SELECT 1 FROM ... WHERE ...)`.
  References: [#2673](https://www.sqlalchemy.org/trac/ticket/2673)
- Fixed bug when a query of the form:
  `query(SubClass).options(subqueryload(Baseclass.attrname))`,
  where `SubClass` is a joined inh of `BaseClass`,
  would fail to apply the `JOIN` inside the subquery
  on the attribute load, producing a cartesian product.
  The populated results still tended to be correct as additional
  rows are just ignored, so this issue may be present as a
  performance degradation in applications that are
  otherwise working correctly.
  This change is also **backported** to: 0.7.11
  References: [#2699](https://www.sqlalchemy.org/trac/ticket/2699)
- Fixed bug in unit of work whereby a joined-inheritance
  subclass could insert the row for the “sub” table
  before the parent table, if the two tables had no
  ForeignKey constraints set up between them.
  This change is also **backported** to: 0.7.11
  References: [#2689](https://www.sqlalchemy.org/trac/ticket/2689)
- Fixes to the `sqlalchemy.ext.serializer` extension, including
  that the “id” passed from the pickler is turned into a string
  to prevent against bytes being parsed on Py3K, as well as that
  `relationship()` and `orm.join()` constructs are now properly
  serialized.
  References: [#2698](https://www.sqlalchemy.org/trac/ticket/2698)
- A significant improvement to the inner workings of query.join(),
  such that the decisionmaking involved on how to join has been
  dramatically simplified.  New test cases now pass such as
  multiple joins extending from the middle of an already complex
  series of joins involving inheritance and such.   Joining from
  deeply nested subquery structures is still complicated and
  not without caveats, but with these improvements the edge
  cases are hopefully pushed even farther out to the edges.
  References: [#2714](https://www.sqlalchemy.org/trac/ticket/2714)
- Added a conditional to the unpickling process for ORM
  mapped objects, such that if the reference to the object
  were lost when the object was pickled, we don’t
  erroneously try to set up _sa_instance_state - fixes
  a NoneType error.
- Fixed bug where many-to-many relationship with uselist=False
  would fail to delete the association row and raise an error
  if the scalar attribute were set to None.  This was a
  regression introduced by the changes for [#2229](https://www.sqlalchemy.org/trac/ticket/2229).
  References: [#2710](https://www.sqlalchemy.org/trac/ticket/2710)
- Improved the behavior of instance management regarding
  the creation of strong references within the Session;
  an object will no longer have an internal reference cycle
  created if it’s in the transient state or moves into the
  detached state - the strong ref is created only when the
  object is attached to a Session and is removed when the
  object is detached.  This makes it somewhat safer for an
  object to have a __del__() method, even though this is
  not recommended, as relationships with backrefs produce
  cycles too.  A warning has been added when a class with
  a __del__() method is mapped.
  References: [#2708](https://www.sqlalchemy.org/trac/ticket/2708)
- Fixed bug whereby ORM would run the wrong kind of
  query when refreshing an inheritance-mapped class
  where the superclass was mapped to a non-Table
  object, like a custom join() or a select(),
  running a query that assumed a hierarchy that’s
  mapped to individual Table-per-class.
  References: [#2697](https://www.sqlalchemy.org/trac/ticket/2697)
- Fixed __repr__() on mapper property constructs
  to work before the object is initialized, so
  that Sphinx builds with recent Sphinx versions
  can read them.

### orm declarative

- Fixed indirect regression regarding [has_inherited_table()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.has_inherited_table),
  where since it considers the current class’ `__table__`, was
  sensitive to when it was called.  This is 0.7’s behavior also,
  but in 0.7 things tended to “work out” within events like
  `__mapper_args__()`.  [has_inherited_table()](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.has_inherited_table) now only
  considers superclasses, so should return the same answer
  regarding the current class no matter when it’s called
  (obviously assuming the state of the superclass).
  References: [#2656](https://www.sqlalchemy.org/trac/ticket/2656)

### examples

- Fixed a long-standing bug in the caching example, where
  the limit/offset parameter values wouldn’t be taken into
  account when computing the cache key.  The
  _key_from_query() function has been simplified to work
  directly from the final compiled statement in order to get
  at both the full statement as well as the fully processed
  parameter list.

### sql

- Loosened the check on dialect-specific argument names
  passed to Table(); since we want to support external dialects
  and also want to support args without a certain dialect
  being installed, it only checks the format of the arg now,
  rather than looking for that dialect in sqlalchemy.dialects.
- Fully implemented the IS and IS NOT operators with
  regards to the True/False constants.  An expression like
  `col.is_(True)` will now render `col IS true`
  on the target platform, rather than converting the True/
  False constant to an integer bound parameter.
  This allows the `is_()` operator to work on MySQL when
  given True/False constants.
  References: [#2682](https://www.sqlalchemy.org/trac/ticket/2682)
- A major fix to the way in which a select() object produces
  labeled columns when apply_labels() is used; this mode
  produces a SELECT where each column is labeled as in
  <tablename>_<columnname>, to remove column name collisions
  for a multiple table select.   The fix is that if two labels
  collide when combined with the table name, i.e.
  “foo.bar_id” and “foo_bar.id”, anonymous aliasing will be
  applied to one of the dupes.  This allows the ORM to handle
  both columns independently; previously, 0.7
  would in some cases silently emit a second SELECT for the
  column that was “duped”, and in 0.8 an ambiguous column error
  would be emitted.   The “keys” applied to the .c. collection
  of the select() will also be deduped, so that the “column
  being replaced” warning will no longer emit for any select()
  that specifies use_labels, though the dupe key will be given
  an anonymous label which isn’t generally user-friendly.
  References: [#2702](https://www.sqlalchemy.org/trac/ticket/2702)
- Fixed bug where disconnect detect on error would
  raise an attribute error if the error were being
  raised after the Connection object had already
  been closed.
  References: [#2691](https://www.sqlalchemy.org/trac/ticket/2691)
- Reworked internal exception raises that emit
  a rollback() before re-raising, so that the stack
  trace is preserved from sys.exc_info() before entering
  the rollback.  This so that the traceback is preserved
  when using coroutine frameworks which may have switched
  contexts before the rollback function returns.
  References: [#2703](https://www.sqlalchemy.org/trac/ticket/2703)
- The _Binary base type now converts values through
  the bytes() callable when run on Python 3; in particular
  psycopg2 2.5 with Python 3.3 seems to now be returning
  the “memoryview” type, so this is converted to bytes
  before return.
- Improvements to Connection auto-invalidation
  handling.  If a non-disconnect error occurs,
  but leads to a delayed disconnect error within error
  handling (happens with MySQL), the disconnect condition
  is detected.  The Connection can now also be closed
  when in an invalid state, meaning it will raise “closed”
  on next usage, and additionally the “close with result”
  feature will work even if the autorollback in an error
  handling routine fails and regardless of whether the
  condition is a disconnect or not.
  References: [#2695](https://www.sqlalchemy.org/trac/ticket/2695)
- Fixed bug whereby a DBAPI that can return “0”
  for cursor.lastrowid would not function correctly
  in conjunction with `ResultProxy.inserted_primary_key`.

### postgresql

- Opened up the checking for “disconnect” with psycopg2/libpq
  to check for all the various “disconnect” messages within
  the full exception hierarchy.  Specifically the
  “closed the connection unexpectedly” message has now been
  seen in at least three different exception types.
  Courtesy Eli Collins.
  References: [#2712](https://www.sqlalchemy.org/trac/ticket/2712)
- The operators for the PostgreSQL ARRAY type supports
  input types of sets, generators, etc. even when
  a dimension is not specified, by turning the given
  iterable into a collection unconditionally.
  References: [#2681](https://www.sqlalchemy.org/trac/ticket/2681)
- Added missing HSTORE type to postgresql type names
  so that the type can be reflected.
  References: [#2680](https://www.sqlalchemy.org/trac/ticket/2680)

### mysql

- Fixes to support the latest cymysql DBAPI, courtesy
  Hajime Nakagami.
- Improvements to the operation of the pymysql dialect on
  Python 3, including some important decode/bytes steps.
  Issues remain with BLOB types due to driver issues.
  Courtesy Ben Trofatter.
  References: [#2663](https://www.sqlalchemy.org/trac/ticket/2663)
- Updated a regexp to correctly extract error code on
  google app engine v1.7.5 and newer.  Courtesy
  Dan Ring.

### mssql

- Part of a longer series of fixes needed for pyodbc+
  mssql, a CAST to NVARCHAR(max) has been added to the bound
  parameter for the table name and schema name in all information schema
  queries to avoid the issue of comparing NVARCHAR to NTEXT,
  which seems to be rejected by the ODBC driver in some cases,
  such as FreeTDS (0.91 only?) plus unicode bound parameters being passed.
  The issue seems to be specific to the SQL Server information
  schema tables and the workaround is harmless for those cases
  where the problem doesn’t exist in the first place.
  References: [#2355](https://www.sqlalchemy.org/trac/ticket/2355)
- Added support for additional “disconnect” messages
  to the pymssql dialect.  Courtesy John Anderson.
- Fixed Py3K bug regarding “binary” types and
  pymssql.  Courtesy Marc Abramowitz.
  References: [#2683](https://www.sqlalchemy.org/trac/ticket/2683)

## 0.8.0

Released: March 9, 2013

Note

There are some new behavioral changes as of 0.8.0
not present in 0.8.0b2.  They are present in the
migration document as follows:

- [The consideration of a “pending” object as an “orphan” has been made more aggressive](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html#legacy-is-orphan-addition)
- [create_all() and drop_all() will now honor an empty list as such](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html#metadata-create-drop-tables)
- [Correlation is now always context-specific](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html#correlation-context-specific)

### orm

- A meaningful [QueryableAttribute.info](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.QueryableAttribute.info) attribute is
  added, which proxies down to the `.info` attribute on either
  the [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column) object if directly present, or
  the [MapperProperty](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.MapperProperty) otherwise.  The full behavior
  is documented and ensured by tests to remain stable.
  References: [#2675](https://www.sqlalchemy.org/trac/ticket/2675)
- Can set/change the “cascade” attribute on a [relationship()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship)
  construct after it’s been constructed already.  This is not
  a pattern for normal use but we like to change the setting
  for demonstration purposes in tutorials.
- Added new helper function [was_deleted()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.util.was_deleted), returns True
  if the given object was the subject of a [Session.delete()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session.delete)
  operation.
  References: [#2658](https://www.sqlalchemy.org/trac/ticket/2658)
- Extended the [Runtime Inspection API](https://docs.sqlalchemy.org/en/20/core/inspection.html) system so that all Python descriptors
  associated with the ORM or its extensions can be retrieved.
  This fulfills the common request of being able to inspect
  all [QueryableAttribute](https://docs.sqlalchemy.org/en/20/orm/internals.html#sqlalchemy.orm.QueryableAttribute) descriptors in addition to
  extension types such as [hybrid_property](https://docs.sqlalchemy.org/en/20/orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property) and
  [AssociationProxy](https://docs.sqlalchemy.org/en/20/orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy).  See [Mapper.all_orm_descriptors](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper.all_orm_descriptors).
- Improved checking for an existing backref name conflict during
  mapper configuration; will now test for name conflicts on
  superclasses and subclasses, in addition to the current mapper,
  as these conflicts break things just as much.  This is new for
  0.8, but see below for a warning that will also be triggered
  in 0.7.11.
  References: [#2674](https://www.sqlalchemy.org/trac/ticket/2674)
- Improved the error message emitted when a “backref loop” is detected,
  that is when an attribute event triggers a bidirectional
  assignment between two other attributes with no end.
  This condition can occur not just when an object of the wrong
  type is assigned, but also when an attribute is mis-configured
  to backref into an existing backref pair.  Also in 0.7.11.
  References: [#2674](https://www.sqlalchemy.org/trac/ticket/2674)
- A warning is emitted when a MapperProperty is assigned to a mapper
  that replaces an existing property, if the properties in question
  aren’t plain column-based properties.   Replacement of relationship
  properties is rarely (ever?) what is intended and usually refers to a
  mapper mis-configuration.   Also in 0.7.11.
  References: [#2674](https://www.sqlalchemy.org/trac/ticket/2674)
- A clear error message is emitted if an event handler
  attempts to emit SQL on a Session within the after_commit()
  handler, where there is not a viable transaction in progress.
  References: [#2662](https://www.sqlalchemy.org/trac/ticket/2662)
- Detection of a primary key change within the process
  of cascading a natural primary key update will succeed
  even if the key is composite and only some of the
  attributes have changed.
  References: [#2665](https://www.sqlalchemy.org/trac/ticket/2665)
- An object that’s deleted from a session will be de-associated with
  that session fully after the transaction is committed, that is
  the [object_session()](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.object_session) function will return None.
  References: [#2658](https://www.sqlalchemy.org/trac/ticket/2658)
- Fixed bug whereby [Query.yield_per()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.yield_per) would set the execution
  options incorrectly, thereby breaking subsequent usage of the
  [Query.execution_options()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.execution_options) method.  Courtesy Ryan Kelly.
  References: [#2661](https://www.sqlalchemy.org/trac/ticket/2661)
- Fixed the consideration of the `between()` operator
  so that it works correctly with the new relationship local/remote
  system.
  References: [#1768](https://www.sqlalchemy.org/trac/ticket/1768)
- the consideration of a pending object as
  an “orphan” has been modified to more closely match the
  behavior as that of persistent objects, which is that the object
  is expunged from the [Session](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session) as soon as it is
  de-associated from any of its orphan-enabled parents.  Previously,
  the pending object would be expunged only if de-associated
  from all of its orphan-enabled parents.  The new flag `legacy_is_orphan`
  is added to [Mapper](https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper) which re-establishes the
  legacy behavior.
  See the change note and example case at [The consideration of a “pending” object as an “orphan” has been made more aggressive](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html#legacy-is-orphan-addition)
  for a detailed discussion of this change.
  References: [#2655](https://www.sqlalchemy.org/trac/ticket/2655)
- Fixed the (most likely never used) “@collection.link” collection
  method, which fires off each time the collection is associated
  or de-associated with a mapped object - the decorator
  was not tested or functional.  The decorator method
  is now named `collection.linker()` though the name “link”
  remains for backwards compatibility.  Courtesy Luca Wehrstedt.
  References: [#2653](https://www.sqlalchemy.org/trac/ticket/2653)
- Made some fixes to the system of producing custom instrumented
  collections, mainly that the usage of the @collection decorators
  will now honor the __mro__ of the given class, applying the
  logic of the sub-most classes’ version of a particular collection
  method.   Previously, it wasn’t predictable when subclassing
  an existing instrumented class such as [MappedCollection](https://docs.sqlalchemy.org/en/20/orm/collection_api.html#sqlalchemy.orm.MappedCollection)
  whether or not custom methods would resolve correctly.
  References: [#2654](https://www.sqlalchemy.org/trac/ticket/2654)
- Fixed potential memory leak which could occur if an
  arbitrary number of [sessionmaker](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.sessionmaker) objects
  were created.   The anonymous subclass created by
  the sessionmaker, when dereferenced, would not be garbage
  collected due to remaining class-level references from the
  event package.  This issue also applies to any custom system
  that made use of ad-hoc subclasses in conjunction with
  an event dispatcher.  Also in 0.7.10.
  References: [#2650](https://www.sqlalchemy.org/trac/ticket/2650)
- [Query.merge_result()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.merge_result) can now load rows from an outer join
  where an entity may be `None` without throwing an error.
  Also in 0.7.10.
  References: [#2640](https://www.sqlalchemy.org/trac/ticket/2640)
- Fixes to the “dynamic” loader on [relationship()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship), includes
  that backrefs will work properly even when autoflush is disabled,
  history events are more accurate in scenarios where multiple add/remove
  of the same object occurs.
  References: [#2637](https://www.sqlalchemy.org/trac/ticket/2637)
- The undocumented (and hopefully unused) system of producing
  custom collections using an `__instrumentation__` datastructure
  associated with the collection has been removed, as this was a complex
  and untested feature which was also essentially redundant versus the
  decorator approach.   Other internal simplifications to the
  orm.collections module have been made as well.

### examples

- Fixed a regression in the examples/dogpile_caching example
  which was due to the change in [#2614](https://www.sqlalchemy.org/trac/ticket/2614).

### sql

- Added a new argument to [Enum](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Enum) and its base
  [SchemaType](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.SchemaType) `inherit_schema`.  When set to `True`,
  the type will set its `schema` attribute of that of the
  [Table](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table) to which it is associated.  This also occurs
  during a [Table.tometadata()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.tometadata) operation; the [SchemaType](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.SchemaType)
  is now copied in all cases when [Table.tometadata()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.tometadata) happens,
  and if `inherit_schema=True`, the type will take on the new
  schema name passed to the method.   The `schema` is important
  when used with the PostgreSQL backend, as the type results in
  a `CREATE TYPE` statement.
  References: [#2657](https://www.sqlalchemy.org/trac/ticket/2657)
- [Index](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.Index) now supports arbitrary SQL expressions and/or
  functions, in addition to straight columns.   Common modifiers
  include using `somecolumn.desc()` for a descending index and
  `func.lower(somecolumn)` for a case-insensitive index, depending on the
  capabilities of the target backend.
  References: [#695](https://www.sqlalchemy.org/trac/ticket/695)
- The behavior of SELECT correlation has been improved such that
  the [Select.correlate()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.correlate) and [Select.correlate_except()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.correlate_except)
  methods, as well as their ORM analogues, will still retain
  “auto-correlation” behavior in that the FROM clause is modified
  only if the output would be legal SQL; that is, the FROM clause
  is left intact if the correlated SELECT is not used in the context
  of an enclosing SELECT inside of the WHERE, columns, or HAVING clause.
  The two methods now only specify conditions to the default
  “auto correlation”, rather than absolute FROM lists.
  References: [#2668](https://www.sqlalchemy.org/trac/ticket/2668)
- Fixed a bug regarding column annotations which in particular
  could impact some usages of the new [remote()](https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.remote) and
  `local()` annotation functions, where annotations
  could be lost when the column were used in a subsequent
  expression.
  References: [#1768](https://www.sqlalchemy.org/trac/ticket/1768), [#2660](https://www.sqlalchemy.org/trac/ticket/2660)
- The [ColumnOperators.in_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.in_) operator will now coerce
  values of `None` to [null()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.null).
  References: [#2496](https://www.sqlalchemy.org/trac/ticket/2496)
- Fixed bug where [Table.tometadata()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.tometadata) would fail if a
  [Column](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column) had both a foreign key as well as an
  alternate “.key” name for the column.   Also in 0.7.10.
  References: [#2643](https://www.sqlalchemy.org/trac/ticket/2643)
- insert().returning() raises an informative CompileError if attempted
  to compile on a dialect that doesn’t support RETURNING.
  References: [#2629](https://www.sqlalchemy.org/trac/ticket/2629)
- Tweaked the “REQUIRED” symbol used by the compiler to identify
  INSERT/UPDATE bound parameters that need to be passed, so that
  it’s more easily identifiable when writing custom bind-handling
  code.
  References: [#2648](https://www.sqlalchemy.org/trac/ticket/2648)

### schema

- [MetaData.create_all()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.create_all) and [MetaData.drop_all()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.drop_all) will
  now accommodate an empty list as an instruction to not create/drop
  any items, rather than ignoring the collection.
  References: [#2664](https://www.sqlalchemy.org/trac/ticket/2664)

### postgresql

- Added support for PostgreSQL’s traditional SUBSTRING
  function syntax, renders as “SUBSTRING(x FROM y FOR z)”
  when regular `func.substring()` is used.
  Courtesy Gunnlaugur Þór Briem.
  This change is also **backported** to: 0.7.11
  References: [#2676](https://www.sqlalchemy.org/trac/ticket/2676)
- Added `Comparator.any()` and
  `Comparator.all()`
  methods, as well as standalone expression constructs.   Big thanks
  to Audrius Kažukauskas for the terrific work here.
- Fixed bug in [array()](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.array) construct whereby using it
  inside of an [insert()](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.insert) construct would produce an
  error regarding a parameter issue in the `self_group()` method.

### mysql

- New dialect for CyMySQL added, courtesy Hajime Nakagami.
- GAE dialect now accepts username/password arguments in the URL,
  courtesy Owen Nelson.
- Added a conditional import to the `gaerdbms` dialect which attempts
  to import rdbms_apiproxy vs. rdbms_googleapi to work
  on both dev and production platforms.  Also now honors the
  `instance` attribute.  Courtesy Sean Lynch.
  Also in 0.7.10.
  References: [#2649](https://www.sqlalchemy.org/trac/ticket/2649)
- GAE dialect won’t fail on None match if the error code can’t be extracted
  from the exception throw; courtesy Owen Nelson.

### mssql

- Added `mssql_include` and `mssql_clustered` options to
  [Index](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.Index), renders the `INCLUDE` and `CLUSTERED` keywords,
  respectively.  Courtesy Derek Harland.
- DDL for IDENTITY columns is now supported on
  non-primary key columns, by establishing a
  [Sequence](https://docs.sqlalchemy.org/en/20/core/defaults.html#sqlalchemy.schema.Sequence) construct on any
  integer column.  Courtesy Derek Harland.
  References: [#2644](https://www.sqlalchemy.org/trac/ticket/2644)
- Added a py3K conditional around unnecessary .decode()
  call in mssql information schema, fixes reflection
  in Py3K. Also in 0.7.10.
  References: [#2638](https://www.sqlalchemy.org/trac/ticket/2638)
- Fixed a regression whereby the “collation” parameter
  of the character types CHAR, NCHAR, etc. stopped working,
  as “collation” is now supported by the base string types.
  The TEXT, NCHAR, CHAR, VARCHAR types within the
  MSSQL dialect are now synonyms for the base types.

### oracle

- The cx_oracle dialect will no longer run the bind parameter names
  through `encode()`, as this is not valid on Python 3, and prevented
  statements from functioning correctly on Python 3.  We now
  encode only if `supports_unicode_binds` is False, which is not
  the case for cx_oracle when at least version 5 of cx_oracle is used.

### tests

- Fixed an import of “logging” in test_execute which was not
  working on some linux platforms.  Also in 0.7.11.
  References: [#2669](https://www.sqlalchemy.org/trac/ticket/2669)

## 0.8.0b2

Released: December 14, 2012

### orm

- Added `KeyedTuple._asdict()` and `KeyedTuple._fields`
  to the `KeyedTuple` class to provide some degree of compatibility
  with the Python standard library `collections.namedtuple()`.
  References: [#2601](https://www.sqlalchemy.org/trac/ticket/2601)
- Allow synonyms to be used when defining primary and secondary
  joins for relationships.
- The [Query.select_from()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.select_from) method can now be used with a
  [aliased()](https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.aliased) construct without it interfering with the entities
  being selected.   Basically, a statement like this:
  ```
  ua = aliased(User)
  session.query(User.name).select_from(ua).join(User, User.name > ua.name)
  ```
  Will maintain the columns clause of the SELECT as coming from the
  unaliased “user”, as specified; the select_from only takes place in the
  FROM clause:
  ```
  SELECT users.name AS users_name FROM users AS users_1
  JOIN users ON users.name < users_1.name
  ```
  Note that this behavior is in contrast
  to the original, older use case for [Query.select_from()](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.select_from), which is that
  of restating the mapped entity in terms of a different selectable:
  ```
  session.query(User.name).select_from(user_table.select().where(user_table.c.id > 5))
  ```
  Which produces:
  ```
  SELECT anon_1.name AS anon_1_name FROM (SELECT users.id AS id,
  users.name AS name FROM users WHERE users.id > :id_1) AS anon_1
  ```
  It was the “aliasing” behavior of the latter use case that was
  getting in the way of the former use case.   The method now
  specifically considers a SQL expression like
  [select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select) or [alias()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.alias)
  separately from a mapped entity like a [aliased()](https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.aliased)
  construct.
  References: [#2635](https://www.sqlalchemy.org/trac/ticket/2635)
- The [MutableComposite](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableComposite) type did not allow for the
  [MutableBase.coerce()](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableBase.coerce) method to be used, even though
  the code seemed to indicate this intent, so this now works
  and a brief example is added.  As a side-effect,
  the mechanics of this event handler have been changed so that
  new [MutableComposite](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableComposite) types no longer add per-type
  global event handlers.  Also in 0.7.10.
  References: [#2624](https://www.sqlalchemy.org/trac/ticket/2624)
- A second overhaul of aliasing/internal pathing mechanics
  now allows two subclasses to have different relationships
  of the same name, supported with subquery or joined eager
  loading on both simultaneously when a full polymorphic
  load is used.
  References: [#2614](https://www.sqlalchemy.org/trac/ticket/2614)
- Fixed bug whereby a multi-hop subqueryload within
  a particular with_polymorphic load would produce a KeyError.
  Takes advantage of the same internal pathing overhaul
  as [#2614](https://www.sqlalchemy.org/trac/ticket/2614).
  References: [#2617](https://www.sqlalchemy.org/trac/ticket/2617)
- Fixed regression where query.update() would produce
  an error if an object matched by the “fetch”
  synchronization strategy wasn’t locally present.
  Courtesy Scott Torborg.
  References: [#2602](https://www.sqlalchemy.org/trac/ticket/2602)

### orm extensions

- The [sqlalchemy.ext.mutable](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#module-sqlalchemy.ext.mutable) extension now includes the
  example [MutableDict](https://docs.sqlalchemy.org/en/20/orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableDict) class as part of the extension.

### engine

- The `Connection.connect()` and `Connection.contextual_connect()`
  methods now return a “branched” version so that the [Connection.close()](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.close)
  method can be called on the returned connection without affecting the
  original.   Allows symmetry when using [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine) and
  [Connection](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection) objects as context managers:
  ```
  with conn.connect() as c:  # leaves the Connection open
      c.execute("...")
  with engine.connect() as c:  # closes the Connection
      c.execute("...")
  ```
- Fixed [MetaData.reflect()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.reflect) to correctly use
  the given [Connection](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection), if given, without
  opening a second connection from that connection’s
  [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine).
  This change is also **backported** to: 0.7.10
  References: [#2604](https://www.sqlalchemy.org/trac/ticket/2604)
- The “reflect=True” argument to [MetaData](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData) is deprecated.
  Please use the [MetaData.reflect()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.MetaData.reflect) method.

### sql

- The [Insert](https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql.expression.Insert) construct now supports multi-valued inserts,
  that is, an INSERT that renders like
  “INSERT INTO table VALUES (…), (…), …”.
  Supported by PostgreSQL, SQLite, and MySQL.
  Big thanks to Idan Kamara for doing the legwork on this one.
  See also
  [Multiple-VALUES support for Insert](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html#feature-2623)
  References: [#2623](https://www.sqlalchemy.org/trac/ticket/2623)
- Fixed bug where using server_onupdate=<FetchedValue|DefaultClause>
  without passing the “for_update=True” flag would apply the default
  object to the server_default, blowing away whatever was there.
  The explicit for_update=True argument shouldn’t be needed with this usage
  (especially since the documentation shows an example without it being
  used) so it is now arranged internally using a copy of the given default
  object, if the flag isn’t set to what corresponds to that argument.
  This change is also **backported** to: 0.7.10
  References: [#2631](https://www.sqlalchemy.org/trac/ticket/2631)
- Fixed a regression caused by [#2410](https://www.sqlalchemy.org/trac/ticket/2410) whereby a
  [CheckConstraint](https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.CheckConstraint) would apply itself back to the
  original table during a [Table.tometadata()](https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.tometadata) operation, as
  it would parse the SQL expression for a parent table. The
  operation now copies the given expression to correspond to the
  new table.
  References: [#2633](https://www.sqlalchemy.org/trac/ticket/2633)
- Fixed bug whereby using a label_length on dialect that was smaller
  than the size of actual column identifiers would fail to render
  the columns correctly in a SELECT statement.
  References: [#2610](https://www.sqlalchemy.org/trac/ticket/2610)
- The [DECIMAL](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.DECIMAL) type now honors the “precision” and
  “scale” arguments when rendering DDL.
  References: [#2618](https://www.sqlalchemy.org/trac/ticket/2618)
- Made an adjustment to the “boolean”, (i.e. `__nonzero__`)
  evaluation of binary expressions, i.e. `x1 == x2`, such
  that the “auto-grouping” applied by [BinaryExpression](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.BinaryExpression)
  in some cases won’t get in the way of this comparison.
  Previously, an expression like:
  ```
  expr1 = mycolumn > 2
  bool(expr1 == expr1)
  ```
  Would evaluate as `False`, even though this is an identity
  comparison, because `mycolumn > 2` would be “grouped” before
  being placed into the [BinaryExpression](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.BinaryExpression), thus changing
  its identity.   [BinaryExpression](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.BinaryExpression) now keeps track
  of the “original” objects passed in.
  Additionally the `__nonzero__` method now only returns if
  the operator is `==` or `!=` - all others raise `TypeError`.
  References: [#2621](https://www.sqlalchemy.org/trac/ticket/2621)
- Fixed a gotcha where inadvertently calling list() on a
  [ColumnElement](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement) would go into an endless loop, if
  [ColumnOperators.__getitem__()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.__getitem__) were implemented.
  A new NotImplementedError is emitted via `__iter__()`.
- Fixed bug in type_coerce() whereby typing information
  could be lost if the statement were used as a subquery
  inside of another statement, as well as other similar
  situations.  Among other things, would cause
  typing information to be lost when the Oracle/mssql dialects
  would apply limit/offset wrappings.
  References: [#2603](https://www.sqlalchemy.org/trac/ticket/2603)
- Fixed bug whereby the “.key” of a Column wasn’t being
  used when producing a “proxy” of the column against
  a selectable.   This probably didn’t occur in 0.7
  since 0.7 doesn’t respect the “.key” in a wider
  range of scenarios.
  References: [#2597](https://www.sqlalchemy.org/trac/ticket/2597)

### postgresql

- [HSTORE](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.HSTORE) is now available in the PostgreSQL dialect.
  Will also use psycopg2’s extensions if available.  Courtesy
  Audrius Kažukauskas.
  References: [#2606](https://www.sqlalchemy.org/trac/ticket/2606)

### sqlite

- More adjustment to this SQLite related issue which was released in
  0.7.9, to intercept legacy SQLite quoting characters when reflecting
  foreign keys.  In addition to intercepting double quotes, other
  quoting characters such as brackets, backticks, and single quotes
  are now also intercepted.
  This change is also **backported** to: 0.7.10
  References: [#2568](https://www.sqlalchemy.org/trac/ticket/2568)

### mssql

- Support for reflection of the “name” of primary key
  constraints added, courtesy Dave Moore.
  References: [#2600](https://www.sqlalchemy.org/trac/ticket/2600)
- Fixed bug whereby using “key” with Column
  in conjunction with “schema” for the owning
  Table would fail to locate result rows due
  to the MSSQL dialect’s “schema rendering”
  logic’s failure to take .key into account.
  This change is also **backported** to: 0.7.10

### oracle

- Fixed table reflection for Oracle when accessing a synonym that refers
  to a DBLINK remote database; while the syntax has been present in the
  Oracle dialect for some time, up until now it has never been tested.
  The syntax has been tested against a sample database linking to itself,
  however there’s still some uncertainty as to what should be used for the
  “owner” when querying the remote database for table information.
  Currently, the value of “username” from user_db_links is used to
  match the “owner”.
  References: [#2619](https://www.sqlalchemy.org/trac/ticket/2619)
- The Oracle LONG type, while an unbounded text type, does not appear
  to use the cx_Oracle.LOB type when result rows are returned,
  so the dialect has been repaired to exclude LONG from
  having cx_Oracle.LOB filtering applied.  Also in 0.7.10.
  References: [#2620](https://www.sqlalchemy.org/trac/ticket/2620)
- Repaired the usage of `.prepare()` in conjunction with
  cx_Oracle so that a return value of `False` will result
  in no call to `connection.commit()`, hence avoiding
  “no transaction” errors.   Two-phase transactions have
  now been shown to work in a rudimental fashion with
  SQLAlchemy and cx_oracle, however are subject to caveats
  observed with the driver; check the documentation
  for details.  Also in 0.7.10.
  References: [#2611](https://www.sqlalchemy.org/trac/ticket/2611)

### misc

- Reflection support has been added to the Sybase dialect.
  Big thanks to Ben Trofatter for all the work developing and
  testing this.
  References: [#1753](https://www.sqlalchemy.org/trac/ticket/1753)
- The [Pool](https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.Pool) will now log all connection.close()
  operations equally, including closes which occur for
  invalidated connections, detached connections, and connections
  beyond the pool capacity.
- The [Pool](https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.Pool) now consults the [Dialect](https://docs.sqlalchemy.org/en/20/core/internals.html#sqlalchemy.engine.Dialect) for
  functionality regarding how the connection should be
  “auto rolled back”, as well as closed.   This grants more
  control of transaction scope to the dialect, so that we
  will be better able to implement transactional workarounds
  like those potentially needed for pysqlite and cx_oracle.
  References: [#2611](https://www.sqlalchemy.org/trac/ticket/2611)
- Added new [PoolEvents.reset()](https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.PoolEvents.reset) hook to capture
  the event before a connection is auto-rolled back, upon
  return to the pool.   Together with
  [ConnectionEvents.rollback()](https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.rollback) this allows all rollback
  events to be intercepted.
- Added missing import for “fdb” to the experimental
  “firebird+fdb” dialect.
  References: [#2622](https://www.sqlalchemy.org/trac/ticket/2622)
- Some cruft regarding informix transaction handling has been
  removed, including a feature that would skip calling
  commit()/rollback() as well as some hardcoded isolation level
  assumptions on begin()..   The status of this dialect is not
  well understood as we don’t have any users working with it,
  nor any access to an Informix database.   If someone with
  access to Informix wants to help test this dialect, please
  let us know.

## 0.8.0b1

Released: October 30, 2012

### general

- The “sqlalchemy.exceptions”
  synonym for “sqlalchemy.exc” is removed
  fully.
  References: [#2433](https://www.sqlalchemy.org/trac/ticket/2433)
- SQLAlchemy 0.8 now targets Python 2.5 and
  above.  Python 2.4 is no longer supported.

### orm

- Major rewrite of relationship()
  internals now allow join conditions which
  include columns pointing to themselves
  within composite foreign keys.   A new
  API for very specialized primaryjoin conditions
  is added, allowing conditions based on
  SQL functions, CAST, etc. to be handled
  by placing the annotation functions
  remote() and foreign() inline within the
  expression when necessary.  Previous recipes
  using the semi-private _local_remote_pairs
  approach can be upgraded to this new
  approach.
  See also
  [Rewritten _orm.relationship() mechanics](https://docs.sqlalchemy.org/en/20/changelog/migration_08.html#feature-relationship-08)
  References: [#1401](https://www.sqlalchemy.org/trac/ticket/1401)
- New standalone function with_polymorphic()
  provides the functionality of query.with_polymorphic()
  in a standalone form.   It can be applied to any
  entity within a query, including as the target
  of a join in place of the “of_type()” modifier.
  References: [#2333](https://www.sqlalchemy.org/trac/ticket/2333)
- The of_type() construct on attributes
  now accepts aliased() class constructs as well
  as with_polymorphic constructs, and works with
  query.join(), any(), has(), and also
  eager loaders subqueryload(), joinedload(),
  contains_eager()
  References: [#1106](https://www.sqlalchemy.org/trac/ticket/1106), [#2438](https://www.sqlalchemy.org/trac/ticket/2438)
- Improvements to event listening for
  mapped classes allows that unmapped classes
  can be specified for instance- and mapper-events.
  The established events will be automatically
  set up on subclasses of that class when the
  propagate=True flag is passed, and the
  events will be set up for that class itself
  if and when it is ultimately mapped.
  References: [#2585](https://www.sqlalchemy.org/trac/ticket/2585)
- The “deferred declarative
  reflection” system has been moved into the
  declarative extension itself, using the
  new DeferredReflection class.  This
  class is now tested with both single
  and joined table inheritance use cases.
  References: [#2485](https://www.sqlalchemy.org/trac/ticket/2485)
- Added new core function “inspect()”,
  which serves as a generic gateway to
  introspection into mappers, objects,
  others.   The Mapper and InstanceState
  objects have been enhanced with a public
  API that allows inspection of mapped
  attributes, including filters for column-bound
  or relationship-bound properties, inspection
  of current object state, history of
  attributes, etc.
  References: [#2208](https://www.sqlalchemy.org/trac/ticket/2208)
- Calling rollback() within a
  session.begin_nested() will now only expire
  those objects that had net changes within the
  scope of that transaction, that is objects which
  were dirty or were modified on a flush.  This
  allows the typical use case for begin_nested(),
  that of altering a small subset of objects, to
  leave in place the data from the larger enclosing
  set of objects that weren’t modified in
  that sub-transaction.
  References: [#2452](https://www.sqlalchemy.org/trac/ticket/2452)
- Added utility feature
  Session.enable_relationship_loading(),
  supersedes relationship.load_on_pending.
  Both features should be avoided, however.
  References: [#2372](https://www.sqlalchemy.org/trac/ticket/2372)
- Added support for .info dictionary argument to
  column_property(), relationship(), composite().
  All MapperProperty classes have an auto-creating .info
  dict available overall.
- Adding/removing None from a mapped collection
  now generates attribute events.  Previously, a None
  append would be ignored in some cases.  Related
  to.
  References: [#2229](https://www.sqlalchemy.org/trac/ticket/2229)
- The presence of None in a mapped collection
  now raises an error during flush.   Previously,
  None values in collections would be silently ignored.
  References: [#2229](https://www.sqlalchemy.org/trac/ticket/2229)
- The Query.update() method is now
  more lenient as to the table
  being updated.  Plain Table objects are better
  supported now, and additional a joined-inheritance
  subclass may be used with update(); the subclass
  table will be the target of the update,
  and if the parent table is referenced in the
  WHERE clause, the compiler will call upon
  UPDATE..FROM syntax as allowed by the dialect
  to satisfy the WHERE clause.  MySQL’s multi-table
  update feature is also supported if columns
  are specified by object in the “values” dictionary.
  PG’s DELETE..USING is also not available
  in Core yet.
- New session events after_transaction_create
  and after_transaction_end
  allows tracking of new SessionTransaction objects.
  If the object is inspected, can be used to determine
  when a session first becomes active and when
  it deactivates.
- The Query can now load entity/scalar-mixed
  “tuple” rows that contain
  types which aren’t hashable, by setting the flag
  “hashable=False” on the corresponding TypeEngine object
  in use.  Custom types that return unhashable types
  (typically lists) can set this flag to False.
  References: [#2592](https://www.sqlalchemy.org/trac/ticket/2592)
- Query now “auto correlates” by
  default in the same way as select() does.
  Previously, a Query used as a subquery
  in another would require the correlate()
  method be called explicitly in order to
  correlate a table on the inside to the
  outside.  As always, correlate(None)
  disables correlation.
  References: [#2179](https://www.sqlalchemy.org/trac/ticket/2179)
- The after_attach event is now
  emitted after the object is established
  in Session.new or Session.identity_map
  upon Session.add(), Session.merge(),
  etc., so that the object is represented
  in these collections when the event
  is called.  Added before_attach
  event to accommodate use cases that
  need autoflush w pre-attached object.
  References: [#2464](https://www.sqlalchemy.org/trac/ticket/2464)
- The Session will produce warnings
  when unsupported methods are used inside the
  “execute” portion of the flush.   These are
  the familiar methods add(), delete(), etc.
  as well as collection and related-object
  manipulations, as called within mapper-level
  flush events
  like after_insert(), after_update(), etc.
  It’s been prominently documented for a long
  time that  SQLAlchemy cannot guarantee
  results when the Session is manipulated within
  the execution of the flush plan,
  however users are still doing it, so now
  there’s a warning.   Maybe someday the Session
  will be enhanced to support these operations
  inside of the flush, but for now, results
  can’t be guaranteed.
- ORM entities can be passed
  to the core select() construct as well
  as to the select_from(),
  correlate(), and correlate_except()
  methods of select(), where they will be unwrapped
  into selectables.
  References: [#2245](https://www.sqlalchemy.org/trac/ticket/2245)
- Some support for auto-rendering of a
  relationship join condition based on the mapped
  attribute, with usage of core SQL constructs.
  E.g. select([SomeClass]).where(SomeClass.somerelationship)
  would render SELECT from “someclass” and use the
  primaryjoin of “somerelationship” as the WHERE
  clause.   This changes the previous meaning
  of “SomeClass.somerelationship” when used in a
  core SQL context; previously, it would “resolve”
  to the parent selectable, which wasn’t generally
  useful.  Also works with query.filter().
  Related to.
  References: [#2245](https://www.sqlalchemy.org/trac/ticket/2245)
- The registry of classes
  in declarative_base() is now a
  WeakValueDictionary.  So subclasses of
  “Base” that are dereferenced will be
  garbage collected, *if they are not
  referred to by any other mappers/superclass
  mappers*. See the next note for this ticket.
  References: [#2526](https://www.sqlalchemy.org/trac/ticket/2526)
- Conflicts between columns on
  single-inheritance declarative subclasses,
  with or without using a mixin, can be resolved
  using a new @declared_attr usage described
  in the documentation.
  References: [#2472](https://www.sqlalchemy.org/trac/ticket/2472)
- declared_attr can now be used
  on non-mixin classes, even though this is generally
  only useful for single-inheritance subclass
  column conflict resolution.
  References: [#2472](https://www.sqlalchemy.org/trac/ticket/2472)
- declared_attr can now be used with
  attributes that are not Column or MapperProperty;
  including any user-defined value as well
  as association proxy objects.
  References: [#2517](https://www.sqlalchemy.org/trac/ticket/2517)
- *Very limited* support for
  inheriting mappers to be GC’ed when the
  class itself is deferenced.  The mapper
  must not have its own table (i.e.
  single table inh only) without polymorphic
  attributes in place.
  This allows for the use case of
  creating a temporary subclass of a declarative
  mapped class, with no table or mapping
  directives of its own, to be garbage collected
  when dereferenced by a unit test.
  References: [#2526](https://www.sqlalchemy.org/trac/ticket/2526)
- Declarative now maintains a registry
  of classes by string name as well as by full
  module-qualified name.   Multiple classes with the
  same name can now be looked up based on a module-qualified
  string within relationship().   Simple class name
  lookups where more than one class shares the same
  name now raises an informative error message.
  References: [#2338](https://www.sqlalchemy.org/trac/ticket/2338)
- Can now provide class-bound attributes
  that override columns which are of any
  non-ORM type, not just descriptors.
  References: [#2535](https://www.sqlalchemy.org/trac/ticket/2535)
- Added with_labels and
  reduce_columns keyword arguments to
  Query.subquery(), to provide two alternate
  strategies for producing queries with uniquely-
  named columns. .
  References: [#1729](https://www.sqlalchemy.org/trac/ticket/1729)
- A warning is emitted when a reference
  to an instrumented collection is no longer
  associated with the parent class due to
  expiration/attribute refresh/collection
  replacement, but an append
  or remove operation is received on the
  now-detached collection.
  References: [#2476](https://www.sqlalchemy.org/trac/ticket/2476)
- ORM will perform extra effort to determine
  that an FK dependency between two tables is
  not significant during flush if the tables
  are related via joined inheritance and the FK
  dependency is not part of the inherit_condition,
  saves the user a use_alter directive.
  References: [#2527](https://www.sqlalchemy.org/trac/ticket/2527)
- The instrumentation events class_instrument(),
  class_uninstrument(), and attribute_instrument()
  will now fire off only for descendant classes
  of the class assigned to listen().  Previously,
  an event listener would be assigned to listen
  for all classes in all cases regardless of the
  “target” argument passed.
  References: [#2590](https://www.sqlalchemy.org/trac/ticket/2590)
- with_polymorphic() produces JOINs
  in the correct order and with correct inheriting
  tables in the case of sending multi-level
  subclasses in an arbitrary order or with
  intermediary classes missing.
  References: [#1900](https://www.sqlalchemy.org/trac/ticket/1900)
- Improvements to joined/subquery eager
  loading dealing with chains of subclass entities
  sharing a common base, with no specific “join depth”
  provided.  Will chain out to
  each subclass mapper individually before detecting
  a “cycle”, rather than considering the base class
  to be the source of the “cycle”.
  References: [#2481](https://www.sqlalchemy.org/trac/ticket/2481)
- The “passive” flag on Session.is_modified()
  no longer has any effect. is_modified() in
  all cases looks only at local in-memory
  modified flags and will not emit any
  SQL or invoke loader callables/initializers.
  References: [#2320](https://www.sqlalchemy.org/trac/ticket/2320)
- The warning emitted when using
  delete-orphan cascade with one-to-many
  or many-to-many without single-parent=True
  is now an error.  The ORM
  would fail to function subsequent to this
  warning in any case.
  References: [#2405](https://www.sqlalchemy.org/trac/ticket/2405)
- Lazy loads emitted within flush events
  such as before_flush(), before_update(),
  etc. will now function as they would
  within non-event code, regarding consideration
  of the PK/FK values used in the lazy-emitted
  query.   Previously,
  special flags would be established that
  would cause lazy loads to load related items
  based on the “previous” value of the
  parent PK/FK values specifically when called
  upon within a flush; the signal to load
  in this way is now localized to where the
  unit of work actually needs to load that
  way.  Note that the UOW does
  sometimes load these collections before
  the before_update() event is called,
  so the usage of “passive_updates” or not
  can affect whether or not a collection will
  represent the “old” or “new” data, when
  accessed within a flush event, based
  on when the lazy load was emitted.
  The change is backwards incompatible in
  the exceedingly small chance that
  user event code depended on the old
  behavior.
  References: [#2350](https://www.sqlalchemy.org/trac/ticket/2350)
- Continuing regarding extra
  state post-flush due to event listeners;
  any states that are marked as “dirty” from an
  attribute perspective, usually via column-attribute
  set events within after_insert(), after_update(),
  etc., will get the “history” flag reset
  in all cases, instead of only those instances
  that were part of the flush.  This has the effect
  that this “dirty” state doesn’t carry over
  after the flush and won’t result in UPDATE
  statements.   A warning is emitted to this
  effect; the set_committed_state()
  method can be used to assign attributes on objects
  without producing history events.
  References: [#2566](https://www.sqlalchemy.org/trac/ticket/2566), [#2582](https://www.sqlalchemy.org/trac/ticket/2582)
- Fixed a disconnect that slowly evolved
  between a @declared_attr Column and a
  directly-defined Column on a mixin. In both
  cases, the Column will be applied to the
  declared class’ table, but not to that of a
  joined inheritance subclass.   Previously,
  the directly-defined Column would be placed
  on both the base and the sub table, which isn’t
  typically what’s desired.
  References: [#2565](https://www.sqlalchemy.org/trac/ticket/2565)
- Declarative can now propagate a column
  declared on a single-table inheritance subclass
  up to the parent class’ table, when the parent
  class is itself mapped to a join() or select()
  statement, directly or via joined inheritance,
  and not just a Table.
  References: [#2549](https://www.sqlalchemy.org/trac/ticket/2549)
- An error is emitted when uselist=False
  is combined with a “dynamic” loader.
  This is a warning in 0.7.9.
- The legacy “mutable” system of the
  ORM, including the MutableType class as well
  as the mutable=True flag on PickleType
  and postgresql.ARRAY has been removed.
  In-place mutations are detected by the ORM
  using the sqlalchemy.ext.mutable extension,
  introduced in 0.7.   The removal of MutableType
  and associated constructs removes a great
  deal of complexity from SQLAlchemy’s internals.
  The approach performed poorly as it would incur
  a scan of the full contents of the Session
  when in use.
  References: [#2442](https://www.sqlalchemy.org/trac/ticket/2442)
- Deprecated identifiers removed:
  - allow_null_pks mapper() argument
    (use allow_partial_pks)
  - _get_col_to_prop() mapper method
    (use get_property_by_column())
  - dont_load argument to Session.merge()
    (use load=True)
  - sqlalchemy.orm.shard module
    (use sqlalchemy.ext.horizontal_shard)
- The InstrumentationManager interface
  and the entire related system of alternate
  class implementation is now moved out
  to sqlalchemy.ext.instrumentation.   This is
  a seldom used system that adds significant
  complexity and overhead to the mechanics of
  class instrumentation.  The new architecture
  allows it to remain unused until
  InstrumentationManager is actually imported,
  at which point it is bootstrapped into
  the core.

### examples

- The Beaker caching example has been converted
  to use [dogpile.cache](https://dogpilecache.readthedocs.io/).
  This is a new caching library written by the same
  creator of Beaker’s caching internals, and represents a
  vastly improved, simplified, and modernized system of caching.
  See also
  [Dogpile Caching](https://docs.sqlalchemy.org/en/20/orm/examples.html#examples-caching)
  References: [#2589](https://www.sqlalchemy.org/trac/ticket/2589)

### engine

- Connection event listeners can
  now be associated with individual
  Connection objects, not just Engine
  objects.
  References: [#2511](https://www.sqlalchemy.org/trac/ticket/2511)
- The before_cursor_execute event
  fires off for so-called “_cursor_execute”
  events, which are usually special-case
  executions of primary-key bound sequences
  and default-generation SQL
  phrases that invoke separately when RETURNING
  is not used with INSERT.
  References: [#2459](https://www.sqlalchemy.org/trac/ticket/2459)
- The libraries used by the test suite
  have been moved around a bit so that they are
  part of the SQLAlchemy install again.  In addition,
  a new suite of tests is present in the
  new sqlalchemy.testing.suite package.  This is
  an under-development system that hopes to provide
  a universal testing suite for external dialects.
  Dialects which are maintained outside of SQLAlchemy
  can use the new test fixture as the framework
  for their own tests, and will get for free a
  “compliance” suite of dialect-focused tests,
  including an improved “requirements” system
  where specific capabilities and features can
  be enabled or disabled for testing.
- Added a new system
  for registration of new dialects in-process
  without using an entrypoint.  See the
  docs for “Registering New Dialects”.
  References: [#2462](https://www.sqlalchemy.org/trac/ticket/2462)
- The “required” flag is set to
  True by default, if not passed explicitly,
  on bindparam() if the “value” or “callable”
  parameters are not passed.
  This will cause statement execution to check
  for the parameter being present in the final
  collection of bound parameters, rather than
  implicitly assigning None.
  References: [#2556](https://www.sqlalchemy.org/trac/ticket/2556)
- Various API tweaks to the “dialect”
  API to better support highly specialized
  systems such as the Akiban database, including
  more hooks to allow an execution context to
  access type processors.
- Inspector.get_primary_keys() is
  deprecated; use Inspector.get_pk_constraint().
  Courtesy Diana Clarke.
  References: [#2422](https://www.sqlalchemy.org/trac/ticket/2422)
- New C extension module “utils” has
  been added for additional function speedups
  as we have time to implement.
- The Inspector.get_table_names()
  order_by=”foreign_key” feature now sorts
  tables by dependee first, to be consistent
  with util.sort_tables and metadata.sorted_tables.
- Fixed bug whereby if a database restart
  affected multiple connections, each
  connection would individually invoke a new
  disposal of the pool, even though only
  one disposal is needed.
  References: [#2522](https://www.sqlalchemy.org/trac/ticket/2522)
- The names of the columns on the
  .c. attribute of a select().apply_labels()
  is now based on <tablename>_<colkey> instead
  of <tablename>_<colname>, for those columns
  that have a distinctly named .key.
  References: [#2397](https://www.sqlalchemy.org/trac/ticket/2397)
- The autoload_replace flag on Table,
  when False, will cause any reflected foreign key
  constraints which refer to already-declared
  columns to be skipped, assuming that the
  in-Python declared column will take over
  the task of specifying in-Python ForeignKey
  or ForeignKeyConstraint declarations.
- The ResultProxy methods inserted_primary_key,
  last_updated_params(), last_inserted_params(),
  postfetch_cols(), prefetch_cols() all
  assert that the given statement is a compiled
  construct, and is an insert() or update()
  statement as is appropriate, else
  raise InvalidRequestError.
  References: [#2498](https://www.sqlalchemy.org/trac/ticket/2498)
- ResultProxy.last_inserted_ids is removed,
  replaced by inserted_primary_key.

### sql

- Added a new method [Engine.execution_options()](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine.execution_options)
  to [Engine](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine).  This method works similarly to
  [Connection.execution_options()](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.execution_options) in that it creates
  a copy of the parent object which will refer to the new
  set of options.   The method can be used to build
  sharding schemes where each engine shares the same
  underlying pool of connections.   The method
  has been tested against the horizontal shard
  recipe in the ORM as well.
  See also
  [Engine.execution_options()](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine.execution_options)
- Major rework of operator system
  in Core, to allow redefinition of existing
  operators as well as addition of new operators
  at the type level.  New types can be created
  from existing ones which add or redefine
  operations that are exported out to column
  expressions, in a similar manner to how the
  ORM has allowed comparator_factory.   The new
  architecture moves this capability into the
  Core so that it is consistently usable in
  all cases, propagating cleanly using existing
  type propagation behavior.
  References: [#2547](https://www.sqlalchemy.org/trac/ticket/2547)
- To complement, types
  can now provide “bind expressions” and
  “column expressions” which allow compile-time
  injection of SQL expressions into statements
  on a per-column or per-bind level.   This is
  to suit the use case of a type which needs
  to augment bind- and result- behavior at the
  SQL level, as opposed to in the Python level.
  Allows for schemes like transparent encryption/
  decryption, usage of PostGIS functions, etc.
  References: [#1534](https://www.sqlalchemy.org/trac/ticket/1534), [#2547](https://www.sqlalchemy.org/trac/ticket/2547)
- The Core operator system now includes
  the getitem operator, i.e. the bracket
  operator in Python.  This is used at first
  to provide index and slice behavior to the
  PostgreSQL ARRAY type, and also provides a hook
  for end-user definition of custom __getitem__
  schemes which can be applied at the type
  level as well as within ORM-level custom
  operator schemes.   lshift (<<)
  and rshift (>>) are also supported as
  optional operators.
  Note that this change has the effect that
  descriptor-based __getitem__ schemes used by
  the ORM in conjunction with synonym() or other
  “descriptor-wrapped” schemes will need
  to start using a custom comparator in order
  to maintain this behavior.
- Revised the rules used to determine
  the operator precedence for the user-defined
  operator, i.e. that granted using the `op()`
  method.   Previously, the smallest precedence
  was applied in all cases, now the default
  precedence is zero, lower than all operators
  except “comma” (such as, used in the argument
  list of a `func` call) and “AS”, and is
  also customizable via the “precedence” argument
  on the `op()` method.
  References: [#2537](https://www.sqlalchemy.org/trac/ticket/2537)
- Added “collation” parameter to all
  String types.  When present, renders as
  COLLATE <collation>.  This to support the
  COLLATE keyword now supported by several
  databases including MySQL, SQLite, and PostgreSQL.
  References: [#2276](https://www.sqlalchemy.org/trac/ticket/2276)
- Custom unary operators can now be
  used by combining operators.custom_op() with
  UnaryExpression().
- Enhanced GenericFunction and func.*
  to allow for user-defined GenericFunction
  subclasses to be available via the func.*
  namespace automatically by classname,
  optionally using a package name, as well
  as with the ability to have the rendered
  name different from the identified name
  in func.*.
- The cast() and extract() constructs
  will now be produced via the func.* accessor
  as well, as users naturally try to access these
  names from func.* they might as well do
  what’s expected, even though the returned
  object is not a FunctionElement.
  References: [#2562](https://www.sqlalchemy.org/trac/ticket/2562)
- The Inspector object can now be
  acquired using the new inspect() service,
  part of
  References: [#2208](https://www.sqlalchemy.org/trac/ticket/2208)
- The column_reflect event now
  accepts the Inspector object as the first
  argument, preceding “table”.   Code which
  uses the 0.7 version of this very new
  event will need modification to add the
  “inspector” object as the first argument.
  References: [#2418](https://www.sqlalchemy.org/trac/ticket/2418)
- The behavior of column targeting
  in result sets is now case sensitive by
  default.   SQLAlchemy for many years would
  run a case-insensitive conversion on these values,
  probably to alleviate early case sensitivity
  issues with dialects like Oracle and
  Firebird.   These issues have been more cleanly
  solved in more modern versions so the performance
  hit of calling lower() on identifiers is removed.
  The case insensitive comparisons can be re-enabled
  by setting “case_insensitive=False” on
  create_engine().
  References: [#2423](https://www.sqlalchemy.org/trac/ticket/2423)
- The “unconsumed column names” warning emitted
  when keys are present in insert.values() or update.values()
  that aren’t in the target table is now an exception.
  References: [#2415](https://www.sqlalchemy.org/trac/ticket/2415)
- Added “MATCH” clause to ForeignKey,
  ForeignKeyConstraint, courtesy Ryan Kelly.
  References: [#2502](https://www.sqlalchemy.org/trac/ticket/2502)
- Added support for DELETE and UPDATE from
  an alias of a table, which would assumedly
  be related to itself elsewhere in the query,
  courtesy Ryan Kelly.
  References: [#2507](https://www.sqlalchemy.org/trac/ticket/2507)
- select() features a correlate_except()
  method, auto correlates all selectables except those
  passed.
- The prefix_with() method is now available
  on each of select(), insert(), update(), delete(),
  all with the same API, accepting multiple
  prefix calls, as well as a “dialect name” so that
  the prefix can be limited to one kind of dialect.
  References: [#2431](https://www.sqlalchemy.org/trac/ticket/2431)
- Added reduce_columns() method
  to select() construct, replaces columns inline
  using the util.reduce_columns utility function
  to remove equivalent columns.  reduce_columns()
  also adds “with_only_synonyms” to limit the
  reduction just to those columns which have the same
  name.  The deprecated fold_equivalents() feature is
  removed.
  References: [#1729](https://www.sqlalchemy.org/trac/ticket/1729)
- Reworked the startswith(), endswith(),
  contains() operators to do a better job with
  negation (NOT LIKE), and also to assemble them
  at compilation time so that their rendered SQL
  can be altered, such as in the case for Firebird
  STARTING WITH
  References: [#2470](https://www.sqlalchemy.org/trac/ticket/2470)
- Added a hook to the system of rendering
  CREATE TABLE that provides access to the render for each
  Column individually, by constructing a @compiles
  function against the new schema.CreateColumn
  construct.
  References: [#2463](https://www.sqlalchemy.org/trac/ticket/2463)
- ”scalar” selects now have a WHERE method
  to help with generative building.  Also slight adjustment
  regarding how SS “correlates” columns; the new methodology
  no longer applies meaning to the underlying
  Table column being selected.  This improves
  some fairly esoteric situations, and the logic
  that was there didn’t seem to have any purpose.
- An explicit error is raised when
  a ForeignKeyConstraint() that was
  constructed to refer to multiple remote tables
  is first used.
  References: [#2455](https://www.sqlalchemy.org/trac/ticket/2455)
- Added [ColumnOperators.notin_()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.notin_),
  [ColumnOperators.notlike()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.notlike),
  [ColumnOperators.notilike()](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.notilike) to [ColumnOperators](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators).
  References: [#2580](https://www.sqlalchemy.org/trac/ticket/2580)
- The Text() type renders the length
  given to it, if a length was specified.
- Most classes in expression.sql
  are no longer preceded with an underscore,
  i.e. Label, SelectBase, Generative, CompareMixin.
  _BindParamClause is also renamed to
  BindParameter.   The old underscore names for
  these classes will remain available as synonyms
  for the foreseeable future.
- Fixed bug where keyword arguments passed to
  `Compiler.process()` wouldn’t get propagated
  to the column expressions present in the columns
  clause of a SELECT statement.  In particular this would
  come up when used by custom compilation schemes that
  relied upon special flags.
  References: [#2593](https://www.sqlalchemy.org/trac/ticket/2593)
- The auto-correlation feature of [select()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select), and
  by proxy that of [Query](https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query), will not
  take effect for a SELECT statement that is being
  rendered directly in the FROM list of the enclosing
  SELECT.  Correlation in SQL only applies to column
  expressions such as those in the WHERE, ORDER BY,
  columns clause.
  References: [#2595](https://www.sqlalchemy.org/trac/ticket/2595)
- A tweak to column precedence which moves the
  “concat” and “match” operators to be the same as
  that of “is”, “like”, and others; this helps with
  parenthesization rendering when used in conjunction
  with “IS”.
  References: [#2564](https://www.sqlalchemy.org/trac/ticket/2564)
- Applying a column expression to a select
  statement using a label with or without other
  modifying constructs will no longer “target” that
  expression to the underlying Column; this affects
  ORM operations that rely upon Column targeting
  in order to retrieve results.  That is, a query
  like query(User.id, User.id.label(‘foo’)) will now
  track the value of each “User.id” expression separately
  instead of munging them together.  It is not expected
  that any users will be impacted by this; however,
  a usage that uses select() in conjunction with
  query.from_statement() and attempts to load fully
  composed ORM entities may not function as expected
  if the select() named Column objects with arbitrary
  .label() names, as these will no longer target to
  the Column objects mapped by that entity.
  References: [#2591](https://www.sqlalchemy.org/trac/ticket/2591)
- Fixes to the interpretation of the
  Column “default” parameter as a callable
  to not pass ExecutionContext into a keyword
  argument parameter.
  References: [#2520](https://www.sqlalchemy.org/trac/ticket/2520)
- All of UniqueConstraint, ForeignKeyConstraint,
  CheckConstraint, and PrimaryKeyConstraint will
  attach themselves to their parent table automatically
  when they refer to a Table-bound Column object directly
  (i.e. not just string column name), and refer to
  one and only one Table.   Prior to 0.8 this behavior
  occurred for UniqueConstraint and PrimaryKeyConstraint,
  but not ForeignKeyConstraint or CheckConstraint.
  References: [#2410](https://www.sqlalchemy.org/trac/ticket/2410)
- TypeDecorator now includes a generic repr()
  that works in terms of the “impl” type by default.
  This is a behavioral change for those TypeDecorator
  classes that specify a custom __init__ method; those
  types will need to re-define __repr__() if they need
  __repr__() to provide a faithful constructor representation.
  References: [#2594](https://www.sqlalchemy.org/trac/ticket/2594)
- column.label(None) now produces an
  anonymous label, instead of returning the
  column object itself, consistent with the behavior
  of label(column, None).
  References: [#2168](https://www.sqlalchemy.org/trac/ticket/2168)
- The long-deprecated and non-functional `assert_unicode` flag on
  [create_engine()](https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.create_engine) as well as [String](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.String) is removed.

### postgresql

- postgresql.ARRAY features an optional
  “dimension” argument, will assign a specific
  number of dimensions to the array which will
  render in DDL as ARRAY[][]…, also improves
  performance of bind/result processing.
  References: [#2441](https://www.sqlalchemy.org/trac/ticket/2441)
- postgresql.ARRAY now supports
  indexing and slicing.  The Python [] operator
  is available on all SQL expressions that are
  of type ARRAY; integer or simple slices can be
  passed.  The slices can also be used on the
  assignment side in the SET clause of an UPDATE
  statement by passing them into Update.values();
  see the docs for examples.
- Added new “array literal” construct
  postgresql.array().  Basically a “tuple” that
  renders as ARRAY[1,2,3].
- Added support for the PostgreSQL ONLY
  keyword, which can appear corresponding to a
  table in a SELECT, UPDATE, or DELETE statement.
  The phrase is established using with_hint().
  Courtesy Ryan Kelly
  References: [#2506](https://www.sqlalchemy.org/trac/ticket/2506)
- The “ischema_names” dictionary of the
  PostgreSQL dialect is “unofficially” customizable.
  Meaning, new types such as PostGIS types can
  be added into this dictionary, and the PG type
  reflection code should be able to handle simple
  types with variable numbers of arguments.
  The functionality here is “unofficial” for
  three reasons:
  1. this is not an “official” API.  Ideally
    an “official” API would allow custom type-handling
    callables at the dialect or global level
    in a generic way.
  2. This is only implemented for the PG dialect,
    in particular because PG has broad support
    for custom types vs. other database backends.
    A real API would be implemented at the
    default dialect level.
  3. The reflection code here is only tested against
    simple types and probably has issues with more
    compositional types.
  patch courtesy Éric Lemoine.

### mysql

- Added TIME type to mysql dialect,
  accepts “fst” argument which is the new
  “fractional seconds” specifier for recent
  MySQL versions.  The datatype will interpret
  a microseconds portion received from the driver,
  however note that at this time most/all MySQL
  DBAPIs do not support returning this value.
  References: [#2534](https://www.sqlalchemy.org/trac/ticket/2534)
- Dialect no longer emits expensive server
  collations query, as well as server casing,
  on first connect.  These functions are still
  available as semi-private.
  References: [#2404](https://www.sqlalchemy.org/trac/ticket/2404)

### sqlite

- the SQLite date and time types
  have been overhauled to support a more open
  ended format for input and output, using
  name based format strings and regexps.  A
  new argument “microseconds” also provides
  the option to omit the “microseconds”
  portion of timestamps.  Thanks to
  Nathan Wright for the work and tests on
  this.
  References: [#2363](https://www.sqlalchemy.org/trac/ticket/2363)
- Added [NCHAR](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.NCHAR), [NVARCHAR](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.NVARCHAR)
  to the SQLite dialect’s list of recognized type names
  for reflection.   SQLite returns the name given
  to a type as the name returned.
  References: [rc3addcc9ffad](https://www.sqlalchemy.org/trac/changeset/c3addcc9ffad)

### mssql

- SQL Server dialect can be given
  database-qualified schema names,
  i.e. “schema=’mydatabase.dbo’”; reflection
  operations will detect this, split the schema
  among the “.” to get the owner separately,
  and emit a “USE mydatabase” statement before
  reflecting targets within the “dbo” owner;
  the existing database returned from
  DB_NAME() is then restored.
- updated support for the mxodbc
  driver; mxodbc 3.2.1 is recommended for full
  compatibility.
- removed legacy behavior whereby
  a column comparison to a scalar SELECT via
  == would coerce to an IN with the SQL server
  dialect.  This is implicit
  behavior which fails in other scenarios
  so is removed.  Code which relies on this
  needs to be modified to use column.in_(select)
  explicitly.
  References: [#2277](https://www.sqlalchemy.org/trac/ticket/2277)

### oracle

- The types of columns excluded from the
  setinputsizes() set can be customized by sending
  a list of string DBAPI type names to exclude,
  using the exclude_setinputsizes dialect parameter.
  This list was previously fixed.  The list also
  now defaults to STRING, UNICODE, removing
  CLOB, NCLOB from the list.
  References: [#2561](https://www.sqlalchemy.org/trac/ticket/2561)
- Quoting information is now passed along
  from a Column with quote=True when generating
  a same-named bound parameter to the bindparam()
  object, as is the case in generated INSERT and UPDATE
  statements, so that unknown reserved names can
  be fully supported.
  References: [#2437](https://www.sqlalchemy.org/trac/ticket/2437)
- The CreateIndex construct in Oracle
  will now schema-qualify the name of the index
  to be that of the parent table.  Previously this
  name was omitted which apparently creates the
  index in the default schema, rather than that
  of the table.

### misc

- the MS Access dialect has been
  moved to its own project on Bitbucket,
  taking advantage of the new SQLAlchemy
  dialect compliance suite.   The dialect is
  still in very rough shape and probably not
  ready for general use yet, however
  it does have *extremely* rudimental
  functionality now.
  [https://github.com/gordthompson/sqlalchemy-access](https://github.com/gordthompson/sqlalchemy-access)
- The “startswith()” operator renders
  as “STARTING WITH”, “~startswith()” renders
  as “NOT STARTING WITH”, using FB’s more efficient
  operator.
  References: [#2470](https://www.sqlalchemy.org/trac/ticket/2470)
- An experimental dialect for the fdb
  driver is added, but is untested as I cannot
  get the fdb package to build.
  References: [#2504](https://www.sqlalchemy.org/trac/ticket/2504)
- CompileError is raised when VARCHAR with
  no length is attempted to be emitted, same
  way as MySQL.
  References: [#2505](https://www.sqlalchemy.org/trac/ticket/2505)
- Firebird now uses strict “ansi bind rules”
  so that bound parameters don’t render in the
  columns clause of a statement - they render
  literally instead.
- Support for passing datetime as date when
  using the DateTime type with Firebird; other
  dialects support this.
- The MaxDB dialect, which hasn’t been
  functional for several years, is
  moved out to a pending bitbucket project, (deleted; to view
  the MaxDB code see the commit before it was removed at
  [https://github.com/sqlalchemy/sqlalchemy/tree/ba67f7dbc5eb7a1ed2a3e1b56df72a837130f7bb/lib/sqlalchemy/dialects/maxdb](https://github.com/sqlalchemy/sqlalchemy/tree/ba67f7dbc5eb7a1ed2a3e1b56df72a837130f7bb/lib/sqlalchemy/dialects/maxdb))
