1.4.1

Released: March 17, 2021

orm

• [orm] [bug] [regression] Fixed regression where producing a Core expression construct such as _sql.select() using ORM entities would eagerly configure the mappers, in an effort to maintain compatibility with the _orm.Query object which necessarily does this to support many backref-related legacy cases. However, core _sql.select() constructs are also used in mapper configurations and such, and to that degree this eager configuration is more of an inconvenience, so eager configure has been disabled for the _sql.select() and other Core constructs in the absence of ORM loading types of functions such as _orm.Load.

  The change maintains the behavior of _orm.Query so that backwards compatibility is maintained. However, when using a _sql.select() in conjunction with ORM entities, a "backref" that isn't explicitly placed on one of the classes until mapper configure time won't be available unless _orm.configure_mappers() or the newer _orm.registry.configure() has been called elsewhere. Prefer using _orm.relationship.back_populates for more explicit relationship configuration which does not have the eager configure requirement.

  References: #6066

• [orm] [bug] [regression] Fixed a critical regression in the relationship lazy loader where the SQL criteria used to fetch a related many-to-one object could go stale in relation to other memoized structures within the loader if the mapper had configuration changes, such as can occur when mappers are late configured or configured on demand, producing a comparison to None and returning no object. Huge thanks to Alan Hamlett for their help tracking this down late into the night.

  References: #6055

• [orm] [bug] [regression] Fixed regression where the _orm.Query.exists() method would fail to create an expression if the entity list of the _orm.Query were an arbitrary SQL column expression.

  References: #6076

• [orm] [bug] [regression] Fixed regression where calling upon _orm.Query.count() in conjunction with a loader option such as _orm.joinedload() would fail to ignore the loader option. This is a behavior that has always been very specific to the _orm.Query.count() method; an error is normally raised if a given _orm.Query has options that don't apply to what it is returning.

  References: #6052

• [orm] [bug] [regression] Fixed regression in _orm.Session.identity_key(), including that the method and related methods were not covered by any unit test as well as that the method contained a typo preventing it from functioning correctly.

  References: #6067

orm declarative

• [orm] [declarative] [bug] [regression] Fixed bug where user-mapped classes that contained an attribute named "registry" would cause conflicts with the new registry-based mapping system when using DeclarativeMeta. While the attribute remains something that can be set explicitly on a declarative base to be consumed by the metaclass, once located it is placed under a private class variable so it does not conflict with future subclasses that use the same name for other purposes.

  References: #6054

engine

• [engine] [bug] [regression] The Python namedtuple() has the behavior such that the names count and index will be served as tuple values if the named tuple includes those names; if they are absent, then their behavior as methods of collections.abc.Sequence is maintained. Therefore the _result.Row and _result.LegacyRow classes have been fixed so that they work in this same way, maintaining the expected behavior for database rows that have columns named "index" or "count".

  References: #6074

mssql

• [mssql] [bug] [regression] Fixed regression where a new setinputsizes() API that's available for pyodbc was enabled, which is apparently incompatible with pyodbc's fast_executemany() mode in the absence of more accurate typing information, which as of yet is not fully implemented or tested. The pyodbc dialect and connector has been modified so that setinputsizes() is not used at all unless the parameter use_setinputsizes is passed to the dialect, e.g. via _sa.create_engine(), at which point its behavior can be customized using the DialectEvents.do_setinputsizes() hook.

  References: #6058

misc

• [bug] [regression] Added back items and values to ColumnCollection class. The regression was introduced while adding support for duplicate columns in from clauses and selectable in ticket #4753.

  References: #6068

1.4.0

Released: March 15, 2021

orm

• [orm] [bug] Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many cases placing this parameter on a many-to-one relationship is not what was intended, there are use cases where delete cascade may want to be disallowed following from such a relationship.

  This change is also backported to: 1.3.24

  References: #5983

• [orm] [bug] Fixed issue where the process of joining two tables could fail if one of the tables had an unrelated, unresolvable foreign key constraint which would raise _exc.NoReferenceError within the join process, which nonetheless could be bypassed to allow the join to complete. The logic which tested the exception for significance within the process would make assumptions about the construct which would fail.

  This change is also backported to: 1.3.24

  References: #5952

• [orm] [bug] Fixed issue where the _mutable.MutableComposite construct could be placed into an invalid state when the parent object was already loaded, and then covered by a subsequent query, due to the composite properties' refresh handler replacing the object with a new one not handled by the mutable extension.

  This change is also backported to: 1.3.24

  References: #6001

• [orm] [bug] Fixed regression where the _orm.relationship.query_class parameter stopped being functional for "dynamic" relationships. The AppenderQuery remains dependent on the legacy _orm.Query class; users are encouraged to migrate from the use of "dynamic" relationships to using _orm.with_parent() instead.

  References: #5981

• [orm] [bug] [regression] Fixed regression where _orm.Query.join() would produce no effect if the query itself as well as the join target were against a _schema.Table object, rather than a mapped class. This was part of a more systemic issue where the legacy ORM query compiler would not be correctly used from a _orm.Query if the statement produced had not ORM entities present within it.

  References: #6003

• [orm] [bug] [asyncio] The API for _asyncio.AsyncSession.delete() is now an awaitable; this method cascades along relationships which must be loaded in a similar manner as the _asyncio.AsyncSession.merge() method.

  References: #5998

• [orm] [bug] The unit of work process now turns off all "lazy='raise'" behavior altogether when a flush is proceeding. While there are areas where the UOW is sometimes loading things that aren't ultimately needed, the lazy="raise" strategy is not helpful here as the user often does not have much control or visibility into the flush process.

  References: #5984

engine

• [engine] [bug] Fixed bug where the "schema_translate_map" feature failed to be taken into account for the use case of direct execution of _schema.DefaultGenerator objects such as sequences, which included the case where they were "pre-executed" in order to generate primary key values when implicit_returning was disabled.

  This change is also backported to: 1.3.24

  References: #5929

• [engine] [bug] Improved engine logging to note ROLLBACK and COMMIT which is logged while the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library level and do not have any effect when AUTOCOMMIT is in effect, however it's still worthwhile to log as these indicate where SQLAlchemy sees the "transaction" demarcation.

  References: #6002

• [engine] [bug] [regression] Fixed a regression where the "reset agent" of the connection pool wasn't really being utilized by the _engine.Connection when it were closed, and also leading to a double-rollback scenario that was somewhat wasteful. The newer architecture of the engine has been updated so that the connection pool "reset-on-return" logic will be skipped when the _engine.Connection explicitly closes out the transaction before returning the pool to the connection.

  References: #6004

sql

• [sql] [change] Altered the compilation for the CTE construct so that a string is returned representing the inner SELECT statement if the CTE is stringified directly, outside of the context of an enclosing SELECT; This is the same behavior of _sql.FromClause.alias() and _sql.Select.subquery(). Previously, a blank string would be returned as the CTE is normally placed above a SELECT after that SELECT has been generated, which is generally misleading when debugging.

• [sql] [bug] [sqlite] Fixed issue where the CHECK constraint generated by _types.Boolean or _types.Enum would fail to render the naming convention correctly after the first compilation, due to an unintended change of state within the name given to the constraint. This issue was first introduced in 0.9 in the fix for issue #3067, and the fix revises the approach taken at that time which appears to have been more involved than what was needed.

  This change is also backported to: 1.3.24

  References: #6007

• [sql] [bug] Fixed bug where the "percent escaping" feature that occurs with dialects that use the "format" or "pyformat" bound parameter styles was not enabled for the _sql.Operators.op() and _sql.custom_op constructs, for custom operators that use percent signs. The percent sign will now be automatically doubled based on the paramstyle as necessary.

  References: #6016

• [sql] [bug] [regression] Fixed regression where the "unsupported compilation error" for unknown datatypes would fail to raise correctly.

  References: #5979

• [sql] [bug] [regression] Fixed regression where usage of the standalone _sql.distinct() used in the form of being directly SELECTed would fail to be locatable in the result set by column identity, which is how the ORM locates columns. While standalone _sql.distinct() is not oriented towards being directly SELECTed (use _sql.select.distinct() for a regular SELECT DISTINCT..) , it was usable to a limited extent in this way previously (but wouldn't work in subqueries, for example). The column targeting for unary expressions such as "DISTINCT <col>" has been improved so that this case works again, and an additional improvement has been made so that usage of this form in a subquery at least generates valid SQL which was not the case previously.

  The change additionally enhances the ability to target elements in row._mapping based on SQL expression objects in ORM-enabled SELECT statements, including whether the statement was invoked by connection.execute() or session.execute().

  References: #6008

schema

• [schema] [bug] Repaired / implemented support for primary key constraint naming conventions that use column names/keys/etc as part of the convention. In particular, this includes that the PrimaryKeyConstraint object that's automatically associated with a schema.Table will update its name as new primary key _schema.Column objects are added to the table and then to the constraint. Internal failure modes related to this constraint construction process including no columns present, no name present or blank name present are now accommodated.

  This change is also backported to: 1.3.24

  References: #5919

• [schema] [bug] Deprecated all schema-level .copy() methods and renamed to _copy(). These are not standard Python "copy()" methods as they typically rely upon being instantiated within particular contexts which are passed to the method as optional keyword arguments. The _schema.Table.tometadata() method is the public API that provides copying for _schema.Table objects.

  References: #5953

mypy

• [mypy] [feature] Rudimentary and experimental support for Mypy has been added in the form of a new plugin, which itself depends on new typing stubs for SQLAlchemy. The plugin allows declarative mappings in their standard form to both be compatible with Mypy as well as to provide typing support for mapped classes and instances.

  References: #4609

postgresql

• [postgresql] [usecase] [asyncio] [mysql] Added an asyncio.Lock() within SQLAlchemy's emulated DBAPI cursor, local to the connection, for the asyncpg and aiomysql dialects for the scope of the cursor.execute() and cursor.executemany() methods. The rationale is to prevent failures and corruption for the case where the connection is used in multiple awaitables at once.

  While this use case can also occur with threaded code and non-asyncio dialects, we anticipate this kind of use will be more common under asyncio, as the asyncio API is encouraging of such use. It's definitely better to use a distinct connection per concurrent awaitable however as concurrency will not be achieved otherwise.

  For the asyncpg dialect, this is so that the space between the call to prepare() and fetch() is prevented from allowing concurrent executions on the connection from causing interface error exceptions, as well as preventing race conditions when starting a new transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level so this intends to provide a similar behavior, outside the realm of server side cursors.

  For the aiomysql dialect, the mutex will provide safety such that the statement execution and the result set fetch, which are two distinct steps at the connection level, won't get corrupted by concurrent executions on the same connection.

  References: #5967

• [postgresql] [bug] Fixed issue where using _postgresql.aggregate_order_by would return ARRAY(NullType) under certain conditions, interfering with the ability of the result object to return data correctly.

  This change is also backported to: 1.3.24

  References: #5989

mssql

• [mssql] [bug] Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes.

  References: #5919

misc

• [usecase] [ext] Add new parameter _automap.AutomapBase.prepare.reflection_options to allow passing of _schema.MetaData.reflect() options like only or dialect-specific reflection options like oracle_resolve_synonyms.

  References: #5942

• [bug] [ext] The sqlalchemy.ext.mutable extension now tracks the "parents" collection using the InstanceState associated with objects, rather than the object itself. The latter approach required that the object be hashable so that it can be inside of a WeakKeyDictionary, which goes against the behavioral contract of the ORM overall which is that ORM mapped objects do not need to provide any particular kind of __hash__() method and that unhashable objects are supported.

  References: #6020

# 1.4.0b3

Released: February 15, 2021 ## orm

• [orm] [feature] The ORM used in 2.0 style can now return ORM objects from the rows returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by supplying the construct to _sql.Select.from_statement() in an ORM context.

  Unknown interpreted text role "term".

• [orm] [bug] Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label style would not be preserved in the keys used by result rows; this has been applied to all combinations of Core/ORM columns / session vs. connection etc. so that the linkage from statement to result row is the same in all cases. As part of this change, the labeling of column expressions in rows has been improved to retain the original name of the ORM attribute even if used in a subquery.

  References: [#5933](http://www.sqlalchemy.org/trac/ticket/5933)

## engine

• [engine] [bug] [postgresql] Continued with the improvement made as part of [#5653](http://www.sqlalchemy.org/trac/ticket/5653) to further support bound parameter names, including those generated against column names, for names that include colons, parenthesis, and question marks, as well as improved test support, so that bound parameter names even if they are auto-derived from column names should have no problem including for parenthesis in psycopg2's "pyformat" style.

  As part of this change, the format used by the asyncpg DBAPI adapter (which is local to SQLAlchemy's asyncpg dialect) has been changed from using "qmark" paramstyle to "format", as there is a standard and internally supported SQL string escaping style for names that use percent signs with "format" style (i.e. to double percent signs), as opposed to names that use question marks with "qmark" style (where an escaping system is not defined by pep-249 or Python).

  References: [#5941](http://www.sqlalchemy.org/trac/ticket/5941)

## sql

• [sql] [usecase] [postgresql] [sqlite] Enhance set_ keyword of OnConflictDoUpdate to accept a ColumnCollection, such as the .c. collection from a Selectable, or the .excluded contextual object.

  References: [#5939](http://www.sqlalchemy.org/trac/ticket/5939)

• [sql] [bug] Fixed bug where the "cartesian product" assertion was not correctly accommodating for joins between tables that relied upon the use of LATERAL to connect from a subquery to another subquery in the enclosing context.

  References: [#5924](http://www.sqlalchemy.org/trac/ticket/5924)

• [sql] [bug] Fixed 1.4 regression where the _functions.Function.in_() method was not covered by tests and failed to function properly in all cases.

  References: [#5934](http://www.sqlalchemy.org/trac/ticket/5934)

• [sql] [bug] Fixed regression where use of an arbitrary iterable with the _sql.select() function was not working, outside of plain lists. The forwards/backwards compatibility logic here now checks for a wider range of incoming "iterable" types including that a .c collection from a selectable can be passed directly. Pull request compliments of Oliver Rice.

  References: [#5935](http://www.sqlalchemy.org/trac/ticket/5935)

1.4.0b2

Released: February 3, 2021

general

• [general] [bug] Fixed a SQLite source file that had non-ascii characters inside of its docstring without a source encoding, introduced within the "INSERT..ON CONFLICT" feature, which would cause failures under Python 2.

platform

• [platform] [performance] Adjusted some elements related to internal class production at import time which added significant latency to the time spent to import the library vs. that of 1.3. The time is now about 20-30% slower than 1.3 instead of 200%.

  References: #5681

orm

• [orm] [usecase] Added _orm.ORMExecuteState.bind_mapper and _orm.ORMExecuteState.all_mappers accessors to _orm.ORMExecuteState event object, so that handlers can respond to the target mapper and/or mapped class or classes involved in an ORM statement execution.

• [orm] [usecase] [asyncio] Added _asyncio.AsyncSession.scalar(), _asyncio.AsyncSession.get() as well as support for _orm.sessionmaker.begin() to work as an async context manager with _asyncio.AsyncSession. Also added _asyncio.AsyncSession.in_transaction() accessor.

  References: #5796, #5797, #5802

• [orm] [changed] Mapper "configuration", which occurs within the _orm.configure_mappers() function, is now organized to be on a per-registry basis. This allows for example the mappers within a certain declarative base to be configured, but not those of another base that is also present in memory. The goal is to provide a means of reducing application startup time by only running the "configure" process for sets of mappers that are needed. This also adds the _orm.registry.configure() method that will run configure for the mappers local in a particular registry only.

  References: #5897

• [orm] [bug] Added a comprehensive check and an informative error message for the case where a mapped class, or a string mapped class name, is passed to _orm.relationship.secondary. This is an extremely common error which warrants a clear message.

  Additionally, added a new rule to the class registry resolution such that with regards to the _orm.relationship.secondary parameter, if a mapped class and its table are of the identical string name, the Table will be favored when resolving this parameter. In all other cases, the class continues to be favored if a class and table share the identical name.

  This change is also backported to: 1.3.21

  References: #5774

• [orm] [bug] Fixed bug involving the restore_load_context option of ORM events such as _ormevent.InstanceEvents.load() such that the flag would not be carried along to subclasses which were mapped after the event handler were first established.

  This change is also backported to: 1.3.21

  References: #5737

• [orm] [bug] [regression] Fixed issue in new _orm.Session similar to that of the _engine.Connection where the new "autobegin" logic could be tripped into a re-entrant (recursive) state if SQL were executed within the SessionEvents.after_transaction_create() event hook.

  References: #5845

• [orm] [bug] [unitofwork] Improved the unit of work topological sorting system such that the toplogical sort is now deterministic based on the sorting of the input set, which itself is now sorted at the level of mappers, so that the same inputs of affected mappers should produce the same output every time, among mappers / tables that don't have any dependency on each other. This further reduces the chance of deadlocks as can be observed in a flush that UPDATEs among multiple, unrelated tables such that row locks are generated.

  References: #5735

• [orm] [bug] Fixed regression where the Bundle.single_entity flag would take effect for a Bundle even though it were not set. Additionally, this flag is legacy as it only makes sense for the _orm.Query object and not 2.0 style execution. a deprecation warning is emitted when used with new-style execution.

  References: #5702

• [orm] [bug] Fixed regression where creating an _orm.aliased construct against a plain selectable and including a name would raise an assertionerror.

  References: #5750

• [orm] [bug] Related to the fixes for the lambda criteria system within Core, within the ORM implemented a variety of fixes for the _orm.with_loader_criteria() feature as well as the _orm.SessionEvents.do_orm_execute() event handler that is often used in conjunction [ticket:5760]:

  -   fixed issue where `_orm.with_loader_criteria()` function would fail
      if the given entity or base included non-mapped mixins in its descending
      class hierarchy [ticket:5766]
  
  -   The `_orm.with_loader_criteria()` feature is now unconditionally
      disabled for the case of ORM "refresh" operations, including loads
      of deferred or expired column attributes as well as for explicit
      operations like `_orm.Session.refresh()`.  These loads are necessarily
      based on primary key identity where addiional WHERE criteria is
      never appropriate.  [ticket:5762]
  
  -   Added new attribute `_orm.ORMExecuteState.is_column_load` to indicate
      that a `_orm.SessionEvents.do_orm_execute()` handler that a particular
      operation is a primary-key-directed column attribute load, where additional
      criteria should not be added.  The `_orm.with_loader_criteria()`
      function as above ignores these in any case now.  [ticket:5761]
  
  -   Fixed issue where the `_orm.ORMExecuteState.is_relationship_load`
      attribute would not be set correctly for many lazy loads as well as all
      selectinloads.  The flag is essential in order to test if options should
      be added to statements or if they would already have been propagated via
      relationship loads.  [ticket:5764]
  

  References: #5760, #5761, #5762, #5764, #5766

• [orm] [bug] Fixed 1.4 regression where the use of _orm.Query.having() in conjunction with queries with internally adapted SQL elements (common in inheritance scenarios) would fail due to an incorrect function call. Pull request courtesy esoh.

  References: #5781

• [orm] [bug] Fixed an issue where the API to create a custom executable SQL construct using the sqlalchemy.ext.compiles extension according to the documentation that's been up for many years would no longer function if only Executable, ClauseElement were used as the base classes, additional classes were needed if wanting to use _orm.Session.execute(). This has been resolved so that those extra classes aren't needed.

• [orm] [bug] [regression] Fixed ORM unit of work regression where an errant "assert primary_key" statement interferes with primary key generation sequences that don't actually consider the columns in the table to use a real primary key constraint, instead using _orm.mapper.primary_key to establish certain columns as "primary".

  References: #5867

orm declarative

• [orm] [declarative] [feature] Added an alternate resolution scheme to Declarative that will extract the SQLAlchemy column or mapped property from the "metadata" dictionary of a dataclasses.Field object. This allows full declarative mappings to be combined with dataclass fields.

  References: #5745

engine

• [engine] [feature] Dialect-specific constructs such as _postgresql.Insert.on_conflict_do_update() can now stringify in-place without the need to specify an explicit dialect object. The constructs, when called upon for str(), print(), etc. now have internal direction to call upon their appropriate dialect rather than the "default"dialect which doesn't know how to stringify these. The approach is also adapted to generic schema-level create/drop such as _schema.AddConstraint, which will adapt its stringify dialect to one indicated by the element within it, such as the _postgresql.ExcludeConstraint object.

• [engine] [feature] Added new execution option _engine.Connection.execution_options.logging_token. This option will add an additional per-message token to log messages generated by the _engine.Connection as it executes statements. This token is not part of the logger name itself (that part can be affected using the existing _sa.create_engine.logging_name parameter), so is appropriate for ad-hoc connection use without the side effect of creating many new loggers. The option can be set at the level of _engine.Connection or _engine.Engine.

  References: #5911

• [engine] [bug] [sqlite] Fixed bug in the 2.0 "future" version of Engine where emitting SQL during the EngineEvents.begin() event hook would cause a re-entrant (recursive) condition due to autobegin, affecting among other things the recipe documented for SQLite to allow for savepoints and serializable isolation support.

  References: #5845

• [engine] [bug] [oracle] [postgresql] Adjusted the "setinputsizes" logic relied upon by the cx_Oracle, asyncpg and pg8000 dialects to support a TypeDecorator that includes an override the TypeDecorator.get_dbapi_type() method.

• [engine] [bug] Added the "future" keyword to the list of words that are known by the _sa.engine_from_config() function, so that the values "true" and "false" may be configured as "boolean" values when using a key such as sqlalchemy.future = true or sqlalchemy.future = false.

sql

• [sql] [feature] Implemented support for "table valued functions" along with additional syntaxes supported by PostgreSQL, one of the most commonly requested features. Table valued functions are SQL functions that return lists of values or rows, and are prevalent in PostgreSQL in the area of JSON functions, where the "table value" is commonly referred towards as the "record" datatype. Table valued functions are also supported by Oracle and SQL Server.

  Features added include:

  -   the `_functions.FunctionElement.table_valued()` modifier that creates a table-like
      selectable object from a SQL function
  
  -   A `_sql.TableValuedAlias` construct that renders a SQL function
      as a named table
  
  -   Support for PostgreSQL's special "derived column" syntax that includes
      column names and sometimes datatypes, such as for the
      `json_to_recordset` function, using the
      `_sql.TableValuedAlias.render_derived()` method.
  
  -   Support for PostgreSQL's "WITH ORDINALITY" construct using the
      `_functions.FunctionElement.table_valued.with_ordinality` parameter
  
  -   Support for selection FROM a SQL function as column-valued scalar, a
      syntax supported by PostgreSQL and Oracle, via the
      `_functions.FunctionElement.column_valued()` method
  
  -   A way to SELECT a single column from a table-valued expression without
      using a FROM clause via the `_functions.FunctionElement.scalar_table_valued()`
      method.
  

  References: #3566

• [sql] [usecase] Multiple calls to "returning", e.g. _sql.Insert.returning(), may now be chained to add new columns to the RETURNING clause.

  References: #5695

• [sql] [usecase] Added _sql.Select.outerjoin_from() method to complement _sql.Select.join_from().

• [sql] [usecase] Adjusted the "literal_binds" feature of _sql.Compiler to render NULL for a bound parameter that has None as the value, either explicitly passed or omitted. The previous error message "bind parameter without a renderable value" is removed, and a missing or None value will now render NULL in all cases. Previously, rendering of NULL was starting to happen for DML statements due to internal refactorings, but was not explicitly part of test coverage, which it now is.

  While no error is raised, when the context is within that of a column comparison, and the operator is not "IS"/"IS NOT", a warning is emitted that this is not generally useful from a SQL perspective.

  References: #5888

• [sql] [bug] Fixed issue in new _sql.Select.join() method where chaining from the current JOIN wasn't looking at the right state, causing an expression like "FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than "FROM a JOIN b <onclause> JOIN c <onclause>".

  References: #5858

• [sql] [bug] Deprecation warnings are emitted under "SQLALCHEMY_WARN_20" mode when passing a plain string to _orm.Session.execute().

  References: #5754

• [sql] [bug] [orm] A wide variety of fixes to the "lambda SQL" feature introduced at engine_lambda_caching have been implemented based on user feedback, with an emphasis on its use within the _orm.with_loader_criteria() feature where it is most prominently used [ticket:5760]:

  -   fixed issue where boolean True/False values referred towards in the
      closure variables of the lambda would cause failures [ticket:5763]
  
  -   Repaired a non-working detection for Python functions embedded in the
      lambda that produce bound values; this case is likely not supportable
      so raises an informative error, where the function should be invoked
      outside the lambda itself.  New documentation has been added to
      further detail this behavior. [ticket:5770]
  
  -   The lambda system by default now rejects the use of non-SQL elements
      within the closure variables of the lambda entirely, where the error
      suggests the two options of either explicitly ignoring closure variables
      that are not SQL parameters, or specifying a specific set of values to be
      considered as part of the cache key based on hash value.   This critically
      prevents the lambda system from assuming that arbitrary objects within
      the lambda's closure are appropriate for caching while also refusing to
      ignore them by default, preventing the case where their state might
      not be constant and have an impact on the SQL construct produced.
      The error message is comprehensive and new documentation has been
      added to further detail this behavior. [ticket:5765]
  
  -   Fixed support for the edge case where an `in_()` expression
      against a list of SQL elements, such as `_sql.literal()` objects,
      would fail to be accommodated correctly. [ticket:5768]
  

  References: #5760, #5763, #5765, #5768, #5770

• [sql] [bug] [mysql] [postgresql] [sqlite] An informative error message is now raised for a selected set of DML methods (currently all part of _dml.Insert constructs) if they are called a second time, which would implicitly cancel out the previous setting. The methods altered include: _sqlite.Insert.on_conflict_do_update, _sqlite.Insert.on_conflict_do_nothing (SQLite), _postgresql.Insert.on_conflict_do_update, _postgresql.Insert.on_conflict_do_nothing (PostgreSQL), _mysql.Insert.on_duplicate_key_update (MySQL)

  References: #5169

• [sql] [bug] Fixed issue in new _sql.Values construct where passing tuples of objects would fall back to per-value type detection rather than making use of the _schema.Column objects passed directly to _sql.Values that tells SQLAlchemy what the expected type is. This would lead to issues for objects such as enumerations and numpy strings that are not actually necessary since the expected type is given.

  References: #5785

• [sql] [bug] Fixed issue where a RemovedIn20Warning would erroneously emit when the .bind attribute were accessed internally on objects, particularly when stringifying a SQL construct.

  References: #5717

• [sql] [bug] Properly render cycle=False and order=False as NO CYCLE and NO ORDER in _sql.Sequence and _sql.Identity objects.

  References: #5722

• [sql] Replace _orm.Query.with_labels() and _sql.GenerativeSelect.apply_labels() with explicit getters and setters _sql.GenerativeSelect.get_label_style() and _sql.GenerativeSelect.set_label_style() to accommodate the three supported label styles: :data:_sql.LABEL_STYLE_DISAMBIGUATE_ONLY, :data:_sql.LABEL_STYLE_TABLENAME_PLUS_COL, and :data:_sql.LABEL_STYLE_NONE.

  Unknown interpreted text role "data".

  Unknown interpreted text role "data".

  Unknown interpreted text role "data".

  In addition, for Core and "future style" ORM queries, LABEL_STYLE_DISAMBIGUATE_ONLY is now the default label style. This style differs from the existing "no labels" style in that labeling is applied in the case of column name conflicts; with LABEL_STYLE_NONE, a duplicate column name is not accessible via name in any case.

  For cases where labeling is significant, namely that the .c collection of a subquery is able to refer to all columns unambiguously, the behavior of LABEL_STYLE_DISAMBIGUATE_ONLY is now sufficient for all SQLAlchemy features across Core and ORM which involve this behavior. Result set rows since SQLAlchemy 1.0 are usually aligned with column constructs positionally.

  For legacy ORM queries using _query.Query, the table-plus-column names labeling style applied by LABEL_STYLE_TABLENAME_PLUS_COL continues to be used so that existing test suites and logging facilities see no change in behavior by default.

  References: #4757

schema

• [schema] [feature] Added _types.TypeEngine.as_generic() to map dialect-specific types, such as sqlalchemy.dialects.mysql.INTEGER, with the "best match" generic SQLAlchemy type, in this case _types.Integer. Pull request courtesy Andrew Hannigan.

  References: #5659

• [schema] [usecase] The _events.DDLEvents.column_reflect() event may now be applied to a _schema.MetaData object where it will take effect for the _schema.Table objects local to that collection.

  References: #5712

• [schema] [usecase] Added parameters _ddl.CreateTable.if_not_exists, _ddl.CreateIndex.if_not_exists, _ddl.DropTable.if_exists and _ddl.DropIndex.if_exists to the _ddl.CreateTable, _ddl.DropTable, _ddl.CreateIndex and _ddl.DropIndex constructs which result in "IF NOT EXISTS" / "IF EXISTS" DDL being added to the CREATE/DROP. These phrases are not accepted by all databases and the operation will fail on a database that does not support it as there is no similarly compatible fallback within the scope of a single DDL statement. Pull request courtesy Ramon Williams.

  References: #2843

• [schema] [changed] Altered the behavior of the _schema.Identity construct such that when applied to a _schema.Column, it will automatically imply that the value of _sql.Column.nullable should default to False, in a similar manner as when the _sql.Column.primary_key parameter is set to True. This matches the default behavior of all supporting databases where IDENTITY implies NOT NULL. The PostgreSQL backend is the only one that supports adding NULL to an IDENTITY column, which is here supported by passing a True value for the _sql.Column.nullable parameter at the same time.

  References: #5775

asyncio

• [asyncio] [usecase] The AsyncEngine, AsyncConnection and AsyncTransaction objects may be compared using Python == or !=, which will compare the two given objects based on the "sync" object they are proxying towards. This is useful as there are cases particularly for AsyncTransaction where multiple instances of AsyncTransaction can be proxying towards the same sync _engine.Transaction, and are actually equivalent. The AsyncConnection.get_transaction() method will currently return a new proxying AsyncTransaction each time as the AsyncTransaction is not otherwise statefully associated with its originating AsyncConnection.

• [asyncio] [bug] Adjusted the greenlet integration, which provides support for Python asyncio in SQLAlchemy, to accommodate for the handling of Python contextvars (introduced in Python 3.7) for greenlet versions greater than 0.4.17. Greenlet version 0.4.17 added automatic handling of contextvars in a backwards-incompatible way; we've coordinated with the greenlet authors to add a preferred API for this in versions subsequent to 0.4.17 which is now supported by SQLAlchemy's greenlet integration. For greenlet versions prior to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked from the dependencies.

  References: #5615

• [asyncio] [bug] Implemented "connection-binding" for AsyncSession, the ability to pass an AsyncConnection to create an AsyncSession. Previously, this use case was not implemented and would use the associated engine when the connection were passed. This fixes the issue where the "join a session to an external transaction" use case would not work correctly for the AsyncSession. Additionally, added methods AsyncConnection.in_transaction(), AsyncConnection.in_nested_transaction(), AsyncConnection.get_transaction(), AsyncConnection.get_nested_transaction() and AsyncConnection.info attribute.

  References: #5811

• [asyncio] [bug] Fixed bug in asyncio connection pool where asyncio.TimeoutError would be raised rather than exc.TimeoutError. Also repaired the _sa.create_engine.pool_timeout parameter set to zero when using the async engine, which previously would ignore the timeout and block rather than timing out immediately as is the behavior with regular QueuePool.

  References: #5827

• [asyncio] [bug] [pool] When using an asyncio engine, the connection pool will now detach and discard a pooled connection that is was not explicitly closed/returned to the pool when its tracking object is garbage collected, emitting a warning that the connection was not properly closed. As this operation occurs during Python gc finalizers, it's not safe to run any IO operations upon the connection including transaction rollback or connection close as this will often be outside of the event loop.

  The AsyncAdaptedQueue used by default on async dpapis should instantiate a queue only when it's first used to avoid binding it to a possibly wrong event loop.

  References: #5823

• [asyncio] The SQLAlchemy async mode now detects and raises an informative error when an non asyncio compatible :term:DBAPI is used. Using a standard DBAPI with async SQLAlchemy will cause it to block like any sync call, interrupting the executing asyncio loop.

  Unknown interpreted text role "term".

postgresql

• [postgresql] [usecase] Added new parameter _postgresql.ExcludeConstraint.ops to the _postgresql.ExcludeConstraint object, to support operator class specification with this constraint. Pull request courtesy Alon Menczer.

  This change is also backported to: 1.3.21

  References: #5604

• [postgresql] [usecase] Added a read/write .autocommit attribute to the DBAPI-adaptation layer for the asyncpg dialect. This so that when working with DBAPI-specific schemes that need to use "autocommit" directly with the DBAPI connection, the same .autocommit attribute which works with both psycopg2 as well as pg8000 is available.

• [postgresql] [changed] Fixed issue where the psycopg2 dialect would silently pass the use_native_unicode=False flag without actually having any effect under Python 3, as the psycopg2 DBAPI uses Unicode unconditionally under Python 3. This usage now raises an _exc.ArgumentError when used under Python 3. Added test support for Python 2.

• [postgresql] [performance] Enhanced the performance of the asyncpg dialect by caching the asyncpg PreparedStatement objects on a per-connection basis. For a test case that makes use of the same statement on a set of pooled connections this appears to grant a 10-20% speed improvement. The cache size is adjustable and may also be disabled.

• [postgresql] [bug] [mysql] Fixed regression introduced in 1.3.2 for the PostgreSQL dialect, also copied out to the MySQL dialect's feature in 1.3.18, where usage of a non _schema.Table construct such as _sql.text() as the argument to _sql.Select.with_for_update.of would fail to be accommodated correctly within the PostgreSQL or MySQL compilers.

  This change is also backported to: 1.3.21

  References: #5729

• [postgresql] [bug] Fixed a small regression where the query for "show standard_conforming_strings" upon initialization would be emitted even if the server version info were detected as less than version 8.2, previously it would only occur for server version 8.2 or greater. The query fails on Amazon Redshift which reports a PG server version older than this value.

  References: #5698

• [postgresql] [bug] Established support for _schema.Column objects as well as ORM instrumented attributes as keys in the set_ dictionary passed to the _postgresql.Insert.on_conflict_do_update() and _sqlite.Insert.on_conflict_do_update() methods, which match to the _schema.Column objects in the .c collection of the target _schema.Table. Previously, only string column names were expected; a column expression would be assumed to be an out-of-table expression that would render fully along with a warning.

  References: #5722

• [postgresql] [bug] [asyncio] Fixed bug in asyncpg dialect where a failure during a "commit" or less likely a "rollback" should cancel the entire transaction; it's no longer possible to emit rollback. Previously the connection would continue to await a rollback that could not succeed as asyncpg would reject it.

  References: #5824

mysql

• [mysql] [feature] Added support for the aiomysql driver when using the asyncio SQLAlchemy extension.

  References: #5747

• [mysql] [bug] [reflection] Fixed issue where reflecting a server default on MariaDB only that contained a decimal point in the value would fail to be reflected correctly, leading towards a reflected table that lacked any server default.

  This change is also backported to: 1.3.21

  References: #5744

sqlite

• [sqlite] [usecase] Implemented INSERT... ON CONFLICT clause for SQLite. Pull request courtesy Ramon Williams.

  References: #4010

• [sqlite] [bug] Use python re.search() instead of re.match() as the operation used by the Column.regexp_match() method when using sqlite. This matches the behavior of regular expressions on other databases as well as that of well-known SQLite plugins.

  References: #5699

mssql

• [mssql] [bug] [datatypes] [mysql] Decimal accuracy and behavior has been improved when extracting floating point and/or decimal values from JSON strings using the _sql.sqltypes.JSON.Comparator.as_float() method, when the numeric value inside of the JSON string has many significant digits; previously, MySQL backends would truncate values with many significant digits and SQL Server backends would raise an exception due to a DECIMAL cast with insufficient significant digits. Both backends now use a FLOAT-compatible approach that does not hardcode significant digits for floating point values. For precision numerics, a new method _sql.sqltypes.JSON.Comparator.as_numeric() has been added which accepts arguments for precision and scale, and will return values as Python Decimal objects with no floating point conversion assuming the DBAPI supports it (all but pysqlite).

  References: #5788

oracle

• [oracle] [bug] Fixed regression which occured due to #5755 which implemented isolation level support for Oracle. It has been reported that many Oracle accounts don't actually have permission to query the v$transaction view so this feature has been altered to gracefully fallback when it fails upon database connect, where the dialect will assume "READ COMMITTED" is the default isolation level as was the case prior to SQLAlchemy 1.3.21. However, explicit use of the _engine.Connection.get_isolation_level() method must now necessarily raise an exception, as Oracle databases with this restriction explicitly disallow the user from reading the current isolation level.

  This change is also backported to: 1.3.22

  References: #5784

• [oracle] [bug] Oracle two-phase transactions at a rudimentary level are now no longer deprecated. After receiving support from cx_Oracle devs we can provide for basic xid + begin/prepare support with some limitations, which will work more fully in an upcoming release of cx_Oracle. Two phase "recovery" is not currently supported.

  References: #5884

• [oracle] [bug] The Oracle dialect now uses select sys_context( 'userenv', 'current_schema' ) from dual to get the default schema name, rather than SELECT USER FROM DUAL, to accommodate for changes to the session-local schema name under Oracle.

  References: #5716

misc

• [usecase] [pool] [tests] Improve documentation and add test for sub-second pool timeouts. Pull request courtesy Jordan Pittier.

  References: #5582

• [usecase] [pool] The internal mechanics of the engine connection routine has been altered such that it's now guaranteed that a user-defined event handler for the _pool.PoolEvents.connect() handler, when established using insert=True, will allow an event handler to run that is definitely invoked before any dialect-specific initialization starts up, most notably when it does things like detect default schema name. Previously, this would occur in most cases but not unconditionally. A new example is added to the schema documentation illustrating how to establish the "default schema name" within an on-connect event.

  References: #5497, #5708

• [bug] [reflection] Fixed bug where the now-deprecated autoload parameter was being called internally within the reflection routines when a related table were reflected.

  References: #5684

• [bug] [pool] Fixed regression where a connection pool event specified with a keyword, most notably insert=True, would be lost when the event were set up. This would prevent startup events that need to fire before dialect-level events from working correctly.

  References: #5708

• [bug] [pool] [pypy] Fixed issue where connection pool would not return connections to the pool or otherwise be finalized upon garbage collection under pypy if the checked out connection fell out of scope without being closed. This is a long standing issue due to pypy's difference in GC behavior that does not call weakref finalizers if they are relative to another object that is also being garbage collected. A strong reference to the related record is now maintained so that the weakref has a strong-referenced "base" to trigger off of.

  References: #5842

# 1.4.0b1

Released: November 2, 2020 ## general

• [general] [change] "python setup.py test" is no longer a test runner, as this is deprecated by Pypa. Please use "tox" with no arguments for a basic test run.

  References: [#4789](http://www.sqlalchemy.org/trac/ticket/4789)

• [general] [bug] Refactored the internal conventions used to cross-import modules that have mutual dependencies between them, such that the inspected arguments of functions and methods are no longer modified. This allows tools like pylint, Pycharm, other code linters, as well as hypothetical pep-484 implementations added in the future to function correctly as they no longer see missing arguments to function calls. The new approach is also simpler and more performant.

  References: [#4656](http://www.sqlalchemy.org/trac/ticket/4656), [#4689](http://www.sqlalchemy.org/trac/ticket/4689)

## platform

• [platform] [change] The importlib_metadata library is used to scan for setuptools entrypoints rather than pkg_resources. as importlib_metadata is a small library that is included as of Python 3.8, the compatibility library is installed as a dependency for Python versions older than 3.8.

  References: [#5400](http://www.sqlalchemy.org/trac/ticket/5400)

• [platform] [change] Installation has been modernized to use setup.cfg for most package metadata.

  References: [#5404](http://www.sqlalchemy.org/trac/ticket/5404)

• [platform] [removed] Dropped support for python 3.4 and 3.5 that has reached EOL. SQLAlchemy 1.4 series requires python 2.7 or 3.6+.

  References: [#5634](http://www.sqlalchemy.org/trac/ticket/5634)

• [platform] [removed] Removed all dialect code related to support for Jython and zxJDBC. Jython has not been supported by SQLAlchemy for many years and it is not expected that the current zxJDBC code is at all functional; for the moment it just takes up space and adds confusion by showing up in documentation. At the moment, it appears that Jython has achieved Python 2.7 support in its releases but not Python 3. If Jython were to be supported again, the form it should take is against the Python 3 version of Jython, and the various zxJDBC stubs for various backends should be implemented as a third party dialect.

  References: [#5094](http://www.sqlalchemy.org/trac/ticket/5094)

## orm

• [orm] [feature] The ORM can now generate queries previously only available when using _orm.Query using the _sql.select() construct directly. A new system by which ORM "plugins" may establish themselves within a Core _sql.Select allow the majority of query building logic previously inside of _orm.Query to now take place within a compilation-level extension for _sql.Select. Similar changes have been made for the _sql.Update and _sql.Delete constructs as well. The constructs when invoked using _orm.Session.execute() now do ORM-related work within the method. For _sql.Select, the _engine.Result object returned now contains ORM-level entities and results.

  References: [#5159](http://www.sqlalchemy.org/trac/ticket/5159)

• [orm] [feature] Added the ability to add arbitrary criteria to the ON clause generated by a relationship attribute in a query, which applies to methods such as _query.Query.join() as well as loader options like _orm.joinedload(). Additionally, a "global" version of the option allows limiting criteria to be applied to particular entities in a query globally.

  References: [#4472](http://www.sqlalchemy.org/trac/ticket/4472)

• [orm] [feature] The ORM Declarative system is now unified into the ORM itself, with new import spaces under sqlalchemy.orm and new kinds of mappings. Support for decorator-based mappings without using a base class, support for classical style-mapper() calls that have access to the declarative class registry for relationships, and full integration of Declarative with 3rd party class attribute systems like dataclasses and attrs is now supported.

  References: [#5508](http://www.sqlalchemy.org/trac/ticket/5508)

• [orm] [feature] Eager loaders, such as joined loading, SELECT IN loading, etc., when configured on a mapper or via query options will now be invoked during the refresh on an expired object; in the case of selectinload and subqueryload, since the additional load is for a single object only, the "immediateload" scheme is used in these cases which resembles the single-parent query emitted by lazy loading.

  References: [#1763](http://www.sqlalchemy.org/trac/ticket/1763)

• [orm] [feature] Added support for direct mapping of Python classes that are defined using the Python dataclasses decorator. Pull request courtesy Václav Klusák. The new feature integrates into new support at the Declarative level for systems such as dataclasses and attrs.

  References: [#5027](http://www.sqlalchemy.org/trac/ticket/5027)

• [orm] [feature] Added "raiseload" feature for ORM mapped columns via orm.defer.raiseload parameter on defer() and deferred(). This provides similar behavior for column-expression mapped attributes as the raiseload() option does for relationship mapped attributes. The change also includes some behavioral changes to deferred columns regarding expiration; see the migration notes for details.

  References: [#4826](http://www.sqlalchemy.org/trac/ticket/4826)

• [orm] [usecase] The evaluator that takes place within the ORM bulk update and delete for synchronize_session="evaluate" now supports the IN and NOT IN operators. Tuple IN is also supported.

  References: [#1653](http://www.sqlalchemy.org/trac/ticket/1653)

• [orm] [usecase] Enhanced logic that tracks if relationships will be conflicting with each other when they write to the same column to include simple cases of two relationships that should have a "backref" between them. This means that if two relationships are not viewonly, are not linked with back_populates and are not otherwise in an inheriting sibling/overriding arrangement, and will populate the same foreign key column, a warning is emitted at mapper configuration time warning that a conflict may arise. A new parameter _orm.relationship.overlaps is added to suit those very rare cases where such an overlapping persistence arrangement may be unavoidable.

  References: [#5171](http://www.sqlalchemy.org/trac/ticket/5171)

• [orm] [usecase] The ORM bulk update and delete operations, historically available via the _orm.Query.update() and _orm.Query.delete() methods as well as via the _dml.Update and _dml.Delete constructs for 2.0 style execution, will now automatically accommodate for the additional WHERE criteria needed for a single-table inheritance discriminator in order to limit the statement to rows referring to the specific subtype requested. The new _orm.with_loader_criteria() construct is also supported for with bulk update/delete operations.

  Unknown interpreted text role "term".

  References: [#3903](http://www.sqlalchemy.org/trac/ticket/3903), [#5018](http://www.sqlalchemy.org/trac/ticket/5018)

• [orm] [usecase] Update _orm.relationship.sync_backref flag in a relationship to make it implicitly False in viewonly=True relationships, preventing synchronization events.

  References: [#5237](http://www.sqlalchemy.org/trac/ticket/5237)

• [orm] [change] The condition where a pending object being flushed with an identity that already exists in the identity map has been adjusted to emit a warning, rather than throw a FlushError. The rationale is so that the flush will proceed and raise a IntegrityError instead, in the same way as if the existing object were not present in the identity map already. This helps with schemes that are using the IntegrityError as a means of catching whether or not a row already exists in the table.

  References: [#4662](http://www.sqlalchemy.org/trac/ticket/4662)

• [orm] [change] [sql] A selection of Core and ORM query objects now perform much more of their Python computational tasks within the compile step, rather than at construction time. This is to support an upcoming caching model that will provide for caching of the compiled statement structure based on a cache key that is derived from the statement construct, which itself is expected to be newly constructed in Python code each time it is used. This means that the internal state of these objects may not be the same as it used to be, as well as that some but not all error raise scenarios for various kinds of argument validation will occur within the compilation / execution phase, rather than at statement construction time. See the migration notes linked below for complete details.

• [orm] [change] The automatic uniquing of rows on the client side is turned off for the new 2.0 style of ORM querying. This improves both clarity and performance. However, uniquing of rows on the client side is generally necessary when using joined eager loading for collections, as there will be duplicates of the primary entity for each element in the collection because a join was used. This uniquing must now be manually enabled and can be achieved using the new _engine.Result.unique() modifier. To avoid silent failure, the ORM explicitly requires the method be called when the result of an ORM query in 2.0 style makes use of joined load collections. The newer _orm.selectinload() strategy is likely preferable for eager loading of collections in any case.

  Unknown interpreted text role "term".

  References: [#4395](http://www.sqlalchemy.org/trac/ticket/4395)

• [orm] [change] The ORM will now warn when asked to coerce a _expression.select() construct into a subquery implicitly. This occurs within places such as the _query.Query.select_entity_from() and _query.Query.select_from() methods as well as within the with_polymorphic() function. When a _expression.SelectBase (which is what's produced by _expression.select()) or _query.Query object is passed directly to these functions and others, the ORM is typically coercing them to be a subquery by calling the _expression.SelectBase.alias() method automatically (which is now superseded by the _expression.SelectBase.subquery() method). See the migration notes linked below for further details.

  References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)

• [orm] [change] The "KeyedTuple" class returned by _query.Query is now replaced with the Core Row class, which behaves in the same way as KeyedTuple. In SQLAlchemy 2.0, both Core and ORM will return result rows using the same Row object. In the interim, Core uses a backwards-compatibility class LegacyRow that maintains the former mapping/tuple hybrid behavior used by "RowProxy".

  References: [#4710](http://www.sqlalchemy.org/trac/ticket/4710)

• [orm] [performance] The bulk update and delete methods Query.update() and Query.delete(), as well as their 2.0-style counterparts, now make use of RETURNING when the "fetch" strategy is used in order to fetch the list of affected primary key identites, rather than emitting a separate SELECT, when the backend in use supports RETURNING. Additionally, the "fetch" strategy will in ordinary cases not expire the attributes that have been updated, and will instead apply the updated values directly in the same way that the "evaluate" strategy does, to avoid having to refresh the object. The "evaluate" strategy will also fall back to expiring attributes that were updated to a SQL expression that was unevaluable in Python.

• [orm] [performance] [postgresql] Implemented support for the psycopg2 execute_values() extension within the ORM flush process via the enhancements to Core made in [#5401](http://www.sqlalchemy.org/trac/ticket/5401), so that this extension is used both as a strategy to batch INSERT statements together as well as that RETURNING may now be used among multiple parameter sets to retrieve primary key values back in batch. This allows nearly all INSERT statements emitted by the ORM on behalf of PostgreSQL to be submitted in batch and also via the execute_values() extension which benches at five times faster than plain executemany() for this particular backend.

  References: [#5263](http://www.sqlalchemy.org/trac/ticket/5263)

• [orm] [bug] A query that is against a mapped inheritance subclass which also uses _query.Query.select_entity_from() or a similar technique in order to provide an existing subquery to SELECT from, will now raise an error if the given subquery returns entities that do not correspond to the given subclass, that is, they are sibling or superclasses in the same hierarchy. Previously, these would be returned without error. Additionally, if the inheritance mapping is a single-inheritance mapping, the given subquery must apply the appropriate filtering against the polymorphic discriminator column in order to avoid this error; previously, the _query.Query would add this criteria to the outside query however this interferes with some kinds of query that return other kinds of entities as well.

  References: [#5122](http://www.sqlalchemy.org/trac/ticket/5122)

• [orm] [bug] The internal attribute symbols NO_VALUE and NEVER_SET have been unified, as there was no meaningful difference between these two symbols, other than a few codepaths where they were differentiated in subtle and undocumented ways, these have been fixed.

  References: [#4696](http://www.sqlalchemy.org/trac/ticket/4696)

• [orm] [bug] Fixed bug where a versioning column specified on a mapper against a _expression.select() construct where the version_id_col itself were against the underlying table would incur additional loads when accessed, even if the value were locally persisted by the flush. The actual fix is a result of the changes in [#4617](http://www.sqlalchemy.org/trac/ticket/4617), by fact that a _expression.select() object no longer has a .c attribute and therefore does not confuse the mapper into thinking there's an unknown column value present.

  References: [#4194](http://www.sqlalchemy.org/trac/ticket/4194)

• [orm] [bug] An UnmappedInstanceError is now raised for InstrumentedAttribute if an instance is an unmapped object. Prior to this an AttributeError was raised. Pull request courtesy Ramon Williams.

  References: [#3858](http://www.sqlalchemy.org/trac/ticket/3858)

• [orm] [bug] The Session object no longer initiates a SessionTransaction object immediately upon construction or after the previous transaction is closed; instead, "autobegin" logic now initiates the new SessionTransaction on demand when it is next needed. Rationale includes to remove reference cycles from a Session that has been closed out, as well as to remove the overhead incurred by the creation of SessionTransaction objects that are often discarded immediately. This change affects the behavior of the SessionEvents.after_transaction_create() hook in that the event will be emitted when the Session first requires a SessionTransaction be present, rather than whenever the Session were created or the previous SessionTransaction were closed. Interactions with the _engine.Engine and the database itself remain unaffected.

  References: [#5074](http://www.sqlalchemy.org/trac/ticket/5074)

• [orm] [bug] Added new entity-targeting capabilities to the ORM query context help with the case where the Session is using a bind dictionary against mapped classes, rather than a single bind, and the _query.Query is against a Core statement that was ultimately generated from a method such as _query.Query.subquery(). First implemented using a deep search, the current approach leverages the unified _sql.select() construct to keep track of the first mapper that is part of the construct.

  References: [#4829](http://www.sqlalchemy.org/trac/ticket/4829)

• [orm] [bug] [inheritance] An ArgumentError is now raised if both the selectable and flat parameters are set to True in orm.with_polymorphic(). The selectable name is already aliased and applying flat=True overrides the selectable name with an anonymous name that would've previously caused the code to break. Pull request courtesy Ramon Williams.

  References: [#4212](http://www.sqlalchemy.org/trac/ticket/4212)

• [orm] [bug] Fixed issue in polymorphic loading internals which would fall back to a more expensive, soon-to-be-deprecated form of result column lookup within certain unexpiration scenarios in conjunction with the use of "with_polymorphic".

  References: [#4718](http://www.sqlalchemy.org/trac/ticket/4718)

• [orm] [bug] An error is raised if any persistence-related "cascade" settings are made on a _orm.relationship() that also sets up viewonly=True. The "cascade" settings now default to non-persistence related settings only when viewonly is also set. This is the continuation from [#4993](http://www.sqlalchemy.org/trac/ticket/4993) where this setting was changed to emit a warning in 1.3.

  References: [#4994](http://www.sqlalchemy.org/trac/ticket/4994)

• [orm] [bug] Improved declarative inheritance scanning to not get tripped up when the same base class appears multiple times in the base inheritance list.

  References: [#4699](http://www.sqlalchemy.org/trac/ticket/4699)

• [orm] [bug] Fixed bug in ORM versioning feature where assignment of an explicit version_id for a counter configured against a mapped selectable where version_id_col is against the underlying table would fail if the previous value were expired; this was due to the fact that the mapped attribute would not be configured with active_history=True.

  References: [#4195](http://www.sqlalchemy.org/trac/ticket/4195)

• [orm] [bug] An exception is now raised if the ORM loads a row for a polymorphic instance that has a primary key but the discriminator column is NULL, as discriminator columns should not be null.

  References: [#4836](http://www.sqlalchemy.org/trac/ticket/4836)

• [orm] [bug] Accessing a collection-oriented attribute on a newly created object no longer mutates __dict__, but still returns an empty collection as has always been the case. This allows collection-oriented attributes to work consistently in comparison to scalar attributes which return None, but also don't mutate __dict__. In order to accommodate for the collection being mutated, the same empty collection is returned each time once initially created, and when it is mutated (e.g. an item appended, added, etc.) it is then moved into __dict__. This removes the last of mutating side-effects on read-only attribute access within the ORM.

  References: [#4519](http://www.sqlalchemy.org/trac/ticket/4519)

• [orm] [bug] The refresh of an expired object will now trigger an autoflush if the list of expired attributes include one or more attributes that were explicitly expired or refreshed using the Session.expire() or Session.refresh() methods. This is an attempt to find a middle ground between the normal unexpiry of attributes that can happen in many cases where autoflush is not desirable, vs. the case where attributes are being explicitly expired or refreshed and it is possible that these attributes depend upon other pending state within the session that needs to be flushed. The two methods now also gain a new flag Session.expire.autoflush and Session.refresh.autoflush, defaulting to True; when set to False, this will disable the autoflush that occurs on unexpire for these attributes.

  References: [#5226](http://www.sqlalchemy.org/trac/ticket/5226)

• [orm] [bug] The behavior of the _orm.relationship.cascade_backrefs flag will be reversed in 2.0 and set to False unconditionally, such that backrefs don't cascade save-update operations from a forwards-assignment to a backwards assignment. A 2.0 deprecation warning is emitted when the parameter is left at its default of True at the point at which such a cascade operation actually takes place. The new behavior can be established as always by setting the flag to False on a specific _orm.relationship(), or more generally can be set up across the board by setting the the _orm.Session.future flag to True.

  References: [#5150](http://www.sqlalchemy.org/trac/ticket/5150)

• [orm] [deprecated] The "slice index" feature used by _orm.Query as well as by the dynamic relationship loader will no longer accept negative indexes in SQLAlchemy 2.0. These operations do not work efficiently and load the entire collection in, which is both surprising and undesirable. These will warn in 1.4 unless the _orm.Session.future flag is set in which case they will raise IndexError.

  References: [#5606](http://www.sqlalchemy.org/trac/ticket/5606)

• [orm] [deprecated] Calling the _query.Query.instances() method without passing a QueryContext is deprecated. The original use case for this was that a _query.Query could yield ORM objects when given only the entities to be selected as well as a DBAPI cursor object. However, for this to work correctly there is essential metadata that is passed from a SQLAlchemy _engine.ResultProxy that is derived from the mapped column expressions, which comes originally from the QueryContext. To retrieve ORM results from arbitrary SELECT statements, the _query.Query.from_statement() method should be used.

  References: [#4719](http://www.sqlalchemy.org/trac/ticket/4719)

• [orm] [deprecated] Using strings to represent relationship names in ORM operations such as _orm.Query.join(), as well as strings for all ORM attribute names in loader options like _orm.selectinload() is deprecated and will be removed in SQLAlchemy 2.0. The class-bound attribute should be passed instead. This provides much better specificity to the given method, allows for modifiers such as of_type(), and reduces internal complexity.

  Additionally, the aliased and from_joinpoint parameters to _orm.Query.join() are also deprecated. The _orm.aliased() construct now provides for a great deal of flexibility and capability and should be used directly.

  References: [#4705](http://www.sqlalchemy.org/trac/ticket/4705), [#5202](http://www.sqlalchemy.org/trac/ticket/5202)

• [orm] [deprecated] Deprecated logic in _query.Query.distinct() that automatically adds columns in the ORDER BY clause to the columns clause; this will be removed in 2.0.

  References: [#5134](http://www.sqlalchemy.org/trac/ticket/5134)

• [orm] [deprecated] Passing keyword arguments to methods such as _orm.Session.execute() to be passed into the _orm.Session.get_bind() method is deprecated; the new _orm.Session.execute.bind_arguments dictionary should be passed instead.

  References: [#5573](http://www.sqlalchemy.org/trac/ticket/5573)

• [orm] [deprecated] The eagerload() and relation() were old aliases and are now deprecated. Use _orm.joinedload() and _orm.relationship() respectively.

  References: [#5192](http://www.sqlalchemy.org/trac/ticket/5192)

• [orm] [removed] All long-deprecated "extension" classes have been removed, including MapperExtension, SessionExtension, PoolListener, ConnectionProxy, AttributeExtension. These classes have been deprecated since version 0.7 long superseded by the event listener system.

  References: [#4638](http://www.sqlalchemy.org/trac/ticket/4638)

• [orm] [removed] Remove the deprecated loader options joinedload_all, subqueryload_all, lazyload_all, selectinload_all. The normal version with method chaining should be used in their place.

  References: [#4642](http://www.sqlalchemy.org/trac/ticket/4642)

• [orm] [removed] Remove deprecated function comparable_property. Please refer to the ~sqlalchemy.ext.hybrid extension. This also removes the function comparable_using in the declarative extension.

  Remove deprecated function compile_mappers. Please use configure_mappers()

  Remove deprecated method collection.linker. Please refer to the AttributeEvents.init_collection() and AttributeEvents.dispose_collection() event handlers.

  Remove deprecated method Session.prune and parameter Session.weak_identity_map. See the recipe at session_referencing_behavior for an event-based approach to maintaining strong identity references. This change also removes the class StrongInstanceDict.

  Remove deprecated parameter mapper.order_by. Use _query.Query.order_by() to determine the ordering of a result set.

  Remove deprecated parameter Session._enable_transaction_accounting.

  Remove deprecated parameter Session.is_modified.passive.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

## engine

• [engine] [feature] Implemented an all-new _result.Result object that replaces the previous ResultProxy object. As implemented in Core, the subclass _result.CursorResult features a compatible calling interface with the previous ResultProxy, and additionally adds a great amount of new functionality that can be applied to Core result sets as well as ORM result sets, which are now integrated into the same model. _result.Result includes features such as column selection and rearrangement, improved fetchmany patterns, uniquing, as well as a variety of implementations that can be used to create database results from in-memory structures as well.

  References: [#4395](http://www.sqlalchemy.org/trac/ticket/4395), [#4959](http://www.sqlalchemy.org/trac/ticket/4959), [#5087](http://www.sqlalchemy.org/trac/ticket/5087)

• [engine] [feature] [orm] SQLAlchemy now includes support for Python asyncio within both Core and ORM, using the included asyncio extension <asyncio_toplevel>. The extension makes use of the [greenlet](https://greenlet.readthedocs.io/en/latest/) library in order to adapt SQLAlchemy's sync-oriented internals such that an asyncio interface that ultimately interacts with an asyncio database adapter is now feasible. The single driver supported at the moment is the dialect-postgresql-asyncpg driver for PostgreSQL.

  References: [#3414](http://www.sqlalchemy.org/trac/ticket/3414)

• [engine] [feature] [alchemy2] Implemented the _sa.create_engine.future parameter which enables forwards compatibility with SQLAlchemy 2. is used for forwards compatibility with SQLAlchemy 2. This engine features always-transactional behavior with autobegin.

  References: [#4644](http://www.sqlalchemy.org/trac/ticket/4644)

• [engine] [feature] [pyodbc] Reworked the "setinputsizes()" set of dialect hooks to be correctly extensible for any arbirary DBAPI, by allowing dialects individual hooks that may invoke cursor.setinputsizes() in the appropriate style for that DBAPI. In particular this is intended to support pyodbc's style of usage which is fundamentally different from that of cx_Oracle. Added support for pyodbc.

  References: [#5649](http://www.sqlalchemy.org/trac/ticket/5649)

• [engine] [feature] Added new reflection method Inspector.get_sequence_names() which returns all the sequences defined and Inspector.has_sequence() to check if a particular sequence exits. Support for this method has been added to the backend that support Sequence: PostgreSQL, Oracle and MariaDB >= 10.3.

  References: [#2056](http://www.sqlalchemy.org/trac/ticket/2056)

• [engine] [feature] The _schema.Table.autoload_with parameter now accepts an _reflection.Inspector object directly, as well as any _engine.Engine or _engine.Connection as was the case before.

  References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)

• [engine] [change] The RowProxy class is no longer a "proxy" object, and is instead directly populated with the post-processed contents of the DBAPI row tuple upon construction. Now named Row, the mechanics of how the Python-level value processors have been simplified, particularly as it impacts the format of the C code, so that a DBAPI row is processed into a result tuple up front. The object returned by the _engine.ResultProxy is now the LegacyRow subclass, which maintains mapping/tuple hybrid behavior, however the base Row class now behaves more fully like a named tuple.

  References: [#4710](http://www.sqlalchemy.org/trac/ticket/4710)

• [engine] [performance] The pool "pre-ping" feature has been refined to not invoke for a DBAPI connection that was just opened in the same checkout operation. pre ping only applies to a DBAPI connection that's been checked into the pool and is being checked out again.

  References: [#4524](http://www.sqlalchemy.org/trac/ticket/4524)

• [engine] [performance] [change] [py3k] Disabled the "unicode returns" check that runs on dialect startup when running under Python 3, which for many years has occurred in order to test the current DBAPI's behavior for whether or not it returns Python Unicode or Py2K strings for the VARCHAR and NVARCHAR datatypes. The check still occurs by default under Python 2, however the mechanism to test the behavior will be removed in SQLAlchemy 2.0 when Python 2 support is also removed.

  This logic was very effective when it was needed, however now that Python 3 is standard, all DBAPIs are expected to return Python 3 strings for character datatypes. In the unlikely case that a third party DBAPI does not support this, the conversion logic within String is still available and the third party dialect may specify this in its upfront dialect flags by setting the dialect level flag returns_unicode_strings to one of String.RETURNS_CONDITIONAL or String.RETURNS_BYTES, both of which will enable Unicode conversion even under Python 3.

  References: [#5315](http://www.sqlalchemy.org/trac/ticket/5315)

• [engine] [bug] Revised the Connection.execution_options.schema_translate_map feature such that the processing of the SQL statement to receive a specific schema name occurs within the execution phase of the statement, rather than at the compile phase. This is to support the statement being efficiently cached. Previously, the current schema being rendered into the statement for a particular run would be considered as part of the cache key itself, meaning that for a run against hundreds of schemas, there would be hundreds of cache keys, rendering the cache much less performant. The new behavior is that the rendering is done in a similar manner as the "post compile" rendering added in 1.4 as part of [#4645](http://www.sqlalchemy.org/trac/ticket/4645), [#4808](http://www.sqlalchemy.org/trac/ticket/4808).

  References: [#5004](http://www.sqlalchemy.org/trac/ticket/5004)

• [engine] [bug] The _engine.Connection object will now not clear a rolled-back transaction until the outermost transaction is explicitly rolled back. This is essentially the same behavior that the ORM Session has had for a long time, where an explicit call to .rollback() on all enclosing transactions is required for the transaction to logically clear, even though the DBAPI-level transaction has already been rolled back. The new behavior helps with situations such as the "ORM rollback test suite" pattern where the test suite rolls the transaction back within the ORM scope, but the test harness which seeks to control the scope of the transaction externally does not expect a new transaction to start implicitly.

  References: [#4712](http://www.sqlalchemy.org/trac/ticket/4712)

• [engine] [bug] Adjusted the dialect initialization process such that the _engine.Dialect.on_connect() is not called a second time on the first connection. The hook is called first, then the _engine.Dialect.initialize() is called if that connection is the first for that dialect, then no more events are called. This eliminates the two calls to the "on_connect" function which can produce very difficult debugging situations.

  References: [#5497](http://www.sqlalchemy.org/trac/ticket/5497)

• [engine] [deprecated] The _engine.URL object is now an immutable named tuple. To modify a URL object, use the _engine.URL.set() method to produce a new URL object.

  References: [#5526](http://www.sqlalchemy.org/trac/ticket/5526)

• [engine] [deprecated] The _schema.MetaData.bind argument as well as the overall concept of "bound metadata" is deprecated in SQLAlchemy 1.4 and will be removed in SQLAlchemy 2.0. The parameter as well as related functions now emit a _exc.RemovedIn20Warning when deprecation_20_mode is in use.

  References: [#4634](http://www.sqlalchemy.org/trac/ticket/4634)

• [engine] [deprecated] The server_side_cursors engine-wide parameter is deprecated and will be removed in a future release. For unbuffered cursors, the _engine.Connection.execution_options.stream_results execution option should be used on a per-execution basis.

• [engine] [deprecated] The _engine.Connection.connect() method is deprecated as is the concept of "connection branching", which copies a _engine.Connection into a new one that has a no-op ".close()" method. This pattern is oriented around the "connectionless execution" concept which is also being removed in 2.0.

  References: [#5131](http://www.sqlalchemy.org/trac/ticket/5131)

• [engine] [deprecated] The case_sensitive flag on _sa.create_engine() is deprecated; this flag was part of the transition of the result row object to allow case sensitive column matching as the default, while providing backwards compatibility for the former matching method. All string access for a row should be assumed to be case sensitive just like any other Python mapping.

  References: [#4878](http://www.sqlalchemy.org/trac/ticket/4878)

• [engine] [deprecated] "Implicit autocommit", which is the COMMIT that occurs when a DML or DDL statement is emitted on a connection, is deprecated and won't be part of SQLAlchemy 2.0. A 2.0-style warning is emitted when autocommit takes effect, so that the calling code may be adjusted to use an explicit transaction.

  As part of this change, DDL methods such as _schema.MetaData.create_all() when used against an _engine.Engine will run the operation in a BEGIN block if one is not started already.

  References: [#4846](http://www.sqlalchemy.org/trac/ticket/4846)

• [engine] [deprecated] Deprecated the behavior by which a _schema.Column can be used as the key in a result set row lookup, when that _schema.Column is not part of the SQL selectable that is being selected; that is, it is only matched on name. A deprecation warning is now emitted for this case. Various ORM use cases, such as those involving _expression.text() constructs, have been improved so that this fallback logic is avoided in most cases.

  References: [#4877](http://www.sqlalchemy.org/trac/ticket/4877)

• [engine] [deprecated] Deprecated remaining engine-level introspection and utility methods including _engine.Engine.run_callable(), _engine.Engine.transaction(), _engine.Engine.table_names(), _engine.Engine.has_table(). The utility methods are superseded by modern context-manager patterns, and the table introspection tasks are suited by the _reflection.Inspector object.

  References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)

• [engine] [removed] Remove deprecated method get_primary_keys in the Dialect and _reflection.Inspector classes. Please refer to the Dialect.get_pk_constraint() and _reflection.Inspector.get_primary_keys() methods.

  Remove deprecated event dbapi_error and the method ConnectionEvents.dbapi_error. Please refer to the _events.ConnectionEvents.handle_error() event. This change also removes the attributes ExecutionContext.is_disconnect and ExecutionContext.exception.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

• [engine] [removed] The internal dialect method Dialect.reflecttable has been removed. A review of third party dialects has not found any making use of this method, as it was already documented as one that should not be used by external dialects. Additionally, the private Engine._run_visitor method is also removed.

  References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)

• [engine] [removed] The long-deprecated Inspector.get_table_names.order_by parameter has been removed.

  References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)

• [engine] [renamed] The _reflection.Inspector.reflecttable() was renamed to _reflection.Inspector.reflect_table().

  References: [#5244](http://www.sqlalchemy.org/trac/ticket/5244)

## sql

• [sql] [feature] Added "from linting" as a built-in feature to the SQL compiler. This allows the compiler to maintain graph of all the FROM clauses in a particular SELECT statement, linked by criteria in either the WHERE or in JOIN clauses that link these FROM clauses together. If any two FROM clauses have no path between them, a warning is emitted that the query may be producing a cartesian product. As the Core expression language as well as the ORM are built on an "implicit FROMs" model where a particular FROM clause is automatically added if any part of the query refers to it, it is easy for this to happen inadvertently and it is hoped that the new feature helps with this issue.

  References: [#4737](http://www.sqlalchemy.org/trac/ticket/4737)

• [sql] [feature] [mssql] [oracle] Added new "post compile parameters" feature. This feature allows a bindparam() construct to have its value rendered into the SQL string before being passed to the DBAPI driver, but after the compilation step, using the "literal render" feature of the compiler. The immediate rationale for this feature is to support LIMIT/OFFSET schemes that don't work or perform well as bound parameters handled by the database driver, while still allowing for SQLAlchemy SQL constructs to be cacheable in their compiled form. The immediate targets for the new feature are the "TOP N" clause used by SQL Server (and Sybase) which does not support a bound parameter, as well as the "ROWNUM" and optional "FIRST_ROWS()" schemes used by the Oracle dialect, the former of which has been known to perform better without bound parameters and the latter of which does not support a bound parameter. The feature builds upon the mechanisms first developed to support "expanding" parameters for IN expressions. As part of this feature, the Oracle use_binds_for_limits feature is turned on unconditionally and this flag is now deprecated.

  References: [#4808](http://www.sqlalchemy.org/trac/ticket/4808)

• [sql] [feature] Add support for regular expression on supported backends. Two operations have been defined:

  • _sql.ColumnOperators.regexp_match() implementing a regular expression match like function.

  • _sql.ColumnOperators.regexp_replace() implementing a regular expression string replace function.

  Supported backends include SQLite, PostgreSQL, MySQL / MariaDB, and Oracle.

  References: [#1390](http://www.sqlalchemy.org/trac/ticket/1390)

• [sql] [feature] The _expression.select() construct and related constructs now allow for duplication of column labels and columns themselves in the columns clause, mirroring exactly how column expressions were passed in. This allows the tuples returned by an executed result to match what was SELECTed for in the first place, which is how the ORM _query.Query works, so this establishes better cross-compatibility between the two constructs. Additionally, it allows column-positioning-sensitive structures such as UNIONs (i.e. _selectable.CompoundSelect) to be more intuitively constructed in those cases where a particular column might appear in more than one place. To support this change, the _expression.ColumnCollection has been revised to support duplicate columns as well as to allow integer index access.

  References: [#4753](http://www.sqlalchemy.org/trac/ticket/4753)

• [sql] [feature] Enhanced the disambiguating labels feature of the _expression.select() construct such that when a select statement is used in a subquery, repeated column names from different tables are now automatically labeled with a unique label name, without the need to use the full "apply_labels()" feature that combines tablename plus column name. The disambiguated labels are available as plain string keys in the .c collection of the subquery, and most importantly the feature allows an ORM _orm.aliased() construct against the combination of an entity and an arbitrary subquery to work correctly, targeting the correct columns despite same-named columns in the source tables, without the need for an "apply labels" warning.

  References: [#5221](http://www.sqlalchemy.org/trac/ticket/5221)

• [sql] [feature] The "expanding IN" feature, which generates IN expressions at query execution time which are based on the particular parameters associated with the statement execution, is now used for all IN expressions made against lists of literal values. This allows IN expressions to be fully cacheable independently of the list of values being passed, and also includes support for empty lists. For any scenario where the IN expression contains non-literal SQL expressions, the old behavior of pre-rendering for each position in the IN is maintained. The change also completes support for expanding IN with tuples, where previously type-specific bind processors weren't taking effect.

  References: [#4645](http://www.sqlalchemy.org/trac/ticket/4645)

• [sql] [feature] Along with the new transparent statement caching feature introduced as part of [#4369](http://www.sqlalchemy.org/trac/ticket/4369), a new feature intended to decrease the Python overhead of creating statements is added, allowing lambdas to be used when indicating arguments being passed to a statement object such as select(), Query(), update(), etc., as well as allowing the construction of full statements within lambdas in a similar manner as that of the "baked query" system. The rationale of using lambdas is adapted from that of the "baked query" approach which uses lambdas to encapsulate any amount of Python code into a callable that only needs to be called when the statement is first constructed into a string. The new feature however is more sophisticated in that Python literal values that would be passed as parameters are automatically extracted, so that there is no longer a need to use bindparam() objects with such queries. Use of the feature is optional and can be used to as small or as great a degree as is desired, while still allowing statements to be fully cacheable.

  References: [#5380](http://www.sqlalchemy.org/trac/ticket/5380)

• [sql] [usecase] The Index.create() and Index.drop() methods now have a parameter Index.create.checkfirst, in the same way as that of _schema.Table and Sequence, which when enabled will cause the operation to detect if the index exists (or not) before performing a create or drop operation.

  References: [#527](http://www.sqlalchemy.org/trac/ticket/527)

• [sql] [usecase] The true() and false() operators may now be applied as the "onclause" of a _expression.join() on a backend that does not support "native boolean" expressions, e.g. Oracle or SQL Server, and the expression will render as "1=1" for true and "1=0" false. This is the behavior that was introduced many years ago in [#2804](http://www.sqlalchemy.org/trac/ticket/2804) for and/or expressions.

• [sql] [usecase] Change the method __str of ColumnCollection to avoid confusing it with a python list of string.

  References: [#5191](http://www.sqlalchemy.org/trac/ticket/5191)

• [sql] [usecase] Add support to FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES} in the select for the supported backends, currently PostgreSQL, Oracle and MSSQL.

  References: [#5576](http://www.sqlalchemy.org/trac/ticket/5576)

• [sql] [usecase] Additional logic has been added such that certain SQL expressions which typically wrap a single database column will use the name of that column as their "anonymous label" name within a SELECT statement, potentially making key-based lookups in result tuples more intuitive. The primary example of this is that of a CAST expression, e.g. CAST(table.colname AS INTEGER), which will export its default name as "colname", rather than the usual "anon_1" label, that is, CAST(table.colname AS INTEGER) AS colname. If the inner expression doesn't have a name, then the previous "anonymous label" logic is used. When using SELECT statements that make use of _expression.Select.apply_labels(), such as those emitted by the ORM, the labeling logic will produce <tablename>_<inner column name> in the same was as if the column were named alone. The logic applies right now to the cast() and type_coerce() constructs as well as some single-element boolean expressions.

  References: [#4449](http://www.sqlalchemy.org/trac/ticket/4449)

• [sql] [change] The "clause coercion" system, which is SQLAlchemy Core's system of receiving arguments and resolving them into _expression.ClauseElement structures in order to build up SQL expression objects, has been rewritten from a series of ad-hoc functions to a fully consistent class-based system. This change is internal and should have no impact on end users other than more specific error messages when the wrong kind of argument is passed to an expression object, however the change is part of a larger set of changes involving the role and behavior of _expression.select() objects.

  References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)

• [sql] [change] Added a core Values object that enables a VALUES construct to be used in the FROM clause of an SQL statement for databases that support it (mainly PostgreSQL and SQL Server).

  References: [#4868](http://www.sqlalchemy.org/trac/ticket/4868)

• [sql] [change] The _expression.select() construct is moving towards a new calling form that is select(col1, col2, col3, ..), with all other keyword arguments removed, as these are all suited using generative methods. The single list of column or table arguments passed to select() is still accepted, however is no longer necessary if expressions are passed in a simple positional style. Other keyword arguments are disallowed when this form is used.

  References: [#5284](http://www.sqlalchemy.org/trac/ticket/5284)

• [sql] [change] As part of the SQLAlchemy 2.0 migration project, a conceptual change has been made to the role of the _expression.SelectBase class hierarchy, which is the root of all "SELECT" statement constructs, in that they no longer serve directly as FROM clauses, that is, they no longer subclass _expression.FromClause. For end users, the change mostly means that any placement of a _expression.select() construct in the FROM clause of another _expression.select() requires first that it be wrapped in a subquery first, which historically is through the use of the _expression.SelectBase.alias() method, and is now also available through the use of _expression.SelectBase.subquery(). This was usually a requirement in any case since several databases don't accept unnamed SELECT subqueries in their FROM clause in any case.

  References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)

• [sql] [change] Added a new Core class Subquery, which takes the place of _expression.Alias when creating named subqueries against a _expression.SelectBase object. Subquery acts in the same way as _expression.Alias and is produced from the _expression.SelectBase.subquery() method; for ease of use and backwards compatibility, the _expression.SelectBase.alias() method is synonymous with this new method.

  References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)

• [sql] [performance] An all-encompassing reorganization and refactoring of Core and ORM internals now allows all Core and ORM statements within the areas of DQL (e.g. SELECTs) and DML (e.g. INSERT, UPDATE, DELETE) to allow their SQL compilation as well as the construction of result-fetching metadata to be fully cached in most cases. This effectively provides a transparent and generalized version of what the "Baked Query" extension has offered for the ORM in past versions. The new feature can calculate the cache key for any given SQL construction based on the string that it would ultimately produce for a given dialect, allowing functions that compose the equivalent select(), Query(), insert(), update() or delete() object each time to have that statement cached after it's generated the first time.

  The feature is enabled transparently but includes some new programming paradigms that may be employed to make the caching even more efficient.

  References: [#4639](http://www.sqlalchemy.org/trac/ticket/4639)

• [sql] [bug] Fixed issue where when constructing constraints from ORM-bound columns, primarily _schema.ForeignKey objects but also UniqueConstraint, CheckConstraint and others, the ORM-level InstrumentedAttribute is discarded entirely, and all ORM-level annotations from the columns are removed; this is so that the constraints are still fully pickleable without the ORM-level entities being pulled in. These annotations are not necessary to be present at the schema/metadata level.

  References: [#5001](http://www.sqlalchemy.org/trac/ticket/5001)

• [sql] [bug] Registered function names based on GenericFunction are now retrieved in a case-insensitive fashion in all cases, removing the deprecation logic from 1.3 which temporarily allowed multiple GenericFunction objects to exist with differing cases. A GenericFunction that replaces another on the same name whether or not it's case sensitive emits a warning before replacing the object.

  References: [#4569](http://www.sqlalchemy.org/trac/ticket/4569), [#4649](http://www.sqlalchemy.org/trac/ticket/4649)

• [sql] [bug] Creating an and_() or or_() construct with no arguments or empty *args will now emit a deprecation warning, as the SQL produced is a no-op (i.e. it renders as a blank string). This behavior is considered to be non-intuitive, so for empty or possibly empty and_() or or_() constructs, an appropriate default boolean should be included, such as and_(True, *args) or or_(False, *args). As has been the case for many major versions of SQLAlchemy, these particular boolean values will not render if the *args portion is non-empty.

  References: [#5054](http://www.sqlalchemy.org/trac/ticket/5054)

• [sql] [bug] Improved the _sql.tuple_() construct such that it behaves predictably when used in a columns-clause context. The SQL tuple is not supported as a "SELECT" columns clause element on most backends; on those that do (PostgreSQL, not surprisingly), the Python DBAPI does not have a "nested type" concept so there are still challenges in fetching rows for such an object. Use of _sql.tuple_() in a _sql.select() or _orm.Query will now raise a _exc.CompileError at the point at which the _sql.tuple_() object is seen as presenting itself for fetching rows (i.e., if the tuple is in the columns clause of a subquery, no error is raised). For ORM use,the _orm.Bundle object is an explicit directive that a series of columns should be returned as a sub-tuple per row and is suggested by the error message. Additionally ,the tuple will now render with parenthesis in all contexts. Previously, the parenthesization would not render in a columns context leading to non-defined behavior.

  References: [#5127](http://www.sqlalchemy.org/trac/ticket/5127)

• [sql] [bug] [postgresql] Improved support for column names that contain percent signs in the string, including repaired issues involving anoymous labels that also embedded a column name with a percent sign in it, as well as re-established support for bound parameter names with percent signs embedded on the psycopg2 dialect, using a late-escaping process similar to that used by the cx_Oracle dialect.

  References: [#5653](http://www.sqlalchemy.org/trac/ticket/5653)

• [sql] [bug] Custom functions that are created as subclasses of FunctionElement will now generate an "anonymous label" based on the "name" of the function just like any other Function object, e.g. "SELECT myfunc() AS myfunc_1". While SELECT statements no longer require labels in order for the result proxy object to function, the ORM still targets columns in rows by using objects as mapping keys, which works more reliably when the column expressions have distinct names. In any case, the behavior is now made consistent between functions generated by func and those generated as custom FunctionElement objects.

  References: [#4887](http://www.sqlalchemy.org/trac/ticket/4887)

• [sql] [bug] Reworked the _expression.ClauseElement.compare() methods in terms of a new visitor-based approach, and additionally added test coverage ensuring that all _expression.ClauseElement subclasses can be accurately compared against each other in terms of structure. Structural comparison capability is used to a small degree within the ORM currently, however it also may form the basis for new caching features.

  References: [#4336](http://www.sqlalchemy.org/trac/ticket/4336)

• [sql] [bug] Deprecate usage of DISTINCT ON in dialect other than PostgreSQL. Deprecate old usage of string distinct in MySQL dialect

  References: [#4002](http://www.sqlalchemy.org/trac/ticket/4002)

• [sql] [bug] The ORDER BY clause of a _selectable.CompoundSelect, e.g. UNION, EXCEPT, etc. will not render the table name associated with a given column when applying _selectable.CompoundSelect.order_by() in terms of a _schema.Table - bound column. Most databases require that the names in the ORDER BY clause be expressed as label names only which are matched to names in the first SELECT statement. The change is related to [#4617](http://www.sqlalchemy.org/trac/ticket/4617) in that a previous workaround was to refer to the .c attribute of the _selectable.CompoundSelect in order to get at a column that has no table name. As the subquery is now named, this change allows both the workaround to continue to work, as well as allows table-bound columns as well as the _selectable.CompoundSelect.selected_columns collections to be usable in the _selectable.CompoundSelect.order_by() method.

  References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)

• [sql] [bug] The _expression.Join construct no longer considers the "onclause" as a source of additional FROM objects to be omitted from the FROM list of an enclosing _expression.Select object as standalone FROM objects. This applies to an ON clause that includes a reference to another FROM object outside the JOIN; while this is usually not correct from a SQL perspective, it's also incorrect for it to be omitted, and the behavioral change makes the _expression.Select / _expression.Join behave a bit more intuitively.

  References: [#4621](http://www.sqlalchemy.org/trac/ticket/4621)

• [sql] [deprecated] The _sql.Join.alias() method is deprecated and will be removed in SQLAlchemy 2.0. An explicit select + subquery, or aliasing of the inner tables, should be used instead.

  References: [#5010](http://www.sqlalchemy.org/trac/ticket/5010)

• [sql] [deprecated] The _schema.Table class now raises a deprecation warning when columns with the same name are defined. To replace a column a new parameter _schema.Table.append_column.replace_existing was added to the _schema.Table.append_column() method.

  The _expression.ColumnCollection.contains_column() will now raises an error when called with a string, suggesting the caller to use in instead.

• [sql] [removed] The "threadlocal" execution strategy, deprecated in 1.3, has been removed for 1.4, as well as the concept of "engine strategies" and the Engine.contextual_connect method. The "strategy='mock'" keyword argument is still accepted for now with a deprecation warning; use create_mock_engine() instead for this use case.

  References: [#4632](http://www.sqlalchemy.org/trac/ticket/4632)

• [sql] [removed] Removed the sqlalchemy.sql.visitors.iterate_depthfirst and sqlalchemy.sql.visitors.traverse_depthfirst functions. These functions were unused by any part of SQLAlchemy. The _sa.sql.visitors.iterate() and _sa.sql.visitors.traverse() functions are commonly used for these functions. Also removed unused options from the remaining functions including "column_collections", "schema_visitor".

• [sql] [removed] Removed the concept of a bound engine from the Compiler object, and removed the .execute() and .scalar() methods from Compiler. These were essentially forgotten methods from over a decade ago and had no practical use, and it's not appropriate for the Compiler object itself to be maintaining a reference to an _engine.Engine.

• [sql] [removed] Remove deprecated methods Compiled.compile, ClauseElement.__and__ and ClauseElement.__or__ and attribute Over.func.

  Remove deprecated FromClause.count method. Please use the _functions.count function available from the func namespace.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

• [sql] [removed] Remove deprecated parameters text.bindparams and text.typemap. Please refer to the _expression.TextClause.bindparams() and _expression.TextClause.columns() methods.

  Remove deprecated parameter Table.useexisting. Please use _schema.Table.extend_existing.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

• [sql] [renamed] _schema.Table parameter mustexist has been renamed to _schema.Table.must_exist and will now warn when used.

• [sql] [renamed] The _expression.SelectBase.as_scalar() and _query.Query.as_scalar() methods have been renamed to _expression.SelectBase.scalar_subquery() and _query.Query.scalar_subquery(), respectively. The old names continue to exist within 1.4 series with a deprecation warning. In addition, the implicit coercion of _expression.SelectBase, _expression.Alias, and other SELECT oriented objects into scalar subqueries when evaluated in a column context is also deprecated, and emits a warning that the _expression.SelectBase.scalar_subquery() method should be called explicitly. This warning will in a later major release become an error, however the message will always be clear when _expression.SelectBase.scalar_subquery() needs to be invoked. The latter part of the change is for clarity and to reduce the implicit decisionmaking by the query coercion system. The Subquery.as_scalar() method, which was previously Alias.as_scalar, is also deprecated; .scalar_subquery() should be invoked directly from ` _expression.select() object or _query.Query object.

  This change is part of the larger change to convert _expression.select() objects to no longer be directly part of the "from clause" class hierarchy, which also includes an overhaul of the clause coercion system.

  References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)

• [sql] [renamed] Several operators are renamed to achieve more consistent naming across SQLAlchemy.

  The operator changes are:

  • isfalse is now is_false

  • isnot_distinct_from is now is_not_distinct_from

  • istrue is now is_true

  • notbetween is now not_between

  • notcontains is now not_contains

  • notendswith is now not_endswith

  • notilike is now not_ilike

  • notlike is now not_like

  • notmatch is now not_match

  • notstartswith is now not_startswith

  • nullsfirst is now nulls_first

  • nullslast is now nulls_last

  • isnot is now is_not

  • not_in_ is now not_in

  Because these are core operators, the internal migration strategy for this change is to support legacy terms for an extended period of time -- if not indefinitely -- but update all documentation, tutorials, and internal usage to the new terms. The new terms are used to define the functions, and the legacy terms have been deprecated into aliases of the new terms.

  References: [#5429](http://www.sqlalchemy.org/trac/ticket/5429), [#5435](http://www.sqlalchemy.org/trac/ticket/5435)

• [sql] [postgresql] Allow specifying the data type when creating a Sequence in PostgreSQL by using the parameter Sequence.data_type.

  References: [#5498](http://www.sqlalchemy.org/trac/ticket/5498)

• [sql] [reflection] The "NO ACTION" keyword for foreign key "ON UPDATE" is now considered to be the default cascade for a foreign key on all supporting backends (SQlite, MySQL, PostgreSQL) and when detected is not included in the reflection dictionary; this is already the behavior for PostgreSQL and MySQL for all previous SQLAlchemy versions in any case. The "RESTRICT" keyword is positively stored when detected; PostgreSQL does report on this keyword, and MySQL as of version 8.0 does as well. On earlier MySQL versions, it is not reported by the database.

  References: [#4741](http://www.sqlalchemy.org/trac/ticket/4741)

• [sql] [reflection] Added support for reflecting "identity" columns, which are now returned as part of the structure returned by _reflection.Inspector.get_columns(). When reflecting full _schema.Table objects, identity columns will be represented using the _schema.Identity construct. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).

  References: [#5324](http://www.sqlalchemy.org/trac/ticket/5324), [#5527](http://www.sqlalchemy.org/trac/ticket/5527)

## schema

• [schema] [change] The Enum.create_constraint and Boolean.create_constraint parameters now default to False, indicating when a so-called "non-native" version of these two datatypes is created, a CHECK constraint will not be generated by default. These CHECK constraints present schema-management maintenance complexities that should be opted in to, rather than being turned on by default.

  References: [#5367](http://www.sqlalchemy.org/trac/ticket/5367)

• [schema] [bug] Cleaned up the internal str() for datatypes so that all types produce a string representation without any dialect present, including that it works for third-party dialect types without that dialect being present. The string representation defaults to being the UPPERCASE name of that type with nothing else.

  References: [#4262](http://www.sqlalchemy.org/trac/ticket/4262)

• [schema] [removed] Remove deprecated class Binary. Please use LargeBinary.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

• [schema] [renamed] Renamed the _schema.Table.tometadata() method to _schema.Table.to_metadata(). The previous name remains with a deprecation warning.

  References: [#5413](http://www.sqlalchemy.org/trac/ticket/5413)

• [schema] [sql] Added the _schema.Identity construct that can be used to configure identity columns rendered with GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).

  References: [#5324](http://www.sqlalchemy.org/trac/ticket/5324), [#5360](http://www.sqlalchemy.org/trac/ticket/5360), [#5362](http://www.sqlalchemy.org/trac/ticket/5362)

## extensions

• [extensions] [usecase] Custom compiler constructs created using the sqlalchemy.ext.compiled extension will automatically add contextual information to the compiler when a custom construct is interpreted as an element in the columns clause of a SELECT statement, such that the custom element will be targetable as a key in result row mappings, which is the kind of targeting that the ORM uses in order to match column elements into result tuples.

  References: [#4887](http://www.sqlalchemy.org/trac/ticket/4887)

• [extensions] [change] Added new parameter _automap.AutomapBase.prepare.autoload_with which supersedes _automap.AutomapBase.prepare.reflect and _automap.AutomapBase.prepare.engine.

  References: [#5142](http://www.sqlalchemy.org/trac/ticket/5142)

## postgresql

• [postgresql] [usecase] Added support for PostgreSQL "readonly" and "deferrable" flags for all of psycopg2, asyncpg and pg8000 dialects. This takes advantage of a newly generalized version of the "isolation level" API to support other kinds of session attributes set via execution options that are reliably reset when connections are returned to the connection pool.

  References: [#5549](http://www.sqlalchemy.org/trac/ticket/5549)

• [postgresql] [usecase] The maximum buffer size for the BufferedRowResultProxy, which is used by dialects such as PostgreSQL when stream_results=True, can now be set to a number greater than 1000 and the buffer will grow to that size. Previously, the buffer would not go beyond 1000 even if the value were set larger. The growth of the buffer is also now based on a simple multiplying factor currently set to 5. Pull request courtesy Soumaya Mauthoor.

  References: [#4914](http://www.sqlalchemy.org/trac/ticket/4914)

• [postgresql] [change] When using the psycopg2 dialect for PostgreSQL, psycopg2 minimum version is set at 2.7. The psycopg2 dialect relies upon many features of psycopg2 released in the past few years, so to simplify the dialect, version 2.7, released in March, 2017 is now the minimum version required.

• [postgresql] [performance] The psycopg2 dialect now defaults to using the very performant execute_values() psycopg2 extension for compiled INSERT statements, and also implements RETURNING support when this extension is used. This allows INSERT statements that even include an autoincremented SERIAL or IDENTITY value to run very fast while still being able to return the newly generated primary key values. The ORM will then integrate this new feature in a separate change.

  References: [#5401](http://www.sqlalchemy.org/trac/ticket/5401)

• [postgresql] [bug] The pg8000 dialect has been revised and modernized for the most recent version of the pg8000 driver for PostgreSQL. Changes to the dialect include:

  • All data types are now sent as text rather than binary.

  • Using adapters, custom types can be plugged in to pg8000.

  • Previously, named prepared statements were used for all statements. Now unnamed prepared statements are used by default, and named prepared statements can be used explicitly by calling the Connection.prepare() method, which returns a PreparedStatement object.

  Pull request courtesy Tony Locke.

• [postgresql] [deprecated] The pygresql and py-postgresql dialects are deprecated.

  References: [#5189](http://www.sqlalchemy.org/trac/ticket/5189)

• [postgresql] [removed] Remove support for deprecated engine URLs of the form postgres://; this has emitted a warning for many years and projects should be using postgresql://.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

## mysql

• [mysql] [feature] Added support for MariaDB Connector/Python to the mysql dialect. Original pull request courtesy Georg Richter.

  References: [#5459](http://www.sqlalchemy.org/trac/ticket/5459)

• [mysql] [usecase] Added a new dialect token "mariadb" that may be used in place of "mysql" in the _sa.create_engine() URL. This will deliver a MariaDB dialect subclass of the MySQLDialect in use that forces the "is_mariadb" flag to True. The dialect will raise an error if a server version string that does not indicate MariaDB in use is received. This is useful for MariaDB-specific testing scenarios as well as to support applications that are hardcoding to MariaDB-only concepts. As MariaDB and MySQL featuresets and usage patterns continue to diverge, this pattern may become more prominent.

  References: [#5496](http://www.sqlalchemy.org/trac/ticket/5496)

• [mysql] [usecase] Added support for use of the Sequence construct with MariaDB 10.3 and greater, as this is now supported by this database. The construct integrates with the _schema.Table object in the same way that it does for other databases like PostgreSQL and Oracle; if is present on the integer primary key "autoincrement" column, it is used to generate defaults. For backwards compatibility, to support a _schema.Table that has a Sequence on it to support sequence only databases like Oracle, while still not having the sequence fire off for MariaDB, the optional=True flag should be set, which indicates the sequence should only be used to generate the primary key if the target database offers no other option.

  References: [#4976](http://www.sqlalchemy.org/trac/ticket/4976)

• [mysql] [bug] The MySQL and MariaDB dialects now query from the information_schema.tables system view in order to determine if a particular table exists or not. Previously, the "DESCRIBE" command was used with an exception catch to detect non-existent, which would have the undesirable effect of emitting a ROLLBACK on the connection. There appeared to be legacy encoding issues which prevented the use of "SHOW TABLES", for this, but as MySQL support is now at 5.0.2 or above due to [#4189](http://www.sqlalchemy.org/trac/ticket/4189), the information_schema tables are now available in all cases.

• [mysql] [bug] The "skip_locked" keyword used with with_for_update() will render "SKIP LOCKED" on all MySQL backends, meaning it will fail for MySQL less than version 8 and on current MariaDB backends. This is because those backends do not support "SKIP LOCKED" or any equivalent, so this error should not be silently ignored. This is upgraded from a warning in the 1.3 series.

  References: [#5568](http://www.sqlalchemy.org/trac/ticket/5568)

• [mysql] [bug] MySQL dialect's server_version_info tuple is now all numeric. String tokens like "MariaDB" are no longer present so that numeric comparison works in all cases. The .is_mariadb flag on the dialect should be consulted for whether or not mariadb was detected. Additionally removed structures meant to support extremely old MySQL versions 3.x and 4.x; the minimum MySQL version supported is now version 5.0.2.

  References: [#4189](http://www.sqlalchemy.org/trac/ticket/4189)

• [mysql] [deprecated] The OurSQL dialect is deprecated.

  References: [#5189](http://www.sqlalchemy.org/trac/ticket/5189)

• [mysql] [removed] Remove deprecated dialect mysql+gaerdbms that has been deprecated since version 1.0. Use the MySQLdb dialect directly.

  Remove deprecated parameter quoting from mysql.ENUM and mysql.SET in the mysql dialect. The values passed to the enum or the set are quoted by SQLAlchemy when needed automatically.

  References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)

## sqlite

• [sqlite] [change] Dropped support for right-nested join rewriting to support old SQLite versions prior to 3.7.16, released in 2013. It is expected that all modern Python versions among those now supported should all include much newer versions of SQLite.

  References: [#4895](http://www.sqlalchemy.org/trac/ticket/4895)

## mssql

• [mssql] [feature] [sql] Added support for the _types.JSON datatype on the SQL Server dialect using the _mssql.JSON implementation, which implements SQL Server's JSON functionality against the NVARCHAR(max) datatype as per SQL Server documentation. Implementation courtesy Gord Thompson.

  References: [#4384](http://www.sqlalchemy.org/trac/ticket/4384)

• [mssql] [feature] Added support for "CREATE SEQUENCE" and full Sequence support for Microsoft SQL Server. This removes the deprecated feature of using Sequence objects to manipulate IDENTITY characteristics which should now be performed using mssql_identity_start and mssql_identity_increment as documented at mssql_identity. The change includes a new parameter Sequence.data_type to accommodate SQL Server's choice of datatype, which for that backend includes INTEGER, BIGINT, and DECIMAL(n, 0). The default starting value for SQL Server's version of Sequence has been set at 1; this default is now emitted within the CREATE SEQUENCE DDL for all backends.

  References: [#4235](http://www.sqlalchemy.org/trac/ticket/4235), [#4633](http://www.sqlalchemy.org/trac/ticket/4633)

• [mssql] [usecase] [postgresql] [reflection] [schema] Improved support for covering indexes (with INCLUDE columns). Added the ability for postgresql to render CREATE INDEX statements with an INCLUDE clause from Core. Index reflection also report INCLUDE columns separately for both mssql and postgresql (11+).

  References: [#4458](http://www.sqlalchemy.org/trac/ticket/4458)

• [mssql] [usecase] [postgresql] Added support for inspection / reflection of partial indexes / filtered indexes, i.e. those which use the mssql_where or postgresql_where parameters, with _schema.Index. The entry is both part of the dictionary returned by Inspector.get_indexes() as well as part of a reflected _schema.Index construct that was reflected. Pull request courtesy Ramon Williams.

  References: [#4966](http://www.sqlalchemy.org/trac/ticket/4966)

• [mssql] [usecase] [reflection] Added support for reflection of temporary tables with the SQL Server dialect. Table names that are prefixed by a pound sign "#" are now introspected from the MSSQL "tempdb" system catalog.

  References: [#5506](http://www.sqlalchemy.org/trac/ticket/5506)

• [mssql] [change] SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather than using a window function, for SQL Server versions 11 and higher. TOP is

Note truncated.

1.3.24

Released: March 30, 2021

orm

• [orm] [bug] Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many cases placing this parameter on a many-to-one relationship is not what was intended, there are use cases where delete cascade may want to be disallowed following from such a relationship.

  References: #5983

• [orm] [bug] Fixed issue where the process of joining two tables could fail if one of the tables had an unrelated, unresolvable foreign key constraint which would raise _exc.NoReferenceError within the join process, which nonetheless could be bypassed to allow the join to complete. The logic which tested the exception for significance within the process would make assumptions about the construct which would fail.

  References: #5952

• [orm] [bug] Fixed issue where the _mutable.MutableComposite construct could be placed into an invalid state when the parent object was already loaded, and then covered by a subsequent query, due to the composite properties' refresh handler replacing the object with a new one not handled by the mutable extension.

  References: #6001

engine

• [engine] [bug] Fixed bug where the "schema_translate_map" feature failed to be taken into account for the use case of direct execution of _schema.DefaultGenerator objects such as sequences, which included the case where they were "pre-executed" in order to generate primary key values when implicit_returning was disabled.

  References: #5929

schema

• [schema] [bug] Fixed bug first introduced in as some combination of #2892, #2919 nnd #3832 where the attachment events for a _types.TypeDecorator would be doubled up against the "impl" class, if the "impl" were also a _types.SchemaType. The real-world case is any _types.TypeDecorator against _types.Enum or _types.Boolean would get a doubled _schema.CheckConstraint when the create_constraint=True flag is set.

  References: #6152

• [schema] [bug] [sqlite] Fixed issue where the CHECK constraint generated by _types.Boolean or _types.Enum would fail to render the naming convention correctly after the first compilation, due to an unintended change of state within the name given to the constraint. This issue was first introduced in 0.9 in the fix for issue #3067, and the fix revises the approach taken at that time which appears to have been more involved than what was needed.

  References: #6007

• [schema] [bug] Repaired / implemented support for primary key constraint naming conventions that use column names/keys/etc as part of the convention. In particular, this includes that the PrimaryKeyConstraint object that's automatically associated with a schema.Table will update its name as new primary key _schema.Column objects are added to the table and then to the constraint. Internal failure modes related to this constraint construction process including no columns present, no name present or blank name present are now accommodated.

  References: #5919

• [schema] [bug] Adjusted the logic that emits DROP statements for _schema.Sequence objects among the dropping of multiple tables, such that all _schema.Sequence objects are dropped after all tables, even if the given _schema.Sequence is related only to a _schema.Table object and not directly to the overall _schema.MetaData object. The use case supports the same _schema.Sequence being associated with more than one _schema.Table at a time.

  References: #6071

postgresql

• [postgresql] [bug] Fixed issue where using _postgresql.aggregate_order_by would return ARRAY(NullType) under certain conditions, interfering with the ability of the result object to return data correctly.

  References: #5989

• [postgresql] [bug] [reflection] Fixed issue in PostgreSQL reflection where a column expressing "NOT NULL" will supersede the nullability of a corresponding domain.

  References: #6161

• [postgresql] [bug] [types] Adjusted the psycopg2 dialect to emit an explicit PostgreSQL-style cast for bound parameters that contain ARRAY elements. This allows the full range of datatypes to function correctly within arrays. The asyncpg dialect already generated these internal casts in the final statement. This also includes support for array slice updates as well as the PostgreSQL-specific _postgresql.ARRAY.contains() method.

  References: #6023

mssql

• [mssql] [bug] [reflection] Fixed issue regarding SQL Server reflection for older SQL Server 2005 version, a call to sp_columns would not proceed correctly without being prefixed with the EXEC keyword. This method is not used in current 1.4 series.

  References: #5921

1.3.23

Released: February 1, 2021

sql

• [sql] [bug] Fixed bug where making use of the TypeEngine.with_variant() method on a TypeDecorator type would fail to take into account the dialect-specific mappings in use, due to a rule in TypeDecorator that was instead attempting to check for chains of TypeDecorator instances.

  References: #5816

postgresql

• [postgresql] [bug] For SQLAlchemy 1.3 only, setup.py pins pg8000 to a version lower than 1.16.6. Version 1.16.6 and above is supported by SQLAlchemy 1.4. Pull request courtesy Giuseppe Lumia.

  References: #5645

• [postgresql] [bug] Fixed issue where using _schema.Table.to_metadata() (called _schema.Table.tometadata() in 1.3) in conjunction with a PostgreSQL _postgresql.ExcludeConstraint that made use of ad-hoc column expressions would fail to copy correctly.

  References: #5850

mysql

• [mysql] [usecase] Casting to FLOAT is now supported in MySQL >= (8, 0, 17) and MariaDb >= (10, 4, 5).

  References: #5808

• [mysql] [bug] [reflection] Fixed bug where MySQL server default reflection would fail for numeric values with a negation symbol present.

  References: #5860

• [mysql] [bug] Fixed long-lived bug in MySQL dialect where the maximum identifier length of 255 was too long for names of all types of constraints, not just indexes, all of which have a size limit of 64. As metadata naming conventions can create too-long names in this area, apply the limit to the identifier generator within the DDL compiler.

  References: #5898

• [mysql] [bug] Fixed deprecation warnings that arose as a result of the release of PyMySQL 1.0, including deprecation warnings for the "db" and "passwd" parameters now replaced with "database" and "password".

  References: #5821

• [mysql] [bug] Fixed regression from SQLAlchemy 1.3.20 caused by the fix for #5462 which adds double-parenthesis for MySQL functional expressions in indexes, as is required by the backend, this inadvertently extended to include arbitrary _sql.text() expressions as well as Alembic's internal textual component, which are required by Alembic for arbitrary index expressions which don't imply double parenthesis. The check has been narrowed to include only binary/ unary/functional expressions directly.

  References: #5800

oracle

• [oracle] [bug] Fixed regression in Oracle dialect introduced by #4894 in SQLAlchemy 1.3.11 where use of a SQL expression in RETURNING for an UPDATE would fail to compile, due to a check for "server_default" when an arbitrary SQL expression is not a column.

  References: #5813

• [oracle] [bug] Fixed bug in Oracle dialect where retriving a CLOB/BLOB column via _dml.Insert.returning() would fail as the LOB value would need to be read when returned; additionally, repaired support for retrieval of Unicode values via RETURNING under Python 2.

  References: #5812

misc

• [bug] [ext] Fixed issue where the stringification that is sometimes called when attempting to generate the "key" for the .c collection on a selectable would fail if the column were an unlabeled custom SQL construct using the sqlalchemy.ext.compiler extension, and did not provide a default compilation form; while this seems like an unusual case, it can get invoked for some ORM scenarios such as when the expression is used in an "order by" in combination with joined eager loading. The issue is that the lack of a default compiler function was raising CompileError and not UnsupportedCompilationError.

  References: #5836

1.3.22

Released: December 18, 2020

oracle

• [oracle] [bug] Fixed regression which occured due to #5755 which implemented isolation level support for Oracle. It has been reported that many Oracle accounts don't actually have permission to query the v$transaction view so this feature has been altered to gracefully fallback when it fails upon database connect, where the dialect will assume "READ COMMITTED" is the default isolation level as was the case prior to SQLAlchemy 1.3.21. However, explicit use of the _engine.Connection.get_isolation_level() method must now necessarily raise an exception, as Oracle databases with this restriction explicitly disallow the user from reading the current isolation level.

  References: #5784

1.3.21

Released: December 17, 2020

orm

• [orm] [bug] Added a comprehensive check and an informative error message for the case where a mapped class, or a string mapped class name, is passed to _orm.relationship.secondary. This is an extremely common error which warrants a clear message.

  Additionally, added a new rule to the class registry resolution such that with regards to the _orm.relationship.secondary parameter, if a mapped class and its table are of the identical string name, the Table will be favored when resolving this parameter. In all other cases, the class continues to be favored if a class and table share the identical name.

  References: #5774

• [orm] [bug] Fixed bug in _query.Query.update() where objects in the _ormsession.Session that were already expired would be unnecessarily SELECTed individually when they were refreshed by the "evaluate"synchronize strategy.

  References: #5664

• [orm] [bug] Fixed bug involving the restore_load_context option of ORM events such as _ormevent.InstanceEvents.load() such that the flag would not be carried along to subclasses which were mapped after the event handler were first established.

  References: #5737

sql

• [sql] [bug] A warning is emmitted if a returning() method such as _sql.Insert.returning() is called multiple times, as this does not yet support additive operation. Version 1.4 will support additive operation for this. Additionally, any combination of the _sql.Insert.returning() and _sql.ValuesBase.return_defaults() methods now raises an error as these methods are mutually exclusive; previously the operation would fail silently.

  References: #5691

• [sql] [bug] Fixed structural compiler issue where some constructs such as MySQL / PostgreSQL "on conflict / on duplicate key" would rely upon the state of the _sql.Compiler object being fixed against their statement as the top level statement, which would fail in cases where those statements are branched from a different context, such as a DDL construct linked to a SQL statement.

  References: #5656

postgresql

• [postgresql] [usecase] Added new parameter _postgresql.ExcludeConstraint.ops to the _postgresql.ExcludeConstraint object, to support operator class specification with this constraint. Pull request courtesy Alon Menczer.

  References: #5604

• [postgresql] [bug] [mysql] Fixed regression introduced in 1.3.2 for the PostgreSQL dialect, also copied out to the MySQL dialect's feature in 1.3.18, where usage of a non _schema.Table construct such as _sql.text() as the argument to _sql.Select.with_for_update.of would fail to be accommodated correctly within the PostgreSQL or MySQL compilers.

  References: #5729

mysql

• [mysql] [bug] [reflection] Fixed issue where reflecting a server default on MariaDB only that contained a decimal point in the value would fail to be reflected correctly, leading towards a reflected table that lacked any server default.

  References: #5744

• [mysql] [sql] Added missing keywords to the RESERVED_WORDS list for the MySQL dialect: action, level, mode, status, text, time. Pull request courtesy Oscar Batori.

  References: #5696

sqlite

• [sqlite] [usecase] Added sqlite_with_rowid=False dialect keyword to enable creating tables as CREATE TABLE … WITHOUT ROWID. Patch courtesy Sean Anderson.

  References: #5685

mssql

• [mssql] [bug] Fixed bug where a CREATE INDEX statement was rendered incorrectly when both mssql-include and mssql_where were specified. Pull request courtesy @Adiorz.

  References: #5751

• [mssql] [bug] Added SQL Server code "01000" to the list of disconnect codes.

  References: #5646

• [mssql] [reflection] [sqlite] Fixed issue with composite primary key columns not being reported in the correct order. Patch courtesy @fulpm.

  References: #5661

oracle

• [oracle] [usecase] Implemented support for the SERIALIZABLE isolation level for Oracle databases, as well as a real implementation for _engine.Connection.get_isolation_level().

  References: #5755

1.3.20

Released: October 12, 2020

orm

• [orm] [bug] An ArgumentError with more detail is now raised if the target parameter for _query.Query.join() is set to an unmapped object. Prior to this change a less detailed AttributeError was raised. Pull request courtesy Ramon Williams.

  References: #4428

• [orm] [bug] Fixed issue where using a loader option against a string attribute name that is not actually a mapped attribute, such as a plain Python descriptor, would raise an uninformative AttributeError; a descriptive error is now raised.

  References: #4589

engine

• [engine] [bug] Fixed issue where a non-string object sent to _exc.SQLAlchemyError or a subclass, as occurs with some third party dialects, would fail to stringify correctly. Pull request courtesy Andrzej Bartosiński.

  References: #5599

• [engine] [bug] Repaired a function-level import that was not using SQLAlchemy's standard late-import system within the sqlalchemy.exc module.

  References: #5632

sql

• [sql] [bug] Fixed issue where the pickle.dumps() operation against _expression.Over construct would produce a recursion overflow.

  References: #5644

• [sql] [bug] Fixed bug where an error was not raised in the case where a _sql.column() were added to more than one _sql.table() at a time. This raised correctly for the _schema.Column and _schema.Table objects. An _exc.ArgumentError is now raised when this occurs.

  References: #5618

postgresql

• [postgresql] [usecase] The psycopg2 dialect now support PostgreSQL multiple host connections, by passing host/port combinations to the query string. Pull request courtesy Ramon Williams.

  References: #4392

• [postgresql] [bug] Adjusted the _types.ARRAY.Comparator.any() and _types.ARRAY.Comparator.all() methods to implement a straight "NOT" operation for negation, rather than negating the comparison operator.

  References: #5518

• [postgresql] [bug] Fixed issue where the _postgresql.ENUM type would not consult the schema translate map when emitting a CREATE TYPE or DROP TYPE during the test to see if the type exists or not. Additionally, repaired an issue where if the same enum were encountered multiple times in a single DDL sequence, the "check" query would run repeatedly rather than relying upon a cached value.

  References: #5520

mysql

• [mysql] [usecase] Adjusted the MySQL dialect to correctly parenthesize functional index expressions as accepted by MySQL 8. Pull request courtesy Ramon Williams.

  References: #5462

• [mysql] [bug] The "skip_locked" keyword used with with_for_update() will emit a warning when used on MariaDB backends, and will then be ignored. This is a deprecated behavior that will raise in SQLAlchemy 1.4, as an application that requests "skip locked" is looking for a non-blocking operation which is not available on those backends.

  References: #5568

• [mysql] [bug] Fixed bug where an UPDATE statement against a JOIN using MySQL multi-table format would fail to include the table prefix for the target table if the statement had no WHERE clause, as only the WHERE clause were scanned to detect a "multi table update" at that particular point. The target is now also scanned if it's a JOIN to get the leftmost table as the primary table and the additional entries as additional FROM entries.

  References: #5617

• [mysql] [change] Add new MySQL reserved words: cube, lateral added in MySQL 8.0.1 and 8.0.14, respectively; this indicates that these terms will be quoted if used as table or column identifier names.

  References: #5539

mssql

• [mssql] [bug] Fixed issue where a SQLAlchemy connection URI for Azure DW with authentication=ActiveDirectoryIntegrated (and no username+password) was not constructing the ODBC connection string in a way that was acceptable to the Azure DW instance.

  References: #5592

misc

• [bug] [pool] Fixed issue where the following pool parameters were not being propagated to the new pool created when _engine.Engine.dispose() were called: pre_ping, use_lifo. Additionally the recycle and reset_on_return parameter is now propagated for the _engine.AssertionPool class.

  References: #5582

• [bug] [associationproxy] [ext] An informative error is now raised when attempting to use an association proxy element as a plain column expression to be SELECTed from or used in a SQL function; this use case is not currently supported.

  References: #5541, #5542

• [bug] [tests] Fixed incompatibilities in the test suite when running against Pytest 6.x.

  References: #5635

1.3.19

Released: August 17, 2020

orm

• [orm] [usecase] Adjusted the workings of the _orm.Mapper.all_orm_descriptors() accessor to represent the attributes in the order that they are located in a deterministic way, assuming the use of Python 3.6 or higher which maintains the sorting order of class attributes based on how they were declared. This sorting is not guaranteed to match the declared order of attributes in all cases however; see the method documentation for the exact scheme.

  References: #5494

orm declarative

• [declarative] [orm] [usecase] The name of the virtual column used when using the _declarative.AbstractConcreteBase and _declarative.ConcreteBase classes can now be customized, to allow for models that have a column that is actually named type. Pull request courtesy Jesse-Bakker.

  References: #5513

sql

• [sql] [bug] Repaired an issue where the "ORDER BY" clause rendering a label name rather than a complete expression, which is particularly important for SQL Server, would fail to occur if the expression were enclosed in a parenthesized grouping in some cases. This case has been added to test support. The change additionally adjusts the "automatically add ORDER BY columns when DISTINCT is present" behavior of ORM query, deprecated in 1.4, to more accurately detect column expressions that are already present.

  References: #5470

• [sql] [bug] [datatypes] The LookupError message will now provide the user with up to four possible values that a column is constrained to via the Enum. Values longer than 11 characters will be truncated and replaced with ellipses. Pull request courtesy Ramon Williams.

  References: #4733

• [sql] [bug] Fixed issue where the _engine.Connection.execution_options.schema_translate_map feature would not take effect when the _schema.Sequence.next_value() function function for a _schema.Sequence were used in the _schema.Column.server_default parameter and the create table DDL were emitted.

  References: #5500

postgresql

• [postgresql] [bug] Fixed issue where the return type for the various RANGE comparison operators would itself be the same RANGE type rather than BOOLEAN, which would cause an undesirable result in the case that a TypeDecorator that defined result-processing behavior were in use. Pull request courtesy Jim Bosch.

  References: #5476

mysql

• [mysql] [usecase] The MySQL dialect will render FROM DUAL for a SELECT statement that has no FROM clause but has a WHERE clause. This allows things like "SELECT 1 WHERE EXISTS (subquery)" kinds of queries to be used as well as other use cases.

  References: #5481

• [mysql] [bug] Fixed an issue where CREATE TABLE statements were not specifying the COLLATE keyword correctly.

  References: #5411

• [mysql] [bug] Added MariaDB code 1927 to the list of "disconnect" codes, as recent MariaDB versions apparently use this code when the database server was stopped.

  References: #5493

sqlite

• [sqlite] [bug] [mssql] [reflection] Applied a sweep through all included dialects to ensure names that contain single or double quotes are properly escaped when querying system tables, for all Inspector methods that accept object names as an argument (e.g. table names, view names, etc). SQLite and MSSQL contained two quoting issues that were repaired.

  References: #5456

mssql

• [mssql] [bug] [sql] Fixed bug where the mssql dialect incorrectly escaped object names that contained ']' character(s).

  References: #5467

misc

• [py3k] [usecase] Added a **kw argument to the DeclarativeMeta.__init__() method. This allows a class to support the PEP 487 metaclass hook __init_subclass__. Pull request courtesy Ewen Gillies.

  References: ##5357

1.3.18

Released: June 25, 2020

orm

• [orm] [usecase] Improve error message when using _query.Query.filter_by() in a query where the first entity is not a mapped class.

  References: #5326

• [orm] [usecase] Added a new parameter _orm.query_expression.default_expr to the _orm.query_expression() construct, which will be appled to queries automatically if the _orm.with_expression() option is not used. Pull request courtesy Haoyu Sun.

  References: #5198

engine

• [engine] [bug] Further refinements to the fixes to the "reset" agent fixed in #5326, which now emits a warning when it is not being correctly invoked and corrects for the behavior. Additional scenarios have been identified and fixed where this warning was being emitted.

  References: #5326

• [engine] [bug] Fixed issue in URL object where stringifying the object would not URL encode special characters, preventing the URL from being re-consumable as a real URL. Pull request courtesy Miguel Grinberg.

  References: #5341

sql

• [sql] [schema] Introduce IdentityOptions to store common parameters for sequences and identity columns.

  References: #5324

• [sql] [usecase] Added a ".schema" parameter to the _expression.table() construct, allowing ad-hoc table expressions to also include a schema name. Pull request courtesy Dylan Modesitt.

  References: #5309

• [sql] [bug] Correctly apply self_group in type_coerce element.

  The type coerce element did not correctly apply grouping rules when using in an expression

  References: #5344

• [sql] [change] [sybase] Added .offset support to sybase dialect. Pull request courtesy Alan D. Snow.

  References: #5294

• [sql] [bug] Added Select.with_hint() output to the generic SQL string that is produced when calling str() on a statement. Previously, this clause would be omitted under the assumption that it was dialect specific. The hint text is presented within brackets to indicate the rendering of such hints varies among backends.

  References: #5353

schema

• [schema] [bug] Fixed issue where dialect_options were omitted when a database object (e.g., Table) was copied using tometadata().

  References: #5276

mysql

• [mysql] [usecase] Implemented row-level locking support for mysql. Pull request courtesy Quentin Somerville.

  References: #4860

sqlite

• [sqlite] [bug] Added "exists" to the list of reserved words for SQLite so that this word will be quoted when used as a label or column name. Pull request courtesy Thodoris Sotiropoulos.

  References: #5395

• [sqlite] [usecase] SQLite 3.31 added support for computed column. This change enables their support in SQLAlchemy when targeting SQLite.

  References: #5297

mssql

• [mssql] [bug] Refined the logic used by the SQL Server dialect to interpret multi-part schema names that contain many dots, to not actually lose any dots if the name does not have bracking or quoting used, and additionally to support a "dbname" token that has many parts including that it may have multiple, independently-bracketed sections.

  References: #5364, #5366

• [mssql] [bug] [pyodbc] Fixed an issue in the pyodbc connector such that a warning about pyodbc "drivername" would be emitted when using a totally empty URL. Empty URLs are normal when producing a non-connected dialect object or when using the "creator" argument to create_engine(). The warning now only emits if the driver name is missing but other parameters are still present.

  References: #5346

• [mssql] [bug] Fixed issue with assembling the ODBC connection string for the pyodbc DBAPI. Tokens containing semicolons and/or braces "{}" were not being correctly escaped, causing the ODBC driver to misinterpret the connection string attributes.

  References: #5373

• [mssql] [bug] Fixed issue where datetime.time parameters were being converted to datetime.datetime, making them incompatible with comparisons like >= against an actual _mssql.TIME column.

  References: #5339

• [mssql] [bug] Fixed an issue where the is_disconnect function in the SQL Server pyodbc dialect was incorrectly reporting the disconnect state when the exception messsage had a substring that matched a SQL Server ODBC error code.

  References: #5359

• [mssql] [change] Moved the supports_sane_rowcount_returning = False requirement from the PyODBCConnector level to the MSDialect_pyodbc since pyodbc does work properly in some circumstances.

  References: #5321

oracle

• [oracle] [bug] [reflection] Fixed bug in Oracle dialect where indexes that contain the full set of primary key columns would be mistaken as the primary key index itself, which is omitted, even if there were multiples. The check has been refined to compare the name of the primary key constraint against the index name itself, rather than trying to guess based on the columns present in the index.

  References: #5421

misc

• [change] [examples] Added new option --raw to the examples.performance suite which will dump the raw profile test for consumption by any number of profiling visualizer tools. Removed the "runsnake" option as runsnake is very hard to build at this point;

1.3.17

Released: May 13, 2020

orm

• [orm] [bug] Fixed bug where using with_polymorphic() as the target of a join via RelationshipComparator.of_type() on a mapper that already has a subquery-based with_polymorphic setting that's equivalent to the one requested would not correctly alias the ON clause in the join.

  References: #5288

• [orm] [bug] Fixed issue in the area of where loader options such as selectinload() interact with the baked query system, such that the caching of a query is not supposed to occur if the loader options themselves have elements such as with_polymorphic() objects in them that currently are not cache-compatible. The baked loader could sometimes not fully invalidate itself in these some of these scenarios leading to missed eager loads.

  References: #5303

• [orm] [bug] Modified the internal "identity set" implementation, which is a set that hashes objects on their id() rather than their hash values, to not actually call the __hash__() method of the objects, which are typically user-mapped objects. Some methods were calling this method as a side effect of the implementation.

  References: #5304

• [orm] [bug] An informative error message is raised when an ORM many-to-one comparison is attempted against an object that is not an actual mapped instance. Comparisons such as those to scalar subqueries aren't supported; generalized comparison with subqueries is better achieved using ~.RelationshipProperty.Comparator.has().

  References: #5269

• [orm] [usecase] Added an accessor ColumnProperty.Comparator.expressions which provides access to the group of columns mapped under a multi-column ColumnProperty attribute.

  References: #5262

• [orm] [usecase] Introduce _orm.relationship.sync_backref flag in a relationship to control if the synchronization events that mutate the in-Python attributes are added. This supersedes the previous change #5149, which warned that viewonly=True relationship target of a back_populates or backref configuration would be disallowed.

  References: #5237

engine

• [engine] [bug] Fixed fairly critical issue where the DBAPI connection could be returned to the connection pool while still in an un-rolled-back state. The reset agent responsible for rolling back the connection could be corrupted in the case that the transaction was "closed" without being rolled back or committed, which can occur in some scenarios when using ORM sessions and emitting .close() in a certain pattern involving savepoints. The fix ensures that the reset agent is always active.

  References: #5326

schema

• [schema] Add comment attribute to _schema.Column __repr__ method.

  References: #4138

• [schema] [bug] Fixed issue where an Index that is deferred in being associated with a table, such as as when it contains a Column that is not associated with any Table yet, would fail to attach correctly if it also contained a non table-oriented expession.

  References: #5298

• [schema] [bug] A warning is emitted when making use of the MetaData.sorted_tables attribute as well as the _schema.sort_tables() function, and the given tables cannot be correctly sorted due to a cyclic dependency between foreign key constraints. In this case, the functions will no longer sort the involved tables by foreign key, and a warning will be emitted. Other tables that are not part of the cycle will still be returned in dependency order. Previously, the sorted_table routines would return a collection that would unconditionally omit all foreign keys when a cycle was detected, and no warning was emitted.

  References: #5316

postgresql

• [postgresql] [usecase] Added support for columns or type ARRAY of Enum, JSON or _postgresql.JSONB in PostgreSQL. Previously a workaround was required in these use cases.

  References: #5265

• [postgresql] [usecase] Raise an explicit exc.CompileError when adding a table with a column of type ARRAY of Enum configured with Enum.native_enum set to False when Enum.create_constraint is not set to False

  References: #5266

mssql

• [mssql] [bug] [reflection] Fix a regression introduced by the reflection of computed column in MSSQL when using the legacy TDS version 4.2. The dialect will try to detect the protocol version of first connect and run in compatibility mode if it cannot detect it.

  References: #5255

• [mssql] [bug] [reflection] Fix a regression introduced by the reflection of computed column in MSSQL when using SQL server versions before 2012, which does not support the concat function.

  References: #5271

oracle

• [oracle] [bug] Some modifications to how the cx_oracle dialect sets up per-column outputtype handlers for LOB and numeric datatypes to adjust for potential changes coming in cx_Oracle 8.

  References: #5246

• [oracle] [bug] [performance] Changed the implementation of fetching CLOB and BLOB objects to use cx_Oracle's native implementation which fetches CLOB/BLOB objects inline with other result columns, rather than performing a separate fetch. As always, this can be disabled by setting auto_convert_lobs to False.

  As part of this change, the behavior of a CLOB that was given a blank string on INSERT now returns None on SELECT, which is now consistent with that of VARCHAR on Oracle.

  References: #5314

firebird

• [firebird] [change] Adjusted dialect loading for firebird:// URIs so the external sqlalchemy-firebird dialect will be used if it has been installed, otherwise fall back to the (now deprecated) internal Firebird dialect.

  References: #5278

1.3.16

Released: April 7, 2020

orm

• [orm] [bug] Fixed bug in orm.selectinload() loading option where two or more loaders that represent different relationships with the same string key name as referenced from a single orm.with_polymorphic() construct with multiple subclass mappers would fail to invoke each subqueryload separately, instead making use of a single string-based slot that would prevent the other loaders from being invoked.

  References: #5228

• [orm] [performance] Modified the queries used by subqueryload and selectinload to no longer ORDER BY the primary key of the parent entity; this ordering was there to allow the rows as they come in to be copied into lists directly with a minimal level of Python-side collation. However, these ORDER BY clauses can negatively impact the performance of the query as in many scenarios these columns are derived from a subquery or are otherwise not actual primary key columns such that SQL planners cannot make use of indexes. The Python-side collation uses the native itertools.group_by() to collate the incoming rows, and has been modified to allow multiple row-groups-per-parent to be assembled together using list.extend(), which should still allow for relatively fast Python-side performance. There will still be an ORDER BY present for a relationship that includes an explicit order_by parameter, however this is the only ORDER BY that will be added to the query for both kinds of loading.

  References: #5162

• [orm] [bug] Fixed issue where a lazyload that uses session-local "get" against a target many-to-one relationship where an object with the correct primary key is present, however it's an instance of a sibling class, does not correctly return None as is the case when the lazy loader actually emits a load for that row.

  References: #5210

orm declarative

• [bug] [declarative] [orm] The string argument accepted as the first positional argument by the relationship() function when using the Declarative API is no longer interpreted using the Python eval() function; instead, the name is dot separated and the names are looked up directly in the name resolution dictionary without treating the value as a Python expression. However, passing a string argument to the other relationship() parameters that necessarily must accept Python expressions will still use eval(); the documentation has been clarified to ensure that there is no ambiguity that this is in use.

  References: #5238

sql

• [sql] [types] Add ability to literal compile a DateTime, Date or :class:"Time" when using the string dialect for debugging purposes. This change does not impact real dialect implementation that retain their current behavior.

  References: #5052

schema

• [schema] [reflection] Added support for reflection of "computed" columns, which are now returned as part of the structure returned by Inspector.get_columns(). When reflecting full Table objects, computed columns will be represented using the Computed construct.

  References: #5063

postgresql

• [postgresql] [bug] Fixed issue where a "covering" index, e.g. those which have an INCLUDE clause, would be reflected including all the columns in INCLUDE clause as regular columns. A warning is now emitted if these additional columns are detected indicating that they are currently ignored. Note that full support for "covering" indexes is part of #4458. Pull request courtesy Marat Sharafutdinov.

  References: #5205

mysql

• [mysql] [bug] Fixed issue in MySQL dialect when connecting to a psuedo-MySQL database such as that provided by ProxySQL, the up front check for isolation level when it returns no row will not prevent the dialect from continuing to connect. A warning is emitted that the isolation level could not be detected.

  References: #5239

sqlite

• [sqlite] [usecase] Implemented AUTOCOMMIT isolation level for SQLite when using pysqlite.

  References: #5164

mssql

• [mssql] [mysql] [oracle] [usecase] Added support for ColumnOperators.is_distinct_from() and ColumnOperators.isnot_distinct_from() to SQL Server, MySQL, and Oracle.

  References: #5137

oracle

• [oracle] [usecase] Implemented AUTOCOMMIT isolation level for Oracle when using cx_Oracle. Also added a fixed default isolation level of READ COMMITTED for Oracle.

  References: #5200

• [oracle] [bug] [reflection] Fixed regression / incorrect fix caused by fix for #5146 where the Oracle dialect reads from the "all_tab_comments" view to get table comments but fails to accommodate for the current owner of the table being requested, causing it to read the wrong comment if multiple tables of the same name exist in multiple schemas.

  References: #5146

misc

• [bug] [tests] Fixed an issue that prevented the test suite from running with the recently released py.test 5.4.0.

  References: #5201

• [enum] [types] The Enum type now supports the parameter Enum.length to specify the length of the VARCHAR column to create when using non native enums by setting Enum.native_enum to False

  References: #5183

• [installer] Ensured that the "pyproject.toml" file is not included in builds, as the presence of this file indicates to pip that a pep-517 installation process should be used. As this mode of operation appears to be not well supported by current tools / distros, these problems are avoided within the scope of SQLAlchemy installation by omitting the file.

  References: #5207

1.3.15

Released: March 11, 2020

orm

• [orm] [bug] Adjusted the error message emitted by Query.join() when a left hand side can't be located that the Query.select_from() method is the best way to resolve the issue. Also, within the 1.3 series, used a deterministic ordering when determining the FROM clause from a given column entity passed to Query so that the same expression is determined each time.

  References: #5194

• [orm] [bug] Fixed regression in 1.3.14 due to #4849 where a sys.exc_info() call failed to be invoked correctly when a flush error would occur. Test coverage has been added for this exception case.

  References: #5196

1.3.14

Released: March 10, 2020

general

• [general] [bug] [py3k] Applied an explicit "cause" to most if not all internally raised exceptions that are raised from within an internal exception catch, to avoid misleading stacktraces that suggest an error within the handling of an exception. While it would be preferable to suppress the internally caught exception in the way that the __suppress_context__ attribute would, there does not as yet seem to be a way to do this without suppressing an enclosing user constructed context, so for now it exposes the internally caught exception as the cause so that full information about the context of the error is maintained.

  References: #4849

orm

• [orm] [bug] Fixed regression caused in 1.3.13 by #5056 where a refactor of the ORM path registry system made it such that a path could no longer be compared to an empty tuple, which can occur in a particular kind of joined eager loading path. The "empty tuple" use case has been resolved so that the path registry is compared to a path registry in all cases; the PathRegistry object itself now implements __eq__() and __ne__() methods which will take place for all equality comparisons and continue to succeed in the not anticipated case that a non- PathRegistry object is compared, while emitting a warning that this object should not be the subject of the comparison.

  References: #5110

• [orm] [bug] Setting a relationship to viewonly=True which is also the target of a back_populates or backref configuration will now emit a warning and eventually be disallowed. back_populates refers specifically to mutation of an attribute or collection, which is disallowed when the attribute is subject to viewonly=True. The viewonly attribute is not subject to persistence behaviors which means it will not reflect correct results when it is locally mutated.

  References: #5149

• [orm] [bug] Fixed an additional regression in the same area as that of #5080 introduced in 1.3.0b3 via #4468 where the ability to create a joined option across a with_polymorphic() into a relationship against the base class of that with_polymorphic, and then further into regular mapped relationships would fail as the base class component would not add itself to the load path in a way that could be located by the loader strategy. The changes applied in #5080 have been further refined to also accommodate this scenario.

  References: #5121

• [orm] [usecase] Added a new flag InstanceEvents.restore_load_context and SessionEvents.restore_load_context which apply to the InstanceEvents.load(), InstanceEvents.refresh(), and SessionEvents.loaded_as_persistent() events, which when set will restore the "load context" of the object after the event hook has been called. This ensures that the object remains within the "loader context" of the load operation that is already ongoing, rather than the object being transferred to a new load context due to refresh operations which may have occurred in the event. A warning is now emitted when this condition occurs, which recommends use of the flag to resolve this case. The flag is "opt-in" so that there is no risk introduced to existing applications.

  The change additionally adds support for the raw=True flag to session lifecycle events.

  References: #5129

engine

• [engine] [bug] Expanded the scope of cursor/connection cleanup when a statement is executed to include when the result object fails to be constructed, or an after_cursor_execute() event raises an error, or autocommit / autoclose fails. This allows the DBAPI cursor to be cleaned up on failure and for connectionless execution allows the connection to be closed out and returned to the connection pool, where previously it waiting until garbage collection would trigger a pool return.

  References: #5182

sql

• [sql] [bug] [postgresql] Fixed bug where a CTE of an INSERT/UPDATE/DELETE that also uses RETURNING could then not be SELECTed from directly, as the internal state of the compiler would try to treat the outer SELECT as a DELETE statement itself and access nonexistent state.

  References: #5181

postgresql

• [postgresql] [bug] Fixed issue where the "schema_translate_map" feature would not work with a PostgreSQL native enumeration type (i.e. Enum, postgresql.ENUM) in that while the "CREATE TYPE" statement would be emitted with the correct schema, the schema would not be rendered in the CREATE TABLE statement at the point at which the enumeration was referenced.

  References: #5158

• [postgresql] [bug] [reflection] Fixed bug where PostgreSQL reflection of CHECK constraints would fail to parse the constraint if the SQL text contained newline characters. The regular expression has been adjusted to accommodate for this case. Pull request courtesy Eric Borczuk.

  References: #5170

mysql

• [mysql] [bug] Fixed issue in MySQL mysql.Insert.on_duplicate_key_update() construct where using a SQL function or other composed expression for a column argument would not properly render the VALUES keyword surrounding the column itself.

  References: #5173

mssql

• [mssql] [bug] Fixed issue where the mssql.DATETIMEOFFSET type would not accommodate for the None value, introduced as part of the series of fixes for this type first introduced in #4983, #5045. Additionally, added support for passing a backend-specific date formatted string through this type, as is typically allowed for date/time types on most other DBAPIs.

  References: #5132

oracle

• [oracle] [bug] Fixed a reflection bug where table comments could only be retrieved for tables actually owned by the user but not for tables visible to the user but owned by someone else. Pull request courtesy Dave Hirschfeld.

  References: #5146

misc

• [bug] [performance] Revised an internal change to the test system added as a result of #5085 where a testing-related module per dialect would be loaded unconditionally upon making use of that dialect, pulling in SQLAlchemy's testing framework as well as the ORM into the module import space. This would only impact initial startup time and memory to a modest extent, however it's best that these additional modules aren't reverse-dependent on straight Core usage.

  References: #5180

• [bug] [installation] Vendored the inspect.formatannotation function inside of sqlalchemy.util.compat, which is needed for the vendored version of inspect.formatargspec. The function is not documented in cPython and is not guaranteed to be available in future Python versions.

  References: #5138

• [ext] [usecase] Added keyword arguments to the MutableList.sort() function so that a key function as well as the "reverse" keyword argument can be provided.

  References: #5114

1.3.13

Released: January 22, 2020

orm

• [orm] [bug] [engine] Added test support and repaired a wide variety of unnecessary reference cycles created for short-lived objects, mostly in the area of ORM queries. Thanks much to Carson Ip for the help on this.

  References: #5050, #5056, #5071

• [orm] [bug] Fixed regression in loader options introduced in 1.3.0b3 via #4468 where the ability to create a loader option using PropComparator.of_type() targeting an aliased entity that is an inheriting subclass of the entity which the preceding relationship refers to would fail to produce a matching path. See also #5082 fixed in this same release which involves a similar kind of issue.

  References: #5107

• [orm] [bug] Fixed regression in joined eager loading introduced in 1.3.0b3 via #4468 where the ability to create a joined option across a with_polymorphic() into a polymorphic subclass using RelationshipProperty.of_type() and then further along regular mapped relationships would fail as the polymorphic subclass would not add itself to the load path in a way that could be located by the loader strategy. A tweak has been made to resolve this scenario.

  References: #5082

• [orm] [performance] Identified a performance issue in the system by which a join is constructed based on a mapped relationship. The clause adaption system would be used for the majority of join expressions including in the common case where no adaptation is needed. The conditions under which this adaptation occur have been refined so that average non-aliased joins along a simple relationship without a "secondary" table use about 70% less function calls.

• [orm] [bug] Repaired a warning in the ORM flush process that was not covered by test coverage when deleting objects that use the "version_id" feature. This warning is generally unreachable unless using a dialect that sets the "supports_sane_rowcount" flag to False, which is not typically the case however is possible for some MySQL configurations as well as older Firebird drivers, and likely some third party dialects.

  References: #5068

• [orm] [bug] Fixed bug where usage of joined eager loading would not properly wrap the query inside of a subquery when Query.group_by() were used against the query. When any kind of result-limiting approach is used, such as DISTINCT, LIMIT, OFFSET, joined eager loading embeds the row-limited query inside of a subquery so that the collection results are not impacted. For some reason, the presence of GROUP BY was never included in this criterion, even though it has a similar effect as using DISTINCT. Additionally, the bug would prevent using GROUP BY at all for a joined eager load query for most database platforms which forbid non-aggregated, non-grouped columns from being in the query, as the additional columns for the joined eager load would not be accepted by the database.

  References: #5065

engine

• [engine] [bug] Fixed issue where the collection of value processors on a Compiled object would be mutated when "expanding IN" parameters were used with a datatype that has bind value processors; in particular, this would mean that when using statement caching and/or baked queries, the same compiled._bind_processors collection would be mutated concurrently. Since these processors are the same function for a given bind parameter namespace every time, there was no actual negative effect of this issue, however, the execution of a Compiled object should never be causing any changes in its state, especially given that they are intended to be thread-safe and reusable once fully constructed.

  References: #5048

sql

• [sql] [usecase] A function created using GenericFunction can now specify that the name of the function should be rendered with or without quotes by assigning the quoted_name construct to the .name element of the object. Prior to 1.3.4, quoting was never applied to function names, and some quoting was introduced in #4467 but no means to force quoting for a mixed case name was available. Additionally, the quoted_name construct when used as the name will properly register its lowercase name in the function registry so that the name continues to be available via the func. registry.

  References: #5079

postgresql

• [postgresql] [bug] Fixed issue where the PostgreSQL dialect would fail to parse a reflected CHECK constraint that was a boolean-valued function (as opposed to a boolean-valued expression).

  References: #5039

• [postgresql] [tests] Improved detection of two phase transactions requirement for the PostgreSQL database by testing that max_prepared_transactions is set to a value greater than 0. Pull request courtesy Federico Caselli.

  References: #5057

• [postgresql] [usecase] Added support for prefixes to the CTE construct, to allow support for Postgresql 12 "MATERIALIZED" and "NOT MATERIALIZED" phrases. Pull request courtesy Marat Sharafutdinov.

  References: #5040

mssql

• [mssql] [bug] Fixed issue where a timezone-aware datetime value being converted to string for use as a parameter value of a mssql.DATETIMEOFFSET column was omitting the fractional seconds.

  References: #5045

misc

• [bug] [ext] Fixed bug in sqlalchemy.ext.serializer where a unique BindParameter object could conflict with itself if it were present in the mapping itself, as well as the filter condition of the query, as one side would be used against the non-deserialized version and the other side would use the deserialized version. Logic is added to BindParameter similar to its "clone" method which will uniquify the parameter name upon deserialize so that it doesn't conflict with its original.

  References: #5086

• [bug] [tests] Fixed a few test failures which would occur on Windows due to SQLite file locking issues, as well as some timing issues in connection pool related tests; pull request courtesy Federico Caselli.

  References: #4946

1.3.12

Released: December 16, 2019

orm

• [orm] [bug] Fixed issue involving lazy="raise" strategy where an ORM delete of an object would raise for a simple "use-get" style many-to-one relationship that had lazy="raise" configured. This is inconsistent vs. the change introduced in 1.3 as part of #4353, where it was established that a history operation that does not expect emit SQL should bypass the lazy="raise" check, and instead effectively treat it as lazy="raise_on_sql" for this case. The fix adjusts the lazy loader strategy to not raise for the case where the lazy load was instructed that it should not emit SQL if the object were not present.

  References: #4997

• [orm] [bug] Fixed regression introduced in 1.3.0 related to the association proxy refactor in #4351 that prevented composite() attributes from working in terms of an association proxy that references them.

  References: #5000

• [orm] [bug] Setting persistence-related flags on relationship() while also setting viewonly=True will now emit a regular warning, as these flags do not make sense for a viewonly=True relationship. In particular, the "cascade" settings have their own warning that is generated based on the individual values, such as "delete, delete-orphan", that should not apply to a viewonly relationship. Note however that in the case of "cascade", these settings are still erroneously taking effect even though the relationship is set up as "viewonly". In 1.4, all persistence-related cascade settings will be disallowed on a viewonly=True relationship in order to resolve this issue.

  References: #4993

• [orm] [bug] [py3k] Fixed issue where when assigning a collection to itself as a slice, the mutation operation would fail as it would first erase the assigned collection inadvertently. As an assignment that does not change the contents should not generate events, the operation is now a no-op. Note that the fix only applies to Python 3; in Python 2, the __setitem__ hook isn't called in this case; __setslice__ is used instead which recreates the list item-by-item in all cases.

  References: #4990

• [orm] [bug] Fixed issue where by if the "begin" of a transaction failed at the Core engine/connection level, such as due to network error or database is locked for some transactional recipes, within the context of the Session procuring that connection from the conneciton pool and then immediately returning it, the ORM Session would not close the connection despite this connection not being stored within the state of that Session. This would lead to the connection being cleaned out by the connection pool weakref handler within garbage collection which is an unpreferred codepath that in some special configurations can emit errors in standard error.

  References: #5034

sql

• [sql] [bug] Fixed bug where "distinct" keyword passed to select() would not treat a string value as a "label reference" in the same way that the select.distinct() does; it would instead raise unconditionally. This keyword argument and the others passed to select() will ultimately be deprecated for SQLAlchemy 2.0.

  References: #5028

• [sql] [bug] Changed the text of the exception for "Can't resolve label reference" to include other kinds of label coercions, namely that "DISTINCT" is also in this category under the PostgreSQL dialect.

sqlite

• [sqlite] [bug] Fixed issue to workaround SQLite's behavior of assigning "numeric" affinity to JSON datatypes, first described at change_3850, which returns scalar numeric JSON values as a number and not as a string that can be JSON deserialized. The SQLite-specific JSON deserializer now gracefully degrades for this case as an exception and bypasses deserialization for single numeric values, as from a JSON perspective they are already deserialized.

  References: #5014

mssql

• [mssql] [bug] Repaired support for the mssql.DATETIMEOFFSET datatype on PyODBC, by adding PyODBC-level result handlers as it does not include native support for this datatype. This includes usage of the Python 3 "timezone" tzinfo subclass in order to set up a timezone, which on Python 2 makes use of a minimal backport of "timezone" in sqlalchemy.util.

  References: #4983

1.3.11

Released: November 11, 2019

orm

• [orm] [usecase] Added accessor Query.is_single_entity() to Query, which will indicate if the results returned by this Query will be a list of ORM entities, or a tuple of entities or column expressions. SQLAlchemy hopes to improve upon the behavior of single entity / tuples in future releases such that the behavior would be explicit up front, however this attribute should be helpful with the current behavior. Pull request courtesy Patrick Hayes.

  References: #4934

• [orm] [bug] The relationship.omit_join flag was not intended to be manually set to True, and will now emit a warning when this occurs. The omit_join optimization is detected automatically, and the omit_join flag was only intended to disable the optimization in the hypothetical case that the optimization may have interfered with correct results, which has not been observed with the modern version of this feature. Setting the flag to True when it is not automatically detected may cause the selectin load feature to not work correctly when a non-default primary join condition is in use.

  References: #4954

• [orm] [bug] A warning is emitted if a primary key value is passed to Query.get() that consists of None for all primary key column positions. Previously, passing a single None outside of a tuple would raise a TypeError and passing a composite None (tuple of None values) would silently pass through. The fix now coerces the single None into a tuple where it is handled consistently with the other None conditions. Thanks to Lev Izraelit for the help with this.

  References: #4915

• [orm] [bug] The BakedQuery will not cache a query that was modified by a QueryEvents.before_compile() event, so that compilation hooks that may be applying ad-hoc modifications to queries will take effect on each run. In particular this is helpful for events that modify queries used in lazy loading as well as eager loading such as "select in" loading. In order to re-enable caching for a query modified by this event, a new flag bake_ok is added; see baked_with_before_compile for details.

  A longer term plan to provide a new form of SQL caching should solve this kind of issue more comprehensively.

  References: #4947

• [orm] [bug] Fixed ORM bug where a "secondary" table that referred to a selectable which in some way would refer to the local primary table would apply aliasing to both sides of the join condition when a relationship-related join, either via Query.join() or by joinedload(), were generated. The "local" side is now excluded.

  References: #4974

engine

• [engine] [bug] Fixed bug where parameter repr as used in logging and error reporting needs additional context in order to distinguish between a list of parameters for a single statement and a list of parameter lists, as the "list of lists" structure could also indicate a single parameter list where the first parameter itself is a list, such as for an array parameter. The engine/connection now passes in an additional boolean indicating how the parameters should be considered. The only SQLAlchemy backend that expects arrays as parameters is that of psycopg2 which uses pyformat parameters, so this issue has not been too apparent, however as other drivers that use positional gain more features it is important that this be supported. It also eliminates the need for the parameter repr function to guess based on the parameter structure passed.

  References: #4902

• [engine] [bug] [postgresql] Fixed bug in Inspector where the cache key generation did not take into account arguments passed in the form of tuples, such as the tuple of view name styles to return for the PostgreSQL dialect. This would lead the inspector to cache too generally for a more specific set of criteria. The logic has been adjusted to include every keyword element in the cache, as every argument is expected to be appropriate for a cache else the caching decorator should be bypassed by the dialect.

  References: #4955

sql

• [sql] [bug] [py3k] Changed the repr() of the quoted_name construct to use regular string repr() under Python 3, rather than running it through "backslashreplace" escaping, which can be misleading.

  References: #4931

• [sql] [usecase] Added new accessors to expressions of type JSON to allow for specific datatype access and comparison, covering strings, integers, numeric, boolean elements. This revises the documented approach of CASTing to string when comparing values, instead adding specific functionality into the PostgreSQL, SQlite, MySQL dialects to reliably deliver these basic types in all cases.

  References: #4276

• [sql] [usecase] The text() construct now supports "unique" bound parameters, which will dynamically uniquify themselves on compilation thus allowing multiple text() constructs with the same bound parameter names to be combined together.

  References: #4933

schema

• [schema] [bug] Fixed bug where a table that would have a column label overlap with a plain column name, such as "foo.id AS foo_id" vs. "foo.foo_id", would prematurely generate the ._label attribute for a column before this overlap could be detected due to the use of the index=True or unique=True flag on the column in conjunction with the default naming convention of "column_0_label". This would then lead to failures when ._label were used later to generate a bound parameter name, in particular those used by the ORM when generating the WHERE clause for an UPDATE statement. The issue has been fixed by using an alternate ._label accessor for DDL generation that does not affect the state of the Column. The accessor also bypasses the key-deduplication step as it is not necessary for DDL, the naming is now consistently "<tablename>_<columnname>" without any subsequent numeric symbols when used in DDL.

  References: #4911

• [schema] [usecase] Added DDL support for "computed columns"; these are DDL column specifications for columns that have a server-computed value, either upon SELECT (known as "virtual") or at the point of which they are INSERTed or UPDATEd (known as "stored"). Support is established for Postgresql, MySQL, Oracle SQL Server and Firebird. Thanks to Federico Caselli for lots of work on this one.

  References: #4894

mysql

• [mysql] [bug] Added "Connection was killed" message interpreted from the base pymysql.Error class in order to detect closed connection, based on reports that this message is arriving via a pymysql.InternalError() object which indicates pymysql is not handling it correctly.

  References: #4945

mssql

• [mssql] [bug] Fixed issue in MSSQL dialect where an expression-based OFFSET value in a SELECT would be rejected, even though the dialect can render this expression inside of a ROW NUMBER-oriented LIMIT/OFFSET construct.

  References: #4973

• [mssql] [bug] Fixed an issue in the Engine.table_names() method where it would feed the dialect's default schema name back into the dialect level table function, which in the case of SQL Server would interpret it as a dot-tokenized schema name as viewed by the mssql dialect, which would cause the method to fail in the case where the database username actually had a dot inside of it. In 1.3, this method is still used by the MetaData.reflect() function so is a prominent codepath. In 1.4, which is the current master development branch, this issue doesn't exist, both because MetaData.reflect() isn't using this method nor does the method pass the default schema name explicitly. The fix nonetheless guards against the default server name value returned by the dialect from being interpreted as dot-tokenized name under any circumstances by wrapping it in quoted_name().

  References: #4923

oracle

• [oracle] [bug] [firebird] Modified the approach of "name normalization" for the Oracle and Firebird dialects, which converts from the UPPERCASE-as-case-insensitive convention of these dialects into lowercase-as-case-insensitive for SQLAlchemy, to not automatically apply the quoted_name construct to a name that matches itself under upper or lower case conversion, as is the case for many non-european characters. All names used within metadata structures are converted to quoted_name objects in any case; the change here would only affect the output of some inspection functions.

  References: #4931

• [oracle] [usecase] Added dialect-level flag encoding_errors to the cx_Oracle dialect, which can be specified as part of create_engine(). This is passed to SQLAlchemy's unicode decoding converter under Python 2, and to cx_Oracle's cursor.var() object as the encodingErrors parameter under Python 3, for the very unusual case that broken encodings are present in the target database which cannot be fetched unless error handling is relaxed. The value is ultimately one of the Python "encoding errors" parameters passed to decode().

  References: #4799

• [oracle] [bug] The sqltypes.NCHAR datatype will now bind to the cx_Oracle.FIXED_NCHAR DBAPI data bindings when used in a bound parameter, which supplies proper comparison behavior against a variable-length string. Previously, the sqltypes.NCHAR datatype would bind to cx_oracle.NCHAR which is not fixed length; the sqltypes.CHAR datatype already binds to cx_Oracle.FIXED_CHAR so it is now consistent that sqltypes.NCHAR binds to cx_Oracle.FIXED_NCHAR.

  References: #4913

firebird

• [firebird] [bug] Added additional "disconnect" message "Error writing data to the connection" to Firebird disconnection detection. Pull request courtesy lukens.

  References: #4903

misc

• [bug] [tests] Fixed test failures which would occur with newer SQLite as of version 3.30 or greater, due to their addition of nulls ordering syntax as well as new restrictions on aggregate functions. Pull request courtesy Nils Philippsen.

  References: #4920

• [bug] [installation] [windows] Added a workaround for a setuptools-related failure that has been observed as occurring on Windows installations, where setuptools is not correctly reporting a build error when the MSVC build dependencies are not installed and therefore not allowing graceful degradation into non C extensions builds.

  References: #4967

1.3.10

Released: October 9, 2019

• [bug] [mssql] Fixed bug in SQL Server dialect with new "max_identifier_length" feature where the mssql dialect already featured this flag, and the implementation did not accommodate for the new initialization hook correctly.

  References: #4857

• [bug] [oracle] Fixed regression in Oracle dialect that was inadvertently using max identifier length of 128 characters on Oracle server 12.2 and greater even though the stated contract for the remainder of the 1.3 series is that this value stays at 30 until version SQLAlchemy 1.4. Also repaired issues with the retrieval of the "compatibility" version, and removed the warning emitted when the "v$parameter" view was not accessible as this was causing user confusion.

  References: #4857, #4898

1.3.9

Released: October 4, 2019

• [engine] [usecase] Added new create_engine() parameter create_engine.max_identifier_length. This overrides the dialect-coded "max identifier length" in order to accommodate for databases that have recently changed this length and the SQLAlchemy dialect has not yet been adjusted to detect for that version. This parameter interacts with the existing create_engine.label_length parameter in that it establishes the maximum (and default) value for anonymously generated labels. Additionally, post-connection detection of max identifier lengths has been added to the dialect system. This feature is first being used by the Oracle dialect.

  References: #4857

• [oracle] [usecase] The Oracle dialect now emits a warning if Oracle version 12.2 or greater is used, and the create_engine.max_identifier_length parameter is not set. The version in this specific case defaults to that of the "compatibility" version set in the Oracle server configuration, not the actual server version. In version 1.4, the default max_identifier_length for 12.2 or greater will move to 128 characters. In order to maintain forwards compatibility, applications should set create_engine.max_identifier_length to 30 in order to maintain the same length behavior, or to 128 in order to test the upcoming behavior. This length determines among other things how generated constraint names are truncated for statements like CREATE CONSTRAINT and DROP CONSTRAINT, which means a the new length may produce a name-mismatch against a name that was generated with the old length, impacting database migrations.

  References: #4857

• [sqlite] [usecase] Added support for sqlite "URI" connections, which allow for sqlite-specific flags to be passed in the query string such as "read only" for Python sqlite3 drivers that support this.

  References: #4863

• [bug] [tests] Fixed unit test regression released in 1.3.8 that would cause failure for Oracle, SQL Server and other non-native ENUM platforms due to new enumeration tests added as part of #4285 enum sortability in the unit of work; the enumerations created constraints that were duplicated on name.

  References: #4285

• [bug] [oracle] Restored adding cx_Oracle.DATETIME to the setinputsizes() call when a SQLAlchemy Date, DateTime or Time datatype is used, as some complex queries require this to be present. This was removed in the 1.2 series for arbitrary reasons.

  References: #4886

• [bug] [mssql] Added identifier quoting to the schema name applied to the "use" statement which is invoked when a SQL Server multipart schema name is used within a Table that is being reflected, as well as for Inspector methods such as Inspector.get_table_names(); this accommodates for special characters or spaces in the database name. Additionally, the "use" statement is not emitted if the current database matches the target owner database name being passed.

  References: #4883

• [bug] [orm] Fixed regression in selectinload loader strategy caused by #4775 (released in version 1.3.6) where a many-to-one attribute of None would no longer be populated by the loader. While this was usually not noticeable due to the lazyloader populating None upon get, it would lead to a detached instance error if the object were detached.

  References: #4872

• [bug] [orm] Passing a plain string expression to Session.query() is deprecated, as all string coercions were removed in #4481 and this one should have been included. The literal_column() function may be used to produce a textual column expression.

  References: #4873

• [sql] [usecase] Added an explicit error message for the case when objects passed to Table are not SchemaItem objects, rather than resolving to an attribute error.

  References: #4847

• [bug] [orm] A warning is emitted for a condition in which the Session may implicitly swap an object out of the identity map for another one with the same primary key, detaching the old one, which can be an observed result of load operations which occur within the SessionEvents.after_flush() hook. The warning is intended to notify the user that some special condition has caused this to happen and that the previous object may not be in the expected state.

  References: #4890

• [bug] [sql] Characters that interfere with "pyformat" or "named" formats in bound parameters, namely %, (, ) and the space character, as well as a few other typically undesirable characters, are stripped early for a bindparam() that is using an anonymized name, which is typically generated automatically from a named column which itself includes these characters in its name and does not use a .key, so that they do not interfere either with the SQLAlchemy compiler's use of string formatting or with the driver-level parsing of the parameter, both of which could be demonstrated before the fix. The change only applies to anonymized parameter names that are generated and consumed internally, not end-user defined names, so the change should have no impact on any existing code. Applies in particular to the psycopg2 driver which does not otherwise quote special parameter names, but also strips leading underscores to suit Oracle (but not yet leading numbers, as some anon parameters are currently entirely numeric/underscore based); Oracle in any case continues to quote parameter names that include special characters.

  References: #4837

1.3.8

Released: August 27, 2019

• [bug] [orm] Fixed bug where Load objects were not pickleable due to mapper/relationship state in the internal context dictionary. These objects are now converted to picklable using similar techniques as that of other elements within the loader option system that have long been serializable.

  References: #4823

• [bug] [postgresql] Revised the approach for the just added support for the psycopg2 "execute_values()" feature added in 1.3.7 for #4623. The approach relied upon a regular expression that would fail to match for a more complex INSERT statement such as one which had subqueries involved. The new approach matches exactly the string that was rendered as the VALUES clause.

  References: #4623

• [orm] [usecase] Added support for the use of an Enum datatype using Python pep-435 enumeration objects as values for use as a primary key column mapped by the ORM. As these values are not inherently sortable, as required by the ORM for primary keys, a new TypeEngine.sort_key_function attribute is added to the typing system which allows any SQL type to implement a sorting for Python objects of its type which is consulted by the unit of work. The Enum type then defines this using the database value of a given enumeration. The sorting scheme can be also be redefined by passing a callable to the Enum.sort_key_function parameter. Pull request courtesy Nicolas Caniart.

  References: #4285

• [bug] [engine] Fixed an issue whereby if the dialect "initialize" process which occurs on first connect would encounter an unexpected exception, the initialize process would fail to complete and then no longer attempt on subsequent connection attempts, leaving the dialect in an un-initialized, or partially initialized state, within the scope of parameters that need to be established based on inspection of a live connection. The "invoke once" logic in the event system has been reworked to accommodate for this occurrence using new, private API features that establish an "exec once" hook that will continue to allow the initializer to fire off on subsequent connections, until it completes without raising an exception. This does not impact the behavior of the existing once=True flag within the event system.

  References: #4807

• [bug] [reflection] [sqlite] Fixed bug where a FOREIGN KEY that was set up to refer to the parent table by table name only without the column names would not correctly be reflected as far as setting up the "referred columns", since SQLite's PRAGMA does not report on these columns if they weren't given explicitly. For some reason this was harcoded to assume the name of the local column, which might work for some cases but is not correct. The new approach reflects the primary key of the referred table and uses the constraint columns list as the referred columns list, if the remote column(s) aren't present in the reflected pragma directly.

  References: #4810

• [bug] [postgresql] Fixed bug where Postgresql operators such as postgresql.ARRAY.Comparator.contains() and postgresql.ARRAY.Comparator.contained_by() would fail to function correctly for non-integer values when used against a postgresql.array object, due to an erroneous assert statement.

  References: #4822

• [engine] [feature] Added new parameter create_engine.hide_parameters which when set to True will cause SQL parameters to no longer be logged, nor rendered in the string representation of a StatementError object.

  References: #4815

• [postgresql] [usecase] Added support for reflection of CHECK constraints that include the special PostgreSQL qualifier "NOT VALID", which can be present for CHECK constraints that were added to an exsiting table with the directive that they not be applied to existing data in the table. The PostgreSQL dictionary for CHECK constraints as returned by Inspector.get_check_constraints() may include an additional entry dialect_options which within will contain an entry "not_valid": True if this symbol is detected. Pull request courtesy Bill Finn.

  References: #4824

1.3.7

Released: August 14, 2019

• [bug] [sql] Fixed issue where Index object which contained a mixture of functional expressions which were not resolvable to a particular column, in combination with string-based column names, would fail to initialize its internal state correctly leading to failures during DDL compilation.

  References: #4778

• [bug] [sqlite] The dialects that support json are supposed to take arguments json_serializer and json_deserializer at the create_engine() level, however the SQLite dialect calls them _json_serilizer and _json_deserilalizer. The names have been corrected, the old names are accepted with a change warning, and these parameters are now documented as create_engine.json_serializer and create_engine.json_deserializer.

  References: #4798

• [bug] [mysql] The MySQL dialects will emit "SET NAMES" at the start of a connection when charset is given to the MySQL driver, to appease an apparent behavior observed in MySQL 8.0 that raises a collation error when a UNION includes string columns unioned against columns of the form CAST(NULL AS CHAR(..)), which is what SQLAlchemy's polymorphic_union function does. The issue seems to have affected PyMySQL for at least a year, however has recently appeared as of mysqlclient 1.4.4 based on changes in how this DBAPI creates a connection. As the presence of this directive impacts three separate MySQL charset settings which each have intricate effects based on their presense, SQLAlchemy will now emit the directive on new connections to ensure correct behavior.

  References: #4804

• [postgresql] [usecase] Added new dialect flag for the psycopg2 dialect, executemany_mode which supersedes the previous experimental use_batch_mode flag. executemany_mode supports both the "execute batch" and "execute values" functions provided by psycopg2, the latter which is used for compiled insert() constructs. Pull request courtesy Yuval Dinari.

  References: #4623

• [bug] [sql] Fixed bug where TypeEngine.column_expression() method would not be applied to subsequent SELECT statements inside of a UNION or other CompoundSelect, even though the SELECT statements are rendered at the topmost level of the statement. New logic now differentiates between rendering the column expression, which is needed for all SELECTs in the list, vs. gathering the returned data type for the result row, which is needed only for the first SELECT.

  References: #4787

• [bug] [sqlite] Fixed bug where usage of "PRAGMA table_info" in SQLite dialect meant that reflection features to detect for table existence, list of table columns, and list of foreign keys, would default to any table in any attached database, when no schema name was given and the table did not exist in the base schema. The fix explicitly runs PRAGMA for the 'main' schema and then the 'temp' schema if the 'main' returned no rows, to maintain the behavior of tables + temp tables in the "no schema" namespace, attached tables only in the "schema" namespace.

  References: #4793

• [bug] [sql] Fixed issue where internal cloning of SELECT constructs could lead to a key error if the copy of the SELECT changed its state such that its list of columns changed. This was observed to be occurring in some ORM scenarios which may be unique to 1.3 and above, so is partially a regression fix.

  References: #4780

• [bug] [orm] Fixed regression caused by new selectinload for many-to-one logic where a primaryjoin condition not based on real foreign keys would cause KeyError if a related object did not exist for a given key value on the parent object.

  References: #4777

• [mysql] [usecase] Added reserved words ARRAY and MEMBER to the MySQL reserved words list, as MySQL 8.0 has now made these reserved.

  References: #4783

• [bug] [events] Fixed issue in event system where using the once=True flag with dynamically generated listener functions would cause event registration of future events to fail if those listener functions were garbage collected after they were used, due to an assumption that a listened function is strongly referenced. The "once" wrapped is now modified to strongly reference the inner function persistently, and documentation is updated that using "once" does not imply automatic de-registration of listener functions.

  References: #4794

• [bug] [mysql] Added another fix for an upstream MySQL 8 issue where a case sensitive table name is reported incorrectly in foreign key constraint reflection, this is an extension of the fix first added for #4344 which affects a case sensitive column name. The new issue occurs through MySQL 8.0.17, so the general logic of the 88718 fix remains in place.

  References: #4751

• [mssql] [usecase] Added new mssql.try_cast() construct for SQL Server which emits "TRY_CAST" syntax. Pull request courtesy Leonel Atencio.

  References: #4782

• [bug] [orm] Fixed bug where using Query.first() or a slice expression in conjunction with a query that has an expression based "offset" applied would raise TypeError, due to an "or" conditional against "offset" that did not expect it to be a SQL expression as opposed to an integer or None.

  References: #4803

1.3.6

Released: July 21, 2019

• [bug] [engine] Fixed bug where using reflection function such as MetaData.reflect() with an Engine object that had execution options applied to it would fail, as the resulting OptionEngine proxy object failed to include a .engine attribute used within the reflection routines.

  References: #4754

• [bug] [mysql] Fixed bug where the special logic to render "NULL" for the TIMESTAMP datatype when nullable=True would not work if the column's datatype were a TypeDecorator or a Variant. The logic now ensures that it unwraps down to the original TIMESTAMP so that this special case NULL keyword is correctly rendered when requested.

  References: #4743

• [orm] [performance] The optimization applied to selectin loading in #4340 where a JOIN is not needed to eagerly load related items is now applied to many-to-one relationships as well, so that only the related table is queried for a simple join condition. In this case, the related items are queried based on the value of a foreign key column on the parent; if these columns are deferred or otherwise not loaded on any of the parent objects in the collection, the loader falls back to the JOIN method.

  References: #4775

• [bug] [orm] Fixed regression caused by #4365 where a join from an entity to itself without using aliases no longer raises an informative error message, instead failing on an assertion. The informative error condition has been restored.

  References: #4773

• [feature] [orm] Added new loader option method Load.options() which allows loader options to be constructed hierarchically, so that many sub-options can be applied to a particular path without needing to call defaultload() many times. Thanks to Alessio Bogon for the idea.

  References: #4736

• [postgresql] [usecase] Added support for reflection of indexes on PostgreSQL partitioned tables, which was added to PostgreSQL as of version 11.

  References: #4771

• [bug] [mysql] Enhanced MySQL/MariaDB version string parsing to accommodate for exotic MariaDB version strings where the "MariaDB" word is embedded among other alphanumeric characters such as "MariaDBV1". This detection is critical in order to correctly accommodate for API features that have split between MySQL and MariaDB such as the "transaction_isolation" system variable.

  References: #4624

• [bug] [mssql] Ensured that the queries used to reflect indexes and view definitions will explicitly CAST string parameters into NVARCHAR, as many SQL Server drivers frequently treat string values, particularly those with non-ascii characters or larger string values, as TEXT which often don't compare correctly against VARCHAR characters in SQL Server's information schema tables for some reason. These CAST operations already take place for reflection queries against SQL Server information_schema. tables but were missing from three additional queries that are against sys. tables.

  References: #4745

• [bug] [orm] Fixed an issue where the orm._ORMJoin.join() method, which is a not-internally-used ORM-level method that exposes what is normally an internal process of Query.join(), did not propagate the full and outerjoin keyword arguments correctly. Pull request courtesy Denis Kataev.

  References: #4713

• [bug] [sql] Adjusted the initialization for Enum to minimize how often it invokes the .__members__ attribute of a given PEP-435 enumeration object, to suit the case where this attribute is expensive to invoke, as is the case for some popular third party enumeration libraries.

  References: #4758

• [bug] [orm] Fixed bug where a many-to-one relationship that specified uselist=True would fail to update correctly during a primary key change where a related column needs to change.

  References: #4772

• [bug] [orm] Fixed bug where the detection for many-to-one or one-to-one use with a "dynamic" relationship, which is an invalid configuration, would fail to raise if the relationship were configured with uselist=True. The current fix is that it warns, instead of raises, as this would otherwise be backwards incompatible, however in a future release it will be a raise.

  References: #4772

• [bug] [orm] Fixed bug where a synonym created against a mapped attribute that does not exist yet, as is the case when it refers to backref before mappers are configured, would raise recursion errors when trying to test for attributes on it which ultimately don't exist (as occurs when the classes are run through Sphinx autodoc), as the unconfigured state of the synonym would put it into an attribute not found loop.

  References: #4767

• [postgresql] [usecase] Added support for multidimensional Postgresql array literals via nesting the postgresql.array object within another one. The multidimensional array type is detected automatically.

  References: #4756

• [bug] [postgresql] [sql] Fixed issue where the array_agg construct in combination with FunctionElement.filter() would not produce the correct operator precedence in combination with the array index operator.

  References: #4760

• [bug] [sql] Fixed an unlikely issue where the "corresponding column" routine for unions and other CompoundSelect objects could return the wrong column in some overlapping column situtations, thus potentially impacting some ORM operations when set operations are in use, if the underlying select() constructs were used previously in other similar kinds of routines, due to a cached value not being cleared.

  References: #4747

• [sqlite] [usecase] Added support for composite (tuple) IN operators with SQLite, by rendering the VALUES keyword for this backend. As other backends such as DB2 are known to use the same syntax, the syntax is enabled in the base compiler using a dialect-level flag tuple_in_values. The change also includes support for "empty IN tuple" expressions for SQLite when using "in_()" between a tuple value and an empty set.

  References: #4766

1.3.5

Released: June 17, 2019

• [bug] [mysql] Fixed bug where MySQL ON DUPLICATE KEY UPDATE would not accommodate setting a column to the value NULL. Pull request courtesy Lukáš Banič.

  References: #4715

• [bug] [orm] Fixed a series of related bugs regarding joined table inheritance more than two levels deep, in conjunction with modification to primary key values, where those primary key columns are also linked together in a foreign key relationship as is typical for joined table inheritance. The intermediary table in a three-level inheritance hierarchy will now get its UPDATE if only the primary key value has changed and passive_updates=False (e.g. foreign key constraints not being enforced), whereas before it would be skipped; similarly, with passive_updates=True (e.g. ON UPDATE CASCADE in effect), the third-level table will not receive an UPDATE statement as was the case earlier which would fail since CASCADE already modified it. In a related issue, a relationship linked to a three-level inheritance hierarchy on the primary key of an intermediary table of a joined-inheritance hierarchy will also correctly have its foreign key column updated when the parent object's primary key is modified, even if that parent object is a subclass of the linked parent class, whereas before these classes would not be counted.

  References: #4723

• [bug] [orm] Fixed bug where the Mapper.all_orm_descriptors accessor would return an entry for the Mapper itself under the declarative __mapper___ key, when this is not a descriptor. The .is_attribute flag that's present on all InspectionAttr objects is now consulted, which has also been modified to be True for an association proxy, as it was erroneously set to False for this object.

  References: #4729

• [bug] [orm] Fixed regression in Query.join() where the aliased=True flag would not properly apply clause adaptation to filter criteria, if a previous join were made to the same entity. This is because the adapters were placed in the wrong order. The order has been reversed so that the adapter for the most recent aliased=True call takes precedence as was the case in 1.2 and earlier. This broke the "elementtree" examples among other things.

  References: #4704

• [bug] [orm] [py3k] Replaced the Python compatbility routines for getfullargspec() with a fully vendored version from Python 3.3. Originally, Python was emitting deprecation warnings for this function in Python 3.8 alphas. While this change was reverted, it was observed that Python 3 implementations for getfullargspec() are an order of magnitude slower as of the 3.4 series where it was rewritten against Signature. While Python plans to improve upon this situation, SQLAlchemy projects for now are using a simple replacement to avoid any future issues.

  References: #4674

• [bug] [orm] Reworked the attribute mechanics used by AliasedClass to no longer rely upon calling __getattribute__ on the MRO of the wrapped class, and to instead resolve the attribute normally on the wrapped class using getattr(), and then unwrap/adapt that. This allows a greater range of attribute styles on the mapped class including special __getattr__() schemes; but it also makes the code simpler and more resilient in general.

  References: #4694

• [postgresql] [usecase] Added support for column sorting flags when reflecting indexes for PostgreSQL, including ASC, DESC, NULLSFIRST, NULLSLAST. Also adds this facility to the reflection system in general which can be applied to other dialects in future releases. Pull request courtesy Eli Collins.

  References: #4717

• [bug] [postgresql] Fixed bug where PostgreSQL dialect could not correctly reflect an ENUM datatype that has no members, returning a list with None for the get_enums() call and raising a TypeError when reflecting a column which has such a datatype. The inspection now returns an empty list.

  References: #4701

• [bug] [sql] Fixed a series of quoting issues which all stemmed from the concept of the literal_column() construct, which when being "proxied" through a subquery to be referred towards by a label that matches its text, the label would not have quoting rules applied to it, even if the string in the Label were set up as a quoted_name construct. Not applying quoting to the text of the Label is a bug because this text is strictly a SQL identifier name and not a SQL expression, and the string should not have quotes embedded into it already unlike the literal_column() which it may be applied towards. The existing behavior of a non-labeled literal_column() being propagated as is on the outside of a subquery is maintained in order to help with manual quoting schemes, although it's not clear if valid SQL can be generated for such a construct in any case.

  References: #4730

1.3.4

Released: May 27, 2019

• [feature] [mssql] Added support for SQL Server filtered indexes, via the mssql_where parameter which works similarly to that of the postgresql_where index function in the PostgreSQL dialect.

  References: #4657

• [bug] [misc] Removed errant "sqla_nose.py" symbol from MANIFEST.in which created an undesirable warning message.

  References: #4625

• [bug] [sql] Fixed that the GenericFunction class was inadvertently registering itself as one of the named functions. Pull request courtesy Adrien Berchet.

  References: #4653

• [bug] [engine] [postgresql] Moved the "rollback" which occurs during dialect initialization so that it occurs after additional dialect-specific initialize steps, in particular those of the psycopg2 dialect which would inadvertently leave transactional state on the first new connection, which could interfere with some psycopg2-specific APIs which require that no transaction is started. Pull request courtesy Matthew Wilkes.

  References: #4663

• [bug] [orm] Fixed issue where the AttributeEvents.active_history flag would not be set for an event listener that propgated to a subclass via the AttributeEvents.propagate flag. This bug has been present for the full span of the AttributeEvents system.

  References: #4695

• [bug] [orm] Fixed regression where new association proxy system was still not proxying hybrid attributes when they made use of the @hybrid_property.expression decorator to return an alternate SQL expression, or when the hybrid returned an arbitrary PropComparator, at the expression level. This involved further generalization of the heuristics used to detect the type of object being proxied at the level of QueryableAttribute, to better detect if the descriptor ultimately serves mapped classes or column expressions.

  References: #4690

• [bug] [orm] Applied the mapper "configure mutex" against the declarative class mapping process, to guard against the race which can occur if mappers are used while dynamic module import schemes are still in the process of configuring mappers for related classes. This does not guard against all possible race conditions, such as if the concurrent import has not yet encountered the dependent classes as of yet, however it guards against as much as possible within the SQLAlchemy declarative process.

  References: #4686

• [bug] [mssql] Added error code 20047 to "is_disconnect" for pymssql. Pull request courtesy Jon Schuff.

  References: #4680

• [bug] [orm] [postgresql] Fixed an issue where the "number of rows matched" warning would emit even if the dialect reported "supports_sane_multi_rowcount=False", as is the case for psycogp2 with use_batch_mode=True and others.

  References: #4661

• [bug] [sql] Fixed issue where double negation of a boolean column wouldn't reset the "NOT" operator.

  References: #4618

• [bug] [mysql] Added support for DROP CHECK constraint which is required by MySQL 8.0.16 to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT. The logic distinguishes between the two syntaxes by checking the server version string for MariaDB presence. Alembic migrations has already worked around this issue by implementing its own DROP for MySQL / MariaDB CHECK constraints, however this change implements it straight in Core so that its available for general use. Pull request courtesy Hannes Hansen.

  References: #4650

• [bug] [orm] A warning is now emitted for the case where a transient object is being merged into the session with Session.merge() when that object is already transient in the Session. This warns for the case where the object would normally be double-inserted.

  References: #4647

• [bug] [orm] Fixed regression in new relationship m2o comparison logic first introduced at change_4359 when comparing to an attribute that is persisted as NULL and is in an un-fetched state in the mapped instance. Since the attribute has no explicit default, it needs to default to NULL when accessed in a persistent setting.

  References: #4676

• [bug] [sql] The GenericFunction namespace is being migrated so that function names are looked up in a case-insensitive manner, as SQL functions do not collide on case sensitive differences nor is this something which would occur with user-defined functions or stored procedures. Lookups for functions declared with GenericFunction now use a case insensitive scheme, however a deprecation case is supported which allows two or more GenericFunction objects with the same name of different cases to exist, which will cause case sensitive lookups to occur for that particular name, while emitting a warning at function registration time. Thanks to Adrien Berchet for a lot of work on this complicated feature.

  References: #4569

1.3.3

Released: April 15, 2019

• [bug] [postgresql] Fixed regression from release 1.3.2 caused by #4562 where a URL that contained only a query string and no hostname, such as for the purposes of specifying a service file with connection information, would no longer be propagated to psycopg2 properly. The change in #4562 has been adjusted to further suit psycopg2's exact requirements, which is that if there are any connection parameters whatsoever, the "dsn" parameter is no longer required, so in this case the query string parameters are passed alone.

  References: #4601

• [bug] [pool] Fixed behavioral regression as a result of deprecating the "use_threadlocal" flag for Pool, where the SingletonThreadPool no longer makes use of this option which causes the "rollback on return" logic to take place when the same Engine is used multiple times in the context of a transaction to connect or implicitly execute, thereby cancelling the transaction. While this is not the recommended way to work with engines and connections, it is nonetheless a confusing behavioral change as when using SingletonThreadPool, the transaction should stay open regardless of what else is done with the same engine in the same thread. The use_threadlocal flag remains deprecated however the SingletonThreadPool now implements its own version of the same logic.

  References: #4585

• [bug] [orm] Fixed 1.3 regression in new "ambiguous FROMs" query logic introduced in change_4365 where a Query that explicitly places an entity in the FROM clause with Query.select_from() and also joins to it using Query.join() would later cause an "ambiguous FROM" error if that entity were used in additional joins, as the entity appears twice in the "from" list of the Query. The fix resolves this ambiguity by folding the standalone entity into the join that it's already a part of in the same way that ultimately happens when the SELECT statement is rendered.

  References: #4584

• [bug] [ext] Fixed bug where using copy.copy() or copy.deepcopy() on MutableList would cause the items within the list to be duplicated, due to an inconsistency in how Python pickle and copy both make use of __getstate__() and __setstate__() regarding lists. In order to resolve, a __reduce_ex__ method had to be added to MutableList. In order to maintain backwards compatibility with existing pickles based on __getstate__(), the __setstate__() method remains as well; the test suite asserts that pickles made against the old version of the class can still be deserialized by the pickle module.

  References: #4603

• [bug] [orm] Adjusted the Query.filter_by() method to not call and() internally against multiple criteria, instead passing it off to Query.filter() as a series of criteria, instead of a single criteria. This allows Query.filter_by() to defer to Query.filter()'s treatment of variable numbers of clauses, including the case where the list is empty. In this case, the Query object will not have a .whereclause, which allows subsequent "no whereclause" methods like Query.select_from() to behave consistently.

  References: #4606

• [bug] [mssql] Fixed issue in SQL Server dialect where if a bound parameter were present in an ORDER BY expression that would ultimately not be rendered in the SQL Server version of the statement, the parameters would still be part of the execution parameters, leading to DBAPI-level errors. Pull request courtesy Matt Lewellyn.

  References: #4587

1.3.2

Released: April 2, 2019

• [bug] [documentation] [sql] Thanks to change_3981, we no longer need to rely on recipes that subclass dialect-specific types directly, TypeDecorator can now handle all cases. Additionally, the above change made it slightly less likely that a direct subclass of a base SQLAlchemy type would work as expected, which could be misleading. Documentation has been updated to use TypeDecorator for these examples including the PostgreSQL "ArrayOfEnum" example datatype and direct support for the "subclass a type directly" has been removed.

  References: #4580

• [bug] [postgresql] Modified the Select.with_for_update.of parameter so that if a join or other composed selectable is passed, the individual Table objects will be filtered from it, allowing one to pass a join() object to the parameter, as occurs normally when using joined table inheritance with the ORM. Pull request courtesy Raymond Lu.

  References: #4550

• [feature] [postgresql] Added support for parameter-less connection URLs for the psycopg2 dialect, meaning, the URL can be passed to create_engine() as "postgresql+psycopg2://" with no additional arguments to indicate an empty DSN passed to libpq, which indicates to connect to "localhost" with no username, password, or database given. Pull request courtesy Julian Mehnle.

  References: #4562

• [bug] [ext] [orm] Restored instance-level support for plain Python descriptors, e.g. @property objects, in conjunction with association proxies, in that if the proxied object is not within ORM scope at all, it gets classified as "ambiguous" but is proxed directly. For class level access, a basic class level__get__() now returns the AmbiguousAssociationProxyInstance directly, rather than raising its exception, which is the closest approximation to the previous behavior that returned the AssociationProxy itself that's possible. Also improved the stringification of these objects to be more descriptive of current state.

  References: #4573, #4574

• [bug] [orm] Fixed bug where use of with_polymorphic() or other aliased construct would not properly adapt when the aliased target were used as the Select.correlate_except() target of a subquery used inside of a column_property(). This required a fix to the clause adaption mechanics to properly handle a selectable that shows up in the "correlate except" list, in a similar manner as which occurs for selectables that show up in the "correlate" list. This is ultimately a fairly fundamental bug that has lasted for a long time but it is hard to come across it.

  References: #4537

• [bug] [orm] Fixed regression where a new error message that was supposed to raise when attempting to link a relationship option to an AliasedClass without using PropComparator.of_type() would instead raise an AttributeError. Note that in 1.3, it is no longer valid to create an option path from a plain mapper relationship to an AliasedClass without using PropComparator.of_type().

  References: #4566

1.3.1

Released: March 9, 2019

• [bug] [mssql] A commit() is emitted after an isolation level change to SNAPSHOT, as both pyodbc and pymssql open an implicit transaction which blocks subsequent SQL from being emitted in the current transaction.

  This change is also backported to: 1.2.19

  References: #4536

• [bug] [mssql] Fixed regression in SQL Server reflection due to #4393 where the removal of open-ended **kw from the Float datatype caused reflection of this type to fail due to a "scale" argument being passed.

  References: #4525

• [bug] [ext] [orm] Fixed regression where an association proxy linked to a synonym would no longer work, both at instance level and at class level.

  References: #4522

1.3.0

Released: March 4, 2019

• [feature] [schema] Added new parameters Table.resolve_fks and MetaData.reflect.resolve_fks which when set to False will disable the automatic reflection of related tables encountered in ForeignKey objects, which can both reduce SQL overhead for omitted tables as well as avoid tables that can't be reflected for database-specific reasons. Two Table objects present in the same MetaData collection can still refer to each other even if the reflection of the two tables occurred separately.

  References: #4517

• [feature] [orm] The Query.get() method can now accept a dictionary of attribute keys and values as a means of indicating the primary key value to load; is particularly useful for composite primary keys. Pull request courtesy Sanjana S.

  References: #4316

• [feature] [orm] A SQL expression can now be assigned to a primary key attribute for an ORM flush in the same manner as ordinary attributes as described in flush_embedded_sql_expressions where the expression will be evaulated and then returned to the ORM using RETURNING, or in the case of pysqlite, works using the cursor.lastrowid attribute.Requires either a database that supports RETURNING (e.g. Postgresql, Oracle, SQL Server) or pysqlite.

  References: #3133

• [bug] [sql] The Alias class and related subclasses CTE, Lateral and TableSample have been reworked so that it is not possible for a user to construct the objects directly. These constructs require that the standalone construction function or selectable-bound method be used to instantiate new objects.

  References: #4509

• [engine] [feature] Revised the formatting for StatementError when stringified. Each error detail is broken up over multiple newlines instead of spaced out on a single line. Additionally, the SQL representation now stringifies the SQL statement rather than using repr(), so that newlines are rendered as is. Pull request courtesy Nate Clark.

  References: #4500

1.3.0b3

Released: February 8, 2019

• [bug] [ext] Implemented a more comprehensive assignment operation (e.g. "bulk replace") when using association proxy with sets or dictionaries. Fixes the problem of redundant proxy objects being created to replace the old ones, which leads to excessive events and SQL and in the case of unique constraints will cause the flush to fail.

  References: #2642

• [bug] [postgresql] Fixed issue where using an uppercase name for an index type (e.g. GIST, BTREE, etc. ) or an EXCLUDE constraint would treat it as an identifier to be quoted, rather than rendering it as is. The new behavior converts these types to lowercase and ensures they contain only valid SQL characters.

  References: #4473

• [bug] [orm] Improved the behavior of orm.with_polymorphic() in conjunction with loader options, in particular wildcard operations as well as orm.load_only(). The polymorphic object will be more accurately targeted so that column-level options on the entity will correctly take effect.The issue is a continuation of the same kinds of things fixed in #4468.

  References: #4469

• [bug] [sql] Fully removed the behavior of strings passed directly as components of a select() or Query object being coerced to text() constructs automatically; the warning that has been emitted is now an ArgumentError or in the case of order_by() / group_by() a CompileError. This has emitted a warning since version 1.0 however its presence continues to create concerns for the potential of mis-use of this behavior.

  Note that public CVEs have been posted for order_by() / group_by() which are resolved by this commit: CVE-2019-7164 CVE-2019-7548

  References: #4481

• [bug] [sql] Quoting is applied to Function names, those which are usually but not necessarily generated from the sql.func construct, at compile time if they contain illegal characters, such as spaces or punctuation. The names are as before treated as case insensitive however, meaning if the names contain uppercase or mixed case characters, that alone does not trigger quoting. The case insensitivity is currently maintained for backwards compatibility.

  References: #4467

• [bug] [sql] Added "SQL phrase validation" to key DDL phrases that are accepted as plain strings, including ForeignKeyConstraint.on_delete, ForeignKeyConstraint.on_update, ExcludeConstraint.using, ForeignKeyConstraint.initially, for areas where a series of SQL keywords only are expected.Any non-space characters that suggest the phrase would need to be quoted will raise a CompileError. This change is related to the series of changes committed as part of #4481.

  References: #4481

• [bug] [declarative] [orm] Added some helper exceptions that invoke when a mapping based on AbstractConcreteBase, DeferredReflection, or AutoMap is used before the mapping is ready to be used, which contain descriptive information on the class, rather than falling through into other failure modes that are less informative.

  References: #4470

• [change] [tests] The test system has removed support for Nose, which is unmaintained for several years and is producing warnings under Python 3. The test suite is currently standardized on Pytest. Pull request courtesy Parth Shandilya.

  References: #4460

1.3.0b2

Released: January 25, 2019

• [bug] [ext] Fixed a regression in 1.3.0b1 caused by #3423 where association proxy objects that access an attribute that's only present on a polymorphic subclass would raise an AttributeError even though the actual instance being accessed was an instance of that subclass.

  References: #4401

• [bug] [orm] Fixed long-standing issue where duplicate collection members would cause a backref to delete the association between the member and its parent object when one of the duplicates were removed, as occurs as a side effect of swapping two objects in one statement.

  References: #1103

• [bug] [mssql] The literal_processor for the Unicode and UnicodeText datatypes now render an N character in front of the literal string expression as required by SQL Server for Unicode string values rendered in SQL expressions.

  References: #4442

• [feature] [orm] Implemented a new feature whereby the AliasedClass construct can now be used as the target of a relationship(). This allows the concept of "non primary mappers" to no longer be necessary, as the AliasedClass is much easier to configure and automatically inherits all the relationships of the mapped class, as well as preserves the ability for loader options to work normally.

  References: #4423

• [bug] [orm] Extended the fix first made as part of #3287, where a loader option made against a subclass using a wildcard would extend itself to include application of the wildcard to attributes on the super classes as well, to a "bound" loader option as well, e.g. in an expression like Load(SomeSubClass).load_only('foo'). Columns that are part of the parent class of SomeSubClass will also be excluded in the same way as if the unbound option load_only('foo') were used.

  References: #4373

• [bug] [orm] Improved error messages emitted by the ORM in the area of loader option traversal. This includes early detection of mis-matched loader strategies along with a clearer explanation why these strategies don't match.

  References: #4433

• [change] [orm] Added a new function close_all_sessions() which takes over the task of the Session.close_all() method, which is now deprecated as this is confusing as a classmethod. Pull request courtesy Augustin Trancart.

  References: #4412

• [feature] [orm] Added new MapperEvents.before_mapper_configured() event. This event complements the other "configure" stage mapper events with a per mapper event that receives each Mapper right before its configure step, and additionally may be used to prevent or delay the configuration of specific Mapper objects using a new return value orm.interfaces.EXT_SKIP. See the documentation link for an example.

  References: #4397

• [bug] [orm] The "remove" event for collections is now called before the item is removed in the case of the collection.remove() method, as is consistent with the behavior for most other forms of collection item removal (such as __delitem__, replacement under __setitem__). For pop() methods, the remove event still fires after the operation.

• [bug] [orm declarative] Added a __clause_element__() method to ColumnProperty which can allow the usage of a not-fully-declared column or deferred attribute in a declarative mapped class slightly more friendly when it's used in a constraint or other column-oriented scenario within the class declaration, though this still can't work in open-ended expressions; prefer to call the ColumnProperty.expression attribute if receiving TypeError.

  References: #4372

• [bug] [engine] [orm] Added accessors for execution options to Core and ORM, via Query.get_execution_options(), Connection.get_execution_options(), Engine.get_execution_options(), and Executable.get_execution_options(). PR courtesy Daniel Lister.

  References: #4464

• [bug] [orm] Fixed issue in association proxy due to #3423 which caused the use of custom PropComparator objects with hybrid attributes, such as the one demonstrated in the dictlike-polymorphic example to not function within an association proxy. The strictness that was added in #3423 has been relaxed, and additional logic to accommodate for an association proxy that links to a custom hybrid have been added.

  References: #4446

• [change] [general] A large change throughout the library has ensured that all objects, parameters, and behaviors which have been noted as deprecated or legacy now emit DeprecationWarning warnings when invoked.As the Python 3 interpreter now defaults to displaying deprecation warnings, as well as that modern test suites based on tools like tox and pytest tend to display deprecation warnings, this change should make it easier to note what API features are obsolete. A major rationale for this change is so that long- deprecated features that nonetheless still see continue to see real world use can finally be removed in the near future; the biggest example of this are the SessionExtension and MapperExtension classes as well as a handful of other pre-event extension hooks, which have been deprecated since version 0.7 but still remain in the library. Another is that several major longstanding behaviors are to be deprecated as well, including the threadlocal engine strategy, the convert_unicode flag, and non primary mappers.

  References: #4393

• [change] [engine] The "threadlocal" engine strategy which has been a legacy feature of SQLAlchemy since around version 0.2 is now deprecated, along with the Pool.threadlocal parameter of Pool which has no effect in most modern use cases.

  References: #4393

• [change] [sql] The create_engine.convert_unicode and String.convert_unicode parameters have been deprecated. These parameters were built back when most Python DBAPIs had little to no support for Python Unicode objects, and SQLAlchemy needed to take on the very complex task of marshalling data and SQL strings between Unicode and bytestrings throughout the system in a performant way. Thanks to Python 3, DBAPIs were compelled to adapt to Unicode-aware APIs and today all DBAPIs supported by SQLAlchemy support Unicode natively, including on Python 2, allowing this long-lived and very complicated feature to finally be (mostly) removed. There are still of course a few Python 2 edge cases where SQLAlchemy has to deal with Unicode however these are handled automatically; in modern use, there should be no need for end-user interaction with these flags.

  References: #4393

• [bug] [orm] Implemented the .get_history() method, which also implies availability of AttributeState.history, for synonym() attributes. Previously, trying to access attribute history via a synonym would raise an AttributeError.

  References: #3777

• [engine] [feature] Added public accessor QueuePool.timeout() that returns the configured timeout for a QueuePool object. Pull request courtesy Irina Delamare.

  References: #3689

• [feature] [sql] Amended the AnsiFunction class, the base of common SQL functions like CURRENT_TIMESTAMP, to accept positional arguments like a regular ad-hoc function. This to suit the case that many of these functions on specific backends accept arguments such as "fractional seconds" precision and such. If the function is created with arguments, it renders the parenthesis and the arguments. If no arguments are present, the compiler generates the non-parenthesized form.

  References: #4386

1.3.0b1

Released: November 16, 2018

• [feature] [sql] Refactored SQLCompiler to expose a SQLCompiler.group_by_clause() method similar to the SQLCompiler.order_by_clause() and SQLCompiler.limit_clause() methods, which can be overridden by dialects to customize how GROUP BY renders. Pull request courtesy Samuel Chou.

  This change is also backported to: 1.2.13

• [bug] [orm] Fixed bug where use of Lateral construct in conjunction with Query.join() as well as Query.select_entity_from() would not apply clause adaption to the right side of the join. "lateral" introduces the use case of the right side of a join being correlatable. Previously, adaptation of this clause wasn't considered. Note that in 1.2 only, a selectable introduced by Query.subquery() is still not adapted due to #4304; the selectable needs to be produced by the select() function to be the right side of the "lateral" join.

  This change is also backported to: 1.2.12

  References: #4334

• [feature] [oracle] Added a new event currently used only by the cx_Oracle dialect, DialectEvents.setiputsizes(). The event passes a dictionary of BindParameter objects to DBAPI-specific type objects that will be passed, after conversion to parameter names, to the cx_Oracle cursor.setinputsizes() method. This allows both visibility into the setinputsizes process as well as the ability to alter the behavior of what datatypes are passed to this method.

  This change is also backported to: 1.2.9

  References: #4290

• [ext] [feature] Added new attribute Query.lazy_loaded_from which is populated with an InstanceState that is using this Query in order to lazy load a relationship. The rationale for this is that it serves as a hint for the horizontal sharding feature to use, such that the identity token of the state can be used as the default identity token to use for the query within id_chooser().

  This change is also backported to: 1.2.9

  References: #4243

• [feature] [postgresql] Added new PG type postgresql.REGCLASS which assists in casting table names to OID values. Pull request courtesy Sebastian Bank.

  This change is also backported to: 1.2.7

  References: #4160

• [feature] [orm] Added new feature Query.only_return_tuples(). Causes the Query object to return keyed tuple objects unconditionally even if the query is against a single entity. Pull request courtesy Eric Atkin.

  This change is also backported to: 1.2.5

• [bug] [ext] Reworked AssociationProxy to store state that's specific to a parent class in a separate object, so that a single AssociationProxy can serve for multiple parent classes, as is intrinsic to inheritance, without any ambiguity in the state returned by it. A new method AssociationProxy.for_class() is added to allow inspection of class-specific state.

  References: #3423

• [bug] [oracle] Updated the parameters that can be sent to the cx_Oracle DBAPI to both allow for all current parameters as well as for future parameters not added yet. In addition, removed unused parameters that were deprecated in version 1.2, and additionally we are now defaulting "threaded" to False.

  References: #4369

• [bug] [oracle] The Oracle dialect will no longer use the NCHAR/NCLOB datatypes represent generic unicode strings or clob fields in conjunction with Unicode and UnicodeText unless the flag use_nchar_for_unicode=True is passed to create_engine() - this includes CREATE TABLE behavior as well as setinputsizes() for bound parameters. On the read side, automatic Unicode conversion under Python 2 has been added to CHAR/VARCHAR/CLOB result rows, to match the behavior of cx_Oracle under Python 3. In order to mitigate the performance hit under Python 2, SQLAlchemy's very performant (when C extensions are built) native Unicode handlers are used under Python 2.

  References: #4242

• [bug] [orm] Fixed issue regarding passive_deletes="all", where the foreign key attribute of an object is maintained with its value even after the object is removed from its parent collection. Previously, the unit of work would set this to NULL even though passive_deletes indicated it should not be modified.

  References: #3844

• [bug] [ext] The long-standing behavior of the association proxy collection maintaining only a weak reference to the parent object is reverted; the proxy will now maintain a strong reference to the parent for as long as the proxy collection itself is also in memory, eliminating the "stale association proxy" error. This change is being made on an experimental basis to see if any use cases arise where it causes side effects.

  References: #4268

• [bug] [sql] Added "like" based operators as "comparison" operators, including ColumnOperators.startswith() ColumnOperators.endswith() ColumnOperators.ilike() ColumnOperators.notilike() among many others, so that all of these operators can be the basis for an ORM "primaryjoin" condition.

  References: #4302

• [feature] [sqlite] Added support for SQLite's json functionality via the new SQLite implementation for types.JSON, sqlite.JSON. The name used for the type is JSON, following an example found at SQLite's own documentation. Pull request courtesy Ilja Everilä.

  References: #3850

• [engine] [feature] Added new "lifo" mode to QueuePool, typically enabled by setting the flag create_engine.pool_use_lifo to True. "lifo" mode means the same connection just checked in will be the first to be checked out again, allowing excess connections to be cleaned up from the server side during periods of the pool being only partially utilized. Pull request courtesy Taem Park.

• [bug] [orm] Improved the behavior of a relationship-bound many-to-one object expression such that the retrieval of column values on the related object are now resilient against the object being detached from its parent Session, even if the attribute has been expired. New features within the InstanceState are used to memoize the last known value of a particular column attribute before its expired, so that the expression can still evaluate when the object is detached and expired at the same time. Error conditions are also improved using modern attribute state features to produce more specific messages as needed.

  References: #4359

• [feature] [mysql] Support added for the "WITH PARSER" syntax of CREATE FULLTEXT INDEX in MySQL, using the mysql_with_parser keyword argument. Reflection is also supported, which accommodates MySQL's special comment format for reporting on this option as well. Additionally, the "FULLTEXT" and "SPATIAL" index prefixes are now reflected back into the mysql_prefix index option.

  References: #4219

• [bug] [mysql] [orm] [postgresql] The ORM now doubles the "FOR UPDATE" clause within the subquery that renders in conjunction with joined eager loading in some cases, as it has been observed that MySQL does not lock the rows from a subquery. This means the query renders with two FOR UPDATE clauses; note that on some backends such as Oracle, FOR UPDATE clauses on subqueries are silently ignored since they are unnecessary. Additionally, in the case of the "OF" clause used primarily with PostgreSQL, the FOR UPDATE is rendered only on the inner subquery when this is used so that the selectable can be targeted to the table within the SELECT statement.

  References: #4246

• [feature] [mssql] Added fast_executemany=True parameter to the SQL Server pyodbc dialect, which enables use of pyodbc's new performance feature of the same name when using Microsoft ODBC drivers.

  References: #4158

• [bug] [ext] Fixed multiple issues regarding de-association of scalar objects with the association proxy. del now works, and additionally a new flag AssociationProxy.cascade_scalar_deletes is added, which when set to True indicates that setting a scalar attribute to None or deleting via del will also set the source association to None.

  References: #4308

• [ext] [feature] Added new feature BakedQuery.to_query(), which allows for a clean way of using one BakedQuery as a subquery inside of another BakedQuery without needing to refer explicitly to a Session.

  References: #4318

• [feature] [sqlite] Implemented the SQLite ON CONFLICT clause as understood at the DDL level, e.g. for primary key, unique, and CHECK constraints as well as specified on a Column to satisfy inline primary key and NOT NULL. Pull request courtesy Denis Kataev.

  References: #4360

• [feature] [postgresql] Added rudimental support for reflection of PostgreSQL partitioned tables, e.g. that relkind='p' is added to reflection queries that return table information.

  References: #4237

• [ext] [feature] The AssociationProxy now has standard column comparison operations such as ColumnOperators.like() and ColumnOperators.startswith() available when the target attribute is a plain column - the EXISTS expression that joins to the target table is rendered as usual, but the column expression is then use within the WHERE criteria of the EXISTS. Note that this alters the behavior of the .contains() method on the association proxy to make use of ColumnOperators.contains() when used on a column-based attribute.

  References: #4351

• [feature] [orm] Added new flag Session.bulk_save_objects.preserve_order to the Session.bulk_save_objects() method, which defaults to True. When set to False, the given mappings will be grouped into inserts and updates per each object type, to allow for greater opportunities to batch common operations together. Pull request courtesy Alessandro Cucci.

• [bug] [orm] Refactored Query.join() to further clarify the individual components of structuring the join. This refactor adds the ability for Query.join() to determine the most appropriate "left" side of the join when there is more than one element in the FROM list or the query is against multiple entities. If more than one FROM/entity matches, an error is raised that asks for an ON clause to be specified to resolve the ambiguity. In particular this targets the regression we saw in #4363 but is also of general use. The codepaths within Query.join() are now easier to follow and the error cases are decided more specifically at an earlier point in the operation.

  References: #4365

• [bug] [sql] Fixed issue with TypeEngine.bind_expression() and TypeEngine.column_expression() methods where these methods would not work if the target type were part of a Variant, or other target type of a TypeDecorator. Additionally, the SQL compiler now calls upon the dialect-level implementation when it renders these methods so that dialects can now provide for SQL-level processing for built-in types.

  References: #3981

• [bug] [orm] Fixed long-standing issue in Query where a scalar subquery such as produced by Query.exists(), Query.as_scalar() and other derivations from Query.statement would not correctly be adapted when used in a new Query that required entity adaptation, such as when the query were turned into a union, or a from_self(), etc. The change removes the "no adaptation" annotation from the select() object produced by the Query.statement accessor.

  References: #4304

• [bug] [declarative] [orm] Fixed bug where declarative would not update the state of the Mapper as far as what attributes were present, when additional attributes were added or removed after the mapper attribute collections had already been called and memoized. Additionally, a NotImplementedError is now raised if a fully mapped attribute (e.g. column, relationship, etc.) is deleted from a class that is currently mapped, since the mapper will not function correctly if the attribute has been removed.

  References: #4133

• [bug] [mssql] Deprecated the use of Sequence with SQL Server in order to affect the "start" and "increment" of the IDENTITY value, in favor of new parameters mssql_identity_start and mssql_identity_increment which set these parameters directly. Sequence will be used to generate real CREATE SEQUENCE DDL with SQL Server in a future release.

  References: #4362

• [feature] [mysql] Added support for the parameters in an ON DUPLICATE KEY UPDATE statement on MySQL to be ordered, since parameter order in a MySQL UPDATE clause is significant, in a similar manner as that described at updates_order_parameters. Pull request courtesy Maxim Bublis.

• [feature] [sql] Added Sequence to the "string SQL" system that will render a meaningful string expression ("<next sequence value: my_sequence>") when stringifying without a dialect a statement that includes a "sequence nextvalue" expression, rather than raising a compilation error.

  References: #4144

• [bug] [orm] An informative exception is re-raised when a primary key value is not sortable in Python during an ORM flush under Python 3, such as an Enum that has no __lt__() method; normally Python 3 raises a TypeError in this case. The flush process sorts persistent objects by primary key in Python so the values must be sortable.

  References: #4232

• [bug] [orm] Removed the collection converter used by the MappedCollection class. This converter was used only to assert that the incoming dictionary keys matched that of their corresponding objects, and only during a bulk set operation. The converter can interfere with a custom validator or AttributeEvents.bulk_replace() listener that wants to convert incoming values further. The TypeError which would be raised by this converter when an incoming key didn't match the value is removed; incoming values during a bulk assignment will be keyed to their value-generated key, and not the key that's explicitly present in the dictionary.

  Overall, @converter is superseded by the AttributeEvents.bulk_replace() event handler added as part of #3896.

  References: #3604

• [feature] [sql] Added new naming convention tokens column_0N_name, column_0_N_name, etc., which will render the names / keys / labels for all columns referenced by a particular constraint in a sequence. In order to accommodate for the length of such a naming convention, the SQL compiler's auto-truncation feature now applies itself to constraint names as well, which creates a shortened, deterministically generated name for the constraint that will apply to a target backend without going over the character limit of that backend.

  The change also repairs two other issues. One is that the column_0_key token wasn't available even though this token was documented, the other was that the referred_column_0_name token would inadvertently render the .key and not the .name of the column if these two values were different.

  References: #3989

• [ext] [feature] Added support for bulk Query.update() and Query.delete() to the ShardedQuery class within the horizontal sharding extension. This also adds an additional expansion hook to the bulk update/delete methods Query._execute_crud().

  References: #4196

• [feature] [sql] Added new logic to the "expanding IN" bound parameter feature whereby if the given list is empty, a special "empty set" expression that is specific to different backends is generated, thus allowing IN expressions to be fully dynamic including empty IN expressions.

  References: #4271

• [feature] [mysql] The "pre-ping" feature of the connection pool now uses the ping() method of the DBAPI connection in the case of mysqlclient, PyMySQL and mysql-connector-python. Pull request courtesy Maxim Bublis.

• [feature] [orm] The "selectin" loader strategy now omits the JOIN in the case of a simple one-to-many load, where it instead relies loads only from the related table, relying upon the foreign key columns of the related table in order to match up to primary keys in the parent table. This optimization can be disabled by setting the relationship.omit_join flag to False. Many thanks to Jayson Reis for the efforts on this.

  References: #4340

• [bug] [orm] Added new behavior to the lazy load that takes place when the "old" value of a many-to-one is retrieved, such that exceptions which would be raised due to either lazy="raise" or a detached session error are skipped.

  References: #4353

• [feature] [sql] The Python builtin dir() is now supported for a SQLAlchemy "properties" object, such as that of a Core columns collection (e.g. .c), mapper.attrs, etc. Allows iPython autocompletion to work as well. Pull request courtesy Uwe Korn.

• [feature] [orm] Added .info dictionary to the InstanceState class, the object that comes from calling inspect() on a mapped object.

  References: #4257

• [feature] [sql] Added new feature FunctionElement.as_comparison() which allows a SQL function to act as a binary comparison operation that can work within the ORM.

  References: #3831

• [bug] [orm] A long-standing oversight in the ORM, the __delete__ method for a many- to-one relationship was non-functional, e.g. for an operation such as del a.b. This is now implemented and is equivalent to setting the attribute to None.

  References: #4354

1.2.19

Released: April 15, 2019

• [bug] [orm] Fixed a regression in 1.2 due to the introduction of baked queries for relationship lazy loaders, where a race condition is created during the generation of the "lazy clause" which occurs within a memoized attribute. If two threads initialize the memoized attribute concurrently, the baked query could be generated with bind parameter keys that are then replaced with new keys by the next run, leading to a lazy load query that specifies the related criteria as None. The fix establishes that the parameter names are fixed before the new clause and parameter objects are generated, so that the names are the same every time.

  References: #4507

• [bug] [oracle] Added support for reflection of the NCHAR datatype to the Oracle dialect, and added NCHAR to the list of types exported by the Oracle dialect.

  References: #4506

• [bug] [examples] Fixed bug in large_resultsets example case where a re-named "id" variable due to code reformatting caused the test to fail. Pull request courtesy Matt Schuchhardt.

  References: #4528

• [bug] [mssql] A commit() is emitted after an isolation level change to SNAPSHOT, as both pyodbc and pymssql open an implicit transaction which blocks subsequent SQL from being emitted in the current transaction.

  References: #4536

• [bug] [engine] Comparing two objects of URL using __eq__() did not take port number into consideration, two objects differing only by port number were considered equal. Port comparison is now added in __eq__() method of URL, objects differing by port number are now not equal. Additionally, __ne__() was not implemented for URL which caused unexpected result when != was used in Python2, since there are no implied relationships among the comparison operators in Python2.

  References: #4406

1.2.18

Released: February 15, 2019

• [bug] [orm] Fixed a regression in 1.2 where a wildcard/load_only loader option would not work correctly against a loader path where of_type() were used to limit to a particular subclass. The fix only works for of_type() of a simple subclass so far, not a with_polymorphic entity which will be addressed in a separate issue; it is unlikely this latter case was working previously.

  References: #4468

• [bug] [orm] Fixed fairly simple but critical issue where the SessionEvents.pending_to_persistent() event would be invoked for objects not just when they move from pending to persistent, but when they were also already persistent and just being updated, thus causing the event to be invoked for all objects on every update.

  References: #4489

• [bug] [sql] Fixed issue where the JSON type had a read-only JSON.should_evaluate_none attribute, which would cause failures when making use of the TypeEngine.evaluates_none() method in conjunction with this type. Pull request courtesy Sanjana S.

  References: #4485

• [bug] [mssql] Fixed bug where the SQL Server "IDENTITY_INSERT" logic that allows an INSERT to proceed with an explicit value on an IDENTITY column was not detecting the case where Insert.values() were used with a dictionary that contained a Column as key and a SQL expression as a value.

  References: #4499

• [bug] [sqlite] Fixed bug in SQLite DDL where using an expression as a server side default required that it be contained within parenthesis to be accepted by the sqlite parser. Pull request courtesy Bartlomiej Biernacki.

  References: #4474

• [bug] [mysql] Fixed a second regression caused by #4344 (the first was #4361), which works around MySQL issue 88718, where the lower casing function used was not correct for Python 2 with OSX/Windows casing conventions, which would then raise TypeError. Full coverage has been added to this logic so that every codepath is exercised in a mock style for all three casing conventions on all versions of Python. MySQL 8.0 has meanwhile fixed issue 88718 so the workaround is only applies to a particular span of MySQL 8.0 versions.

  References: #4492

1.2.17

Released: January 25, 2019

• [feature] [orm] Added new event hooks QueryEvents.before_compile_update() and QueryEvents.before_compile_delete() which complement QueryEvents.before_compile() in the case of the Query.update() and Query.delete() methods.

  References: #4461

• [bug] [postgresql] Revised the query used when reflecting CHECK constraints to make use of the pg_get_constraintdef function, as the consrc column is being deprecated in PG 12. Thanks to John A Stevenson for the tip.

  References: #4463

• [bug] [orm] Fixed issue where when using single-table inheritance in conjunction with a joined inheritance hierarchy that uses "with polymorphic" loading, the "single table criteria" for that single-table entity could get confused for that of other entities from the same hierarchy used in the same query.The adaption of the "single table criteria" is made more specific to the target entity to avoid it accidentally getting adapted to other tables in the query.

  References: #4454

• [bug] [oracle] Fixed regression in integer precision logic due to the refactor of the cx_Oracle dialect in 1.2. We now no longer apply the cx_Oracle.NATIVE_INT type to result columns sending integer values (detected as positive precision with scale ==0) which encounters integer overflow issues with values that go beyond the 32 bit boundary. Instead, the output variable is left untyped so that cx_Oracle can choose the best option.

  References: #4457

1.2.16

Released: January 11, 2019

• Fixed issue in "expanding IN" feature where using the same bound parameter name more than once in a query would lead to a KeyError within the process of rewriting the parameters in the query.

  References: #4394

• [bug] [postgresql] Fixed issue where a postgresql.ENUM or a custom domain present in a remote schema would not be recognized within column reflection if the name of the enum/domain or the name of the schema required quoting. A new parsing scheme now fully parses out quoted or non-quoted tokens including support for SQL-escaped quotes.

  References: #4416

• [bug] [postgresql] Fixed issue where multiple postgresql.ENUM objects referred to by the same MetaData object would fail to be created if multiple objects had the same name under different schema names. The internal memoization the PostgreSQL dialect uses to track if it has created a particular postgresql.ENUM in the database during a DDL creation sequence now takes schema name into account.

• [bug] [engine] Fixed a regression introduced in version 1.2 where a refactor of the SQLAlchemyError base exception class introduced an inappropriate coercion of a plain string message into Unicode under python 2k, which is not handled by the Python interpreter for characters outside of the platform's encoding (typically ascii). The SQLAlchemyError class now passes a bytestring through under Py2K for __str__() as is the behavior of exception objects in general under Py2K, does a safe coercion to unicode utf-8 with backslash fallback for __unicode__(). For Py3K the message is typically unicode already, but if not is again safe-coerced with utf-8 with backslash fallback for the __str__() method.

  References: #4429

• [bug] [mysql] [oracle] [sql] Fixed issue where the DDL emitted for DropTableComment, which will be used by an upcoming version of Alembic, was incorrect for the MySQL and Oracle databases.

  References: #4436

• [bug] [sqlite] Reflection of an index based on SQL expressions are now skipped with a warning, in the same way as that of the Postgresql dialect, where we currently do not support reflecting indexes that have SQL expressions within them. Previously, an index with columns of None were produced which would break tools like Alembic.

  References: #4431

1.2.15

Released: December 11, 2018

• [bug] [orm] Fixed bug where the ORM annotations could be incorrect for the primaryjoin/secondaryjoin a relationship if one used the pattern ForeignKey(SomeClass.id) in the declarative mappings. This pattern would leak undesired annotations into the join conditions which can break aliasing operations done within Query that are not supposed to impact elements in that join condition. These annotations are now removed up front if present.

  References: #4367

• [bug] [declarative] [orm] A warning is emitted in the case that a column() object is applied to a declarative class, as it seems likely this intended to be a Column object.

  References: #4374

• [bug] [orm] In continuing with a similar theme as that of very recent #4349, repaired issue with RelationshipProperty.Comparator.any() and RelationshipProperty.Comparator.has() where the "secondary" selectable needs to be explicitly part of the FROM clause in the EXISTS subquery to suit the case where this "secondary" is a Join object.

  References: #4366

• [bug] [orm] Fixed regression caused by #4349 where adding the "secondary" table to the FROM clause for a dynamic loader would affect the ability of the Query to make a subsequent join to another entity. The fix adds the primary entity as the first element of the FROM list since Query.join() wants to jump from that. Version 1.3 will have a more comprehensive solution to this problem as well (#4365).

  References: #4363

• [bug] [orm] Fixed bug where chaining of mapper options using RelationshipProperty.of_type() in conjunction with a chained option that refers to an attribute name by string only would fail to locate the attribute.

• Added support for the write_timeout flag accepted by mysqlclient and pymysql to be passed in the URL string.

  References: #4381

• Fixed issue where reflection of a PostgreSQL domain that is expressed as an array would fail to be recognized. Pull request courtesy Jakub Synowiec.

  References: #4377, #4380

1.2.14

Released: November 10, 2018

• [bug] [orm] Fixed bug in Session.bulk_update_mappings() where alternate mapped attribute names would result in the primary key column of the UPDATE statement being included in the SET clause, as well as the WHERE clause; while usually harmless, for SQL Server this can raise an error due to the IDENTITY column. This is a continuation of the same bug that was fixed in #3849, where testing was insufficient to catch this additional flaw.

  References: #4357

• [bug] [mysql] Fixed regression caused by #4344 released in 1.2.13, where the fix for MySQL 8.0's case sensitivity problem with referenced column names when reflecting foreign key referents is worked around using the information_schema.columns view. The workaround was failing on OSX / lower_case_table_names=2 which produces non-matching casing for the information_schema.columns vs. that of SHOW CREATE TABLE, so in case-insensitive SQL modes case-insensitive matching is now used.

  References: #4361

• [bug] [orm] Fixed a minor performance issue which could in some cases add unnecessary overhead to result fetching, involving the use of ORM columns and entities that include those same columns at the same time within a query. The issue has to do with hash / eq overhead when referring to the column in different ways.

  References: #4347

1.2.13

Released: October 31, 2018

• [bug] [postgresql] Added support for the aggregate_order_by function to receive multiple ORDER BY elements, previously only a single element was accepted.

  References: #4337

• [bug] [mysql] Added word function to the list of reserved words for MySQL, which is now a keyword in MySQL 8.0

  References: #4348

• [feature] [sql] Refactored SQLCompiler to expose a SQLCompiler.group_by_clause() method similar to the SQLCompiler.order_by_clause() and SQLCompiler.limit_clause() methods, which can be overridden by dialects to customize how GROUP BY renders. Pull request courtesy Samuel Chou.

• [bug] [misc] Fixed issue where part of the utility language helper internals was passing the wrong kind of argument to the Python __import__ builtin as the list of modules to be imported. The issue produced no symptoms within the core library but could cause issues with external applications that redefine the __import__ builtin or otherwise instrument it. Pull request courtesy Joe Urciuoli.

• [bug] [orm] Fixed bug where "dynamic" loader needs to explicitly set the "secondary" table in the FROM clause of the query, to suit the case where the secondary is a join object that is otherwise not pulled into the query from its columns alone.

  References: #4349

• [bug] [declarative] [orm] Fixed regression caused by #4326 in version 1.2.12 where using declared_attr with a mixin in conjunction with orm.synonym() would fail to map the synonym properly to an inherited subclass.

  References: #4350

• [bug] [misc] [py3k] Fixed additional warnings generated by Python 3.7 due to changes in the organization of the Python collections and collections.abc packages. Previous collections warnings were fixed in version 1.2.11. Pull request courtesy xtreak.

  References: #4339

• [bug] [ext] Added missing .index() method to list-based association collections in the association proxy extension.

• [bug] [mysql] Added a workaround for a MySQL bug #88718 introduced in the 8.0 series, where the reflection of a foreign key constraint is not reporting the correct case sensitivity for the referred column, leading to errors during use of the reflected constraint such as when using the automap extension. The workaround emits an additional query to the information_schema tables in order to retrieve the correct case sensitive name.

  References: #4344

• [bug] [sql] Fixed bug where the Enum.create_constraint flag on the Enum datatype would not be propagated to copies of the type, which affects use cases such as declarative mixins and abstract bases.

  References: #4341

• [bug] [declarative] [orm] The column conflict resolution technique discussed at declarative_column_conflicts is now functional for a Column that is also a primary key column. Previously, a check for primary key columns declared on a single-inheritance subclass would occur before the column copy were allowed to pass.

  References: #4352

1.2.12

Released: September 19, 2018

• [bug] [postgresql] Fixed bug in PostgreSQL dialect where compiler keyword arguments such as literal_binds=True were not being propagated to a DISTINCT ON expression.

  References: #4325

• [bug] [ext] Fixed issue where BakedQuery did not include the specific query class used by the Session as part of the cache key, leading to incompatibilities when using custom query classes, in particular the ShardedQuery which has some different argument signatures.

  References: #4328

• [bug] [postgresql] Fixed the postgresql.array_agg() function, which is a slightly altered version of the usual functions.array_agg() function, to also accept an incoming "type" argument without forcing an ARRAY around it, essentially the same thing that was fixed for the generic function in 1.1 in #4107.

  References: #4324

• [bug] [postgresql] Fixed bug in PostgreSQL ENUM reflection where a case-sensitive, quoted name would be reported by the query including quotes, which would not match a target column during table reflection as the quotes needed to be stripped off.

  References: #4323

• [bug] [orm] Added a check within the weakref cleanup for the InstanceState object to check for the presence of the dict builtin, in an effort to reduce error messages generated when these cleanups occur during interpreter shutdown. Pull request courtesy Romuald Brunet.

• [bug] [declarative] [orm] Fixed bug where the declarative scan for attributes would receive the expression proxy delivered by a hybrid attribute at the class level, and not the hybrid attribute itself, when receiving the descriptor via the @declared_attr callable on a subclass of an already-mapped class. This would lead to an attribute that did not report itself as a hybrid when viewed within Mapper.all_orm_descriptors.

  References: #4326

• [bug] [orm] Fixed bug where use of Lateral construct in conjunction with Query.join() as well as Query.select_entity_from() would not apply clause adaption to the right side of the join. "lateral" introduces the use case of the right side of a join being correlatable. Previously, adaptation of this clause wasn't considered. Note that in 1.2 only, a selectable introduced by Query.subquery() is still not adapted due to #4304; the selectable needs to be produced by the select() function to be the right side of the "lateral" join.

  References: #4334

• [bug] [oracle] Fixed issue for cx_Oracle 7.0 where the behavior of Oracle param.getvalue() now returns a list, rather than a single scalar value, breaking autoincrement logic throughout the Core and ORM. The dml_ret_array_val compatibility flag is used for cx_Oracle 6.3 and 6.4 to establish compatible behavior with 7.0 and forward, for cx_Oracle 6.2.1 and prior a version number check falls back to the old logic.

  References: #4335

• [bug] [orm] Fixed 1.2 regression caused by #3472 where the handling of an "updated_at" style column within the context of a post-update operation would also occur for a row that is to be deleted following the update, meaning both that a column with a Python-side value generator would show the now-deleted value that was emitted for the UPDATE before the DELETE (which was not the previous behavior), as well as that a SQL- emitted value generator would have the attribute expired, meaning the previous value would be unreachable due to the row having been deleted and the object detached from the session.The "postfetch" logic that was added as part of #3472 is now skipped entirely for an object that ultimately is to be deleted.

  References: #4327

1.2.11

Released: August 20, 2018

• [bug] [py3k] Started importing "collections" from "collections.abc" under Python 3.3 and greater for Python 3.8 compatibility. Pull request courtesy Nathaniel Knight.

• Fixed issue where the "schema" name used for a SQLite database within table reflection would not quote the schema name correctly. Pull request courtesy Phillip Cloud.

• [bug] [sql] Fixed issue that is closely related to #3639 where an expression rendered in a boolean context on a non-native boolean backend would be compared to 1/0 even though it is already an implicitly boolean expression, when ColumnElement.self_group() were used. While this does not affect the user-friendly backends (MySQL, SQLite) it was not handled by Oracle (and possibly SQL Server). Whether or not the expression is implicitly boolean on any database is now determined up front as an additional check to not generate the integer comparison within the compilation of the statement.

  References: #4320

• [bug] [oracle] For cx_Oracle, Integer datatypes will now be bound to "int", per advice from the cx_Oracle developers. Previously, using cx_Oracle.NUMBER caused a loss in precision within the cx_Oracle 6.x series.

  References: #4309

• [bug] [declarative] [orm] Fixed issue in previously untested use case, allowing a declarative mapped class to inherit from a classically-mapped class outside of the declarative base, including that it accommodates for unmapped intermediate classes. An unmapped intermediate class may specify __abstract__, which is now interpreted correctly, or the intermediate class can remain unmarked, and the classically mapped base class will be detected within the hierarchy regardless. In order to anticipate existing scenarios which may be mixing in classical mappings into existing declarative hierarchies, an error is now raised if multiple mapped bases are detected for a given class.

  References: #4321

• [bug] [sql] Added missing window function parameters WithinGroup.over.range_ and WithinGroup.over.rows parameters to the WithinGroup.over() and FunctionFilter.over() methods, to correspond to the range/rows feature added to the "over" method of SQL functions as part of #3049 in version 1.1.

  References: #4322

• [bug] [sql] Fixed bug where the multi-table support for UPDATE and DELETE statements did not consider the additional FROM elements as targets for correlation, when a correlated SELECT were also combined with the statement. This change now includes that a SELECT statement in the WHERE clause for such a statement will try to auto-correlate back to these additional tables in the parent UPDATE/DELETE or unconditionally correlate if Select.correlate() is used. Note that auto-correlation raises an error if the SELECT statement would have no FROM clauses as a result, which can now occur if the parent UPDATE/DELETE specifies the same tables in its additional set of tables; specify Select.correlate() explicitly to resolve.

  References: #4313

1.2.10

Released: July 13, 2018

• [bug] [sql] Fixed bug where a Sequence would be dropped explicitly before any Table that refers to it, which breaks in the case when the sequence is also involved in a server-side default for that table, when using MetaData.drop_all(). The step which processes sequences to be dropped via non server-side column default functions is now invoked after the table itself is dropped.

  References: #4300

• [bug] [orm] Fixed bug in Bundle construct where placing two columns of the same name would be de-duplicated, when the Bundle were used as part of the rendered SQL, such as in the ORDER BY or GROUP BY of the statement.

  References: #4295

• [bug] [orm] Fixed regression in 1.2.9 due to #4287 where using a Load option in conjunction with a string wildcard would result in a TypeError.

  References: #4298

1.2.9

Released: June 29, 2018

• [bug] [mysql] Fixed percent-sign doubling in mysql-connector-python dialect, which does not require de-doubling of percent signs. Additionally, the mysql- connector-python driver is inconsistent in how it passes the column names in cursor.description, so a workaround decoder has been added to conditionally decode these randomly-sometimes-bytes values to unicode only if needed. Also improved test support for mysql-connector-python, however it should be noted that this driver still has issues with unicode that continue to be unresolved as of yet.

• [bug] [mssql] Fixed bug in MSSQL reflection where when two same-named tables in different schemas had same-named primary key constraints, foreign key constraints referring to one of the tables would have their columns doubled, causing errors. Pull request courtesy Sean Dunn.

  References: #4288

• [bug] [sql] Fixed regression in 1.2 due to #4147 where a Table that has had some of its indexed columns redefined with new ones, as would occur when overriding columns during reflection or when using Table.extend_existing, such that the Table.tometadata() method would fail when attempting to copy those indexes as they still referred to the replaced column. The copy logic now accommodates for this condition.

  References: #4279

• [bug] [mysql] Fixed bug in index reflection where on MySQL 8.0 an index that includes ASC or DESC in an indexed column specification would not be correctly reflected, as MySQL 8.0 introduces support for returning this information in a table definition string.

  References: #4293

• [bug] [orm] Fixed issue where chaining multiple join elements inside of Query.join() might not correctly adapt to the previous left-hand side, when chaining joined inheritance classes that share the same base class.

  References: #3505

• [bug] [orm] Fixed bug in cache key generation for baked queries which could cause a too-short cache key to be generated for the case of eager loads across subclasses. This could in turn cause the eagerload query to be cached in place of a non-eagerload query, or vice versa, for a polymorhic "selectin" load, or possibly for lazy loads or selectin loads as well.

  References: #4287

• [bug] [sqlite] Fixed issue in test suite where SQLite 3.24 added a new reserved word that conflicted with a usage in TypeReflectionTest. Pull request courtesy Nils Philippsen.

• [feature] [oracle] Added a new event currently used only by the cx_Oracle dialect, DialectEvents.setiputsizes(). The event passes a dictionary of BindParameter objects to DBAPI-specific type objects that will be passed, after conversion to parameter names, to the cx_Oracle cursor.setinputsizes() method. This allows both visibility into the setinputsizes process as well as the ability to alter the behavior of what datatypes are passed to this method.

  References: #4290

• [bug] [orm] Fixed bug in new polymorphic selectin loading where the BakedQuery used internally would be mutated by the given loader options, which would both inappropriately mutate the subclass query as well as carry over the effect to subsequent queries.

  References: #4286

• [bug] [py3k] Replaced the usage of inspect.formatargspec() with a vendored version copied from the Python standard library, as inspect.formatargspec() is deprecated and as of Python 3.7.0 is emitting a warning.

  References: #4291

• [ext] [feature] Added new attribute Query.lazy_loaded_from which is populated with an InstanceState that is using this Query in order to lazy load a relationship. The rationale for this is that it serves as a hint for the horizontal sharding feature to use, such that the identity token of the state can be used as the default identity token to use for the query within id_chooser().

  References: #4243

• [bug] [mysql] Fixed bug in MySQLdb dialect and variants such as PyMySQL where an additional "unicode returns" check upon connection makes explicit use of the "utf8" character set, which in MySQL 8.0 emits a warning that utf8mb4 should be used. This is now replaced with a utf8mb4 equivalent. Documentation is also updated for the MySQL dialect to specify utf8mb4 in all examples. Additional changes have been made to the test suite to use utf8mb3 charsets and databases (there seem to be collation issues in some edge cases with utf8mb4), and to support configuration default changes made in MySQL 8.0 such as explicit_defaults_for_timestamp as well as new errors raised for invalid MyISAM indexes.

  References: #4283

• [bug] [mysql] The Update construct now accommodates a Join object as supported by MySQL for UPDATE..FROM. As the construct already accepted an alias object for a similar purpose, the feature of UPDATE against a non-table was already implied so this has been added.

  References: #3645

• [bug] [mssql] [py3k] Fixed issue within the SQL Server dialect under Python 3 where when running against a non-standard SQL server database that does not contain either the "sys.dm_exec_sessions" or "sys.dm_pdw_nodes_exec_sessions" views, leading to a failure to fetch the isolation level, the error raise would fail due to an UnboundLocalError.

  References: #4273

• [bug] [orm] Fixed regression caused by #4256 (itself a regression fix for #4228) which breaks an undocumented behavior which converted for a non-sequence of entities passed directly to the Query constructor into a single-element sequence. While this behavior was never supported or documented, it's already in use so has been added as a behavioral contract to Query.

  References: #4269

• [bug] [orm] Fixed an issue that was both a performance regression in 1.2 as well as an incorrect result regarding the "baked" lazy loader, involving the generation of cache keys from the original Query object's loader options. If the loader options were built up in a "branched" style using common base elements for multiple options, the same options would be rendered into the cache key repeatedly, causing both a performance issue as well as generating the wrong cache key. This is fixed, along with a performance improvement when such "branched" options are applied via Query.options() to prevent the same option objects from being applied repeatedly.

  References: #4270

• [bug] [mysql] [oracle] Fixed INSERT FROM SELECT with CTEs for the Oracle and MySQL dialects, where the CTE was being placed above the entire statement as is typical with other databases, however Oracle and MariaDB 10.2 wants the CTE underneath the "INSERT" segment. Note that the Oracle and MySQL dialects don't yet work when a CTE is applied to a subquery inside of an UPDATE or DELETE statement, as the CTE is still applied to the top rather than inside the subquery.

  References: #4275

1.2.8

Released: May 28, 2018

• [bug] [orm] Fixed regression in 1.2.7 caused by #4228, which itself was fixing a 1.2-level regression, where the query_cls callable passed to a Session was assumed to be a subclass of Query with class method availability, as opposed to an arbitrary callable. In particular, the dogpile caching example illustrates query_cls as a function and not a Query subclass.

  References: #4256

• [bug] [engine] Fixed connection pool issue whereby if a disconnection error were raised during the connection pool's "reset on return" sequence in conjunction with an explicit transaction opened against the enclosing Connection object (such as from calling Session.close() without a rollback or commit, or calling Connection.close() without first closing a transaction declared with Connection.begin()), a double-checkin would result, which could then lead towards concurrent checkouts of the same connection. The double-checkin condition is now prevented overall by an assertion, as well as the specific double-checkin scenario has been fixed.

  References: #4252

• [bug] [oracle] The Oracle BINARY_FLOAT and BINARY_DOUBLE datatypes now participate within cx_Oracle.setinputsizes(), passing along NATIVE_FLOAT, so as to support the NaN value. Additionally, oracle.BINARY_FLOAT, oracle.BINARY_DOUBLE and oracle.DOUBLE_PRECISION now subclass Float, since these are floating point datatypes, not decimal. These datatypes were already defaulting the Float.asdecimal flag to False in line with what Float already does.

  References: #4264

• [bug] [oracle] Added reflection capabilities for the oracle.BINARY_FLOAT, oracle.BINARY_DOUBLE datatypes.

• [bug] [ext] The horizontal sharding extension now makes use of the identity token added to ORM identity keys as part of #4137, when an object refresh or column-based deferred load or unexpiration operation occurs. Since we know the "shard" that the object originated from, we make use of this value when refreshing, thereby avoiding queries against other shards that don't match this object's identity in any case.

  References: #4247

• [bug] [sql] Fixed issue where the "ambiguous literal" error message used when interpreting literal values as SQL expression values would encounter a tuple value, and fail to format the message properly. Pull request courtesy Miguel Ventura.

• [bug] [mssql] Fixed a 1.2 regression caused by #4061 where the SQL Server "BIT" type would be considered to be "native boolean". The goal here was to avoid creating a CHECK constraint on the column, however the bigger issue is that the BIT value does not behave like a true/false constant and cannot be interpreted as a standalone expression, e.g. "WHERE <column>". The SQL Server dialect now goes back to being non-native boolean, but with an extra flag that still avoids creating the CHECK constraint.

  References: #4250

• [bug] [oracle] Altered the Oracle dialect such that when an Integer type is in use, the cx_Oracle.NUMERIC type is set up for setinputsizes(). In SQLAlchemy 1.1 and earlier, cx_Oracle.NUMERIC was passed for all numeric types unconditionally, and in 1.2 this was removed to allow for better numeric precision. However, for integers, some database/client setups will fail to coerce boolean values True/False into integers which introduces regressive behavior when using SQLAlchemy 1.2. Overall, the setinputsizes logic seems like it will need a lot more flexibility going forward so this is a start for that.

  References: #4259

• [bug] [engine] Fixed a reference leak issue where the values of the parameter dictionary used in a statement execution would remain referenced by the "compiled cache", as a result of storing the key view used by Python 3 dictionary keys(). Pull request courtesy Olivier Grisel.

• [bug] [orm] Fixed a long-standing regression that occurred in version 1.0, which prevented the use of a custom MapperOption that alters the _params of a Query object for a lazy load, since the lazy loader itself would overwrite those parameters. This applies to the "temporal range" example on the wiki. Note however that the Query.populate_existing() method is now required in order to rewrite the mapper options associated with an object already loaded in the identity map.

  As part of this change, a custom defined MapperOption will now cause lazy loaders related to the target object to use a non-baked query by default unless the MapperOption._generate_cache_key() method is implemented. In particular, this repairs one regression which occurred when using the dogpile.cache "advanced" example, which was not returning cached results and instead emitting SQL due to an incompatibility with the baked query loader; with the change, the RelationshipCache option included for many releases in the dogpile example will disable the "baked" query altogether. Note that the dogpile example is also modernized to avoid both of these issues as part of issue #4258.

  References: #4128

• [bug] [ext] Fixed a race condition which could occur if automap AutomapBase.prepare() were used within a multi-threaded context against other threads which may call configure_mappers() as a result of use of other mappers. The unfinished mapping work of automap is particularly sensitive to being pulled in by a configure_mappers() step leading to errors.

  References: #4266

• [bug] [orm] Fixed bug where the new baked.Result.with_post_criteria() method would not interact with a subquery-eager loader correctly, in that the "post criteria" would not be applied to embedded subquery eager loaders. This is related to #4128 in that the post criteria feature is now used by the lazy loader.

• [bug] [tests] Fixed a bug in the test suite where if an external dialect returned None for server_version_info, the exclusion logic would raise an AttributeError.

  References: #4249

• [bug] [orm] Updated the dogpile.caching example to include new structures that accommodate for the "baked" query system, which is used by default within lazy loaders and some eager relationship loaders. The dogpile.caching "relationship_caching" and "advanced" examples were also broken due to #4256. The issue here is also worked-around by the fix in #4128.

  References: #4258

1.2.7

Released: April 20, 2018

• [bug] [orm] Fixed regression in 1.2 within sharded query feature where the new "identity_token" element was not being correctly considered within the scope of a lazy load operation, when searching the identity map for a related many-to-one element. The new behavior will allow for making use of the "id_chooser" in order to determine the best identity key to retrieve from the identity map. In order to achieve this, some refactoring of 1.2's "identity_token" approach has made some slight changes to the implementation of ShardedQuery which should be noted for other derivations of this class.

  References: #4228

• [bug] [postgresql] Fixed bug where the special "not equals" operator for the PostgreSQL "range" datatypes such as DATERANGE would fail to render "IS NOT NULL" when compared to the Python None value.

  References: #4229

• [bug] [mssql] Fixed 1.2 regression caused by #4060 where the query used to reflect SQL Server cross-schema foreign keys was limiting the criteria incorrectly.

  References: #4234

• [bug] [oracle] The Oracle NUMBER datatype is reflected as INTEGER if the precision is NULL and the scale is zero, as this is how INTEGER values come back when reflected from Oracle's tables. Pull request courtesy Kent Bower.

• [feature] [postgresql] Added new PG type postgresql.REGCLASS which assists in casting table names to OID values. Pull request courtesy Sebastian Bank.

  References: #4160

• [bug] [sql] Fixed issue where the compilation of an INSERT statement with the "literal_binds" option that also uses an explicit sequence and "inline" generation, as on PostgreSQL and Oracle, would fail to accommodate the extra keyword argument within the sequence processing routine.

  References: #4231

• [bug] [orm] Fixed issue in single-inheritance loading where the use of an aliased entity against a single-inheritance subclass in conjunction with the Query.select_from() method would cause the SQL to be rendered with the unaliased table mixed in to the query, causing a cartesian product. In particular this was affecting the new "selectin" loader when used against a single-inheritance subclass.

  References: #4241

1.2.6

Released: March 30, 2018

• [bug] [mssql] Adjusted the SQL Server version detection for pyodbc to only allow for numeric tokens, filtering out non-integers, since the dialect does tuple- numeric comparisons with this value. This is normally true for all known SQL Server / pyodbc drivers in any case.

  References: #4227

• [feature] [postgresql] Added support for "PARTITION BY" in PostgreSQL table definitions, using "postgresql_partition_by". Pull request courtesy Vsevolod Solovyov.

• [bug] [sql] Fixed a regression that occurred from the previous fix to #4204 in version 1.2.5, where a CTE that refers to itself after the CTE.alias() method has been called would not refer to itself correctly.

  References: #4204

• [bug] [engine] Fixed bug in connection pool where a connection could be present in the pool without all of its "connect" event handlers called, if a previous "connect" handler threw an exception; note that the dialects themselves have connect handlers that emit SQL, such as those which set transaction isolation, which can fail if the database is in a non-available state, but still allows a connection. The connection is now invalidated first if any of the connect handlers fail.

  References: #4225

• [bug] [oracle] The minimum cx_Oracle version supported is 5.2 (June 2015). Previously, the dialect asserted against version 5.0 but as of 1.2.2 we are using some symbols that did not appear until 5.2.

  References: #4211

• [bug] [declarative] Removed a warning that would be emitted when calling upon __table_args__, __mapper_args__ as named with a @declared_attr method, when called from a non-mapped declarative mixin. Calling these directly is documented as the approach to use when one is overriding one of these methods on a mapped class. The warning still emits for regular attribute names.

  References: #4221

• [bug] [orm] Fixed bug where using Mutable.associate_with() or Mutable.as_mutable() in conjunction with a class that has non- primary mappers set up with alternatively-named attributes would produce an attribute error. Since non-primary mappers are not used for persistence, the mutable extension now excludes non-primary mappers from its instrumentation steps.

  References: #4215

1.2.5

Released: March 6, 2018

• [bug] [mysql] MySQL dialects now query the server version using SELECT @@version explicitly to the server to ensure we are getting the correct version information back. Proxy servers like MaxScale interfere with the value that is passed to the DBAPI's connection.server_version value so this is no longer reliable.

  This change is also backported to: 1.1.18

  References: #4205

• [bug] [postgresql] [py3k] Fixed bug in PostgreSQL COLLATE / ARRAY adjustment first introduced in #4006 where new behaviors in Python 3.7 regular expressions caused the fix to fail.

  This change is also backported to: 1.1.18

  References: #4208

• [bug] [sql] Fixed bug in :class:.CTE construct along the same lines as that of #4204 where a CTE that was aliased would not copy itself correctly during a "clone" operation as is frequent within the ORM as well as when using the ClauseElement.params() method.

  References: #4210

• [bug] [orm] Fixed bug in new "polymorphic selectin" loading when a selection of polymorphic objects were to be partially loaded from a relationship lazy loader, leading to an "empty IN" condition within the load that raises an error for the "inline" form of "IN".

  References: #4199

• [bug] [sql] Fixed bug in CTE rendering where a CTE that was also turned into an Alias would not render its "ctename AS aliasname" clause appropriately if there were more than one reference to the CTE in a FROM clause.

  References: #4204

• [bug] [orm] Fixed 1.2 regression where a mapper option that contains an AliasedClass object, as is typical when using the QueryableAttribute.of_type() method, could not be pickled. 1.1's behavior was to omit the aliased class objects from the path, so this behavior is restored.

  References: #4209

• [feature] [orm] Added new feature Query.only_return_tuples(). Causes the Query object to return keyed tuple objects unconditionally even if the query is against a single entity. Pull request courtesy Eric Atkin.

• [bug] [sql] Fixed bug in new "expanding IN parameter" feature where the bind parameter processors for values wasn't working at all, tests failed to cover this pretty basic case which includes that ENUM values weren't working.

  References: #4198

1.2.4

Released: February 22, 2018

• [bug] [orm] Fixed 1.2 regression in ORM versioning feature where a mapping against a select() or alias() that also used a versioning column against the underlying table would fail due to the check added as part of #3673.

  References: #4193

• [bug] [engine] Fixed regression caused in 1.2.3 due to fix from #4181 where the changes to the event system involving Engine and OptionEngine did not accommodate for event removals, which would raise an AttributeError when invoked at the class level.

  References: #4190

• [bug] [sql] Fixed bug where CTE expressions would not have their name or alias name quoted when the given name is case sensitive or otherwise requires quoting. Pull request courtesy Eric Atkin.

  References: #4197

1.2.3

Released: February 16, 2018

• [bug] [postgresql] Added "SSL SYSCALL error: Operation timed out" to the list of messages that trigger a "disconnect" scenario for the psycopg2 driver. Pull request courtesy André Cruz.

  This change is also backported to: 1.1.16

• [bug] [orm] Fixed issue in post_update feature where an UPDATE is emitted when the parent object has been deleted but the dependent object is not. This issue has existed for a long time however since 1.2 now asserts rows matched for post_update, this was raising an error.

  This change is also backported to: 1.1.16

  References: #4187

• [bug] [orm] Fixed regression caused by fix for issue #4116 affecting versions 1.2.2 as well as 1.1.15, which had the effect of mis-calculation of the "owning class" of an AssociationProxy as the NoneType class in some declarative mixin/inheritance situations as well as if the association proxy were accessed off of an un-mapped class. The "figure out the owner" logic has been replaced by an in-depth routine that searches through the complete mapper hierarchy assigned to the class or subclass to determine the correct (we hope) match; will not assign the owner if no match is found. An exception is now raised if the proxy is used against an un-mapped instance.

  This change is also backported to: 1.1.16

  References: #4185

• [bug] [postgresql] Added "TRUNCATE" to the list of keywords accepted by the PostgreSQL dialect as an "autocommit"-triggering keyword. Pull request courtesy Jacob Hayes.

  This change is also backported to: 1.1.16

• [bug] [pool] Fixed a fairly serious connection pool bug where a connection that is acquired after being refreshed as a result of a user-defined DisconnectionError or due to the 1.2-released "pre_ping" feature would not be correctly reset if the connection were returned to the pool by weakref cleanup (e.g. the front-facing object is garbage collected); the weakref would still refer to the previously invalidated DBAPI connection which would have the reset operation erroneously called upon it instead. This would lead to stack traces in the logs and a connection being checked into the pool without being reset, which can cause locking issues.

  This change is also backported to: 1.1.16

  References: #4184

• [bug] [oracle] Fixed bug in cx_Oracle disconnect detection, used by pre_ping and other features, where an error could be raised as DatabaseError which includes a numeric error code; previously we weren't checking in this case for a disconnect code.

  References: #4182

• [bug] [sqlite] Fixed the import error raised when a platform has neither pysqlite2 nor sqlite3 installed, such that the sqlite3-related import error is raised, not the pysqlite2 one which is not the actual failure mode. Pull request courtesy Robin.

• [bug] [orm] Fixed bug where the Bundle object did not correctly report upon the primary Mapper object represented by the bundle, if any. An immediate side effect of this issue was that the new selectinload loader strategy wouldn't work with the horizontal sharding extension.

  References: #4175

• [bug] [sql] Fixed bug where the Enum type wouldn't handle enum "aliases" correctly, when more than one key refers to the same value. Pull request courtesy Daniel Knell.

  References: #4180

• [bug] [engine] Fixed bug where events associated with an Engine at the class level would be doubled when the Engine.execution_options() method were used. To achieve this, the semi-private class OptionEngine no longer accepts events directly at the class level and will raise an error; the class only propagates class-level events from its parent Engine. Instance-level events continue to work as before.

  References: #4181

• [bug] [tests] A test added in 1.2 thought to confirm a Python 2.7 behavior turns out to be confirming the behavior only as of Python 2.7.8. Python bug #8743 still impacts set comparison in Python 2.7.7 and earlier, so the test in question involving AssociationSet no longer runs for these older Python 2.7 versions.

  References: #3265

• [feature] [oracle] The ON DELETE options for foreign keys are now part of Oracle reflection. Oracle does not support ON UPDATE cascades. Pull request courtesy Miroslav Shubernetskiy.

• [bug] [orm] Fixed bug in concrete inheritance mapping where user-defined attributes such as hybrid properties that mirror the names of mapped attributes from sibling classes would be overwritten by the mapper as non-accessible at the instance level. Additionally ensured that user-bound descriptors are not implicitly invoked at the class level during the mapper configuration stage.

  References: #4188

• [bug] [orm] Fixed bug where the orm.reconstructor() event helper would not be recognized if it were applied to the __init__() method of the mapped class.

  References: #4178

• [bug] [engine] The URL object now allows query keys to be specified multiple times where their values will be joined into a list. This is to support the plugins feature documented at CreateEnginePlugin which documents that "plugin" can be passed multiple times. Additionally, the plugin names can be passed to create_engine() outside of the URL using the new create_engine.plugins parameter.

  References: #4170

• [feature] [sql] Added support for Enum to persist the values of the enumeration, rather than the keys, when using a Python pep-435 style enumerated object. The user supplies a callable function that will return the string values to be persisted. This allows enumerations against non-string values to be value-persistable as well. Pull request courtesy Jon Snyder.

  References: #3906

• [feature] [orm] Added new argument attributes.set_attribute.inititator to the attributes.set_attribute() function, allowing an event token received from a listener function to be propagated to subsequent set events.

1.2.2

Released: January 24, 2018

• [bug] [mssql] Added ODBC error code 10054 to the list of error codes that count as a disconnect for ODBC / MSSQL server.

  References: #4164

• [bug] [orm] Fixed 1.2 regression regarding new bulk_replace event where a backref would fail to remove an object from the previous owner when a bulk-assignment assigned the object to a new owner.

  References: #4171

• [bug] [oracle] The cx_Oracle dialect now calls setinputsizes() with cx_Oracle.NCHAR unconditionally when the NVARCHAR2 datatype, in SQLAlchemy corresponding to sqltypes.Unicode(), is in use. Per cx_Oracle's author this allows the correct conversions to occur within the Oracle client regardless of the setting for NLS_NCHAR_CHARACTERSET.

  References: #4163

• [bug] [mysql] Added more MySQL 8.0 reserved words to the MySQL dialect for quoting purposes. Pull request courtesy Riccardo Magliocchetti.

1.2.1

Released: January 15, 2018

• [bug] [sql] Fixed bug in Insert.values() where using the "multi-values" format in combination with Column objects as keys rather than strings would fail. Pull request courtesy Aubrey Stark-Toller.

  This change is also backported to: 1.1.16

  References: #4162

• [bug] [orm] Fixed bug where an object that is expunged during a rollback of a nested or subtransaction which also had its primary key mutated would not be correctly removed from the session, causing subsequent issues in using the session.

  This change is also backported to: 1.1.16

  References: #4151

• [bug] [orm] Fixed regression where pickle format of a Load / _UnboundLoad object (e.g. loader options) changed and __setstate__() was raising an UnboundLocalError for an object received from the legacy format, even though an attempt was made to do so. tests are now added to ensure this works.

  References: #4159

• [bug] [ext] Fixed regression in association proxy due to #3769 (allow for chained any() / has()) where contains() against an association proxy chained in the form (o2m relationship, associationproxy(m2o relationship, m2o relationship)) would raise an error regarding the re-application of contains() on the final link of the chain.

  References: #4150

• [bug] [orm] Fixed regression caused by new lazyload caching scheme in #3954 where a query that makes use of loader options with of_type would cause lazy loads of unrelated paths to fail with a TypeError.

  References: #4153

• [bug] [oracle] Fixed regression where the removal of most setinputsizes rules from cx_Oracle dialect impacted the TIMESTAMP datatype's ability to retrieve fractional seconds.

  References: #4157

• [bug] [tests] Removed an oracle-specific requirements rule from the public test suite that was interfering with third party dialect suites.

• [bug] [mssql] Fixed regression in 1.2 where newly repaired quoting of collation names in #3785 breaks SQL Server, which explicitly does not understand a quoted collation name. Whether or not mixed-case collation names are quoted or not is now deferred down to a dialect-level decision so that each dialect can prepare these identifiers directly.

  References: #4154

• [bug] [orm] Fixed bug in new "selectin" relationship loader where the loader could try to load a non-existent relationship when loading a collection of polymorphic objects, where only some of the mappers include that relationship, typically when PropComparator.of_type() is being used.

  References: #4156

• [bug] [tests] Added a new exclusion rule group_by_complex_expression which disables tests that use "GROUP BY <expr>", which seems to be not viable for at least two third party dialects.

• [bug] [oracle] Fixed regression in Oracle imports where a missing comma caused an undefined symbol to be present. Pull request courtesy Miroslav Shubernetskiy.

1.2.0

Released: December 27, 2017

• [bug] [sql] Fixed bug where __repr__ of ColumnDefault would fail if the argument were a tuple. Pull request courtesy Nicolas Caniart.

  This change is also backported to: 1.1.15

  References: #4126

• [bug] [declarative] [orm] Fixed bug where a descriptor that is elsewhere a mapped column or relationship within a hierarchy based on AbstractConcreteBase would be referred towards during a refresh operation, causing an error as the attribute is not mapped as a mapper property. A similar issue can arise for other attributes like the "type" column added by AbstractConcreteBase if the class fails to include "concrete=True" in its mapper, however the check here should also prevent that scenario from causing a problem.

  This change is also backported to: 1.1.15

  References: #4124

• [bug] [ext] [orm] Fixed bug where the association proxy would inadvertently link itself to an AliasedClass object if it were called first with the AliasedClass as a parent, causing errors upon subsequent usage.

  This change is also backported to: 1.1.15

  References: #4116

• [bug] [mysql] MySQL 5.7.20 now warns for use of the @tx_isolation variable; a version check is now performed and uses @transaction_isolation instead to prevent this warning.

  This change is also backported to: 1.1.15

  References: #4120

• [feature] [orm] Added a new data member to the identity key tuple used by the ORM's identity map, known as the "identity_token". This token defaults to None but may be used by database sharding schemes to differentiate objects in memory with the same primary key that come from different databases. The horizontal sharding extension integrates this token applying the shard identifier to it, thus allowing primary keys to be duplicated across horizontally sharded backends.

  References: #4137

• [bug] [mysql] Fixed regression from issue 1.2.0b3 where "MariaDB" version comparison can fail for some particular MariaDB version strings under Python 3.

  References: #4115

• [enhancement] [sql] Implemented "DELETE..FROM" syntax for PostgreSQL, MySQL, MS SQL Server (as well as within the unsupported Sybase dialect) in a manner similar to how "UPDATE..FROM" works. A DELETE statement that refers to more than one table will switch into "multi-table" mode and render the appropriate "USING" or multi-table "FROM" clause as understood by the database. Pull request courtesy Pieter Mulder.

  References: #959

• [bug] [sql] Reworked the new "autoescape" feature introduced in change_2694 in 1.2.0b2 to be fully automatic; the escape character now defaults to a forwards slash "/" and is applied to percent, underscore, as well as the escape character itself, for fully automatic escaping. The character can also be changed using the "escape" parameter.

  References: #2694

• [bug] [sql] Fixed bug where the Table.tometadata() method would not properly accommodate Index objects that didn't consist of simple column expressions, such as indexes against a text() construct, indexes that used SQL expressions or func, etc. The routine now copies expressions fully to a new Index object while substituting all table-bound Column objects for those of the target table.

  References: #4147

• [bug] [sql] Changed the "visit name" of ColumnElement from "column" to "column_element", so that when this element is used as the basis for a user-defined SQL element, it is not assumed to behave like a table-bound ColumnClause when processed by various SQL traversal utilities, as are commonly used by the ORM.

  References: #4142

• [bug] [ext] [sql] Fixed issue in ARRAY datatype which is essentially the same issue as that of #3832, except not a regression, where column attachment events on top of ARRAY would not fire correctly, thus interfering with systems which rely upon this. A key use case that was broken by this is the use of mixins to declare columns that make use of MutableList.as_mutable().

  References: #4141

• [engine] [feature] The "password" attribute of the url.URL object can now be any user-defined or user-subclassed string object that responds to the Python str() builtin. The object passed will be maintained as the datamember url.URL.password_original and will be consulted when the url.URL.password attribute is read to produce the string value.

  References: #4089

• [bug] [orm] Fixed bug in contains_eager() query option where making use of a path that used PropComparator.of_type() to refer to a subclass across more than one level of joins would also require that the "alias" argument were provided with the same subtype in order to avoid adding unwanted FROM clauses to the query; additionally, using contains_eager() across subclasses that use aliased() objects of subclasses as the PropComparator.of_type() argument will also render correctly.

  References: #4130

• [feature] [postgresql] Added new postgresql.MONEY datatype. Pull request courtesy Cleber J Santos.

• [bug] [sql] Fixed bug in new "expanding bind parameter" feature whereby if multiple params were used in one statement, the regular expression would not match the parameter name correctly.

  References: #4140

• [enhancement] [ext] Added new method baked.Result.with_post_criteria() to baked query system, allowing non-SQL-modifying transformations to take place after the query has been pulled from the cache. Among other things, this method can be used with horizontal_shard.ShardedQuery to set the shard identifier. horizontal_shard.ShardedQuery has also been modified such that its ShardedQuery.get() method interacts correctly with that of baked.Result.

  References: #4135

• [bug] [oracle] Added some additional rules to fully handle Decimal('Infinity'), Decimal('-Infinity') values with cx_Oracle numerics when using asdecimal=True.

  References: #4064

• [bug] [mssql] Fixed bug where sqltypes.BINARY and sqltypes.VARBINARY datatypes would not include correct bound-value handlers for pyodbc, which allows the pyodbc.NullParam value to be passed that helps with FreeTDS.

  References: #4121

• [feature] [misc] Added a new errors section to the documentation with background about common error messages. Selected exceptions within SQLAlchemy will include a link in their string output to the relevant section within this page.

• [bug] [orm] The Query.exists() method will now disable eager loaders for when the query is rendered. Previously, joined-eager load joins would be rendered unnecessarily as well as subquery eager load queries would be needlessly generated. The new behavior matches that of the Query.subquery() method.

  References: #4032

1.2.0b2

Released: July 24, 2017

• [bug] [py3k] [tests] Fixed issue in testing fixtures which was incompatible with a change made as of Python 3.6.2 involving context managers.

  This change is also backported to: 1.1.12, 1.0.18

  References: #4034

• [bug] [orm] Fixed regression from 1.1.11 where adding additional non-entity columns to a query that includes an entity with subqueryload relationships would fail, due to an inspection added in 1.1.11 as a result of #4011.

  This change is also backported to: 1.1.12

  References: #4033

• [bug] [orm] Fixed bug involving JSON NULL evaluation logic added in 1.1 as part of #3514 where the logic would not accommodate ORM mapped attributes named differently from the Column that was mapped.

  This change is also backported to: 1.1.12

  References: #4031

• [bug] [orm] Added KeyError checks to all methods within WeakInstanceDict where a check for key in dict is followed by indexed access to that key, to guard against a race against garbage collection that under load can remove the key from the dict after the code assumes its present, leading to very infrequent KeyError raises.

  This change is also backported to: 1.1.12

  References: #4030

1.2.0b1

Released: July 10, 2017

• [feature] [oracle] [postgresql] Added new keywords Sequence.cache and Sequence.order to Sequence, to allow rendering of the CACHE parameter understood by Oracle and PostgreSQL, and the ORDER parameter understood by Oracle. Pull request courtesy David Moore.

  This change is also backported to: 1.1.12

• [bug] [sql] Fixed AttributeError which would occur in WithinGroup construct during an iteration of the structure.

  This change is also backported to: 1.1.11

  References: #4012

• [bug] [orm] Fixed issue with subquery eagerloading which continues on from the series of issues fixed in #2699, #3106, #3893 involving that the "subquery" contains the correct FROM clause when beginning from a joined inheritance subclass and then subquery eager loading onto a relationship from the base class, while the query also includes criteria against the subclass. The fix in the previous tickets did not accommodate for additional subqueryload operations loading more deeply from the first level, so the fix has been further generalized.

  This change is also backported to: 1.1.11

  References: #4011

• [bug] [postgresql] Continuing with the fix that correctly handles PostgreSQL version string "10devel" released in 1.1.8, an additional regexp bump to handle version strings of the form "10beta1". While PostgreSQL now offers better ways to get this information, we are sticking w/ the regexp at least through 1.1.x for the least amount of risk to compatibility w/ older or alternate PostgreSQL databases.

  This change is also backported to: 1.1.11

  References: #4005

• [bug] [postgresql] Fixed bug where using ARRAY with a string type that features a collation would fail to produce the correct syntax within CREATE TABLE.

  This change is also backported to: 1.1.11

  References: #4006

• [bug] [mysql] MySQL 5.7 has introduced permission limiting for the "SHOW VARIABLES" command; the MySQL dialect will now handle when SHOW returns no row, in particular for the initial fetch of SQL_MODE, and will emit a warning that user permissions should be modified to allow the row to be present.

  This change is also backported to: 1.1.11

  References: #4007

• [bug] [mssql] Fixed bug where SQL Server transaction isolation must be fetched from a different view when using Azure data warehouse, the query is now attempted against both views and then a NotImplemented is raised unconditionally if failure continues to provide the best resiliency against future arbitrary API changes in new SQL Server versions.

  This change is also backported to: 1.1.11

  References: #3994

• [bug] [oracle] Support for two-phase transactions has been removed entirely for cx_Oracle when version 6.0b1 or later of the DBAPI is in use. The two- phase feature historically has never been usable under cx_Oracle 5.x in any case, and cx_Oracle 6.x has removed the connection-level "twophase" flag upon which this feature relied.

  This change is also backported to: 1.1.11

  References: #3997

• [bug] [mssql] Added a placeholder type mssql.XML to the SQL Server dialect, so that a reflected table which includes this type can be re-rendered as a CREATE TABLE. The type has no special round-trip behavior nor does it currently support additional qualifying arguments.

  This change is also backported to: 1.1.11

  References: #3973

• [bug] [orm] Fixed bug where a cascade such as "delete-orphan" (but others as well) would fail to locate an object linked to a relationship that itself is local to a subclass in an inheritance relationship, thus causing the operation to not take place.

  This change is also backported to: 1.1.10

  References: #3986

• [bug] [oracle] Fixed bug in cx_Oracle dialect where version string parsing would fail for cx_Oracle version 6.0b1 due to the "b" character. Version string parsing is now via a regexp rather than a simple split.

  This change is also backported to: 1.1.10

  References: #3975

• [bug] [schema] An ArgumentError is now raised if a ForeignKeyConstraint object is created with a mismatched number of "local" and "remote" columns, which otherwise causes the internal state of the constraint to be incorrect. Note that this also impacts the condition where a dialect's reflection process produces a mismatched set of columns for a foreign key constraint.

  This change is also backported to: 1.1.10

  References: #3949

• [bug] [ext] Protected against testing "None" as a class in the case where declarative classes are being garbage collected and new automap prepare() operations are taking place concurrently, very infrequently hitting a weakref that has not been fully acted upon after gc.

  This change is also backported to: 1.1.10

  References: #3980

• [bug] [postgresql] Added "autocommit" support for GRANT, REVOKE keywords. Pull request courtesy Jacob Hayes.

  This change is also backported to: 1.1.10

• [bug] [mysql] Removed an ancient and unnecessary intercept of the UTC_TIMESTAMP MySQL function, which was getting in the way of using it with a parameter.

  This change is also backported to: 1.1.10

  References: #3966

• [bug] [mysql] Fixed bug in MySQL dialect regarding rendering of table options in conjunction with PARTITION options when rendering CREATE TABLE. The PARTITION related options need to follow the table options, whereas previously this ordering was not enforced.

  This change is also backported to: 1.1.10

  References: #3961

• [bug] [sql] Fixed regression released in 1.1.5 due to #3859 where adjustments to the "right-hand-side" evaluation of an expression based on Variant to honor the underlying type's "right-hand-side" rules caused the Variant type to be inappropriately lost, in those cases when we do want the left-hand side type to be transferred directly to the right hand side so that bind-level rules can be applied to the expression's argument.

  This change is also backported to: 1.1.9

  References: #3952

• [bug] [postgresql] [sql] Changed the mechanics of ResultProxy to unconditionally delay the "autoclose" step until the Connection is done with the object; in the case where PostgreSQL ON CONFLICT with RETURNING returns no rows, autoclose was occurring in this previously non-existent use case, causing the usual autocommit behavior that occurs unconditionally upon INSERT/UPDATE/DELETE to fail.

  This change is also backported to: 1.1.9

  References: #3955

• [bug] [ext] Fixed bug in sqlalchemy.ext.mutable where the Mutable.as_mutable() method would not track a type that had been copied using TypeEngine.copy(). This became more of a regression in 1.1 compared to 1.0 because the TypeDecorator class is now a subclass of SchemaEventTarget, which among other things indicates to the parent Column that the type should be copied when the Column is. These copies are common when using declarative with mixins or abstract classes.

  This change is also backported to: 1.1.8

  References: #3950

• [bug] [ext] Added support for bound parameters, e.g. those normally set up via Query.params(), to the baked.Result.count() method. Previously, support for parameters were omitted. Pull request courtesy Pat Deegan.

  This change is also backported to: 1.1.8

• [bug] [postgresql] Added support for parsing the PostgreSQL version string for a development version like "PostgreSQL 10devel". Pull request courtesy Sean McCully.

  This change is also backported to: 1.1.8

• [feature] [orm] An aliased() construct can now be passed to the Query.select_entity_from() method. Entities will be pulled from the selectable represented by the aliased() construct. This allows special options for aliased() such as aliased.adapt_on_names to be used in conjunction with Query.select_entity_from().

  This change is also backported to: 1.1.7

  References: #3933

• [bug] [engine] Added an exception handler that will warn for the "cause" exception on Py2K when the "autorollback" feature of Connection itself raises an exception. In Py3K, the two exceptions are naturally reported by the interpreter as one occurring during the handling of the other. This is continuing with the series of changes for rollback failure handling that were last visited as part of #2696 in 1.0.12.

  This change is also backported to: 1.1.7

  References: #3946

• [bug] [orm] Fixed a race condition which could occur under threaded environments as a result of the caching added via #3915. An internal collection of Column objects could be regenerated on an alias object inappropriately, confusing a joined eager loader when it attempts to render SQL and collect results and resulting in an attribute error. The collection is now generated up front before the alias object is cached and shared among threads.

  This change is also backported to: 1.1.7

  References: #3947

• [feature] [orm] Added .autocommit attribute to scoped_session, proxying the .autocommit attribute of the underling Session currently assigned to the thread. Pull request courtesy Ben Fagin.

• [feature] [mysql] Added support for MySQL's ON DUPLICATE KEY UPDATE MySQL-specific mysql.dml.Insert object. Pull request courtesy Michael Doronin.

  References: #4009

• [bug] [sql] The rules for type coercion between Numeric, Integer, and date-related types now include additional logic that will attempt to preserve the settings of the incoming type on the "resolved" type. Currently the target for this is the asdecimal flag, so that a math operation between Numeric or Float and Integer will preserve the "asdecimal" flag as well as if the type should be the Float subclass.

  References: #4018

• [bug] [mysql] [sql] The result processor for the Float type now unconditionally runs values through the float() processor if the dialect specifies that it also supports "native decimal" mode. While most backends will deliver Python float objects for a floating point datatype, the MySQL backends in some cases lack the typing information in order to provide this and return Decimal unless the float conversion is done.

  References: #4020

• [bug] [sql] Added some extra strictness to the handling of Python "float" values passed to SQL statements. A "float" value will be associated with the Float datatype and not the Decimal-coercing Numeric datatype as was the case before, eliminating a confusing warning emitted on SQLite as well as unnecessary coercion to Decimal.

  References: #4017

• [feature] [orm] Added a new feature orm.with_expression() that allows an ad-hoc SQL expression to be added to a specific entity in a query at result time. This is an alternative to the SQL expression being delivered as a separate element in the result tuple.

  References: #3058

• [bug] [orm] An UPDATE emitted as a result of the relationship.post_update feature will now integrate with the versioning feature to both bump the version id of the row as well as assert that the existing version number was matched.

  References: #3496

• [bug] [ext] The AssociationProxy.any(), AssociationProxy.has() and AssociationProxy.contains() comparison methods now support linkage to an attribute that is itself also an AssociationProxy, recursively.

  References: #3769

• [bug] [ext] Implemented in-place mutation operators __ior__, __iand__, __ixor__ and __isub__ for mutable.MutableSet and __iadd__ for mutable.MutableList so that change events are fired off when these mutator methods are used to alter the collection.

  References: #3853

• [bug] [declarative] A warning is emitted if the declared_attr.cascading modifier is used with a declarative attribute that is itself declared on a class that is to be mapped, as opposed to a declarative mixin class or __abstract__ class. The declared_attr.cascading modifier currently only applies to mixin/abstract classes.

  References: #3847

• [feature] [oracle] The Oracle dialect now inspects unique and check constraints when using Inspector.get_unique_constraints(), Inspector.get_check_constraints(). As Oracle does not have unique constraints that are separate from a unique Index, a Table that's reflected will still continue to not have UniqueConstraint objects associated with it. Pull requests courtesy Eloy Felix.

  References: #4003

• [feature] [orm] Added a new style of mapper-level inheritance loading "polymorphic selectin". This style of loading emits queries for each subclass in an inheritance hierarchy subsequent to the load of the base object type, using IN to specify the desired primary key values.

  References: #3948

• [bug] [orm] Repaired several use cases involving the relationship.post_update feature when used in conjunction with a column that has an "onupdate" value. When the UPDATE emits, the corresponding object attribute is now expired or refreshed so that the newly generated "onupdate" value can populate on the object; previously the stale value would remain. Additionally, if the target attribute is set in Python for the INSERT of the object, the value is now re-sent during the UPDATE so that the "onupdate" does not overwrite it (note this works just as well for server-generated onupdates). Finally, the SessionEvents.refresh_flush() event is now emitted for these attributes when refreshed within the flush.

  References: #3471, #3472

• [bug] [orm] Fixed bug where programmatic version_id counter in conjunction with joined table inheritance would fail if the version_id counter were not actually incremented and no other values on the base table were modified, as the UPDATE would have an empty SET clause. Since programmatic version_id where version counter is not incremented is a documented use case, this specific condition is now detected and the UPDATE now sets the version_id value to itself, so that concurrency checks still take place.

  References: #3996

• [bug] [declarative] [orm] Fixed bug where using declared_attr on an AbstractConcreteBase where a particular return value were some non-mapped symbol, including None, would cause the attribute to hard-evaluate just once and store the value to the object dictionary, not allowing it to invoke for subclasses. This behavior is normal when declared_attr is on a mapped class, and does not occur on a mixin or abstract class. Since AbstractConcreteBase is both "abstract" and actually "mapped", a special exception case is made here so that the "abstract" behavior takes precedence for declared_attr.

  References: #3848

• [bug] [orm] The versioning feature does not support NULL for the version counter. An exception is now raised if the version id is programmatic and was set to NULL for an UPDATE. Pull request courtesy Diana Clarke.

  References: #3673

• [bug] [sql] The operator precedence for all comparison operators such as LIKE, IS, IN, MATCH, equals, greater than, less than, etc. has all been merged into one level, so that expressions which make use of these against each other will produce parentheses between them. This suits the stated operator precedence of databases like Oracle, MySQL and others which place all of these operators as equal precedence, as well as PostgreSQL as of 9.5 which has also flattened its operator precedence.

  References: #3999

• [bug] [orm] Removed a very old keyword argument from scoped_session called scope. This keyword was never documented and was an early attempt at allowing for variable scopes.

  References: #3796

• [bug] [mysql] Added support for views that are unreflectable due to stale table definitions, when calling MetaData.reflect(); a warning is emitted for the table that cannot respond to DESCRIBE, but the operation succeeds.

  References: #3871

• [ext] [feature] Added new flag Session.enable_baked_queries to the Session to allow baked queries to be disabled session-wide, reducing memory use. Also added new Bakery wrapper so that the bakery returned by BakedQuery.bakery can be inspected.

• [bug] [orm] Fixed bug where combining a "with_polymorphic" load in conjunction with subclass-linked relationships that specify joinedload with innerjoin=True, would fail to demote those "innerjoins" to "outerjoins" to suit the other polymorphic classes that don't support that relationship. This applies to both a single and a joined inheritance polymorphic load.

  References: #3988

• [bug] [orm] Added new argument with_for_update to the Session.refresh() method. When the Query.with_lockmode() method were deprecated in favor of Query.with_for_update(), the Session.refresh() method was never updated to reflect the new option.

  References: #3991

• [bug] [orm] Fixed bug where a column_property() that is also marked as "deferred" would be marked as "expired" during a flush, causing it to be loaded along with the unexpiry of regular attributes even though this attribute was never accessed.

  References: #3984

• [bug] [sql] Repaired issue where the type of an expression that used ColumnOperators.is_() or similar would not be a "boolean" type, instead the type would be "nulltype", as well as when using custom comparison operators against an untyped expression. This typing can impact how the expression behaves in larger contexts as well as in result-row-handling.

  References: #3873

• [bug] [ext] Improved the association proxy list collection so that premature autoflush against a newly created association object can be prevented in the case where list.append() is being used, and a lazy load would be invoked when the association proxy accesses the endpoint collection. The endpoint collection is now accessed first before the creator is invoked to produce the association object.

  References: #3941

• [bug] [sql] Fixed the negation of a Label construct so that the inner element is negated correctly, when the not_() modifier is applied to the labeled expression.

  References: #3969

• [feature] [orm] Added a new kind of eager loading called "selectin" loading. This style of loading is very similar to "subquery" eager loading, except that it uses an IN expression given a list of primary key values from the loaded parent objects, rather than re-stating the original query. This produces a more efficient query that is "baked" (e.g. the SQL string is cached) and also works in the context of Query.yield_per().

  References: #3944

• [bug] [orm] Fixed bug in subquery eager loading where the "join_depth" parameter for self-referential relationships would not be correctly honored, loading all available levels deep rather than correctly counting the specified number of levels for eager loading.

  References: #3967

• [bug] [orm] Added warnings to the LRU "compiled cache" used by the Mapper (and ultimately will be for other ORM-based LRU caches) such that when the cache starts hitting its size limits, the application will emit a warning that this is a performance-degrading situation that may require attention. The LRU caches can reach their size limits primarily if an application is making use of an unbounded number of Engine objects, which is an antipattern. Otherwise, this may suggest an issue that should be brought to the SQLAlchemy developer's attention.

• [bug] [postgresql] Fixed bug where the base sqltypes.ARRAY datatype would not invoke the bind/result processors of postgresql.ARRAY.

  References: #3964

• [bug] [orm] Fixed bug to improve upon the specificity of loader options that take effect subsequent to the lazy load of a related entity, so that the loader options will match to an aliased or non-aliased entity more specifically if those options include entity information.

  References: #3963

• [feature] [orm] The lazy="select" loader strategy now makes used of the BakedQuery query caching system in all cases. This removes most overhead of generating a Query object and running it into a select() and then string SQL statement from the process of lazy-loading related collections and objects. The "baked" lazy loader has also been improved such that it can now cache in most cases where query load options are used.

  References: #3954

• [bug] [sql] The system by which percent signs in SQL statements are "doubled" for escaping purposes has been refined. The "doubling" of percent signs mostly associated with the literal_column construct as well as operators like ColumnOperators.contains() now occurs based on the stated paramstyle of the DBAPI in use; for percent-sensitive paramstyles as are common with the PostgreSQL and MySQL drivers the doubling will occur, for others like that of SQLite it will not. This allows more database-agnostic use of the literal_column construct to be possible.

  References: #3740

• [bug] [postgresql] Added support for all possible "fields" identifiers when reflecting the PostgreSQL INTERVAL datatype, e.g. "YEAR", "MONTH", "DAY TO MINUTE", etc.. In addition, the postgresql.INTERVAL datatype itself now includes a new parameter postgresql.INTERVAL.fields where these qualifiers can be specified; the qualifier is also reflected back into the resulting datatype upon reflection / inspection.

  References: #3959

• [bug] [sql] Fixed bug where a column-level CheckConstraint would fail to compile the SQL expression using the underlying dialect compiler as well as apply proper flags to generate literal values as inline, in the case that the sqltext is a Core expression and not just a plain string. This was long-ago fixed for table-level check constraints in 0.9 as part of #2742, which more commonly feature Core SQL expressions as opposed to plain string expressions.

  References: #3957

• [bug] [mssql] The SQL Server dialect now allows for a database and/or owner name with a dot inside of it, using brackets explicitly in the string around the owner and optionally the database name as well. In addition, sending the quoted_name construct for the schema name will not split on the dot and will deliver the full string as the "owner". quoted_name is also now available from the sqlalchemy.sql import space.

  References: #2626

• [feature] [sql] Added a new kind of bindparam() called "expanding". This is for use in IN expressions where the list of elements is rendered into individual bound parameters at statement execution time, rather than at statement compilation time. This allows both a single bound parameter name to be linked to an IN expression of multiple elements, as well as allows query caching to be used with IN expressions. The new feature allows the related features of "select in" loading and "polymorphic in" loading to make use of the baked query extension to reduce call overhead. This feature should be considered to be experimental for 1.2.

  References: #3953

• [bug] [sql] Fixed bug where a SQL-oriented Python-side column default could fail to be executed properly upon INSERT in the "pre-execute" codepath, if the SQL itself were an untyped expression, such as plain text. The "pre- execute" codepath is fairly uncommon however can apply to non-integer primary key columns with SQL defaults when RETURNING is not used.

  References: #3923

• [bug] [sql] The expression used for COLLATE as rendered by the column-level expression.collate() and ColumnOperators.collate() is now quoted as an identifier when the name is case sensitive, e.g. has uppercase characters. Note that this does not impact type-level collation, which is already quoted.

  References: #3785

• [ext] [feature] [orm] The Query.update() method can now accommodate both hybrid attributes as well as composite attributes as a source of the key to be placed in the SET clause. For hybrids, an additional decorator hybrid_property.update_expression() is supplied for which the user supplies a tuple-returning function.

  References: #3229

• [bug] [orm] The attributes.flag_modified() function now raises InvalidRequestError if the named attribute key is not present within the object, as this is assumed to be present in the flush process. To mark an object "dirty" for a flush without referring to any specific attribute, the attributes.flag_dirty() function may be used.

  References: #3753

• [bug] [ext] The sqlalchemy.ext.hybrid.hybrid_property class now supports calling mutators like @setter, @expression etc. multiple times across subclasses, and now provides a @getter mutator, so that a particular hybrid can be repurposed across subclasses or other classes. This now matches the behavior of @property in standard Python.

  References: #3911, #3912

• [feature] [mysql] [oracle] [postgresql] [sql] Added support for SQL comments on Table and Column objects, via the new Table.comment and Column.comment arguments. The comments are included as part of DDL on table creation, either inline or via an appropriate ALTER statement, and are also reflected back within table reflection, as well as via the Inspector. Supported backends currently include MySQL, PostgreSQL, and Oracle. Many thanks to Frazer McLean for a large amount of effort on this.

  References: #1546

• [engine] [feature] Added native "pessimistic disconnection" handling to the Pool object. The new parameter Pool.pre_ping, available from the engine as create_engine.pool_pre_ping, applies an efficient form of the "pre-ping" recipe featured in the pooling documentation, which upon each connection check out, emits a simple statement, typically "SELECT 1", to test the connection for liveness. If the existing connection is no longer able to respond to commands, the connection is transparently recycled, and all other connections made prior to the current timestamp are invalidated.

  References: #3919

• [bug] [sql] Fixed bug where the use of an Alias object in a column context would raise an argument error when it tried to group itself into a parenthesized expression. Using Alias in this way is not yet a fully supported API, however it applies to some end-user recipes and may have a more prominent role in support of some future PostgreSQL features.

  References: #3939

• [bug] [orm] The "evaluate" strategy used by Query.update() and Query.delete() can now accommodate a simple object comparison from a many-to-one relationship to an instance, when the attribute names of the primary key / foreign key columns don't match the actual names of the columns. Previously this would do a simple name-based match and fail with an AttributeError.

  References: #3366

• [feature] [orm] Added new attribute event AttributeEvents.bulk_replace(). This event is triggered when a collection is assigned to a relationship, before the incoming collection is compared with the existing one. This early event allows for conversion of incoming non-ORM objects as well. The event is integrated with the @validates decorator.

  References: #3896

• [bug] [orm] The @validates decorator now allows the decorated method to receive objects from a "bulk collection set" operation that have not yet been compared to the existing collection. This allows incoming values to be converted to compatible ORM objects as is already allowed from an "append" event. Note that this means that the @validates method is called for all values during a collection assignment, rather than just the ones that are new.

  References: #3896

• [bug] [engine] Fixed bug where in the unusual case of passing a Compiled object directly to Connection.execute(), the dialect with which the Compiled object were generated was not consulted for the paramstyle of the string statement, instead assuming it would match the dialect-level paramstyle, causing mismatches to occur.

  References: #3938

• [feature] [orm] Added new event handler AttributeEvents.modified() which is triggered when the func:.attributes.flag_modified function is invoked, which is common when using the sqlalchemy.ext.mutable extension module.

  References: #3303

• [bug] [ext] Fixed a bug in the sqlalchemy.ext.serializer extension whereby an "annotated" SQL element (as produced by the ORM for many types of SQL expressions) could not be reliably serialized. Also bumped the default pickle level for the serializer to "HIGHEST_PROTOCOL".

  References: #3918

• [bug] [orm] Fixed bug in single-table inheritance where the select_from() argument would not be taken into account when limiting rows to a subclass. Previously, only expressions in the columns requested would be taken into account.

  References: #3891

• [bug] [orm] When assigning a collection to an attribute mapped by a relationship, the previous collection is no longer mutated. Previously, the old collection would be emptied out in conjunction with the "item remove" events that fire off; the events now fire off without affecting the old collection.

  References: #3913

• [bug] [oracle] The cx_Oracle dialect now supports "sane multi rowcount", that is, when a series of parameter sets are executed via DBAPI cursor.executemany(), we can make use of cursor.rowcount to verify the number of rows matched. This has an impact within the ORM when detecting concurrent modification scenarios, in that some simple conditions can now be detected even when the ORM is batching statements, as well as when the more strict versioning feature is used, the ORM can still use statement batching. The flag is enabled for cx_Oracle assuming at least version 5.0, which is now commonplace.

  References: #3932

• [feature] [sql] The longstanding behavior of the ColumnOperators.in_() and ColumnOperators.notin_() operators emitting a warning when the right-hand condition is an empty sequence has been revised; a simple "static" expression of "1 != 1" or "1 = 1" is now rendered by default, rather than pulling in the original left-hand expression. This causes the result for a NULL column comparison against an empty set to change from NULL to true/false. The behavior is configurable, and the old behavior can be enabled using the create_engine.empty_in_strategy parameter to create_engine().

  References: #3907

• [bug] [oracle] Oracle reflection now "normalizes" the name given to a foreign key constraint, that is, returns it as all lower case for a case insensitive name. This was already the behavior for indexes and primary key constraints as well as all table and column names. This will allow Alembic autogenerate scripts to compare and render foreign key constraint names correctly when initially specified as case insensitive.

  References: #3276

• [feature] [sql] Added a new option autoescape to the "startswith" and "endswith" classes of comparators; this supplies an escape character also applies it to all occurrences of the wildcard characters "%" and "_" automatically. Pull request courtesy Diana Clarke.

  This feature has been changed as of 1.2.0 from its initial implementation in 1.2.0b2 such that autoescape is now passed as a boolean value, rather than a specific character to use as the escape character.

  References: #2694

• [bug] [orm] The state of the Session is now present when the SessionEvents.after_rollback() event is emitted, that is, the attribute state of objects prior to their being expired. This is now consistent with the behavior of the SessionEvents.after_commit() event which also emits before the attribute state of objects is expired.

  References: #3934

• [bug] [orm] Fixed bug where Query.with_parent() would not work if the Query were against an aliased() construct rather than a regular mapped class. Also adds a new parameter util.with_parent.from_entity to the standalone util.with_parent() function as well as Query.with_parent().

  References: #3607

1.1.18

Released: March 6, 2018

postgresql

• [postgresql] [bug] [py3k] Fixed bug in PostgreSQL COLLATE / ARRAY adjustment first introduced in #4006 where new behaviors in Python 3.7 regular expressions caused the fix to fail.

  References: #4208

mysql

• [mysql] [bug] MySQL dialects now query the server version using SELECT @@version explicitly to the server to ensure we are getting the correct version information back. Proxy servers like MaxScale interfere with the value that is passed to the DBAPI's connection.server_version value so this is no longer reliable.

  References: #4205

1.1.17

Released: February 22, 2018

• [bug] [ext] Repaired regression caused in 1.2.3 and 1.1.16 regarding association proxy objects, revising the approach to #4185 when calculating the "owning class" of an association proxy to default to choosing the current class if the proxy object is not directly associated with a mapped class, such as a mixin.

  References: #4185

1.1.16

Released: February 16, 2018

orm

• [orm] [bug] Fixed issue in post_update feature where an UPDATE is emitted when the parent object has been deleted but the dependent object is not. This issue has existed for a long time however since 1.2 now asserts rows matched for post_update, this was raising an error.

  References: #4187

• [orm] [bug] Fixed regression caused by fix for issue #4116 affecting versions 1.2.2 as well as 1.1.15, which had the effect of mis-calculation of the "owning class" of an AssociationProxy as the NoneType class in some declarative mixin/inheritance situations as well as if the association proxy were accessed off of an un-mapped class. The "figure out the owner" logic has been replaced by an in-depth routine that searches through the complete mapper hierarchy assigned to the class or subclass to determine the correct (we hope) match; will not assign the owner if no match is found. An exception is now raised if the proxy is used against an un-mapped instance.

  References: #4185

• [orm] [bug] Fixed bug where an object that is expunged during a rollback of a nested or subtransaction which also had its primary key mutated would not be correctly removed from the session, causing subsequent issues in using the session.

  References: #4151

sql

• [sql] [bug] Added nullsfirst() and nullslast() as top level imports in the sqlalchemy. and sqlalchemy.sql. namespace. Pull request courtesy Lele Gaifax.

• [sql] [bug] Fixed bug in Insert.values() where using the "multi-values" format in combination with Column objects as keys rather than strings would fail. Pull request courtesy Aubrey Stark-Toller.

  References: #4162

postgresql

• [postgresql] [bug] Added "SSL SYSCALL error: Operation timed out" to the list of messages that trigger a "disconnect" scenario for the psycopg2 driver. Pull request courtesy André Cruz.

• [postgresql] [bug] Added "TRUNCATE" to the list of keywords accepted by the PostgreSQL dialect as an "autocommit"-triggering keyword. Pull request courtesy Jacob Hayes.

mysql

• [mysql] [bug] Fixed bug where the MySQL "concat" and "match" operators failed to propagate kwargs to the left and right expressions, causing compiler options such as "literal_binds" to fail.

  References: #4136

misc

• [bug] [pool] Fixed a fairly serious connection pool bug where a connection that is acquired after being refreshed as a result of a user-defined DisconnectionError or due to the 1.2-released "pre_ping" feature would not be correctly reset if the connection were returned to the pool by weakref cleanup (e.g. the front-facing object is garbage collected); the weakref would still refer to the previously invalidated DBAPI connection which would have the reset operation erroneously called upon it instead. This would lead to stack traces in the logs and a connection being checked into the pool without being reset, which can cause locking issues.

  References: #4184