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 " 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