2.0.21

2.0.21

Released: September 18, 2023

orm

• [orm] [bug] Adjusted the ORM's interpretation of the "target" entity used within
  Update and Delete to not interfere with the target
  "from" object passed to the statement, such as when passing an ORM-mapped
  _orm.aliased construct that should be maintained within a phrase
  like "UPDATE FROM". Cases like ORM session synchonize using "SELECT"
  statements such as with MySQL/ MariaDB will still have issues with
  UPDATE/DELETE of this form so it's best to disable synchonize_session when
  using DML statements of this type.

  References: #10279

• [orm] [bug] Added new capability to the _orm.selectin_polymorphic() loader option
  which allows other loader options to be bundled as siblings, referring to
  one of its subclasses, within the sub-options of parent loader option.
  Previously, this pattern was only supported if the
  _orm.selectin_polymorphic() were at the top level of the options for
  the query. See new documentation section for example.

  As part of this change, improved the behavior of the
  _orm.Load.selectin_polymorphic() method / loader strategy so that the
  subclass load does not load most already-loaded columns from the parent
  table, when the option is used against a class that is already being
  relationship-loaded. Previously, the logic to load only the subclass
  columns worked only for a top level class load.

  References: #10348

engine

• [engine] [bug] Fixed a series of reflection issues affecting the PostgreSQL,
  MySQL/MariaDB, and SQLite dialects when reflecting foreign key constraints
  where the target column contained parenthesis in one or both of the table
  name or column name.

  References: #10275

sql

• [sql] [usecase] Adjusted the _types.Enum datatype to accept an argument of
  None for the _types.Enum.length parameter, resulting in a
  VARCHAR or other textual type with no length in the resulting DDL. This
  allows for new elements of any length to be added to the type after it
  exists in the schema. Pull request courtesy Eugene Toder.

  References: #10269

• [sql] [usecase] Added new generic SQL function _functions.aggregate_strings, which
  accepts a SQL expression and a decimeter, concatenating strings on multiple
  rows into a single aggregate value. The function is compiled on a
  per-backend basis, into functions such as group_concat(),
string_agg(), or LISTAGG().
  Pull request courtesy Joshua Morris.

  References: #9873

• [sql] [bug] Adjusted the operator precedence for the string concatenation operator to
  be equal to that of string matching operators, such as
  ColumnElement.like(), ColumnElement.regexp_match(),
  ColumnElement.match(), etc., as well as plain == which has the
  same precedence as string comparison operators, so that parenthesis will be
  applied to a string concatenation expression that follows a string match
  operator. This provides for backends such as PostgreSQL where the "regexp
  match" operator is apparently of higher precedence than the string
  concatenation operator.

  References: #9610

• [sql] [bug] Qualified the use of hashlib.md5() within the DDL compiler, which is
  used to generate deterministic four-character suffixes for long index and
  constraint names in DDL statements, to include the Python 3.9+
  usedforsecurity=False parameter so that Python interpreters built for
  restricted environments such as FIPS do not consider this call to be
  related to security concerns.

  References: #10342

• [sql] [bug] The Values construct will now automatically create a proxy (i.e.
  a copy) of a _sql.column if the column were already associated
  with an existing FROM clause. This allows that an expression like
  values_obj.c.colname will produce the correct FROM clause even in the
  case that colname was passed as a _sql.column that was already
  used with a previous Values or other table construct.
  Originally this was considered to be a candidate for an error condition,
  however it's likely this pattern is already in widespread use so it's
  now added to support.

  References: #10280

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.

  This change is also backported to: 1.4.50

  References: #10207

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

  This change is also backported to: 1.4.50

  References: #10207

typing

• [typing] [usecase] Made the contained type for Mapped covariant; this is to allow
  greater flexibility for end-user typing scenarios, such as the use of
  protocols to represent particular mapped class structures that are passed
  to other functions. As part of this change, the contained type was also
  made covariant for dependent and related types such as
  _orm.base.SQLORMOperations, _orm.WriteOnlyMapped, and
  _sql.SQLColumnExpression. Pull request courtesy Roméo Després.

  References: #10288

• [typing] [bug] Fixed regression introduced in 2.0.20 via #9600 fix which
  attempted to add more formal typing to
  _schema.MetaData.naming_convention. This change prevented basic
  naming convention dictionaries from passing typing and has been adjusted so
  that a plain dictionary of strings for keys as well as dictionaries that
  use constraint types as keys or a mix of both, are again accepted.

  As part of this change, lesser used forms of the naming convention
  dictionary are also typed, including that it currently allows for
  Constraint type objects as keys as well.

  References: #10264, #9284

• [typing] [bug] Fixed the type annotation for __class_getitem__() as applied to the
  Visitable class at the base of expression constructs to accept Any
  for a key, rather than str, which helps with some IDEs such as PyCharm
  when attempting to write typing annotations for SQL constructs which
  include generic selectors. Pull request courtesy Jordan Macdonald.

  References: #9878

• [typing] [bug] Repaired the core "SQL element" class SQLCoreOperations to support the
  __hash__() method from a typing perspective, as objects like
  Column and ORM InstrumentedAttribute are hashable and
  are used as dictionary keys in the public API for the _dml.Update
  and _dml.Insert constructs. Previously, type checkers were not
  aware the root SQL element was hashable.

  References: #10353

• [typing] [bug] Fixed typing issue with _sql.Existing.select_from() that
  prevented its use with ORM classes.

  References: #10337

• [typing] [bug] Update type annotations for ORM loading options, restricting them to accept
  only "*" instead of any string for string arguments. Pull request
  courtesy Janek Nouvertné.

  References: #10131

postgresql

• [postgresql] [bug] Fixed regression which appeared in 2.0 due to #8491 where the
  revised "ping" used for PostgreSQL dialects when the
  _sa.create_engine.pool_pre_ping parameter is in use would
  interfere with the use of asyncpg with PGBouncer "transaction" mode, as the
  multiple PostgreSQL commands emitted by asnycpg could be broken out among
  multiple connections leading to errors, due to the lack of any transaction
  around this newly revised "ping". The ping is now invoked within a
  transaction, in the same way that is implicit with all other backends that
  are based on the pep-249 DBAPI; this guarantees that the series of PG
  commands sent by asyncpg for this command are invoked on the same backend
  connection without it jumping to a different connection mid-command. The
  transaction is not used if the asyncpg dialect is used in "AUTOCOMMIT"
  mode, which remains incompatible with pgbouncer transaction mode.

  References: #10226

misc

• [bug] [setup] Fixed very old issue where the full extent of SQLAlchemy modules, including
  sqlalchemy.testing.fixtures, could not be imported outside of a pytest
  run. This suits inspection utilities such as pkgutil that attempt to
  import all installed modules in all packages.

  References: #10321