2.0.0rc1

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 t...
  

1.4.45

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

2.0.0b4

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

1.4.44

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

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

2.0.0b3

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

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

1.4.42

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

2.0.0b1

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.SessionTran...

1.4.41

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