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