2.0.6

2.0.6

Released: March 13, 2023

orm

• [orm] [bug] Fixed bug where the "active history" feature was not fully
  implemented for composite attributes, making it impossible to receive
  events that included the "old" value. This seems to have been the case
  with older SQLAlchemy versions as well, where "active_history" would
  be propagated to the underlying column-based attributes, but an event
  handler listening to the composite attribute itself would not be given
  the "old" value being replaced, even if the composite() were set up
  with active_history=True.

  Additionally, fixed a regression that's local to 2.0 which disallowed
  active_history on composite from being assigned to the impl with
  attr.impl.active_history=True.

  References: #9460

• [orm] [bug] Fixed regression involving pickling of Python rows between the cython and
  pure Python implementations of Row, which occurred as part of
  refactoring code for version 2.0 with typing. A particular constant were
  turned into a string based Enum for the pure Python version of
  Row whereas the cython version continued to use an integer
  constant, leading to deserialization failures.

  References: #9418

sql

• [sql] [bug] [regression] Fixed regression where the fix for #8098, which was released in
  the 1.4 series and provided a layer of concurrency-safe checks for the
  lambda SQL API, included additional fixes in the patch that failed to be
  applied to the main branch. These additional fixes have been applied.

  References: #9461

• [sql] [bug] Fixed regression where the _sql.select() construct would not be able
  to render if it were given no columns and then used in the context of an
  EXISTS, raising an internal exception instead. While an empty "SELECT" is
  not typically valid SQL, in the context of EXISTS databases such as
  PostgreSQL allow it, and in any case the condition now no longer raises
  an internal exception.

  References: #9440

typing

• [typing] [bug] Fixed typing issue where ColumnElement.cast() did not allow a
  TypeEngine argument independent of the type of the
  ColumnElement itself, which is the purpose of
  ColumnElement.cast().

  References: #9451

• [typing] [bug] Fixed issues to allow typing tests to pass under Mypy 1.1.1.

oracle

• [oracle] [bug] Fixed reflection bug where Oracle "name normalize" would not work correctly
  for reflection of symbols that are in the "PUBLIC" schema, such as
  synonyms, meaning the PUBLIC name could not be indicated as lower case on
  the Python side for the _schema.Table.schema argument. Using
  uppercase "PUBLIC" would work, but would then lead to awkward SQL queries
  including a quoted "PUBLIC" name as well as indexing the table under
  uppercase "PUBLIC", which was inconsistent.

  References: #9459

2.0.5.post1

2.0.5.post1

Released: March 5, 2023

orm

• [orm] [bug] Added constructor arguments to the built-in mapping collection types
  including KeyFuncDict, _orm.attribute_keyed_dict(),
  _orm.column_keyed_dict() so that these dictionary types may be
  constructed in place given the data up front; this provides further
  compatibility with tools such as Python dataclasses .asdict() which
  relies upon invoking these classes directly as ordinary dictionary classes.

  References: #9418

• [orm] [bug] [regression] Fixed multiple regressions due to #8372, involving
  _orm.attribute_mapped_collection() (now called
  _orm.attribute_keyed_dict()).

  First, the collection was no longer usable with "key" attributes that were
  not themselves ordinary mapped attributes; attributes linked to descriptors
  and/or association proxy attributes have been fixed.

  Second, if an event or other operation needed access to the "key" in order
  to populate the dictionary from an mapped attribute that was not
  loaded, this also would raise an error inappropriately, rather than
  trying to load the attribute as was the behavior in 1.4. This is also
  fixed.

  For both cases, the behavior of #8372 has been expanded.
  #8372 introduced an error that raises when the derived key that
  would be used as a mapped dictionary key is effectively unassigned. In this
  change, a warning only is emitted if the effective value of the ".key"
  attribute is None, where it cannot be unambiguously determined if this
  None was intentional or not. None will be not supported as mapped
  collection dictionary keys going forward (as it typically refers to NULL
  which means "unknown"). Setting
  _orm.attribute_keyed_dict.ignore_unpopulated_attribute will now
  cause such None keys to be ignored as well.

  References: #9424

• [orm] [bug] Identified that the sqlite and mssql+pyodbc dialects are now
  compatible with the SQLAlchemy ORM's "versioned rows" feature, since
  SQLAlchemy now computes rowcount for a RETURNING statement in this specific
  case by counting the rows returned, rather than relying upon
  cursor.rowcount. In particular, the ORM versioned rows use case
  (documented at mapper_version_counter) should now be fully
  supported with the SQL Server pyodbc dialect.

• [orm] [bug] Added support for the _orm.Mapper.polymorphic_load parameter to
  be applied to each mapper in an inheritance hierarchy more than one level
  deep, allowing columns to load for all classes in the hierarchy that
  indicate "selectin" using a single statement, rather than ignoring
  elements on those intermediary classes that nonetheless indicate they also
  would participate in "selectin" loading and were not part of the
  base-most SELECT statement.

  References: #9373

• [orm] [bug] Continued the fix for #8853, allowing the _orm.Mapped
  name to be fully qualified regardless of whether or not
  from __annotations__ import future were present. This issue first fixed
  in 2.0.0b3 confirmed that this case worked via the test suite, however the
  test suite apparently was not testing the behavior for the name
  _orm.Mapped not being locally present at all; string resolution
  has been updated to ensure the _orm.Mapped symbol is locatable as
  applies to how the ORM uses these functions.

  References: #8853, #9335

orm declarative

• [bug] [orm declarative] Fixed issue where new _orm.mapped_column.use_existing_column
  feature would not work if the two same-named columns were mapped under
  attribute names that were differently-named from an explicit name given to
  the column itself. The attribute names can now be differently named when
  using this parameter.

  References: #9332

engine

• [engine] [performance] A small optimization to the Cython implementation of Result
  using a cdef for a particular int value to avoid Python overhead. Pull
  request courtesy Matus Valo.

  References: #9343

• [engine] [bug] Fixed bug where _engine.Row objects could not be reliably unpickled
  across processes due to an accidental reliance on an unstable hash value.

  References: #9423

sql

• [sql] [bug] [regression] Restore the nullslast() and nullsfirst() legacy functions
  into the sqlalchemy import namespace. Previously, the newer
  nulls_last() and nulls_first() functions were available, but
  the legacy ones were inadvertently removed.

  References: #9390

schema

• [schema] Validate that when provided the _schema.MetaData.schema
  argument of _schema.MetaData is a string.

typing

• [typing] [usecase] Exported the type returned by
  _orm.scoped_session.query_property() using a new public type
  orm.QueryPropertyDescriptor.

  References: #9338

• [typing] [bug] Fixed bug where the _engine.Connection.scalars() method was not typed
  as allowing a multiple-parameters list, which is now supported using
  insertmanyvalues operations.

• [typing] [bug] Improved typing for the mapping passed to Insert.values() and
  Update.values() to be more open-ended about collection type, by
  indicating read-only Mapping instead of writeable Dict which would
  error out on too limited of a key type.

  References: #9376

• [typing] [bug] Added missing init overload to the _types.Numeric type object so
  that pep-484 type checkers may properly resolve the complete type, deriving
  from the _types.Numeric.asdecimal parameter whether Decimal
  or float objects will be represented.

  References: #9391

• [typing] [bug] Fixed typing bug where _sql.Select.from_statement() would not accept
  _sql.text() or TextualSelect objects as a valid type.
  Additionally repaired the TextClause.columns method to have a
  return type, which was missing.

  References: #9398

• [typing] [bug] Fixed typing issue where _orm.with_polymorphic() would not
  record the class type correctly.

  References: #9340

postgresql

• [postgresql] [bug] Fixed issue in PostgreSQL _postgresql.ExcludeConstraint where
  literal values were being compiled as bound parameters and not direct
  inline values as is required for DDL.

  References: #9349

• [postgresql] [bug] Fixed issue where the PostgreSQL _postgresql.ExcludeConstraint
  construct would not be copyable within operations such as
  _schema.Table.to_metadata() as well as within some Alembic scenarios,
  if the constraint contained textual expression elements.

  References: #9401

mysql

• [mysql] [bug] [postgresql] The support for pool ping listeners to receive exception events via the
  DialectEvents.handle_error() event added in 2.0.0b1 for
  #5648 failed to take into account dialect-specific ping routines
  such as that of MySQL and PostgreSQL. The dialect feature has been reworked
  so that all dialects participate within event handling. Additionally,
  a new boolean element ExceptionContext.is_pre_ping is added
  which identifies if this operation is occurring within the pre-ping
  operation.

  For this release, third party dialects which implement a custom
  _engine.Dialect.do_ping() method can opt in to the newly improved
  behavior by having their method no longer catch exceptions or check
  exceptions for "is_disconnect", instead just propagating all exceptions
  outwards. Checking the exception for "is_disconnect" is now done by an
  enclosing method on the default dialect, which ensures that the event hook
  is invoked for all exception scenarios before testing the exception as a
  "disconnect" exception. If an existing do_ping() method continues to
  catch exceptions and check "is_disconnect", it will continue to work as it
  did previously, but handle_error hooks will not have access to the
  exception if it isn't propagated outwards.

  References: #5648

sqlite

• [sqlite] [bug] [regression] Fixed regression for SQLite connections where use of the deterministic
  parameter when establishing database functions would fail for older SQLite
  versions, those prior to version 3.8.3. The version checking logic has been
  improved to accommodate for this case.

  References: #9379

mssql

• [mssql] [bug] Fixed issue in the new Uuid datatype which prevented it from
  working with the pymssql driver. As...

2.0.4

2.0.4

Released: February 17, 2023

orm

• [orm] [usecase] The _orm.Session.refresh() method will now immediately load a
  relationship-bound attribute that is explicitly named within the
  _orm.Session.refresh.attribute_names collection even if it is
  currently linked to the "select" loader, which normally is a "lazy" loader
  that does not fire off during a refresh. The "lazy loader" strategy will
  now detect that the operation is specifically a user-initiated
  _orm.Session.refresh() operation which named this attribute
  explicitly, and will then call upon the "immediateload" strategy to
  actually emit SQL to load the attribute. This should be helpful in
  particular for some asyncio situations where the loading of an unloaded
  lazy-loaded attribute must be forced, without using the actual lazy-loading
  attribute pattern not supported in asyncio.

  References: #9298

• [orm] [bug] [regression] Fixed regression introduced in version 2.0.2 due to #9217 where
  using DML RETURNING statements, as well as
  _sql.Select.from_statement() constructs as was "fixed" in
  #9217, in conjunction with ORM mapped classes that used
  expressions such as with _orm.column_property(), would lead to an
  internal error within Core where it would attempt to match the expression
  by name. The fix repairs the Core issue, and also adjusts the fix in
  #9217 to not take effect for the DML RETURNING use case, where it
  adds unnecessary overhead.

  References: #9273

• [orm] [bug] Marked the internal EvaluatorCompiler module as private to the ORM, and
  renamed it to _EvaluatorCompiler. For users that may have been relying
  upon this, the name EvaluatorCompiler is still present, however this
  use is not supported and will be removed in a future release.

• [orm] [use_case] To accommodate a change in column ordering used by ORM Declarative in
  SQLAlchemy 2.0, a new parameter _orm.mapped_column.sort_order
  has been added that can be used to control the order of the columns defined
  in the table by the ORM, for common use cases such as mixins with primary
  key columns that should appear first in tables. The change notes at
  change_9297 illustrate the default change in ordering behavior
  (which is part of all SQLAlchemy 2.0 releases) as well as use of the
  _orm.mapped_column.sort_order to control column ordering when
  using mixins and multiple classes (new in 2.0.4).

  References: #9297

orm declarative

• [usecase] [orm declarative] Added new parameter dataclasses_callable to both the
  _orm.MappedAsDataclass class as well as the
  _orm.registry.mapped_as_dataclass() method which allows an
  alternative callable to Python dataclasses.dataclass to be used in
  order to produce dataclasses. The use case here is to drop in Pydantic's
  dataclass function instead. Adjustments have been made to the mixin support
  added for #9179 in version 2.0.1 so that the __annotations__
  collection of the mixin is rewritten to not include the
  _orm.Mapped container, in the same way as occurs with mapped
  classes, so that the Pydantic dataclasses constructor is not exposed to
  unknown types.

  References: #9266

sql

• [sql] [bug] Fixed issue where element types of a tuple value would be hardcoded to take
  on the types from a compared-to tuple, when the comparison were using the
  ColumnOperators.in_() operator. This was inconsistent with the usual
  way that types are determined for a binary expression, which is that the
  actual element type on the right side is considered first before applying
  the left-hand-side type.

  References: #9313

• [sql] Added public property _schema.Table.autoincrement_column that
  returns the column identified as autoincrementing in the column.

  References: #9277

typing

• [typing] [usecase] Improved the typing support for the hybrids_toplevel
  extension, updated all documentation to use ORM Annotated Declarative
  mappings, and added a new modifier called hybrid_property.inplace.
  This modifier provides a way to alter the state of a hybrid_property
  in place, which is essentially what very early versions of hybrids
  did, before SQLAlchemy version 1.2.0 #3912 changed this to
  remove in-place mutation. This in-place mutation is now restored on an
  opt-in basis to allow a single hybrid to have multiple methods
  set up, without the need to name all the methods the same and without the
  need to carefully "chain" differently-named methods in order to maintain
  the composition. Typing tools such as Mypy and Pyright do not allow
  same-named methods on a class, so with this change a succinct method
  of setting up hybrids with typing support is restored.

  References: #9321

oracle

• [oracle] [bug] Adjusted the behavior of the thick_mode parameter for the
  oracledb dialect to correctly accept False as a value.
  Previously, only None would indicate that thick mode should be
  disabled.

  References: #9295

2.0.3

2.0.3

Released: February 9, 2023

sql

• [sql] [bug] [regression] Fixed critical regression in SQL expression formulation in the 2.0 series
  due to #7744 which improved support for SQL expressions that
  contained many elements against the same operator repeatedly; parenthesis
  grouping would be lost with expression elements beyond the first two
  elements.

  References: #9271

typing

• [typing] [bug] Remove typing.Self workaround, now using PEP 673 for most methods
  that return Self. Pull request courtesy Yurii Karabas.

  References: #9254

2.0.2

2.0.2

Released: February 6, 2023

orm

• [orm] [usecase] Added new event hook _orm.MapperEvents.after_mapper_constructed(),
  which supplies an event hook to take place right as the
  _orm.Mapper object has been fully constructed, but before the
  _orm.registry.configure() call has been called. This allows code that
  can create additional mappings and table structures based on the initial
  configuration of a _orm.Mapper, which also integrates within
  Declarative configuration. Previously, when using Declarative, where the
  _orm.Mapper object is created within the class creation process,
  there was no documented means of running code at this point. The change
  is to immediately benefit custom mapping schemes such as that
  of the examples_versioned_history example, which generate additional
  mappers and tables in response to the creation of mapped classes.

  References: #9220

• [orm] [usecase] The infrequently used _orm.Mapper.iterate_properties attribute and
  _orm.Mapper.get_property() method, which are primarily used
  internally, no longer implicitly invoke the _orm.registry.configure()
  process. Public access to these methods is extremely rare and the only
  benefit to having _orm.registry.configure() would have been allowing
  "backref" properties be present in these collections. In order to support
  the new _orm.MapperEvents.after_mapper_constructed() event, iteration
  and access to the internal _orm.MapperProperty objects is now
  possible without triggering an implicit configure of the mapper itself.

  The more-public facing route to iteration of all mapper attributes, the
  _orm.Mapper.attrs collection and similar, will still implicitly
  invoke the _orm.registry.configure() step thus making backref
  attributes available.

  In all cases, the _orm.registry.configure() is always available to
  be called directly.

  References: #9220

• [orm] [bug] [regression] Fixed obscure ORM inheritance issue caused by #8705 where some
  scenarios of inheriting mappers that indicated groups of columns from the
  local table and the inheriting table together under a
  _orm.column_property() would nonetheless warn that properties of the
  same name were being combined implicitly.

  References: #9232

• [orm] [bug] [regression] Fixed regression where using the _orm.Mapper.version_id_col
  feature with a regular Python-side incrementing column would fail to work
  for SQLite and other databases that don't support "rowcount" with
  "RETURNING", as "RETURNING" would be assumed for such columns even though
  that's not what actually takes place.

  References: #9228

• [orm] [bug] [regression] Fixed regression when using _sql.Select.from_statement() in an ORM
  context, where matching of columns to SQL labels based on name alone was
  disabled for ORM-statements that weren't fully textual. This would prevent
  arbitrary SQL expressions with column-name labels from matching up to the
  entity to be loaded, which previously would work within the 1.4
  and previous series, so the previous behavior has been restored.

  References: #9217

orm declarative

• [bug] [orm declarative] Fixed regression caused by the fix for #9171, which itself was
  fixing a regression, involving the mechanics of __init__() on classes
  that extend from _orm.DeclarativeBase. The change made it such
  that __init__() was applied to the user-defined base if there were no
  __init__() method directly on the class. This has been adjusted so that
  __init__() is applied only if no other class in the hierarchy of the
  user-defined base has an __init__() method. This again allows
  user-defined base classes based on _orm.DeclarativeBase to include
  mixins that themselves include a custom __init__() method.

  References: #9249

• [bug] [orm declarative] Fixed issue in ORM Declarative Dataclass mappings related to newly added
  support for mixins added in 2.0.1 via #9179, where a combination
  of using mixins plus ORM inheritance would mis-classify fields in some
  cases leading to field-level dataclass arguments such as init=False being
  lost.

  References: #9226

• [bug] [orm declarative] Repaired ORM Declarative mappings to allow for the
  _orm.Mapper.primary_key parameter to be specified within
  __mapper_args__ when using _orm.mapped_column(). Despite this
  usage being directly in the 2.0 documentation, the _orm.Mapper was
  not accepting the _orm.mapped_column() construct in this context. Ths
  feature was already working for the _orm.Mapper.version_id_col
  and _orm.Mapper.polymorphic_on parameters.

  As part of this change, the __mapper_args__ attribute may be specified
  without using _orm.declared_attr() on a non-mapped mixin class,
  including a "primary_key" entry that refers to _schema.Column
  or _orm.mapped_column() objects locally present on the mixin;
  Declarative will also translate these columns into the correct ones for a
  particular mapped class. This again was working already for the
  _orm.Mapper.version_id_col and
  _orm.Mapper.polymorphic_on parameters. Additionally,
  elements within "primary_key" may be indicated as string names of
  existing mapped properties.

  References: #9240

• [bug] [orm declarative] An explicit error is raised if a mapping attempts to mix the use of
  _orm.MappedAsDataclass with
  _orm.registry.mapped_as_dataclass() within the same class hierarchy,
  as this produces issues with the dataclass function being applied at the
  wrong time to the mapped class, leading to errors during the mapping
  process.

  References: #9211

examples

• [examples] [bug] Reworked the examples_versioned_history to work with
  version 2.0, while at the same time improving the overall working of
  this example to use newer APIs, including a newly added hook
  _orm.MapperEvents.after_mapper_constructed().

  References: #9220

sql

• [sql] [usecase] Added a full suite of new SQL bitwise operators, for performing
  database-side bitwise expressions on appropriate data values such as
  integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich.

  References: #8780

asyncio

• [asyncio] [bug] Repaired a regression caused by the fix for #8419 which caused
  asyncpg connections to be reset (i.e. transaction rollback() called)
  and returned to the pool normally in the case that the connection were not
  explicitly returned to the connection pool and was instead being
  intercepted by Python garbage collection, which would fail if the garbage
  collection operation were being called outside of the asyncio event loop,
  leading to a large amount of stack trace activity dumped into logging
  and standard output.

  The correct behavior is restored, which is that all asyncio connections
  that are garbage collected due to not being explicitly returned to the
  connection pool are detached from the pool and discarded, along with a
  warning, rather than being returned the pool, as they cannot be reliably
  reset. In the case of asyncpg connections, the asyncpg-specific
  terminate() method will be used to end the connection more gracefully
  within this process as opposed to just dropping it.

  This change includes a small behavioral change that is hoped to be useful
  for debugging asyncio applications, where the warning that's emitted in the
  case of asyncio connections being unexpectedly garbage collected has been
  made slightly more aggressive by moving it outside of a try/except
  block and into a finally: block, where it will emit unconditionally
  regardless of whether the detach/termination operation succeeded or not. It
  will also have the effect that applications or test suites which promote
  Python warnings to exceptions will see this as a full exception raise,
  whereas previously it was not possible for this warning to actually
  propagate as an exception. Applications and test suites which need to
  tolerate this warning in the interim should adjust the Python warnings
  filter to allow these warnings to not raise.

  The behavior for traditional sync connections remains unchanged, that
  garbage collected connections continue to be returned to the pool normally
  without emitting a warning. This will likely be changed in a future major
  release to at least emit a similar warning as is emitted for asyncio
  drivers, as it is a usage error for pooled connections to be intercepted by
  garbage collection without being properly returned to the pool.

  References: #9237

mysql

• [mysql] [bug] [regression] Fixed regression caused by issue #9058 which adjusted the MySQL
  dialect's has_table() to again use "DESCRIBE", where the specific error
  code raised by MySQL version 8 whe...

2.0.1

2.0.1

Released: February 1, 2023

orm

• [orm] [bug] [regression] Fixed regression where ORM models that used joined table inheritance with a
  composite foreign key would encounter an internal error in the mapper
  internals.

  References: #9164

• [orm] [bug] Added support for PEP 484 NewType to be used in the
  _orm.registry.type_annotation_map as well as within
  Mapped constructs. These types will behave in the same way as
  custom subclasses of types right now; they must appear explicitly within
  the _orm.registry.type_annotation_map to be mapped.

  References: #9175

• [orm] [bug] When using the MappedAsDataclass superclass, all classes within
  the hierarchy that are subclasses of this class will now be run through the
  @dataclasses.dataclass function whether or not they are actually
  mapped, so that non-ORM fields declared on non-mapped classes within the
  hierarchy will be used when mapped subclasses are turned into dataclasses.
  This behavior applies both to intermediary classes mapped with
  __abstract__ = True as well as to the user-defined declarative base
  itself, assuming MappedAsDataclass is present as a superclass for
  these classes.

  This allows non-mapped attributes such as InitVar declarations on
  superclasses to be used, without the need to run the
  @dataclasses.dataclass decorator explicitly on each non-mapped class.
  The new behavior is considered as correct as this is what the PEP 681
  implementation expects when using a superclass to indicate dataclass
  behavior.

  References: #9179

• [orm] [bug] Added support for PEP 586 Literal[] to be used in the
  _orm.registry.type_annotation_map as well as within
  Mapped constructs. To use custom types such as these, they must
  appear explicitly within the _orm.registry.type_annotation_map
  to be mapped. Pull request courtesy Frederik Aalund.

  As part of this change, the support for sqltypes.Enum in the
  _orm.registry.type_annotation_map has been expanded to include
  support for Literal[] types consisting of string values to be used,
  in addition to enum.Enum datatypes. If a Literal[] datatype
  is used within Mapped[] that is not linked in
  _orm.registry.type_annotation_map to a specific datatype,
  a sqltypes.Enum will be used by default.

  References: #9187

• [orm] [bug] Fixed issue involving the use of sqltypes.Enum within the
  _orm.registry.type_annotation_map where the
  _sqltypes.Enum.native_enum parameter would not be correctly
  copied to the mapped column datatype, if it were overridden
  as stated in the documentation to set this parameter to False.

  References: #9200

• [orm] [bug] [regression] Fixed regression in DeclarativeBase class where the registry's
  default constructor would not be applied to the base itself, which is
  different from how the previous _orm.declarative_base() construct
  works. This would prevent a mapped class with its own __init__() method
  from calling super().__init__() in order to access the registry's
  default constructor and automatically populate attributes, instead hitting
  object.__init__() which would raise a TypeError on any arguments.

  References: #9171

• [orm] [bug] Improved the ruleset used to interpret PEP 593 Annotated types when
  used with Annotated Declarative mapping, the inner type will be checked for
  "Optional" in all cases which will be added to the criteria by which the
  column is set as "nullable" or not; if the type within the Annotated
  container is optional (or unioned with None), the column will be
  considered nullable if there are no explicit
  _orm.mapped_column.nullable parameters overriding it.

  References: #9177

• [orm] [bug] Improved the error reporting when linking strategy options from a base
  class to another attribute that's off a subclass, where of_type()
  should be used. Previously, when Load.options() is used, the message
  would lack informative detail that of_type() should be used, which was
  not the case when linking the options directly. The informative detail now
  emits even if Load.options() is used.

  References: #9182

sql

• [sql] [bug] Corrected the fix for #7664, released in version 2.0.0, to also
  include DropSchema which was inadvertently missed in this fix,
  allowing stringification without a dialect. The fixes for both constructs
  is backported to the 1.4 series as of 1.4.47.

  References: #7664

• [sql] [bug] [regression] Fixed regression related to the implementation for the new
  "insertmanyvalues" feature where an internal TypeError would occur in
  arrangements where a _sql.insert() would be referred towards inside
  of another _sql.insert() via a CTE; made additional repairs for this
  use case for positional dialects such as asyncpg when using
  "insertmanyvalues".

  References: #9173

typing

• [typing] [bug] Opened up typing on Select.with_for_update.of to also accept
  table and mapped class arguments, as seems to be available for the MySQL
  dialect.

  References: #9174

• [typing] [bug] Fixed typing for limit/offset methods including Select.limit(),
  Select.offset(), _orm.Query.limit(), _orm.Query.offset()
  to allow None, which is the documented API to "cancel" the current
  limit/offset.

  References: #9183

• [typing] [bug] Fixed typing issue where _orm.mapped_column() objects typed as
  _orm.Mapped wouldn't be accepted in schema constraints such as
  _schema.ForeignKey, _schema.UniqueConstraint or
  _schema.Index.

  References: #9170

• [typing] [bug] Fixed typing for _expression.ColumnElement.cast() to accept
  both Type[TypeEngine[T]] and TypeEngine[T]; previously
  only TypeEngine[T] was accepted. Pull request courtesy Yurii Karabas.

  References: #9156

2.0.0

2.0.0

Released: January 26, 2023

orm

• [orm] [bug] Improved the notification of warnings that are emitted within the configure
  mappers or flush process, which are often invoked as part of a different
  operation, to add additional context to the message that indicates one of
  these operations as the source of the warning within operations that may
  not be obviously related.

  References: #7305

orm extensions

• [feature] [orm extensions] Added new option to horizontal sharding API
  _horizontal.set_shard_id which sets the effective shard identifier
  to query against, for both the primary query as well as for all secondary
  loaders including relationship eager loaders as well as relationship and
  column lazy loaders.

  References: #7226

• [usecase] [orm extensions] Added new feature to AutomapBase for autoload of classes across
  multiple schemas which may have overlapping names, by providing a
  AutomapBase.prepare.modulename_for_table parameter which
  allows customization of the __module__ attribute of newly generated
  classes, as well as a new collection AutomapBase.by_module, which
  stores a dot-separated namespace of module names linked to classes based on
  the __module__ attribute.

  Additionally, the AutomapBase.prepare() method may now be invoked
  any number of times, with or without reflection enabled; only newly
  added tables that were not previously mapped will be processed on each
  call. Previously, the MetaData.reflect() method would need to be
  called explicitly each time.

  References: #5145

sql

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

  This change is also backported to: 1.4.47

  References: #7664

typing

• [typing] [bug] Added typing for the built-in generic functions that are available from the
  :data:_sql.func namespace, which accept a particular set of arguments and
  return a particular type, such as for _sql.count,
  _sql.current_timestamp, etc.

  Unknown interpreted text role "data".

  References: #9129

• [typing] [bug] Corrected the type passed for "lambda statements" so that a plain lambda is
  accepted by mypy, pyright, others without any errors about argument types.
  Additionally implemented typing for more of the public API for lambda
  statements and ensured StatementLambdaElement is part of the
  Executable hierarchy so it's typed as accepted by
  _engine.Connection.execute().

  References: #9120

• [typing] [bug] The _sql.ColumnOperators.in_() and
  _sql.ColumnOperators.not_in() methods are typed to include
  Iterable[Any] rather than Sequence[Any] for more flexibility in
  argument type.

  References: #9122

• [typing] [bug] The _sql.or_() and _sql.and_() from a typing perspective
  require the first argument to be present, however these functions still
  accept zero arguments which will emit a deprecation warning at runtime.
  Typing is also added to support sending the fixed literal False for
  _sql.or_() and True for _sql.and_() as the first argument
  only, however the documentation now indicates sending the
  _sql.false() and _sql.true() constructs in these cases as a
  more explicit approach.

  References: #9123

• [typing] [bug] Fixed typing issue where iterating over a _orm.Query object
  was not correctly typed.

  References: #9125

• [typing] [bug] Fixed typing issue where the object type when using _engine.Result
  as a context manager were not preserved, indicating _engine.Result
  in all cases rather than the specific _engine.Result sub-type.
  Pull request courtesy Martin Baláž.

  References: #9136

• [typing] [bug] Fixed issue where using the _orm.relationship.remote_side
  and similar parameters, passing an annotated declarative object typed as
  _orm.Mapped, would not be accepted by the type checker.

  References: #9150

• [typing] [bug] Added typing to legacy operators such as isnot(), notin_(), etc.
  which previously were referencing the newer operators but were not
  themselves typed.

  References: #9148

mssql

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

  This change is also backported to: 1.4.47

  References: #9133

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

  This change is also backported to: 1.4.47

  References: #9133

• [mssql] [bug] [regression] The newly added comment reflection and rendering capability of the MSSQL
  dialect, added in #7844, will now be disabled by default if it
  cannot be determined that an unsupported backend such as Azure Synapse may
  be in use; this backend does not support table and column comments and does
  not support the SQL Server routines in use to generate them as well as to
  reflect them. A new parameter supports_comments is added to the dialect
  which defaults to None, indicating that comment support should be
  auto-detected. When set to True or False, the comment support is
  either enabled or disabled unconditionally.

  References: #9142

oracle

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

  This change is also backported to: 1.4.47

  References: #5047

2.0.0rc3

2.0.0rc3

Released: January 18, 2023

orm

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

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

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

  References: #9060

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

  References: #9099

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

  References: #9100

sql

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

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

  This change is also backported to: 1.4.47

  References: #9075

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

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

  This change is also backported to: 1.4.47

  References: #9075

typing

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

  References: #9096

mypy

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

  This change is also backported to: 1.4.47

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

  This change is also backported to: 1.4.47

  References: #9102

postgresql

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

  References: #9106

oracle

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

  References: #9086

2.0.0rc2

2.0.0rc2

Released: January 9, 2023

orm

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

  References: #9071

typing

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

  References: #9067

postgresql

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

  This change is also backported to: 1.4.47

  References: #9048

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

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

  Pull request curtesy of Guilherme Martins Crocetti.

  References: #7147

mysql

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

  This change is also backported to: 1.4.47

  References: #9047

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

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

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

  References: #9058

oracle

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

  References: #9059

1.4.46

1.4.46

Released: January 3, 2023

general

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

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

  References: #8983

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

  References: #8995

orm

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

  References: #9033

engine

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

  References: #8974

sql

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

  References: #9009

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

  References: #8989

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

  References: #9029

postgresql

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

  References: #8393

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

  References: #9023

sqlite

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

  References: #8969

tests

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

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

  References: #9002