1.4.50

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

2.0.22

2.0.22

Released: October 12, 2023

orm

• [orm] [usecase] Added method _orm.Session.get_one() that behaves like
  _orm.Session.get() but raises an exception instead of returning
  None if no instance was found with the provided primary key.
  Pull request courtesy of Carlos Sousa.

  References: #10202

• [orm] [usecase] Added an option to permanently close sessions.
  Set to False the new parameter _orm.Session.close_resets_only
  will prevent a _orm.Session from performing any other
  operation after _orm.Session.close() has been called.

  Added new method _orm.Session.reset() that will reset a _orm.Session
  to its initial state. This is an alias of _orm.Session.close(),
  unless _orm.Session.close_resets_only is set to False.

  References: #7787

• [orm] [bug] Fixed a wide range of _orm.mapped_column() parameters that were not
  being transferred when using the _orm.mapped_column() object inside
  of a pep-593 Annotated object, including
  _orm.mapped_column.sort_order,
  _orm.mapped_column.deferred,
  _orm.mapped_column.autoincrement,
  _orm.mapped_column.system, _orm.mapped_column.info
  etc.

  Additionally, it remains not supported to have dataclass arguments, such as
  _orm.mapped_column.kw_only,
  _orm.mapped_column.default_factory etc. indicated within the
  _orm.mapped_column() received by Annotated, as this is not
  supported with pep-681 Dataclass Transforms. A warning is now emitted when
  these parameters are used within Annotated in this way (and they
  continue to be ignored).

  References: #10046, #10369

• [orm] [bug] Fixed issue where calling _engine.Result.unique() with a new-style
  select() query in the ORM, where one or more columns yields values
  that are of "unknown hashability", typically when using JSON functions like
  func.json_build_object() without providing a type, would fail
  internally when the returned values were not actually hashable. The
  behavior is repaired to test the objects as they are received for
  hashability in this case, raising an informative error message if not. Note
  that for values of "known unhashability", such as when the
  _types.JSON or _types.ARRAY types are used directly, an
  informative error message was already raised.

  The "hashabiltiy testing" fix here is applied to legacy Query as
  well, however in the legacy case, _engine.Result.unique() is used for
  nearly all queries, so no new warning is emitted here; the legacy behavior
  of falling back to using id() in this case is maintained, with the
  improvement that an unknown type that turns out to be hashable will now be
  uniqufied, whereas previously it would not.

  References: #10459

• [orm] [bug] Fixed regression in recently revised "insertmanyvalues" feature (likely
  issue #9618) where the ORM would inadvertently attempt to
  interpret a non-RETURNING result as one with RETURNING, in the case where
  the implicit_returning=False parameter were applied to the mapped
  Table, indicating that "insertmanyvalues" cannot be used if the
  primary key values are not provided.

  References: #10453

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

  References: #10365

• [orm] [bug] Fixed issue where Mapped symbols like WriteOnlyMapped
  and DynamicMapped could not be correctly resolved when referenced
  as an element of a sub-module in the given annotation, assuming
  string-based or "future annotations" style annotations.

  References: #10412

• [orm] [bug] Fixed issue with __allow_unmapped__ declarative option
  where types that were declared using collection types such as
  list[SomeClass] vs. the typing construct List[SomeClass]
  would fail to be recognized correctly. Pull request courtesy
  Pascal Corpet.

  References: #10385

engine

• [engine] [bug] Fixed issue within some dialects where the dialect could incorrectly return
  an empty result set for an INSERT statement that does not actually return
  rows at all, due to artfacts from pre- or post-fetching the primary key of
  the row or rows still being present. Affected dialects included asyncpg,
  all mssql dialects.

• [engine] [bug] Fixed issue where under some garbage collection / exception scenarios the
  connection pool's cleanup routine would raise an error due to an unexpected
  set of state, which can be reproduced under specific conditions.

  References: #10414

sql

• [sql] [bug] Fixed issue where referring to a FROM entry in the SET clause of an UPDATE
  statement would not include it in the FROM clause of the UPDATE statement,
  if that entry were nowhere else in the statement; this occurs currently for
  CTEs that were added using Update.add_cte() to provide the desired
  CTE at the top of the statement.

  References: #10408

• [sql] [bug] Fixed 2.0 regression where the DDL construct would no longer
  __repr__() due to the removed on attribute not being accommodated.
  Pull request courtesy Iuri de Silvio.

  References: #10443

typing

• [typing] [bug] Fixed typing issue where the argument list passed to Values was
  too-restrictively tied to List rather than Sequence. Pull request
  courtesy Iuri de Silvio.

  References: #10451

• [typing] [bug] Updates to the codebase to support Mypy 1.6.0.

asyncio

• [asyncio] [bug] Fixed the _asyncio.AsyncSession.get.execution_options parameter
  which was not being propagated to the underlying _orm.Session and
  was instead being ignored.

mariadb

• [mariadb] [bug] Modified the mariadb-connector driver to pre-load the cursor.rowcount
  value for all queries, to suit tools such as Pandas that hardcode to
  calling Result.rowcount in this way. SQLAlchemy normally pre-loads
  cursor.rowcount only for UPDATE/DELETE statements and otherwise passes
  through to the DBAPI where it can return -1 if no value is available.
  However, mariadb-connector does not support invoking cursor.rowcount
  after the cursor itself is closed, raising an error instead. Generic test
  support has been added to ensure all backends support the allowing
  Result.rowcount to succceed (that is, returning an integer
  value with -1 for "not available") after the result is closed.

  References: #10396

• [mariadb] [bug] Additional fixes for the mariadb-connector dialect to support UUID data
  values in the result in INSERT..RETURNING statements.

mssql

• [mssql] [bug] Fixed bug where the rule that prevents ORDER BY from emitting within
  subqueries on SQL Server was not being disabled in the case where the
  select.fetch() method were used to limit rows in conjunction with
  WITH TIES or PERCENT, preventing valid subqueries with TOP / ORDER BY from
  being used.

  References: #10458

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

2.0.20

2.0.20

Released: August 15, 2023

orm

• [orm] [usecase] Implemented the "RETURNING '*'" use case for ORM enabled DML statements.
  This will render in as many cases as possible and return the unfiltered
  result set, however is not supported for multi-parameter "ORM bulk INSERT"
  statements that have specific column rendering requirements.

  References: #10192

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

  This change is also backported to: 1.4.50

  References: #10223

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

  This change is also backported to: 1.4.50

  References: #10223

• [orm] [bug] Fixed issue where the ORM's generation of a SELECT from a joined
  inheritance model with same-named columns in superclass and subclass would
  somehow not send the correct list of column names to the CTE
  construct, when the RECURSIVE column list were generated.

  References: #10169

• [orm] [bug] Fixed fairly major issue where execution options passed to
  _orm.Session.execute(), as well as execution options local to the ORM
  executed statement itself, would not be propagated along to eager loaders
  such as that of _orm.selectinload(), _orm.immediateload(), and
  _orm.subqueryload(), making it impossible to do things such as
  disabling the cache for a single statement or using
  schema_translate_map for a single statement, as well as the use of
  user-custom execution options. A change has been made where all
  user-facing execution options present for _orm.Session.execute() will
  be propagated along to additional loaders.

  As part of this change, the warning for "excessively deep" eager loaders
  leading to caching being disabled can be silenced on a per-statement
  basis by sending execution_options={"compiled_cache": None} to
  _orm.Session.execute(), which will disable caching for the full
  series of statements within that scope.

  References: #10231

• [orm] [bug] Fixed issue where internal cloning used by the ORM for expressions like
  _orm.relationship.Comparator.any() to produce correlated EXISTS
  constructs would interfere with the "cartesian product warning" feature of
  the SQL compiler, leading the SQL compiler to warn when all elements of the
  statement were correctly joined.

  References: #10124

• [orm] [bug] Fixed issue where the lazy="immediateload" loader strategy would place
  an internal loading token into the ORM mapped attribute under circumstances
  where the load should not occur, such as in a recursive self-referential
  load. As part of this change, the lazy="immediateload" strategy now
  honors the _orm.relationship.join_depth parameter for
  self-referential eager loads in the same way as that of other eager
  loaders, where leaving it unset or set at zero will lead to a
  self-referential immediateload not occurring, setting it to a value of one
  or greater will immediateload up until that given depth.

  References: #10139

• [orm] [bug] Fixed issue where dictionary-based collections such as
  _orm.attribute_keyed_dict() did not fully pickle/unpickle correctly,
  leading to issues when attempting to mutate such a collection after
  unpickling.

  References: #10175

• [orm] [bug] Fixed issue where chaining _orm.load_only() or other wildcard use of
  _orm.defer() from another eager loader using a _orm.aliased()
  against a joined inheritance subclass would fail to take effect for columns
  local to the superclass.

  References: #10125

• [orm] [bug] Fixed issue where an ORM-enabled _sql.select() construct would not
  render any CTEs added only via the _sql.Select.add_cte() method that
  were not otherwise referenced in the statement.

  References: #10167

examples

• [examples] [bug] The dogpile_caching examples have been updated for 2.0 style queries.
  Within the "caching query" logic itself there is one conditional added to
  differentiate between Query and select() when performing an
  invalidation operation.

engine

• [engine] [bug] Fixed critical issue where setting
  _sa.create_engine.isolation_level to AUTOCOMMIT (as opposed
  to using the _engine.Engine.execution_options() method) would fail to
  restore "autocommit" to a pooled connection if an alternate isolation level
  were temporarily selected using
  _engine.Connection.execution_options.isolation_level.

  References: #10147

sql

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

  This change is also backported to: 1.4.50

  References: #10213

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

  This change is also backported to: 1.4.50

  References: #10213

typing

• [typing] [usecase] Added new typing only utility functions Nullable() and
  NotNullable() to type a column or ORM class as, respectively,
  nullable or not nullable.
  These function are no-op at runtime, returning the input unchanged.

  References: #10173

• [typing] [bug] Typing improvements:

  -   `CursorResult` is returned for some forms of
      `_orm.Session.execute()` where DML without RETURNING is used
  
  -   fixed type for `_orm.Query.with_for_update.of` parameter within
      `_orm.Query.with_for_update()`
  
  -   improvements to `_DMLColumnArgument` type used by some DML methods to
      pass column expressions
  
  -   Add overload to `_sql.literal()` so that it is inferred that the
      return type is `BindParameter[NullType]` where
      `_sql.literal.type_` param is None
  
  -   Add overloads to `_sql.ColumnElement.op()` so that the inferred
      type when `_sql.ColumnElement.op.return_type` is not provided
      is `Callable[[Any], BinaryExpression[Any]]`
  
  -   Add missing overload to `_sql.ColumnElement.__add__()`
  

  Pull request courtesy Mehdi Gmira.

  References: #9185

• [typing] [bug] Fixed issue in _orm.Session and _asyncio.AsyncSession
  methods such as _orm.Session.connection() where the
  _orm.Session.connection.execution_options parameter were
  hardcoded to an internal type that is not user-facing.

  References: #10182

asyncio

• [asyncio] [usecase] Added new methods _asyncio.AsyncConnection.aclose() as a synonym for
  _asyncio.AsyncConnection.close() and
  _asyncio.AsyncSession.aclose() as a synonym for
  _asyncio.AsyncSession.close() to the
  _asyncio.AsyncConnection and _asyncio.AsyncSession
  objects, to provide compatibility with Python standard library
  @contextlib.aclosing construct. Pull request courtesy Grigoriev Semyon.

  References: #9698

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.

  This change is also backported to: 1.4.50

2.0.19

2.0.19

Released: July 15, 2023

orm

• [orm] [bug] Fixed issue where setting a relationship collection directly, where an
  object in the new collection were already present, would not trigger a
  cascade event for that object, leading to it not being added to the
  _orm.Session if it were not already present. This is similar in
  nature to #6471 and is a more apparent issue due to the removal of
  cascade_backrefs in the 2.0 series. The
  _orm.AttributeEvents.append_wo_mutation() event added as part of
  #6471 is now also emitted for existing members of a collection
  that are present in a bulk set of that same collection.

  References: #10089

• [orm] [bug] Fixed issue where objects that were associated with an unloaded collection
  via backref, but were not merged into the _orm.Session due to the
  removal of cascade_backrefs in the 2.0 series, would not emit a warning
  that these objects were not being included in a flush, even though they
  were pending members of the collection; in other such cases, a warning is
  emitted when a collection being flushed contains non-attached objects which
  will be essentially discarded. The addition of the warning for
  backref-pending collection members establishes greater consistency with
  collections that may be present or non-present and possibly flushed or not
  flushed at different times based on different relationship loading
  strategies.

  References: #10090

• [orm] [bug] [regression] Fixed additional regression caused by #9805 where more aggressive
  propagation of the "ORM" flag on statements could lead to an internal
  attribute error when embedding an ORM Query construct that
  nonetheless contained no ORM entities within a Core SQL statement, in this
  case ORM-enabled UPDATE and DELETE statements.

  References: #10098

engine

• [engine] [bug] Renamed _result.Row.t and _result.Row.tuple() to
  _result.Row._t and _result.Row._tuple(); this is to suit the
  policy that all methods and pre-defined attributes on Row should
  be in the style of Python standard library namedtuple where all fixed
  names have a leading underscore, to avoid name conflicts with existing
  column names. The previous method/attribute is now deprecated and will
  emit a deprecation warning.

  References: #10093

• [engine] [bug] Added detection for non-string, non-_engine.URL objects to the
  _engine.make_url() function, allowing ArgumentError to be thrown
  immediately, rather than causing failures later on. Special logic ensures
  that mock forms of _engine.URL are allowed through. Pull request
  courtesy Grigoriev Semyon.

  References: #10079

postgresql

• [postgresql] [bug] Fixed regression caused by improvements to PostgreSQL URL parsing in
  #10004 where "host" query string arguments that had colons in
  them, to support various third party proxy servers and/or dialects, would
  not parse correctly as these were evaluted as host:port combinations.
  Parsing has been updated to consider a colon as indicating a host:port
  value only if the hostname contains only alphanumeric characters with dots
  or dashes only (e.g. no slashes), followed by exactly one colon followed by
  an all-integer token of zero or more integers. In all other cases, the
  full string is taken as a host.

  References: #10069

• [postgresql] [bug] Fixed issue where comparisons to the _postgresql.CITEXT datatype
  would cast the right side to VARCHAR, leading to the right side not
  being interpreted as a CITEXT datatype, for the asyncpg, psycopg3 and
  pg80000 dialects. This led to the _postgresql.CITEXT type being
  essentially unusable for practical use; this is now fixed and the test
  suite has been corrected to properly assert that expressions are rendered
  correctly.

  References: #10096

2.0.18

2.0.18

Released: July 5, 2023

engine

• [engine] [bug] Adjusted the _sa.create_engine.schema_translate_map feature
  such that all schema names in the statement are now tokenized,
  regardless of whether or not a specific name is in the immediate schema
  translate map given, and to fallback to substituting the original name when
  the key is not in the actual schema translate map at execution time. These
  two changes allow for repeated use of a compiled object with schema
  schema_translate_maps that include or dont include various keys on each
  run, allowing cached SQL constructs to continue to function at runtime when
  schema translate maps with different sets of keys are used each time. In
  addition, added detection of schema_translate_map dictionaries which gain
  or lose a None key across calls for the same statement, which affects
  compilation of the statement and is not compatible with caching; an
  exception is raised for these scenarios.

  References: #10025

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.

  This change is also backported to: 1.4.49

  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.

  This change is also backported to: 1.4.49

extensions

• [extensions] [usecase] Added new option to association_proxy()
association_proxy.create_on_none_assignment; when an
  association proxy which refers to a scalar relationship is assigned the
  value None, and the referenced object is not present, a new object is
  created via the creator. This was apparently an undefined behavior in the
  1.2 series that was silently removed.

  References: #10013

typing

• [typing] [usecase] Improved typing when using standalone operator functions from
  sqlalchemy.sql.operators such as sqlalchemy.sql.operators.eq.

  References: #10054

• [typing] [bug] Fixed some of the typing within the _orm.aliased() construct to
  correctly accept a Table object that's been aliased with
  Table.alias(), as well as general support for FromClause
  objects to be passed as the "selectable" argument, since this is all
  supported.

  References: #10061

postgresql

• [postgresql] [usecase] Added multi-host support for the asyncpg dialect. General improvements and
  error checking added to the PostgreSQL URL routines for the "multihost" use
  case added as well. Pull request courtesy Ilia Dmitriev.

  References: #10004

• [postgresql] [bug] Added new parameter native_inet_types=False to all PostgreSQL
  dialects, which indicates converters used by the DBAPI to
  convert rows from PostgreSQL INET and CIDR columns
  into Python ipaddress datatypes should be disabled, returning strings
  instead. This allows code written to work with strings for these datatypes
  to be migrated to asyncpg, psycopg, or pg8000 without code changes
  other than adding this parameter to the _sa.create_engine()
  or _asyncio.create_async_engine() function call.

  References: #9945

mariadb

• [mariadb] [usecase] [reflection] Allowed reflecting _types.UUID columns from MariaDB. This allows
  Alembic to properly detect the type of such columns in existing MariaDB
  databases.

  References: #10028

mssql

• [mssql] [usecase] Added support for creation and reflection of COLUMNSTORE
  indexes in MSSQL dialect. Can be specified on indexes
  specifying mssql_columnstore=True.

  References: #7340

• [mssql] [bug] [sql] Fixed issue where performing Cast to a string type with an
  explicit collation would render the COLLATE clause inside the CAST
  function, which resulted in a syntax error.

  References: #9932

1.4.49

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.

2.0.17

2.0.17

Released: June 23, 2023

orm

• [orm] [bug] [regression] Fixed regression in the 2.0 series where a query that used
  undefer_group() with _orm.selectinload() or
  _orm.subqueryload() would raise an AttributeError. Pull request
  courtesy of Matthew Martin.

  References: #9870

• [orm] [bug] Fixed issue in ORM Annotated Declarative which prevented a
  _orm.declared_attr from being used on a mixin which did not return
  a Mapped datatype, and instead returned a supplemental ORM
  datatype such as AssociationProxy. The Declarative runtime would
  erroneously try to interpret this annotation as needing to be
  Mapped and raise an error.

  References: #9957

• [orm] [bug] [typing] Fixed typing issue where using the AssociationProxy return type
  from a _orm.declared_attr function was disallowed.

  References: #9957

• [orm] [bug] [regression] Fixed regression introduced in 2.0.16 by #9879 where passing a
  callable to the _orm.mapped_column.default parameter of
  _orm.mapped_column while also setting init=False would
  interpret this value as a Dataclass default value which would be assigned
  directly to new instances of the object directly, bypassing the default
  generator taking place as the _schema.Column.default
  value generator on the underlying _schema.Column. This condition
  is now detected so that the previous behavior is maintained, however a
  deprecation warning for this ambiguous use is emitted; to populate the
  default generator for a _schema.Column, the
  _orm.mapped_column.insert_default parameter should be used,
  which disambiguates from the _orm.mapped_column.default
  parameter whose name is fixed as per pep-681.

  References: #9936

• [orm] [bug] Additional hardening and documentation for the ORM _orm.Session
  "state change" system, which detects concurrent use of
  _orm.Session and _asyncio.AsyncSession objects; an
  additional check is added within the process to acquire connections from
  the underlying engine, which is a critical section with regards to internal
  connection management.

  References: #9973

• [orm] [bug] Fixed issue in ORM loader strategy logic which further allows for long
  chains of _orm.contains_eager() loader options across complex
  inheriting polymorphic / aliased / of_type() relationship chains to take
  proper effect in queries.

  References: #10006

• [orm] [bug] Fixed issue in support for the Enum datatype in the
  _orm.registry.type_annotation_map first added as part of
  #8859 where using a custom Enum with fixed configuration
  in the map would fail to transfer the Enum.name parameter,
  which among other issues would prevent PostgreSQL enums from working if the
  enum values were passed as individual values. Logic has been updated so
  that "name" is transferred over, but also that the default Enum
  which is against the plain Python enum.Enum class or other "empty" enum
  won't set a hardcoded name of "enum" either.

  References: #9963

orm declarative

• [orm] [declarative] [bug] A warning is emitted when an ORM _orm.relationship() and other
  MapperProperty objects are assigned to two different class
  attributes at once; only one of the attributes will be mapped. A warning
  for this condition was already in place for _schema.Column and
  _orm.mapped_column objects.

  References: #3532

typing

• [typing] [bug] Fixed typing issue which prevented _orm.WriteOnlyMapped and
  _orm.DynamicMapped attributes from being used fully within ORM
  queries.

  References: #9985

postgresql

• [postgresql] [usecase] The pg8000 dialect now supports RANGE and MULTIRANGE datatypes, using the
  existing RANGE API described at postgresql_ranges. Range and
  multirange types are supported in the pg8000 driver from version 1.29.8.
  Pull request courtesy Tony Locke.

  References: #9965

misc

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

  This change is also backported to: 1.4.49

2.0.16

2.0.16

Released: June 10, 2023

platform

• [platform] [usecase] Compatibility improvements allowing the complete test suite to pass
  on Python 3.12.0b1.

orm

• [orm] [usecase] Improved DeferredReflection.prepare() to accept arbitrary **kw
  arguments that are passed to _schema.MetaData.reflect(), allowing use
  cases such as reflection of views as well as dialect-specific arguments to
  be passed. Additionally, modernized the
  DeferredReflection.prepare.bind argument so that either an
  Engine or Connection are accepted as the "bind"
  argument.

  References: #9828

• [orm] [bug] Fixed issue where DeclarativeBaseNoMeta declarative base class
  would not function with non-mapped mixins or abstract classes, raising an
  AttributeError instead.

  References: #9862

• [orm] [bug] [regression] Fixed regression in the 2.0 series where the default value of
  _orm.validates.include_backrefs got changed to False for
  the _orm.validates() function. This default is now restored to
  True.

  References: #9820

• [orm] [bug] Fixed bug in new feature which allows a WHERE clause to be used in
  conjunction with orm_queryguide_bulk_update, added in version 2.0.11
  as part of #9583, where sending dictionaries that did not include
  the primary key values for each row would run through the bulk process and
  include "pk=NULL" for the rows, silently failing. An exception is now
  raised if primary key values for bulk UPDATE are not supplied.

  References: #9917

• [orm] [bug] [dataclasses] Fixed an issue where generating dataclasses fields that specified a
  default value and set init=False would not work.
  The dataclasses behavior in this case is to set the default
  value on the class, that's not compatible with the descriptors used
  by SQLAlchemy. To support this case the default is transformed to
  a default_factory when generating the dataclass.

  References: #9879

• [orm] [bug] A deprecation warning is emitted whenever a property is added to a
  _orm.Mapper where an ORM mapped property were already configured,
  or an attribute is already present on the class. Previously, there was a
  non-deprecation warning for this case that did not emit consistently. The
  logic for this warning has been improved so that it detects end-user
  replacement of attribute while not having false positives for internal
  Declarative and other cases where replacement of descriptors with new ones
  is expected.

  References: #9841

• [orm] [bug] Improved the argument chacking on the
  _orm.registry.map_imperatively.local_table parameter of the
  _orm.registry.map_imperatively() method, ensuring only a
  Table or other FromClause is passed, and not an
  existing mapped class, which would lead to undefined behavior as the object
  were further interpreted for a new mapping.

  References: #9869

• [orm] [bug] The _orm.InstanceState.unloaded_expirable attribute is a synonym
  for _orm.InstanceState.unloaded, and is now deprecated; this
  attribute was always implementation-specific and should not have been
  public.

  References: #9913

asyncio

• [asyncio] [usecase] Added new _asyncio.create_async_engine.async_creator parameter
  to create_async_engine(), which accomplishes the same purpose as the
  create_engine.creator parameter of create_engine().
  This is a no-argument callable that provides a new asyncio connection,
  using the asyncio database driver directly. The
  create_async_engine() function will wrap the driver-level connection
  in the appropriate structures. Pull request curtesy of Jack Wotherspoon.

  References: #8215

postgresql

• [postgresql] [usecase] [reflection] Cast NAME columns to TEXT when using ARRAY_AGG in PostgreSQL
  reflection. This seems to improve compatibility with some PostgreSQL
  derivatives that may not support aggregations on the NAME type.

  References: #9838

• [postgresql] [usecase] Unified the custom PostgreSQL operator definitions, since they are
  shared among multiple different data types.

  References: #9041

• [postgresql] [usecase] Added support for PostgreSQL 10 NULLS NOT DISTINCT feature of
  unique indexes and unique constraint using the dialect option
  postgresql_nulls_not_distinct.
  Updated the reflection logic to also correctly take this option
  into account.
  Pull request courtesy of Pavel Siarchenia.

  References: #8240

• [postgresql] [bug] Use proper precedence on PostgreSQL specific operators, such as @>.
  Previously the precedence was wrong, leading to wrong parenthesis when
  rendering against and ANY or ALL construct.

  References: #9836

• [postgresql] [bug] Fixed issue where the ColumnOperators.like.escape and similar
  parameters did not allow an empty string as an argument that would be
  passed through as the "escape" character; this is a supported syntax by
  PostgreSQL. Pull requset courtesy Martin Caslavsky.

  References: #9907

2.0.15

2.0.15

Released: May 19, 2023

orm

• [orm] [bug] As more projects are using new-style "2.0" ORM querying, it's becoming
  apparent that the conditional nature of "autoflush", being based on whether
  or not the given statement refers to ORM entities, is becoming more of a
  key behavior. Up until now, the "ORM" flag for a statement has been loosely
  based around whether or not the statement returns rows that correspond to
  ORM entities or columns; the original purpose of the "ORM" flag was to
  enable ORM-entity fetching rules which apply post-processing to Core result
  sets as well as ORM loader strategies to the statement. For statements
  that don't build on rows that contain ORM entities, the "ORM" flag was
  considered to be mostly unnecessary.

  It still may be the case that "autoflush" would be better taking effect for
  all usage of _orm.Session.execute() and related methods, even for
  purely Core SQL constructs. However, this still could impact legacy cases
  where this is not expected and may be more of a 2.1 thing. For now however,
  the rules for the "ORM-flag" have been opened up so that a statement that
  includes ORM entities or attributes anywhere within, including in the WHERE
  / ORDER BY / GROUP BY clause alone, within scalar subqueries, etc. will
  enable this flag. This will cause "autoflush" to occur for such statements
  and also be visible via the _orm.ORMExecuteState.is_orm_statement
  event-level attribute.

  References: #9805

postgresql

• [postgresql] [bug] [regression] Repaired the base Uuid datatype for the PostgreSQL dialect to
  make full use of the PG-specific UUID dialect-specific datatype when
  "native_uuid" is selected, so that PG driver behaviors are included. This
  issue became apparent due to the insertmanyvalues improvement made as part
  of #9618, where in a similar manner as that of #9739, the
  asyncpg driver is very sensitive to datatype casts being present or not,
  and the PostgreSQL driver-specific native UUID datatype must be invoked
  when this generic type is used so that these casts take place.

  References: #9808