2.0.0rc3

Released: January 18, 2023

orm

• [orm] [feature] Added a new parameter to _orm.Mapper called _orm.Mapper.polymorphic_abstract. The purpose of this directive is so that the ORM will not consider the class to be instantiated or loaded directly, only subclasses. The actual effect is that the _orm.Mapper will prevent direct instantiation of instances of the class and will expect that the class does not have a distinct polymorphic identity configured.

  In practice, the class that is mapped with _orm.Mapper.polymorphic_abstract can be used as the target of a _orm.relationship() as well as be used in queries; subclasses must of course include polymorphic identities in their mappings.

  The new parameter is automatically applied to classes that subclass the AbstractConcreteBase class, as this class is not intended to be instantiated.

  References: #9060

• [orm] [bug] Fixed issue where using a pep-593 Annotated type in the _orm.registry.type_annotation_map which itself contained a generic plain container or collections.abc type (e.g. list, dict, collections.abc.Sequence, etc. ) as the target type would produce an internal error when the ORM were trying to interpret the Annotated instance.

  References: #9099

• [orm] [bug] Added an error message when a _orm.relationship() is mapped against an abstract container type, such as Mapped[Sequence[B]], without providing the _orm.relationship.container_class parameter which is necessary when the type is abstract. Previously the the abstract container would attempt to be instantiated at a later step and fail.

  References: #9100

sql

• [sql] [bug] Fixed bug / regression where using bindparam() with the same name as a column in the Update.values() method of Update, as well as the _sql.Insert.values() method of _sql.Insert in 2.0 only, would in some cases silently fail to honor the SQL expression in which the parameter were presented, replacing the expression with a new parameter of the same name and discarding any other elements of the SQL expression, such as SQL functions, etc. The specific case would be statements that were constructed against ORM entities rather than plain Table instances, but would occur if the statement were invoked with a Session or a Connection.

  Update part of the issue was present in both 2.0 and 1.4 and is backported to 1.4.

  This change is also backported to: 1.4.47

  References: #9075

• [sql] [bug] Fixed bug / regression where using bindparam() with the same name as a column in the Update.values() method of Update, as well as the _sql.Insert.values() method of _sql.Insert in 2.0 only, would in some cases silently fail to honor the SQL expression in which the parameter were presented, replacing the expression with a new parameter of the same name and discarding any other elements of the SQL expression, such as SQL functions, etc. The specific case would be statements that were constructed against ORM entities rather than plain Table instances, but would occur if the statement were invoked with a Session or a Connection.

  Update part of the issue was present in both 2.0 and 1.4 and is backported to 1.4.

  This change is also backported to: 1.4.47

  References: #9075

typing

• [typing] [bug] Fixes to the annotations within the sqlalchemy.ext.hybrid extension for more effective typing of user-defined methods. The typing now uses PEP 612 features, now supported by recent versions of Mypy, to maintain argument signatures for hybrid_method. Return values for hybrid methods are accepted as SQL expressions in contexts such as _sql.Select.where() while still supporting SQL methods.

  References: #9096

mypy

• [mypy] [bug] Adjustments made to the mypy plugin to accommodate for some potential changes being made for issue #236 sqlalchemy2-stubs when using SQLAlchemy 1.4. These changes are being kept in sync within SQLAlchemy 2.0. The changes are also backwards compatible with older versions of sqlalchemy2-stubs.

  This change is also backported to: 1.4.47

• [mypy] [bug] Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions if a decorator for the _orm.registry.mapped() decorator were used that was referenced in an expression with more than two components (e.g. @Backend.mapper_registry.mapped). This scenario is now ignored; when using the plugin, the decorator expression needs to be two components (i.e. @reg.mapped).

  This change is also backported to: 1.4.47

  References: #9102

postgresql

• [postgresql] [bug] Fixed regression where psycopg3 changed an API call as of version 3.1.8 to expect a specific object type that was previously not enforced, breaking connectivity for the psycopg3 dialect.

  References: #9106

oracle

• [oracle] [usecase] Added support for the Oracle SQL type TIMESTAMP WITH LOCAL TIME ZONE, using a newly added Oracle-specific _oracle.TIMESTAMP datatype.

  References: #9086

2.0.0rc2

Released: January 9, 2023

orm

• [orm] [bug] Fixed issue where an overly restrictive ORM mapping rule were added in 2.0 which prevented mappings against TableClause objects, such as those used in the view recipe on the wiki.

  References: #9071

typing

• [typing] [bug] The Data Class Transforms argument field_descriptors was renamed to field_specifiers in the accepted version of PEP 681.

  References: #9067

postgresql

• [postgresql] [bug] Added support to the asyncpg dialect to return the cursor.rowcount value for SELECT statements when available. While this is not a typical use for cursor.rowcount, the other PostgreSQL dialects generally provide this value. Pull request courtesy Michael Gorven.

  This change is also backported to: 1.4.47

  References: #9048

• [postgresql] [json] Implemented missing JSONB operations:

  -   `@@` using `_postgresql.JSONB.Comparator.path_match()`
  
  -   `@?` using `_postgresql.JSONB.Comparator.path_exists()`
  
  -   `#-` using `_postgresql.JSONB.Comparator.delete_path()`
  

  Pull request curtesy of Guilherme Martins Crocetti.

  References: #7147

mysql

• [mysql] [usecase] Added support to MySQL index reflection to correctly reflect the mysql_length dictionary, which previously was being ignored.

  This change is also backported to: 1.4.47

  References: #9047

• [mysql] [bug] Restored the behavior of Inspector.has_table() to report on temporary tables for MySQL / MariaDB. This is currently the behavior for all other included dialects, but was removed for MySQL in 1.4 due to no longer using the DESCRIBE command; there was no documented support for temp tables being reported by the Inspector.has_table() method in this version or on any previous version, so the previous behavior was undefined.

  As SQLAlchemy 2.0 has added formal support for temp table status via Inspector.has_table(), the MySQL /MariaDB dialect has been reverted to use the "DESCRIBE" statement as it did in the SQLAlchemy 1.3 series and previously, and test support is added to include MySQL / MariaDB for this behavior. The previous issues with ROLLBACK being emitted which 1.4 sought to improve upon don't apply in SQLAlchemy 2.0 due to simplifications in how Connection handles transactions.

  DESCRIBE is necessary as MariaDB in particular has no consistently available public information schema of any kind in order to report on temp tables other than DESCRIBE/SHOW COLUMNS, which rely on throwing an error in order to report no results.

  References: #9058

oracle

• [oracle] [bug] Supported use case for foreign key constraints where the local column is marked as "invisible". The errors normally generated when a ForeignKeyConstraint is created that check for the target column are disabled when reflecting, and the constraint is skipped with a warning in the same way which already occurs for an Index with a similar issue.

  References: #9059

2.0.0rc1

Released: December 28, 2022

general

• [general] [bug] Fixed regression where the base compat module was calling upon platform.architecture() in order to detect some system properties, which results in an over-broad system call against the system-level file call that is unavailable under some circumstances, including within some secure environment configurations.

  This change is also backported to: 1.4.46

  References: #8995

orm

• [orm] [feature] Added a new default value for the Mapper.eager_defaults parameter "auto", which will automatically fetch table default values during a unit of work flush, if the dialect supports RETURNING for the INSERT being run, as well as insertmanyvalues <engine_insertmanyvalues> available. Eager fetches for server-side UPDATE defaults, which are very uncommon, continue to only take place if Mapper.eager_defaults is set to True, as there is no batch-RETURNING form for UPDATE statements.

  References: #8889

• [orm] [usecase] Adjustments to the _orm.Session in terms of extensibility, as well as updates to the ShardedSession extension:

  -   `_orm.Session.get()` now accepts
      `_orm.Session.get.bind_arguments`, which in particular may be
      useful when using the horizontal sharding extension.
  
  -   `_orm.Session.get_bind()` accepts arbitrary kw arguments, which
      assists in developing code that uses a `_orm.Session` class which
      overrides this method with additional arguments.
  
  -   Added a new ORM execution option `identity_token` which may be used
      to directly affect the "identity token" that will be associated with
      newly loaded ORM objects.  This token is how sharding approaches
      (namely the `ShardedSession`, but can be used in other cases
      as well) separate object identities across different "shards".
  
  -   The `_orm.SessionEvents.do_orm_execute()` event hook may now be used
      to affect all ORM-related options, including `autoflush`,
      `populate_existing`, and `yield_per`; these options are re-consumed
      subsequent to event hooks being invoked before they are acted upon.
      Previously, options like `autoflush` would have been already evaluated
      at this point. The new `identity_token` option is also supported in
      this mode and is now used by the horizontal sharding extension.
  
  -   The `ShardedSession` class replaces the
      `ShardedSession.id_chooser` hook with a new hook
      `ShardedSession.identity_chooser`, which no longer relies upon
      the legacy `_orm.Query` object.
      `ShardedSession.id_chooser` is still accepted in place of
      `ShardedSession.identity_chooser` with a deprecation warning.
  

  References: #7837

• [orm] [usecase] The behavior of "joining an external transaction into a Session" has been revised and improved, allowing explicit control over how the _orm.Session will accommodate an incoming _engine.Connection that already has a transaction and possibly a savepoint already established. The new parameter _orm.Session.join_transaction_mode includes a series of option values which can accommodate the existing transaction in several ways, most importantly allowing a _orm.Session to operate in a fully transactional style using savepoints exclusively, while leaving the externally initiated transaction non-committed and active under all circumstances, allowing test suites to rollback all changes that take place within tests.

  Additionally, revised the _orm.Session.close() method to fully close out savepoints that may still be present, which also allows the "external transaction" recipe to proceed without warnings if the _orm.Session did not explicitly end its own SAVEPOINT transactions.

  References: #9015

• [orm] [usecase] Removed the requirement that the __allow_unmapped__ attribute be used on Declarative Dataclass Mapped class when non-Mapped[] annotations are detected; previously, an error message that was intended to support legacy ORM typed mappings would be raised, which additionally did not mention correct patterns to use with Dataclasses specifically. This error message is now no longer raised if _orm.registry.mapped_as_dataclass() or _orm.MappedAsDataclass is used.

  References: #8973

• [orm] [bug] Fixed issue in the internal SQL traversal for DML statements like _dml.Update and _dml.Delete which would cause among other potential issues, a specific issue using lambda statements with the ORM update/delete feature.

  This change is also backported to: 1.4.46

  References: #9033

• [orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the current loaded contents of relationship attributes that were indicated with the _orm.relationship.viewonly parameter, thus defeating strategies that use _orm.Session.merge() to pull fully loaded objects from caches and other similar techniques. In a related change, fixed issue where an object that contains a loaded relationship that was nonetheless configured as lazy='raise' on the mapping would fail when passed to _orm.Session.merge(); checks for "raise" are now suspended within the merge process assuming the _orm.Session.merge.load parameter remains at its default of True.

  Overall, this is a behavioral adjustment to a change introduced in the 1.4 series as of #4994, which took "merge" out of the set of cascades applied by default to "viewonly" relationships. As "viewonly" relationships aren't persisted under any circumstances, allowing their contents to transfer during "merge" does not impact the persistence behavior of the target object. This allows _orm.Session.merge() to correctly suit one of its use cases, that of adding objects to a Session that were loaded elsewhere, often for the purposes of restoring from a cache.

  This change is also backported to: 1.4.45

  References: #8862

• [orm] [bug] Fixed issues in _orm.with_expression() where expressions that were composed of columns that were referenced from the enclosing SELECT would not render correct SQL in some contexts, in the case where the expression had a label name that matched the attribute which used _orm.query_expression(), even when _orm.query_expression() had no default expression. For the moment, if the _orm.query_expression() does have a default expression, that label name is still used for that default, and an additional label with the same name will continue to be ignored. Overall, this case is pretty thorny so further adjustments might be warranted.

  This change is also backported to: 1.4.45

  References: #8881

• [orm] [bug] A warning is emitted if a backref name used in _orm.relationship() names an attribute on the target class which already has a method or attribute assigned to that name, as the backref declaration will replace that attribute.

  References: #4629

• [orm] [bug] A series of changes and improvements regarding _orm.Session.refresh(). The overall change is that primary key attributes for an object are now included in a refresh operation unconditionally when relationship-bound attributes are to be refreshed, even if not expired and even if not specified in the refresh.

  -   Improved `_orm.Session.refresh()` so that if autoflush is enabled
      (as is the default for `_orm.Session`), the autoflush takes place
      at an earlier part of the refresh process so that pending primary key
      changes are applied without errors being raised.  Previously, this
      autoflush took place too late in the process and the SELECT statement
      would not use the correct key to locate the row and an
      `InvalidRequestError` would be raised.
  
  -   When the above condition is present, that is, unflushed primary key
      changes are present on the object, but autoflush is not enabled,
      the refresh() method now explicitly disallows the operation to proceed,
      and an informative `InvalidRequestError` is raised asking that
      the pending primary key changes be flushed first.  Previously,
      this use case was simply broken and `InvalidRequestError`
      would be raised anyway. This restriction is so that it's safe for the
      primary key attributes to be refreshed, as is necessary for the case of
      being able to refresh the object with relationship-bound secondary
      eagerloaders also being emitted. This rule applies in all cases to keep
      API behavior consistent regardless of whether or not the PK cols are
      actually needed in the refresh, as it is unusual to be refreshing
      some attributes on an object while keeping other attributes "pending"
      in any case.
  
  -   The `_orm.Session.refresh()` method has been enhanced such that
      attributes which are `_orm.relationship()`-bound and linked to an
      eager loader, either at mapping time or via last-used loader options,
      will be refreshed in all cases even when a list of attributes is passed
      that does not include any columns on the parent row. This builds upon the
      feature first implemented for non-column attributes as part of
      [#1763](https://www.sqlalchemy.org/trac/ticket/1763) fixed in 1.4 allowing eagerly-loaded relationship-bound
      attributes to participate in the `_orm.Session.refresh()` operation.
      If the refresh operation does not indicate any columns on the parent row
      to be refreshed, the primary key columns will nonetheless be included
      in the refresh operation, which allows the load to proceed into the
      secondary relationship loaders indicated as it does normally.
      Previously an `InvalidRequestError` error would be raised
      for this condition ([#8703](https://www.sqlalchemy.org/trac/ticket/8703))
  
  -   Fixed issue where an unnecessary additional SELECT would be emitted in
      the case where `_orm.Session.refresh()` were called with a
      combination of expired attributes, as well as an eager loader such as
      `_orm.selectinload()` that emits a "secondary" query, if the primary
      key attributes were also in an expired state.  As the primary key
      attributes are now included in the refresh automatically, there is no
      additional load for these attributes when a relationship loader
      goes to select for them ([#8997](https://www.sqlalchemy.org/trac/ticket/8997))
  
  -   Fixed regression caused by [#8126](https://www.sqlalchemy.org/trac/ticket/8126) released in 2.0.0b1 where the
      `_orm.Session.refresh()` method would fail with an
      `AttributeError`, if passed both an expired column name as well as the
      name of a relationship-bound attribute that was linked to a "secondary"
      eagerloader such as the `_orm.selectinload()` eager loader
      ([#8996](https://www.sqlalchemy.org/trac/ticket/8996))
  

  References: #8703, #8996, #8997

• [orm] [bug] Improved a fix first made in version 1.4 for #8456 which scaled back the usage of internal "polymorphic adapters", that are used to render ORM queries when the _orm.Mapper.with_polymorphic parameter is used. These adapters, which are very complex and error prone, are now used only in those cases where an explicit user-supplied subquery is used for _orm.Mapper.with_polymorphic, which includes only the use case of concrete inheritance mappings that use the _orm.polymorphic_union() helper, as well as the legacy use case of using an aliased subquery for joined inheritance mappings, which is not needed in modern use.

  For the most common case of joined inheritance mappings that use the built-in polymorphic loading scheme, which includes those which make use of the _orm.Mapper.polymorphic_load parameter set to inline, polymorphic adapters are now no longer used. This has both a positive performance impact on the construction of queries as well as a substantial simplification of the internal query rendering process.

  The specific issue targeted was to allow a _orm.column_property() to refer to joined-inheritance classes within a scalar subquery, which now works as intuitively as is feasible.

  References: #8168

engine

• [engine] [bug] Fixed a long-standing race condition in the connection pool which could occur under eventlet/gevent monkeypatching schemes in conjunction with the use of eventlet/gevent Timeout conditions, where a connection pool checkout that's interrupted due to the timeout would fail to clean up the failed state, causing the underlying connection record and sometimes the database connection itself to "leak", leaving the pool in an invalid state with unreachable entries. This issue was first identified and fixed in SQLAlchemy 1.2 for #4225, however the failure modes detected in that fix failed to accommodate for BaseException, rather than Exception, which prevented eventlet/gevent Timeout from being caught. In addition, a block within initial pool connect has also been identified and hardened with a BaseException -> "clean failed connect" block to accommodate for the same condition in this location. Big thanks to Github user @niklaus for their tenacious efforts in identifying and describing this intricate issue.

  This change is also backported to: 1.4.46

  References: #8974

• [engine] [bug] Fixed a long-standing race condition in the connection pool which could occur under eventlet/gevent monkeypatching schemes in conjunction with the use of eventlet/gevent Timeout conditions, where a connection pool checkout that's interrupted due to the timeout would fail to clean up the failed state, causing the underlying connection record and sometimes the database connection itself to "leak", leaving the pool in an invalid state with unreachable entries. This issue was first identified and fixed in SQLAlchemy 1.2 for #4225, however the failure modes detected in that fix failed to accommodate for BaseException, rather than Exception, which prevented eventlet/gevent Timeout from being caught. In addition, a block within initial pool connect has also been identified and hardened with a BaseException -> "clean failed connect" block to accommodate for the same condition in this location. Big thanks to Github user @niklaus for their tenacious efforts in identifying and describing this intricate issue.

  This change is also backported to: 1.4.46

  References: #8974

• [engine] [bug] Fixed issue where _engine.Result.freeze() method would not work for textual SQL using either _sql.text() or _engine.Connection.exec_driver_sql().

  This change is also backported to: 1.4.45

  References: #8963

sql

• [sql] [usecase] An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.

  This change is also backported to: 1.4.45

  References: #8800

• [sql] [bug] Added parameter FunctionElement.column_valued.joins_implicitly, which is useful in preventing the "cartesian product" warning when making use of table-valued or column-valued functions. This parameter was already introduced for FunctionElement.table_valued() in #7845, however it failed to be added for FunctionElement.column_valued() as well.

  This change is also backported to: 1.4.46

  References: #9009

• [sql] [bug] Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType error in 1.4) when using an expression whose type included _types.TypeEngine.bind_expression(), in the context of an "expanding" (i.e. "IN") parameter in conjunction with the literal_binds compiler parameter.

  This change is also backported to: 1.4.46

  References: #8989

• [sql] [bug] Fixed issue in lambda SQL feature where the calculated type of a literal value would not take into account the type coercion rules of the "compared to type", leading to a lack of typing information for SQL expressions, such as comparisons to _types.JSON elements and similar.

  This change is also backported to: 1.4.46

  References: #9029

• [sql] [bug] Added parameter FunctionElement.column_valued.joins_implicitly, which is useful in preventing the "cartesian product" warning when making use of table-valued or column-valued functions. This parameter was already introduced for FunctionElement.table_valued() in #7845, however it failed to be added for FunctionElement.column_valued() as well.

  This change is also backported to: 1.4.46

  References: #9009

• [sql] [bug] Fixed a series of issues regarding the position and sometimes the identity of rendered bound parameters, such as those used for SQLite, asyncpg, MySQL, Oracle and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL regexp_replace() function, the "nesting" feature of the CTE construct first introduced in #4123, and selectable tables formed by using the FunctionElement.column_valued() method with Oracle.

  This change is also backported to: 1.4.45

  References: #8827

• [sql] [bug] Added test support to ensure that all compiler visit_xyz() methods across all Compiler implementations in SQLAlchemy accept a **kw parameter, so that all compilers accept additional keyword arguments under all circumstances.

  References: #8988

• [sql] [bug] The SQLCompiler.construct_params() method, as well as the SQLCompiler.params accessor, will now return the exact parameters that correspond to a compiled statement that used the render_postcompile parameter to compile. Previously, the method returned a parameter structure that by itself didn't correspond to either the original parameters or the expanded ones.

  Passing a new dictionary of parameters to SQLCompiler.construct_params() for a SQLCompiler that was constructed with render_postcompile is now disallowed; instead, to make a new SQL string and parameter set for an alternate set of parameters, a new method SQLCompiler.construct_expanded_state() is added which will produce a new expanded form for the given parameter set, using the ExpandedState container which includes a new SQL statement and new parameter dictionary, as well as a positional parameter tuple.

  References: #6114

• [sql] [bug] To accommodate for third party dialects with different character escaping needs regarding bound parameters, the system by which SQLAlchemy "escapes" (i.e., replaces with another character in its place) special characters in bound parameter names has been made extensible for third party dialects, using the SQLCompiler.bindname_escape_chars dictionary which can be overridden at the class declaration level on any SQLCompiler subclass. As part of this change, also added the dot "." as a default "escaped" character.

  References: #8994

typing

• [typing] [bug] pep-484 typing has been completed for the sqlalchemy.ext.horizontal_shard extension as well as the sqlalchemy.orm.events module. Thanks to Gleb Kisenkov for their efforts.

  References: #6810, #9025

asyncio

• [asyncio] [bug] Removed non-functional merge() method from _asyncio.AsyncResult. This method has never worked and was included with _asyncio.AsyncResult in error.

  This change is also backported to: 1.4.45

  References: #8952

postgresql

• [postgresql] [usecase] Added the PostgreSQL type MACADDR8. Pull request courtesy of Asim Farooq.

  This change is also backported to: 1.4.46

  References: #8393

• [postgresql] [bug] Fixed bug where the PostgreSQL _postgresql.OnConflictClause.constraint parameter would accept an Index object, however would not expand this index out into its individual index expressions, instead rendering its name in an ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the "constraint name" form only accepts unique or exclude constraint names. The parameter continues to accept the index but now expands it out into its component expressions for the render.

  This change is also backported to: 1.4.46

  References: #9023

• [postgresql] [bug] Fixed bug where the PostgreSQL _postgresql.OnConflictClause.constraint parameter would accept an Index object, however would not expand this index out into its individual index expressions, instead rendering its name in an ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the "constraint name" form only accepts unique or exclude constraint names. The parameter continues to accept the index but now expands it out into its component expressions for the render.

  This change is also backported to: 1.4.46

  References: #9023

• [postgresql] [bug] Made an adjustment to how the PostgreSQL dialect considers column types when it reflects columns from a table, to accommodate for alternative backends which may return NULL from the PG format_type() function.

  This change is also backported to: 1.4.45

  References: #8748

• [postgresql] [bug] Added support for explicit use of PG full text functions with asyncpg and psycopg (SQLAlchemy 2.0 only), with regards to the REGCONFIG type cast for the first argument, which previously would be incorrectly cast to a VARCHAR, causing failures on these dialects that rely upon explicit type casts. This includes support for _postgresql.to_tsvector, _postgresql.to_tsquery, _postgresql.plainto_tsquery, _postgresql.phraseto_tsquery, _postgresql.websearch_to_tsquery, _postgresql.ts_headline, each of which will determine based on number of arguments passed if the first string argument should be interpreted as a PostgreSQL "REGCONFIG" value; if so, the argument is typed using a newly added type object _postgresql.REGCONFIG which is then explicitly cast in the SQL expression.

  References: #8977

• [postgresql] [bug] Fixed regression where newly revised PostgreSQL range types such as _postgresql.INT4RANGE could not be set up as the impl of a TypeDecorator custom type, instead raising a TypeError.

  References: #9020

• [postgresql] [bug] The _postgresql.Range.__eq___() will now return NotImplemented when comparing with an instance of a different class, instead of raising an AttributeError exception.

  References: #8984

sqlite

• [sqlite] [usecase] Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.

  This change is also backported to: 1.4.45

  References: #8903

• [sqlite] [usecase] Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.

  This change is also backported to: 1.4.45

  References: #8804

• [sqlite] [bug] Fixed regression caused by new support for reflection of partial indexes on SQLite added in 1.4.45 for #8804, where the index_list pragma command in very old versions of SQLite (possibly prior to 3.8.9) does not return the current expected number of columns, leading to exceptions raised when reflecting tables and indexes.

  This change is also backported to: 1.4.46

  References: #8969

oracle

• [oracle] [bug] Fixed issue in Oracle compiler where the syntax for FunctionElement.column_valued() was incorrect, rendering the name COLUMN_VALUE without qualifying the source table correctly.

  This change is also backported to: 1.4.45

  References: #8945

tests

• [tests] [bug] Fixed issue in tox.ini file where changes in the tox 4.0 series to the format of "passenv" caused tox to not function correctly, in particular raising an error as of tox 4.0.6.

  This change is also backported to: 1.4.46

• [tests] [bug] Added new exclusion rule for third party dialects called unusual_column_name_characters, which can be "closed" for third party dialects that don't support column names with unusual characters such as dots, slashes, or percent signs in them, even if the name is properly quoted.

  This change is also backported to: 1.4.46

  References: #9002

2.0.0b4

Released: December 5, 2022

orm

• [orm] [feature] Added a new parameter _orm.mapped_column.use_existing_column to accommodate the use case of a single-table inheritance mapping that uses the pattern of more than one subclass indicating the same column to take place on the superclass. This pattern was previously possible by using _orm.declared_attr() in conjunction with locating the existing column in the .__table__ of the superclass, however is now updated to work with _orm.mapped_column() as well as with pep-484 typing, in a simple and succinct way.

  References: #8822

• [orm] [usecase] Added support custom user-defined types which extend the Python enum.Enum base class to be resolved automatically to SQLAlchemy Enum SQL types, when using the Annotated Declarative Table feature. The feature is made possible through new lookup features added to the ORM type map feature, and includes support for changing the arguments of the Enum that's generated by default as well as setting up specific enum.Enum types within the map with specific arguments.

  References: #8859

• [orm] [usecase] Added _orm.mapped_column.compare parameter to relevant ORM attribute constructs including _orm.mapped_column(), _orm.relationship() etc. to provide for the Python dataclasses compare parameter on field(), when using the orm_declarative_native_dataclasses feature. Pull request courtesy Simon Schiele.

  References: #8905

• [orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the current loaded contents of relationship attributes that were indicated with the _orm.relationship.viewonly parameter, thus defeating strategies that use _orm.Session.merge() to pull fully loaded objects from caches and other similar techniques. In a related change, fixed issue where an object that contains a loaded relationship that was nonetheless configured as lazy='raise' on the mapping would fail when passed to _orm.Session.merge(); checks for "raise" are now suspended within the merge process assuming the _orm.Session.merge.load parameter remains at its default of True.

  Overall, this is a behavioral adjustment to a change introduced in the 1.4 series as of #4994, which took "merge" out of the set of cascades applied by default to "viewonly" relationships. As "viewonly" relationships aren't persisted under any circumstances, allowing their contents to transfer during "merge" does not impact the persistence behavior of the target object. This allows _orm.Session.merge() to correctly suit one of its use cases, that of adding objects to a Session that were loaded elsewhere, often for the purposes of restoring from a cache.

  This change is also backported to: 1.4.45

  References: #8862

• [orm] [bug] Fixed issues in _orm.with_expression() where expressions that were composed of columns that were referenced from the enclosing SELECT would not render correct SQL in some contexts, in the case where the expression had a label name that matched the attribute which used _orm.query_expression(), even when _orm.query_expression() had no default expression. For the moment, if the _orm.query_expression() does have a default expression, that label name is still used for that default, and an additional label with the same name will continue to be ignored. Overall, this case is pretty thorny so further adjustments might be warranted.

  This change is also backported to: 1.4.45

  References: #8881

• [orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the current loaded contents of relationship attributes that were indicated with the _orm.relationship.viewonly parameter, thus defeating strategies that use _orm.Session.merge() to pull fully loaded objects from caches and other similar techniques. In a related change, fixed issue where an object that contains a loaded relationship that was nonetheless configured as lazy='raise' on the mapping would fail when passed to _orm.Session.merge(); checks for "raise" are now suspended within the merge process assuming the _orm.Session.merge.load parameter remains at its default of True.

  Overall, this is a behavioral adjustment to a change introduced in the 1.4 series as of #4994, which took "merge" out of the set of cascades applied by default to "viewonly" relationships. As "viewonly" relationships aren't persisted under any circumstances, allowing their contents to transfer during "merge" does not impact the persistence behavior of the target object. This allows _orm.Session.merge() to correctly suit one of its use cases, that of adding objects to a Session that were loaded elsewhere, often for the purposes of restoring from a cache.

  This change is also backported to: 1.4.45

  References: #8862

• [orm] [bug] Fixed issues in _orm.with_expression() where expressions that were composed of columns that were referenced from the enclosing SELECT would not render correct SQL in some contexts, in the case where the expression had a label name that matched the attribute which used _orm.query_expression(), even when _orm.query_expression() had no default expression. For the moment, if the _orm.query_expression() does have a default expression, that label name is still used for that default, and an additional label with the same name will continue to be ignored. Overall, this case is pretty thorny so further adjustments might be warranted.

  This change is also backported to: 1.4.45

  References: #8881

• [orm] [bug] Fixed issue where use of an unknown datatype within a Mapped annotation for a column-based attribute would silently fail to map the attribute, rather than reporting an exception; an informative exception message is now raised.

  References: #8888

• [orm] [bug] Fixed a suite of issues involving Mapped use with dictionary types, such as Mapped[dict[str, str] | None], would not be correctly interpreted in Declarative ORM mappings. Support to correctly "de-optionalize" this type including for lookup in type_annotation_map has been fixed.

  References: #8777

• [orm] [bug] [performance] Additional performance enhancements within ORM-enabled SQL statements, specifically targeting callcounts within the construction of ORM statements, using combinations of _orm.aliased() with _sql.union() and similar "compound" constructs, in addition to direct performance improvements to the corresponding_column() internal method that is used heavily by the ORM by constructs like _orm.aliased() and similar.

  References: #8796

• [orm] [bug] Fixed bug in orm_declarative_native_dataclasses feature where using plain dataclass fields with the __allow_unmapped__ directive in a mapping would not create a dataclass with the correct class-level state for those fields, copying the raw Field object to the class inappropriately after dataclasses itself had replaced the Field object with the class-level default value.

  References: #8880

• [orm] [bug] [regression] Fixed regression where flushing a mapped class that's mapped against a subquery, such as a direct mapping or some forms of concrete table inheritance, would fail if the _orm.Mapper.eager_defaults parameter were used.

  References: #8812

• [orm] [bug] Fixed regression in 2.0.0b3 caused by #8759 where indicating the Mapped name using a qualified name such as sqlalchemy.orm.Mapped would fail to be recognized by Declarative as indicating the Mapped construct.

  References: #8853

orm extensions

• [usecase] [orm extensions] Added support for the association_proxy() extension function to take part within Python dataclasses configuration, when using the native dataclasses feature described at orm_declarative_native_dataclasses. Included are attribute-level arguments including association_proxy.init and association_proxy.default_factory.

  Documentation for association proxy has also been updated to use "Annotated Declarative Table" forms within examples, including type annotations used for AssocationProxy itself.

  References: #8878

sql

• [sql] [usecase] An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.

  This change is also backported to: 1.4.45

  References: #8800

• [sql] [usecase] Added _expression.ScalarValues that can be used as a column element allowing using _expression.Values inside IN clauses or in conjunction with ANY or ALL collection aggregates. This new class is generated using the method _expression.Values.scalar_values(). The _expression.Values instance is now coerced to a _expression.ScalarValues when used in a IN or NOT IN operation.

  References: #6289

• [sql] [bug] Fixed a series of issues regarding positionally rendered bound parameters, such as those used for SQLite, asyncpg, MySQL and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL regexp_replace() function as well as within the "nesting" feature of the CTE construct first introduced in #4123.

  This change is also backported to: 1.4.45

  References: #8827

• [sql] [bug] Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of ORM aliases with subqueries, cache key generation could produce excessively large keys that were orders of magnitude bigger than the statement itself. Much thanks to Rollo Konig Brock for their very patient, long term help in finally identifying this issue.

  This change is also backported to: 1.4.44

  References: #8790

• [sql] [bug] The approach to the numeric pep-249 paramstyle has been rewritten, and is now fully supported, including by features such as "expanding IN" and "insertmanyvalues". Parameter names may also be repeated in the source SQL construct which will be correctly represented within the numeric format using a single parameter. Introduced an additional numeric paramstyle called numeric_dollar, which is specifically what's used by the asyncpg dialect; the paramstyle is equivalent to numeric except numeric indicators are indicated by a dollar-sign rather than a colon. The asyncpg dialect now uses numeric_dollar paramstyle directly, rather than compiling to format style first.

  The numeric and numeric_dollar paramstyles assume that the target backend is capable of receiving the numeric parameters in any order, and will match the given parameter values to the statement based on matching their position (1-based) to the numeric indicator. This is the normal behavior of "numeric" paramstyles, although it was observed that the SQLite DBAPI implements a not-used "numeric" style that does not honor parameter ordering.

  References: #8849

• [sql] [bug] Adjusted the rendering of RETURNING, in particular when using _sql.Insert, such that it now renders columns using the same logic as that of the Select construct to generate labels, which will include disambiguating labels, as well as that a SQL function surrounding a named column will be labeled using the column name itself. This establishes better cross-compatibility when selecting rows from either Select constructs or from DML statements that use UpdateBase.returning(). A narrower scale change was also made for the 1.4 series that adjusted the function label issue only.

  References: #8770

schema

• [schema] [bug] Stricter rules are in place for appending of Column objects to Table objects, both moving some previous deprecation warnings to exceptions, and preventing some previous scenarios that would cause duplicate columns to appear in tables, when Table.extend_existing were set to True, for both programmatic Table construction as well as during reflection operations.

  See change_8925 for a rundown of these changes.

  References: #8925

typing

• [typing] [usecase] Added a new type SQLColumnExpression which may be indicated in user code to represent any SQL column oriented expression, including both those based on ColumnElement as well as on ORM QueryableAttribute. This type is a real class, not an alias, so can also be used as the foundation for other objects. An additional ORM-specific subclass SQLORMExpression is also included.

  References: #8847

• [typing] [bug] Adjusted internal use of the Python enum.IntFlag class which changed its behavioral contract in Python 3.11. This was not causing runtime failures however caused typing runs to fail under Python 3.11.

  References: #8783

• [typing] [bug] The sqlalchemy.ext.mutable extension and sqlalchemy.ext.automap extensions are now fully pep-484 typed. Huge thanks to Gleb Kisenkov for their efforts on this.

  References: #6810, #8667

• [typing] [bug] Corrected typing support for the _orm.relationship.secondary argument which may also accept a callable (lambda) that returns a FromClause.

• [typing] [bug] Improved the typing for sessionmaker and async_sessionmaker, so that the default type of their return value will be Session or AsyncSession, without the need to type this explicitly. Previously, Mypy would not automaticaly infer these return types from its generic base.

  As part of this change, arguments for Session, AsyncSession, sessionmaker and async_sessionmaker beyond the initial "bind" argument have been made keyword-only, which includes parameters that have always been documented as keyword arguments, such as Session.autoflush, Session.class_, etc.

  Pull request courtesy Sam Bull.

  References: #8842

• [typing] [bug] Fixed issue where passing a callbale function returning an iterable of column elements to _orm.relationship.order_by was flagged as an error in type checkers.

  References: #8776

postgresql

• [postgresql] [usecase] Complementing #8690, new comparison methods such as _postgresql.Range.adjacent_to(), _postgresql.Range.difference(), _postgresql.Range.union(), etc., were added to the PG-specific range objects, bringing them in par with the standard operators implemented by the underlying _postgresql.AbstractRange.comparator_factory.

  In addition, the __bool__() method of the class has been corrected to be consistent with the common Python containers behavior as well as how other popular PostgreSQL drivers do: it now tells whether the range instance is not empty, rather than the other way around.

  Pull request courtesy Lele Gaifax.

  References: #8765

• [postgresql] [change] [asyncpg] Changed the paramstyle used by asyncpg from format to numeric_dollar. This has two main benefits since it does not require additional processing of the statement and allows for duplicate parameters to be present in the statements.

  References: #8926

• [postgresql] [bug] Made an adjustment to how the PostgreSQL dialect considers column types when it reflects columns from a table, to accommodate for alternative backends which may return NULL from the PG format_type() function.

  This change is also backported to: 1.4.45

  References: #8748

• [postgresql] [bug] [mssql] For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the "non anon" label that's used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column's type, where the label name should match the column's name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to #6718, #6710. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends.

  This change is also backported to: 1.4.44

  References: #8770

• [postgresql] [bug] Added additional type-detection for the new PostgreSQL _postgresql.Range type, where previous cases that allowed the psycopg2-native range objects to be received directly by the DBAPI without SQLAlchemy intercepting them stopped working, as we now have our own value object. The _postgresql.Range object has been enhanced such that SQLAlchemy Core detects it in otherwise ambiguous situations (such as comparison to dates) and applies appropriate bind handlers. Pull request courtesy Lele Gaifax.

  References: #8884

sqlite

• [sqlite] [usecase] Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.

  This change is also backported to: 1.4.45

  References: #8903

• [sqlite] [usecase] Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.

  This change is also backported to: 1.4.45

  References: #8804

mssql

• [mssql] [bug] Fixed regression caused by the combination of #8177, re-enable setinputsizes for SQL server unless fast_executemany + DBAPI executemany is used for a statement, along with #6047, implement "insertmanyvalues", which bypasses DBAPI executemany in place of a custom DBAPI execute for INSERT statements. setinputsizes would incorrectly not be used for a multiple parameter-set INSERT statement that used "insertmanyvalues" if fast_executemany were turned on, as the check would incorrectly assume this is a DBAPI executemany call. The "regression" would then be that the "insertmanyvalues" statement format is apparently slightly more sensitive to multiple rows that don't use the same types for each row, so in such a case setinputsizes is especially needed.

  The fix repairs the fast_executemany check so that it only disables setinputsizes if true DBAPI executemany is to be used.

  References: #8917

oracle

• [oracle] [bug] Continued fixes for Oracle fix #8708 released in 1.4.43 where bound parameter names that start with underscores, which are disallowed by Oracle, were still not being properly escaped in all circumstances.

  This change is also backported to: 1.4.45

  References: #8708

tests

• [tests] [bug] Fixed issue where the --disable-asyncio parameter to the test suite would fail to not actually run greenlet tests and would also not prevent the suite from using a "wrapping" greenlet for the whole suite. This parameter now ensures that no greenlet or asyncio use will occur within the entire run when set.

  This change is also backported to: 1.4.44

  References: #8793

2.0.0b3

Released: November 4, 2022

orm declarative

• [orm] [declarative] [bug] Added support in ORM declarative annotations for class names specified for _orm.relationship(), as well as the name of the _orm.Mapped symbol itself, to be different names than their direct class name, to support scenarios such as where _orm.Mapped is imported as from sqlalchemy.orm import Mapped as M, or where related class names are imported with an alternate name in a similar fashion. Additionally, a target class name given as the lead argument for _orm.relationship() will always supersede the name given in the left hand annotation, so that otherwise un-importable names that also don't match the class name can still be used in annotations.

  References: #8759

• [orm] [declarative] [bug] Improved support for legacy 1.4 mappings that use annotations which don't include Mapped[], by ensuring the __allow_unmapped__ attribute can be used to allow such legacy annotations to pass through Annotated Declarative without raising an error and without being interpreted in an ORM runtime context. Additionally improved the error message generated when this condition is detected, and added more documentation for how this situation should be handled. Unfortunately the 1.4 WARN_SQLALCHEMY_20 migration warning cannot detect this particular configurational issue at runtime with its current architecture.

  References: #8692

• [orm] [declarative] [bug] Changed a fundamental configuration behavior of Mapper, where _schema.Column objects that are explicitly present in the _orm.Mapper.properties dictionary, either directly or enclosed within a mapper property object, will now be mapped within the order of how they appear within the mapped Table (or other selectable) itself (assuming they are in fact part of that table's list of columns), thereby maintaining the same order of columns in the mapped selectable as is instrumented on the mapped class, as well as what renders in an ORM SELECT statement for that mapper. Previously (where "previously" means since version 0.0.1), Column objects in the _orm.Mapper.properties dictionary would always be mapped first, ahead of when the other columns in the mapped Table would be mapped, causing a discrepancy in the order in which the mapper would assign attributes to the mapped class as well as the order in which they would render in statements.

  The change most prominently takes place in the way that Declarative assigns declared columns to the Mapper, specifically how Column (or _orm.mapped_column()) objects are handled when they have a DDL name that is explicitly different from the mapped attribute name, as well as when constructs such as _orm.deferred() etc. are used. The new behavior will see the column ordering within the mapped Table being the same order in which the attributes are mapped onto the class, assigned within the Mapper itself, and rendered in ORM statements such as SELECT statements, independent of how the _schema.Column was configured against the Mapper.

  References: #8705

• [orm] [declarative] [bug] Fixed issue in new dataclass mapping feature where a column declared on the decalrative base / abstract base / mixin would leak into the constructor for an inheriting subclass under some circumstances.

  References: #8718

• [bug] [orm declarative] Fixed issues within the declarative typing resolver (i.e. which resolves ForwardRef objects) where types that were declared for columns in one particular source file would raise NameError when the ultimate mapped class were in another source file. The types are now resolved in terms of the module for each class in which the types are used.

  References: #8742

engine

• [engine] [feature] To better support the use case of iterating Result and AsyncResult objects where user-defined exceptions may interrupt the iteration, both objects as well as variants such as ScalarResult, MappingResult, AsyncScalarResult, AsyncMappingResult now support context manager usage, where the result will be closed at the end of the context manager block.

  In addition, ensured that all the above mentioned Result objects include a Result.close() method as well as Result.closed accessors, including ScalarResult and MappingResult which previously did not have a .close() method.

  References: #8710

• [engine] [usecase] Added new parameter PoolEvents.reset.reset_state parameter to the PoolEvents.reset() event, with deprecation logic in place that will continue to accept event hooks using the previous set of arguments. This indicates various state information about how the reset is taking place and is used to allow custom reset schemes to take place with full context given.

  Within this change a fix that's also backported to 1.4 is included which re-enables the PoolEvents.reset() event to continue to take place under all circumstances, including when Connection has already "reset" the connection.

  The two changes together allow custom reset schemes to be implemented using the PoolEvents.reset() event, instead of the PoolEvents.checkin() event (which continues to function as it always has).

  References: #8717

typing

• [typing] [bug] Corrected various typing issues within the engine and async engine packages.

postgresql

• [postgresql] [feature] Added new methods _postgresql.Range.contains() and _postgresql.Range.contained_by() to the new Range data object, which mirror the behavior of the PostgreSQL @> and <@ operators, as well as the _postgresql.AbstractRange.comparator_factory.contains() and _postgresql.AbstractRange.comparator_factory.contained_by() SQL operator methods. Pull request courtesy Lele Gaifax.

  References: #8706

• [postgresql] [usecase] Refined the new approach to range objects described at change_7156 to accommodate driver-specific range and multirange objects, to better accommodate both legacy code as well as when passing results from raw SQL result sets back into new range or multirange expressions.

  References: #8690

2.0.0b2

Released: October 20, 2022

orm

• [orm] [bug] Removed the warning that emits when using ORM-enabled update/delete regarding evaluation of columns by name, first added in #4073; this warning actually covers up a scenario that otherwise could populate the wrong Python value for an ORM mapped attribute depending on what the actual column is, so this deprecated case is removed. In 2.0, ORM enabled update/delete uses "auto" for "synchronize_session", which should do the right thing automatically for any given UPDATE expression.

  References: #8656

orm declarative

• [orm] [declarative] [usecase] Added support for mapped classes that are also Generic subclasses, to be specified as a GenericAlias object (e.g. MyClass[str]) within statements and calls to _sa.inspect().

  References: #8665

• [orm] [declarative] [bug] Improved the DeclarativeBase class so that when combined with other mixins like MappedAsDataclass, the order of the classes may be in either order.

  References: #8665

• [orm] [declarative] [bug] Fixed bug in new ORM typed declarative mappings where the ability to use Optional[MyClass] or similar forms such as MyClass | None in the type annotation for a many-to-one relationship was not implemented, leading to errors. Documentation has also been added for this use case to the relationship configuration documentation.

  References: #8668

• [orm] [declarative] [bug] Fixed issue with new dataclass mapping feature where arguments passed to the dataclasses API could sometimes be mis-ordered when dealing with mixins that override _orm.mapped_column() declarations, leading to initializer problems.

  References: #8688

sql

• [sql] [bug] [regression] Fixed bug in new "insertmanyvalues" feature where INSERT that included a subquery with _sql.bindparam() inside of it would fail to render correctly in "insertmanyvalues" format. This affected psycopg2 most directly as "insertmanyvalues" is used unconditionally with this driver.

  References: #8639

typing

• [typing] [bug] Fixed typing issue where pylance strict mode would report "instance variable overrides class variable" when using a method to define __tablename__, __mapper_args__ or __table_args__.

  References: #8645

• [typing] [bug] Fixed typing issue where pylance strict mode would report "partially unknown" datatype for the _orm.mapped_column() construct.

  References: #8644

mssql

• [mssql] [bug] Fixed regression caused by SQL Server pyodbc change #8177 where we now use setinputsizes() by default; for VARCHAR, this fails if the character size is greater than 4000 (or 2000, depending on data) characters as the incoming datatype is NVARCHAR, which has a limit of 4000 characters, despite the fact that VARCHAR can handle unlimited characters. Additional pyodbc-specific typing information is now passed to setinputsizes() when the datatype's size is > 2000 characters. The change is also applied to the _types.JSON type which was also impacted by this issue for large JSON serializations.

  References: #8661

• [mssql] [bug] The Sequence construct restores itself to the DDL behavior it had prior to the 1.4 series, where creating a Sequence with no additional arguments will emit a simple CREATE SEQUENCE instruction without any additional parameters for "start value". For most backends, this is how things worked previously in any case; however, for MS SQL Server, the default value on this database is -2**63; to prevent this generally impractical default from taking effect on SQL Server, the Sequence.start parameter should be provided. As usage of Sequence is unusual for SQL Server which for many years has standardized on IDENTITY, it is hoped that this change has minimal impact.

  References: #7211

2.0.0b1

Released: October 13, 2022

general

• [general] [changed] Migrated the codebase to remove all pre-2.0 behaviors and architectures that were previously noted as deprecated for removal in 2.0, including, but not limited to:

  -   removal of all Python 2 code, minimum version is now Python 3.7
  
  -   `_engine.Engine` and `_engine.Connection` now use the
      new 2.0 style of working, which includes "autobegin", library level
      autocommit removed, subtransactions and "branched" connections
      removed
  
  -   Result objects use 2.0-style behaviors; `_result.Row` is fully
      a named tuple without "mapping" behavior, use `_result.RowMapping`
      for "mapping" behavior
  
  -   All Unicode encoding/decoding architecture has been removed from
      SQLAlchemy.  All modern DBAPI implementations support Unicode
      transparently thanks to Python 3, so the `convert_unicode` feature
      as well as related mechanisms to look for bytestrings in
      DBAPI `cursor.description` etc. have been removed.
  
  -   The `.bind` attribute and parameter from `MetaData`,
      `Table`, and from all DDL/DML/DQL elements that previously could
      refer to a "bound engine"
  
  -   The standalone `sqlalchemy.orm.mapper()` function is removed; all
      classical mapping should be done through the
      `_orm.registry.map_imperatively()` method of `_orm.registry`.
  
  -   The `_orm.Query.join()` method no longer accepts strings for
      relationship names; the long-documented approach of using
      `Class.attrname` for join targets is now standard.
  
  -   `_orm.Query.join()` no longer accepts the "aliased" and
      "from_joinpoint" arguments
  
  -   `_orm.Query.join()` no longer accepts chains of multiple join
      targets in one method call.
  
  -   `Query.from_self()`, `Query.select_entity_from()` and
      `Query.with_polymorphic()` are removed.
  
  -   The `_orm.relationship.cascade_backrefs` parameter must now
      remain at its new default of `False`; the `save-update` cascade
      no longer cascades along a backref.
  
  -   the `_orm.Session.future` parameter must always be set to
      `True`.  2.0-style transactional patterns for `_orm.Session`
      are now always in effect.
  
  -   Loader options no longer accept strings for attribute names.  The
      long-documented approach of using `Class.attrname` for loader option
      targets is now standard.
  
  -   Legacy forms of `_sql.select()` removed, including
      `select([cols])`, the "whereclause" and keyword parameters of
      `some_table.select()`.
  
  -   Legacy "in-place mutator" methods on `_sql.Select` such as
      `append_whereclause()`, `append_order_by()` etc are removed.
  
  -   Removed the very old "dbapi_proxy" module, which in very early
      SQLAlchemy releases was used to provide a transparent connection pool
      over a raw DBAPI connection.
  

  References: #7257

• [general] [changed] The _orm.Query.instances() method is deprecated. The behavioral contract of this method, which is that it can iterate objects through arbitrary result sets, is long obsolete and no longer tested. Arbitrary statements can return objects by using constructs such as :meth.Select.from_statement or _orm.aliased().

platform

• [platform] [feature] The SQLAlchemy C extensions have been replaced with all new implementations written in Cython. Like the C extensions before, pre-built wheel files for a wide range of platforms are available on pypi so that building is not an issue for common platforms. For custom builds, python setup.py build_ext works as before, needing only the additional Cython install. pyproject.toml is also part of the source now which will establish the proper build dependencies when using pip.

  References: #7256

• [platform] [change] SQLAlchemy's source build and installation now includes a pyproject.toml file for full PEP 517 support.

  References: #7311

orm

• [orm] [feature] [sql] Added new feature to all included dialects that support RETURNING called "insertmanyvalues". This is a generalization of the "fast executemany" feature first introduced for the psycopg2 driver in 1.4 at change_5263, which allows the ORM to batch INSERT statements into a much more efficient SQL structure while still being able to fetch newly generated primary key and SQL default values using RETURNING.

  The feature now applies to the many dialects that support RETURNING along with multiple VALUES constructs for INSERT, including all PostgreSQL drivers, SQLite, MariaDB, MS SQL Server. Separately, the Oracle dialect also gains the same capability using native cx_Oracle or OracleDB features.

  References: #6047

• [orm] [feature] Added new parameter _orm.AttributeEvents.include_key, which will include the dictionary or list key for operations such as __setitem__() (e.g. obj[key] = value) and __delitem__() (e.g. del obj[key]), using a new keyword parameter "key" or "keys", depending on event, e.g. _orm.AttributeEvents.append.key, _orm.AttributeEvents.bulk_replace.keys. This allows event handlers to take into account the key that was passed to the operation and is of particular importance for dictionary operations working with _orm.MappedCollection.

  References: #8375

• [orm] [feature] Added new parameter _sql.Operators.op.python_impl, available from _sql.Operators.op() and also when using the _sql.Operators.custom_op constructor directly, which allows an in-Python evaluation function to be provided along with the custom SQL operator. This evaluation function becomes the implementation used when the operator object is used given plain Python objects as operands on both sides, and in particular is compatible with the synchronize_session='evaluate' option used with orm_expression_update_delete.

  References: #3162

• [orm] [feature] The _orm.Session (and by extension AsyncSession) now has new state-tracking functionality that will proactively trap any unexpected state changes which occur as a particular transactional method proceeds. This is to allow situations where the _orm.Session is being used in a thread-unsafe manner, where event hooks or similar may be calling unexpected methods within operations, as well as potentially under other concurrency situations such as asyncio or gevent to raise an informative message when the illegal access first occurs, rather than passing silently leading to secondary failures due to the _orm.Session being in an invalid state.

  References: #7433

• [orm] [feature] The _orm.composite() mapping construct now supports automatic resolution of values when used with a Python dataclass; the __composite_values__() method no longer needs to be implemented as this method is derived from inspection of the dataclass.

  Additionally, classes mapped by _orm.composite now support ordering comparison operations, e.g. <, >=, etc.

  See the new documentation at mapper_composite for examples.

• [orm] [feature] Added very experimental feature to the _orm.selectinload() and _orm.immediateload() loader options called _orm.selectinload.recursion_depth / _orm.immediateload.recursion_depth , which allows a single loader option to automatically recurse into self-referential relationships. Is set to an integer indicating depth, and may also be set to -1 to indicate to continue loading until no more levels deep are found. Major internal changes to _orm.selectinload() and _orm.immediateload() allow this feature to work while continuing to make correct use of the compilation cache, as well as not using arbitrary recursion, so any level of depth is supported (though would emit that many queries). This may be useful for self-referential structures that must be loaded fully eagerly, such as when using asyncio.

  A warning is also emitted when loader options are connected together with arbitrary lengths (that is, without using the new recursion_depth option) when excessive recursion depth is detected in related object loading. This operation continues to use huge amounts of memory and performs extremely poorly; the cache is disabled when this condition is detected to protect the cache from being flooded with arbitrary statements.

  References: #8126

• [orm] [feature] Added new parameter _orm.Session.autobegin, which when set to False will prevent the _orm.Session from beginning a transaction implicitly. The _orm.Session.begin() method must be called explicitly first in order to proceed with operations, otherwise an error is raised whenever any operation would otherwise have begun automatically. This option can be used to create a "safe" _orm.Session that won't implicitly start new transactions.

  As part of this change, also added a new status variable _orm.SessionTransaction.origin which may be useful for event handling code to be aware of the origin of a particular _orm.SessionTransaction.

  References: #6928

• [orm] [feature] Declarative mixins which use _schema.Column objects that contain _schema.ForeignKey references no longer need to use _orm.declared_attr() to achieve this mapping; the _schema.ForeignKey object is copied along with the _schema.Column itself when the column is applied to the declared mapping.

• [orm] [usecase] Added _orm.load_only.raiseload parameter to the _orm.load_only() loader option, so that the unloaded attributes may have "raise" behavior rather than lazy loading. Previously there wasn't really a way to do this with the _orm.load_only() option directly.

• [orm] [change] To better accommodate explicit typing, the names of some ORM constructs that are typically constructed internally, but nonetheless are sometimes visible in messaging as well as typing, have been changed to more succinct names which also match the name of their constructing function (with different casing), in all cases maintaining aliases to the old names for the forseeable future:

  -   `_orm.RelationshipProperty` becomes an alias for the primary name
      `_orm.Relationship`, which is constructed as always from the
      `_orm.relationship()` function
  
  -   `_orm.SynonymProperty` becomes an alias for the primary name
      `_orm.Synonym`, constructed as always from the
      `_orm.synonym()` function
  
  -   `_orm.CompositeProperty` becomes an alias for the primary name
      `_orm.Composite`, constructed as always from the
      `_orm.composite()` function
  

• [orm] [change] For consistency with the prominent ORM concept _orm.Mapped, the names of the dictionary-oriented collections, _orm.attribute_mapped_collection(), _orm.column_mapped_collection(), and _orm.MappedCollection, are changed to _orm.attribute_keyed_dict(), _orm.column_keyed_dict() and _orm.KeyFuncDict, using the phrase "dict" to minimize any confusion against the term "mapped". The old names will remain indefinitely with no schedule for removal.

  References: #8608

• [orm] [bug] All _result.Result objects will now consistently raise _exc.ResourceClosedError if they are used after a hard close, which includes the "hard close" that occurs after calling "single row or value" methods like _result.Result.first() and _result.Result.scalar(). This was already the behavior of the most common class of result objects returned for Core statement executions, i.e. those based on _engine.CursorResult, so this behavior is not new. However, the change has been extended to properly accommodate for the ORM "filtering" result objects returned when using 2.0 style ORM queries, which would previously behave in "soft closed" style of returning empty results, or wouldn't actually "soft close" at all and would continue yielding from the underlying cursor.

  As part of this change, also added _result.Result.close() to the base _result.Result class and implemented it for the filtered result implementations that are used by the ORM, so that it is possible to call the _engine.CursorResult.close() method on the underlying _engine.CursorResult when the the yield_per execution option is in use to close a server side cursor before remaining ORM results have been fetched. This was again already available for Core result sets but the change makes it available for 2.0 style ORM results as well.

  This change is also backported to: 1.4.27

  References: #7274

• [orm] [bug] Fixed issue where the _orm.registry.map_declaratively() method would return an internal "mapper config" object and not the Mapper object as stated in the API documentation.

• [orm] [bug] Fixed performance regression which appeared at least in version 1.3 if not earlier (sometime after 1.0) where the loading of deferred columns, those explicitly mapped with _orm.defer() as opposed to non-deferred columns that were expired, from a joined inheritance subclass would not use the "optimized" query which only queried the immediate table that contains the unloaded columns, instead running a full ORM query which would emit a JOIN for all base tables, which is not necessary when only loading columns from the subclass.

  References: #7463

• [orm] [bug] The internals for the _orm.Load object and related loader strategy patterns have been mostly rewritten, to take advantage of the fact that only attribute-bound paths, not strings, are now supported. The rewrite hopes to make it more straightforward to address new use cases and subtle issues within the loader strategy system going forward.

  References: #6986

• [orm] [bug] Made an improvement to the "deferred" / "load_only" set of strategy options where if a certain object is loaded from two different logical paths within one query, attributes that have been configured by at least one of the options to be populated will be populated in all cases, even if other load paths for that same object did not set this option. previously, it was based on randomness as to which "path" addressed the object first.

  References: #8166

• [orm] [bug] Fixed issue in ORM enabled UPDATE when the statement is created against a joined-inheritance subclass, updating only local table columns, where the "fetch" synchronization strategy would not render the correct RETURNING clause for databases that use RETURNING for fetch synchronization. Also adjusts the strategy used for RETURNING in UPDATE FROM and DELETE FROM statements.

  References: #8344

• [orm] [bug] [asyncio] Removed the unused **kw arguments from _asyncio.AsyncSession.begin and _asyncio.AsyncSession.begin_nested. These kw aren't used and appear to have been added to the API in error.

  References: #7703

• [orm] [bug] Changed the attribute access method used by _orm.attribute_mapped_collection() and _orm.column_mapped_collection(), used when populating the dictionary, to assert that the data value on the object to be used as the dictionary key is actually present, and is not instead using "None" due to the attribute never being actually assigned. This is used to prevent a mis-population of None for a key when assigning via a backref where the "key" attribute on the object is not yet assigned.

  As the failure mode here is a transitory condition that is not typically persisted to the database, and is easy to produce via the constructor of the class based on the order in which parameters are assigned, it is very possible that many applications include this behavior already which is silently passed over. To accommodate for applications where this error is now raised, a new parameter _orm.attribute_mapped_collection.ignore_unpopulated_attribute is also added to both _orm.attribute_mapped_collection() and _orm.column_mapped_collection() that instead causes the erroneous backref assignment to be skipped.

  References: #8372

• [orm] [bug] Added new parameter AbstractConcreteBase.strict_attrs to the AbstractConcreteBase declarative mixin class. The effect of this parameter is that the scope of attributes on subclasses is correctly limited to the subclass in which each attribute is declared, rather than the previous behavior where all attributes of the entire hierarchy are applied to the base "abstract" class. This produces a cleaner, more correct mapping where subclasses no longer have non-useful attributes on them which are only relevant to sibling classes. The default for this parameter is False, which leaves the previous behavior unchanged; this is to support existing code that makes explicit use of these attributes in queries. To migrate to the newer approach, apply explicit attributes to the abstract base class as needed.

  References: #8403

• [orm] [bug] The behavior of _orm.defer() regarding primary key and "polymorphic discriminator" columns is revised such that these columns are no longer deferrable, either explicitly or when using a wildcard such as defer('*'). Previously, a wildcard deferral would not load PK/polymorphic columns which led to errors in all cases, as the ORM relies upon these columns to produce object identities. The behavior of explicit deferral of primary key columns is unchanged as these deferrals already were implicitly ignored.

  References: #7495

• [orm] [bug] Fixed bug in the behavior of the _orm.Mapper.eager_defaults parameter such that client-side SQL default or onupdate expressions in the table definition alone will trigger a fetch operation using RETURNING or SELECT when the ORM emits an INSERT or UPDATE for the row. Previously, only server side defaults established as part of table DDL and/or server-side onupdate expressions would trigger this fetch, even though client-side SQL expressions would be included when the fetch was rendered.

  References: #7438

engine

• [engine] [feature] The DialectEvents.handle_error() event is now moved to the DialectEvents suite from the EngineEvents suite, and now participates in the connection pool "pre ping" event for those dialects that make use of disconnect codes in order to detect if the database is live. This allows end-user code to alter the state of "pre ping". Note that this does not include dialects which contain a native "ping" method such as that of psycopg2 or most MySQL dialects.

  References: #5648

• [engine] [feature] The ConnectionEvents.set_connection_execution_options() and ConnectionEvents.set_engine_execution_options() event hooks now allow the given options dictionary to be modified in-place, where the new contents will be received as the ultimate execution options to be acted upon. Previously, in-place modifications to the dictionary were not supported.

• [engine] [usecase] Generalized the _sa.create_engine.isolation_level parameter to the base dialect so that it is no longer dependent on individual dialects to be present. This parameter sets up the "isolation level" setting to occur for all new database connections as soon as they are created by the connection pool, where the value then stays set without being reset on every checkin.

  The _sa.create_engine.isolation_level parameter is essentially equivalent in functionality to using the _engine.Engine.execution_options.isolation_level parameter via _engine.Engine.execution_options() for an engine-wide setting. The difference is in that the former setting assigns the isolation level just once when a connection is created, the latter sets and resets the given level on each connection checkout.

  References: #6342

• [engine] [change] Some small API changes regarding engines and dialects:

  -   The `Dialect.set_isolation_level()`, `Dialect.get_isolation_level()`,
      :meth:
      dialect methods will always be passed the raw DBAPI connection
  
  -   The `Connection` and `Engine` classes no longer share a base
      `Connectable` superclass, which has been removed.
  
  -   Added a new interface class `PoolProxiedConnection` - this is the
      public facing interface for the familiar `_ConnectionFairy`
      class which is nonetheless a private class.
  

  References: #7122

• [engine] [bug] [regression] Fixed regression where the _engine.CursorResult.fetchmany() method would fail to autoclose a server-side cursor (i.e. when stream_results or yield_per is in use, either Core or ORM oriented results) when the results were fully exhausted.

  This change is also backported to: 1.4.27

  References: #7274

• [engine] [bug] Fixed issue in future _engine.Engine where calling upon _engine.Engine.begin() and entering the context manager would not close the connection if the actual BEGIN operation failed for some reason, such as an event handler raising an exception; this use case failed to be tested for the future version of the engine. Note that the "future" context managers which handle begin() blocks in Core and ORM don't actually run the "BEGIN" operation until the context managers are actually entered. This is different from the legacy version which runs the "BEGIN" operation up front.

  This change is also backported to: 1.4.27

  References: #7272

• [engine] [bug] For improved security, the _url.URL object will now use password obfuscation by default when str(url) is called. To stringify a URL with cleartext password, the _url.URL.render_as_string() may be used, passing the _url.URL.render_as_string.hide_password parameter as False. Thanks to our contributors for this pull request.

  References: #8567

• [engine] [bug] The _engine.Inspector.has_table() method will now consistently check for views of the given name as well as tables. Previously this behavior was dialect dependent, with PostgreSQL, MySQL/MariaDB and SQLite supporting it, and Oracle and SQL Server not supporting it. Third party dialects should also seek to ensure their _engine.Inspector.has_table() method searches for views as well as tables for the given name.

  References: #7161

• [engine] [bug] Fixed issue in Result.columns() method where calling upon Result.columns() with a single index could in some cases, particularly ORM result object cases, cause the Result to yield scalar objects rather than Row objects, as though the Result.scalars() method had been called. In SQLAlchemy 1.4, this scenario emits a warning that the behavior will change in SQLAlchemy 2.0.

  References: #7953

• [engine] [bug] Passing a DefaultGenerator object such as a Sequence to the Connection.execute() method is deprecated, as this method is typed as returning a CursorResult object, and not a plain scalar value. The Connection.scalar() method should be used instead, which has been reworked with new internal codepaths to suit invoking a SELECT for default generation objects without going through the Connection.execute() method.

• [engine] [removed] Removed the previously deprecated case_sensitive parameter from _sa.create_engine(), which would impact only the lookup of string column names in Core-only result set rows; it had no effect on the behavior of the ORM. The effective behavior of what case_sensitive refers towards remains at its default value of True, meaning that string names looked up in row._mapping will match case-sensitively, just like any other Python mapping.

  Note that the case_sensitive parameter was not in any way related to the general subject of case sensitivity control, quoting, and "name normalization" (i.e. converting for databases that consider all uppercase words to be case insensitive) for DDL identifier names, which remains a normal core feature of SQLAlchemy.

• [engine] [removed] Removed legacy and deprecated package sqlalchemy.databases. Please use sqlalchemy.dialects instead.

  References: #7258

• [engine] [deprecations] The _sa.create_engine.implicit_returning parameter is deprecated on the _sa.create_engine() function only; the parameter remains available on the _schema.Table object. This parameter was originally intended to enable the "implicit returning" feature of SQLAlchemy when it was first developed and was not enabled by default. Under modern use, there's no reason this parameter should be disabled, and it has been observed to cause confusion as it degrades performance and makes it more difficult for the ORM to retrieve recently inserted server defaults. The parameter remains available on _schema.Table to specifically suit database-level edge cases which make RETURNING infeasible, the sole example currently being SQL Server's limitation that INSERT RETURNING may not be used on a table that has INSERT triggers on it.

  References: #6962

sql

• [sql] [feature] Added long-requested case-insensitive string operators _sql.ColumnOperators.icontains(), _sql.ColumnOperators.istartswith(), _sql.ColumnOperators.iendswith(), which produce case-insensitive LIKE compositions (using ILIKE on PostgreSQL, and the LOWER() function on all other backends) to complement the existing LIKE composition operators _sql.ColumnOperators.contains(), _sql.ColumnOperators.startswith(), etc. Huge thanks to Matias Martinez Rebori for their meticulous and complete efforts in implementing these new methods.

  References: #3482

• [sql] [feature] Added new syntax to the FromClause.c collection on all FromClause objects allowing tuples of keys to be passed to __getitem__(), along with support for the _sql.select() construct to handle the resulting tuple-like collection directly, allowing the syntax select(table.c['a', 'b', 'c']) to be possible. The sub-collection returned is itself a ColumnCollection which is also directly consumable by _sql.select() and similar now.

  References: #8285

• [sql] [usecase] Altered the compilation mechanics of the _dml.Insert construct such that the "autoincrement primary key" column value will be fetched via cursor.lastrowid or RETURNING even if present in the parameter set or within the _dml.Insert.values() method as a plain bound value, for single-row INSERT statements on specific backends that are known to generate autoincrementing values even when explicit NULL is passed. This restores a behavior that was in the 1.3 series for both the use case of separate parameter set as well as _dml.Insert.values(). In 1.4, the parameter set behavior unintentionally changed to no longer do this, but the _dml.Insert.values() method would still fetch autoincrement values up until 1.4.21 where #6770 changed the behavior yet again again unintentionally as this use case was never covered.

  The behavior is now defined as "working" to suit the case where databases such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key value and nonetheless invoke an autoincrement generator.

  References: #7998

• [sql] [usecase] Added new parameter HasCTE.add_cte.nest_here to HasCTE.add_cte() which will "nest" a given CTE at the level of the parent statement. This parameter is equivalent to using the HasCTE.cte.nesting parameter, but may be more intuitive in some scenarios as it allows the nesting attribute to be set simultaneously along with the explicit level of the CTE.

  The HasCTE.add_cte() method also accepts multiple CTE objects.

  References: #7759

• [sql] [bug] The FROM clauses that are established on a _sql.select() construct when using the _sql.Select.select_from() method will now render first in the FROM clause of the rendered SELECT, which serves to maintain the ordering of clauses as was passed to the _sql.Select.select_from() method itself without being affected by the presence of those clauses also being mentioned in other parts of the query. If other elements of the _sql.Select also generate FROM clauses, such as the columns clause or WHERE clause, these will render after the clauses delivered by _sql.Select.select_from() assuming they were not explictly passed to _sql.Select.select_from() also. This improvement is useful in those cases where a particular database generates a desirable query plan based on a particular ordering of FROM clauses and allows full control over the ordering of FROM clauses.

  References: #7888

• [sql] [bug] The Enum.length parameter, which sets the length of the VARCHAR column for non-native enumeration types, is now used unconditionally when emitting DDL for the VARCHAR datatype, including when the Enum.native_enum parameter is set to True for target backends that continue to use VARCHAR. Previously the parameter would be erroneously ignored in this case. The warning previously emitted for this case is now removed.

  References: #7791

• [sql] [bug] The in-place type detection for Python integers, as occurs with an expression such as literal(25), will now apply value-based adaption as well to accommodate Python large integers, where the datatype determined will be BigInteger rather than Integer. This accommodates for dialects such as that of asyncpg which both sends implicit typing information to the driver as well as is sensitive to numeric scale.

  References: #7909

• [sql] [bug] Added if_exists and if_not_exists parameters for all "Create" / "Drop" constructs including CreateSequence, DropSequence, CreateIndex, DropIndex, etc. allowing generic "IF EXISTS" / "IF NOT EXISTS" phrases to be rendered within DDL. Pull request courtesy Jesse Bakker.

  References: #7354

• [sql] [bug] Improved the construction of SQL binary expressions to allow for very long expressions against the same associative operator without special steps needed in order to avoid high memory use and excess recursion depth. A particular binary operation A op B can now be joined against another element op C and the resulting structure will be "flattened" so that the representation as well as SQL compilation does not require recursion.

  One effect of this change is that string concatenation expressions which use SQL functions come out as "flat", e.g. MySQL will now render concat('x', 'y', 'z', ...)`` rather than nesting together two-element functions like concat(concat('x', 'y'), 'z'). Third-party dialects which override the string concatenation operator will need to implement a new method def visit_concat_op_expression_clauselist()to accompany the existingdef visit_concat_op_binary()` method.

  References: #7744

• [sql] [bug] Implemented full support for "truediv" and "floordiv" using the "/" and "//" operators. A "truediv" operation between two expressions using _types.Integer now considers the result to be _types.Numeric, and the dialect-level compilation will cast the right operand to a numeric type on a dialect-specific basis to ensure truediv is achieved. For floordiv, conversion is also added for those databases that don't already do floordiv by default (MySQL, Oracle) and the FLOOR() function is rendered in this case, as well as for cases where the right operand is not an integer (needed for PostgreSQL, others).

  The change resolves issues both with inconsistent behavior of the division operator on different backends and also fixes an issue where integer division on Oracle would fail to be able to fetch a result due to inappropriate outputtypehandlers.

  References: #4926

• [sql] [bug] Added an additional lookup step to the compiler which will track all FROM clauses which are tables, that may have the same name shared in multiple schemas where one of the schemas is the implicit "default" schema; in this case, the table name when referring to that name without a schema qualification will be rendered with an anonymous alias name at the compiler level in order to disambiguate the two (or more) names. The approach of schema-qualifying the normally unqualified name with the server-detected "default schema name" value was also considered, however this approach doesn't apply to Oracle nor is it accepted by SQL Server, nor would it work with multiple entries in the PostgreSQL search path. The name collision issue resolved here has been identified as affecting at least Oracle, PostgreSQL, SQL Server, MySQL and MariaDB.

  References: #7471

• [sql] [bug] The _functions.array_agg will now set the array dimensions to 1. Improved _types.ARRAY processing to accept None values as value of a multi-array.

  References: #7083

schema

• [schema] [feature] Expanded on the "conditional DDL" system implemented by the _schema.ExecutableDDLElement class (renamed from _schema.DDLElement) to be directly available on _schema.SchemaItem constructs such as _schema.Index, _schema.ForeignKeyConstraint, etc. such that the conditional logic for generating these elements is included within the default DDL emitting process. This system can also be accommodated by a future release of Alembic to support conditional DDL elements within all schema-management systems.

  References: #7631

• [schema] [usecase] Added parameter _ddl.DropConstraint.if_exists to the _ddl.DropConstraint construct which result in "IF EXISTS" DDL being added to the DROP statement. This phrase is 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 Mike Fiedler.

  References: #8141

• [schema] [usecase] Implemented the DDL event hooks DDLEvents.before_create(), DDLEvents.after_create(), DDLEvents.before_drop(), DDLEvents.after_drop() for all SchemaItem objects that include a distinct CREATE or DROP step, when that step is invoked as a distinct SQL statement, including for ForeignKeyConstraint, Sequence, Index, and PostgreSQL's _postgresql.ENUM.

  References: #8394

• [schema] [performance] Rearchitected the schema reflection API to allow participating dialects to make use of high performing batch queries to reflect the schemas of many tables at once using fewer queries by an order of magnitude. The new performance features are targeted first at the PostgreSQL and Oracle backends, and may be applied to any dialect that makes use of SELECT queries against system catalog tables to reflect tables. The change also includes new API features and behavioral improvements to the Inspector object, including consistent, cached behavior of methods like Inspector.has_table(), Inspector.get_table_names() and new methods Inspector.has_schema() and Inspector.has_index().

  References: #4379

• [schema] [bug] The warnings that are emitted regarding reflection of indexes or unique constraints, when the Table.include_columns parameter is used to exclude columns that are then found to be part of those constraints, have been removed. When the Table.include_columns parameter is used it should be expected that the resulting Table construct will not include constraints that rely upon omitted columns. This change was made in response to #8100 which repaired Table.include_columns in conjunction with foreign key constraints that rely upon omitted columns, where the use case became clear that omitting such constraints should be expected.

  References: #8102

• [schema] [postgresql] Added support for comments on Constraint objects, including DDL and reflection; the field is added to the base Constraint class and corresponding constructors, however PostgreSQL is the only included backend to support the feature right now. See parameters such as ForeignKeyConstraint.comment, UniqueConstraint.comment or CheckConstraint.comment.

  References: #5677

• [schema] [mariadb] [mysql] Add support for Partitioning and Sample pages on MySQL and MariaDB reflected options. The options are stored in the table dialect options dictionary, so the following keyword need to be prefixed with mysql_ or mariadb_ depending on the backend. Supported options are:

  -   `stats_sample_pages`
  
  -   `partition_by`
  
  -   `partitions`
  
  -   `subpartition_by`
  

  These options are also reflected when loading a table from database, and will populate the table _schema.Table.dialect_options. Pull request courtesy of Ramon Will.

  References: #4038

typing

• [typing] [improvement] The _sqltypes.TypeEngine.with_variant() method now returns a copy of the original _sqltypes.TypeEngine object, rather than wrapping it inside the Variant class, which is effectively removed (the import symbol remains for backwards compatibility with code that may be testing for this symbol). While the previous approach maintained in-Python behaviors, maintaining the original type allows for clearer type checking and debugging.

  _sqltypes.TypeEngine.with_variant() also accepts multiple dialect names per call as well, in particular this is helpful for related backend names such as "mysql", "mariadb".

  References: #6980

postgresql

• [postgresql] [feature] Added a new PostgreSQL _postgresql.DOMAIN datatype, which follows the same CREATE TYPE / DROP TYPE behaviors as that of PostgreSQL _postgresql.ENUM. Much thanks to David Baumgold for the efforts on this.

  References: #7316

• [postgresql] [usecase] [asyncpg] Added overridable methods PGDialect_asyncpg.setup_asyncpg_json_codec and PGDialect_asyncpg.setup_asyncpg_jsonb_codec codec, which handle the required task of registering JSON/JSONB codecs for these datatypes when using asyncpg. The change is that methods are broken out as individual, overridable methods to support third party dialects that need to alter or disable how these particular codecs are set up.

  This change is also backported to: 1.4.27

  References: #7284

• [postgresql] [usecase] Added literal type rendering for the _sqltypes.ARRAY and _postgresql.ARRAY datatypes. The generic stringify will render using brackets, e.g. [1, 2, 3] and the PostgreSQL specific will use the ARRAY literal e.g. ARRAY[1, 2, 3]. Multiple dimensions and quoting are also taken into account.

  References: #8138

• [postgresql] [usecase] Adds support for PostgreSQL multirange types, introduced in PostgreSQL 14. Support for PostgreSQL ranges and multiranges has now been generalized to the psycopg3, psycopg2 and asyncpg backends, with room for further dialect support, using a backend-agnostic _postgresql.Range data object that's constructor-compatible with the previously used psycopg2 object. See the new documentation for usage patterns.

  In addition, range type handling has been enhanced so that it automatically renders type casts, so that in-place round trips for statements that don't provide the database with any context don't require the _sql.cast() construct to be explicit for the database to know the desired type (discussed at #8540).

  Thanks very much to @zeeeeeb for the pull request implementing and testing the new datatypes and psycopg support.

  References: #7156, #8540

• [postgresql] [usecase] The "ping" query emitted when configuring _sa.create_engine.pool_pre_ping for psycopg, asyncpg and pg8000, but not for psycopg2, has been changed to be an empty query (;) instead of SELECT 1; additionally, for the asyncpg driver, the unnecessary use of a prepared statement for this query has been fixed. Rationale is to eliminate the need for PostgreSQL to produce a query plan when the ping is emitted. The operation is not currently supported by the psycopg2 driver which continues to use SELECT 1.

  References: #8491

• [postgresql] [change] SQLAlchemy now requires PostgreSQL version 9 or greater. Older versions may still work in some limited use cases.

• [postgresql] [change] [mssql] The parameter _types.UUID.as_uuid of _types.UUID, previously specific to the PostgreSQL dialect but now generalized for Core (along with a new backend-agnostic _types.Uuid datatype) now defaults to True, indicating that Python UUID objects are accepted by this datatype by default. Additionally, the SQL Server _mssql.UNIQUEIDENTIFIER datatype has been converted to be a UUID-receiving type; for legacy code that makes use of _mssql.UNIQUEIDENTIFIER using string values, set the _mssql.UNIQUEIDENTIFIER.as_uuid parameter to False.

  References: #7225

• [postgresql] [change] The _postgresql.ENUM.name parameter for the PostgreSQL-specific _postgresql.ENUM datatype is now a required keyword argument. The "name" is necessary in any case in order for the _postgresql.ENUM to be usable as an error would be raised at SQL/DDL render time if "name" were not present.

• [postgresql] [change] In support of new PostgreSQL features including the psycopg3 dialect as well as extended "fast insertmany" support, the system by which typing information for bound parameters is passed to the PostgreSQL database has been redesigned to use inline casts emitted by the SQL compiler, and is now applied to all PostgreSQL dialects. This is in contrast to the previous approach which would rely upon the DBAPI in use to render these casts itself, which in cases such as that of pg8000 and the adapted asyncpg driver, would use the pep-249 setinputsizes() method, or with the psycopg2 driver would rely on the driver itself in most cases, with some special exceptions made for ARRAY.

  The new approach now has all PostgreSQL dialects rendering these casts as needed using PostgreSQL double-colon style within the compiler, and the use of setinputsizes() is removed for PostgreSQL dialects, as this was not generally part of these DBAPIs in any case (pg8000 being the only exception, which added the method at the request of SQLAlchemy developers).

  Advantages to this approach include per-statement performance, as no second pass over the compiled statement is required at execution time, better support for all DBAPIs, as there is now one consistent system of applying typing information, and improved transparency, as the SQL logging output, as well as the string output of a compiled statement, will show these casts present in the statement directly, whereas previously these casts were not visible in logging output as they would occur after the statement were logged.

• [postgresql] [bug] The Operators.match() operator now uses plainto_tsquery() for PostgreSQL full text search, rather than to_tsquery(). The rationale for this change is to provide better cross-compatibility with match on other database backends. Full support for all PostgreSQL full text functions remains available through the use of :data:.func in conjunction with Operators.bool_op() (an improved version of Operators.op() for boolean operators).

  Unknown interpreted text role "data".

  References: #7086

• [postgresql] [removed] Removed support for multiple deprecated drivers:

  -   pypostgresql for PostgreSQL. This is available as an
      external driver at [https://github.com/PyGreSQL](https://github.com/PyGreSQL)
  
  -   pygresql for PostgreSQL.
  

  Please switch to one of the supported drivers or to the external version of the same driver.

  References: #7258

• [postgresql] [dialect] Added support for psycopg dialect supporting both sync and async execution. This dialect is available under the postgresql+psycopg name for both the _sa.create_engine() and _asyncio.create_async_engine() engine-creation functions.

  References: #6842

• [postgresql] [psycopg2] Update psycopg2 dialect to use the DBAPI interface to execute two phase transactions. Previously SQL commands were execute to handle this kind of transactions.

  References: #7238

• [postgresql] [schema] Introduced the type _postgresql.JSONPATH that can be used in cast expressions. This is required by some PostgreSQL dialects when using functions such as jsonb_path_exists or jsonb_path_match that accept a jsonpath as input.

  References: #8216

• [postgresql] [reflection] The PostgreSQL dialect now supports reflection of expression based indexes. The reflection is supported both when using _engine.Inspector.get_indexes() and when reflecting a _schema.Table using _schema.Table.autoload_with. Thanks to immerrr and Aidan Kane for the help on this ticket.

  References: #7442

mysql

• [mysql] [usecase] [mariadb] The ROLLUP function will now correctly render WITH ROLLUP on MySql and MariaDB, allowing the use of group by rollup with these backend.

  References: #8503

• [mysql] [bug] Fixed issue in MySQL _mysql.Insert.on_duplicate_key_update() which would render the wrong column name when an expression were used in a VALUES expression. Pull request courtesy Cristian Sabaila.

  This change is also backported to: 1.4.27

  References: #7281

• [mysql] [removed] Removed support for the OurSQL driver for MySQL and MariaDB, as this driver does not seem to be maintained.

  References: #7258

mariadb

• [mariadb] [usecase] Added a new execution option is_delete_using=True, which is consumed by the ORM when using an ORM-enabled DELETE statement in conjunction with the "fetch" synchronization strategy; this option indicates that the DELETE statement is expected to use multiple tables, which on MariaDB is the DELETE..USING syntax. The option then indicates that RETURNING (newly implemented in SQLAlchemy 2.0 for MariaDB for #7011) should not be used for databases that are known to not support "DELETE..USING..RETURNING" syntax, even though they support "DELETE..USING", which is MariaDB's current capability.

  The rationale for this option is that the current workings of ORM-enabled DELETE doesn't know up front if a DELETE statement is against multiple tables or not until compilation occurs, which is cached in any case, yet it needs to be known so that a SELECT for the to-be-deleted row can be emitted up front. Instead of applying an across-the-board performance penalty for all DELETE statements by proactively checking them all for this relatively unusual SQL pattern, the is_delete_using=True execution option is requested via a new exception message that is raised within the compilation step. This exception message is specifically (and only) raised when: the statement is an ORM-enabled DELETE where the "fetch" synchronization strategy has been requested; the backend is MariaDB or other backend with this specific limitation; the statement has been detected within the initial compilation that it would otherwise emit "DELETE..USING..RETURNING". By applying the execution option, the ORM knows to run a SELECT upfront instead. A similar option is implemented for ORM-enabled UPDATE but there is not currently a backend where it is needed.

  References: #8344

• [mariadb] [usecase] Added INSERT..RETURNING and DELETE..RETURNING support for the MariaDB dialect. UPDATE..RETURNING is not yet supported by MariaDB. MariaDB supports INSERT..RETURNING as of 10.5.0 and DELETE..RETURNING as of 10.0.5.

  References: #7011

sqlite

• [sqlite] [usecase] Added new parameter to SQLite for reflection methods called sqlite_include_internal=True; when omitted, local tables that start with the prefix sqlite_, which per SQLite documentation are noted as "internal schema" tables such as the sqlite_sequence table generated to support "AUTOINCREMENT" columns, will not be included in reflection methods that return lists of local objects. This prevents issues for example when using Alembic autogenerate, which previously would consider these SQLite-generated tables as being remove from the model.

  References: #8234

• [sqlite] [usecase] Added RETURNING support for the SQLite dialect. SQLite supports RETURNING since version 3.35.

  References: #6195

• [sqlite] [usecase] The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements that may refer to additional tables within the WHERE criteria of the statement without the need to use subqueries. This syntax is invoked automatically when using the _dml.Update construct when more than one table or other entity or selectable is used.

  References: #7185

• [sqlite] [performance] [usecase] SQLite datetime, date, and time datatypes now use Python standard lib fromisoformat() methods in order to parse incoming datetime, date, and time string values. This improves performance vs. the previous regular expression-based approach, and also automatically accommodates for datetime and time formats that contain either a six-digit "microseconds" format or a three-digit "milliseconds" format.

  References: #7029

• [sqlite] [bug] Removed the warning that emits from the _types.Numeric type about DBAPIs not supporting Decimal values natively. This warning was oriented towards SQLite, which does not have any real way without additional extensions or workarounds of handling precision numeric values more than 15 significant digits as it only uses floating point math to represent numbers. As this is a known and documented limitation in SQLite itself, and not a quirk of the pysqlite driver, there's no need for SQLAlchemy to warn for this. The change does not otherwise modify how precision numerics are handled. Values can continue to be handled as Decimal() or float() as configured with the _types.Numeric, _types.Float , and related datatypes, just without the ability to maintain precision beyond 15 significant digits when using SQLite, unless alternate representations such as strings are used.

  References: #7299

• [sqlite] [bug] [performance] The SQLite dialect now defaults to _pool.QueuePool when a file based database is used. This is set along with setting the check_same_thread parameter to False. It has been observed that the previous approach of defaulting to _pool.NullPool, which does not hold onto database connections after they are released, did in fact have a measurable negative performance impact. As always, the pool class is customizable via the _sa.create_engine.poolclass parameter.

  References: #7490

mssql

• [mssql] [usecase] Implemented reflection of the "clustered index" flag mssql_clustered for the SQL Server dialect. Pull request courtesy John Lennox.

  References: #8288

• [mssql] [usecase] Added support table and column comments on MSSQL when creating a table. Added support for reflecting table comments. Thanks to Daniel Hall for the help in this pull request.

  References: #7844

• [mssql] [bug] The use_setinputsizes parameter for the mssql+pyodbc dialect now defaults to True; this is so that non-unicode string comparisons are bound by pyodbc to pyodbc.SQL_VARCHAR rather than pyodbc.SQL_WVARCHAR, allowing indexes against VARCHAR columns to take effect. In order for the fast_executemany=True parameter to continue functioning, the use_setinputsizes mode now skips the cursor.setinputsizes() call specifically when fast_executemany is True and the specific method in use is cursor.executemany(), which doesn't support setinputsizes. The change also adds appropriate pyodbc DBAPI typing to values that are typed as _types.Unicode or _types.UnicodeText, as well as altered the base _types.JSON datatype to consider JSON string values as _types.Unicode rather than _types.String.

  References: #8177

• [mssql] [removed] Removed support for the mxodbc driver due to lack of testing support. ODBC users may use the pyodbc dialect which is fully supported.

  References: #7258

oracle

• [oracle] [feature] Add support for the new oracle driver oracledb.

  References: #8054

• [oracle] [feature] Implemented DDL and reflection support for FLOAT datatypes which include an explicit "binary_precision" value. Using the Oracle-specific _oracle.FLOAT datatype, the new parameter _oracle.FLOAT.binary_precision may be specified which will render Oracle's precision for floating point types directly. This value is interpreted during reflection. Upon reflecting back a FLOAT datatype, the datatype returned is one of _types.DOUBLE_PRECISION for a FLOAT for a precision of 126 (this is also Oracle's default precision for FLOAT), _types.REAL for a precision of 63, and _oracle.FLOAT for a custom precision, as per Oracle documentation.

  As part of this change, the generic _sqltypes.Float.precision value is explicitly rejected when generating DDL for Oracle, as this precision cannot be accurately converted to "binary precision"; instead, an error message encourages the use of _sqltypes.TypeEngine.with_variant() so that Oracle's specific form of precision may be chosen exactly. This is a backwards-incompatible change in behavior, as the previous "precision" value was silently ignored for Oracle.

  References: #5465

• [oracle] [feature] Full "RETURNING" support is implemented for the cx_Oracle dialect, covering two individual types of functionality:

  -   multi-row RETURNING is implemented, meaning multiple RETURNING rows are
      now received for DML statements that produce more than one row for
      RETURNING.
  
  -   "executemany RETURNING" is also implemented - this allows RETURNING to
      yield row-per statement when `cursor.executemany()` is used.
      The implementation of this part of the feature delivers dramatic
      performance improvements to ORM inserts, in the same way as was
      added for psycopg2 in the SQLAlchemy 1.4 change `change_5263`.
  

  References: #6245

• [oracle] [usecase] Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset support by default for Oracle 12c and above. This syntax was already available when _sql.Select.fetch() were used directly, it's now implied for _sql.Select.limit() and _sql.Select.offset() as well.

  References: #8221

• [oracle] [change] Materialized views on oracle are now reflected as views. On previous versions of SQLAlchemy the views were returned among the table names, not among the view names. As a side effect of this change they are not reflected by default by _sql.MetaData.reflect(), unless views=True is set. To get a list of materialized views, use the new inspection method Inspector.get_materialized_view_names().

• [oracle] [bug] Adjustments made to the BLOB / CLOB / NCLOB datatypes in the cx_Oracle and oracledb dialects, to improve performance based on recommendations from Oracle developers.

  References: #7494

• [oracle] [bug] Related to the deprecation for _sa.create_engine.implicit_returning, the "implicit_returning" feature is now enabled for the Oracle dialect in all cases; previously, the feature would be turned off when an Oracle 8/8i version were detected, however online documentation indicates both versions support the same RETURNING syntax as modern versions.

  References: #6962

• [oracle] cx_Oracle 7 is now the minimum version for cx_Oracle.

misc

• [feature] [types] Added new backend-agnostic _types.Uuid datatype generalized from the PostgreSQL dialects to now be a core type, as well as migrated _types.UUID from the PostgreSQL dialect. The SQL Server _mssql.UNIQUEIDENTIFIER datatype also becomes a UUID-handling datatype. Thanks to Trevor Gross for the help on this.

  References: #7212

• [feature] [types] Added Double, DOUBLE, DOUBLE_PRECISION datatypes to the base sqlalchemy. module namespace, for explicit use of double/double precision as well as generic "double" datatypes. Use Double for generic support that will resolve to DOUBLE/DOUBLE PRECISION/FLOAT as needed for different backends.

  References: #5465

• [usecase] [datatypes] Added modified ISO-8601 rendering (i.e. ISO-8601 with the T converted to a space) when using literal_binds with the SQL compilers provided by the PostgreSQL, MySQL, MariaDB, MSSQL, Oracle dialects. For Oracle, the ISO format is wrapped inside of an appropriate TO_DATE() function call. Previously this rendering was not implemented for dialect-specific compilation.

  References: #5052

• [bug] [pool] The _pool.QueuePool now ignores max_overflow when pool_size=0, properly making the pool unlimited in all cases.

  References: #8523

• [bug] [types] Python string values for which a SQL type is determined from the type of the value, mainly when using _sql.literal(), will now apply the _types.String type, rather than the _types.Unicode datatype, for Python string values that test as "ascii only" using Python str.isascii(). If the string is not isascii(), the _types.Unicode datatype will be bound instead, which was used in all string detection previously. This behavior only applies to in-place detection of datatypes when using literal() or other contexts that have no existing datatype, which is not usually the case under normal _schema.Column comparison operations, where the type of the _schema.Column being compared always takes precedence.

  Use of the _types.Unicode datatype can determine literal string formatting on backends such as SQL Server, where a literal value (i.e. using literal_binds) will be rendered as N'<value>' instead of 'value'. For normal bound value handling, the _types.Unicode datatype also may have implications for passing values to the DBAPI, again in the case of SQL Server, the pyodbc driver supports the use of setinputsizes mode <mssql_pyodbc_setinputsizes> which will handle _types.String versus _types.Unicode differently.

  References: #7551

• [removed] [sybase] Removed the "sybase" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.

  References: #7258

• [removed] [firebird] Removed the "firebird" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.

  References: #7258

1.4.54

Released: September 5, 2024

general

• [general] [change] The pin for setuptools<69.3 in pyproject.toml has been removed. This pin was to prevent a sudden change in setuptools to use PEP 625 from taking place, which would change the file name of SQLAlchemy's source distribution on pypi to be an all lower case name, which is likely to cause problems with various build environments that expected the previous naming style. However, the presence of this pin is holding back environments that otherwise want to use a newer setuptools, so we've decided to move forward with this change, with the assumption that build environments will have largely accommodated the setuptools change by now.

  This change was first released in version 2.0.33 however is being backported to 1.4.54 to support ongoing releases.

  References: #11818

• [general] [change] The setuptools "test" command is removed from the 1.4 series as modern versions of setuptools actively refuse to accommodate this extension being present. This change was already part of the 2.0 series. To run the test suite use the tox command.

orm

• [orm] [bug] [regression] Fixed regression from 1.3 where the column key used for a hybrid property might be populated with that of the underlying column that it returns, for a property that returns an ORM mapped column directly, rather than the key used by the hybrid property itself.

  References: #11728

postgresql

• [postgresql] [bug] Fixed critical issue in the asyncpg driver where a rollback or commit that fails specifically for the MissingGreenlet condition or any other error that is not raised by asyncpg itself would discard the asyncpg transaction in any case, even though the transaction were still idle, leaving to a server side condition with an idle transaction that then goes back into the connection pool. The flags for "transaction closed" are now not reset for errors that are raised outside of asyncpg itself. When asyncpg itself raises an error for .commit() or .rollback(), asyncpg does then discard of this transaction.

  References: #11819

1.4.53

Released: July 29, 2024

general

• [general] [bug] Set up full Python 3.13 support to the extent currently possible, repairing issues within internal language helpers as well as the serializer extension module.

  For version 1.4, this also modernizes the "extras" names in setup.cfg to use dashes and not underscores for two-word names. Underscore names are still present to accommodate potential compatibility issues.

  References: #11417

orm

• [orm] [bug] [regression] Fixed regression going back to 1.4 where accessing a collection using the "dynamic" strategy on a transient object and attempting to query would raise an internal error rather than the expected NoResultFound that occurred in 1.3.

  References: #11562

engine

• [engine] [usecase] Modified the internal representation used for adapting asyncio calls to greenlets to allow for duck-typed compatibility with third party libraries that implement SQLAlchemy's "greenlet-to-asyncio" pattern directly. Running code within a greenlet that features the attribute __sqlalchemy_greenlet_provider__ = True will allow calls to sqlalchemy.util.await_only() directly.

• [engine] [bug] Adjustments to the C extensions, which are specific to the SQLAlchemy 1.x series, to work under Python 3.13. Pull request courtesy Ben Beasley.

  References: #11499

sql

• [sql] [bug] Fixed caching issue where using the TextualSelect.add_cte() method of the TextualSelect construct would not set a correct cache key which distinguished between different CTE expressions.

  References: #11471

• [sql] [bug] Fixed caching issue where the _sql.Select.with_for_update.key_share element of _sql.Select.with_for_update() was not considered as part of the cache key, leading to incorrect caching if different variations of this parameter were used with an otherwise identical statement.

  References: #11544

mypy

• [mypy] [bug] The deprecated mypy plugin is no longer fully functional with the latest series of mypy 1.11.0, as changes in the mypy interpreter are no longer compatible with the approach used by the plugin. If code is dependent on the mypy plugin with sqlalchemy2-stubs, it's recommended to pin mypy to be below the 1.11.0 series. Seek upgrading to the 2.0 series of SQLAlchemy and migrating to the modern type annotations.

sqlite

• [sqlite] [bug] [reflection] Fixed reflection of computed column in SQLite to properly account for complex expressions.

  References: #11582

mssql

• [mssql] [bug] Fixed issue where SQL Server drivers don't support bound parameters when rendering the "frame specification" for a window function, e.g. "ROWS BETWEEN", etc.

  References: #11514

1.4.52

Released: March 4, 2024

orm

• [orm] [bug] Fixed bug where ORM _orm.with_loader_criteria() would not apply itself to a _sql.Select.join() where the ON clause were given as a plain SQL comparison, rather than as a relationship target or similar.

  This is a backport of the same issue fixed in version 2.0 for 2.0.22.

  References: #10365

1.4.51

Released: January 2, 2024

orm

• [orm] [bug] Improved a fix first implemented for #3208 released in version 0.9.8, where the registry of classes used internally by declarative could be subject to a race condition in the case where individual mapped classes are being garbage collected at the same time while new mapped classes are being constructed, as can happen in some test suite configurations or dynamic class creation environments. In addition to the weakref check already added, the list of items being iterated is also copied first to avoid "list changed while iterating" errors. Pull request courtesy Yilei Yang.

  References: #10782

asyncio

• [asyncio] [bug] Fixed critical issue in asyncio version of the connection pool where calling _asyncio.AsyncEngine.dispose() would produce a new connection pool that did not fully re-establish the use of asyncio-compatible mutexes, leading to the use of a plain threading.Lock() which would then cause deadlocks in an asyncio context when using concurrency features like asyncio.gather().

  References: #10813

mysql

• [mysql] [bug] Fixed regression introduced by the fix in ticket #10492 when using pool pre-ping with PyMySQL version older than 1.0.

  References: #10650

1.4.50

Released: October 29, 2023

orm

• [orm] [bug] Fixed fundamental issue which prevented some forms of ORM "annotations" from taking place for subqueries which made use of _sql.Select.join() against a relationship target. These annotations are used whenever a subquery is used in special situations such as within _orm.PropComparator.and_() and other ORM-specific scenarios.

  References: #10223

sql

• [sql] [bug] Fixed issue where using the same bound parameter more than once with literal_execute=True in some combinations with other literal rendering parameters would cause the wrong values to render due to an iteration issue.

  References: #10142

• [sql] [bug] Fixed issue where unpickling of a _schema.Column or other _sql.ColumnElement would fail to restore the correct "comparator" object, which is used to generate SQL expressions specific to the type object.

  References: #10213

schema

• [schema] [bug] Modified the rendering of the Oracle only Identity.order parameter that's part of both Sequence and Identity to only take place for the Oracle backend, and not other backends such as that of PostgreSQL. A future release will rename the Identity.order, Sequence.order and Identity.on_null parameters to Oracle-specific names, deprecating the old names, these parameters only apply to Oracle.

  References: #10207

mysql

• [mysql] [usecase] Updated aiomysql dialect since the dialect appears to be maintained again. Re-added to the ci testing using version 0.2.0.

• [mysql] [bug] Repaired a new incompatibility in the MySQL "pre-ping" routine where the False argument passed to connection.ping(), which is intended to disable an unwanted "automatic reconnect" feature, is being deprecated in MySQL drivers and backends, and is producing warnings for some versions of MySQL's native client drivers. It's removed for mysqlclient, whereas for PyMySQL and drivers based on PyMySQL, the parameter will be deprecated and removed at some point, so API introspection is used to future proof against these various stages of removal.

  References: #10492

mssql

• [mssql] [bug] [reflection] Fixed issue where identity column reflection would fail for a bigint column with a large identity start value (more than 18 digits).

  References: #10504

1.4.49

Released: July 5, 2023

platform

• [platform] [usecase] Compatibility improvements to work fully with Python 3.12

sql

• [sql] [bug] Fixed issue where the _sql.ColumnOperators.regexp_match() when using "flags" would not produce a "stable" cache key, that is, the cache key would keep changing each time causing cache pollution. The same issue existed for _sql.ColumnOperators.regexp_replace() with both the flags and the actual replacement expression. The flags are now represented as fixed modifier strings rendered as safestrings rather than bound parameters, and the replacement expression is established within the primary portion of the "binary" element so that it generates an appropriate cache key.

  Note that as part of this change, the _sql.ColumnOperators.regexp_match.flags and _sql.ColumnOperators.regexp_replace.flags have been modified to render as literal strings only, whereas previously they were rendered as full SQL expressions, typically bound parameters. These parameters should always be passed as plain Python strings and not as SQL expression constructs; it's not expected that SQL expression constructs were used in practice for this parameter, so this is a backwards-incompatible change.

  The change also modifies the internal structure of the expression generated, for _sql.ColumnOperators.regexp_replace() with or without flags, and for _sql.ColumnOperators.regexp_match() with flags. Third party dialects which may have implemented regexp implementations of their own (no such dialects could be located in a search, so impact is expected to be low) would need to adjust the traversal of the structure to accommodate.

  References: #10042

• [sql] [bug] Fixed issue in mostly-internal CacheKey construct where the __ne__() operator were not properly implemented, leading to nonsensical results when comparing CacheKey instances to each other.

extensions

• [extensions] [bug] Fixed issue in mypy plugin for use with mypy 1.4.

1.4.48

Released: April 30, 2023

orm

• [orm] [bug] Fixed critical caching issue where the combination of _orm.aliased() and _hybrid.hybrid_property() expression compositions would cause a cache key mismatch, leading to cache keys that held onto the actual _orm.aliased() object while also not matching that of equivalent constructs, filling up the cache.

  References: #9728

• [orm] [bug] Fixed bug where various ORM-specific getters such as ORMExecuteState.is_column_load, ORMExecuteState.is_relationship_load, ORMExecuteState.loader_strategy_path etc. would throw an AttributeError if the SQL statement itself were a "compound select" such as a UNION.

  References: #9634

• [orm] [bug] Fixed endless loop which could occur when using "relationship to aliased class" feature and also indicating a recursive eager loader such as lazy="selectinload" in the loader, in combination with another eager loader on the opposite side. The check for cycles has been fixed to include aliased class relationships.

  References: #9590

1.4.47

Released: March 18, 2023

sql

• [sql] [bug] Fixed bug / regression where using bindparam() with the same name as a column in the Update.values() method of Update, as well as the Insert.values() method of Insert in 2.0 only, would in some cases silently fail to honor the SQL expression in which the parameter were presented, replacing the expression with a new parameter of the same name and discarding any other elements of the SQL expression, such as SQL functions, etc. The specific case would be statements that were constructed against ORM entities rather than plain Table instances, but would occur if the statement were invoked with a Session or a Connection.

  Update part of the issue was present in both 2.0 and 1.4 and is backported to 1.4.

  References: #9075

• [sql] [bug] Fixed stringify for a the CreateSchema and DropSchema DDL constructs, which would fail with an AttributeError when stringified without a dialect.

  References: #7664

• [sql] [bug] Fixed critical SQL caching issue where use of the _sql.Operators.op() custom operator function would not produce an appropriate cache key, leading to reduce the effectiveness of the SQL cache.

  References: #9506

mypy

• [mypy] [bug] Adjustments made to the mypy plugin to accommodate for some potential changes being made for issue #236 sqlalchemy2-stubs when using SQLAlchemy 1.4. These changes are being kept in sync within SQLAlchemy 2.0. The changes are also backwards compatible with older versions of sqlalchemy2-stubs.

• [mypy] [bug] Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions if a decorator for the _orm.registry.mapped() decorator were used that was referenced in an expression with more than two components (e.g. @Backend.mapper_registry.mapped). This scenario is now ignored; when using the plugin, the decorator expression needs to be two components (i.e. @reg.mapped).

  References: #9102

postgresql

• [postgresql] [bug] Added support to the asyncpg dialect to return the cursor.rowcount value for SELECT statements when available. While this is not a typical use for cursor.rowcount, the other PostgreSQL dialects generally provide this value. Pull request courtesy Michael Gorven.

  References: #9048

mysql

• [mysql] [usecase] Added support to MySQL index reflection to correctly reflect the mysql_length dictionary, which previously was being ignored.

  References: #9047

mssql

• [mssql] [bug] Fixed bug where a schema name given with brackets, but no dots inside the name, for parameters such as _schema.Table.schema would not be interpreted within the context of the SQL Server dialect's documented behavior of interpreting explicit brackets as token delimiters, first added in 1.2 for #2626, when referring to the schema name in reflection operations. The original assumption for #2626's behavior was that the special interpretation of brackets was only significant if dots were present, however in practice, the brackets are not included as part of the identifier name for all SQL rendering operations since these are not valid characters within regular or delimited identifiers. Pull request courtesy Shan.

  References: #9133

oracle

• [oracle] [bug] Added _oracle.ROWID to reflected types as this type may be used in a "CREATE TABLE" statement.

  References: #5047

1.4.46

Released: January 3, 2023

general

• [general] [change] A new deprecation "uber warning" is now emitted at runtime the first time any SQLAlchemy 2.0 deprecation warning would normally be emitted, but the SQLALCHEMY_WARN_20 environment variable is not set. The warning emits only once at most, before setting a boolean to prevent it from emitting a second time.

  This deprecation warning intends to notify users who may not have set an appropriate constraint in their requirements files to block against a surprise SQLAlchemy 2.0 upgrade and also alert that the SQLAlchemy 2.0 upgrade process is available, as the first full 2.0 release is expected very soon. The deprecation warning can be silenced by setting the environment variable SQLALCHEMY_SILENCE_UBER_WARNING to "1".

  References: #8983

• [general] [bug] Fixed regression where the base compat module was calling upon platform.architecture() in order to detect some system properties, which results in an over-broad system call against the system-level file call that is unavailable under some circumstances, including within some secure environment configurations.

  References: #8995

orm

• [orm] [bug] Fixed issue in the internal SQL traversal for DML statements like _dml.Update and _dml.Delete which would cause among other potential issues, a specific issue using lambda statements with the ORM update/delete feature.

  References: #9033

engine

• [engine] [bug] Fixed a long-standing race condition in the connection pool which could occur under eventlet/gevent monkeypatching schemes in conjunction with the use of eventlet/gevent Timeout conditions, where a connection pool checkout that's interrupted due to the timeout would fail to clean up the failed state, causing the underlying connection record and sometimes the database connection itself to "leak", leaving the pool in an invalid state with unreachable entries. This issue was first identified and fixed in SQLAlchemy 1.2 for #4225, however the failure modes detected in that fix failed to accommodate for BaseException, rather than Exception, which prevented eventlet/gevent Timeout from being caught. In addition, a block within initial pool connect has also been identified and hardened with a BaseException -> "clean failed connect" block to accommodate for the same condition in this location. Big thanks to Github user @niklaus for their tenacious efforts in identifying and describing this intricate issue.

  References: #8974

sql

• [sql] [bug] Added parameter FunctionElement.column_valued.joins_implicitly, which is useful in preventing the "cartesian product" warning when making use of table-valued or column-valued functions. This parameter was already introduced for FunctionElement.table_valued() in #7845, however it failed to be added for FunctionElement.column_valued() as well.

  References: #9009

• [sql] [bug] Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType error in 1.4) when using an expression whose type included _types.TypeEngine.bind_expression(), in the context of an "expanding" (i.e. "IN") parameter in conjunction with the literal_binds compiler parameter.

  References: #8989

• [sql] [bug] Fixed issue in lambda SQL feature where the calculated type of a literal value would not take into account the type coercion rules of the "compared to type", leading to a lack of typing information for SQL expressions, such as comparisons to _types.JSON elements and similar.

  References: #9029

postgresql

• [postgresql] [usecase] Added the PostgreSQL type MACADDR8. Pull request courtesy of Asim Farooq.

  References: #8393

• [postgresql] [bug] Fixed bug where the PostgreSQL _postgresql.Insert.on_conflict_do_update.constraint parameter would accept an Index object, however would not expand this index out into its individual index expressions, instead rendering its name in an ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the "constraint name" form only accepts unique or exclude constraint names. The parameter continues to accept the index but now expands it out into its component expressions for the render.

  References: #9023

sqlite

• [sqlite] [bug] Fixed regression caused by new support for reflection of partial indexes on SQLite added in 1.4.45 for #8804, where the index_list pragma command in very old versions of SQLite (possibly prior to 3.8.9) does not return the current expected number of columns, leading to exceptions raised when reflecting tables and indexes.

  References: #8969

tests

• [tests] [bug] Fixed issue in tox.ini file where changes in the tox 4.0 series to the format of "passenv" caused tox to not function correctly, in particular raising an error as of tox 4.0.6.

• [tests] [bug] Added new exclusion rule for third party dialects called unusual_column_name_characters, which can be "closed" for third party dialects that don't support column names with unusual characters such as dots, slashes, or percent signs in them, even if the name is properly quoted.

  References: #9002

1.4.45

Released: December 10, 2022

orm

• [orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the current loaded contents of relationship attributes that were indicated with the _orm.relationship.viewonly parameter, thus defeating strategies that use _orm.Session.merge() to pull fully loaded objects from caches and other similar techniques. In a related change, fixed issue where an object that contains a loaded relationship that was nonetheless configured as lazy='raise' on the mapping would fail when passed to _orm.Session.merge(); checks for "raise" are now suspended within the merge process assuming the _orm.Session.merge.load parameter remains at its default of True.

  Overall, this is a behavioral adjustment to a change introduced in the 1.4 series as of #4994, which took "merge" out of the set of cascades applied by default to "viewonly" relationships. As "viewonly" relationships aren't persisted under any circumstances, allowing their contents to transfer during "merge" does not impact the persistence behavior of the target object. This allows _orm.Session.merge() to correctly suit one of its use cases, that of adding objects to a Session that were loaded elsewhere, often for the purposes of restoring from a cache.

  References: #8862

• [orm] [bug] Fixed issues in _orm.with_expression() where expressions that were composed of columns that were referenced from the enclosing SELECT would not render correct SQL in some contexts, in the case where the expression had a label name that matched the attribute which used _orm.query_expression(), even when _orm.query_expression() had no default expression. For the moment, if the _orm.query_expression() does have a default expression, that label name is still used for that default, and an additional label with the same name will continue to be ignored. Overall, this case is pretty thorny so further adjustments might be warranted.

  References: #8881

engine

• [engine] [bug] Fixed issue where _engine.Result.freeze() method would not work for textual SQL using either _sql.text() or _engine.Connection.exec_driver_sql().

  References: #8963

sql

• [sql] [usecase] An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.

  References: #8800

• [sql] [bug] Fixed a series of issues regarding the position and sometimes the identity of rendered bound parameters, such as those used for SQLite, asyncpg, MySQL, Oracle and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL regexp_replace() function, the "nesting" feature of the CTE construct first introduced in #4123, and selectable tables formed by using the FunctionElement.column_valued() method with Oracle.

  References: #8827

asyncio

• [asyncio] [bug] Removed non-functional merge() method from _asyncio.AsyncResult. This method has never worked and was included with _asyncio.AsyncResult in error.

  References: #8952

postgresql

• [postgresql] [bug] Made an adjustment to how the PostgreSQL dialect considers column types when it reflects columns from a table, to accommodate for alternative backends which may return NULL from the PG format_type() function.

  References: #8748

sqlite

• [sqlite] [usecase] Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.

  References: #8903

• [sqlite] [usecase] Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.

  References: #8804

• [sqlite] [bug] Backported a fix for SQLite reflection of unique constraints in attached schemas, released in 2.0 as a small part of #4379. Previously, unique constraints in attached schemas would be ignored by SQLite reflection. Pull request courtesy Michael Gorven.

  References: #8866

oracle

• [oracle] [bug] Continued fixes for Oracle fix #8708 released in 1.4.43 where bound parameter names that start with underscores, which are disallowed by Oracle, were still not being properly escaped in all circumstances.

  References: #8708

• [oracle] [bug] Fixed issue in Oracle compiler where the syntax for FunctionElement.column_valued() was incorrect, rendering the name COLUMN_VALUE without qualifying the source table correctly.

  References: #8945

1.4.44

Released: November 12, 2022

sql

• [sql] [bug] Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of ORM aliases with subqueries, cache key generation could produce excessively large keys that were orders of magnitude bigger than the statement itself. Much thanks to Rollo Konig Brock for their very patient, long term help in finally identifying this issue.

  References: #8790

postgresql

• [postgresql] [bug] [mssql] For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the "non anon" label that's used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column's type, where the label name should match the column's name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to #6718, #6710. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends.

  References: #8770

oracle

• [oracle] [bug] Fixed issue in the Oracle dialect where an INSERT statement that used insert(some_table).values(...).returning(some_table) against a full Table object at once would fail to execute, raising an exception.

tests

• [tests] [bug] Fixed issue where the --disable-asyncio parameter to the test suite would fail to not actually run greenlet tests and would also not prevent the suite from using a "wrapping" greenlet for the whole suite. This parameter now ensures that no greenlet or asyncio use will occur within the entire run when set.

  References: #8793

• [tests] [bug] Adjusted the test suite which tests the Mypy plugin to accommodate for changes in Mypy 0.990 regarding how it handles message output, which affect how sys.path is interpreted when determining if notes and errors should be printed for particular files. The change broke the test suite as the files within the test directory itself no longer produced messaging when run under the mypy API.

1.4.43

Released: November 4, 2022

orm

• [orm] [bug] Fixed issue in joined eager loading where an assertion fail would occur with a particular combination of outer/inner joined eager loads, when eager loading across three mappers where the middle mapper was an inherited subclass mapper.

  References: #8738

• [orm] [bug] Fixed bug involving Select constructs, where combinations of Select.select_from() with Select.join(), as well as when using Select.join_from(), would cause the _orm.with_loader_criteria() feature as well as the IN criteria needed for single-table inheritance queries to not render, in cases where the columns clause of the query did not explicitly include the left-hand side entity of the JOIN. The correct entity is now transferred to the Join object that's generated internally, so that the criteria against the left side entity is correctly added.

  References: #8721

• [orm] [bug] An informative exception is now raised when the _orm.with_loader_criteria() option is used as a loader option added to a specific "loader path", such as when using it within Load.options(). This use is not supported as _orm.with_loader_criteria() is only intended to be used as a top level loader option. Previously, an internal error would be generated.

  References: #8711

• [orm] [bug] Improved "dictionary mode" for _orm.Session.get() so that synonym names which refer to primary key attribute names may be indicated in the named dictionary.

  References: #8753

• [orm] [bug] Fixed issue where "selectin_polymorphic" loading for inheritance mappers would not function correctly if the _orm.Mapper.polymorphic_on parameter referred to a SQL expression that was not directly mapped on the class.

  References: #8704

• [orm] [bug] Fixed issue where the underlying DBAPI cursor would not be closed when using the _orm.Query object as an iterator, if a user-defined exception case were raised within the iteration process, thereby causing the iterator to be closed by the Python interpreter. When using _orm.Query.yield_per() to create server-side cursors, this would lead to the usual MySQL-related issues with server side cursors out of sync, and without direct access to the Result object, end-user code could not access the cursor in order to close it.

  To resolve, a catch for GeneratorExit is applied within the iterator method, which will close the result object in those cases when the iterator were interrupted, and by definition will be closed by the Python interpreter.

  As part of this change as implemented for the 1.4 series, ensured that .close() methods are available on all Result implementations including ScalarResult, MappingResult. The 2.0 version of this change also includes new context manager patterns for use with Result classes.

  References: #8710

engine

• [engine] [bug] [regression] Fixed issue where the PoolEvents.reset() event hook would not be be called in all cases when a _engine.Connection were closed and was in the process of returning its DBAPI connection to the connection pool.

  The scenario was when the _engine.Connection had already emitted .rollback() on its DBAPI connection within the process of returning the connection to the pool, where it would then instruct the connection pool to forego doing its own "reset" to save on the additional method call. However, this prevented custom pool reset schemes from being used within this hook, as such hooks by definition are doing more than just calling .rollback(), and need to be invoked under all circumstances. This was a regression that appeared in version 1.4.

  For version 1.4, the PoolEvents.checkin() remains viable as an alternate event hook to use for custom "reset" implementations. Version 2.0 will feature an improved version of PoolEvents.reset() which is called for additional scenarios such as termination of asyncio connections, and is also passed contextual information about the reset, to allow for "custom connection reset" schemes which can respond to different reset scenarios in different ways.

  References: #8717

• [engine] [bug] Ensured all Result objects include a Result.close() method as well as a Result.closed attribute, including on ScalarResult and MappingResult.

  References: #8710

sql

• [sql] [bug] Fixed issue which prevented the _sql.literal_column() construct from working properly within the context of a Select construct as well as other potential places where "anonymized labels" might be generated, if the literal expression contained characters which could interfere with format strings, such as open parenthesis, due to an implementation detail of the "anonymous label" structure.

  References: #8724

mssql

• [mssql] [bug] Fixed issue with Inspector.has_table(), which when used against a temporary table with the SQL Server dialect would fail on some Azure variants, due to an unnecessary information schema query that is not supported on those server versions. Pull request courtesy Mike Barry.

  References: #8714

• [mssql] [bug] [reflection] Fixed issue with Inspector.has_table(), which when used against a view with the SQL Server dialect would erroneously return False, due to a regression in the 1.4 series which removed support for this on SQL Server. The issue is not present in the 2.0 series which uses a different reflection architecture. Test support is added to ensure has_table() remains working per spec re: views.

  References: #8700

oracle

• [oracle] [bug] Fixed issue where bound parameter names, including those automatically derived from similarly-named database columns, which contained characters that normally require quoting with Oracle would not be escaped when using "expanding parameters" with the Oracle dialect, causing execution errors. The usual "quoting" for bound parameters used by the Oracle dialect is not used with the "expanding parameters" architecture, so escaping for a large range of characters is used instead, now using a list of characters/escapes that are specific to Oracle.

  References: #8708

• [oracle] [bug] Fixed issue where the nls_session_parameters view queried on first connect in order to get the default decimal point character may not be available depending on Oracle connection modes, and would therefore raise an error. The approach to detecting decimal char has been simplified to test a decimal value directly, instead of reading system views, which works on any backend / driver.

  References: #8744

1.4.42

Released: October 16, 2022

orm

• [orm] [bug] The _orm.Session.execute.bind_arguments dictionary is no longer mutated when passed to _orm.Session.execute() and similar; instead, it's copied to an internal dictionary for state changes. Among other things, this fixes and issue where the "clause" passed to the _orm.Session.get_bind() method would be incorrectly referring to the _sql.Select construct used for the "fetch" synchronization strategy, when the actual query being emitted was a _dml.Delete or _dml.Update. This would interfere with recipes for "routing sessions".

  References: #8614

• [orm] [bug] A warning is emitted in ORM configurations when an explicit _orm.remote() annotation is applied to columns that are local to the immediate mapped class, when the referenced class does not include any of the same table columns. Ideally this would raise an error at some point as it's not correct from a mapping point of view.

  References: #7094

• [orm] [bug] A warning is emitted when attempting to configure a mapped class within an inheritance hierarchy where the mapper is not given any polymorphic identity, however there is a polymorphic discriminator column assigned. Such classes should be abstract if they never intend to load directly.

  References: #7545

• [orm] [bug] [regression] Fixed regression for 1.4 in _orm.contains_eager() where the "wrap in subquery" logic of _orm.joinedload() would be inadvertently triggered for use of the _orm.contains_eager() function with similar statements (e.g. those that use distinct(), limit() or offset()), which would then lead to secondary issues with queries that used some combinations of SQL label names and aliasing. This "wrapping" is not appropriate for _orm.contains_eager() which has always had the contract that the user-defined SQL statement is unmodified with the exception of adding the appropriate columns to be fetched.

  References: #8569

• [orm] [bug] [regression] Fixed regression where using ORM update() with synchronize_session='fetch' would fail due to the use of evaluators that are now used to determine the in-Python value for expressions in the the SET clause when refreshing objects; if the evaluators make use of math operators against non-numeric values such as PostgreSQL JSONB, the non-evaluable condition would fail to be detected correctly. The evaluator now limits the use of math mutation operators to numeric types only, with the exception of "+" that continues to work for strings as well. SQLAlchemy 2.0 may alter this further by fetching the SET values completely rather than using evaluation.

  References: #8507

engine

• [engine] [bug] Fixed issue where mixing "*" with additional explicitly-named column expressions within the columns clause of a _sql.select() construct would cause result-column targeting to sometimes consider the label name or other non-repeated names to be an ambiguous target.

  References: #8536

asyncio

• [asyncio] [bug] Improved implementation of asyncio.shield() used in context managers as added in #8145, such that the "close" operation is enclosed within an asyncio.Task which is then strongly referenced as the operation proceeds. This is per Python documentation indicating that the task is otherwise not strongly referenced.

  References: #8516

postgresql

• [postgresql] [usecase] _postgresql.aggregate_order_by now supports cache generation.

  References: #8574

mysql

• [mysql] [bug] Adjusted the regular expression used to match "CREATE VIEW" when testing for views to work more flexibly, no longer requiring the special keyword "ALGORITHM" in the middle, which was intended to be optional but was not working correctly. The change allows view reflection to work more completely on MySQL-compatible variants such as StarRocks. Pull request courtesy John Bodley.

  References: #8588

mssql

• [mssql] [bug] [regression] Fixed yet another regression in SQL Server isolation level fetch (see #8231, #8475), this time with "Microsoft Dynamics CRM Database via Azure Active Directory", which apparently lacks the system_views view entirely. Error catching has been extended that under no circumstances will this method ever fail, provided database connectivity is present.

  References: #8525

1.4.41

Released: September 6, 2022

orm

• [orm] [bug] [events] Fixed event listening issue where event listeners added to a superclass would be lost if a subclass were created which then had its own listeners associated. The practical example is that of the sessionmaker class created after events have been associated with the _orm.Session class.

  References: #8467

• [orm] [bug] Hardened the cache key strategy for the _orm.aliased() and _orm.with_polymorphic() constructs. While no issue involving actual statements being cached can easily be demonstrated (if at all), these two constructs were not including enough of what makes them unique in their cache keys for caching on the aliased construct alone to be accurate.

  References: #8401

• [orm] [bug] [regression] Fixed regression appearing in the 1.4 series where a joined-inheritance query placed as a subquery within an enclosing query for that same entity would fail to render the JOIN correctly for the inner query. The issue manifested in two different ways prior and subsequent to version 1.4.18 (related issue #6595), in one case rendering JOIN twice, in the other losing the JOIN entirely. To resolve, the conditions under which "polymorphic loading" are applied have been scaled back to not be invoked for simple joined inheritance queries.

  References: #8456

• [orm] [bug] Fixed issue in sqlalchemy.ext.mutable extension where collection links to the parent object would be lost if the object were merged with Session.merge() while also passing Session.merge.load as False.

  References: #8446

• [orm] [bug] Fixed issue involving _orm.with_loader_criteria() where a closure variable used as bound parameter value within the lambda would not carry forward correctly into additional relationship loaders such as _orm.selectinload() and _orm.lazyload() after the statement were cached, using the stale originally-cached value instead.

  References: #8399

sql

• [sql] [bug] Fixed issue where use of the _sql.table() construct, passing a string for the _sql.table.schema parameter, would fail to take the "schema" string into account when producing a cache key, thus leading to caching collisions if multiple, same-named _sql.table() constructs with different schemas were used.

  References: #8441

asyncio

• [asyncio] [bug] Integrated support for asyncpg's terminate() method call for cases where the connection pool is recycling a possibly timed-out connection, where a connection is being garbage collected that wasn't gracefully closed, as well as when the connection has been invalidated. This allows asyncpg to abandon the connection without waiting for a response that may incur long timeouts.

  References: #8419

mssql

• [mssql] [bug] [regression] Fixed regression caused by the fix for #8231 released in 1.4.40 where connection would fail if the user did not have permission to query the dm_exec_sessions or dm_pdw_nodes_exec_sessions system views when trying to determine the current transaction isolation level.

  References: #8475

1.4.40

Released: August 8, 2022

orm

• [orm] [bug] Fixed issue where referencing a CTE multiple times in conjunction with a polymorphic SELECT could result in multiple "clones" of the same CTE being constructed, which would then trigger these two CTEs as duplicates. To resolve, the two CTEs are deep-compared when this occurs to ensure that they are equivalent, then are treated as equivalent.

  References: #8357

• [orm] [bug] A _sql.select() construct that is passed a sole '*' argument for SELECT *, either via string, _sql.text(), or _sql.literal_column(), will be interpreted as a Core-level SQL statement rather than as an ORM level statement. This is so that the *, when expanded to match any number of columns, will result in all columns returned in the result. the ORM- level interpretation of _sql.select() needs to know the names and types of all ORM columns up front which can't be achieved when '*' is used.

  If '* is used amongst other expressions simultaneously with an ORM statement, an error is raised as this can't be interpreted correctly by the ORM.

  References: #8235

orm declarative

• [orm] [declarative] [bug] Fixed issue where a hierarchy of classes set up as an abstract or mixin declarative classes could not declare standalone columns on a superclass that would then be copied correctly to a _orm.declared_attr callable that wanted to make use of them on a descendant class.

  References: #8190

engine

• [engine] [usecase] Implemented new _engine.Connection.execution_options.yield_per execution option for _engine.Connection in Core, to mirror that of the same yield_per <orm_queryguide_yield_per> option available in the ORM. The option sets both the _engine.Connection.execution_options.stream_results option at the same time as invoking _engine.Result.yield_per(), to provide the most common streaming result configuration which also mirrors that of the ORM use case in its usage pattern.

• [engine] [bug] Fixed bug in _engine.Result where the usage of a buffered result strategy would not be used if the dialect in use did not support an explicit "server side cursor" setting, when using _engine.Connection.execution_options.stream_results. This is in error as DBAPIs such as that of SQLite and Oracle already use a non-buffered result fetching scheme, which still benefits from usage of partial result fetching. The "buffered" strategy is now used in all cases where _engine.Connection.execution_options.stream_results is set.

• [engine] [bug] Added FilterResult.yield_per() so that result implementations such as MappingResult, ScalarResult and AsyncResult have access to this method.

  References: #8199

sql

• [sql] [bug] Adjusted the SQL compilation for string containment functions .contains(), .startswith(), .endswith() to force the use of the string concatenation operator, rather than relying upon the overload of the addition operator, so that non-standard use of these operators with for example bytestrings still produces string concatenation operators.

  References: #8253

mypy

• [mypy] [bug] Fixed a crash of the mypy plugin when using a lambda as a Column default. Pull request curtesy of tchapi.

  References: #8196

asyncio

• [asyncio] [bug] Added asyncio.shield() to the connection and session release process specifically within the __aexit__() context manager exit, when using AsyncConnection or AsyncSession as a context manager that releases the object when the context manager is complete. This appears to help with task cancellation when using alternate concurrency libraries such as anyio, uvloop that otherwise don't provide an async context for the connection pool to release the connection properly during task cancellation.

  References: #8145

postgresql

• [postgresql] [bug] Fixed issue in psycopg2 dialect where the "multiple hosts" feature implemented for #4392, where multiple host:port pairs could be passed in the query string as ?host=host1:port1&host=host2:port2&host=host3:port3 was not implemented correctly, as it did not propagate the "port" parameter appropriately. Connections that didn't use a different "port" likely worked without issue, and connections that had "port" for some of the entries may have incorrectly passed on that hostname. The format is now corrected to pass hosts/ports appropriately.

  As part of this change, maintained support for another multihost style that worked unintentionally, which is comma-separated ?host=h1,h2,h3&port=p1,p2,p3. This format is more consistent with libpq's query-string format, whereas the previous format is inspired by a different aspect of libpq's URI format but is not quite the same thing.

  If the two styles are mixed together, an error is raised as this is ambiguous.

  References: #4392

mssql

• [mssql] [bug] Fixed issues that prevented the new usage patterns for using DML with ORM objects presented at orm_dml_returning_objects from working correctly with the SQL Server pyodbc dialect.

  References: #8210

• [mssql] [bug] Fixed issue where the SQL Server dialect's query for the current isolation level would fail on Azure Synapse Analytics, due to the way in which this database handles transaction rollbacks after an error has occurred. The initial query has been modified to no longer rely upon catching an error when attempting to detect the appropriate system view. Additionally, to better support this database's very specific "rollback" behavior, implemented new parameter ignore_no_transaction_on_rollback indicating that a rollback should ignore Azure Synapse error 'No corresponding transaction found. (111214)', which is raised if no transaction is present in conflict with the Python DBAPI.

  Initial patch and valuable debugging assistance courtesy of @ww2406.

  References: #8231

misc

• [bug] [types] Fixed issue where TypeDecorator would not correctly proxy the __getitem__() operator when decorating the _types.ARRAY datatype, without explicit workarounds.

  References: #7249

1.4.39

Released: June 24, 2022

orm

• [orm] [bug] [regression] Fixed regression caused by #8133 where the pickle format for mutable attributes was changed, without a fallback to recognize the old format, causing in-place upgrades of SQLAlchemy to no longer be able to read pickled data from previous versions. A check plus a fallback for the old format is now in place.

  References: #8133

1.4.38

Released: June 23, 2022

orm

• [orm] [bug] [regression] Fixed regression caused by #8064 where a particular check for column correspondence was made too liberal, resulting in incorrect rendering for some ORM subqueries such as those using PropComparator.has() or PropComparator.any() in conjunction with joined-inheritance queries that also use legacy aliasing features.

  References: #8162

• [orm] [bug] [sql] Fixed an issue where _sql.GenerativeSelect.fetch() would not be applied when executing a statement using the ORM.

  References: #8091

• [orm] [bug] Fixed issue where a _orm.with_loader_criteria() option could not be pickled, as is necessary when it is carried along for propagation to lazy loaders in conjunction with a caching scheme. Currently, the only form that is supported as picklable is to pass the "where criteria" as a fixed module-level callable function that produces a SQL expression. An ad-hoc "lambda" can't be pickled, and a SQL expression object is usually not fully picklable directly.

  References: #8109

engine

• [engine] [bug] Repaired a deprecation warning class decorator that was preventing key objects such as _engine.Connection from having a proper __weakref__ attribute, causing operations like Python standard library inspect.getmembers() to fail.

  References: #8115

sql

• [sql] [bug] Fixed multiple observed race conditions related to lambda_stmt(), including an initial "dogpile" issue when a new Python code object is initially analyzed among multiple simultaneous threads which created both a performance issue as well as some internal corruption of state. Additionally repaired observed race condition which could occur when "cloning" an expression construct that is also in the process of being compiled or otherwise accessed in a different thread due to memoized attributes altering the __dict__ while iterated, for Python versions prior to 3.10; in particular the lambda SQL construct is sensitive to this as it holds onto a single statement object persistently. The iteration has been refined to use dict.copy() with or without an additional iteration instead.

  References: #8098

• [sql] [bug] Enhanced the mechanism of Cast and other "wrapping" column constructs to more fully preserve a wrapped Label construct, including that the label name will be preserved in the .c collection of a Subquery. The label was already able to render in the SQL correctly on the outside of the construct which it was wrapped inside.

  References: #8084

• [sql] [bug] Adjusted the fix made for #8056 which adjusted the escaping of bound parameter names with special characters such that the escaped names were translated after the SQL compilation step, which broke a published recipe on the FAQ illustrating how to merge parameter names into the string output of a compiled SQL string. The change restores the escaped names that come from compiled.params and adds a conditional parameter to SQLCompiler.construct_params() named escape_names that defaults to True, restoring the old behavior by default.

  References: #8113

schema

• [schema] [bug] Fixed bugs involving the Table.include_columns and the Table.resolve_fks parameters on Table; these little-used parameters were apparently not working for columns that refer to foreign key constraints.

  In the first case, not-included columns that refer to foreign keys would still attempt to create a ForeignKey object, producing errors when attempting to resolve the columns for the foreign key constraint within reflection; foreign key constraints that refer to skipped columns are now omitted from the table reflection process in the same way as occurs for Index and UniqueConstraint objects with the same conditions. No warning is produced however, as we likely want to remove the include_columns warnings for all constraints in 2.0.

  In the latter case, the production of table aliases or subqueries would fail on an FK related table not found despite the presence of resolve_fks=False; the logic has been repaired so that if a related table is not found, the ForeignKey object is still proxied to the aliased table or subquery (these ForeignKey objects are normally used in the production of join conditions), but it is sent with a flag that it's not resolvable. The aliased table / subquery will then work normally, with the exception that it cannot be used to generate a join condition automatically, as the foreign key information is missing. This was already the behavior for such foreign key constraints produced using non-reflection methods, such as joining Table objects from different MetaData collections.

  References: #8100, #8101

• [schema] [bug] [mssql] Fixed issue where Table objects that made use of IDENTITY columns with a Numeric datatype would produce errors when attempting to reconcile the "autoincrement" column, preventing construction of the Column from using the Column.autoincrement parameter as well as emitting errors when attempting to invoke an Insert construct.

  References: #8111

extensions

• [extensions] [bug] Fixed bug in Mutable where pickling and unpickling of an ORM mapped instance would not correctly restore state for mappings that contained multiple Mutable-enabled attributes.

  References: #8133

1.4.37

Released: May 31, 2022

orm

• [orm] [bug] Fixed issue where using a _orm.column_property() construct containing a subquery against an already-mapped column attribute would not correctly apply ORM-compilation behaviors to the subquery, including that the "IN" expression added for a single-table inherits expression would fail to be included.

  References: #8064

• [orm] [bug] Fixed issue where ORM results would apply incorrect key names to the returned Row objects in the case where the set of columns to be selected were changed, such as when using Select.with_only_columns().

  References: #8001

• [orm] [bug] [oracle] [postgresql] Fixed bug, likely a regression from 1.3, where usage of column names that require bound parameter escaping, more concretely when using Oracle with column names that require quoting such as those that start with an underscore, or in less common cases with some PostgreSQL drivers when using column names that contain percent signs, would cause the ORM versioning feature to not work correctly if the versioning column itself had such a name, as the ORM assumes certain bound parameter naming conventions that were being interfered with via the quotes. This issue is related to #8053 and essentially revises the approach towards fixing this, revising the original issue #5653 that created the initial implementation for generalized bound-parameter name quoting.

  References: #8056

engine

• [engine] [bug] [tests] Fixed issue where support for logging "stacklevel" implemented in #7612 required adjustment to work with recently released Python 3.11.0b1, also repairs the unit tests which tested this feature.

  References: #8019

sql

• [sql] [bug] [postgresql] [sqlite] Fixed bug where the PostgreSQL _postgresql.Insert.on_conflict_do_update() method and the SQLite _sqlite.Insert.on_conflict_do_update() method would both fail to correctly accommodate a column with a separate ".key" when specifying the column using its key name in the dictionary passed to _postgresql.Insert.on_conflict_do_update.set_, as well as if the _postgresql.Insert.excluded collection were used as the dictionary directly.

  References: #8014

• [sql] [bug] An informative error is raised for the use case where Insert.from_select() is being passed a "compound select" object such as a UNION, yet the INSERT statement needs to append additional columns to support Python-side or explicit SQL defaults from the table metadata. In this case a subquery of the compound object should be passed.

  References: #8073

• [sql] [bug] Fixed an issue where using bindparam() with no explicit data or type given could be coerced into the incorrect type when used in expressions such as when using ARRAY.Comparator.any() and ARRAY.Comparator.all().

  References: #7979

• [sql] [bug] An informative error is raised if two individual BindParameter objects share the same name, yet one is used within an "expanding" context (typically an IN expression) and the other is not; mixing the same name in these two different styles of usage is not supported and typically the expanding=True parameter should be set on the parameters that are to receive list values outside of IN expressions (where expanding is set by default).

  References: #8018

mysql

• [mysql] [bug] Further adjustments to the MySQL PyODBC dialect to allow for complete connectivity, which was previously still not working despite fixes in #7871.

  References: #7966

• [mysql] [bug] Added disconnect code for MySQL error 4031, introduced in MySQL >= 8.0.24, indicating connection idle timeout exceeded. In particular this repairs an issue where pre-ping could not reconnect on a timed-out connection. Pull request courtesy valievkarim.

  References: #8036

mssql

• [mssql] [bug] Fix issue where a password with a leading "{" would result in login failure.

  References: #8062

• [mssql] [bug] [reflection] Explicitly specify the collation when reflecting table columns using MSSQL to prevent "collation conflict" errors.

  References: #8035

oracle

• [oracle] [usecase] Added two new error codes for Oracle disconnect handling to support early testing of the new "python-oracledb" driver released by Oracle.

  References: #8066

• [oracle] [bug] Fixed SQL compiler issue where the "bind processing" function for a bound parameter would not be correctly applied to a bound value if the bound parameter's name were "escaped". Concretely, this applies, among other cases, to Oracle when a Column has a name that itself requires quoting, such that the quoting-required name is then used for the bound parameters generated within DML statements, and the datatype in use requires bind processing, such as the Enum datatype.

  References: #8053

1.4.36

Released: April 26, 2022

orm

• [orm] [bug] [regression] Fixed regression where the change made for #7861, released in version 1.4.33, that brought the Insert construct to be partially recognized as an ORM-enabled statement did not properly transfer the correct mapper / mapped table state to the Session, causing the Session.get_bind() method to fail for a Session that was bound to engines and/or connections using the Session.binds parameter.

  References: #7936

orm declarative

• [orm] [declarative] [bug] Modified the DeclarativeMeta metaclass to pass cls.__dict__ into the declarative scanning process to look for attributes, rather than the separate dictionary passed to the type's __init__() method. This allows user-defined base classes that add attributes within an __init_subclass__() to work as expected, as __init_subclass__() can only affect the cls.__dict__ itself and not the other dictionary. This is technically a regression from 1.3 where __dict__ was being used.

  References: #7900

engine

• [engine] [bug] Fixed a memory leak in the C extensions which could occur when calling upon named members of Row when the member does not exist under Python 3; in particular this could occur during NumPy transformations when it attempts to call members such as .__array__, but the issue was surrounding any AttributeError thrown by the Row object. This issue does not apply to version 2.0 which has already transitioned to Cython. Thanks much to Sebastian Berg for identifying the problem.

  References: #7875

• [engine] [bug] Added a warning regarding a bug which exists in the Result.columns() method when passing 0 for the index in conjunction with a Result that will return a single ORM entity, which indicates that the current behavior of Result.columns() is broken in this case as the Result object will yield scalar values and not Row objects. The issue will be fixed in 2.0, which would be a backwards-incompatible change for code that relies on the current broken behavior. Code which wants to receive a collection of scalar values should use the Result.scalars() method, which will return a new ScalarResult object that yields non-row scalar objects.

  References: #7953

schema

• [schema] [bug] Fixed bug where ForeignKeyConstraint naming conventions using the referred_column_0 naming convention key would not work if the foreign key constraint were set up as a ForeignKey object rather than an explicit ForeignKeyConstraint object. As this change makes use of a backport of some fixes from version 2.0, an additional little-known feature that has likely been broken for many years is also fixed which is that a ForeignKey object may refer to a referred table by name of the table alone without using a column name, if the name of the referent column is the same as that of the referred column.

  The referred_column_0 naming convention key was previously not tested with the ForeignKey object, only ForeignKeyConstraint, and this bug reveals that the feature has never worked correctly unless ForeignKeyConstraint is used for all FK constraints. This bug traces back to the original introduction of the feature introduced for #3989.

  References: #7958

asyncio

• [asyncio] [bug] Repaired handling of contextvar.ContextVar objects inside of async adapted event handlers. Previously, values applied to a ContextVar would not be propagated in the specific case of calling upon awaitables inside of non-awaitable code.

  References: #7937

postgresql

• [postgresql] [bug] Fixed bug in ARRAY datatype in combination with Enum on PostgreSQL where using the .any() or .all() methods to render SQL ANY() or ALL(), given members of the Python enumeration as arguments, would produce a type adaptation failure on all drivers.

  References: #6515

• [postgresql] [bug] Implemented _postgresql.UUID.python_type attribute for the PostgreSQL _postgresql.UUID type object. The attribute will return either str or uuid.UUID based on the _postgresql.UUID.as_uuid parameter setting. Previously, this attribute was unimplemented. Pull request courtesy Alex Grönholm.

  References: #7943

• [postgresql] [bug] Fixed an issue in the psycopg2 dialect when using the create_engine.pool_pre_ping parameter which would cause user-configured AUTOCOMMIT isolation level to be inadvertently reset by the "ping" handler.

  References: #7930

mysql

• [mysql] [bug] [regression] Fixed a regression in the untested MySQL PyODBC dialect caused by the fix for #7518 in version 1.4.32 where an argument was being propagated incorrectly upon first connect, leading to a TypeError.

  References: #7871

tests

• [tests] [bug] For third party dialects, repaired a missing requirement for the SimpleUpdateDeleteTest suite test which was not checking for a working "rowcount" function on the target dialect.

  References: #7919

1.4.35

Released: April 6, 2022

sql

• [sql] [bug] Fixed bug in newly implemented FunctionElement.table_valued.joins_implicitly feature where the parameter would not automatically propagate from the original TableValuedAlias object to the secondary object produced when calling upon TableValuedAlias.render_derived() or TableValuedAlias.alias().

  Additionally repaired these issues in TableValuedAlias:

  -   repaired a potential memory issue which could occur when
      repeatedly calling `TableValuedAlias.render_derived()` against
      successive copies of the same object (for .alias(), we currently
      have to still continue chaining from the previous element.  not sure
      if this can be improved but this is standard behavior for .alias()
      elsewhere)
  
  -   repaired issue where the individual element types would be lost when
      calling upon `TableValuedAlias.render_derived()` or
      `TableValuedAlias.alias()`.
  

  References: #7890

• [sql] [bug] [regression] Fixed regression caused by #7823 which impacted the caching system, such that bound parameters that had been "cloned" within ORM operations, such as polymorphic loading, would in some cases not acquire their correct execution-time value leading to incorrect bind values being rendered.

  References: #7903

1.4.34

Released: March 31, 2022

orm

• [orm] [bug] [regression] Fixed regression caused by #7861 where invoking an Insert construct which contained ORM entities directly via _orm.Session.execute() would fail.

  References: #7878

postgresql

• [postgresql] [bug] Scaled back a fix made for #6581 where "executemany values" mode for psycopg2 were disabled for all "ON CONFLICT" styles of INSERT, to not apply to the "ON CONFLICT DO NOTHING" clause, which does not include any parameters and is safe for "executemany values" mode. "ON CONFLICT DO UPDATE" is still blocked from "executemany values" as there may be additional parameters in the DO UPDATE clause that cannot be batched (which is the original issue fixed by #6581).

  References: #7880

1.4.33

Released: March 31, 2022

orm

• [orm] [usecase] Added _orm.with_polymorphic.adapt_on_names to the _orm.with_polymorphic() function, which allows a polymorphic load (typically with concrete mapping) to be stated against an alternative selectable that will adapt to the original mapped selectable on column names alone.

  References: #7805

• [orm] [usecase] Added new attributes UpdateBase.returning_column_descriptions and UpdateBase.entity_description to allow for inspection of ORM attributes and entities that are installed as part of an Insert, Update, or Delete construct. The Select.column_descriptions accessor is also now implemented for Core-only selectables.

  References: #7861

• [orm] [performance] [bug] Improvements in memory usage by the ORM, removing a significant set of intermediary expression objects that are typically stored when a copy of an expression object is created. These clones have been greatly reduced, reducing the number of total expression objects stored in memory by ORM mappings by about 30%.

  References: #7823

• [orm] [bug] [regression] Fixed regression in "dynamic" loader strategy where the _orm.Query.filter_by() method would not be given an appropriate entity to filter from, in the case where a "secondary" table were present in the relationship being queried and the mapping were against something complex such as a "with polymorphic".

  References: #7868

• [orm] [bug] Fixed bug where _orm.composite() attributes would not work in conjunction with the _orm.selectin_polymorphic() loader strategy for joined table inheritance.

  References: #7801

• [orm] [bug] Fixed issue where the _orm.selectin_polymorphic() loader option would not work with joined inheritance mappers that don't have a fixed "polymorphic_on" column. Additionally added test support for a wider variety of usage patterns with this construct.

  References: #7799

• [orm] [bug] Fixed bug in _orm.with_loader_criteria() function where loader criteria would not be applied to a joined eager load that were invoked within the scope of a refresh operation for the parent object.

  References: #7862

• [orm] [bug] Fixed issue where the _orm.Mapper would reduce a user-defined _orm.Mapper.primary_key argument too aggressively, in the case of mapping to a UNION where for some of the SELECT entries, two columns are essentially equivalent, but in another, they are not, such as in a recursive CTE. The logic here has been changed to accept a given user-defined PK as given, where columns will be related to the mapped selectable but no longer "reduced" as this heuristic can't accommodate for all situations.

  References: #7842

engine

• [engine] [usecase] Added new parameter Engine.dispose.close, defaulting to True. When False, the engine disposal does not touch the connections in the old pool at all, simply dropping the pool and replacing it. This use case is so that when the original pool is transferred from a parent process, the parent process may continue to use those connections.

  References: #7815, #7877

• [engine] [bug] Further clarified connection-level logging to indicate the BEGIN, ROLLBACK and COMMIT log messages do not actually indicate a real transaction when the AUTOCOMMIT isolation level is in use; messaging has been extended to include the BEGIN message itself, and the messaging has also been fixed to accommodate when the Engine level create_engine.isolation_level parameter was used directly.

  References: #7853

sql

• [sql] [usecase] Added new parameter FunctionElement.table_valued.joins_implicitly, for the FunctionElement.table_valued() construct. This parameter indicates that the given table-valued function implicitly joins to the table it refers towards, essentially disabling the "from linting" feature, i.e. the "cartesian product" warning, from taking effect due to the presence of this parameter. May be used for functions such as func.json_each().

  References: #7845

• [sql] [bug] The bindparam.literal_execute parameter now takes part of the cache generation of a bindparam(), since it changes the sql string generated by the compiler. Previously the correct bind values were used, but the literal_execute would be ignored on subsequent executions of the same query.

  References: #7876

• [sql] [bug] [regression] Fixed regression caused by #7760 where the new capabilities of TextualSelect were not fully implemented within the compiler properly, leading to issues with composed INSERT constructs such as "INSERT FROM SELECT" and "INSERT...ON CONFLICT" when combined with CTE and textual statements.

  References: #7798

schema

• [schema] [usecase] Added support so that the Table.to_metadata.referred_schema_fn callable passed to Table.to_metadata() may return the value BLANK_SCHEMA to indicate that the referenced foreign key should be reset to None. The RETAIN_SCHEMA symbol may also be returned from this function to indicate "no change", which will behave the same as None currently does which also indicates no change.

  References: #7860

sqlite

• [sqlite] [bug] [reflection] Fixed bug where the name of CHECK constraints under SQLite would not be reflected if the name were created using quotes, as is the case when the name uses mixed case or special characters.

  References: #5463

mssql

• [mssql] [bug] [regression] Fixed regression caused by #7160 where FK reflection in conjunction with a low compatibility level setting (compatibility level 80: SQL Server 2000) causes an "Ambiguous column name" error. Patch courtesy @Lin-Your.

  References: #7812

misc

• [bug] [ext] Improved the error message that's raised for the case where the association_proxy() construct attempts to access a target attribute at the class level, and this access fails. The particular use case here is when proxying to a hybrid attribute that does not include a working class-level implementation.

  References: #7827

1.4.32

Released: March 6, 2022

orm

• [orm] [bug] [regression] Fixed regression where the ORM exception that is to be raised when an INSERT silently fails to actually insert a row (such as from a trigger) would not be reached, due to a runtime exception raised ahead of time due to the missing primary key value, thus raising an uninformative exception rather than the correct one. For 1.4 and above, a new _ormexc.FlushError is added for this case that's raised earlier than the previous "null identity" exception was for 1.3, as a situation where the number of rows actually INSERTed does not match what was expected is a more critical situation in 1.4 as it prevents batching of multiple objects from working correctly. This is separate from the case where a newly fetched primary key is fetched as NULL, which continues to raise the existing "null identity" exception.

  References: #7594

• [orm] [bug] Fixed issue where using a fully qualified path for the classname in _orm.relationship() that nonetheless contained an incorrect name for path tokens that were not the first token, would fail to raise an informative error and would instead fail randomly at a later step.

  References: #7697

engine

• [engine] [bug] Adjusted the logging for key SQLAlchemy components including _engine.Engine, _engine.Connection to establish an appropriate stack level parameter, so that the Python logging tokens funcName and lineno when used in custom logging formatters will report the correct information, which can be useful when filtering log output; supported on Python 3.8 and above. Pull request courtesy Markus Gerstel.

  References: #7612

sql

• [sql] [bug] Fixed type-related error messages that would fail for values that were tuples, due to string formatting syntax, including compile of unsupported literal values and invalid boolean values.

  References: #7721

• [sql] [bug] [mysql] Fixed issues in MySQL _mysql.SET datatype as well as the generic Enum datatype where the __repr__() method would not render all optional parameters in the string output, impacting the use of these types in Alembic autogenerate. Pull request for MySQL courtesy Yuki Nishimine.

  References: #7598, #7720, #7789

• [sql] [bug] The _sqltypes.Enum datatype now emits a warning if the _sqltypes.Enum.length argument is specified without also specifying _sqltypes.Enum.native_enum as False, as the parameter is otherwise silently ignored in this case, despite the fact that the _sqltypes.Enum datatype will still render VARCHAR DDL on backends that don't have a native ENUM datatype such as SQLite. This behavior may change in a future release so that "length" is honored for all non-native "enum" types regardless of the "native_enum" setting.

• [sql] [bug] Fixed issue where the HasCTE.add_cte() method as called upon a TextualSelect instance was not being accommodated by the SQL compiler. The fix additionally adds more "SELECT"-like compiler behavior to TextualSelect including that DML CTEs such as UPDATE and INSERT may be accommodated.

  References: #7760

asyncio

• [asyncio] [bug] Fixed issues where a descriptive error message was not raised for some classes of event listening with an async engine, which should instead be a sync engine instance.

• [asyncio] [bug] Fixed issue where the _asyncio.AsyncSession.execute() method failed to raise an informative exception if the _engine.Connection.execution_options.stream_results execution option were used, which is incompatible with a sync-style _result.Result object when using an asyncio calling style, as the operation to fetch more rows would need to be awaited. An exception is now raised in this scenario in the same way one was already raised when the _engine.Connection.execution_options.stream_results option would be used with the _asyncio.AsyncConnection.execute() method.

  Additionally, for improved stability with state-sensitive database drivers such as asyncmy, the cursor is now closed when this error condition is raised; previously with the asyncmy dialect, the connection would go into an invalid state with unconsumed server side results remaining.

  References: #7667

postgresql

• [postgresql] [usecase] Added compiler support for the PostgreSQL NOT VALID phrase when rendering DDL for the CheckConstraint, ForeignKeyConstraint and ForeignKey schema constructs. Pull request courtesy Gilbert Gilb's.

  References: #7600

mysql

• [mysql] [bug] [regression] Fixed regression caused by #7518 where changing the syntax "SHOW VARIABLES" to "SELECT @@" broke compatibility with MySQL versions older than 5.6, including early 5.0 releases. While these are very old MySQL versions, a change in compatibility was not planned, so version-specific logic has been restored to fall back to "SHOW VARIABLES" for MySQL server versions < 5.6.

  References: #7518

mariadb

• [mariadb] [bug] [regression] Fixed regression in mariadbconnector dialect as of mariadb connector 1.0.10 where the DBAPI no longer pre-buffers cursor.lastrowid, leading to errors when inserting objects with the ORM as well as causing non-availability of the _result.CursorResult.inserted_primary_key attribute. The dialect now fetches this value proactively for situations where it applies.

  References: #7738

sqlite

• [sqlite] [usecase] Added support for reflecting SQLite inline unique constraints where the column names are formatted with SQLite "escape quotes" [] or ```, which are discarded by the database when producing the column name.

  References: #7736

• [sqlite] [bug] Fixed issue where SQLite unique constraint reflection would fail to detect a column-inline UNIQUE constraint where the column name had an underscore in its name.

  References: #7736

oracle

• [oracle] [bug] Fixed issue in Oracle dialect where using a column name that requires quoting when written as a bound parameter, such as "_id", would not correctly track a Python generated default value due to the bound-parameter rewriting missing this value, causing an Oracle error to be raised.

  References: #7676

• [oracle] [bug] [regression] Added support to parse "DPI" error codes from cx_Oracle exception objects such as DPI-1080 and DPI-1010, both of which now indicate a disconnect scenario as of cx_Oracle 8.3.

  References: #7748

tests

• [tests] [bug] Improvements to the test suite's integration with pytest such that the "warnings" plugin, if manually enabled, will not interfere with the test suite, such that third parties can enable the warnings plugin or make use of the -W parameter and SQLAlchemy's test suite will continue to pass. Additionally, modernized the detection of the "pytest-xdist" plugin so that plugins can be globally disabled using PYTEST_DISABLE_PLUGIN_AUTOLOAD=1 without breaking the test suite if xdist were still installed. Warning filters that promote deprecation warnings to errors are now localized to SQLAlchemy-specific warnings, or within SQLAlchemy-specific sources for general Python deprecation warnings, so that non-SQLAlchemy deprecation warnings emitted from pytest plugins should also not impact the test suite.

  References: #7599

• [tests] [bug] Made corrections to the default pytest configuration regarding how test discovery is configured, to fix issue where the test suite would not configure warnings correctly and also attempt to load example suites as tests, in the specific case where the SQLAlchemy checkout were located in an absolute path that had a super-directory named "test".

  References: #7045

1.4.31

Released: January 20, 2022

orm

• [orm] [bug] Fixed issue in _orm.Session.bulk_save_objects() where the sorting that takes place when the preserve_order parameter is set to False would sort partially on Mapper objects, which is rejected in Python 3.11.

  References: #7591

postgresql

• [postgresql] [bug] [regression] Fixed regression where the change in #7148 to repair ENUM handling in PostgreSQL broke the use case of an empty ARRAY of ENUM, preventing rows that contained an empty array from being handled correctly when fetching results.

  References: #7590

mysql

• [mysql] [bug] [regression] Fixed regression in asyncmy dialect caused by #7567 where removal of the PyMySQL dependency broke binary columns, due to the asyncmy dialect not being properly included within CI tests.

  References: #7593

mssql

• [mssql] Added support for FILESTREAM when using VARBINARY(max) in MSSQL.

  References: #7243

1.4.30

Released: January 19, 2022

orm

• [orm] [bug] Fixed issue in joined-inheritance load of additional attributes functionality in deep multi-level inheritance where an intermediary table that contained no columns would not be included in the tables joined, instead linking those tables to their primary key identifiers. While this works fine, it nonetheless in 1.4 began producing the cartesian product compiler warning. The logic has been changed so that these intermediary tables are included regardless. While this does include additional tables in the query that are not technically necessary, this only occurs for the highly unusual case of deep 3+ level inheritance with intermediary tables that have no non primary key columns, potential performance impact is therefore expected to be negligible.

  References: #7507

• [orm] [bug] Fixed issue where calling upon _orm.registry.map_imperatively() more than once for the same class would produce an unexpected error, rather than an informative error that the target class is already mapped. This behavior differed from that of the _orm.mapper() function which does report an informative message already.

  References: #7579

• [orm] [bug] [asyncio] Added missing method _asyncio.AsyncSession.invalidate() to the _asyncio.AsyncSession class.

  References: #7524

• [orm] [bug] [regression] Fixed regression which appeared in 1.4.23 which could cause loader options to be mis-handled in some cases, in particular when using joined table inheritance in combination with the polymorphic_load="selectin" option as well as relationship lazy loading, leading to a TypeError.

  References: #7557

• [orm] [bug] [regression] Fixed ORM regression where calling the _orm.aliased() function against an existing _orm.aliased() construct would fail to produce correct SQL if the existing construct were against a fixed table. The fix allows that the original _orm.aliased() construct is disregarded if it were only against a table that's now being replaced. It also allows for correct behavior when constructing a _orm.aliased() without a selectable argument against a _orm.aliased() that's against a subuquery, to create an alias of that subquery (i.e. to change its name).

  The nesting behavior of _orm.aliased() remains in place for the case where the outer _orm.aliased() object is against a subquery which in turn refers to the inner _orm.aliased() object. This is a relatively new 1.4 feature that helps to suit use cases that were previously served by the deprecated Query.from_self() method.

  References: #7576

• [orm] [bug] Fixed issue where _sql.Select.correlate_except() method, when passed either the None value or no arguments, would not correlate any elements when used in an ORM context (that is, passing ORM entities as FROM clauses), rather than causing all FROM elements to be considered as "correlated" in the same way which occurs when using Core-only constructs.

  References: #7514

• [orm] [bug] [regression] Fixed regression from 1.3 where the "subqueryload" loader strategy would fail with a stack trace if used against a query that made use of _orm.Query.from_statement() or _sql.Select.from_statement(). As subqueryload requires modifying the original statement, it's not compatible with the "from_statement" use case, especially for statements made against the _sql.text() construct. The behavior now is equivalent to that of 1.3 and previously, which is that the loader strategy silently degrades to not be used for such statements, typically falling back to using the lazyload strategy.

  References: #7505

sql

• [sql] [bug] [postgresql] Added additional rule to the system that determines TypeEngine implementations from Python literals to apply a second level of adjustment to the type, so that a Python datetime with or without tzinfo can set the timezone=True parameter on the returned DateTime object, as well as Time. This helps with some round-trip scenarios on type-sensitive PostgreSQL dialects such as asyncpg, psycopg3 (2.0 only).

  References: #7537

• [sql] [bug] Added an informative error message when a method object is passed to a SQL construct. Previously, when such a callable were passed, as is a common typographical error when dealing with method-chained SQL constructs, they were interpreted as "lambda SQL" targets to be invoked at compilation time, which would lead to silent failures. As this feature was not intended to be used with methods, method objects are now rejected.

  References: #7032

mypy

• [mypy] [bug] Fixed Mypy crash when running id daemon mode caused by a missing attribute on an internal mypy Var instance.

  References: #7321

asyncio

• [asyncio] [usecase] Added new method AdaptedConnection.run_async() to the DBAPI connection interface used by asyncio drivers, which allows methods to be called against the underlying "driver" connection directly within a sync-style function where the await keyword can't be used, such as within SQLAlchemy event handler functions. The method is analogous to the _asyncio.AsyncConnection.run_sync() method which translates async-style calls to sync-style. The method is useful for things like connection-pool on-connect handlers that need to invoke awaitable methods on the driver connection when it's first created.

  References: #7580

postgresql

• [postgresql] [usecase] Added string rendering to the postgresql.UUID datatype, so that stringifying a statement with "literal_binds" that uses this type will render an appropriate string value for the PostgreSQL backend. Pull request courtesy José Duarte.

  References: #7561

• [postgresql] [bug] [asyncpg] Improved support for asyncpg handling of TIME WITH TIMEZONE, which was not fully implemented.

  References: #7537

• [postgresql] [bug] [mssql] [reflection] Fixed reflection of covering indexes to report include_columns as part of the dialect_options entry in the reflected index dictionary, thereby enabling round trips from reflection->create to be complete. Included columns continue to also be present under the include_columns key for backwards compatibility.

  References: #7382

• [postgresql] [bug] Fixed handling of array of enum values which require escape characters.

  References: #7418

mysql

• [mysql] [change] Replace SHOW VARIABLES LIKE statement with equivalent SELECT @@variable in MySQL and MariaDB dialect initialization. This should avoid mutex contention caused by SHOW VARIABLES, improving initialization performance.

  References: #7518

• [mysql] [bug] Removed unnecessary dependency on PyMySQL from the asyncmy dialect. Pull request courtesy long2ice.

  References: #7567

1.4.29

Released: December 22, 2021

orm

• [orm] [usecase] Added _orm.Session.get.execution_options parameter which was previously missing from the _orm.Session.get() method.

  References: #7410

• [orm] [bug] Fixed issue in new "loader criteria" method _orm.PropComparator.and_() where usage with a loader strategy like _orm.selectinload() against a column that was a member of the .c. collection of a subquery object, where the subquery would be dynamically added to the FROM clause of the statement, would be subject to stale parameter values within the subquery in the SQL statement cache, as the process used by the loader strategy to replace the parameters at execution time would fail to accommodate the subquery when received in this form.

  References: #7489

• [orm] [bug] Fixed recursion overflow which could occur within ORM statement compilation when using either the _orm.with_loader_criteria() feature or the the _orm.PropComparator.and_() method within a loader strategy in conjunction with a subquery which referred to the same entity being altered by the criteria option, or loaded by the loader strategy. A check for coming across the same loader criteria option in a recursive fashion has been added to accommodate for this scenario.

  References: #7491

• [orm] [bug] [mypy] Fixed issue where the __class_getitem__() method of the generated declarative base class by _orm.as_declarative() would lead to inaccessible class attributes such as __table__, for cases where a Generic[T] style typing declaration were used in the class hierarchy. This is in continuation from the basic addition of __class_getitem__() in #7368. Pull request courtesy Kai Mueller.

  References: #7368, #7462

• [orm] [bug] [regression] Fixed caching-related issue where the use of a loader option of the form lazyload(aliased(A).bs).joinedload(B.cs) would fail to result in the joinedload being invoked for runs subsequent to the query being cached, due to a mismatch for the options / object path applied to the objects loaded for a query with a lead entity that used aliased().

  References: #7447

engine

• [engine] [bug] Corrected the error message for the AttributeError that's raised when attempting to write to an attribute on the _result.Row class, which is immutable. The previous message claimed the column didn't exist which is misleading.

  References: #7432

• [engine] [bug] [regression] Fixed regression in the _engine.make_url() function used to parse URL strings where the query string parsing would go into a recursion overflow if a Python 2 u'' string were used.

  References: #7446

mypy

• [mypy] [bug] Fixed mypy regression where the release of mypy 0.930 added additional internal checks to the format of "named types", requiring that they be fully qualified and locatable. This broke the mypy plugin for SQLAlchemy, raising an assertion error, as there was use of symbols such as __builtins__ and other un-locatable or unqualified names that previously had not raised any assertions.

  References: #7496

asyncio

• [asyncio] [usecase] Added _asyncio.async_engine_config() function to create an async engine from a configuration dict. This otherwise behaves the same as _sa.engine_from_config().

  References: #7301

mariadb

• [mariadb] [bug] Corrected the error classes inspected for the "is_disconnect" check for the mariadbconnector dialect, which was failing for disconnects that occurred due to common MySQL/MariaDB error codes such as 2006; the DBAPI appears to currently use the mariadb.InterfaceError exception class for disconnect errors such as error code 2006, which has been added to the list of classes checked.

  References: #7457

tests

• [tests] [bug] [regression] Fixed a regression in the test suite where the test called CompareAndCopyTest::test_all_present would fail on some platforms due to additional testing artifacts being detected. Pull request courtesy Nils Philippsen.

  References: #7450

1.4.28

Released: December 9, 2021

platform

• [platform] [bug] Python 3.10 has deprecated "distutils" in favor of explicit use of "setuptools" in PEP 632; SQLAlchemy's setup.py has replaced imports accordingly. However, since setuptools itself only recently added the replacement symbols mentioned in pep-632 as of November of 2021 in version 59.0.1, setup.py still has fallback imports to distutils, as SQLAlchemy 1.4 does not have a hard setuptools versioning requirement at this time. SQLAlchemy 2.0 is expected to use a full PEP 517 installation layout which will indicate appropriate setuptools versioning up front.

  References: #7311

orm

• [orm] [bug] [ext] Fixed issue where the internal cloning used by the _orm.PropComparator.any() method on a _orm.relationship() in the case where the related class also makes use of ORM polymorphic loading, would fail if a hybrid property on the related, polymorphic class were used within the criteria for the any() operation.

  References: #7425

• [orm] [bug] [mypy] Fixed issue where the _orm.as_declarative() decorator and similar functions used to generate the declarative base class would not copy the __class_getitem__() method from a given superclass, which prevented the use of pep-484 generics in conjunction with the Base class. Pull request courtesy Kai Mueller.

  References: #7368

• [orm] [bug] [regression] Fixed ORM regression where the new behavior of "eager loaders run on unexpire" added in #1763 would lead to loader option errors being raised inappropriately for the case where a single _orm.Query or _sql.Select were used to load multiple kinds of entities, along with loader options that apply to just one of those kinds of entity like a _orm.joinedload(), and later the objects would be refreshed from expiration, where the loader options would attempt to be applied to the mismatched object type and then raise an exception. The check for this mismatch now bypasses raising an error for this case.

  References: #7318

• [orm] [bug] User defined ORM options, such as those illustrated in the dogpile.caching example which subclass _orm.UserDefinedOption, by definition are handled on every statement execution and do not need to be considered as part of the cache key for the statement. Caching of the base ExecutableOption class has been modified so that it is no longer a HasCacheKey subclass directly, so that the presence of user defined option objects will not have the unwanted side effect of disabling statement caching. Only ORM specific loader and criteria options, which are all internal to SQLAlchemy, now participate within the caching system.

  References: #7394

• [orm] [bug] Fixed issue where mappings that made use of _orm.synonym() and potentially other kinds of "proxy" attributes would not in all cases successfully generate a cache key for their SQL statements, leading to degraded performance for those statements.

  References: #7394

• [orm] [bug] Fixed issue where a list mapped with _orm.relationship() would go into an endless loop if in-place added to itself, i.e. the += operator were used, as well as if .extend() were given the same list.

  References: #7389

• [orm] [bug] Fixed issue where if an exception occurred when the _orm.Session were to close the connection within the _orm.Session.commit() method, when using a context manager for _orm.Session.begin() , it would attempt a rollback which would not be possible as the _orm.Session was in between where the transaction is committed and the connection is then to be returned to the pool, raising the exception "this sessiontransaction is in the committed state". This exception can occur mostly in an asyncio context where CancelledError can be raised.

  References: #7388

• [orm] [deprecated] Deprecated an undocumented loader option syntax ".*", which appears to be no different than passing a single asterisk, and will emit a deprecation warning if used. This syntax may have been intended for something but there is currently no need for it.

  References: #4390

engine

• [engine] [usecase] Added support for copy() and deepcopy() to the _url.URL class. Pull request courtesy Tom Ritchford.

  References: #7400

sql

• [sql] [usecase] "Compound select" methods like _sql.Select.union(), _sql.Select.intersect_all() etc. now accept *other as an argument rather than other to allow for multiple additional SELECTs to be compounded with the parent statement at once. In particular, the change as applied to _sql.CTE.union() and _sql.CTE.union_all() now allow for a so-called "non-linear CTE" to be created with the _sql.CTE construct, whereas previously there was no way to have more than two CTE sub-elements in a UNION together while still correctly calling upon the CTE in recursive fashion. Pull request courtesy Eric Masseran.

  References: #7259

• [sql] [usecase] Support multiple clause elements in the _sql.Exists.where() method, unifying the api with the one presented by a normal _sql.select() construct.

  References: #7386

• [sql] [bug] [regression] Extended the TypeDecorator.cache_ok attribute and corresponding warning message if this flag is not defined, a behavior first established for TypeDecorator as part of #6436, to also take place for UserDefinedType, by generalizing the flag and associated caching logic to a new common base for these two types, ExternalType to create UserDefinedType.cache_ok.

  The change means any current UserDefinedType will now cause SQL statement caching to no longer take place for statements which make use of the datatype, along with a warning being emitted, unless the class defines the UserDefinedType.cache_ok flag as True. If the datatype cannot form a deterministic, hashable cache key derived from its arguments, the attribute may be set to False which will continue to keep caching disabled but will suppress the warning. In particular, custom datatypes currently used in packages such as SQLAlchemy-utils will need to implement this flag. The issue was observed as a result of a SQLAlchemy-utils datatype that is not currently cacheable.

  References: #7319

• [sql] [bug] Custom SQL elements, third party dialects, custom or third party datatypes will all generate consistent warnings when they do not clearly opt in or out of SQL statement caching, which is achieved by setting the appropriate attributes on each type of class. The warning links to documentation sections which indicate the appropriate approach for each type of object in order for caching to be enabled.

  References: #7394

• [sql] [bug] Fixed missing caching directives for a few lesser used classes in SQL Core which would cause [no key] to be logged for elements which made use of these.

  References: #7394

mypy

• [mypy] [bug] Fixed Mypy crash which would occur when using Mypy plugin against code which made use of _orm.declared_attr methods for non-mapped names like __mapper_args__, __table_args__, or other dunder names, as the plugin would try to interpret these as mapped attributes which would then be later mis-handled. As part of this change, the decorated function is still converted by the plugin into a generic assignment statement (e.g. __mapper_args__: Any) so that the argument signature can continue to be annotated in the same way one would for any other @classmethod without Mypy complaining about the wrong argument type for a method that isn't explicitly @classmethod.

  References: #7321

postgresql

• [postgresql] [bug] Fixed missing caching directives for _postgresql.hstore and _postgresql.array constructs which would cause [no key] to be logged for these elements.

  References: #7394

tests

• [tests] [bug] Implemented support for the test suite to run correctly under Pytest 7. Previously, only Pytest 6.x was supported for Python 3, however the version was not pinned on the upper bound in tox.ini. Pytest is not pinned in tox.ini to be lower than version 8 so that SQLAlchemy versions released with the current codebase will be able to be tested under tox without changes to the environment. Much thanks to the Pytest developers for their help with this issue.

1.4.27

Released: November 11, 2021

orm

• [orm] [bug] Fixed bug in "relationship to aliased class" feature introduced at relationship_aliased_class where it was not possible to create a loader strategy option targeting an attribute on the target using the _orm.aliased() construct directly in a second loader option, such as selectinload(A.aliased_bs).joinedload(aliased_b.cs), without explicitly qualifying using _orm.PropComparator.of_type() on the preceding element of the path. Additionally, targeting the non-aliased class directly would be accepted (inappropriately), but would silently fail, such as selectinload(A.aliased_bs).joinedload(B.cs); this now raises an error referring to the typing mismatch.

  References: #7224

• [orm] [bug] All _result.Result objects will now consistently raise _exc.ResourceClosedError if they are used after a hard close, which includes the "hard close" that occurs after calling "single row or value" methods like _result.Result.first() and _result.Result.scalar(). This was already the behavior of the most common class of result objects returned for Core statement executions, i.e. those based on _engine.CursorResult, so this behavior is not new. However, the change has been extended to properly accommodate for the ORM "filtering" result objects returned when using 2.0 style ORM queries, which would previously behave in "soft closed" style of returning empty results, or wouldn't actually "soft close" at all and would continue yielding from the underlying cursor.

  As part of this change, also added _result.Result.close() to the base _result.Result class and implemented it for the filtered result implementations that are used by the ORM, so that it is possible to call the _engine.CursorResult.close() method on the underlying _engine.CursorResult when the the yield_per execution option is in use to close a server side cursor before remaining ORM results have been fetched. This was again already available for Core result sets but the change makes it available for 2.0 style ORM results as well.

  References: #7274

• [orm] [bug] [regression] Fixed 1.4 regression where _orm.Query.filter_by() would not function correctly on a _orm.Query that was produced from _orm.Query.union(), _orm.Query.from_self() or similar.

  References: #7239

• [orm] [bug] Fixed issue where deferred polymorphic loading of attributes from a joined-table inheritance subclass would fail to populate the attribute correctly if the _orm.load_only() option were used to originally exclude that attribute, in the case where the load_only were descending from a relationship loader option. The fix allows that other valid options such as defer(..., raiseload=True) etc. still function as expected.

  References: #7304

• [orm] [bug] [regression] Fixed 1.4 regression where _orm.Query.filter_by() would not function correctly when _orm.Query.join() were joined to an entity which made use of _orm.PropComparator.of_type() to specify an aliased version of the target entity. The issue also applies to future style ORM queries constructed with _sql.select().

  References: #7244

engine

• [engine] [bug] Fixed issue in future _future.Connection object where the _future.Connection.execute() method would not accept a non-dict mapping object, such as SQLAlchemy's own RowMapping or other abc.collections.Mapping object as a parameter dictionary.

  References: #7291

• [engine] [bug] [regression] Fixed regression where the _engine.CursorResult.fetchmany() method would fail to autoclose a server-side cursor (i.e. when stream_results or yield_per is in use, either Core or ORM oriented results) when the results were fully exhausted.

  References: #7274

• [engine] [bug] Fixed issue in future _future.Engine where calling upon _future.Engine.begin() and entering the context manager would not close the connection if the actual BEGIN operation failed for some reason, such as an event handler raising an exception; this use case failed to be tested for the future version of the engine. Note that the "future" context managers which handle begin() blocks in Core and ORM don't actually run the "BEGIN" operation until the context managers are actually entered. This is different from the legacy version which runs the "BEGIN" operation up front.

  References: #7272

sql

• [sql] [usecase] Added TupleType to the top level sqlalchemy import namespace.

• [sql] [bug] [regression] Fixed regression where the row objects returned for ORM queries, which are now the normal _sql.Row objects, would not be interpreted by the _sql.ColumnOperators.in_() operator as tuple values to be broken out into individual bound parameters, and would instead pass them as single values to the driver leading to failures. The change to the "expanding IN" system now accommodates for the expression already being of type TupleType and treats values accordingly if so. In the uncommon case of using "tuple-in" with an untyped statement such as a textual statement with no typing information, a tuple value is detected for values that implement collections.abc.Sequence, but that are not str or bytes, as always when testing for Sequence.

  References: #7292

• [sql] [bug] Fixed issue where using the feature of using a string label for ordering or grouping described at tutorial_order_by_label would fail to function correctly if used on a CTE construct, when the CTE were embedded inside of an enclosing _sql.Select statement that itself was set up as a scalar subquery.

  References: #7269

• [sql] [bug] [regression] Fixed regression where the _sql.text() construct would no longer be accepted as a target case in the "whens" list within a _sql.case() construct. The regression appears related to an attempt to guard against some forms of literal values that were considered to be ambiguous when passed here; however, there's no reason the target cases shouldn't be interpreted as open-ended SQL expressions just like anywhere else, and a literal string or tuple will be converted to a bound parameter as would be the case elsewhere.

  References: #7287

schema

• [schema] [bug] Fixed issue in Table where the Table.implicit_returning parameter would not be accommodated correctly when passed along with Table.extend_existing to augment an existing Table.

  References: #7295

postgresql

• [postgresql] [usecase] [asyncpg] Added overridable methods PGDialect_asyncpg.setup_asyncpg_json_codec and PGDialect_asyncpg.setup_asyncpg_jsonb_codec codec, which handle the required task of registering JSON/JSONB codecs for these datatypes when using asyncpg. The change is that methods are broken out as individual, overridable methods to support third party dialects that need to alter or disable how these particular codecs are set up.

  References: #7284

• [postgresql] [bug] [asyncpg] Changed the asyncpg dialect to bind the Float type to the "float" PostgreSQL type instead of "numeric" so that the value float(inf) can be accommodated. Added test suite support for persistence of the "inf" value.

  References: #7283

• [postgresql] [pg8000] Improve array handling when using PostgreSQL with the pg8000 dialect.

  References: #7167

mysql

• [mysql] [bug] [mariadb] Reorganized the list of reserved words into two separate lists, one for MySQL and one for MariaDB, so that these diverging sets of words can be managed more accurately; adjusted the MySQL/MariaDB dialect to switch among these lists based on either explicitly configured or server-version-detected "MySQL" or "MariaDB" backend. Added all current reserved words through MySQL 8 and current MariaDB versions including recently added keywords like "lead" . Pull request courtesy Kevin Kirsche.

  References: #7167

• [mysql] [bug] Fixed issue in MySQL _mysql.Insert.on_duplicate_key_update() which would render the wrong column name when an expression were used in a VALUES expression. Pull request courtesy Cristian Sabaila.

  References: #7281

mssql

• [mssql] [bug] Adjusted the compiler's generation of "post compile" symbols including those used for "expanding IN" as well as for the "schema translate map" to not be based directly on plain bracketed strings with underscores, as this conflicts directly with SQL Server's quoting format of also using brackets, which produces false matches when the compiler replaces "post compile" and "schema translate" symbols. The issue created easy to reproduce examples both with the Inspector.get_schema_names() method when used in conjunction with the _engine.Connection.execution_options.schema_translate_map feature, as well in the unlikely case that a symbol overlapping with the internal name "POSTCOMPILE" would be used with a feature like "expanding in".

  References: #7300

1.4.26

Released: October 19, 2021

orm

• [orm] [bug] Improved the exception message generated when configuring a mapping with joined table inheritance where the two tables either have no foreign key relationships set up, or where they have multiple foreign key relationships set up. The message is now ORM specific and includes context that the _orm.Mapper.inherit_condition parameter may be needed particularly for the ambiguous foreign keys case.

• [orm] [bug] Fixed issue with _orm.with_loader_criteria() feature where ON criteria would not be added to a JOIN for a query of the form select(A).join(B), stating a target while making use of an implicit ON clause.

  References: #7189

• [orm] [bug] Fixed bug where the ORM "plugin", necessary for features such as _orm.with_loader_criteria() to work correctly, would not be applied to a _sql.select() which queried from an ORM column expression if it made use of the _sql.ColumnElement.label() modifier.

  References: #7205

• [orm] [bug] Add missing methods added in #6991 to _scoping.scoped_session and _asyncio.async_scoped_session().

  References: #7103

• [orm] [bug] An extra layer of warning messages has been added to the functionality of _orm.Query.join() and the ORM version of _sql.Select.join(), where a few places where "automatic aliasing" continues to occur will now be called out as a pattern to avoid, mostly specific to the area of joined table inheritance where classes that share common base tables are being joined together without using explicit aliases. One case emits a legacy warning for a pattern that's not recommended, the other case is fully deprecated.

  The automatic aliasing within ORM join() which occurs for overlapping mapped tables does not work consistently with all APIs such as _orm.contains_eager(), and rather than continue to try to make these use cases work everywhere, replacing with a more user-explicit pattern is clearer, less prone to bugs and simplifies SQLAlchemy's internals further.

  The warnings include links to the errors.rst page where each pattern is demonstrated along with the recommended pattern to fix.

  References: #6972, #6974

• [orm] [bug] Fixed bug where iterating a Result from a _orm.Session after that _orm.Session were closed would partially attach objects to that session in an essentially invalid state. It now raises an exception with a link to new documentation if an un-buffered result is iterated from a _orm.Session that was closed or otherwise had the _orm.Session.expunge_all() method called after that Result was generated. The prebuffer_rows execution option, as is used automatically by the asyncio extension for client-side result sets, may be used to produce a Result where the ORM objects are prebuffered, and in this case iterating the result will produce a series of detached objects.

  References: #7128

• [orm] [bug] Related to #7153, fixed an issue where result column lookups would fail for "adapted" SELECT statements that selected for "constant" value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue #6259 which removed all "adaption" for constants like NULL, "true", and "false" when rewriting expressions in a SQL statement, but this broke the case where the same adaption logic were used to resolve the constant to a labeled expression for the purposes of result set targeting.

  References: #7154

• [orm] [bug] [regression] Fixed regression where ORM loaded objects could not be pickled in cases where loader options making use of "*" were used in certain combinations, such as combining the _orm.joinedload() loader strategy with raiseload('*') of sub-elements.

  References: #7134

• [orm] [bug] [regression] Fixed regression where the use of a _hybrid.hybrid_property attribute or a mapped _orm.composite() attribute as a key passed to the _dml.Update.values() method for an ORM-enabled _dml.Update statement, as well as when using it via the legacy _orm.Query.update() method, would be processed for incoming ORM/hybrid/composite values within the compilation stage of the UPDATE statement, which meant that in those cases where caching occurred, subsequent invocations of the same statement would no longer receive the correct values. This would include not only hybrids that use the _hybrid.hybrid_property.update_expression() method, but any use of a plain hybrid attribute as well. For composites, the issue instead caused a non-repeatable cache key to be generated, which would break caching and could fill up the statement cache with repeated statements.

  The _dml.Update construct now handles the processing of key/value pairs passed to _dml.Update.values() and _dml.Update.ordered_values() up front when the construct is first generated, before the cache key has been generated so that the key/value pairs are processed each time, and so that the cache key is generated against the individual column/value pairs that will ultimately be used in the statement.

  References: #7209

• [orm] Passing a Query object to _orm.Session.execute() is not the intended use of this object, and will now raise a deprecation warning.

  References: #6284

examples

• [examples] [bug] Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs correctly; these examples had been missed when API changes like removing "passive" from _orm.Session.is_modified() were made as well as the _ormevents.SessionEvents.do_orm_execute() event hook were added.

  References: #7169

engine

• [engine] [bug] Fixed issue where the deprecation warning for the URL constructor which indicates that the URL.create() method should be used would not emit if a full positional argument list of seven arguments were passed; additionally, validation of URL arguments will now occur if the constructor is called in this way, which was being skipped previously.

  References: #7130

• [engine] [bug] [postgresql] The _reflection.Inspector.reflect_table() method now supports reflecting tables that do not have user defined columns. This allows _schema.MetaData.reflect() to properly complete reflection on databases that contain such tables. Currently, only PostgreSQL is known to support such a construct among the common database backends.

  References: #3247

• [engine] [bug] Implemented proper __reduce__() methods for all SQLAlchemy exception objects to ensure they all support clean round trips when pickling, as exception objects are often serialized for the purposes of various debugging tools.

  References: #7077

sql

• [sql] [bug] Fixed issue where SQL queries using the _functions.FunctionElement.within_group() construct could not be pickled, typically when using the sqlalchemy.ext.serializer extension but also for general generic pickling.

  References: #6520

• [sql] [bug] Repaired issue in new _sql.HasCTE.cte.nesting parameter introduced with #4123 where a recursive _sql.CTE using _sql.HasCTE.cte.recursive in typical conjunction with UNION would not compile correctly. Additionally makes some adjustments so that the _sql.CTE construct creates a correct cache key. Pull request courtesy Eric Masseran.

  References: #4123

• [sql] [bug] Account for the _sql.table.schema parameter passed to the _sql.table() construct, such that it is taken into account when accessing the _sql.TableClause.fullname attribute.

  References: #7061

• [sql] [bug] Fixed an inconsistency in the _sql.ColumnOperators.any_() / _sql.ColumnOperators.all_() functions / methods where the special behavior these functions have of "flipping" the expression such that the "ANY" / "ALL" expression is always on the right side would not function if the comparison were against the None value, that is, "column.any_() == None" should produce the same SQL expression as "null() == column.any_()". Added more docs to clarify this as well, plus mentions that any_() / all_() generally supersede the ARRAY version "any()" / "all()".

  References: #7140

• [sql] [bug] [regression] Fixed issue where "expanding IN" would fail to function correctly with datatypes that use the _types.TypeEngine.bind_expression() method, where the method would need to be applied to each element of the IN expression rather than the overall IN expression itself.

  References: #7177

• [sql] [bug] Adjusted the "column disambiguation" logic that's new in 1.4, where the same expression repeated gets an "extra anonymous" label, so that the logic more aggressively deduplicates those labels when the repeated element is the same Python expression object each time, as occurs in cases like when using "singleton" values like _sql.null(). This is based on the observation that at least some databases (e.g. MySQL, but not SQLite) will raise an error if the same label is repeated inside of a subquery.

  References: #7153

mypy

• [mypy] [bug] Fixed issue in mypy plugin to improve upon some issues detecting Enum() SQL types containing custom Python enumeration classes. Pull request courtesy Hiroshi Ogawa.

  References: #6435

postgresql

• [postgresql] [bug] Added a "disconnect" condition for the "SSL SYSCALL error: Bad address" error message as reported by psycopg2. Pull request courtesy Zeke Brechtel.

  References: #5387

• [postgresql] [bug] [regression] Fixed issue where IN expressions against a series of array elements, as can be done with PostgreSQL, would fail to function correctly due to multiple issues within the "expanding IN" feature of SQLAlchemy Core that was standardized in version 1.4. The psycopg2 dialect now makes use of the _types.TypeEngine.bind_expression() method with _types.ARRAY to portably apply the correct casts to elements. The asyncpg dialect was not affected by this issue as it applies bind-level casts at the driver level rather than at the compiler level.

  References: #7177

mysql

• [mysql] [bug] [mariadb] Fixes to accommodate for the MariaDB 10.6 series, including backwards incompatible changes in both the mariadb-connector Python driver (supported on SQLAlchemy 1.4 only) as well as the native 10.6 client libraries that are used automatically by the mysqlclient DBAPI (applies to both 1.3 and 1.4). The "utf8mb3" encoding symbol is now reported by these client libraries when the encoding is stated as "utf8", leading to lookup and encoding errors within the MySQL dialect that does not expect this symbol. Updates to both the MySQL base library to accommodate for this utf8mb3 symbol being reported as well as to the test suite. Thanks to Georg Richter for support.

  This change is also backported to: 1.3.25

  References: #7115, #7136

• [mysql] [bug] Fixed issue in MySQL _mysql.match() construct where passing a clause expression such as _sql.bindparam() or other SQL expression for the "against" parameter would fail. Pull request courtesy Anton Kovalevich.

  References: #7144

• [mysql] [bug] Fixed installation issue where the sqlalchemy.dialects.mysql module would not be importable if "greenlet" were not installed.

  References: #7204

mssql

• [mssql] [usecase] Added reflection support for SQL Server foreign key options, including "ON UPDATE" and "ON DELETE" values of "CASCADE" and "SET NULL".

• [mssql] [bug] Fixed issue with Inspector.get_foreign_keys() where foreign keys were omitted if they were established against a unique index instead of a unique constraint.

  References: #7160

• [mssql] [bug] Fixed issue with Inspector.has_table() where it would return False if a local temp table with the same name from a different session happened to be returned first when querying tempdb. This is a continuation of #6910 which accounted for the temp table existing only in the alternate session and not the current one.

  References: #7168

• [mssql] [bug] [regression] Fixed bug in SQL Server _mssql.DATETIMEOFFSET datatype where the ODBC implementation would not generate the correct DDL, for cases where the type were converted using the dialect.type_descriptor() method, the usage of which is illustrated in some documented examples for TypeDecorator, though not necessary for most datatypes. Regression was introduced by #6366. As part of this change, the full list of SQL Server date types have been amended to return a "dialect impl" that generates the same DDL name as the supertype.

  References: #7129

1.4.25

Released: September 22, 2021

platform

• [platform] [bug] [regression] Fixed regression due to #7024 where the reorganization of the "platform machine" names used by the greenlet dependency mis-spelled "aarch64" and additionally omitted uppercase "AMD64" as is needed for Windows machines. Pull request courtesy James Dow.

  References: #7024

1.4.24

Released: September 22, 2021

platform

• [platform] [bug] Further adjusted the "greenlet" package specifier in setup.cfg to use a long chain of "or" expressions, so that the comparison of platform_machine to a specific identifier matches only the complete string.

  References: #7024

orm

• [orm] [usecase] Added loader options to _orm.Session.merge() and _asyncio.AsyncSession.merge() via a new _orm.Session.merge.options parameter, which will apply the given loader options to the get() used internally by merge, allowing eager loading of relationships etc. to be applied when the merge process loads a new object. Pull request courtesy Daniel Stone.

  References: #6955

• [orm] [bug] [regression] Fixed ORM issue where column expressions passed to query() or ORM-enabled select() would be deduplicated on the identity of the object, such as a phrase like select(A.id, null(), null()) would produce only one "NULL" expression, which previously was not the case in 1.3. However, the change also allows for ORM expressions to render as given as well, such as select(A.data, A.data) will produce a result row with two columns.

  References: #6979

• [orm] [bug] [regression] Fixed issue in recently repaired Query.with_entities() method where the flag that determines automatic uniquing for legacy ORM Query objects only would be set to True inappropriately in cases where the with_entities() call would be setting the Query to return column-only rows, which are not uniqued.

  References: #6924

engine

• [engine] [usecase] [asyncio] Improve the interface used by adapted drivers, like the asyncio ones, to access the actual connection object returned by the driver.

  The _ConnectionFairy object has two new attributes:

  -   `_ConnectionFairy.dbapi_connection` always represents a DBAPI
      compatible object. For pep-249 drivers, this is the DBAPI connection as
      it always has been, previously accessed under the `.connection`
      attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249
      interface, the returned object will normally be a SQLAlchemy adaption
      object called `_engine.AdaptedConnection`.
  
  -   `_ConnectionFairy.driver_connection` always represents the actual
      connection object maintained by the third party pep-249 DBAPI or async
      driver in use. For standard pep-249 DBAPIs, this will always be the same
      object as that of the `dbapi_connection`. For an asyncio driver, it
      will be the underlying asyncio-only connection object.
  

  The .connection attribute remains available and is now a legacy alias of .dbapi_connection.

  References: #6832

• [engine] [usecase] [orm] Added new methods _orm.Session.scalars(), _engine.Connection.scalars(), _asyncio.AsyncSession.scalars() and _asyncio.AsyncSession.stream_scalars(), which provide a short cut to the use case of receiving a row-oriented _result.Result object and converting it to a _result.ScalarResult object via the _engine.Result.scalars() method, to return a list of values rather than a list of rows. The new methods are analogous to the long existing _orm.Session.scalar() and _engine.Connection.scalar() methods used to return a single value from the first row only. Pull request courtesy Miguel Grinberg.

  References: #6990

• [engine] [bug] [regression] Fixed issue where the ability of the _events.ConnectionEvents.before_execute() method to alter the SQL statement object passed, returning the new object to be invoked, was inadvertently removed. This behavior has been restored.

  References: #6913

• [engine] [bug] Ensure that str() is called on the an _url.URL.create.password argument, allowing usage of objects that implement the __str__() method as password attributes. Also clarified that one such object is not appropriate to dynamically change the password for each database connection; the approaches at engines_dynamic_tokens should be used instead.

  References: #6958

• [engine] [bug] Fixed issue in _engine.URL where validation of "drivername" would not appropriately respond to the None value where a string were expected.

  References: #6983

• [engine] [bug] [postgresql] Fixed issue where an engine that had _sa.create_engine.implicit_returning set to False would fail to function when PostgreSQL's "fast insertmany" feature were used in conjunction with a Sequence, as well as if any kind of "executemany" with "return_defaults()" were used in conjunction with a Sequence. Note that PostgreSQL "fast insertmany" uses "RETURNING" by definition, when the SQL statement is passed to the driver; overall, the _sa.create_engine.implicit_returning flag is legacy and has no real use in modern SQLAlchemy, and will be deprecated in a separate change.

  References: #6963

sql

• [sql] [usecase] Added new parameter _sql.HasCTE.cte.nesting to the _sql.CTE constructor and _sql.HasCTE.cte() method, which flags the CTE as one which should remain nested within an enclosing CTE, rather than being moved to the top level of the outermost SELECT. While in the vast majority of cases there is no difference in SQL functionality, users have identified various edge-cases where true nesting of CTE constructs is desirable. Much thanks to Eric Masseran for lots of work on this intricate feature.

  References: #4123

• [sql] [bug] Implemented missing methods in _functions.FunctionElement which, while unused, would lead pylint to report them as unimplemented abstract methods.

  References: #7052

• [sql] [bug] Fixed a two issues where combinations of select() and join() when adapted to form a copy of the element would not completely copy the state of all column objects associated with subqueries. A key problem this caused is that usage of the _sql.ClauseElement.params() method (which should probably be moved into a legacy category as it is inefficient and error prone) would leave copies of the old _sql.BindParameter objects around, leading to issues in correctly setting the parameters at execution time.

  References: #7055

• [sql] [bug] Fixed issue related to new _sql.HasCTE.add_cte() feature where pairing two "INSERT..FROM SELECT" statements simultaneously would lose track of the two independent SELECT statements, leading to the wrong SQL.

  References: #7036

• [sql] [bug] Fixed issue where using ORM column expressions as keys in the list of dictionaries passed to _sql.Insert.values() for "multi-valued insert" would not be processed correctly into the correct column expressions.

  References: #7060

mypy

• [mypy] [bug] Fixed issue where mypy plugin would crash when interpreting a query_expression() construct.

  References: #6950

• [mypy] [bug] Fixed issue in mypy plugin where columns on a mixin would not be correctly interpreted if the mapped class relied upon a __tablename__ routine that came from a superclass.

  References: #6937

asyncio

• [asyncio] [feature] [mysql] Added initial support for the asyncmy asyncio database driver for MySQL and MariaDB. This driver is very new, however appears to be the only current alternative to the aiomysql driver which currently appears to be unmaintained and is not working with current Python versions. Much thanks to long2ice for the pull request for this dialect.

  References: #6993

• [asyncio] [usecase] The _asyncio.AsyncSession now supports overriding which _orm.Session it uses as the proxied instance. A custom Session class can be passed using the AsyncSession.sync_session_class parameter or by subclassing the AsyncSession and specifying a custom AsyncSession.sync_session_class.

  References: #6746

• [asyncio] [bug] Fixed a bug in _asyncio.AsyncSession.execute() and _asyncio.AsyncSession.stream() that required execution_options to be an instance of immutabledict when defined. It now correctly accepts any mapping.

  References: #6943

• [asyncio] [bug] Added missing **kw arguments to the _asyncio.AsyncSession.connection() method.

• [asyncio] [bug] Deprecate usage of _orm.scoped_session with asyncio drivers. When using Asyncio the _asyncio.async_scoped_session should be used instead.

  References: #6746

postgresql

• [postgresql] [bug] Qualify version() call to avoid shadowing issues if a different search path is configured by the user.

  References: #6912

• [postgresql] [bug] The _postgresql.ENUM datatype is PostgreSQL-native and therefore should not be used with the native_enum=False flag. This flag is now ignored if passed to the _postgresql.ENUM datatype and a warning is emitted; previously the flag would cause the type object to fail to function correctly.

  References: #6106

sqlite

• [sqlite] [bug] Fixed bug where the error message for SQLite invalid isolation level on the pysqlite driver would fail to indicate that "AUTOCOMMIT" is one of the valid isolation levels.

mssql

• [mssql] [bug] [reflection] Fixed an issue where _reflection.has_table() returned True for local temporary tables that actually belonged to a different SQL Server session (connection). An extra check is now performed to ensure that the temp table detected is in fact owned by the current session.

  References: #6910

oracle

• [oracle] [bug] [performance] Added a CAST(VARCHAR2(128)) to the "table name", "owner", and other DDL-name parameters as used in reflection queries against Oracle system views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable indexing to take place against these columns, as they previously would be implicitly handled as NVARCHAR2 due to Python's use of Unicode for strings; these columns are documented in all Oracle versions as being VARCHAR2 with lengths varying from 30 to 128 characters depending on server version. Additionally, test support has been enabled for Unicode-named DDL structures against Oracle databases.

  References: #4486

1.4.23

Released: August 18, 2021

general

• [general] [bug] The setup requirements have been modified such greenlet is a default requirement only for those platforms that are well known for greenlet to be installable and for which there is already a pre-built binary on pypi; the current list is x86_64 aarch64 ppc64le amd64 win32. For other platforms, greenlet will not install by default, which should enable installation and test suite running of SQLAlchemy 1.4 on platforms that don't support greenlet, excluding any asyncio features. In order to install with the greenlet dependency included on a machine architecture outside of the above list, the [asyncio] extra may be included by running pip install sqlalchemy[asyncio] which will then attempt to install greenlet.

  Additionally, the test suite has been repaired so that tests can complete fully when greenlet is not installed, with appropriate skips for asyncio-related tests.

  References: #6136

orm

• [orm] [usecase] Added new attribute _sql.Select.columns_clause_froms that will retrieve the FROM list implied by the columns clause of the _sql.Select statement. This differs from the old _sql.Select.froms collection in that it does not perform any ORM compilation steps, which necessarily deannotate the FROM elements and do things like compute joinedloads etc., which makes it not an appropriate candidate for the _sql.Select.select_from() method. Additionally adds a new parameter _sql.Select.with_only_columns.maintain_column_froms that transfers this collection to _sql.Select.select_from() before replacing the columns collection.

  In addition, the _sql.Select.froms is renamed to _sql.Select.get_final_froms(), to stress that this collection is not a simple accessor and is instead calculated given the full state of the object, which can be an expensive call when used in an ORM context.

  Additionally fixes a regression involving the _orm.with_only_columns() function to support applying criteria to column elements that were replaced with either _sql.Select.with_only_columns() or _orm.Query.with_entities() , which had broken as part of #6503 released in 1.4.19.

  References: #6808

• [orm] [bug] [sql] Fixed issue where a bound parameter object that was "cloned" would cause a name conflict in the compiler, if more than one clone of this parameter were used at the same time in a single statement. This could occur in particular with things like ORM single table inheritance queries that indicated the same "discriminator" value multiple times in one query.

  References: #6824

• [orm] [bug] Fixed issue in loader strategies where the use of the _orm.Load.options() method, particularly when nesting multiple calls, would generate an overly long and more importantly non-deterministic cache key, leading to very large cache keys which were also not allowing efficient cache usage, both in terms of total memory used as well as number of entries used in the cache itself.

  References: #6869

• [orm] [bug] Revised the means by which the _orm.ORMExecuteState.user_defined_options accessor receives _orm.UserDefinedOption and related option objects from the context, with particular emphasis on the "selectinload" on the loader strategy where this previously was not working; other strategies did not have this problem. The objects that are associated with the current query being executed, and not that of a query being cached, are now propagated unconditionally. This essentially separates them out from the "loader strategy" options which are explicitly associated with the compiled state of a query and need to be used in relation to the cached query.

  The effect of this fix is that a user-defined option, such as those used by the dogpile.caching example as well as for other recipes such as defining a "shard id" for the horizontal sharing extension, will be correctly propagated to eager and lazy loaders regardless of whether a cached query was ultimately invoked.

  References: #6887

• [orm] [bug] Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled.

  References: #6812

• [orm] [bug] Fixed issue in _orm.selectinload() where use of the new _orm.PropComparator.and_() feature within options that were nested more than one level deep would fail to update bound parameter values that were in the nested criteria, as a side effect of SQL statement caching.

  References: #6881

• [orm] [bug] Adjusted ORM loader internals to no longer use the "lambda caching" system that was added in 1.4, as well as repaired one location that was still using the previous "baked query" system for a query. The lambda caching system remains an effective way to reduce the overhead of building up queries that have relatively fixed usage patterns. In the case of loader strategies, the queries used are responsible for moving through lots of arbitrary options and criteria, which is both generated and sometimes consumed by end-user code, that make the lambda cache concept not any more efficient than not using it, at the cost of more complexity. In particular the problems noted by #6881 and #6887 are made are made considerably less complicated by removing this feature internally.

  References: #6079, #6889

• [orm] [bug] Fixed an issue where the _orm.Bundle construct would not create proper cache keys, leading to inefficient use of the query cache. This had some impact on the "selectinload" strategy and was identified as part of #6889.

  References: #6889

sql

• [sql] [bug] Fix issue in _sql.CTE where new _sql.HasCTE.add_cte() method added in version 1.4.21 / #6752 failed to function correctly for "compound select" structures such as _sql.union(), _sql.union_all(), _sql.except(), etc. Pull request courtesy Eric Masseran.

  References: #6752

• [sql] [bug] Fixed an issue in the CacheKey.to_offline_string() method used by the dogpile.caching example where attempting to create a proper cache key from the special "lambda" query generated by the lazy loader would fail to include the parameter values, leading to an incorrect cache key.

  References: #6858

• [sql] [bug] Adjusted the "from linter" warning feature to accommodate for a chain of joins more than one level deep where the ON clauses don't explicitly match up the targets, such as an expression such as "ON TRUE". This mode of use is intended to cancel the cartesian product warning simply by the fact that there's a JOIN from "a to b", which was not working for the case where the chain of joins had more than one element.

  References: #6886

• [sql] [bug] Fixed issue in lambda caching system where an element of a query that produces no cache key, like a custom option or clause element, would still populate the expression in the "lambda cache" inappropriately.

schema

• [schema] [enum] Unify behaviour _schema.Enum in native and non-native implementations regarding the accepted values for an enum with aliased elements. When _schema.Enum.omit_aliases is False all values, alias included, are accepted as valid values. When _schema.Enum.omit_aliases is True only non aliased values are accepted as valid values.

  References: #6146

mypy

• [mypy] [usecase] Added support for SQLAlchemy classes to be defined in user code using "generic class" syntax as defined by sqlalchemy2-stubs, e.g. Column[String], without the need for qualifying these constructs within a TYPE_CHECKING block by implementing the Python special method __class_getitem__(), which allows this syntax to pass without error at runtime.

  References: #6759, #6804

postgresql

• [postgresql] [bug] Added the "is_comparison" flag to the PostgreSQL "overlaps", "contained_by", "contains" operators, so that they work in relevant ORM contexts as well as in conjunction with the "from linter" feature.

  References: #6886

mssql

• [mssql] [bug] [sql] Fixed issue where the literal_binds compiler flag, as used externally to render bound parameters inline, would fail to work when used with a certain class of parameters known as "literal_execute", which covers things like LIMIT and OFFSET values for dialects where the drivers don't allow a bound parameter, such as SQL Server's "TOP" clause. The issue locally seemed to affect only the MSSQL dialect.

  References: #6863

misc

• [bug] [ext] Fixed issue where the horizontal sharding extension would not correctly accommodate for a plain textual SQL statement passed to _orm.Session.execute().

  References: #6816

1.4.22

Released: July 21, 2021

orm

• [orm] [bug] Fixed issue in new _schema.Table.table_valued() method where the resulting _sql.TableValuedColumn construct would not respond correctly to alias adaptation as is used throughout the ORM, such as for eager loading, polymorphic loading, etc.

  References: #6775

• [orm] [bug] Fixed issue where usage of the _result.Result.unique() method with an ORM result that included column expressions with unhashable types, such as JSON or ARRAY using non-tuples would silently fall back to using the id() function, rather than raising an error. This now raises an error when the _result.Result.unique() method is used in a 2.0 style ORM query. Additionally, hashability is assumed to be True for result values of unknown type, such as often happens when using SQL functions of unknown return type; if values are truly not hashable then the hash() itself will raise.

  For legacy ORM queries, since the legacy _orm.Query object uniquifies in all cases, the old rules remain in place, which is to use id() for result values of unknown type as this legacy uniquing is mostly for the purpose of uniquing ORM entities and not column values.

  References: #6769

• [orm] [bug] Fixed an issue where clearing of mappers during things like test suite teardowns could cause a "dictionary changed size" warning during garbage collection, due to iteration of a weak-referencing dictionary. A list() has been applied to prevent concurrent GC from affecting this operation.

  References: #6771

• [orm] [bug] [regression] Fixed critical caching issue where the ORM's persistence feature using INSERT..RETURNING would cache an incorrect query when mixing the "bulk save" and standard "flush" forms of INSERT.

  References: #6793

engine

• [engine] [bug] Added some guards against KeyError in the event system to accommodate the case that the interpreter is shutting down at the same time _engine.Engine.dispose() is being called, which would cause stack trace warnings.

  References: #6740

sql

• [sql] [bug] Fixed issue where use of the _sql.case.whens parameter passing a dictionary positionally and not as a keyword argument would emit a 2.0 deprecation warning, referring to the deprecation of passing a list positionally. The dictionary format of "whens", passed positionally, is still supported and was accidentally marked as deprecated.

  References: #6786

• [sql] [bug] Fixed issue where type-specific bound parameter handlers would not be called upon in the case of using the _sql.Insert.values() method with the Python None value; in particular, this would be noticed when using the _types.JSON datatype as well as related PostgreSQL specific types such as _postgresql.JSONB which would fail to encode the Python None value into JSON null, however the issue was generalized to any bound parameter handler in conjunction with this specific method of _sql.Insert.

  References: #6770

1.4.21

Released: July 14, 2021

orm

• [orm] [usecase] Modified the approach used for history tracking of scalar object relationships that are not many-to-one, i.e. one-to-one relationships that would otherwise be one-to-many. When replacing a one-to-one value, the "old" value that would be replaced is no longer loaded immediately, and is instead handled during the flush process. This eliminates an historically troublesome lazy load that otherwise often occurs when assigning to a one-to-one attribute, and is particularly troublesome when using "lazy='raise'" as well as asyncio use cases.

  This change does cause a behavioral change within the _orm.AttributeEvents.set() event, which is nonetheless currently documented, which is that the event applied to such a one-to-one attribute will no longer receive the "old" parameter if it is unloaded and the _orm.relationship.active_history flag is not set. As is documented in _orm.AttributeEvents.set(), if the event handler needs to receive the "old" value when the event fires off, the active_history flag must be established either with the event listener or with the relationship. This is already the behavior with other kinds of attributes such as many-to-one and column value references.

  The change additionally will defer updating a backref on the "old" value in the less common case that the "old" value is locally present in the session, but isn't loaded on the relationship in question, until the next flush occurs. If this causes an issue, again the normal _orm.relationship.active_history flag can be set to True on the relationship.

  References: #6708

• [orm] [bug] [regression] Fixed regression caused in 1.4.19 due to #6503 and related involving _orm.Query.with_entities() where the new structure used would be inappropriately transferred to an enclosing _orm.Query when making use of set operations such as _orm.Query.union(), causing the JOIN instructions within to be applied to the outside query as well.

  References: #6698

• [orm] [bug] [regression] Fixed regression which appeared in version 1.4.3 due to #6060 where rules that limit ORM adaptation of derived selectables interfered with other ORM-adaptation based cases, in this case when applying adaptations for a _orm.with_polymorphic() against a mapping which uses a _orm.column_property() which in turn makes use of a scalar select that includes a _orm.aliased() object of the mapped table.

  References: #6762

• [orm] [regression] Fixed ORM regression where ad-hoc label names generated for hybrid properties and potentially other similar types of ORM-enabled expressions would usually be propagated outwards through subqueries, allowing the name to be retained in the final keys of the result set even when selecting from subqueries. Additional state is now tracked in this case that isn't lost when a hybrid is selected out of a Core select / subquery.

  References: #6718

sql

• [sql] [usecase] Added new method _sql.HasCTE.add_cte() to each of the _sql.select(), _sql.insert(), _sql.update() and _sql.delete() constructs. This method will add the given _sql.CTE as an "independent" CTE of the statement, meaning it renders in the WITH clause above the statement unconditionally even if it is not otherwise referenced in the primary statement. This is a popular use case on the PostgreSQL database where a CTE is used for a DML statement that runs against database rows independently of the primary statement.

  References: #6752

• [sql] [bug] Fixed issue in CTE constructs where a recursive CTE that referred to a SELECT that has duplicate column names, which are typically deduplicated using labeling logic in 1.4, would fail to refer to the deduplicated label name correctly within the WITH clause.

  References: #6710

• [sql] [bug] [regression] Fixed regression where the _sql.tablesample() construct would fail to be executable when constructed given a floating-point sampling value not embedded within a SQL function.

  References: #6735

postgresql

• [postgresql] [bug] Fixed issue in _postgresql.Insert.on_conflict_do_nothing() and _postgresql.Insert.on_conflict_do_update() where the name of a unique constraint passed as the constraint parameter would not be properly truncated for length if it were based on a naming convention that generated a too-long name for the PostgreSQL max identifier length of 63 characters, in the same way which occurs within a CREATE TABLE statement.

  References: #6755

• [postgresql] [bug] Fixed issue where the PostgreSQL ENUM datatype as embedded in the ARRAY datatype would fail to emit correctly in create/drop when the schema_translate_map feature were also in use. Additionally repairs a related issue where the same schema_translate_map feature would not work for the ENUM datatype in combination with a CAST, that's also intrinsic to how the ARRAY(ENUM) combination works on the PostgreSQL dialect.

  References: #6739

• [postgresql] [bug] Fixed issue in _postgresql.Insert.on_conflict_do_nothing() and _postgresql.Insert.on_conflict_do_update() where the name of a unique constraint passed as the constraint parameter would not be properly quoted if it contained characters which required quoting.

  References: #6696

mssql

• [mssql] [bug] [regression] Fixed regression where the special dotted-schema name handling for the SQL Server dialect would not function correctly if the dotted schema name were used within the schema_translate_map feature.

  References: #6697

1.4.20

Released: June 28, 2021

orm

• [orm] [bug] [regression] Fixed regression in ORM regarding an internal reconstitution step for the _orm.with_polymorphic() construct, when the user-facing object is garbage collected as the query is processed. The reconstitution was not ensuring the sub-entities for the "polymorphic" case were handled, leading to an AttributeError.

  References: #6680

• [orm] [bug] [regression] Adjusted _orm.Query.union() and similar set operations to be correctly compatible with the new capabilities just added in #6661, with SQLAlchemy 1.4.19, such that the SELECT statements rendered as elements of the UNION or other set operation will include directly mapped columns that are mapped as deferred; this both fixes a regression involving unions with multiple levels of nesting that would produce a column mismatch, and also allows the _orm.undefer() option to be used at the top level of such a _orm.Query without having to apply the option to each of the elements within the UNION.

  References: #6678

• [orm] [bug] Adjusted the check in the mapper for a callable object that is used as a @validates validator function or a @reconstructor reconstruction function, to check for "callable" more liberally such as to accommodate objects based on fundamental attributes like __func__ and __call___, rather than testing for MethodType / FunctionType, allowing things like cython functions to work properly. Pull request courtesy Miłosz Stypiński.

  References: #6538

engine

• [engine] [bug] Fixed an issue in the C extension for the _result.Row class which could lead to a memory leak in the unlikely case of a _result.Row object which referred to an ORM object that then was mutated to refer back to the Row itself, creating a cycle. The Python C APIs for tracking GC cycles has been added to the native _result.Row implementation to accommodate for this case.

  References: #5348

• [engine] [bug] Fixed old issue where a _sql.select() made against the token "*", which then yielded exactly one column, would fail to correctly organize the cursor.description column name into the keys of the result object.

  References: #6665

sql

• [sql] [usecase] Add a impl parameter to _types.PickleType constructor, allowing any arbitary type to be used in place of the default implementation of _types.LargeBinary. Pull request courtesy jason3gb.

  References: #6646

• [sql] [bug] [orm] Fixed the class hierarchy for the _schema.Sequence and the more general _schema.DefaultGenerator base, as these are "executable" as statements they need to include _sql.Executable in their hierarchy, not just _roles.StatementRole as was applied arbitrarily to _schema.Sequence previously. The fix allows _schema.Sequence to work in all .execute() methods including with _orm.Session.execute() which was not working in the case that a _orm.SessionEvents.do_orm_execute() handler was also established.

  References: #6668

schema

• [schema] [bug] Fixed issue where passing None for the value of _schema.Table.prefixes would not store an empty list, but rather the constant None, which may be unexpected by third party dialects. The issue is revealed by a usage in recent versions of Alembic that are passing None for this value. Pull request courtesy Kai Mueller.

  References: #6685

mysql

• [mysql] [usecase] Made a small adjustment in the table reflection feature of the MySQL dialect to accommodate for alternate MySQL-oriented databases such as TiDB which include their own "comment" directives at the end of a constraint directive within "CREATE TABLE" where the format doesn't have the additional space character after the comment, in this case the TiDB "clustered index" feature. Pull request courtesy Daniël van Eeden.

  References: #6659

misc

• [bug] [ext] [regression] Fixed regression in sqlalchemy.ext.automap extension such that the use case of creating an explicit mapped class to a table that is also the _orm.relationship.secondary element of a _orm.relationship() that automap will be generating would emit the "overlaps" warnings introduced in 1.4 and discussed at error_qzyx. While generating this case from automap is still subject to the same caveats that the "overlaps" warning refers towards, as automap is intended for more ad-hoc use cases, the condition which produces the warning is disabled when a many-to-many relationship with this particular pattern is generated.

  References: #6679

1.4.19

Released: June 22, 2021

orm

• [orm] [bug] [regression] Fixed further regressions in the same area as that of #6052 where loader options as well as invocations of methods like _orm.Query.join() would fail if the left side of the statement for which the option/join depends upon were replaced by using the _orm.Query.with_entities() method, or when using 2.0 style queries when using the _sql.Select.with_only_columns() method. A new set of state has been added to the objects which tracks the "left" entities that the options / join were made against which is memoized when the lead entities are changed.

  References: #6253, #6503

• [orm] [bug] Refined the behavior of ORM subquery rendering with regards to deferred columns and column properties to be more compatible with that of 1.3 while also providing for 1.4's newer features. As a subquery in 1.4 does not make use of loader options, including _orm.undefer(), a subquery that is against an ORM entity with deferred attributes will now render those deferred attributes that refer directly to mapped table columns, as these are needed in the outer SELECT if that outer SELECT makes use of these columns; however a deferred attribute that refers to a composed SQL expression as we normally do with _orm.column_property() will not be part of the subquery, as these can be selected explicitly if needed in the subquery. If the entity is being SELECTed from this subquery, the column expression can still render on "the outside" in terms of the derived subquery columns. This produces essentially the same behavior as when working with 1.3. However in this case the fix has to also make sure that the .selected_columns collection of an ORM-enabled _sql.select() also follows these rules, which in particular allows recursive CTEs to render correctly in this scenario, which were previously failing to render correctly due to this issue.

  References: #6661

sql

• [sql] [bug] Fixed issue in CTE constructs mostly relevant to ORM use cases where a recursive CTE against "anonymous" labels such as those seen in ORM column_property() mappings would render in the WITH RECURSIVE xyz(...) section as their raw internal label and not a cleanly anonymized name.

  References: #6663

mypy

• [mypy] [bug] Fixed issue in mypy plugin where class info for a custom declarative base would not be handled correctly on a cached mypy pass, leading to an AssertionError being raised.

  References: #6476

asyncio

• [asyncio] [usecase] Implemented _asyncio.async_scoped_session to address some asyncio-related incompatibilities between _orm.scoped_session and _asyncio.AsyncSession, in which some methods (notably the _asyncio.async_scoped_session.remove() method) should be used with the await keyword.

  References: #6583

• [asyncio] [bug] [postgresql] Fixed bug in asyncio implementation where the greenlet adaptation system failed to propagate BaseException subclasses, most notably including asyncio.CancelledError, to the exception handling logic used by the engine to invalidate and clean up the connection, thus preventing connections from being correctly disposed when a task was cancelled.

  References: #6652

postgresql

• [postgresql] [bug] [oracle] Fixed issue where the INTERVAL datatype on PostgreSQL and Oracle would produce an AttributeError when used in the context of a comparison operation against a timedelta() object. Pull request courtesy MajorDallas.

  References: #6649

• [postgresql] [bug] Fixed issue where the pool "pre ping" feature would implicitly start a transaction, which would then interfere with custom transactional flags such as PostgreSQL's "read only" mode when used with the psycopg2 driver.

  References: #6621

mysql

• [mysql] [usecase] Added new construct _mysql.match, which provides for the full range of MySQL's MATCH operator including multiple column support and modifiers. Pull request courtesy Anton Kovalevich.

  References: #6132

mssql

• [mssql] [change] Made improvements to the server version regexp used by the pymssql dialect to prevent a regexp overflow in case of an invalid version string.

  References: #6253, #6503

• [mssql] [bug] Fixed bug where the "schema_translate_map" feature would fail to function correctly in conjunction with an INSERT into a table that has an IDENTITY column, where the value of the IDENTITY column were specified in the values of the INSERT thus triggering SQLAlchemy's feature of setting IDENTITY INSERT to "on"; it's in this directive where the schema translate map would fail to be honored.

  References: #6658

1.4.18

Released: June 10, 2021

orm

• [orm] [performance] [bug] [regression] Fixed regression involving how the ORM would resolve a given mapped column to a result row, where under cases such as joined eager loading, a slightly more expensive "fallback" could take place to set up this resolution due to some logic that was removed since 1.3. The issue could also cause deprecation warnings involving column resolution to be emitted when using a 1.4 style query with joined eager loading.

  References: #6596

• [orm] [bug] Clarified the current purpose of the _orm.relationship.bake_queries flag, which in 1.4 is to enable or disable "lambda caching" of statements within the "lazyload" and "selectinload" loader strategies; this is separate from the more foundational SQL query cache that is used for most statements. Additionally, the lazy loader no longer uses its own cache for many-to-one SQL queries, which was an implementation quirk that doesn't exist for any other loader scenario. Finally, the "lru cache" warning that the lazyloader and selectinloader strategies could emit when handling a wide array of class/relationship combinations has been removed; based on analysis of some end-user cases, this warning doesn't suggest any significant issue. While setting bake_queries=False for such a relationship will remove this cache from being used, there's no particular performance gain in this case as using no caching vs. using a cache that needs to refresh often likely still wins out on the caching being used side.

  References: #6072, #6487

• [orm] [bug] [regression] Adjusted the means by which classes such as _orm.scoped_session and _asyncio.AsyncSession are generated from the base _orm.Session class, such that custom _orm.Session subclasses such as that used by Flask-SQLAlchemy don't need to implement positional arguments when they call into the superclass method, and can continue using the same argument styles as in previous releases.

  References: #6285

• [orm] [bug] [regression] Fixed issue where query production for joinedload against a complex left hand side involving joined-table inheritance could fail to produce a correct query, due to a clause adaption issue.

  References: #6595

• [orm] [bug] Fixed issue in experimental "select ORM objects from INSERT/UPDATE" use case where an error was raised if the statement were against a single-table-inheritance subclass.

  References: #6591

• [orm] [bug] The warning that's emitted for _orm.relationship() when multiple relationships would overlap with each other as far as foreign key attributes written towards, now includes the specific "overlaps" argument to use for each warning in order to silence the warning without changing the mapping.

  References: #6400

asyncio

• [asyncio] [usecase] Implemented a new registry architecture that allows the Async version of an object, like AsyncSession, AsyncConnection, etc., to be locatable given the proxied "sync" object, i.e. Session, Connection. Previously, to the degree such lookup functions were used, an Async object would be re-created each time, which was less than ideal as the identity and state of the "async" object would not be preserved across calls.

  From there, new helper functions _asyncio.async_object_session(), _asyncio.async_session() as well as a new _orm.InstanceState attribute _orm.InstanceState.async_session have been added, which are used to retrieve the original _asyncio.AsyncSession associated with an ORM mapped object, a _orm.Session associated with an _asyncio.AsyncSession, and an _asyncio.AsyncSession associated with an _orm.InstanceState, respectively.

  This patch also implements new methods _asyncio.AsyncSession.in_nested_transaction(), _asyncio.AsyncSession.get_transaction(), _asyncio.AsyncSession.get_nested_transaction().

  References: #6319

• [asyncio] [bug] Fixed an issue that presented itself when using the _pool.NullPool or the _pool.StaticPool with an async engine. This mostly affected the aiosqlite dialect.

  References: #6575

• [asyncio] [bug] Added asyncio.exceptions.TimeoutError, asyncio.exceptions.CancelledError as so-called "exit exceptions", a class of exceptions that include things like GreenletExit and KeyboardInterrupt, which are considered to be events that warrant considering a DBAPI connection to be in an unusable state where it should be recycled.

  References: #6592

postgresql

• [postgresql] [bug] [regression] Fixed regression where using the PostgreSQL "INSERT..ON CONFLICT" structure would fail to work with the psycopg2 driver if it were used in an "executemany" context along with bound parameters in the "SET" clause, due to the implicit use of the psycopg2 fast execution helpers which are not appropriate for this style of INSERT statement; as these helpers are the default in 1.4 this is effectively a regression. Additional checks to exclude this kind of statement from that particular extension have been added.

  References: #6581

sqlite

• [sqlite] [bug] Add note regarding encryption-related pragmas for pysqlcipher passed in the url.

  This change is also backported to: 1.3.25

  References: #6589

• [sqlite] [bug] [regression] The fix for pysqlcipher released in version 1.4.3 #5848 was unfortunately non-working, in that the new on_connect_url hook was erroneously not receiving a URL object under normal usage of _sa.create_engine() and instead received a string that was unhandled; the test suite failed to fully set up the actual conditions under which this hook is called. This has been fixed.

  References: #6586

1.4.17

Released: May 29, 2021

orm

• [orm] [bug] [regression] Fixed regression caused by just-released performance fix mentioned in #6550 where a query.join() to a relationship could produce an AttributeError if the query were made against non-ORM structures only, a fairly unusual calling pattern.

  References: #6558

1.4.16

Released: May 28, 2021

general

• [general] [bug] Resolved various deprecation warnings which were appearing as of Python version 3.10.0b1.

  References: #6540, #6543

orm

• [orm] [bug] Fixed issue when using _orm.relationship.cascade_backrefs parameter set to False, which per change_5150 is set to become the standard behavior in SQLAlchemy 2.0, where adding the item to a collection that uniquifies, such as set or dict would fail to fire a cascade event if the object were already associated in that collection via the backref. This fix represents a fundamental change in the collection mechanics by introducing a new event state which can fire off for a collection mutation even if there is no net change on the collection; the action is now suited using a new event hook _orm.AttributeEvents.append_wo_mutation().

  References: #6471

• [orm] [bug] [regression] Fixed regression involving clause adaption of labeled ORM compound elements, such as single-table inheritance discriminator expressions with conditionals or CASE expressions, which could cause aliased expressions such as those used in ORM join / joinedload operations to not be adapted correctly, such as referring to the wrong table in the ON clause in a join.

  This change also improves a performance bump that was located within the process of invoking _sql.Select.join() given an ORM attribute as a target.

  References: #6550

• [orm] [bug] [regression] Fixed regression where the full combination of joined inheritance, global with_polymorphic, self-referential relationship and joined loading would fail to be able to produce a query with the scope of lazy loads and object refresh operations that also attempted to render the joined loader.

  References: #6495

• [orm] [bug] Enhanced the bind resolution rules for _orm.Session.execute() so that when a non-ORM statement such as an _sql.insert() construct nonetheless is built against ORM objects, to the greatest degree possible the ORM entity will be used to resolve the bind, such as for a _orm.Session that has a bind map set up on a common superclass without specific mappers or tables named in the map.

  References: #6484

engine

• [engine] [bug] Fixed issue where an @ sign in the database portion of a URL would not be interpreted correctly if the URL also had a username:password section.

  References: #6482

• [engine] [bug] Fixed a long-standing issue with URL where query parameters following the question mark would not be parsed correctly if the URL did not contain a database portion with a backslash.

  References: #6329

sql

• [sql] [bug] [regression] Fixed regression in dynamic loader strategy and _orm.relationship() overall where the _orm.relationship.order_by parameter were stored as a mutable list, which could then be mutated when combined with additional "order_by" methods used against the dynamic query object, causing the ORDER BY criteria to continue to grow repetitively.

  References: #6549

mssql

• [mssql] [usecase] Implemented support for a _sql.CTE construct to be used directly as the target of a _sql.delete() construct, i.e. "WITH ... AS cte DELETE FROM cte". This appears to be a useful feature of SQL Server.

  References: #6464

misc

• [bug] [ext] Fixed a deprecation warning that was emitted when using _automap.automap_base() without passing an existing Base.

  References: #6529

• [bug] [pep484] Remove pep484 types from the code. Current effort is around the stub package, and having typing in two places makes thing worse, since the types in the SQLAlchemy source were usually outdated compared to the version in the stubs.

  References: #6461

• [bug] [ext] [regression] Fixed regression in the sqlalchemy.ext.instrumentation extension that prevented instrumentation disposal from working completely. This fix includes both a 1.4 regression fix as well as a fix for a related issue that existed in 1.3 also. As part of this change, the sqlalchemy.ext.instrumentation.InstrumentationManager class now has a new method unregister(), which replaces the previous method dispose(), which was not called as of version 1.4.

  References: #6390

1.4.15

Released: May 11, 2021

general

• [general] [feature] A new approach has been applied to the warnings system in SQLAlchemy to accurately predict the appropriate stack level for each warning dynamically. This allows evaluating the source of SQLAlchemy-generated warnings and deprecation warnings to be more straightforward as the warning will indicate the source line within end-user code, rather than from an arbitrary level within SQLAlchemy's own source code.

  References: #6241

orm

• [orm] [bug] [regression] Fixed additional regression caused by "eager loaders run on unexpire" feature #1763 where the feature would run for a contains_eager() eagerload option in the case that the contains_eager() were chained to an additional eager loader option, which would then produce an incorrect query as the original query-bound join criteria were no longer present.

  References: #6449

• [orm] [bug] Fixed issue in subquery loader strategy which prevented caching from working correctly. This would have been seen in the logs as a "generated" message instead of "cached" for all subqueryload SQL emitted, which by saturating the cache with new keys would degrade overall performance; it also would produce "LRU size alert" warnings.

  References: #6459

sql

• [sql] [bug] Adjusted the logic added as part of #6397 in 1.4.12 so that internal mutation of the BindParameter object occurs within the clause construction phase as it did before, rather than in the compilation phase. In the latter case, the mutation still produced side effects against the incoming construct and additionally could potentially interfere with other internal mutation routines.

  References: #6460

mysql

• [mysql] [bug] [documentation] Added support for the ssl_check_hostname= parameter in mysql connection URIs and updated the mysql dialect documentation regarding secure connections. Original pull request courtesy of Jerry Zhao.

  References: #5397

1.4.14

Released: May 6, 2021

orm

• [orm] [bug] [regression] Fixed regression involving lazy='dynamic' loader in conjunction with a detached object. The previous behavior was that the dynamic loader upon calling methods like .all() returns empty lists for detached objects without error, this has been restored; however a warning is now emitted as this is not the correct result. Other dynamic loader scenarios correctly raise DetachedInstanceError.

  References: #6426

engine

• [engine] [usecase] [orm] Applied consistent behavior to the use case of calling .commit() or .rollback() inside of an existing .begin() context manager, with the addition of potentially emitting SQL within the block subsequent to the commit or rollback. This change continues upon the change first added in #6155 where the use case of calling "rollback" inside of a .begin() contextmanager block was proposed:

  -   calling `.commit()` or `.rollback()` will now be allowed
      without error or warning within all scopes, including
      that of legacy and future `_engine.Engine`, ORM
      `_orm.Session`, asyncio `AsyncEngine`.  Previously,
      the `_orm.Session` disallowed this.
  
  -   The remaining scope of the context manager is then closed;
      when the block ends, a check is emitted to see if the transaction
      was already ended, and if so the block returns without action.
  
  -   It will now raise **an error** if subsequent SQL of any kind
      is emitted within the block, **after** `.commit()` or
      `.rollback()` is called.   The block should be closed as
      the state of the executable object would otherwise be undefined
      in this state.
  

  References: #6288

• [engine] [bug] [regression] Established a deprecation path for calling upon the _cursor.CursorResult.keys() method for a statement that returns no rows to provide support for legacy patterns used by the "records" package as well as any other non-migrated applications. Previously, this would raise ResourceClosedException unconditionally in the same way as it does when attempting to fetch rows. While this is the correct behavior going forward, the _cursor.LegacyCursorResult object will now in this case return an empty list for .keys() as it did in 1.3, while also emitting a 2.0 deprecation warning. The _cursor.CursorResult, used when using a 2.0-style "future" engine, will continue to raise as it does now.

  References: #6427

sql

• [sql] [bug] [regression] Fixed regression caused by the "empty in" change just made in #6397 1.4.12 where the expression needs to be parenthesized for the "not in" use case, otherwise the condition will interfere with the other filtering criteria.

  References: #6428

• [sql] [bug] [regression] The TypeDecorator class will now emit a warning when used in SQL compilation with caching unless the .cache_ok flag is set to True or False. A new class-level attribute TypeDecorator.cache_ok may be set which will be used as an indication that all the parameters passed to the object are safe to be used as a cache key if set to True, False means they are not.

  References: #6436

# 1.4.13

Released: May 3, 2021 ## orm

• [orm] [bug] [regression] Fixed regression in selectinload loader strategy that would cause it to cache its internal state incorrectly when handling relationships that join across more than one column, such as when using a composite foreign key. The invalid caching would then cause other unrelated loader operations to fail.

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

• [orm] [bug] [regression] Fixed regression where _orm.Query.filter_by() would not work if the lead entity were a SQL function or other expression derived from the primary entity in question, rather than a simple entity or column of that entity. Additionally, improved the behavior of _sql.Select.filter_by() overall to work with column expressions even in a non-ORM context.

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

• [orm] [bug] [regression] Fixed regression where using _orm.selectinload() and _orm.subqueryload() to load a two-level-deep path would lead to an attribute error.

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

• [orm] [bug] [regression] Fixed regression where using the _orm.noload() loader strategy in conjunction with a "dynamic" relationship would lead to an attribute error as the noload strategy would attempt to apply itself to the dynamic loader.

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

## engine

• [engine] [bug] [regression] Restored a legacy transactional behavior that was inadvertently removed from the _engine.Connection as it was never tested as a known use case in previous versions, where calling upon the _engine.Connection.begin_nested() method, when no transaction is present, does not create a SAVEPOINT at all and instead starts an outer transaction, returning a RootTransaction object instead of a NestedTransaction object. This RootTransaction then will emit a real COMMIT on the database connection when committed. Previously, the 2.0 style behavior was present in all cases that would autobegin a transaction but not commit it, which is a behavioral change.

  When using a 2.0 style connection object, the behavior is unchanged from previous 1.4 versions; calling _future.Connection.begin_nested() will "autobegin" the outer transaction if not already present, and then as instructed emit a SAVEPOINT, returning the NestedTransaction object. The outer transaction is committed by calling upon _future.Connection.commit(), as is "commit-as-you-go" style usage.

  Unknown interpreted text role "term".

  In non-"future" mode, while the old behavior is restored, it also emits a 2.0 deprecation warning as this is a legacy behavior.

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

## asyncio

• [asyncio] [bug] [regression] Fixed a regression introduced by [#6337](http://www.sqlalchemy.org/trac/ticket/6337) that would create an asyncio.Lock which could be attached to the wrong loop when instantiating the async engine before any asyncio loop was started, leading to an asyncio error message when attempting to use the engine under certain circumstances.

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

## postgresql

• [postgresql] [usecase] Add support for server side cursors in the pg8000 dialect for PostgreSQL. This allows use of the Connection.execution_options.stream_results option.

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

1.4.12

Released: April 29, 2021

orm

• [orm] [bug] Fixed issue in _orm.Session.bulk_save_objects() when used with persistent objects which would fail to track the primary key of mappings where the column name of the primary key were different than the attribute name.

  This change is also backported to: 1.3.25

  References: #6392

• [orm] [bug] [caching] [regression] Fixed critical regression where bound parameter tracking as used in the SQL caching system could fail to track all parameters for the case where the same SQL expression containing a parameter were used in an ORM-related query using a feature such as class inheritance, which was then embedded in an enclosing expression which would make use of that same expression multiple times, such as a UNION. The ORM would individually copy the individual SELECT statements as part of compilation with class inheritance, which then embedded in the enclosing statement would fail to accommodate for all parameters. The logic that tracks this condition has been adjusted to work for multiple copies of a parameter.

  References: #6391

• [orm] [bug] Fixed two distinct issues mostly affecting _hybrid.hybrid_property, which would come into play under common mis-configuration scenarios that were silently ignored in 1.3, and now failed in 1.4, where the "expression" implementation would return a non _sql.ClauseElement such as a boolean value. For both issues, 1.3's behavior was to silently ignore the mis-configuration and ultimately attempt to interpret the value as a SQL expression, which would lead to an incorrect query.

  -   Fixed issue regarding interaction of the attribute system with
      hybrid_property, where if the `__clause_element__()` method of the
      attribute returned a non-`_sql.ClauseElement` object, an internal
      `AttributeError` would lead the attribute to return the `expression`
      function on the hybrid_property itself, as the attribute error was
      against the name `.expression` which would invoke the `__getattr__()`
      method as a fallback. This now raises explicitly. In 1.3 the
      non-`_sql.ClauseElement` was returned directly.
  
  -   Fixed issue in SQL argument coercions system where passing the wrong
      kind of object to methods that expect column expressions would fail if
      the object were altogether not a SQLAlchemy object, such as a Python
      function, in cases where the object were not just coerced into a bound
      value. Again 1.3 did not have a comprehensive argument coercion system
      so this case would also pass silently.
  

  References: #6350

• [orm] [bug] Fixed issue where using a _sql.Select as a subquery in an ORM context would modify the _sql.Select in place to disable eagerloads on that object, which would then cause that same _sql.Select to not eagerload if it were then re-used in a top-level execution context.

  References: #6378

• [orm] [bug] [regression] Fixed issue where the new autobegin <session_autobegin> behavior failed to "autobegin" in the case where an existing persistent object has an attribute change, which would then impact the behavior of _orm.Session.rollback() in that no snapshot was created to be rolled back. The "attribute modify" mechanics have been updated to ensure "autobegin", which does not perform any database work, does occur when persistent attributes change in the same manner as when _orm.Session.add() is called. This is a regression as in 1.3, the rollback() method always had a transaction to roll back and would expire every time.

  References: #6359, #6360

• [orm] [bug] [regression] Fixed regression in ORM where using hybrid property to indicate an expression from a different entity would confuse the column-labeling logic in the ORM and attempt to derive the name of the hybrid from that other class, leading to an attribute error. The owning class of the hybrid attribute is now tracked along with the name.

  References: #6386

• [orm] [bug] [regression] Fixed regression in hybrid_property where a hybrid against a SQL function would generate an AttributeError when attempting to generate an entry for the .c collection of a subquery in some cases; among other things this would impact its use in cases like that of Query.count().

  References: #6401

• [orm] [bug] [dataclasses] Adjusted the declarative scan for dataclasses so that the inheritance behavior of _orm.declared_attr() established on a mixin, when using the new form of having it inside of a dataclasses.field() construct and not actually a descriptor attribute on the class, correctly accommodates the case when the target class to be mapped is a subclass of an existing mapped class which has already mapped that _orm.declared_attr(), and therefore should not be re-applied to this class.

  References: #6346

• [orm] [bug] Fixed an issue with the (deprecated in 1.4) _schema.ForeignKeyConstraint.copy() method that caused an error when invoked with the schema argument.

  References: #6353

engine

• [engine] [bug] Fixed issue where usage of an explicit Sequence would produce inconsistent "inline" behavior for an Insert construct that includes multiple values phrases; the first seq would be inline but subsequent ones would be "pre-execute", leading to inconsistent sequence ordering. The sequence expressions are now fully inline.

  References: #6361

sql

• [sql] [bug] Revised the "EMPTY IN" expression to no longer rely upon using a subquery, as this was causing some compatibility and performance problems. The new approach for selected databases takes advantage of using a NULL-returning IN expression combined with the usual "1 != 1" or "1 = 1" expression appended by AND or OR. The expression is now the default for all backends other than SQLite, which still had some compatibility issues regarding tuple "IN" for older SQLite versions.

  Third party dialects can still override how the "empty set" expression renders by implementing a new compiler method def visit_empty_set_op_expr(self, type_, expand_op), which takes precedence over the existing def visit_empty_set_expr(self, element_types) which remains in place.

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

• [sql] [bug] [regression] Fixed regression where usage of the _sql.text() construct inside the columns clause of a _sql.Select construct, which is better handled by using a _sql.literal_column() construct, would nonetheless prevent constructs like _sql.union() from working correctly. Other use cases, such as constructing subuqeries, continue to work the same as in prior versions where the _sql.text() construct is silently omitted from the collection of exported columns. Also repairs similar use within the ORM.

  References: #6343

• [sql] [bug] [regression] Fixed regression involving legacy methods such as _sql.Select.append_column() where internal assertions would fail.

  References: #6261

• [sql] [bug] [regression] Fixed regression caused by #5395 where tuning back the check for sequences in _sql.select() now caused failures when doing 2.0-style querying with a mapped class that also happens to have an __iter__() method. Tuned the check some more to accommodate this as well as some other interesting __iter__() scenarios.

  References: #6300

schema

• [schema] [bug] [mariadb] [mysql] [oracle] [postgresql] Ensure that the MySQL and MariaDB dialect ignore the _sql.Identity construct while rendering the AUTO_INCREMENT keyword in a create table.

  The Oracle and PostgreSQL compiler was updated to not render _sql.Identity if the database version does not support it (Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless of the database version.

  References: #6338

postgresql

• [postgresql] [bug] Fixed very old issue where the _types.Enum datatype would not inherit the _schema.MetaData.schema parameter of a _schema.MetaData object when that object were passed to the _types.Enum using _types.Enum.metadata.

  References: #6373

sqlite

• [sqlite] [usecase] Default to using SingletonThreadPool for in-memory SQLite databases created using URI filenames. Previously the default pool used was the NullPool that precented sharing the same database between multiple engines.

  References: #6379

mssql

• [mssql] [bug] [schema] Add _types.TypeEngine.as_generic() support for sqlalchemy.dialects.mysql.BIT columns, mapping them to _sql.sqltypes.Boolean.

  References: #6345

• [mssql] [bug] [regression] Fixed regression caused by #6306 which added support for DateTime(timezone=True), where the previous behavior of the pyodbc driver of implicitly dropping the tzinfo from a timezone-aware date when INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL Server error when inserting timezone-aware datetime objects into timezone-native database columns.

  References: #6366

1.4.11

Released: April 21, 2021

orm declarative

• [orm] [declarative] [bug] [regression] Fixed regression where recent changes to support Python dataclasses had the inadvertent effect that an ORM mapped class could not successfully override the __new__() method.

  References: #6331

engine

• [engine] [bug] [regression] Fixed critical regression caused by the change in #5497 where the connection pool "init" phase no longer occurred within mutexed isolation, allowing other threads to proceed with the dialect uninitialized, which could then impact the compilation of SQL statements.

  References: #6337

1.4.10

Released: April 20, 2021

orm

• [orm] [usecase] Altered some of the behavior repaired in #6232 where the immediateload loader strategy no longer goes into recursive loops; the modification is that an eager load (joinedload, selectinload, or subqueryload) from A->bs->B which then states immediateload for a simple manytoone B->a->A that's in the identity map will populate the B->A, so that this attribute is back-populated when the collection of A/A.bs are loaded. This allows the objects to be functional when detached.

• [orm] [bug] Fixed bug in new _orm.with_loader_criteria() feature where using a mixin class with _orm.declared_attr() on an attribute that were accessed inside the custom lambda would emit a warning regarding using an unmapped declared attr, when the lambda callable were first initialized. This warning is now prevented using special instrumentation for this lambda initialization step.

  References: #6320

• [orm] [bug] [regression] Fixed additional regression caused by the "eagerloaders on refresh" feature added in #1763 where the refresh operation historically would set populate_existing, which given the new feature now overwrites pending changes on eagerly loaded objects when autoflush is false. The populate_existing flag has been turned off for this case and a more specific method used to ensure the correct attributes refreshed.

  References: #6326

• [orm] [bug] [result] Fixed an issue when using 2.0 style execution that prevented using _result.Result.scalar_one() or _result.Result.scalar_one_or_none() after calling _result.Result.unique(), for the case where the ORM is returning a single-element row in any case.

  References: #6299

sql

• [sql] [bug] Fixed issue in SQL compiler where the bound parameters set up for a Values construct wouldn't be positionally tracked correctly if inside of a _sql.CTE, affecting database drivers that support VALUES + ctes and use positional parameters such as SQL Server in particular as well as asyncpg. The fix also repairs support for compiler flags such as literal_binds.

  References: #6327

• [sql] [bug] Repaired and solidified issues regarding custom functions and other arbitrary expression constructs which within SQLAlchemy's column labeling mechanics would seek to use str(obj) to get a string representation to use as an anonymous column name in the .c collection of a subquery. This is a very legacy behavior that performs poorly and leads to lots of issues, so has been revised to no longer perform any compilation by establishing specific methods on FunctionElement to handle this case, as SQL functions are the only use case that it came into play. An effect of this behavior is that an unlabeled column expression with no derivable name will be given an arbitrary label starting with the prefix "_no_label" in the .c collection of a subquery; these were previously being represented either as the generic stringification of that expression, or as an internal symbol.

  References: #6256

schema

• [schema] [usecase] [mssql] The _types.DateTime.timezone parameter when set to True will now make use of the DATETIMEOFFSET column type with SQL Server when used to emit DDL, rather than DATETIME where the flag was silently ignored.

  References: #6306

• [schema] [bug] Fixed issue where _functions.next_value() was not deriving its type from the corresponding _schema.Sequence, instead hardcoded to _types.Integer. The specific numeric type is now used.

  References: #6287

mypy

• [mypy] [bug] Fixed issue where mypy plugin would not correctly interpret an explicit _orm.Mapped annotation in conjunction with a _orm.relationship() that refers to a class by string name; the correct annotation would be downgraded to a less specific one leading to typing errors.

  References: #6255

misc

• [bug] [declarative] [regression] Fixed _declarative.instrument_declarative() that called a non existing registry method.

  References: #6291

1.4.9

Released: April 17, 2021

orm

• [orm] [usecase] Established support for _orm.synoynm() in conjunction with hybrid property, assocaitionproxy is set up completely, including that synonyms can be established linking to these constructs which work fully. This is a behavior that was semi-explicitly disallowed previously, however since it did not fail in every scenario, explicit support for assoc proxy and hybrids has been added.

  References: #6267

• [orm] [performance] [bug] [regression] [sql] Fixed a critical performance issue where the traversal of a _sql.select() construct would traverse a repetitive product of the represented FROM clauses as they were each referred towards by columns in the columns clause; for a series of nested subqueries with lots of columns this could cause a large delay and significant memory growth. This traversal is used by a wide variety of SQL and ORM functions, including by the ORM _orm.Session when it's configured to have "table-per-bind", which while this is not a common use case, it seems to be what Flask-SQLAlchemy is hardcoded as using, so the issue impacts Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM clauses which was effectively what would happen implicitly with the pre-1.4 architecture.

  References: #6304

• [orm] [bug] [regression] Fixed regression where an attribute that is mapped to a _orm.synonym() could not be used in column loader options such as _orm.load_only().

  References: #6272

sql

• [sql] [bug] [regression] Fixed regression where an empty in statement on a tuple would result in an error when compiled with the option literal_binds=True.

  References: #6290

postgresql

• [postgresql] [bug] [regression] [sql] Fixed an argument error in the default and PostgreSQL compilers that would interfere with an UPDATE..FROM or DELETE..FROM..USING statement that was then SELECTed from as a CTE.

  References: #6303

1.4.8

Released: April 15, 2021

orm

• [orm] [bug] [regression] Fixed a cache leak involving the _orm.with_expression() loader option, where the given SQL expression would not be correctly considered as part of the cache key.

  Additionally, fixed regression involving the corresponding _orm.query_expression() feature. While the bug technically exists in 1.3 as well, it was not exposed until 1.4. The "default expr" value of null() would be rendered when not needed, and additionally was also not adapted correctly when the ORM rewrites statements such as when using joined eager loading. The fix ensures "singleton" expressions like NULL and true aren't "adapted" to refer to columns in ORM statements, and additionally ensures that a _orm.query_expression() with no default expression doesn't render in the statement if a _orm.with_expression() isn't used.

  References: #6259

• [orm] [bug] Fixed issue in the new feature of _orm.Session.refresh() introduced by #1763 where eagerly loaded relationships are also refreshed, where the lazy="raise" and lazy="raise_on_sql" loader strategies would interfere with the _orm.immediateload() loader strategy, thus breaking the feature for relationships that were loaded with _orm.selectinload(), _orm.subqueryload() as well.

  References: #6252

engine

• [engine] [bug] The _engine.Dialect.has_table() method now raises an informative exception if a non-Connection is passed to it, as this incorrect behavior seems to be common. This method is not intended for external use outside of a dialect. Please use the Inspector.has_table() method or for cross-compatibility with older SQLAlchemy versions, the _engine.Engine.has_table() method.

sql

• [sql] [feature] The tuple returned by CursorResult.inserted_primary_key is now a _result.Row object with a named tuple interface on top of the existing tuple interface.

  References: #3314

• [sql] [bug] [regression] Fixed regression where the _sql.BindParameter object would not properly render for an IN expression (i.e. using the "post compile" feature in 1.4) if the object were copied from either an internal cloning operation, or from a pickle operation, and the parameter name contained spaces or other special characters.

  References: #6249

• [sql] [bug] [regression] [sqlite] Fixed regression where the introduction of the INSERT syntax "INSERT... VALUES (DEFAULT)" was not supported on some backends that do however support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are now each individually supported or non-supported for each dialect, for example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES". Support for Oracle has also been enabled.

  References: #6254

mypy

• [mypy] [change] Updated Mypy plugin to only use the public plugin interface of the semantic analyzer.

• [mypy] [bug] Revised the fix for OrderingList from version 1.4.7 which was testing against the incorrect API.

  References: #6205

asyncio

• [asyncio] [bug] Fix typo that prevented setting the bind attribute of an _asyncio.AsyncSession to the correct value.

  References: #6220

mssql

• [mssql] [bug] [regression] Fixed an additional regression in the same area as that of #6173, #6184, where using a value of 0 for OFFSET in conjunction with LIMIT with SQL Server would create a statement using "TOP", as was the behavior in 1.3, however due to caching would then fail to respond accordingly to other values of OFFSET. If the "0" wasn't first, then it would be fine. For the fix, the "TOP" syntax is now only emitted if the OFFSET value is omitted entirely, that is, _sql.Select.offset() is not used. Note that this change now requires that if the "with_ties" or "percent" modifiers are used, the statement can't specify an OFFSET of zero, it now needs to be omitted entirely.

  References: #6265

1.4.7

Released: April 9, 2021

orm

• [orm] [bug] [regression] Fixed regression where the _orm.subqueryload() loader strategy would fail to correctly accommodate sub-options, such as a _orm.defer() option on a column, if the "path" of the subqueryload were more than one level deep.

  References: #6221

• [orm] [bug] [regression] Fixed regression where the _orm.merge_frozen_result() function relied upon by the dogpile.caching example was not included in tests and began failing due to incorrect internal arguments.

  References: #6211

• [orm] [bug] [regression] Fixed critical regression where the _orm.Session could fail to "autobegin" a new transaction when a flush occurred without an existing transaction in place, implicitly placing the _orm.Session into legacy autocommit mode which commit the transaction. The _orm.Session now has a check that will prevent this condition from occurring, in addition to repairing the flush issue.

  Additionally, scaled back part of the change made as part of #5226 which can run autoflush during an unexpire operation, to not actually do this in the case of a _orm.Session using legacy _orm.Session.autocommit mode, as this incurs a commit within a refresh operation.

  References: #6233

• [orm] [bug] [regression] Fixed regression where the ORM compilation scheme would assume the function name of a hybrid property would be the same as the attribute name in such a way that an AttributeError would be raised, when it would attempt to determine the correct name for each element in a result tuple. A similar issue exists in 1.3 but only impacts the names of tuple rows. The fix here adds a check that the hybrid's function name is actually present in the __dict__ of the class or its superclasses before assigning this name; otherwise, the hybrid is considered to be "unnamed" and ORM result tuples will use the naming scheme of the underlying expression.

  References: #6215

• [orm] [bug] [regression] Fixed critical regression caused by the new feature added as part of #1763, eager loaders are invoked on unexpire operations. The new feature makes use of the "immediateload" eager loader strategy as a substitute for a collection loading strategy, which unlike the other "post-load" strategies was not accommodating for recursive invocations between mutually-dependent relationships, leading to recursion overflow errors.

  References: #6232

engine

• [engine] [bug] [regression] Fixed up the behavior of the _result.Row object when dictionary access is used upon it, meaning converting to a dict via dict(row) or accessing members using strings or other objects i.e. row["some_key"] works as it would with a dictionary, rather than raising TypeError as would be the case with a tuple, whether or not the C extensions are in place. This was originally supposed to emit a 2.0 deprecation warning for the "non-future" case using _result.LegacyRow, and was to raise TypeError for the "future" _result.Row class. However, the C version of _result.Row was failing to raise this TypeError, and to complicate matters, the _orm.Session.execute() method now returns _result.Row in all cases to maintain consistency with the ORM result case, so users who didn't have C extensions installed would see different behavior in this one case for existing pre-1.4 style code.

  Therefore, in order to soften the overall upgrade scheme as most users have not been exposed to the more strict behavior of _result.Row up through 1.4.6, _result.LegacyRow and _result.Row both provide for string-key access as well as support for dict(row), in all cases emitting the 2.0 deprecation warning when SQLALCHEMY_WARN_20 is enabled. The _result.Row object still uses tuple-like behavior for __contains__, which is probably the only noticeable behavioral change compared to _result.LegacyRow, other than the removal of dictionary-style methods values() and items().

  References: #6218

sql

• [sql] [bug] [regression] Enhanced the "expanding" feature used for _sql.ColumnOperators.in_() operations to infer the type of expression from the right hand list of elements, if the left hand side does not have any explicit type set up. This allows the expression to support stringification among other things. In 1.3, "expanding" was not automatically used for _sql.ColumnOperators.in_() expressions, so in that sense this change fixes a behavioral regression.

  References: #6222

• [sql] [bug] Fixed the "stringify" compiler to support a basic stringification of a "multirow" INSERT statement, i.e. one with multiple tuples following the VALUES keyword.

schema

• [schema] [bug] [regression] Fixed regression where usage of a token in the _engine.Connection.execution_options.schema_translate_map dictionary which contained special characters such as braces would fail to be substituted properly. Use of square bracket characters [] is now explicitly disallowed as these are used as a delimiter character in the current implementation.

  References: #6216

mypy

• [mypy] [bug] Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct type for columns of subclasses that don’t directly descend from TypeEngine, in particular that of TypeDecorator and UserDefinedType.

misc

• [change] [tests] Added a new flag to DefaultDialect called supports_schema; third party dialects may set this flag to True to enable SQLAlchemy's schema-level tests when running the test suite for a third party dialect.

# 1.4.6

Released: April 6, 2021 ## orm

• [orm] [bug] [regression] Fixed regression where a deprecated form of _orm.Query.join() were used, passing a series of entities to join from without any ON clause in a single _orm.Query.join() call, would fail to function correctly.

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

• [orm] [bug] [regression] Fixed critical regression where the _orm.Query.yield_per() method in the ORM would set up the internal _engine.Result to yield chunks at a time, however made use of the new _engine.Result.unique() method which uniques across the entire result. This would lead to lost rows since the ORM is using id(obj) as the uniquing function, which leads to repeated identifiers for new objects as already-seen objects are garbage collected. 1.3's behavior here was to "unique" across each chunk, which does not actually produce "uniqued" results when results are yielded in chunks. As the _orm.Query.yield_per() method is already explicitly disallowed when joined eager loading is in place, which is the primary rationale for the "uniquing" feature, the "uniquing" feature is now turned off entirely when _orm.Query.yield_per() is used.

  This regression only applies to the legacy _orm.Query object; when using 2.0 style execution, "uniquing" is not automatically applied. To prevent the issue from arising from explicit use of _engine.Result.unique(), an error is now raised if rows are fetched from a "uniqued" ORM-level _engine.Result if any yield per <orm_queryguide_yield_per> API is also in use, as the purpose of yield_per is to allow for arbitrarily large numbers of rows, which cannot be uniqued in memory without growing the number of entries to fit the complete result size.

  Unknown interpreted text role "term".

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

## sql

• [sql] [bug] [mssql] [oracle] [regression] Fixed further regressions in the same area as that of [#6173](http://www.sqlalchemy.org/trac/ticket/6173) released in 1.4.5, where a "postcompile" parameter, again most typically those used for LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed correctly if the same parameter rendered in multiple places in the statement.

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

• [sql] [bug] Executing a _sql.Subquery using _engine.Connection.execute() is deprecated and will emit a deprecation warning; this use case was an oversight that should have been removed from 1.4. The operation will now execute the underlying _sql.Select object directly for backwards compatibility. Similarly, the _sql.CTE class is also not appropriate for execution. In 1.3, attempting to execute a CTE would result in an invalid "blank" SQL statement being executed; since this use case was not working it now raises _exc.ObjectNotExecutableError. Previously, 1.4 was attempting to execute the CTE as a statement however it was working only erratically.

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

## schema

• [schema] [bug] The _schema.Table object now raises an informative error message if it is instantiated without passing at least the _schema.Table.name and _schema.Table.metadata arguments positionally. Previously, if these were passed as keyword arguments, the object would silently fail to initialize correctly.

  This change is also backported to: 1.3.25

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

## mypy

• [mypy] [bug] Applied a series of refactorings and fixes to accommodate for Mypy "incremental" mode across multiple files, which previously was not taken into account. In this mode the Mypy plugin has to accommodate Python datatypes expressed in other files coming in with less information than they have on a direct run.

  Additionally, a new decorator _orm.declarative_mixin() is added, which is necessary for the Mypy plugin to be able to definifitely identify a Declarative mixin class that is otherwise not used inside a particular Python file.

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

• [mypy] [bug] Fixed issue where the Mypy plugin would fail to interpret the "collection_class" of a relationship if it were a callable and not a class. Also improved type matching and error reporting for collection-oriented relationships.

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

## asyncio

• [asyncio] [usecase] [postgresql] Added accessors .sqlstate and synonym .pgcode to the .orig attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI adapter, that is, the intermediary exception object that wraps on top of that raised by the asyncpg library itself, but below the level of the SQLAlchemy dialect.

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

1.4.5

Released: April 2, 2021

orm

• [orm] [bug] [regression] Fixed regression where the _orm.joinedload() loader strategy would not successfully joinedload to a mapper that is mapper against a CTE construct.

  References: #6172

• [orm] [bug] [regression] Scaled back the warning message added in #5171 to not warn for overlapping columns in an inheritance scenario where a particular relationship is local to a subclass and therefore does not represent an overlap.

  References: #6171

sql

• [sql] [bug] [postgresql] Fixed bug in new _functions.FunctionElement.render_derived() feature where column names rendered out explicitly in the alias SQL would not have proper quoting applied for case sensitive names and other non-alphanumeric names.

  References: #6183

• [sql] [bug] [regression] Fixed regression where use of the Operators.in_() method with a _sql.Select object against a non-table-bound column would produce an AttributeError, or more generally using a _sql.ScalarSelect that has no datatype in a binary expression would produce invalid state.

  References: #6181

• [sql] [bug] Added a new flag to the _engine.Dialect class called _engine.Dialect.supports_statement_cache. This flag now needs to be present directly on a dialect class in order for SQLAlchemy's query cache <sql_caching> to take effect for that dialect. The rationale is based on discovered issues such as #6173 revealing that dialects which hardcode literal values from the compiled statement, often the numerical parameters used for LIMIT / OFFSET, will not be compatible with caching until these dialects are revised to use the parameters present in the statement only. For third party dialects where this flag is not applied, the SQL logging will show the message "dialect does not support caching", indicating the dialect should seek to apply this flag once they have verified that no per-statement literal values are being rendered within the compilation phase.

  References: #6184

schema

• [schema] [bug] Introduce a new parameter _types.Enum.omit_aliases in _types.Enum type allow filtering aliases when using a pep435 Enum. Previous versions of SQLAlchemy kept aliases in all cases, creating database enum type with additional states, meaning that they were treated as different values in the db. For backward compatibility this flag defaults to False in the 1.4 series, but will be switched to True in a future version. A deprecation warning is raise if this flag is not specified and the passed enum contains aliases.

  References: #6146

mypy

• [mypy] [bug] Fixed issue in mypy plugin where newly added support for _orm.as_declarative() needed to more fully add the DeclarativeMeta class to the mypy interpreter's state so that it does not result in a name not found error; additionally improves how global names are setup for the plugin including the Mapped name.

  References: #sqlalchemy/sqlalchemy2-stubs/#14

asyncio

• [asyncio] [bug] Fixed issue where the asyncio extension could not be loaded if running Python 3.6 with the backport library of contextvars installed.

  References: #6166

postgresql

• [postgresql] [bug] [regression] Fixed regression caused by #6023 where the PostgreSQL cast operator applied to elements within an _types.ARRAY when using psycopg2 would fail to use the correct type in the case that the datatype were also embedded within an instance of the _types.Variant adapter.

  Additionally, repairs support for the correct CREATE TYPE to be emitted when using a Variant(ARRAY(some_schema_type)).

  This change is also backported to: 1.3.25

  References: #6182

• [postgresql] [bug] Fixed typo in the fix for #6099 released in 1.4.4 that completely prevented this change from working correctly, i.e. the error message did not match what was actually emitted by pg8000.

  References: #6099

• [postgresql] [bug] Fixed issue where the PostgreSQL PGInspector, when generated against an _engine.Engine, would fail for .get_enums(), .get_view_names(), .get_foreign_table_names() and .get_table_oid() when used against a "future" style engine and not the connection directly.

  References: #6170

mysql

• [mysql] [bug] [regression] Fixed regression in the MySQL dialect where the reflection query used to detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions.

  References: #6163

mssql

• [mssql] [bug] Fixed a regression in MSSQL 2012+ that prevented the order by clause to be rendered when offset=0 is used in a subquery.

  References: #6163

oracle

• [oracle] [bug] [regression] Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue.

  References: #6173

1.4.4

Released: March 30, 2021

orm

• [orm] [bug] Fixed critical issue in the new _orm.PropComparator.and_() feature where loader strategies that emit secondary SELECT statements such as _orm.selectinload() and _orm.lazyload() would fail to accommodate for bound parameters in the user-defined criteria in terms of the current statement being executed, as opposed to the cached statement, causing stale bound values to be used.

  This also adds a warning for the case where an object that uses _orm.lazyload() in conjunction with _orm.PropComparator.and_() is attempted to be serialized; the loader criteria cannot reliably be serialized and deserialized and eager loading should be used for this case.

  References: #6139

• [orm] [bug] [regression] Fixed missing method _orm.Session.get() from the _orm.ScopedSession interface.

  References: #6144

engine

• [engine] [usecase] Modified the context manager used by _engine.Transaction so that an "already detached" warning is not emitted by the ending of the context manager itself, if the transaction were already manually rolled back inside the block. This applies to regular transactions, savepoint transactions, and legacy "marker" transactions. A warning is still emitted if the .rollback() method is called explicitly more than once.

  References: #6155

• [engine] [bug] Repair wrong arguments to exception handling method in CursorResult.

  References: #6138

postgresql

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

  This change is also backported to: 1.3.24

  References: #6161

• [postgresql] [bug] Modified the is_disconnect() handler for the pg8000 dialect, which now accommodates for a new InterfaceError emitted by pg8000 1.19.0. Pull request courtesy Hamdi Burak Usul.

  References: #6099

misc

• [misc] [bug] Adjusted the usage of the importlib_metadata library for loading setuptools entrypoints in order to accommodate for some deprecation changes.