2.1.1

Released: September 25, 2026

platform

• [platform] [bug] Removed the legacy underscore-separated extra names such as
  mssql_pymssql and postgresql_psycopg from pyproject.toml.
  They normalize to the same names as the existing dash-separated extras,
  which is disallowed by PEP 685, and caused the 2.1.0 source
  distribution to fail to build with installers that enforce this rule,
  such as uv. The underscore spellings continue to work when installing,
  as installers normalize extra names before matching them.

  References: #13604

2.1.0

Released: September 24, 2026

orm

• [orm] [feature] Added _orm.composite.column_template parameter to
  _orm.composite(). When the composite class is a dataclass, this
  parameter accepts a string template such as "person_%s", containing
  exactly one %s placeholder, that's used to generate column names for
  dataclass fields that don't otherwise have an explicit name, rather than
  using the bare field name. This removes the need to hand-write a
  _orm.mapped_column() for each field when the same composite dataclass
  is mapped multiple times on the same class with different column-name
  prefixes. Pull request courtesy Leonardo Rosa.

  References: #12575

• [orm] [bug] Fixed issue where pickling an ORM object that had an instance level lazy
  loader established, such as when the _orm.raiseload() option is used,
  would emit a spurious warning regarding the loader containing additional
  criteria, if the object had itself been unpickled from a previous
  serialization. This would occur for objects that cross more than one
  serialization boundary, such as when using multiprocessing.

  This change is also backported to: 2.0.53

  References: #13574

• [orm] [bug] Fixed issue where calling _orm.aliased() against an existing
  _orm.aliased() construct, without passing an explicit selectable,
  would disregard the selectable of the existing construct and produce an
  alias of the mapped table instead, if that selectable were anything other
  than a table or a plain subquery, leading to incorrect results and/or
  non-working queries.

  This includes _orm.aliased() against a _orm.with_polymorphic()
  construct, which would previously produce an alias of the base mapped
  class only, discarding the polymorphic selectable and additional mappers.
  The new construct now retains these, so that criteria against subclass
  attributes and the innerjoin and selectable parameters of
  _orm.with_polymorphic() take effect, and subclass columns are loaded
  up front. The SQL rendered for these constructs now includes the
  polymorphic selectable.

  This change is also backported to: 2.0.53

  References: #13583, #13584

• [orm] [bug] Fixed issue where using _orm.aliased() with both the
  _orm.aliased.name and _orm.aliased.flat
  parameters, against an entity that is mapped to a join which includes
  anonymously named aliases, would embed the anonymous name symbol within
  the names generated for each element of the join, producing unusable
  SQL. An anonymously named element of the join is now aliased
  anonymously.

  This change is also backported to: 2.0.53

  References: #13583

sql

• [sql] [bug] Fixed issue where calling _sql.CTE.alias() on a _sql.CTE
  that was itself produced by _sql.CTE.alias() would render SQL that
  referred to the name of the intermediate alias, which is not present in
  the WITH clause, rather than to the name of the original CTE, producing
  invalid SQL.

  This change is also backported to: 2.0.53

  References: #13583

postgresql

• [postgresql] [usecase] PostgreSQL index reflection now reports so-called "invalid" indexes with
  postgresql_invalid=True in the Inspector's dialect_options.
  Reflected Index objects expose this state in the new
  read-only Index.reflect_only_elements mapping, separate from
  DDL options. Added new marker DialectKWArgConst.REFLECTED_ONLY
  which may be used by third party dialects for similar features within
  the DefaultDialect.construct_arguments registry. Pull request
  courtesy Max Azatian.

  References: #13577

mssql

• [mssql] [bug] Adjusted the mssql+mssqlpython dialect for mssql-python version 1.15,
  which now binds all Python Decimal values as SQL_NUMERIC. The
  conversion of very large and very small Decimal values to strings,
  carried over from the pyodbc dialect, is no longer applied for this
  version of the driver, as the string values would otherwise be converted
  by SQL Server to the narrower numeric type of other Decimal values in
  the same statement, such as within a multiple-row INSERT, raising an
  arithmetic overflow error.

  References: #13585

• [mssql] [bug] Fixed issue where Dialect.dbapi_version would raise
  AttributeError for the mssql+mssqlpython dialect, as the version
  was looked up using a version attribute that is not present on the
  mssql_python module, rather than __version__.

  References: #13585

oracle

• [oracle] [usecase] Added support for oracledb's new terminate() feature, which allows for
  clean termination of an Oracle database connection in an asyncio context
  where the connection's state has fallen out of the event loop, and needs to
  be garbage collected. The feature is enabled automatically when using
  oracledb 26.0.0 or greater.

  This change is also backported to: 2.0.53

  References: #13578

2.1.0rc2

Released: September 8, 2026

orm

• [orm] [usecase] The collection of per-mapper / per-table binds established by the
  _orm.Session.binds parameter, as well as by the
  _orm.Session.bind_mapper() and _orm.Session.bind_table()
  methods, is now available publicly as _orm.Session.binds; the
  collection was previously stored under a private, name-mangled attribute.
  It is an immutable dictionary, and is replaced rather than mutated when a
  new bind is added, so a reference to it will not observe subsequent
  changes. The collection is also proxied by
  _orm.scoping.scoped_session.

  References: #13563

• [orm] [bug] Fixed issue where an expression passed to _orm.with_expression()
  that embedded a _sql.select(), such as a correlated
  _sql.exists(), would fail to populate the attribute correctly on
  the second and subsequent executions of an otherwise identical
  statement, when the _orm.query_expression() attribute was loaded
  by a relationship loader that emits a second query, i.e.
  _orm.selectinload(), _orm.lazyload() or
  _orm.immediateload().

  This change is also backported to: 2.0.53

  References: #13560

• [orm] [bug] Fixed memory issue where mapped classes, along with their
  Table and _orm.Mapper objects, would not be garbage
  collected after the _orm.registry in which they were mapped had
  been disposed and dereferenced. The issue would occur for mappings that
  made use of _orm.relationship() together with constructs such as an
  Index established against an ORM-annotated expression.

  This change is also backported to: 2.0.53

  References: #13566

orm declarative

• [bug] [orm declarative] Fixed memory issue where a declarative class making use of the
  __declare_first__() or __declare_last__() hooks could never be
  garbage collected, as each such class established its own permanent
  _orm.Mapper-wide event listener referring to it. These hooks
  are now invoked by _orm.registry-local RegistryEvents
  listeners which refer to the classes weakly, and which are invoked only
  for the _orm.registry being configured. As the hooks are now
  located as the class is added to the _orm.registry, they also
  take effect for classes mapped using
  _orm.registry.map_imperatively().

  References: #9147

engine

• [engine] [changed] Improved the implementation of _pool.Pool to use
  weakref.finalize() instead of a weakref.ref() finalizer to handle
  GC cleanup of non-detached, pooled connections that were not explicitly
  closed.

asyncio

• [asyncio] [usecase] _asyncio.AsyncSession.bind and
  _asyncio.AsyncSession.binds are now always present and are
  derived from the underlying _asyncio.AsyncSession.sync_session,
  translated back into their asyncio equivalents. Added
  _asyncio.AsyncSession.get_async_bind(),
  _asyncio.AsyncSession.bind_mapper() and
  _asyncio.AsyncSession.bind_table() as the asyncio-facing
  counterparts to the _orm.Session methods of the same name.
  Both attributes, along with
  _asyncio.AsyncSession.get_async_bind(), are proxied by
  _asyncio.async_scoped_session.

  References: #13563

sqlite

• [sqlite] [usecase] [reflection] Implemented _engine.Inspector.get_table_options() for the SQLite
  dialect, which previously raised NotImplementedError. The method
  returns the sqlite_with_rowid and sqlite_strict dialect options
  for a table that was created using the WITHOUT ROWID and / or
  STRICT keywords. As a result, these keywords are now also present in
  _schema.Table.kwargs for a _schema.Table that is
  reflected using autoload, so that a table which is recreated from its
  reflected form, such as by Alembic's batch migration mode, renders the
  keywords again rather than silently dropping them.

  This change is also backported to: 2.0.53

  References: #13543

• [sqlite] [bug] [reflection] Reworked the regular expression that parses FOREIGN KEY constraints
  during SQLite CREATE TABLE reflection so that the referred column list
  is matched unambiguously avoiding potential exponential backtracking.
  Pull request courtesy of Javid Khan.

  This change is also backported to: 2.0.53

  References: #13530

2.1.0rc1

Released: August 31, 2026

platform

• [platform] [change] Python 3.11 or above is now required; support for Python 3.10 is dropped,
  in addition to the drop of versions Python 3.9, 3.8 and 3.7 introduced
  in 2.1.0b1. Python 3.10 reaches EOL in October of 2026, so dropping
  support now gives the SQLAlchemy 2.1 series an extra year of space to
  remain on current Python versions.

• [platform] [bug] Python 3.15 support has been added and tested, including minimal changes
  for full compatibility.

  This change is also backported to: 2.0.52

  References: #13477

orm

• [orm] [usecase] Improved the error message raised when a Session is used
  inside a context manager after the transaction has been rolled back due
  to an exception. The InvalidRequestError now includes the original
  exception that triggered the rollback, making it clearer why the
  transaction is no longer active. Pull request courtesy Ilan Keshet.

  References: #11297

• [orm] [usecase] Improved error messages raised when ORM loader strategy options cannot be
  applied to a query. Messages now render the offending option in a
  user-friendly form such as joinedload(User.orders) rather than exposing
  internal class and path representations, and the "does not apply to root
  entities" message now includes the option that triggered the error. The
  same user-friendly rendering is also applied to the "conflicting loader
  strategy" message and to the of_type() representation in "does not
  link" messages. Originating pull request courtesy Jan Vollmer.

  References: #12398

• [orm] [usecase] Python source generated at runtime is now compiled against a descriptive
  filename which is registered with the linecache module, so that
  generated functions appearing on a stack trace render with their source
  rather than as an opaque File "<string>" frame. This allows tools
  like pdb and inspect.getsource() to work with these generated
  source blocks as well. The new feature is applied to the instrumentation
  applied to an ORM object's __init__ method, as well as throughout
  SQLAlchemy functions that are internally instrumented.

  References: #13505

• [orm] [usecase] When a subclass overrides a _orm.validates() method using the
  same method name as the parent class, only the subclass validator is
  now invoked for instances of the subclass. The subclass validator
  may call super() to also invoke the parent class validator.
  Previously, the parent validator was always used regardless of
  whether the subclass provided an override. Pull request courtesy
  Indivar Mishra.

  References: #2943

• [orm] [bug] Fixed a result-column misalignment bug in ORM-enabled UPDATE statements
  where synchronize_session="fetch" is in use, either explicitly or
  because the statement uses constructs such as CTEs that implicitly select
  for it. Columns in rows returned by .returning() could be returned
  under incorrect keys (e.g. row[SomeClass.a] returning the value of
  a different column), a problem most likely to manifest under concurrent
  workloads. ORM DELETE statements were not affected.

  This change is also backported to: 2.0.52

  References: #13439

• [orm] [bug] Fixed bug where a failed _orm.Session.bulk_insert_mappings(),
  _orm.Session.bulk_update_mappings() or
  _orm.Session.bulk_save_objects() call could leave the
  _orm.Session permanently in a "flushing" state, such as when the
  transaction could not be begun because a previous flush had left it
  needing a rollback. Unlike _orm.Session.flush(), the bulk methods
  set the internal flushing flag and began the transaction outside of the
  try/finally block that resets it, so that neither
  _orm.Session.rollback() nor _orm.Session.close() would clear
  it, and every subsequent flush would raise InvalidRequestError: Session is already flushing. Pull request courtesy Hamody We.

  This change is also backported to: 2.0.52

  References: #13485

• [orm] [bug] Fixed issue where unpickling an ORM object that were loaded using loader
  options making use of wildcard tokens, such as _orm.load_only() or
  _orm.raiseload() with "*", would fail with KeyError or
  IndexError if the process doing the unpickling had not yet constructed
  a loader path making use of that same token. This would typically be
  observed when the object were unpickled in a separate process, such as
  with the spawn or forkserver multiprocessing start methods, the
  latter of which became the default on POSIX platforms as of Python 3.14.
  The internal collection of these tokens is now established up front, so
  that it is identical in every process.

  This change is also backported to: 2.0.52

  References: #13493

• [orm] [bug] Fixed issue where a string ending in "*" passed to a
  _orm.Load strategy method, such as
  Load(A).joinedload("bs.*"), would bypass the check which rejects
  string attribute names in loader options, silently producing a loader
  path that matched nothing. Such a string now raises
  ArgumentError with the same message given for any other
  string attribute name. The bare wildcard "*", as in
  Load(A).lazyload("*"), continues to be accepted.

  This change is also backported to: 2.0.52

  References: #13493

• [orm] [bug] Calling _orm.aliased() against a _sql.select() or
  _sql.union() / _sql.CompoundSelect construct, which
  previously failed with an obscure AttributeError regarding a missing
  .mapper attribute, now raises when using SQLAlchemy 2.1, and emits a
  deprecation warning under SQLAlchemy 2.0 as it coerces the construct into a
  subquery instead. This matches the behavior of other similar implicit
  SELECT-to-FROM coercions. Pull request courtesy Rens Groothuijsen.

  This change is also backported to: 2.0.52

  References: #6274

• [orm] [bug] [regression] Fixed regression caused by the dataclasses change in #12168 where
  passing _orm.relationship.default_factory as list to a
  relationship that used the _orm.WriteOnlyMapped or
  _orm.DynamicMapped annotation would raise an error at mapper
  configuration time, as these relationships have no collection_class.
  list is now accepted for these relationships, which behave the same
  as ordinary collections in this regard; the factory itself is never
  invoked, and a newly constructed object begins with an empty
  collection. Documentation is added at write_only_dataclasses
  illustrating the use of write only and dynamic relationships with ORM
  mapped dataclasses.

  References: #13227

• [orm] [bug] Fixed long-standing issue where an object that was loaded at more than one
  path within a single query, such as when a chain of _orm.joinedload()
  options leads back to an entity that was also loaded at the top level of
  the query, would retain the loader options of whichever path the query
  happened to see last, which varied with the loader strategy in use. The
  options an object retains are applied to all forms of :term:lazy loading
  for that object, so an otherwise identical set of options could behave
  differently depending on the loader strategy. The shallowest path is now
  favored, which is deterministic.

  Unknown interpreted text role "term".

  References: #13507

engine

• [engine] [bug] [asyncio] [pool] Fixed issue where a DBAPI connection would be left open and unreachable
  if an exception were raised within the _events.PoolEvents.connect()
  or _events.PoolEvents.first_connect() event handlers, which is
  where Dialect.initialize() runs. The connection had been created
  but not yet associated with anything that could close it, so it was
  neither returned to the pool nor closed. For an asyncio driver in
  particular this could leak a server-side session for the life of the
  process, as the garbage collector is not able to close a connection that
  requires the event loop.

  This change is also backported to: 2.0.53

  References: #13548

sql

• [sql] [usecase] Added new methods _sql.Exists.with_hint() and
  _sql.Exists.with_statement_hint(), which apply a table hint or a
  statement hint to the SELECT statement that's enclosed by the EXISTS
  expression, in the same way as _sql.Select.with_hint() and
  _sql.Select.with_statement_hint(). As ORM constructs such as
  _orm.PropComparator.any() and _orm.PropComparator.has()
  produce an _sql.Exists object, hints may now be applied to the
  subqueries which these constructs generate. Pull request courtesy
  Abhinav Gorrepati.

  References: #8311

• [sql] [performance] Improved the performance of SQL cache key generation by moving the
  traversal into the Cython extension modules. The set of attributes that
  participate in the cache key for a particular construct, along with the
  handler that applies to each one, is now resolved once at class setup
  time into a structure that the compiled traversal consumes directly, so
  that the chain of identity comparisons that formerly rediscovered this
  per attribute, per cache key is no longer run at all. Benchmarks against
  a range of Core and ORM statements show cache key generation running
  approximately 1.5 to 2.2 times faster in a build with the Cython
  extensions compiled, and approximately 1.05 to 1.2 times faster in a
  pure Python build, with the generated cache keys themselves unchanged.

  References: #13506

• [sql] [bug] Fixed an issue in Numeric where the
  Numeric.decimal_return_scale parameter was ignored when the
  DBAPI does not support native decimal objects (i.e.
  dialect.supports_native_decimal is False). In this path the result
  processor was computing the conversion scale from
  Numeric.scale directly, bypassing
  Numeric.decimal_return_scale entirely. The behavior now
  matches Float, which already used the correct
  _effective_decimal_return_scale property. Pull request courtesy Kadir
  Can Ozden.

  This change is also backported to: 2.0.52

  References: #13424

• [sql] [bug] Added auditing to the test suite which exercises the literal execute
  processors across all datatypes and dialects to ensure that string input is
  either appropriately rejected or correctly escaped. Literal execute
  processors are invoked when the bindparam.literal_execute
  parameter is used with an explicit bindparam() object, which
  overrides DBAPI-native bind handling to render the value inline with the
  statement instead. Datatypes that were updated include the originally
  reported SQL Server Uuid / UNIQUEIDENTIFIER rendering which now
  escapes properly, the JSONPATH type that's currently
  PostgreSQL-only, and a full family of numeric types stemming from the
  _types.Float and _types.Numeric bases which now coerce
  the value to a number, rejecting non-numeric input. Thanks to Javid Khan
  for helping to identify the issue.

  This change is also backported to: 2.0.52

  References: #13448

• [sql] [bug] Fixed issue where two bound parameters whose names differ only in the
  characters listed in
  SQLCompiler.bindname_escape_characters, such as those generated
  for columns named "a.b" and "a_b", would be rendered using the
  same name in the compiled statement, as those characters are escaped only
  as the parameter is rendered. The value for one of the two parameters was
  then silently used for both, affecting SELECT criteria as well as the
  VALUES and SET clauses of INSERT and UPDATE statements, where a value
  could be written to the wrong column. Escaped parameter names are now
  disambiguated against the names already in use. The .key of each
  BindParameter is unaffected, so parameter dictionaries passed
  by the caller continue to be keyed as before.

  References: #13534

• [sql] [bug] Fixed issue where an empty string passed to
  IdentifierPreparer.quote(), such as the name of a
  Table constructed with a blank name, would raise
  IndexError rather than being rendered. An empty identifier is now
  always quoted. While a blank name is not a legal identifier on most
  backends, SQLite accepts one, so such a table may be delivered by
  reflection; a table with a blank name can now be used in SELECT, INSERT,
  UPDATE, DELETE and DDL statements.

  References: #13535

schema

• [schema] [usecase] ForeignKeyConstraint now accepts a constraint which names the
  same local column more than once, such as FOREIGN KEY (a, a) REFERENCES r (b, c). This form is valid SQL and constrains the referenced row so
  that two of its columns are equal; it previously raised
  ArgumentError. Such a constraint now emits and reflects like any
  other composite foreign key; the workaround added in 2.0 for
  #13525, which skipped such a constraint during reflection, is
  removed as it is no longer needed. As part of this change, the check that
  the number of constrained columns matches the number of referenced columns
  no longer counts distinct column names, so that a genuine mismatch such as
  ForeignKeyConstraint(["x", "x"], ["r.b"]), which was formerly accepted
  and silently dropped a column, is now rejected.

  References: #13526

• [schema] [usecase] Added new _schema.ForeignKey accessors
  _schema.ForeignKey.target_tokens,
  _schema.ForeignKey.target_column and
  _schema.ForeignKey.target_table_key, as well as new
  ForeignKey-related datastructure _schema.ForeignKeyTarget.
  _schema.ForeignKeyTarget is now accepted as a constructor
  argument as well. See _schema.ForeignKey for new datamembers and
  usage patterns.

  References: #13538

• [schema] [usecase] Added Dialect.dbapi_version, a standardized accessor for the
  version of the DBAPI module in use by a dialect, in contrast to
  Dialect.server_version_info which refers to the database server.
  The implementation on DefaultDialect makes use of a new
  per-dialect method Dialect.retrieve_dbapi_version() in order to
  retrieve the version from the DBAPI module and return it as a
  VersionInfo object, which is a tuple subclass with additional
  properties; third-party dialects should also implement the
  Dialect.retrieve_dbapi_version() method.

• [schema] [performance] Created new reflection method _reflection.Inspector.has_multi_table()
  to check the existence of multiple tables at once, allowing for
  better performance when checking many tables. Like the other "multi"
  reflection methods, the default dialect offers a default implementation
  that just call the single method in a loop. Backends that wish to take
  advantage of this new method can implement it in their dialects.
  The PostgreSQL, Oracle and SQL Server dialects have been updated to use
  this new method.
  The implementation of _schema.MetaData.create_all() has been updated
  to make use of this new method to check the existence of the tables,
  reducing the number of round trips to the database when creating many tables.

  References: #13311

• [schema] [bug] Fixed an issue where _schema.Table.to_metadata() reused column
  default and on-update objects, causing the defaults on the original
  columns to refer to the copied columns. Default generators, including
  sequences, and server-side defaults are now copied and remain associated
  with their respective columns and metadata collections. Applications that
  inspected these objects will now see distinct defaults on the copied table
  instead of the objects owned by the original table. Pull request courtesy
  Goutam Adwant.

  This change is also backported to: 2.0.52

  References: #13481

• [schema] [bug] Fixed issue where a _schema.ForeignKey which refers to a table or
  column whose name contains a dot would be interpreted incorrectly, as the
  dotted string form of the target could not be told apart from the
  separator between a schema, table and column name. Foreign key targets
  are now tracked as their individual schema, table and column names
  throughout, and are no longer derived by splitting a dotted string.

  References: #13538

• [schema] [deprecated] _schema.ForeignKey.target_fullname is now a legacy accessor, and
  raises InvalidRequestError when the target has no unambiguous
  dotted string form, which is the case when the target table or column name
  contains a dot, or when a schema name is present with no column name. No
  SQLAlchemy internals make use of the attribute any longer; new code should
  use _schema.ForeignKey.target_tokens.

  References: #13538

postgresql

• [postgresql] [usecase] Added postgresql_with support to CreateView for specifying
  PostgreSQL view options such as security_invoker, security_barrier,
  and check_option, rendered as a WITH (...) clause between the view
  name and the AS keyword. Additionally, the postgresql_with parameter
  accepted by _schema.Table and _schema.Index now correctly
  renders Python boolean values as true/false (lowercase), and
  None values as the parameter name alone without an = value portion.
  Pull request courtesy alphavector.

  References: #11122, #13432

• [postgresql] [usecase] The PostgreSQL dialect now reflects the schema of a schema-qualified
  column or _postgresql.DOMAIN collation, populating the new
  String.collation_schema /
  _postgresql.DOMAIN.collation_schema parameters so that
  reflected DDL round-trips exactly. The schema is omitted from the
  reflected value when the collation is visible on the current
  search_path without qualification.

  References: #6511

• [postgresql] [usecase] Added a new parameter String.collation_schema, as well as
  _postgresql.DOMAIN.collation_schema and
  ColumnOperators.collate.collation_schema, allowing a
  PostgreSQL schema-qualified collation name to be specified explicitly,
  rather than embedding the schema name within the collation string
  itself, which previously rendered incorrectly. As part of this change,
  collation name rendering across DDL and the _sql.collate()
  construct now consistently uses the dialect's identifier preparer for
  quoting, rather than several separate, inconsistent hand-quoting code
  paths; as a side effect, simple lowercase collation names such as
  "utf8" are no longer unconditionally quoted in generated DDL.

  References: #9693

• [postgresql] [bug] Fixed bug in _reflection.Inspector.get_schema_names() for
  PostgreSQL where the query used to exclude system schemas relied on
  NOT LIKE 'pg_%', which treats the underscore as a SQL LIKE
  wildcard rather than a literal character. This caused user-created
  schemas that happen to start with "pg" followed by any other
  character, such as pgsql or pgstats, to be silently excluded
  along with actual system schemas like pg_catalog. Pull request
  courtesy Evan Rusackas.

  This change is also backported to: 2.0.53

  References: #13472

• [postgresql] [bug] [reflection] Fixed reflection of PostgreSQL CHECK constraints where an expression made
  up of multiple parenthesized sub-expressions, such as (x IS NULL OR y IS NULL) AND (x IS NULL OR y IS NULL), would have its leading and trailing
  parentheses incorrectly stripped, producing an unbalanced and
  syntactically invalid reflected expression. Pull request courtesy
  Shaurya Singh.

  This change is also backported to: 2.0.52

  References: #13157

• [postgresql] [bug] Fixed bug in the PostgreSQL dialect where a single quote in a sequence,
  table, or schema name, such as one supplied via a schema_translate_map
  or an explicit Sequence, could result in a malformed
  nextval() statement. The quote is now properly escaped. Pull request
  courtesy dxbjavid.

  This change is also backported to: 2.0.52

  References: #13429

• [postgresql] [bug] Fixed issue in the asyncpg dialect where the version of the asyncpg
  DBAPI would always be reported as (99, 99, 99), as the version was
  looked up on the dialect's DBAPI wrapper module rather than on the
  asyncpg module itself.

mysql

• [mysql] [bug] Ensure that CREATE TABLE DDL statements for MySQL and MariaDB dialects
  render the table options in a deterministic order. Previously the order
  could change depending on the Python seed.

  This change is also backported to: 2.0.53

  References: #13523

• [mysql] [bug] Fixed issue where the version of the DBAPI reported by the mysqldb and
  pymysql dialects was incorrect. Current mysqlclient releases publish
  MySQLdb.version_info and no version string at all, so no version was
  reported; pymysql publishes __version__ and version_info as
  mysqlclient compatibility values, so the version reported for pymysql
  was that of the mysqlclient release it emulates, e.g. (2, 2, 8)
  rather than (1, 2, 0).

sqlite

• [sqlite] [usecase] Added support for multiple ON CONFLICT clauses within a single
  statement for the SQLite _sqlite.insert() construct; the
  _sqlite.Insert.on_conflict_do_update() and
  _sqlite.Insert.on_conflict_do_nothing() methods may now each be
  invoked more than once against the same construct, where the clauses
  render in the order in which they were established and are evaluated by
  SQLite in that order. As SQLite allows only the last ON CONFLICT
  clause to omit its conflict target, a
  _sqlite.Insert.on_conflict_do_nothing() call that omits
  _sqlite.Insert.on_conflict_do_nothing.index_elements must be
  the last clause established. Documentation is added at
  sqlite_on_conflict_multiple. Pull request courtesy Diemid
  Berozkin.

  References: #13113

• [sqlite] [bug] [reflection] Fixed issue in SQLite reflection where the name of a PRIMARY KEY,
  UNIQUE or FOREIGN KEY constraint would be reflected as None if
  the CONSTRAINT <name> clause were separated from the keyword that
  follows it by a newline rather than by spaces. As SQLite stores the
  CREATE TABLE statement as it was originally typed, this affected
  tables created from hand-written DDL that spans multiple lines. The
  regular expressions used to recover constraint names, as well as the
  ON UPDATE / ON DELETE, DEFERRABLE and INITIALLY options of
  a foreign key constraint, now accept any whitespace between tokens.

  This change is also backported to: 2.0.53

  References: #13528

• [sqlite] [bug] Reworked the regular expression that detects inline UNIQUE column
  constraints during SQLite CREATE TABLE reflection so that the
  whitespace separating a column's type from a following clause is matched
  unambiguously. The previous pattern had three overlapping quantifiers
  that could each consume a space character, so a column definition
  carrying a long run of whitespace in the stored schema made
  _reflection.Inspector.get_unique_constraints() spend cubic time
  backtracking before returning. Fix courtesy of Javid Khan.

  This change is also backported to: 2.0.52

  References: #13419

• [sqlite] [bug] Added a warning for query string arguments that are passed to a SQLite
  URL without the uri=true argument also being present, and which are
  not accepted by the sqlite3 driver itself. SQLite URI arguments
  such as mode or cache take effect only when URI mode is in use;
  without it they were previously discarded silently, so that a URL such
  as sqlite:///file:mydb?mode=memory would connect to a file on disk
  named file:mydb. Arguments intended for the driver itself may be
  passed using the _sa.create_engine.connect_args parameter.

  References: #13433

• [sqlite] [bug] [documentation] Corrected the SQLite documentation regarding shared cache memory
  databases, which incorrectly indicated that the named form
  sqlite:///file:mydb?mode=memory&cache=shared&uri=true makes use of
  QueuePool; a single-connection pool is used for this form.
  Documentation has also been added noting that a shared cache database
  exists only for as long as at least one connection to it remains open,
  so that ordinary pool operations such as
  _engine.Engine.dispose() or use of
  _sa.create_engine.pool_recycle will discard its contents.

  References: #13433

• [sqlite] [deprecated] Deprecated the selection of a single-connection pool class, i.e.
  SingletonThreadPool for pysqlite or StaticPool for
  aiosqlite, based on the presence of the mode=memory query string
  argument in a SQLite URL. Pool selection for SQLite is intended to be
  based on the database name alone, where only :memory: or an empty
  database name indicate a memory database; interpreting the query string
  additionally requires that assumptions be made regarding whether or not
  the resulting database can be shared among multiple connections. In a
  future release, such URLs will make use of QueuePool or
  AsyncAdaptedQueuePool as would any other URL. This notably
  includes the shared cache form
  sqlite:///file:mydb?mode=memory&cache=shared&uri=true, for which a
  queue pool is in fact the appropriate class, as a shared cache database
  supports multiple concurrent connections, whereas a single-connection
  pool causes such connections to share one transaction state.
  Applications that rely upon the present behavior should indicate the
  intended pool using the _sa.create_engine.poolclass
  parameter. Pull request courtesy Itachi-0xAI.

  References: #13433

mssql

• [mssql] [bug] [reflection] Fixed issue in SQL Server reflection where TEXT and NTEXT columns
  would be reflected with a spurious length of 16 and 8, respectively. These
  are unlengthed LOB datatypes; the value originates from the
  sys.columns.max_length column, which reports the size of the in-row LOB
  pointer rather than a character length for these types. The reflected
  _mssql.TEXT and _mssql.NTEXT types now have a length
  of None, so that a reflected table emits valid DDL when re-created,
  which previously failed with "Cannot specify a column width on data type
  text". Pull request courtesy Sam Debruyn.

  This change is also backported to: 2.0.53

  References: #13451

• [mssql] [bug] Improved disconnect detection for the mssql+mssqlpython dialect.
  Connection-level failures such as a dropped or reset network connection
  are now recognized by consulting the driver_error attribute of the
  exception, in addition to the message-based checks that were already in
  place, so that the affected connection is invalidated and the pool
  "pre ping" feature is able to recycle it. Pull request courtesy Sam
  Debruyn.

  References: #13441

oracle

• [oracle] [bug] Updated the oracledb async dialect where the async cursor adapter invoked
  __enter__() rather than __aenter__() on the underlying cursor.
  While these are equivalent in oracledb itself, the correct async form is
  now used for correctness. As AsyncCursor.__aenter__() was added in
  oracledb 2.0.1, the minimum supported oracledb version is now 2.0.1,
  declared via the oracle-oracledb extra. Pull request courtesy AVRC26.

  References: #13420

• [oracle] [bug] Fixed issue in the Oracle dialects where a _types.JSON value would
  be returned as an undecoded string for any JSON expression that is not a
  JSON column, such as a bound parameter, as well as for textual constructs
  with positional columns, such as _expression.text() combined with
  _expression.TextClause.columns().

  References: #13479

tests

• [tests] [usecase] The version specifications used by testing exclusions such as
  testing.fails_if("+asyncmy<0.2.13") now support a driver name, in
  which case the comparison is against the version of the DBAPI rather
  than that of the database server. Previously this form raised
  AssertionError: DBAPI version specs not supported yet.

• [tests] [bug] Altered the dialect reflection test
  test_check_constraint_parenthesized_expressions() so that it does not
  convert the reflected constraint to lowercase, which interferes with some
  third party dialect's representation of reflected check constraints.

  This change is also backported to: 2.0.53

  References: #13521

misc

• [bug] [installation] Added the AUTHORS file to the set of license files included in the
  built wheel, where previously only the LICENSE file was present. As
  the text of LICENSE refers to AUTHORS for the list of copyright
  holders, the reference would not resolve for tools that inspect an
  installed distribution.

  This change is also backported to: 2.0.53

  References: #13518

• [misc] [bug] Allowed the inspection registry to replace an existing registration with a
  reloaded callable from the same module and name. This avoids an assertion
  failure for tooling that unloads and reloads SQLAlchemy modules while still
  rejecting conflicting registrations. Pull request courtersy w-Jessamine.

  References: #10748

2.1.0b3

Released: June 27, 2026

orm

• [orm] [feature] Added selectinload.chunksize parameter to selectinload() allowing users to configure the number of primary keys sent per IN clause when loading relationships. Pull request courtesy bekapono.

  References: #11450

• [orm] [usecase] The populate_existing execution option is now honored when passed in the Session.get.execution_options dict by the method Session.get() and analogous in other session kinds. The current Session.get.populate_existing parameter will takes precedence if specified, overriding the value of the execution options.

  References: #10610

• [orm] [usecase] Updated the attribute _orm.ORMExecuteState.user_defined_options to include options that were added to the statement before calling Select.with_only_columns() or _orm.Query.with_entities().

  References: #13309

• [orm] [usecase] [performance] Optimized _orm.selectinload() to skip the .unique() call on inner result sets when no nested _orm.joinedload() on a collection is present. The uniquing pass is only required when a joined eager load inflates rows due to a one-to-many or many-to-many JOIN; in the common case of a leaf selectin load, rows are already unique by construction and the per-row hashing overhead can be avoided. As a side effect, yield_per set in a do_orm_execute event for a _orm.selectinload() relationship load no longer raises InvalidRequestError when no nested collection joinedload is in effect, since .unique() is no longer called in that path. Pull request courtesy Oliver Parker.

  References: #13339

• [orm] [usecase] Session level _orm.Session.execution_options now take effect for Core level SQL emitted by unit of work operations, in addition to their existing use within ORM statement executions. This is to provide for Core options such as _engine.Connection.execution_options.schema_translate_map to be applicable to a Session overall.

  References: #13346

• [orm] [performance] ORM result row fetching now processes rows as plain tuples rather than constructing Row objects, as ORM loaders use position-based access and do not require the Row interface. Row construction is still used when engine-level debug logging is enabled so that individual rows can be logged. Benchmarks show a 3-16% improvement in ORM entity load times depending on query shape. Pull request courtesy Oliver Parker.

  References: #13363

• [orm] [performance] Improved performance of _orm.selectinload() and _orm.subqueryload() result handling:

  -   in selectinloader, the primary key columns used to correlate related
      rows are now selected directly rather than being wrapped in a
      `Bundle`, and are read from positional slices of each result
      row.  This removes the per-row `Row` construction that the
      `Bundle` introduced, including for the common single-column
      primary key case.
  
  -   removed use of `groupby()` + `lambda` against `Row` objects
      in subqueryloader; rows are converted to plain tuples and the result
      lists are built via `append()`.
  
  -   many-to-one selectinload reads foreign key values directly from the
      parent instance dictionary when present, falling back to attribute-level
      access only for expired or deferred attributes.
  

  Pull request courtesy Oliver Parker.

  References: #13363

• [orm] [performance] The selectinload() loader strategy now selects the omit_join optimization for many-to-many non-self-referential relationships, reducing the number of joins in the secondary SELECT by selecting from the secondary table directly rather than joining back to the parent entity. omit_join is enabled automatically when the join condition determines that the secondary table's foreign keys fully cover the parent's primary key. As always, omit_join can be disabled by setting relationship.omit_join to False. Pull request courtesy bekapono.

  References: #5987

• [orm] [bug] Fixed issue where the declarative class registry would not consider class-level MetaData objects set on abstract mixin classes when resolving string-based table references in relationship() configurations. The registry now uses the same metadata resolution logic as table creation, first checking for a class-specific metadata attribute before falling back to registry.metadata.

  References: #13291

• [orm] [bug] Fixed issue where the _engine.Result.unique() filter was not properly validated against the _engine.Result.yield_per() method when both were called as methods on the result object, such as result.unique().yield_per(N) or result.yield_per(N).unique(). The uniquing filter was previously only checked when yield_per was set via _engine.Connection.execution_options.yield_per. Since these two features are fundamentally incompatible for ORM results, an InvalidRequestError is now raised in all cases.

  References: #13293

• [orm] [bug] A warning is now emitted when a Declarative attribute name is named metadata or registry. Previously, no warning was emitted for registry, and using the name metadata would raise an InvalidRequestError. Since these names can be used for attributes that are mapped as backrefs or using imperative mappings, usage under Declarative has been relaxed for metadata but also warns for both names as they may have unintended interactions with the Declarative reserved names.

  References: #13333

• [orm] [bug] Fixed issue where the declarative class resolver would not consider the MetaData.schema default schema when resolving a string table name for the relationship.secondary parameter as well as within string-based relationship.primaryjoin and relationship.secondaryjoin expressions. The resolution now matches the behavior of ForeignKey, where an unqualified table name is implicitly resolved under the default schema. A deprecation warning is emitted when an unqualified name resolves to a :data:.BLANK_SCHEMA table in a MetaData that has a default schema set, as this implicit resolution will change in a future version.

  Unknown interpreted text role "data".

  References: #8068

engine

• [engine] [bug] Expanded try/except error handling to encompass the _events.ConnectionEvents.before_cursor_execute() and _events.ConnectionEvents.after_cursor_execute() event hooks, so that exceptions raised within these hooks, including BaseException subclasses such as asyncio.CancelledError, are properly handled via the error handling path used for DBAPI errors. This ensures proper connection invalidation and pool notification when exit-type exceptions are raised in event hooks. As part of this change, DBAPI errors raised from within these event hooks will now be wrapped as SQLAlchemy exceptions.

  References: #13381

• [engine] [reflection] Removed the legacy include_columns key from the dictionary returned by the index reflection methods of some dialects. This information is now part of the dialect_options dictionary under the key {dialect_name}_include, such as postgresql_include or mssql_include.

  References: #13350

sql

• [sql] [usecase] Added _sql.Delete.using(), allowing explicit FROM expressions such as joins to be rendered in backend-specific multiple-table DELETE forms including MySQL/MariaDB DELETE .. USING. Pull request courtesy cjc0013.

  References: #8130

• [sql] [bug] Fixed issue where negation of comparison expressions involving func.any(), func.all(), and func.some() SQL functions would incorrectly flip the comparison operator (e.g. = to !=) rather than wrapping the expression with NOT. These functions are now registered as collection aggregate functions that prevent operator flipping on negation, consistent with the behavior of the standalone _expression.any_() and _expression.all_() constructs.

  References: #13343

postgresql

• [postgresql] [usecase] Changed the default backslash escape value in the PostgreSQL dialect to False to align it with the default value of standard_conforming_strings=on. This change should not affect most users since the value is set at driver initialization on first connect.

  References: #13268

mysql

• [mysql] [bug] Improved the regular expression used to parse index COMMENT clauses in MySQL SHOW CREATE TABLE reflection to use an unambiguous single-quoted-string pattern; the previous pattern was theoretically subject to backtracking on malformed input, though such input is not producible by MySQL itself. Fix courtesy of Javid Khan.

  References: #13393

sqlite

• [sqlite] [feature] Added _sqlite.JSONB type for SQLite's binary JSON storage format, available as of SQLite version 3.45.0. Values are stored via the jsonb() SQL function and retrieved via json(), while the Python-side behavior remains identical to _sqlite.JSON. Pull request courtesy Shamil Abdulaev.

  References: #13260

mssql

• [mssql] [performance] [reflection] Implemented native multi-table reflection methods for the SQL Server dialect, providing MSDialect.get_multi_columns(), MSDialect.get_multi_pk_constraint(), MSDialect.get_multi_foreign_keys(), MSDialect.get_multi_indexes() and MSDialect.get_multi_table_comment(). Previously the SQL Server dialect relied on the default dialect default implementation which calls the per-table methods in a loop; the new implementations issue a single bulk query per object type against the sys.* catalog views, avoiding the per-table round trips. The single-table reflection methods are now thin wrappers over the multi-table ones, matching the pattern used by the PostgreSQL and Oracle dialects. Pull request courtesy Gaurav Sharma.

  References: #8430

2.1.0b2

Released: April 16, 2026

orm

• [orm] [usecase] The metadata, type_annotation_map, or registry can now be set up in a declarative base also via a mixin class, not only by directly setting them on the subclass like before. The declarative class setup now uses getattr() to look for these attributes, instead of relying only on the class __dict__.

  References: #13198

sql

• [sql] [usecase] Added new parameter _sql.over.exclude to _sql.over() and related methods, enabling SQL standard frame exclusion clauses EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS in window functions. Pull request courtesy of Varun Chawla.

  References: #11671

• [sql] [usecase] The ColumnCollection class hierarchy has been refactored to allow column names such as add, remove, update, extend, and clear to be used without conflicts. ColumnCollection is now an abstract base class, with mutation operations moved to WriteableColumnCollection and DedupeColumnCollection subclasses. The ReadOnlyColumnCollection exposed as attributes such as Table.c no longer includes mutation methods that raised NotImplementedError, allowing these common column names to be accessed naturally, e.g. table.c.add, table.c.remove, table.c.update, etc.

• [sql] [bug] A warning is emitted when using the standalone _sql.distinct() function in a _sql.select() columns list outside of an aggregate function; this function is not intended as a replacement for the use of Select.distinct(). Pull request courtesy bekapono.

  References: #11526

• [sql] [bug] Improved the ability for TypeDecorator to produce a correct repr() for "schema" types such as Enum and Boolean. This is mostly to support the Alembic autogenerate use case so that custom types render with relevant arguments present. Improved the architecture used by TypeEngine to produce repr() strings to be more modular for compound types like TypeDecorator.

  References: #13140

schema

• [schema] [usecase] Most _sql.FromClause subclasses are now generic on _schema.TypedColumns subclasses, that can be used to type their _sql.FromClause.c collection. This applied to _schema.Table, _sql.Join, _sql.Subquery, _sql.CTE and more.

  References: #13085

• [schema] [bug] Amended the repr() output for Enum so that the MetaData is not shown in the output, as this interferes with Alembic-autogenerated forms of this type which should be inheriting the MetaData of the parent table in the migration script.

  References: #10604

typing

• [typing] [bug] Fixed issue in new PEP 646 support for result sets where an issue in the mypy type checker prevented "scalar" methods including Connection.scalar(), Result.scalar(), _orm.Session.scalar(), as well as async versions of these methods from applying the correct type to the scalar result value, when the columns in the originating _sql.select() were typed as Any. Pull request courtesy Yurii Karabas.

  References: #13091

• [typing] [bug] Improved typing of _sqltypes.JSON as well as dialect specific variants like _postgresql.JSON to include generic capabilities, so that the types may be parameterized to indicate any specific type of contents expected, e.g. JSONB[list[str]]().

  References: #13131

mssql

• [mssql] [feature] Added support for the mssql-python driver, Microsoft's official Python driver for SQL Server.

  References: #12869

oracle

• [oracle] [feature] Added support for the _sqltypes.JSON datatype when using the Oracle database with the oracledb dialect. JSON values are serialized and deserialized using configurable strategies that accommodate Oracle's native JSON type available as of Oracle 21c. Pull request courtesy Abdallah Alhadad.

  References: #10375

2.1.0b1

Released: January 21, 2026

platform

• [platform] [feature] Free-threaded Python versions are now supported in wheels released on Pypi. This integrates with overall free-threaded support added as part of #12881 for the 2.0 and 2.1 series, which includes new test suites as well as a few improvements to race conditions observed under freethreading.

  References: #12881

• [platform] [change] The greenlet dependency used for asyncio support no longer installs by default. This dependency does not publish wheel files for every architecture and is not needed for applications that aren't using asyncio features. Use the sqlalchemy[asyncio] install target to include this dependency.

  References: #10197

• [platform] [change] Updated the setup manifest definition to use PEP 621-compliant pyproject.toml. Also updated the extra install dependency to comply with PEP-685. Thanks for the help of Matt Oberle and KOLANICH on this change.

• [platform] [change] Python 3.10 or above is now required; support for Python 3.9, 3.8 and 3.7 is dropped as these versions are EOL.

  References: #10357, #12029, #12819

orm

• [orm] [feature] The _orm.relationship.back_populates argument to _orm.relationship() may now be passed as a Python callable, which resolves to either the direct linked ORM attribute, or a string value as before. ORM attributes are also accepted directly by _orm.relationship.back_populates. This change allows type checkers and IDEs to confirm the argument for _orm.relationship.back_populates is valid. Thanks to Priyanshu Parikh for the help on suggesting and helping to implement this feature.

  References: #10050

• [orm] [feature] Added new hybrid method hybrid_property.bulk_dml() which works in a similar way as hybrid_property.update_expression() for bulk ORM operations. A user-defined class method can now populate a bulk insert mapping dictionary using the desired hybrid mechanics. New documentation is added showing how both of these methods can be used including in combination with the new _sql.from_dml_column() construct.

  References: #12496

• [orm] [feature] Added new parameter _orm.composite.return_none_on to _orm.composite(), which allows control over if and when this composite attribute should resolve to None when queried or retrieved from the object directly. By default, a composite object is always present on the attribute, including for a pending object which is a behavioral change since 2.0. When _orm.composite.return_none_on is specified, a callable is passed that returns True or False to indicate if the given arguments indicate the composite should be returned as None. This parameter may also be set automatically when ORM Annotated Declarative is used; if the annotation is given as Mapped[SomeClass|None], a _orm.composite.return_none_on rule is applied that will return None if all contained columns are themselves None.

  References: #12570

• [orm] [feature] Added support for per-session execution options that are merged into all queries executed within that session. The _orm.Session, _orm.sessionmaker, _orm.scoped_session, _ext.asyncio.AsyncSession, and _ext.asyncio.async_sessionmaker constructors now accept an _orm.Session.execution_options parameter that will be applied to all explicit query executions (e.g. using _orm.Session.execute(), _orm.Session.get(), _orm.Session.scalars()) for that session instance.

  References: #12659

• [orm] [feature] Session autoflush behavior has been simplified to unconditionally flush the session each time an execution takes place, regardless of whether an ORM statement or Core statement is being executed. This change eliminates the previous conditional logic that only flushed when ORM-related statements were detected, which had become difficult to define clearly with the unified v2 syntax that allows both Core and ORM execution patterns. The change provides more consistent and predictable session behavior across all types of SQL execution.

  References: #9809

• [orm] [feature] Added _orm.RegistryEvents event class that allows event listeners to be established on a _orm.registry object. The new class provides three events: _orm.RegistryEvents.resolve_type_annotation() which allows customization of type annotation resolution that can supplement or replace the use of the registry.type_annotation_map dictionary, including that it can be helpful with custom resolution for complex types such as those of PEP 695, as well as _orm.RegistryEvents.before_configured() and _orm.RegistryEvents.after_configured(), which are registry-local forms of the mapper-wide version of these hooks.

  References: #9832

• [orm] [usecase] The _orm.Session.flush.objects parameter is now deprecated.

  References: #10816

• [orm] [usecase] Added the utility method _orm.Session.merge_all() and _orm.Session.delete_all() that operate on a collection of instances.

  References: #11776

• [orm] [usecase] Added support for using _orm.with_expression() to populate a _orm.query_expression() attribute that is also configured as the polymorphic_on discriminator column. The ORM now detects when a query expression column is serving as the polymorphic discriminator and updates it to use the column provided via _orm.with_expression(), enabling polymorphic loading to work correctly in this scenario. This allows for patterns such as where the discriminator value is computed from a related table.

  References: #12631

• [orm] [usecase] Added default implementations of ColumnOperators.desc(), ColumnOperators.asc(), ColumnOperators.nulls_first(), ColumnOperators.nulls_last() to _orm.composite() attributes, by default applying the modifier to all contained columns. Can be overridden using a custom comparator.

  References: #12769

• [orm] [usecase] The _orm.aliased() object now emits warnings when an attribute is accessed on an aliased class that cannot be located in the target selectable, for those cases where the _orm.aliased() is against a different FROM clause than the regular mapped table (such as a subquery). This helps users identify cases where column names don't match between the aliased class and the underlying selectable. When _orm.aliased.adapt_on_names is True, the warning suggests checking the column name; when False, it suggests using the adapt_on_names parameter for name-based matching.

  References: #12838

• [orm] [usecase] Improvements to the use case of using Declarative Dataclass Mapping <orm_declarative_native_dataclasses> with intermediary classes that are unmapped. As was the existing behavior, classes can subclass _orm.MappedAsDataclass alone without a declarative base to act as mixins, or along with a declarative base as well as __abstract__ = True to define an abstract base. However, the improved behavior scans ORM attributes like _orm.mapped_column() in this case to create correct dataclasses.field() constructs based on their arguments, allowing for more natural ordering of fields without dataclass errors being thrown. Additionally, added a new _orm.unmapped_dataclass() decorator function, which may be used to create unmapped mixins in a mapped hierarchy that is using the _orm.mapped_dataclass() decorator to create mapped dataclasses.

  References: #12854

• [orm] [usecase] Added _orm.DictBundle as a subclass of _orm.Bundle that returns dict objects.

  References: #12960

• [orm] [change] A sweep through class and function names in the ORM renames many classes and functions that have no intent of public visibility to be underscored. This is to reduce ambiguity as to which APIs are intended to be targeted by third party applications and extensions. Third parties are encouraged to propose new public APIs in Discussions to the extent they are needed to replace those that have been clarified as private.

  References: #10497

• [orm] [change] The first_init ORM event has been removed. This event was non-functional throughout the 1.4 and 2.0 series and could not be invoked without raising an internal error, so it is not expected that there is any real-world use of this event hook.

  References: #10500

• [orm] [change] Removed legacy signatures dating back to 0.9 release from the _orm.SessionEvents.after_bulk_update() and _orm.SessionEvents.after_bulk_delete().

  References: #10721

• [orm] [changed] The "non primary" mapper feature, long deprecated in SQLAlchemy since version 1.3, has been removed. The sole use case for "non primary" mappers was that of using _orm.relationship() to link to a mapped class against an alternative selectable; this use case is now suited by the relationship_aliased_class feature.

  References: #12437

• [orm] [bug] The _orm.relationship.secondary parameter no longer uses Python eval() to evaluate the given string. This parameter when passed a string should resolve to a table name that's present in the local MetaData collection only, and never needs to be any kind of Python expression otherwise. To use a real deferred callable based on a name that may not be locally present yet, use a lambda instead.

  References: #10564

• [orm] [bug] Fixed issue where joined eager loading would fail to use the "nested" form of the query when GROUP BY or DISTINCT were present if the eager joins being added were many-to-ones, leading to additional columns in the columns clause which would then cause errors. The check for "nested" is tuned to be enabled for these queries even for many-to-one joined eager loaders, and the "only do nested if it's one to many" aspect is now localized to when the query only has LIMIT or OFFSET added.

  References: #11226

• [orm] [bug] Revised the set "binary" operators for the association proxy set() interface to correctly raise TypeError for invalid use of the |, &, ^, and - operators, as well as the in-place mutation versions of these methods, to match the behavior of standard Python set() as well as SQLAlchemy ORM's "instrumented" set implementation.

  References: #11349

• [orm] [bug] A significant behavioral change has been made to the behavior of the _orm.mapped_column.default and _orm.relationship.default parameters, as well as the _orm.relationship.default_factory parameter with collection-based relationships, when used with SQLAlchemy's orm_declarative_native_dataclasses feature introduced in 2.0, where the given value (assumed to be an immutable scalar value for _orm.mapped_column.default and a simple collection class for _orm.relationship.default_factory) is no longer passed to the @dataclass API as a real default, instead a token that leaves the value un-set in the object's __dict__ is used, in conjunction with a descriptor-level default. This prevents an un-set default value from overriding a default that was actually set elsewhere, such as in relationship / foreign key assignment patterns as well as in _orm.Session.merge() scenarios. See the full writeup in the migration_21_toplevel document which includes guidance on how to re-enable the 2.0 version of the behavior if needed.

  References: #12168

• [orm] [bug] The behavior of _orm.with_polymorphic() when used with a single inheritance mapping has been changed such that its behavior should match as closely as possible to that of an equivalent joined inheritance mapping. Specifically this means that the base class specified in the _orm.with_polymorphic() construct will be the basemost class that is loaded, as well as all descendant classes of that basemost class. The change includes that the descendant classes named will no longer be exclusively indicated in "WHERE polymorphic_col IN" criteria; instead, the whole hierarchy starting with the given basemost class will be loaded. If the query indicates that rows should only be instances of a specific subclass within the polymorphic hierarchy, an error is raised if an incompatible superclass is loaded in the result since it cannot be made to match the requested class; this behavior is the same as what joined inheritance has done for many years. The change also allows a single result set to include column-level results from multiple sibling classes at once which was not previously possible with single table inheritance.

  References: #12395

• [orm] [bug] Improved the behavior of standalone "operators" like _sql.desc(), _sql.asc(), _sql.all_(), etc. so that they consult the given expression object for an overriding method for that operator, even if the object is not itself a ClauseElement, such as if it's an ORM attribute. This allows custom comparators for things like _orm.composite() to provide custom implementations of methods like desc(), asc(), etc.

  References: #12769

• [orm] [bug] ORM entities can now be involved within the SQL expressions used within _orm.relationship.primaryjoin and _orm.relationship.secondaryjoin parameters without the ORM entity information being implicitly sanitized, allowing ORM-specific features such as single-inheritance criteria in subqueries to continue working even when used in this context. This is made possible by overall ORM simplifications that occurred as of the 2.0 series. The changes here also provide a performance boost (up to 20%) for certain query compilation scenarios.

  References: #12843

• [orm] [bug] The _events.SessionEvents.do_orm_execute() event now allows direct mutation or replacement of the ORMExecuteState.parameters dictionary or list, which will take effect when the the statement is executed. Previously, changes to this collection were not accommodated by the event hook. Pull request courtesy Shamil.

  References: #12921

• [orm] [bug] A change in the mechanics of how Python dataclasses are applied to classes that use MappedAsDataclass or registry.mapped_as_dataclass() to apply __annotations__ that are as identical as is possible to the original __annotations__ given, while also adding attributes that SQLAlchemy considers to be part of dataclass __annotations__, then restoring the previous annotations in exactly the same format as they were, using patterns that work with PEP 649 as closely as possible.

  References: #13021

• [orm] [bug] Removed the ORDER BY clause from queries generated by _orm.selectin_polymorphic() and the _orm.Mapper.polymorphic_load parameter set to "selectin". The ORDER BY clause appears to have been an unnecessary implementation artifact.

  References: #13060

• [orm] [bug] A significant change to the ORM mechanics involved with both orm.with_loader_criteria() as well as single table inheritance, to more aggressively locate WHERE criteria which should be augmented by either the custom criteria or single-table inheritance criteria; SELECT statements that do not include the entity within the columns clause or as an explicit FROM, but still reference the entity within the WHERE clause, are now covered, in particular this will allow subqueries using EXISTS (SELECT 1) such as those rendered by RelationshipProperty.Comparator.any() and RelationshipProperty.Comparator.has().

  References: #13070

• [orm] The _orm.noload() relationship loader option and related lazy='noload' setting is deprecated and will be removed in a future release. This option was originally intended for custom loader patterns that are no longer applicable in modern SQLAlchemy.

  References: #11045

• [orm] Ignore _orm.Session.join_transaction_mode in all cases when the bind provided to the _orm.Session is an _engine.Engine. Previously if an event that executed before the session logic, like _engine.ConnectionEvents.engine_connect(), left the connection with an active transaction, the _orm.Session.join_transaction_mode behavior took place, leading to a surprising behavior.

  References: #11163

engine

• [engine] [usecase] Added new execution option _engine.Connection.execution_options.driver_column_names. This option disables the "name normalize" step that takes place against the DBAPI cursor.description for uppercase-default backends like Oracle, and will cause the keys of a result set (e.g. named tuple names, dictionary keys in Row._mapping, etc.) to be exactly what was delivered in cursor.description. This is mostly useful for plain textual statements using _sql.text() or _engine.Connection.exec_driver_sql().

  References: #10789

• [engine] [change] An empty sequence passed to any execute() method now raised a deprecation warning, since such an executemany is invalid. Pull request courtesy of Carlos Sousa.

  References: #9647

• [engine] [change] The private method Connection._execute_compiled is removed. This method may have been used for some special purposes however the SQLCompiler object has lots of special state that should be set up for an execute call, which we don't support.

• [engine] [bug] Fixed issue in "insertmanyvalues" feature where an INSERT..RETURNING that also made use of a sentinel column to track results would fail to filter out the additional column when Result.unique() were used to uniquify the result set.

  References: #10802

• [engine] [bug] Adjusted URL parsing and stringification to apply url quoting to the "database" portion of the URL. This allows a URL where the "database" portion includes special characters such as question marks to be accommodated.

  References: #11234

• [engine] [bug] Fixed issue in the ConnectionEvents.after_cursor_execute() method where the SQL statement and parameter list for an "insertmanyvalues" operation sent to the event would not be the actual SQL / parameters just emitted on the cursor, instead being the non-batched form of the statement that's used as a template to generate the batched statements.

  References: #13018

sql

• [sql] [feature] Added the ability to create custom SQL constructs that can define new clauses within SELECT, INSERT, UPDATE, and DELETE statements without needing to modify the construction or compilation code of of Select, _sql.Insert, Update, or Delete directly. Support for testing these constructs, including caching support, is present along with an example test suite. The use case for these constructs is expected to be third party dialects for analytical SQL (so-called NewSQL) or other novel styles of database that introduce new clauses to these statements. A new example suite is included which illustrates the QUALIFY SQL construct used by several NewSQL databases which includes a cacheable implementation as well as a test suite.

  References: #12195

• [sql] [feature] [core] The Core operator system now includes the matmul operator, i.e. the @ operator in Python as an optional operator. In addition to the __matmul__ and __rmatmul__ operator support this change also adds the missing __rrshift__ and __rlshift__. Pull request courtesy Aramís Segovia.

  References: #12479

• [sql] [feature] Added new Core feature _sql.from_dml_column() that may be used in expressions inside of UpdateBase.values() for INSERT or UPDATE; this construct will copy whatever SQL expression is used for the given target column in the statement to be used with additional columns. The construct is mostly intended to be a helper with ORM hybrid_property within DML hooks.

  References: #12496

• [sql] [feature] Added support for Python 3.14+ template strings (t-strings) via the new _sql.tstring() construct. This feature makes use of Python 3.14 template strings as defined in PEP 750, allowing for ergonomic SQL statement construction by automatically interpolating Python values and SQLAlchemy expressions within template strings.

  References: #12548

• [sql] [usecase] Added new generalized aggregate function ordering to functions via the _functions.FunctionElement.aggregate_order_by() method, which receives an expression and generates the appropriate embedded "ORDER BY" or "WITHIN GROUP (ORDER BY)" phrase depending on backend database. This new function supersedes the use of the PostgreSQL _postgresql.aggregate_order_by() function, which remains present for backward compatibility. To complement the new parameter, the _functions.aggregate_strings.order_by which adds ORDER BY capability to the _functions.aggregate_strings dialect-agnostic function which works for all included backends. Thanks much to Reuven Starodubski with help on this patch.

  References: #12853

• [sql] [usecase] Changed the query style for ORM queries emitted by Session.get() as well as many-to-one lazy load queries to use the default labeling style, _sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY, which normally does not apply labels to columns in a SELECT statement. Previously, the older style _sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL that labels columns as <tablename>_<columname> was used for Session.get() to maintain compatibility with _orm.Query. The change allows the string representation of ORM queries to be less verbose in all cases outside of legacy _orm.Query use. Pull request courtesy Inada Naoki.

  References: #12932

• [sql] [usecase] Added method TableClause.insert_column() to complement TableClause.append_column(), which inserts the given column at a specific index. This can be helpful for prepending primary key columns to tables, etc.

  References: #7910

• [sql] [usecase] Added support for the pow operator (**), with a default SQL implementation of the POW() function. On Oracle Database, PostgreSQL and MSSQL it renders as POWER(). As part of this change, the operator routes through a new first class func member _functions.pow, which renders on Oracle Database, PostgreSQL and MSSQL as POWER().

  References: #8579

• [sql] [usecase] [orm] The _sql.Select.filter_by(), _sql.Update.filter_by() and _sql.Delete.filter_by() methods now search across all entities present in the statement, rather than limiting their search to only the last joined entity or the first FROM entity. This allows these methods to locate attributes unambiguously across multiple joined tables, resolving issues where changing the order of operations such as _sql.Select.with_only_columns() would cause the method to fail.

  If an attribute name exists in more than one FROM clause entity, an _exc.AmbiguousColumnError is now raised, indicating that _sql.Select.filter() (or _sql.Select.where()) should be used instead with explicit table-qualified column references.

  References: #8601

• [sql] [change] The .c and .columns attributes on the Select and TextualSelect constructs, which are not instances of FromClause, have been removed completely, in addition to the .select() method as well as other codepaths which would implicitly generate a subquery from a Select without the need to explicitly call the Select.subquery() method.

  In the case of .c and .columns, these attributes were never useful in practice and have caused a great deal of confusion, hence were deprecated back in version 1.4, and have emitted warnings since that version. Accessing the columns that are specific to a Select construct is done via the Select.selected_columns attribute, which was added in version 1.4 to suit the use case that users often expected .c to accomplish. In the larger sense, implicit production of subqueries works against SQLAlchemy's modern practice of making SQL structure as explicit as possible.

  Note that this is not related to the usual FromClause.c and FromClause.columns attributes, common to objects such as Table and Subquery, which are unaffected by this change.

  References: #10236

• [sql] [change] the Numeric and Float SQL types have been separated out so that Float no longer inherits from Numeric; instead, they both extend from a common mixin NumericCommon. This corrects for some architectural shortcomings where numeric and float types are typically separate, and establishes more consistency with Integer also being a distinct type. The change should not have any end-user implications except for code that may be using isinstance() to test for the Numeric datatype; third party dialects which rely upon specific implementation types for numeric and/or float may also require adjustment to maintain compatibility.

  References: #5252

• [sql] [change] Added new implementation for the Select.params() method and that of similar statements, via a new statement-only ExecutableStatement.params() method which works more efficiently and correctly than the previous implementations available from ClauseElement, by associating the given parameter dictionary with the statement overall rather than cloning the statement and rewriting its bound parameters. The _sql.ClauseElement.params() and _sql.ClauseElement.unique_params() methods, when called on an object that does not implement ExecutableStatement, will continue to work the old way of cloning the object, and will emit a deprecation warning. This issue both resolves the architectural / performance concerns of #7066 and also provides correct ORM compatibility for functions like _orm.aliased(), reported by #12915.

  References: #12915, #7066

• [sql] [bug] The Double type is now used when a Python float value is detected as a literal value to be sent as a bound parameter, rather than the Float type. Double has the same implementation as Float, but when rendered in a CAST, produces DOUBLE or DOUBLE PRECISION rather than FLOAT. The former better matches Python's float datatype which uses 8-byte double-precision storage. Third party dialects which don't support the Double type directly may need adjustment so that they render an appropriate keyword (e.g. FLOAT) when the Double datatype is encountered.

  References: #10300

• [sql] [bug] Fixed issue in name normalization (e.g. "uppercase" backends like Oracle) where using a TextualSelect would not properly maintain as uppercase column names that were quoted as uppercase, even though the TextualSelect includes a Column that explicitly holds this uppercase name.

  References: #10788

• [sql] [bug] Enhanced the caching structure of the _expression.over.rows and _expression.over.range so that different numerical values for the rows / range fields are cached on the same cache key, to the extent that the underlying SQL does not actually change (i.e. "unbounded", "current row", negative/positive status will still change the cache key). This prevents the use of many different numerical range/rows value for a query that is otherwise identical from filling up the SQL cache.

  Note that the semi-private compiler method _format_frame_clause() is removed by this fix, replaced with a new method visit_frame_clause(). Third party dialects which may have referred to this method will need to change the name and revise the approach to rendering the correct SQL for that dialect.

  References: #11515

• [sql] [bug] Updated the _sql.over() clause to allow non integer values in _sql.over.range_ clause. Previously, only integer values were allowed and any other values would lead to a failure. To specify a non-integer value, use the new _sql.FrameClause construct along with the new _sql.FrameClauseType enum to specify the frame boundaries. For example:

  from sqlalchemy import FrameClause, FrameClauseType

  select( func.sum(table.c.value).over( range_=FrameClause( 3.14, 2.71, FrameClauseType.PRECEDING, FrameClauseType.FOLLOWING, ) ) )References: #12596

• [sql] [bug] Added a new concept of "operator classes" to the SQL operators supported by SQLAlchemy, represented within the enum OperatorClass. The purpose of this structure is to provide an extra layer of validation when a particular kind of SQL operation is used with a particular datatype, to catch early the use of an operator that does not have any relevance to the datatype in use; a simple example is an integer or numeric column used with a "string match" operator.

  References: #12736

• [sql] [bug] Fixed an issue in _sql.Select.join_from() where the join condition between the left and right tables specified in the method call could be incorrectly determined based on an intermediate table already present in the FROM clause, rather than matching the foreign keys between the immediate left and right arguments. The join condition is now determined by matching primary keys between the two tables explicitly passed to _sql.Select.join_from(), ensuring consistent and predictable join behavior regardless of the order of join operations or other tables present in the query. The fix is applied to both the Core and ORM implementations of _sql.Select.join_from().

  References: #12931

• [sql] [bug] Fixed issue where anonymous label generation for CTE constructs could produce name collisions when Python's garbage collector reused memory addresses during complex query compilation. The anonymous name generation for CTE and other aliased constructs like Alias, Subquery and others now use os.urandom() to generate unique identifiers instead of relying on object id(), ensuring uniqueness even in cases of aggressive garbage collection and memory reuse.

  References: #12990

• [sql] Removed the automatic coercion of executable objects, such as _orm.Query, when passed into _orm.Session.execute(). This usage raised a deprecation warning since the 1.4 series.

  References: #12218

schema

• [schema] [feature] Added support for the SQL CREATE VIEW statement via the new CreateView DDL class. The new class allows creating database views from SELECT statements, with support for options such as TEMPORARY, IF NOT EXISTS, and MATERIALIZED where supported by the target database. Views defined with CreateView integrate with MetaData for automated DDL generation and provide a Table object for querying.

  References: #181

• [schema] [feature] Added support for the SQL CREATE TABLE ... AS SELECT construct via the new _schema.CreateTableAs DDL construct and the _sql.Select.into() method. The new construct allows creating a table directly from the results of a SELECT statement, with support for options such as TEMPORARY and IF NOT EXISTS where supported by the target database. Tables defined with _schema.CreateTableAs integrate with MetaData for automated DDL generation and provide a Table object for querying. Pull request courtesy Greg Jarzab.

  References: #4950

• [schema] [usecase] The the parameter _schema.DropConstraint.isolate_from_table was deprecated since it has no effect on the drop table behavior. Its default values was also changed to False.

  References: #13006

• [schema] [bug] The Float and Numeric types are no longer automatically considered as auto-incrementing columns when the _schema.Column.autoincrement parameter is left at its default of "auto" on a _schema.Column that is part of the primary key. When the parameter is set to True, a Numeric type will be accepted as an auto-incrementing datatype for primary key columns, but only if its scale is explicitly given as zero; otherwise, an error is raised. This is a change from 2.0 where all numeric types including floats were automatically considered as "autoincrement" for primary key columns.

  References: #11811

• [schema] Deprecate Oracle only parameters _schema.Sequence.order, _schema.Identity.order and _schema.Identity.on_null. They should be configured using the dialect kwargs oracle_order and oracle_on_null.

  References: #10247

typing

• [typing] [feature] The Row object now no longer makes use of an intermediary Tuple in order to represent its individual element types; instead, the individual element types are present directly, via new PEP 646 integration, now available in more recent versions of Mypy. Mypy 1.7 or greater is now required for statements, results and rows to be correctly typed. Pull request courtesy Yurii Karabas.

  References: #10635

• [typing] The default implementation of _sql.TypeEngine.python_type now returns object instead of NotImplementedError, since that's the base for all types in Python3. The python_type of _sql.JSON no longer returns dict, but instead fallbacks to the generic implementation.

  References: #10646

• [typing] [orm] Removed the deprecated mypy plugin. The plugin was non-functional with newer version of mypy and it's no longer needed with modern SQLAlchemy declarative style.

  References: #12293

• [typing] [orm] Deprecated the declarative_mixin decorator since it was used only by the now removed mypy plugin.

  References: #12346

asyncio

• [asyncio] [feature] The "emulated" exception hierarchies for the asyncio drivers such as asyncpg, aiomysql, aioodbc, etc. have been standardized on a common base EmulatedDBAPIException, which is now what's available from the StatementException.orig attribute on a SQLAlchemy DBAPIError object. Within EmulatedDBAPIException and the subclasses in its hierarchy, the original driver-level exception is also now available via the EmulatedDBAPIException.orig attribute, and is also available from DBAPIError directly using the DBAPIError.driver_exception attribute.

  References: #8047

• [asyncio] [change] Added an initialize step to the import of sqlalchemy.ext.asyncio so that greenlet will be imported only when the asyncio extension is first imported. Alternatively, the greenlet library is still imported lazily on first use to support use case that don't make direct use of the SQLAlchemy asyncio extension.

  References: #10296

• [asyncio] [change] Adapted all asyncio dialects, including aiosqlite, aiomysql, asyncmy, psycopg, asyncpg to use the generic asyncio connection adapter first added in #6521 for the aioodbc DBAPI, allowing these dialects to take advantage of a common framework.

  References: #10415

• [asyncio] [change] Removed the compatibility async_fallback mode for async dialects, since it's no longer used by SQLAlchemy tests. Also removed the internal function await_fallback() and renamed the internal function await_only() to await_(). No change is expected to user code.

• [asyncio] [bug] Refactored all asyncio dialects so that exceptions which occur on failed connection attempts are appropriately wrapped with SQLAlchemy exception objects, allowing for consistent error handling.

  References: #11956

postgresql

• [postgresql] [feature] Adds a new str subclass _postgresql.BitString representing PostgreSQL bitstrings in python, that includes functionality for converting to and from int and bytes, in addition to implementing utility methods and operators for dealing with bits.

  This new class is returned automatically by the postgresql.BIT type.

  References: #10556

• [postgresql] [feature] Support for storage parameters in CREATE TABLE using the WITH clause has been added. The postgresql_with dialect option of _schema.Table accepts a mapping of key/value options.

  References: #10909

• [postgresql] [feature] Added syntax extension _postgresql.distinct_on() to build DISTINCT ON clauses. The old api, that passed columns to _sql.Select.distinct(), is now deprecated.

  References: #12342

• [postgresql] [feature] Support for VIRTUAL computed columns on PostgreSQL 18 and later has been added. The default behavior when Computed.persisted is not specified has been changed to align with PostgreSQL 18's default of VIRTUAL. When Computed.persisted is not specified, no keyword is rendered on PostgreSQL 18 and later; on older versions a warning is emitted and STORED is used as the default. To explicitly request STORED behavior on all PostgreSQL versions, specify persisted=True.

  References: #12866

• [postgresql] [feature] [sql] Added support for monotonic server-side functions such as PostgreSQL 18's uuidv7() to work with the engine_insertmanyvalues feature. By passing monotonic=True to any Function, the function can be used as a sentinel for tracking row order in batched INSERT operations with RETURNING, allowing the ORM and Core to efficiently batch INSERT statements while maintaining deterministic row ordering.

  References: #13014

• [postgresql] [feature] Added additional emulated error classes for the subclasses of asyncpg.exception.IntegrityError including RestrictViolationError, NotNullViolationError, ForeignKeyViolationError, UniqueViolationError CheckViolationError, ExclusionViolationError. These exceptions are not directly thrown by SQLAlchemy's asyncio emulation, however are available from the newly added DBAPIError.driver_exception attribute when a IntegrityError is caught.

  References: #8047

• [postgresql] [usecase] Added new parameter Enum.create_type to the Core Enum class. This parameter is automatically passed to the corresponding _postgresql.ENUM native type during DDL operations, allowing control over whether the PostgreSQL ENUM type is implicitly created or dropped within DDL operations that are otherwise targeting tables only. This provides control over the _postgresql.ENUM.create_type behavior without requiring explicit creation of a _postgresql.ENUM object.

  References: #10604

• [postgresql] [usecase] The PostgreSQL dialect now support reflection of table options, including the storage parameters, table access method and table spaces. These options are automatically reflected when autoloading a table, and are also available via the _engine.Inspector.get_table_options() and _engine.Inspector.get_multi_table_optionsmethod() methods.

  References: #10909

• [postgresql] [usecase] Added support for PostgreSQL 14+ HSTORE subscripting syntax. When connected to PostgreSQL 14 or later, HSTORE columns now automatically use the native subscript notation hstore_col['key'] instead of the arrow operator hstore_col -> 'key' for both read and write operations. This provides better compatibility with PostgreSQL's native HSTORE subscripting feature while maintaining backward compatibility with older PostgreSQL versions.

  Indexes in existing PostgreSQL databases which were indexed on an HSTORE subscript expression would need to be updated in order to match the new SQL syntax.

  References: #12948

• [postgresql] [usecase] The default DBAPI driver for the PostgreSQL dialect has been changed to psycopg (psycopg version 3) instead of psycopg2. The psycopg2 driver remains fully supported and can be explicitly specified in the connection URL using postgresql+psycopg2://.

  The psycopg (version 3) driver includes improvements over psycopg2 including better performance when using C extensions and native support for async operations.

  References: #13010

• [postgresql] [change] The _types.ARRAY.Comparator.any() and _types.ARRAY.Comparator.all() methods for the _types.ARRAY type are now deprecated for removal; these two methods along with _postgresql.Any() and _postgresql.All() have been legacy for some time as they are superseded by the _sql.any_() and _sql.all_() functions, which feature more intuitive use.

  References: #10821

• [postgresql] [change] Named types such as _postgresql.ENUM and _postgresql.DOMAIN (as well as the dialect-agnostic _types.Enum version) are now more strongly associated with the _schema.MetaData at the top of the table hierarchy and are de-associated with any particular _schema.Table they may be a part of. This better represents how PostgreSQL named types exist independently of any particular table, and that they may be used across many tables simultaneously. The change impacts the behavior of the "default schema" for a named type, as well as the CREATE/DROP behavior in relationship to the MetaData and Table construct. The change also includes a new CheckFirst enumeration which allows fine grained control over "check" queries during DDL operations, as well as that the _types.SchemaType.inherit_schema parameter is deprecated and will emit a deprecation warning when used. See the migration notes for full details.

  References: #10594, #12690

• [postgresql] [bug] A CompileError is raised if attempting to create a PostgreSQL _postgresql.ENUM or _postgresql.DOMAIN datatype using a name that matches a known pg_catalog datatype name, and a default schema is not specified. These types must be explicit within a schema in order to be differentiated from the built-in pg_catalog type. The "public" or otherwise default schema is not chosen by default here since the type can only be reflected back using the explicit schema name as well (it is otherwise not visible due to the pg_catalog name). Pull request courtesy Kapil Dagur.

  References: #12761

mysql

• [mysql] [feature] Added new construct _mysql.limit() which can be applied to any _sql.update() or _sql.delete() to provide the LIMIT keyword to UPDATE and DELETE. This new construct supersedes the use of the "mysql_limit" dialect keyword argument.

• [mysql] [mariadb] [reflection] Updated the reflection logic for indexes in the MariaDB and MySQL dialect to avoid setting the undocumented type key in the _engine.ReflectedIndex dicts returned by _engine.Inspector.get_indexes method.

  References: #12240

mariadb

• [mariadb] [usecase] Modified the MariaDB dialect so that when using the _sqltypes.Uuid datatype with MariaDB >= 10.7, leaving the _sqltypes.Uuid.native_uuid parameter at its default of True, the native UUID datatype will be rendered in DDL and used for database communication, rather than CHAR(32) (the non-native UUID type) as was the case previously. This is a behavioral change since 2.0, where the generic _sqltypes.Uuid datatype delivered CHAR(32) for all MySQL and MariaDB variants. Support for all major DBAPIs is implemented including support for less common "insertmanyvalues" scenarios where UUID values are generated in different ways for primary keys. Thanks much to Volodymyr Kochetkov for delivering the PR.

  References: #10339

• [mariadb] [bug] Fixes to the MySQL/MariaDB dialect so that mariadb-specific features such as the mariadb.INET4 and mariadb.INET6 datatype may be used with an Engine that uses a mysql:// URL, if the backend database is actually a mariadb database. Previously, support for MariaDB features when mysql:// URLs were used instead of mariadb:// URLs was ad-hoc; with this issue resolution, the full set of schema / compiler / type features are now available regardless of how the URL was presented.

  References: #13076

sqlite

• [sqlite] [bug] Improved the behavior of JSON accessors JSON.Comparator.as_string(), JSON.Comparator.as_boolean(), JSON.Comparator.as_float(), JSON.Comparator.as_integer() to use CAST in a similar way that the PostgreSQL, MySQL and SQL Server dialects do to help enforce the expected Python type is returned.

  References: #11074

mssql

• [mssql] [bug] The JSON.Comparator.as_boolean() method when used on a JSON value on SQL Server will now force a cast to occur for values that are not simple true/false JSON literals, forcing SQL Server to attempt to interpret the given value as a 1/0 BIT, or raise an error if not possible. Previously the expression would return NULL.

  References: #11074

• [mssql] [bug] Fix mssql+pyodbc issue where valid plus signs in an already-unquoted odbc_connect= (raw DBAPI) connection string are replaced with spaces.

  The pyodbc connector would unconditionally pass the odbc_connect value to unquote_plus(), even if it was not required. So, if the (unquoted) odbc_connect value contained PWD=pass+word that would get changed to PWD=pass word, and the login would fail. One workaround was to quote just the plus sign — PWD=pass%2Bword — which would then get unquoted to PWD=pass+word.

  References: #11250

oracle

• [oracle] [feature] Added support for native BOOLEAN support in Oracle Database 23c and above. The Oracle dialect now renders BOOLEAN automatically when Boolean is used in DDL, and also now supports direct use of the BOOLEAN datatype, when 23c and above is in use. For Oracle versions prior to 23c, boolean values continue to be emulated using SMALLINT as before. Special case handling is also present to ensure a SMALLINT that's interpreted with the Boolean datatype on Oracle Database 23c and above continues to return bool values. Pull request courtesy Yeongbae Jeon.

  References: #11633

• [oracle] [usecase] The default DBAPI driver for the Oracle Database dialect has been changed to oracledb instead of cx_oracle. The cx_oracle driver remains fully supported and can be explicitly specified in the connection URL using oracle+cx_oracle://.

  The oracledb driver is a modernized version of cx_oracle with better performance characteristics and ongoing active development from Oracle.

  References: #13010

tests

• [tests] [change] The top-level test runner has been changed to use nox, adding a noxfile.py as well as some included modules. The tox.ini file remains in place so that tox runs will continue to function in the near term, however it will be eventually removed and improvements and maintenance going forward will be only towards noxfile.py.

misc

• [misc] [changed] Removed multiple api that were deprecated in the 1.3 series and earlier. The list of removed features includes:

  -   The `force` parameter of `IdentifierPreparer.quote` and
      `IdentifierPreparer.quote_schema`;
  
  -   The `threaded` parameter of the cx-Oracle dialect;
  
  -   The `_json_serializer` and `_json_deserializer` parameters of the
      SQLite dialect;
  
  -   The `collection.converter` decorator;
  
  -   The `Mapper.mapped_table` property;
  
  -   The `Session.close_all` method;
  
  -   Support for multiple arguments in `_orm.defer()` and
      `_orm.undefer()`.
  

  References: #12441

2.0.54

Released: September 15, 2026

platform

• [platform] [change] Binary wheels are no longer built for Python 3.7. PyPI now rejects wheel
  files whose filename does not begin with the normalized project name, and
  the packaging tools that can be installed on Python 3.7 do not produce
  such a filename. As a result, SQLAlchemy 2.0.44 was the last release to
  publish Python 3.7 wheels to PyPI, and releases 2.0.45 and later have
  been available on Python 3.7 only as a source distribution; the wheel
  builds for Python 3.7 are now removed. Python 3.7 remains supported by
  the 2.0 series.

• [platform] [bug] Fixed issue where the Cython extensions were compiled without the
  freethreading_compatible directive, so that they did not declare
  themselves as safe to run without the GIL. On a free-threaded Python
  interpreter such as Python 3.13t or 3.14t, importing SQLAlchemy would
  cause the interpreter to re-enable the GIL, emitting a
  RuntimeWarning. The directive is now set when building for Python
  3.13 and above, and a test has been added which confirms that importing
  SQLAlchemy on a free-threaded build does not enable the GIL.

  References: #13592

2.0.53

Released: September 14, 2026

orm

• [orm] [bug] Fixed issue where an expression passed to _orm.with_expression()
  that embedded a _sql.select(), such as a correlated
  _sql.exists(), would fail to populate the attribute correctly on
  the second and subsequent executions of an otherwise identical
  statement, when the _orm.query_expression() attribute was loaded
  by a relationship loader that emits a second query, i.e.
  _orm.selectinload(), _orm.lazyload() or
  _orm.immediateload().

  References: #13560

• [orm] [bug] Fixed memory issue where mapped classes, along with their
  Table and _orm.Mapper objects, would not be garbage
  collected after the _orm.registry in which they were mapped had
  been disposed and dereferenced. The issue would occur for mappings that
  made use of _orm.relationship() together with constructs such as an
  Index established against an ORM-annotated expression.

  References: #13566

• [orm] [bug] Fixed issue where pickling an ORM object that had an instance level lazy
  loader established, such as when the _orm.raiseload() option is used,
  would emit a spurious warning regarding the loader containing additional
  criteria, if the object had itself been unpickled from a previous
  serialization. This would occur for objects that cross more than one
  serialization boundary, such as when using multiprocessing.

  References: #13574

• [orm] [bug] Fixed issue where calling _orm.aliased() against an existing
  _orm.aliased() construct, without passing an explicit selectable,
  would disregard the selectable of the existing construct and produce an
  alias of the mapped table instead, if that selectable were anything other
  than a table or a plain subquery, leading to incorrect results and/or
  non-working queries.

  This includes _orm.aliased() against a _orm.with_polymorphic()
  construct, which would previously produce an alias of the base mapped
  class only, discarding the polymorphic selectable and additional mappers.
  The new construct now retains these, so that criteria against subclass
  attributes and the innerjoin and selectable parameters of
  _orm.with_polymorphic() take effect, and subclass columns are loaded
  up front. The SQL rendered for these constructs now includes the
  polymorphic selectable.

  References: #13583, #13584

• [orm] [bug] Fixed issue where using _orm.aliased() with both the
  _orm.aliased.name and _orm.aliased.flat
  parameters, against an entity that is mapped to a join which includes
  anonymously named aliases, would embed the anonymous name symbol within
  the names generated for each element of the join, producing unusable
  SQL. An anonymously named element of the join is now aliased
  anonymously.

  References: #13583

engine

• [engine] [bug] [asyncio] [pool] Fixed issue where a DBAPI connection would be left open and unreachable
  if an exception were raised within the _events.PoolEvents.connect()
  or _events.PoolEvents.first_connect() event handlers, which is
  where Dialect.initialize() runs. The connection had been created
  but not yet associated with anything that could close it, so it was
  neither returned to the pool nor closed. For an asyncio driver in
  particular this could leak a server-side session for the life of the
  process, as the garbage collector is not able to close a connection that
  requires the event loop.

  References: #13548

sql

• [sql] [bug] Fixed issue where calling _sql.CTE.alias() on a _sql.CTE
  that was itself produced by _sql.CTE.alias() would render SQL that
  referred to the name of the intermediate alias, which is not present in
  the WITH clause, rather than to the name of the original CTE, producing
  invalid SQL.

  References: #13583

schema

• [schema] [bug] Fixed issue where reflecting a table with a foreign key constraint that
  names the same source column more than once, such as FOREIGN KEY (a, a) REFERENCES r (b, c) which is accepted by backends such as PostgreSQL,
  would raise ArgumentError and fail the reflection of the entire
  table. As ForeignKeyConstraint has no representation for this
  form, the constraint is now skipped with a warning, in the same way as one
  that names columns which are not present in the table, so that the
  remainder of the table is still reflected.

  Note that in the SQLAlchemy 2.1 series, full support for reflecting
  and constructing foreign key constraints with duplicated source columns
  has been added, with no warnings or skips.

  References: #13525

postgresql

• [postgresql] [bug] Fixed bug in _reflection.Inspector.get_schema_names() for
  PostgreSQL where the query used to exclude system schemas relied on
  NOT LIKE 'pg_%', which treats the underscore as a SQL LIKE
  wildcard rather than a literal character. This caused user-created
  schemas that happen to start with "pg" followed by any other
  character, such as pgsql or pgstats, to be silently excluded
  along with actual system schemas like pg_catalog. Pull request
  courtesy Evan Rusackas.

  References: #13472

mysql

• [mysql] [bug] Ensure that CREATE TABLE DDL statements for MySQL and MariaDB dialects
  render the table options in a deterministic order. Previously the order
  could change depending on the Python seed.

  References: #13523

sqlite

• [sqlite] [usecase] [reflection] Implemented _engine.Inspector.get_table_options() for the SQLite
  dialect, which previously raised NotImplementedError. The method
  returns the sqlite_with_rowid and sqlite_strict dialect options
  for a table that was created using the WITHOUT ROWID and / or
  STRICT keywords. As a result, these keywords are now also present in
  _schema.Table.kwargs for a _schema.Table that is
  reflected using autoload, so that a table which is recreated from its
  reflected form, such as by Alembic's batch migration mode, renders the
  keywords again rather than silently dropping them.

  References: #13543

• [sqlite] [bug] [reflection] Fixed issue in SQLite reflection where the name of a PRIMARY KEY,
  UNIQUE or FOREIGN KEY constraint would be reflected as None if
  the CONSTRAINT <name> clause were separated from the keyword that
  follows it by a newline rather than by spaces. As SQLite stores the
  CREATE TABLE statement as it was originally typed, this affected
  tables created from hand-written DDL that spans multiple lines. The
  regular expressions used to recover constraint names, as well as the
  ON UPDATE / ON DELETE, DEFERRABLE and INITIALLY options of
  a foreign key constraint, now accept any whitespace between tokens.

  References: #13528

• [sqlite] [bug] [reflection] Reworked the regular expression that parses FOREIGN KEY constraints
  during SQLite CREATE TABLE reflection so that the referred column list
  is matched unambiguously avoiding potential exponential backtracking.
  Pull request courtesy of Javid Khan.

  References: #13530

mssql

• [mssql] [bug] [reflection] Fixed issue in SQL Server reflection where TEXT and NTEXT columns
  would be reflected with a spurious length of 16 and 8, respectively. These
  are unlengthed LOB datatypes; the value originates from the
  sys.columns.max_length column, which reports the size of the in-row LOB
  pointer rather than a character length for these types. The reflected
  _mssql.TEXT and _mssql.NTEXT types now have a length
  of None, so that a reflected table emits valid DDL when re-created,
  which previously failed with "Cannot specify a column width on data type
  text". Pull request courtesy Sam Debruyn.

  References: #13451

oracle

• [oracle] [usecase] Added support for oracledb's new terminate() feature, which allows for
  clean termination of an Oracle database connection in an asyncio context
  where the connection's state has fallen out of the event loop, and needs to
  be garbage collected. The feature is enabled automatically when using
  oracledb 26.0.0 or greater.

  References: #13578

tests

• [tests] [bug] Altered the dialect reflection test
  test_check_constraint_parenthesized_expressions() so that it does not
  convert the reflected constraint to lowercase, which interferes with some
  third party dialect's representation of reflected check constraints.

  References: #13521

misc

• [bug] [installation] Added the AUTHORS file to the set of license files included in the
  built wheel, where previously only the LICENSE file was present. As
  the text of LICENSE refers to AUTHORS for the list of copyright
  holders, the reference would not resolve for tools that inspect an
  installed distribution.

  References: #13518

2.0.52

Released: August 11, 2026

platform

• [platform] [bug] Python 3.15 support has been added and tested, including minimal changes for full compatibility.

  References: #13477

orm

• [orm] [bug] Fixed a result-column misalignment bug in ORM-enabled UPDATE statements where synchronize_session="fetch" is in use, either explicitly or because the statement uses constructs such as CTEs that implicitly select for it. Columns in rows returned by .returning() could be returned under incorrect keys (e.g. row[SomeClass.a] returning the value of a different column), a problem most likely to manifest under concurrent workloads. ORM DELETE statements were not affected.

  References: #13439

• [orm] [bug] Fixed bug where a failed _orm.Session.bulk_insert_mappings(), _orm.Session.bulk_update_mappings() or _orm.Session.bulk_save_objects() call could leave the _orm.Session permanently in a "flushing" state, such as when the transaction could not be begun because a previous flush had left it needing a rollback. Unlike _orm.Session.flush(), the bulk methods set the internal flushing flag and began the transaction outside of the try/finally block that resets it, so that neither _orm.Session.rollback() nor _orm.Session.close() would clear it, and every subsequent flush would raise InvalidRequestError: Session is already flushing. Pull request courtesy Hamody We.

  References: #13485

• [orm] [bug] Fixed issue where unpickling an ORM object that were loaded using loader options making use of wildcard tokens, such as _orm.load_only() or _orm.raiseload() with "*", would fail with KeyError or IndexError if the process doing the unpickling had not yet constructed a loader path making use of that same token. This would typically be observed when the object were unpickled in a separate process, such as with the spawn or forkserver multiprocessing start methods, the latter of which became the default on POSIX platforms as of Python 3.14. The internal collection of these tokens is now established up front, so that it is identical in every process.

  References: #13493

• [orm] [bug] Fixed issue where a string ending in "*" passed to a _orm.Load strategy method, such as Load(A).joinedload("bs.*"), would bypass the check which rejects string attribute names in loader options, silently producing a loader path that matched nothing. Such a string now raises ArgumentError with the same message given for any other string attribute name. The bare wildcard "*", as in Load(A).lazyload("*"), continues to be accepted.

  References: #13493

• [orm] [bug] Calling _orm.aliased() against a _sql.select() or _sql.union() / _sql.CompoundSelect construct, which previously failed with an obscure AttributeError regarding a missing .mapper attribute, now raises when using SQLAlchemy 2.1, and emits a deprecation warning under SQLAlchemy 2.0 as it coerces the construct into a subquery instead. This matches the behavior of other similar implicit SELECT-to-FROM coercions. Pull request courtesy Rens Groothuijsen.

  References: #6274

orm declarative

• [bug] [orm declarative] Fixed issue where using PEP 593 Annotated wrapping a PEP 695 type alias, such as Annotated[SomeTypeAlias, mapped_column()], would crash with AttributeError: __value__. The internal is_pep695() check incorrectly identified the Annotated type as a PEP 695 type alias due to a quirk in Annotated.__origin__ returning the first type argument rather than Annotated itself.

  References: #13386

sql

• [sql] [bug] Fixed issue where _sql.Select.get_final_froms() would emit a deprecation warning when the statement made use of the PostgreSQL-specific expression argument to _sql.Select.distinct(); the same spurious warning would be emitted when stringifying such a statement without explicitly using a PostgreSQL dialect. The fix ensures that this 1.4-era warning is suppressed under both 2.0 and 2.1.

  Note that under SQLAlchemy 2.1, passing an expression to _sql.Select.distinct() is deprecated overall, and is replaced by a new PostgreSQL-specific construct (see #12342).

  References: #13396

• [sql] [bug] Fixed an issue in Numeric where the Numeric.decimal_return_scale parameter was ignored when the DBAPI does not support native decimal objects (i.e. dialect.supports_native_decimal is False). In this path the result processor was computing the conversion scale from Numeric.scale directly, bypassing Numeric.decimal_return_scale entirely. The behavior now matches Float, which already used the correct _effective_decimal_return_scale property. Pull request courtesy Kadir Can Ozden.

  References: #13424

• [sql] [bug] Added auditing to the test suite which exercises the literal execute processors across all datatypes and dialects to ensure that string input is either appropriately rejected or correctly escaped. Literal execute processors are invoked when the bindparam.literal_execute parameter is used with an explicit bindparam() object, which overrides DBAPI-native bind handling to render the value inline with the statement instead. Datatypes that were updated include the originally reported SQL Server Uuid / UNIQUEIDENTIFIER rendering which now escapes properly, the JSONPATH type that's currently PostgreSQL-only, and a full family of numeric types stemming from the _types.Float and _types.Numeric bases which now coerce the value to a number, rejecting non-numeric input. Thanks to Javid Khan for helping to identify the issue.

  References: #13448

schema

• [schema] [bug] Fixed an issue where _schema.Table.to_metadata() reused column default and on-update objects, causing the defaults on the original columns to refer to the copied columns. Default generators, including sequences, and server-side defaults are now copied and remain associated with their respective columns and metadata collections. Applications that inspected these objects will now see distinct defaults on the copied table instead of the objects owned by the original table. Pull request courtesy Goutam Adwant.

  References: #13481

postgresql

• [postgresql] [bug] [reflection] Fixed reflection of PostgreSQL CHECK constraints where an expression made up of multiple parenthesized sub-expressions, such as (x IS NULL OR y IS NULL) AND (x IS NULL OR y IS NULL), would have its leading and trailing parentheses incorrectly stripped, producing an unbalanced and syntactically invalid reflected expression. Pull request courtesy Shaurya Singh.

  References: #13157

• [postgresql] [bug] Fixed bug in the PostgreSQL dialect where a single quote in a sequence, table, or schema name, such as one supplied via a schema_translate_map or an explicit Sequence, could result in a malformed nextval() statement. The quote is now properly escaped. Pull request courtesy dxbjavid.

  References: #13429

sqlite

• [sqlite] [bug] Reworked the regular expression that detects inline UNIQUE column constraints during SQLite CREATE TABLE reflection so that the whitespace separating a column's type from a following clause is matched unambiguously. The previous pattern had three overlapping quantifiers that could each consume a space character, so a column definition carrying a long run of whitespace in the stored schema made _reflection.Inspector.get_unique_constraints() spend cubic time backtracking before returning. Fix courtesy of Javid Khan.

  References: #13419

mssql

• [mssql] [bug] Tightened the construction of the ODBC connection string in the pyodbc connector (as well as the mssql-python connector in 2.1) so that the driver name, the names of pass-through connection parameters, and values containing } are brace-quoted. Previously a } in the driver name or in a pass-through value, or a ; in the name of a pass-through parameter, could close the surrounding token early and allow the remainder of the string to be interpreted as additional connection attributes. Pull request courtesy dxbjavid.

  References: #13380

tests

• [tests] [bug] Fixed class-scoped pytest fixtures that were defined as instance methods using self, which is deprecated as of pytest 9.1 and will be removed in pytest 10. Fixtures are now decorated with a compatibility @classmethod decorator and use cls as the first parameter.

  References: #13392

2.0.51

Released: June 15, 2026

orm

• [orm] [bug] Fixed issue where _orm.subqueryload() combined with PropComparator.of_type() and PropComparator.and_() would silently drop the additional filter criteria, causing all related objects to be loaded instead of only those matching the filter. The LoaderCriteriaOption was being constructed against the base entity rather than the effective entity indicated by PropComparator.of_type(). Pull request courtesy Arya Rizky.

  References: #13207

• [orm] [bug] Fixed bug where a failure during tpc_prepare() within _orm.Session.commit() for a two-phase session would raise IllegalStateChangeError instead of the original database exception. The internal _prepare_impl() method's error handler was unable to invoke _orm.SessionTransaction.rollback() due to a state-change guard, preventing proper cleanup and masking the underlying error.

  References: #13356

engine

• [engine] [bug] Fixed issue where Result.freeze() would lose track of ambiguous column names present in the original CursorResult, causing key-based access on the thawed result to silently return a value instead of raising InvalidRequestError. The SimpleResultMetaData now accepts and propagates ambiguous key information so that frozen, thawed, and pickled results raise consistently for duplicate column names. Pull request courtesy Saurabh Kohli.

  References: #9427

sql

• [sql] [bug] Fixed issue where _sql.StatementLambdaElement would proxy attribute access through the cached "expected" expression rather than the resolved expression, causing stale closure-bound parameter values to be used when a lambda statement was extended with non-lambda criteria such as an additional .where() clause. Courtesy cjc0013.

  References: #10827

postgresql

• [postgresql] [bug] Repaired bug introduced in #13229 where a two-phase transaction recovery would not return the correct transaction identifier when generating the identifiers using the xid() method of the psycopg connection.

  References: #13355

• [postgresql] [bug] Fixed regular expression in the pure Python hstore result processor, used when use_native_hstore=False is set, which could hang on malformed hstore text containing unterminated quoted segments with backslashes. Pull request courtesy dxbjavid.

  References: #13370

2.0.50

Released: May 24, 2026

orm

• [orm] [bug] Fixed issue where using _orm.joinedload() with PropComparator.of_type() targeting a joined-table subclass combined with PropComparator.and_() referencing a column on that subclass would generate invalid SQL, where the subclass column was not adapted to the subquery alias. Pull request courtesy Joaquin Hui Gomez.

  References: #13203

• [orm] [bug] Fixed issue where the presence of a SessionEvents.do_orm_execute() event hook would cause internal execution options such as yield_per and loader-specific state from the first orm_pre_session_exec pass to leak into the second pass, leading to errors when using relationship loaders such as selectinload() and immediateload(). The execution options passed to the second compilation pass are now based on the original options plus only the explicit updates made via ORMExecuteState.update_execution_options() within the event hook.

  References: #13301

• [orm] [bug] Fixed issue where using _orm.with_polymorphic() on a leaf class (a subclass with no further descendants) or a non-inherited class would fail with an AttributeError when used in an ORM statement, due to _orm.configure_mappers() not being triggered implicitly. The fix ensures that AliasedInsp participates in the _post_inspect hook, triggering mapper configuration during ORM statement compilation.

  References: #13319

sql

• [sql] [bug] Fixed issue where floor division (//) between a Float or Numeric numerator and an Integer denominator would omit the FLOOR() SQL wrapper on dialects where Dialect.div_is_floordiv is True (the default, including PostgreSQL and SQLite). FLOOR() is now applied if either the denominator or the numerator is a non-integer, so that expressions such as float_col // int_col render as FLOOR(float_col / int_col) instead of the incorrect float_col / int_col. Pull request courtesy r266-tech.

  References: #10528

postgresql

• [postgresql] [bug] Improve handling of two phase transaction identifiers for PostgreSQL when the identifier is provided by the user. As part of this change the psycopg dialect was updated to use the DBAPI two phase transaction API instead of executing the SQL directly.

  References: #13229

• [postgresql] [bug] Fixed issue where the asyncpg driver could throw an insufficiently-handled exception InternalClientError under some circumstances, leading to connections not being properly marked as invalidated.

  References: #13241

• [postgresql] [bug] Fixed issue where the ExcludeConstraint construct did not correctly forward the ExcludeConstraint.info parameter to the superclass, causing user-defined metadata to be lost. Pull request courtesy Wiktor Byrka.

  References: #13317

mysql

• [mysql] [bug] [reflection] Narrowed the scope of the internal workaround for MySQL bugs #88718 and #96365 so that it is only applied where needed: MySQL 8.0.1 through 8.0.13 (where bug 88718 is present), and on systems with lower_case_table_names=2 (where bug 96365 applies, typically macOS). Previously the workaround was applied unconditionally for all MySQL 8.0+ versions, which caused a KeyError during foreign key reflection when the database user lacked SELECT privileges on referred tables.

  References: #13243

• [mysql] [bug] Fixed issue in aiomysql and asyncmy dialects that appears as of using pymysql 1.2.0; the dialects were not properly taking into account logic that detects the argument signature of pymysql's ping() method which was added as part of #10492.

  References: #13306

sqlite

• [sqlite] [bug] Escape key and pragma values when utilizing the pysqlcipher dialect.

  References: #13230

2.0.49

Released: April 3, 2026

orm

• [orm] [bug] Fixed issue where _orm.Session.get() would bypass the identity map and emit unnecessary SQL when with_for_update=False was passed, rather than treating it equivalently to the default of None. Pull request courtesy of Joshua Swanson.

  References: #13176

• [orm] [bug] Fixed issue where chained _orm.joinedload() options would not be applied correctly when the final relationship in the chain is declared on a base mapper and accessed through a subclass mapper in a _orm.with_polymorphic() query. The path registry now correctly computes the natural path when a property declared on a base class is accessed through a path containing a subclass mapper, ensuring the loader option can be located during query compilation.

  References: #13193

• [orm] [bug] [inheritance] Fixed issue where using _orm.Load.options() to apply a chained loader option such as _orm.joinedload() or _orm.selectinload() with _orm.PropComparator.of_type() for a polymorphic relationship would not generate the necessary clauses for the polymorphic subclasses. The polymorphic loading strategy is now correctly propagated when using a call such as joinedload(A.b).options(joinedload(B.c.of_type(poly))) to match the behavior of direct chaining e.g. joinedload(A.b).joinedload(B.c.of_type(poly)).

  References: #13202

• [orm] [bug] [inheritance] Fixed issue where using chained loader options such as _orm.selectinload() after _orm.joinedload() with _orm.PropComparator.of_type() for a polymorphic relationship would not properly apply the chained loader option. The loader option is now correctly applied when using a call such as joinedload(A.b.of_type(poly)).selectinload(poly.SubClass.c) to eagerly load related objects.

  References: #13209

typing

• [typing] [bug] Fixed a typing issue where the typed members of :data:.func would return the appropriate class of the same name, however this creates an issue for typecheckers such as Zuban and pyrefly that assume PEP 749 style typechecking even if the file states that it's a PEP 563 file; they see the returned name as indicating the method object and not the class object. These typecheckers are actually following along with an upcoming test harness that insists on PEP 749 style name resolution for this case unconditionally. Since PEP 749 is the way of the future regardless, differently-named type aliases have been added for these return types.

  Unknown interpreted text role "data".

  References: #13167

postgresql

• [postgresql] [bug] Fixed regular expression used when reflecting foreign keys in PostgreSQL to support escaped quotes in table names. Pull request courtesy of Austin Graham

  References: #10902

mssql

• [mssql] [usecase] Enhanced the aioodbc dialect to expose the fast_executemany attribute of the pyodbc cursor. This allows the fast_executemany parameter to work with the mssql+aioodbc dialect. Pull request courtesy Georg Sieber.

  References: #13152

• [mssql] [usecase] Remove warning for SQL Server dialect when a new version is detected. The warning was originally added more than 15 years ago due to an unexpected value returned when using an old version of FreeTDS. The assumption is that since then the issue has been resolved, so make the SQL Server dialect behave like the other ones that don't have an upper bound check on the version number.

  References: #13185

• [mssql] [bug] [reflection] Fixed regression from version 2.0.42 caused by #12654 where the updated column reflection query would receive SQL Server "type alias" names for special types such as sysname, whereas previously the base name would be received (e.g. nvarchar for sysname), leading to warnings that such types could not be reflected and resulting in NullType, rather than the expected NVARCHAR for a type like sysname. The column reflection query now joins sys.types a second time to look up the base type when the user type name is not present in MSDialect.ischema_names, and both names are checked in MSDialect.ischema_names for a match. Pull request courtesy Carlos Serrano.

  References: #13181, #13182

oracle

• [oracle] [bug] Fixed issue in Oracle dialect where the _oracle.RAW datatype would not reflect the length parameter. Pull request courtesy Daniel Sullivan.

  References: #13150

2.0.48

Released: March 2, 2026

engine

• [engine] [bug] Fixed a critical issue in Engine where connections created in conjunction with the DialectEvents.do_connect() event listeners would receive shared, mutable collections for the connection arguments, leading to a variety of potential issues including unlimited growth of the argument list as well as elements within the parameter dictionary being shared among concurrent connection calls. In particular this could impact do_connect routines making use of complex mutable authentication structures.

  References: #13144

2.0.47

Released: February 24, 2026

orm

• [orm] [bug] Fixed issue when using ORM mappings with Python 3.14's PEP 649 feature that no longer requires "future annotations", where the ORM's introspection of the __init__ method of mapped classes would fail if non-present identifiers in annotations were present. The vendored getfullargspec() method has been amended to use Format.FORWARDREF under Python 3.14 to prevent resolution of names that aren't present.

  References: #13104

engine

• [engine] [usecase] The connection object returned by _engine.Engine.raw_connection() now supports the context manager protocol, automatically returning the connection to the pool when exiting the context.

  References: #13116

postgresql

• [postgresql] [bug] Fixed an issue in the PostgreSQL dialect where foreign key constraint reflection would incorrectly swap or fail to capture onupdate and ondelete values when these clauses appeared in a different order than expected in the constraint definition. This issue primarily affected PostgreSQL-compatible databases such as CockroachDB, which may return ON DELETE before ON UPDATE in the constraint definition string. The reflection logic now correctly parses both clauses regardless of their ordering.

  References: #13105

• [postgresql] [bug] Fixed issue in the engine_insertmanyvalues feature where using PostgreSQL's ON CONFLICT clause with _dml.Insert.returning.sort_by_parameter_order enabled would generate invalid SQL when the insert used an implicit sentinel (server-side autoincrement primary key). The generated SQL would incorrectly declare a sentinel counter column in the imp_sen table alias without providing corresponding values in the VALUES clause, leading to a ProgrammingError indicating column count mismatch. The fix allows batch execution mode when embed_values_counter is active, as the embedded counter provides the ordering capability needed even with upsert behaviors, rather than unnecessarily downgrading to row-at-a-time execution.

  References: #13107

• [postgresql] [bug] Fixed issue where _postgresql.Insert.on_conflict_do_update() parameters were not respecting compilation options such as literal_binds=True. Pull request courtesy Loïc Simon.

  References: #13110

• [postgresql] [bug] Fixed issue where _postgresql.Insert.on_conflict_do_update() using parametrized bound parameters in the set_ clause would fail when used with executemany batching. For dialects that use the use_insertmanyvalues_wo_returning optimization (psycopg2), insertmanyvalues is now disabled when there is an ON CONFLICT clause. For cases with RETURNING, row-at-a-time mode is used when the SET clause contains parametrized bindparams (bindparams that receive values from the parameters dict), ensuring each row's parameters are correctly applied. ON CONFLICT statements using expressions like excluded.<column> continue to batch normally.

  References: #13130

mysql

• [mysql] [bug] Fixed issue where DDL compilation options were registered to the hard-coded dialect name mysql. This made it awkward for MySQL-derived dialects like MariaDB, StarRocks, etc. to work with such options when different sets of options exist for different platforms. Options are now registered under the actual dialect name, and a fallback was added to help avoid errors when an option does not exist for that dialect.

  To maintain backwards compatibility, when using the MariaDB dialect with the options mysql_with_parser or mysql_using without also specifying the corresponding mariadb_ prefixed options, a deprecation warning will be emitted. The mysql_ prefixed options will continue to work during the deprecation period. Users should update their code to additionally specify mariadb_with_parser and mariadb_using when using the mariadb:// dialect, or specify both options to support both dialects.

  Pull request courtesy Tiansu Yu.

  References: #13134

sqlite

• [sqlite] [bug] Fixed issue where _sqlite.Insert.on_conflict_do_update() parameters were not respecting compilation options such as literal_binds=True. Pull request courtesy Loïc Simon.

  References: #13110

• [sqlite] [bug] Fixed issue where _sqlite.Insert.on_conflict_do_update() using parametrized bound parameters in the set_ clause would fail when used with executemany batching. Row-at-a-time mode is now used for ON CONFLICT statements with RETURNING that contain parametrized bindparams, ensuring each row's parameters are correctly applied. ON CONFLICT statements using expressions like excluded.<column> continue to batch normally.

  References: #13130

2.0.46

Released: January 21, 2026

typing

• [typing] [bug] Fixed typing issues where ORM mapped classes and aliased entities could not be used as keys in result row mappings or as join targets in select statements. Patterns such as row._mapping[User], row._mapping[aliased(User)], row._mapping[with_polymorphic(...)] (rejected by both mypy and Pylance), and .join(aliased(User)) (rejected by Pylance) are documented and fully supported at runtime but were previously rejected by type checkers. The type definitions for _KeyType and _FromClauseArgument have been updated to accept these ORM entity types.

  References: #13075

postgresql

• [postgresql] [bug] Fixed issue where PostgreSQL JSONB operators _postgresql.JSONB.Comparator.path_match() and _postgresql.JSONB.Comparator.path_exists() were applying incorrect VARCHAR casts to the right-hand side operand when used with newer PostgreSQL drivers such as psycopg. The operators now indicate the right-hand type as JSONPATH, which currently results in no casting taking place, but is also compatible with explicit casts if the implementation were require it at a later point.

  References: #13059

• [postgresql] [bug] Fixed regression in PostgreSQL dialect where JSONB subscription syntax would generate incorrect SQL for cast() expressions returning JSONB, causing syntax errors. The dialect now properly wraps cast expressions in parentheses when using the [] subscription syntax, generating (CAST(...))[index] instead of CAST(...)[index] to comply with PostgreSQL syntax requirements. This extends the fix from #12778 which addressed the same issue for function calls.

  References: #13067

• [postgresql] [bug] Improved the foreign key reflection regular expression pattern used by the PostgreSQL dialect to be more permissive in matching identifier characters, allowing it to correctly handle unicode characters in table and column names. This change improves compatibility with PostgreSQL variants such as CockroachDB that may use different quoting patterns in combination with unicode characters in their identifiers. Pull request courtesy Gord Thompson.

mariadb

• [mariadb] [bug] Fixed the SQL compilation for the mariadb sequence "NOCYCLE" keyword that is to be emitted when the Sequence.cycle parameter is set to False on a Sequence. Pull request courtesy Diego Dupin.

  References: #13070

sqlite

• [sqlite] [bug] Fixed issue in the aiosqlite driver where SQLAlchemy's setting of aiosqlite's worker thread to "daemon" stopped working because the aiosqlite architecture moved the location of the worker thread in version 0.22.0. This "daemon" flag is necessary so that a program is able to exit if the SQLite connection itself was not explicitly closed, which is particularly likely with SQLAlchemy as it maintains SQLite connections in a connection pool. While it's perfectly fine to call AsyncEngine.dispose() before program exit, this is not historically or technically necessary for any driver of any known backend, since a primary feature of relational databases is durability. The change also implements support for "terminate" with aiosqlite when using version version 0.22.1 or greater, which implements a sync .stop() method.

  References: #13039

mssql

• [mssql] [usecase] Added support for the IF EXISTS clause when dropping indexes on SQL Server 2016 (13.x) and later versions. The DropIndex.if_exists parameter is now honored by the SQL Server dialect, allowing conditional index drops that will not raise an error if the index does not exist. Pull request courtesy Edgar Ramírez Mondragón.

  References: #13045

2.0.45

Released: December 9, 2025

orm

• [orm] [bug] Fixed issue where calling Mapper.add_property() within mapper event hooks such as MapperEvents.instrument_class(), MapperEvents.after_mapper_constructed(), or MapperEvents.before_mapper_configured() would raise an AttributeError because the mapper's internal property collections were not yet initialized. The Mapper.add_property() method now handles early-stage property additions correctly, allowing properties including column properties, deferred columns, and relationships to be added during mapper initialization events. Pull request courtesy G Allajmi.

  References: #12858

• [orm] [bug] Fixed issue in Python 3.14 where dataclass transformation would fail when a mapped class using MappedAsDataclass included a relationship() referencing a class that was not available at runtime (e.g., within a TYPE_CHECKING block). This occurred when using Python 3.14's PEP 649 deferred annotations feature, which is the default behavior without a from __future__ import annotations directive.

  References: #12952

examples

• [examples] [bug] Fixed the "short_selects" performance example where the cache was being used in all the examples, making it impossible to compare performance with and without the cache. Less important comparisons like "lambdas" and "baked queries" have been removed.

sql

• [sql] [bug] Some improvements to the _sql.ClauseElement.params() method to replace bound parameters in a query were made, however the ultimate issue in #12915 involving ORM _orm.aliased() cannot be fixed fully until 2.1, where the method is being rewritten to work without relying on Core cloned traversal.

  References: #12915

• [sql] [bug] Fixed issue where using the ColumnOperators.in_() operator with a nested CompoundSelect statement (e.g. an INTERSECT of UNION queries) would raise a NotImplementedError when the nested compound select was the first argument to the outer compound select. The _scalar_type() internal method now properly handles nested compound selects.

  References: #12987

typing

• [typing] [bug] Fixed typing issue where Select.with_for_update() would not support lists of ORM entities or other FROM clauses in the Select.with_for_update.of parameter. Pull request courtesy Shamil.

  References: #12730

• [typing] [bug] Fixed typing issue where coalesce would not return the correct return type when a nullable form of that argument were passed, even though this function is meant to select the non-null entry among possibly null arguments. Pull request courtesy Yannick PÉROUX.

postgresql

• [postgresql] [usecase] Added support for reflection of collation in types for PostgreSQL. The collation will be set only if different from the default one for the type. Pull request courtesy Denis Laxalde.

  References: #6511

• [postgresql] [bug] Fixed issue where PostgreSQL dialect options such as postgresql_include on PrimaryKeyConstraint and UniqueConstraint were rendered in the wrong position when combined with constraint deferrability options like deferrable=True. Pull request courtesy G Allajmi.

  References: #12867

• [postgresql] [bug] Fixed the structure of the SQL string used for the engine_insertmanyvalues feature when an explicit sequence with nextval() is used. The SQL function invocation for the sequence has been moved from being rendered inline within each tuple inside of VALUES to being rendered once in the SELECT that reads from VALUES. This change ensures the function is invoked in the correct order as rows are processed, rather than assuming PostgreSQL will execute inline function calls within VALUES in a particular order. While current PostgreSQL versions appear to handle the previous approach correctly, the database does not guarantee this behavior for future versions.

  References: #13015

mysql

• [mysql] [usecase] Added support for MySQL 8.0.1 + FOR SHARE to be emitted for the Select.with_for_update() method, which offers compatibility with NOWAIT and SKIP LOCKED. The new syntax is used only for MySQL when version 8.0.1 or higher is detected. Pull request courtesy JetDrag.

  References: #12964

sqlite

• [sqlite] [bug] [reflection] A series of improvements have been made for reflection of CHECK constraints on SQLite. The reflection logic now correctly handles table names containing the strings "CHECK" or "CONSTRAINT", properly supports all four SQLite identifier quoting styles (double quotes, single quotes, brackets, and backticks) for constraint names, and accurately parses CHECK constraint expressions containing parentheses within string literals using balanced parenthesis matching with string context tracking. Big thanks to GruzdevAV for new test cases and implementation ideas.

  References: #12924

• [sqlite] [bug] Fixed issue where SQLite dialect would fail to reflect constraint names that contained uppercase letters or other characters requiring quoting. The regular expressions used to parse primary key, foreign key, and unique constraint names from the CREATE TABLE statement have been updated to properly handle both quoted and unquoted constraint names.

  References: #12954

tests

• [tests] [change] A noxfile.py has been added to allow testing with nox. This is a direct port of 2.1's move to nox, however leaves the tox.ini file in place and retains all test documentation in terms of tox. Version 2.1 will move to nox fully, including deprecation warnings for tox and new testing documentation.

2.0.44

Released: October 10, 2025

platform

• [platform] [bug] Unblocked automatic greenlet installation for Python 3.14 now that there are greenlet wheels on pypi for python 3.14.

orm

• [orm] [usecase] The way ORM Annotated Declarative interprets Python PEP 695 type aliases in Mapped[] annotations has been refined to expand the lookup scheme. A PEP 695 type can now be resolved based on either its direct presence in _orm.registry.type_annotation_map or its immediate resolved value, as long as a recursive lookup across multiple PEP 695 types is not required for it to resolve. This change reverses part of the restrictions introduced in 2.0.37 as part of #11955, which deprecated (and disallowed in 2.1) the ability to resolve any PEP 695 type that was not explicitly present in _orm.registry.type_annotation_map. Recursive lookups of PEP 695 types remains deprecated in 2.0 and disallowed in version 2.1, as do implicit lookups of NewType types without an entry in _orm.registry.type_annotation_map.

  Additionally, new support has been added for generic PEP 695 aliases that refer to PEP 593 Annotated constructs containing _orm.mapped_column() configurations. See the sections below for examples.

  References: #12829

• [orm] [bug] Fixed a caching issue where _orm.with_loader_criteria() would incorrectly reuse cached bound parameter values when used with _sql.CompoundSelect constructs such as _sql.union(). The issue was caused by the cache key for compound selects not including the execution options that are part of the _sql.Executable base class, which _orm.with_loader_criteria() uses to apply its criteria dynamically. The fix ensures that compound selects and other executable constructs properly include execution options in their cache key traversal.

  References: #12905

engine

• [engine] [bug] Implemented initial support for free-threaded Python by adding new tests and reworking the test harness to include Python 3.13t and Python 3.14t in test runs. Two concurrency issues have been identified and fixed: the first involves initialization of the .c collection on a FromClause, a continuation of #12302, where an optional mutex under free-threading is added; the second involves synchronization of the pool "first_connect" event, which first received thread synchronization in #2964, however under free-threading the creation of the mutex itself runs under the same free-threading mutex. Support for free-threaded wheels on Pypi is implemented as well within the 2.1 series only. Initial pull request and test suite courtesy Lysandros Nikolaou.

  References: #12881

sql

• [sql] [bug] Improved the implementation of UpdateBase.returning() to use more robust logic in setting up the .c collection of a derived statement such as a CTE. This fixes issues related to RETURNING clauses that feature expressions based on returned columns with or without qualifying labels.

  References: #12271

schema

• [schema] [bug] Fixed issue where _schema.MetaData.reflect() did not forward dialect-specific keyword arguments to the _engine.Inspector methods, causing options like oracle_resolve_synonyms to be ignored during reflection. The method now ensures that all extra kwargs passed to _schema.MetaData.reflect() are forwarded to _engine.Inspector.get_table_names() and related reflection methods. Pull request courtesy Lukáš Kožušník.

  References: #12884

typing

• [typing] [bug] Fixed typing bug where the Session.execute() method advertised that it would return a CursorResult if given an insert/update/delete statement. This is not the general case as several flavors of ORM insert/update do not actually yield a CursorResult which cannot be differentiated at the typing overload level, so the method now yields Result in all cases. For those cases where CursorResult is known to be returned and the .rowcount attribute is required, please use typing.cast().

  References: #12813

• [typing] [bug] Added new decorator _orm.mapped_as_dataclass(), which is a function based form of _orm.registry.mapped_as_dataclass(); the method form _orm.registry.mapped_as_dataclass() does not seem to be correctly recognized within the scope of PEP 681 in recent mypy versions.

  References: #12855

asyncio

• [asyncio] [usecase] Generalize the terminate logic employed by the asyncpg dialect to reuse it in the aiomysql and asyncmy dialect implementation.

  References: #12273

postgresql

• [postgresql] [bug] Fixed issue where selecting an enum array column containing NULL values would fail to parse properly in the PostgreSQL dialect. The _split_enum_values() function now correctly handles NULL entries by converting them to Python None values.

  References: #12847

• [postgresql] [bug] Fixed issue where the _sql.any_() and _sql.all_() aggregation operators would not correctly coerce the datatype of the compared value, in those cases where the compared value were not a simple int/str etc., such as a Python Enum or other custom value. This would lead to execution time errors for these values. This issue is essentially the same as #6515 which was for the now-legacy ARRAY.any() and ARRAY.all() methods.

  References: #12874

sqlite

• [sqlite] [bug] Fixed issue where SQLite table reflection would fail for tables using WITHOUT ROWID and/or STRICT table options when the table contained generated columns. The regular expression used to parse CREATE TABLE statements for generated column detection has been updated to properly handle these SQLite table options that appear after the column definitions. Pull request courtesy Tip ten Brink.

  References: #12864

mssql

• [mssql] [bug] Improved the base implementation of the asyncio cursor such that it includes the option for the underlying driver's cursor to be actively closed in those cases where it requires await in order to complete the close sequence, rather than relying on garbage collection to "close" it, when a plain Result is returned that does not use await for any of its methods. The previous approach of relying on gc was fine for MySQL and SQLite dialects but has caused problems with the aioodbc implementation on top of SQL Server. The new option is enabled for those dialects which have an "awaitable" cursor.close(), which includes the aioodbc, aiomysql, and asyncmy dialects (aiosqlite is also modified for 2.1 only).

  References: #12798

• [mssql] [bug] Fixed issue where the index reflection for SQL Server would not correctly return the order of the column inside an index when the order of the columns in the index did not match the order of the columns in the table. Pull request courtesy of Allen Chen.

  References: #12894

• [mssql] [bug] [reflection] Fixed issue in the MSSQL dialect's foreign key reflection query where duplicate rows could be returned when a foreign key column and its referenced primary key column have the same name, and both the referencing and referenced tables have indexes with the same name. This resulted in an "ForeignKeyConstraint with duplicate source column references are not supported" error when attempting to reflect such tables. The query has been corrected to exclude indexes on the child table when looking for unique indexes referenced by foreign keys.

  References: #12907

misc

• [bug] [ext] Fixed issue caused by an unwanted functional change while typing the MutableList class. This change also reverts all other functional changes done in the same change.

  References: #12802

2.0.43

Released: August 11, 2025

orm

• [orm] [bug] Fixed issue where using the post_update feature would apply incorrect "pre-fetched" values to the ORM objects after a multi-row UPDATE process completed. These "pre-fetched" values would come from any column that had an Column.onupdate callable or a version id generator used by orm.Mapper.version_id_generator; for a version id generator that delivered random identifiers like timestamps or UUIDs, this incorrect data would lead to a DELETE statement against those same rows to fail in the next step.

  References: #12748

• [orm] [bug] Fixed issue where _orm.mapped_column.use_existing_column parameter in _orm.mapped_column() would not work when the _orm.mapped_column() is used inside of an Annotated type alias in polymorphic inheritance scenarios. The parameter is now properly recognized and processed during declarative mapping configuration.

  References: #12787

• [orm] [bug] Improved the implementation of the _orm.selectin_polymorphic() inheritance loader strategy to properly render the IN expressions using chunks of 500 records each, in the same manner as that of the _orm.selectinload() relationship loader strategy. Previously, the IN expression would be arbitrarily large, leading to failures on databases that have limits on the size of IN expressions including Oracle Database.

  References: #12790

engine

• [engine] [usecase] Added new parameter create_engine.skip_autocommit_rollback which provides for a per-dialect feature of preventing the DBAPI .rollback() from being called under any circumstances, if the connection is detected as being in "autocommit" mode. This improves upon a critical performance issue identified in MySQL dialects where the network overhead of the .rollback() call remains prohibitive even if autocommit mode is set.

  References: #12784

postgresql

• [postgresql] [bug] Fixed regression in PostgreSQL dialect where JSONB subscription syntax would generate incorrect SQL for JSONB-returning functions, causing syntax errors. The dialect now properly wraps function calls and expressions in parentheses when using the [] subscription syntax, generating (function_call)[index] instead of function_call[index] to comply with PostgreSQL syntax requirements.

  References: #12778

oracle

• [oracle] [usecase] Extended _oracle.VECTOR to support sparse vectors. This update introduces _oracle.VectorStorageType to specify sparse or dense storage and added _oracle.SparseVector. Pull request courtesy Suraj Shaw.

  References: #12711

2.0.42

Released: July 29, 2025

orm

• [orm] [usecase] Added dataclass_metadata argument to all ORM attribute constructors that accept dataclasses parameters, e.g. mapped_column.dataclass_metadata, relationship.dataclass_metadata, etc. It's passed to the underlying dataclass metadata attribute of the dataclass field. Pull request courtesy Sigmund Lahn.

  References: #10674

• [orm] [bug] Implemented the _orm.defer(), _orm.undefer() and _orm.load_only() loader options to work for composite attributes, a use case that had never been supported previously.

  References: #12593

• [orm] [bug] Fixed bug where the ORM would pull in the wrong column into an UPDATE when a key name inside of the ValuesBase.values() method could be located from an ORM entity mentioned in the statement, but where that ORM entity was not the actual table that the statement was inserting or updating. An extra check for this edge case is added to avoid this problem.

  References: #12692

engine

• [engine] Improved validation of execution parameters passed to the _engine.Connection.execute() and similar methods to provided a better error when tuples are passed in. Previously the execution would fail with a difficult to understand error message.

sql

• [sql] [usecase] The _sql.values() construct gains a new method _sql.Values.cte(), which allows creation of a named, explicit-columns CTE against an unnamed VALUES expression, producing a syntax that allows column-oriented selection from a VALUES construct on modern versions of PostgreSQL, SQLite, and MariaDB.

  References: #12734

• [sql] [bug] Fixed issue where select() of a free-standing scalar expression that has a unary operator applied, such as negation, would not apply result processors to the selected column even though the correct type remains in place for the unary expression.

  References: #12681

• [sql] [bug] Hardening of the compiler's actions for UPDATE statements that access multiple tables to report more specifically when tables or aliases are referenced in the SET clause; on cases where the backend does not support secondary tables in the SET clause, an explicit error is raised, and on the MySQL or similar backends that support such a SET clause, more specific checking for not-properly-included tables is performed. Overall the change is preventing these erroneous forms of UPDATE statements from being compiled, whereas previously it was relied on the database to raise an error, which was not always guaranteed to happen, or to be non-ambiguous, due to cases where the parent table included the same column name as the secondary table column being updated.

  References: #12692

postgresql

• [postgresql] [usecase] Added support for PostgreSQL 14+ JSONB subscripting syntax. When connected to PostgreSQL 14 or later, JSONB columns now automatically use the native subscript notation jsonb_col['key'] instead of the arrow operator jsonb_col -> 'key' for both read and write operations. This provides better compatibility with PostgreSQL's native JSONB subscripting feature while maintaining backward compatibility with older PostgreSQL versions. JSON columns continue to use the traditional arrow syntax regardless of PostgreSQL version.

  References: #10927

• [postgresql] [usecase] Added postgresql_ops key to the dialect_options entry in reflected dictionary. This maps names of columns used in the index to respective operator class, if distinct from the default one for column's data type. Pull request courtesy Denis Laxalde.

  References: #8664

• [postgresql] [bug] [reflection] Fixed regression caused by #10665 where the newly modified constraint reflection query would fail on older versions of PostgreSQL such as version 9.6. Pull request courtesy Denis Laxalde.

  References: #12600

• [postgresql] [bug] Re-raise catched CancelledError in the terminate method of the asyncpg dialect to avoid possible hangs of the code execution.

  References: #12728

• [postgresql] [bug] [reflection] Fixes bug that would mistakenly interpret a domain or enum type with name starting in interval as an INTERVAL type while reflecting a table.

  References: #12744

mysql

• [mysql] [bug] Fixed yet another regression caused by by the DEFAULT rendering changes in 2.0.40 #12425, similar to #12488, this time where using a CURRENT_TIMESTAMP function with a fractional seconds portion inside a textual default value would also fail to be recognized as a non-parenthesized server default.

  References: #12648

mssql

• [mssql] [bug] Reworked SQL Server column reflection to be based on the sys.columns table rather than information_schema.columns view. By correctly using the SQL Server object_id() function as a lead and joining to related tables on object_id rather than names, this repairs a variety of issues in SQL Server reflection, including:

  -   Issue where reflected column comments would not correctly line up
      with the columns themselves in the case that the table had been ALTERed
  
  -   Correctly targets tables with awkward names such as names with brackets,
      when reflecting not just the basic table / columns but also extended
      information including IDENTITY, computed columns, comments which
      did not work previously
  
  -   Correctly targets IDENTITY, computed status from temporary tables
      which did not work previously
  

  References: #12654

2.0.41

Released: May 14, 2025

platform

• [platform] [bug] Adjusted the test suite as well as the ORM's method of scanning classes for annotations to work under current beta releases of Python 3.14 (currently 3.14.0b1) as part of an ongoing effort to support the production release of this Python release. Further changes to Python's means of working with annotations is expected in subsequent beta releases for which SQLAlchemy's test suite will need further adjustments.

  References: #12405

engine

• [engine] [bug] The error message that is emitted when a URL cannot be parsed no longer includes the URL itself within the error message.

  References: #12579

typing

• [typing] [bug] Removed __getattr__() rule from sqlalchemy/__init__.py that appeared to be trying to correct for a previous typographical error in the imports. This rule interferes with type checking and is removed.

  References: #12588

postgresql

• [postgresql] [usecase] Added support for postgresql_include keyword argument to _schema.UniqueConstraint and _schema.PrimaryKeyConstraint. Pull request courtesy Denis Laxalde.

  References: #10665

mysql

• [mysql] [bug] Fixed regression caused by the DEFAULT rendering changes in version 2.0.40 via #12425 where using lowercase on update in a MySQL server default would incorrectly apply parenthesis, leading to errors when MySQL interpreted the rendered DDL. Pull request courtesy Alexander Ruehe.

  References: #12488

sqlite

• [sqlite] [bug] Fixed and added test support for some SQLite SQL functions hardcoded into the compiler, most notably the localtimestamp function which rendered with incorrect internal quoting.

  References: #12566

oracle

• [oracle] [usecase] Added new datatype _oracle.VECTOR and accompanying DDL and DQL support to fully support this type for Oracle Database. This change includes the base _oracle.VECTOR type that adds new type-specific methods l2_distance, cosine_distance, inner_product as well as new parameters oracle_vector for the Index construct, allowing vector indexes to be configured, and oracle_fetch_approximate for the Select.fetch() clause. Pull request courtesy Suraj Shaw.

  References: #12317, #12341

misc

• [bug] [installation] Removed the "license classifier" from setup.cfg for SQLAlchemy 2.0, which eliminates loud deprecation warnings when building the package. SQLAlchemy 2.1 will use a full PEP 639 configuration in pyproject.toml while SQLAlchemy 2.0 remains using setup.cfg for setup.

2.0.40

Released: March 27, 2025

orm

• [orm] [bug] Fixed regression which occurred as of 2.0.37 where the checked ArgumentError that's raised when an inappropriate type or object is used inside of a Mapped annotation would raise TypeError with "boolean value of this clause is not defined" if the object resolved into a SQL expression in a boolean context, for programs where future annotations mode was not enabled. This case is now handled explicitly and a new error message has also been tailored for this case. In addition, as there are at least half a dozen distinct error scenarios for intepretation of the Mapped construct, these scenarios have all been unified under a new subclass of ArgumentError called MappedAnnotationError, to provide some continuity between these different scenarios, even though specific messaging remains distinct.

  References: #12329

• [orm] [bug] Fixed regression in ORM Annotated Declarative class interpretation caused by typing_extension==4.13.0 that introduced a different implementation for TypeAliasType while SQLAlchemy assumed that it would be equivalent to the typing version, leading to pep-695 type annotations not resolving to SQL types as expected.

  References: #12473

sql

• [sql] [usecase] Implemented support for the GROUPS frame specification in window functions by adding _sql.over.groups option to _sql.over() and FunctionElement.over(). Pull request courtesy Kaan Dikmen.

  References: #12450

• [sql] [bug] Fixed issue in CTE constructs involving multiple DDL _sql.Insert statements with multiple VALUES parameter sets where the bound parameter names generated for these parameter sets would conflict, generating a compile time error.

  References: #12363

• [sql] [bug] Fixed regression caused by #7471 leading to a SQL compilation issue where name disambiguation for two same-named FROM clauses with table aliasing in use at the same time would produce invalid SQL in the FROM clause with two "AS" clauses for the aliased table, due to double aliasing.

  References: #12451

asyncio

• [asyncio] [bug] Fixed issue where AsyncSession.get_transaction() and AsyncSession.get_nested_transaction() would fail with NotImplementedError if the "proxy transaction" used by AsyncSession were garbage collected and needed regeneration.

  References: #12471

postgresql

• [postgresql] [usecase] Added support for specifying a list of columns for SET NULL and SET DEFAULT actions of ON DELETE clause of foreign key definition on PostgreSQL. Pull request courtesy Denis Laxalde.

  References: #11595

• [postgresql] [usecase] When building a PostgreSQL ARRAY literal using _postgresql.array with an empty clauses argument, the _postgresql.array.type_ parameter is now significant in that it will be used to render the resulting ARRAY[] SQL expression with a cast, such as ARRAY[]::INTEGER. Pull request courtesy Denis Laxalde.

  References: #12432

mysql

• [mysql] [bug] Support has been re-added for the MySQL-Connector/Python DBAPI using the mysql+mysqlconnector:// URL scheme. The DBAPI now works against modern MySQL versions as well as MariaDB versions (in the latter case it's required to pass charset/collation explicitly). Note however that server side cursor support is disabled due to unresolved issues with this driver.

  References: #12332

• [mysql] [bug] Fixed issue in MySQL server default reflection where a default that has spaces would not be correctly reflected. Additionally, expanded the rules for when to apply parenthesis to a server default in DDL to suit the general case of a default string that contains non-word characters such as spaces or operators and is not a string literal.

  References: #12425

sqlite

• [sqlite] [bug] Expanded the rules for when to apply parenthesis to a server default in DDL to suit the general case of a default string that contains non-word characters such as spaces or operators and is not a string literal.

  References: #12425

2.0.39

Released: March 11, 2025

orm

• [orm] [bug] Fixed bug where using DML returning such as Insert.returning() with an ORM model that has _orm.column_property() constructs that contain subqueries would fail with an internal error.

  References: #12326

• [orm] [bug] Fixed bug in ORM enabled UPDATE (and theoretically DELETE) where using a multi-table DML statement would not allow ORM mapped columns from mappers other than the primary UPDATE mapper to be named in the RETURNING clause; they would be omitted instead and cause a column not found exception.

  References: #12328

• [orm] [bug] Fixed issue where the "is ORM" flag of a select() or other ORM statement would not be propagated to the ORM Session based on a multi-part operator expression alone, e.g. such as Cls.attr + Cls.attr + Cls.attr or similar, leading to ORM behaviors not taking place for such statements.

  References: #12357

• [orm] [bug] Fixed issue where using _orm.aliased() around a CTE construct could cause inappropriate "duplicate CTE" errors in cases where that aliased construct appeared multiple times in a single statement.

  References: #12364

sql

• [sql] [bug] Added new parameters AddConstraint.isolate_from_table and DropConstraint.isolate_from_table, defaulting to True, which both document and allow to be controllable the long-standing behavior of these two constructs blocking the given constraint from being included inline within the "CREATE TABLE" sequence, under the assumption that separate add/drop directives were to be used.

  References: #12382

typing

• [typing] [usecase] Support generic types for compound selects (_sql.union(), _sql.union_all(), _sql.Select.union(), _sql.Select.union_all(), etc) returning the type of the first select. Pull request courtesy of Mingyu Park.

  References: #11922

asyncio

• [asyncio] [bug] Fixed bug where _asyncio.AsyncResult.scalar(), _asyncio.AsyncResult.scalar_one_or_none(), and _asyncio.AsyncResult.scalar_one() would raise an AttributeError due to a missing internal attribute. Pull request courtesy Allen Ho.

  References: #12338

postgresql

• [postgresql] [bug] Add SQL typing to reflection query used to retrieve a the structure of IDENTITY columns, adding explicit JSON typing to the query to suit unusual PostgreSQL driver configurations that don't support JSON natively.

  References: #11751

• [postgresql] [bug] Fixed issue affecting PostgreSQL 17.3 and greater where reflection of domains with "NOT NULL" as part of their definition would include an invalid constraint entry in the data returned by _postgresql.PGInspector.get_domains() corresponding to an additional "NOT NULL" constraint that isn't a CHECK constraint; the existing "nullable" entry in the dictionary already indicates if the domain includes a "not null" constraint. Note that such domains also cannot be reflected on PostgreSQL 17.0 through 17.2 due to a bug on the PostgreSQL side; if encountering errors in reflection of domains which include NOT NULL, upgrade to PostgreSQL server 17.3 or greater.

• [postgresql] [bug] Fixed issue in PostgreSQL network types _postgresql.INET, _postgresql.CIDR, _postgresql.MACADDR, _postgresql.MACADDR8 where sending string values to compare to these types would render an explicit CAST to VARCHAR, causing some SQL / driver combinations to fail. Pull request courtesy Denis Laxalde.

  References: #12060

• [postgresql] [bug] Fixed compiler issue in the PostgreSQL dialect where incorrect keywords would be passed when using "FOR UPDATE OF" inside of a subquery.

  References: #12417

sqlite

• [sqlite] [bug] Fixed issue that omitted the comma between multiple SQLite table extension clauses, currently WITH ROWID and STRICT, when both options Table.sqlite_with_rowid and Table.sqlite_strict were configured at their non-default settings at the same time. Pull request courtesy david-fed.

  References: #12368

2.0.38

Released: February 6, 2025

engine

• [engine] [bug] Fixed event-related issue where invoking Engine.execution_options() on a Engine multiple times while making use of event-registering parameters such as isolation_level would lead to internal errors involving event registration.

  References: #12289

sql

• [sql] [bug] Reorganized the internals by which the .c collection on a FromClause gets generated so that it is resilient against the collection being accessed in concurrent fashion. An example is creating a Alias or Subquery and accessing it as a module level variable. This impacts the Oracle dialect which uses such module-level global alias objects but is of general use as well.

  References: #12302

• [sql] [bug] Fixed SQL composition bug which impacted caching where using a None value inside of an in_() expression would bypass the usual "expanded bind parameter" logic used by the IN construct, which allows proper caching to take place.

  References: #12314

postgresql

• [postgresql] [usecase] [asyncio] Added an additional asyncio.shield() call within the connection terminate process of the asyncpg driver, to mitigate an issue where terminate would be prevented from completing under the anyio concurrency library.

  References: #12077

• [postgresql] [bug] Adjusted the asyncpg connection wrapper so that the connection.transaction() call sent to asyncpg sends None for isolation_level if not otherwise set in the SQLAlchemy dialect/wrapper, thereby allowing asyncpg to make use of the server level setting for isolation_level in the absense of a client-level setting. Previously, this behavior of asyncpg was blocked by a hardcoded read_committed.

  References: #12159

mariadb

• [mariadb] [bug] [dml] [mysql] Fixed a bug where the MySQL statement compiler would not properly compile statements where _mysql.Insert.on_duplicate_key_update() was passed values that included ORM-mapped attributes (e.g. InstrumentedAttribute objects) as keys. Pull request courtesy of mingyu.

  References: #12117

sqlite

• [sqlite] [bug] [aiosqlite] [asyncio] [pool] Changed default connection pool used by the aiosqlite dialect from NullPool to AsyncAdaptedQueuePool; this change should have been made when 2.0 was first released as the pysqlite dialect was similarly changed to use QueuePool as detailed in change_7490.

  References: #12285

2.0.37

Released: January 9, 2025

orm

• [orm] [bug] Fixed issue regarding Union types that would be present in the _orm.registry.type_annotation_map of a _orm.registry or declarative base class, where a Mapped element that included one of the subtypes present in that Union would be matched to that entry, potentially ignoring other entries that matched exactly. The correct behavior now takes place such that an entry should only match in _orm.registry.type_annotation_map exactly, as a Union type is a self-contained type. For example, an attribute with Mapped[float] would previously match to a _orm.registry.type_annotation_map entry Union[float, Decimal]; this will no longer match and will now only match to an entry that states float. Pull request courtesy Frazer McLean.

  References: #11370

• [orm] [bug] Fixed bug in how type unions were handled within _orm.registry.type_annotation_map as well as _orm.Mapped that made the lookup behavior of a | b different from that of Union[a, b].

  References: #11944

• [orm] [bug] Consistently handle TypeAliasType (defined in PEP 695) obtained with the type X = int syntax introduced in python 3.12. Now in all cases one such alias must be explicitly added to the type map for it to be usable inside Mapped. This change also revises the approach added in #11305, now requiring the TypeAliasType to be added to the type map. Documentation on how unions and type alias types are handled by SQLAlchemy has been added in the orm_declarative_mapped_column_type_map section of the documentation.

  References: #11955

• [orm] [bug] Fixed regression caused by an internal code change in response to recent Mypy releases that caused the very unusual case of a list of ORM-mapped attribute expressions passed to ColumnOperators.in_() to no longer be accepted.

  References: #12019

• [orm] [bug] Fixed issues in type handling within the _orm.registry.type_annotation_map feature which prevented the use of unions, using either pep-604 or Union syntaxes under future annotations mode, which contained multiple generic types as elements from being correctly resolvable.

  References: #12207

• [orm] [bug] Fixed issue in event system which prevented an event listener from being attached and detached from multiple class-like objects, namely the sessionmaker or scoped_session targets that assign to Session subclasses.

  References: #12216

sql

• [sql] [bug] Fixed issue in "lambda SQL" feature where the tracking of bound parameters could be corrupted if the same lambda were evaluated across multiple compile phases, including when using the same lambda across multiple engine instances or with statement caching disabled.

  References: #12084

postgresql

• [postgresql] [usecase] The _postgresql.Range type now supports _postgresql.Range.__contains__(). Pull request courtesy of Frazer McLean.

  References: #12093

• [postgresql] [bug] Fixes issue in Dialect.get_multi_indexes() in the PostgreSQL dialect, where an error would be thrown when attempting to use alembic with a vector index from the pgvecto.rs extension.

  References: #11724

• [postgresql] [bug] Fixed issue where creating a table with a primary column of _sql.SmallInteger and using the asyncpg driver would result in the type being compiled to SERIAL rather than SMALLSERIAL.

  References: #12170

• [postgresql] [bug] Adjusted the asyncpg dialect so that an empty SQL string, which is valid for PostgreSQL server, may be successfully processed at the dialect level, such as when using Connection.exec_driver_sql(). Pull request courtesy Andrew Jackson.

  References: #12220

mysql

• [mysql] [usecase] [mariadb] Added support for the LIMIT clause with DELETE for the MySQL and MariaDB dialects, to complement the already present option for UPDATE. The Delete.with_dialect_options() method of the delete() construct accepts parameters for mysql_limit and mariadb_limit, allowing users to specify a limit on the number of rows deleted. Pull request courtesy of Pablo Nicolás Estevez.

  References: #11764

• [mysql] [bug] [mariadb] Added logic to ensure that the mysql_limit and mariadb_limit parameters of Update.with_dialect_options() and Delete.with_dialect_options() when compiled to string will only compile if the parameter is passed as an integer; a ValueError is raised otherwise.

mariadb

• [mariadb] [usecase] Added sql types INET4 and INET6 in the MariaDB dialect. Pull request courtesy Adam Žurek.

  References: #10720

sqlite

• [sqlite] [usecase] Added SQLite table option to enable STRICT tables. Pull request courtesy of Guilherme Crocetti.

  References: #7398

oracle

• [oracle] [feature] Added new table option oracle_tablespace to specify the TABLESPACE option when creating a table in Oracle. This allows users to define the tablespace in which the table should be created. Pull request courtesy of Miguel Grillo.

  References: #12016

• [oracle] [usecase] Use the connection attribute max_identifier_length available in oracledb since version 2.5 when determining the identifier length in the Oracle dialect.

  References: #12032

• [oracle] [bug] Fixed compilation of TABLE function when used in a FROM clause in Oracle Database dialect.

  References: #12100

• [oracle] [bug] Fixed issue in oracledb / cx_oracle dialects where output type handlers for CLOB were being routed to NVARCHAR rather than VARCHAR, causing a double conversion to take place.

  References: #12150

2.0.36

Released: October 15, 2024

orm

• [orm] [usecase] Added new parameter _orm.mapped_column.hash to ORM constructs such as _orm.mapped_column(), _orm.relationship(), etc., which is interpreted for ORM Native Dataclasses in the same way as other dataclass-specific field parameters.

  References: #11923

• [orm] [bug] Fixed bug in ORM bulk update/delete where using RETURNING with bulk update/delete in combination with populate_existing would fail to accommodate the populate_existing option.

  References: #11912

• [orm] [bug] Continuing from #11912, columns marked with mapped_column.onupdate, mapped_column.server_onupdate, or Computed are now refreshed in ORM instances when running an ORM enabled UPDATE with WHERE criteria, even if the statement does not use RETURNING or populate_existing.

  References: #11917

• [orm] [bug] Fixed regression caused by fixes to joined eager loading in #11449 released in 2.0.31, where a particular joinedload case could not be asserted correctly. We now have an example of that case so the assertion has been repaired to allow for it.

  References: #11965

• [orm] [bug] Improved the error message emitted when trying to map as dataclass a class while also manually providing the __table__ attribute. This usage is currently not supported.

  References: #11973

• [orm] [bug] Refined the check which the ORM lazy loader uses to detect "this would be loading by primary key and the primary key is NULL, skip loading" to take into account the current setting for the orm.Mapper.allow_partial_pks parameter. If this parameter is False, then a composite PK value that has partial NULL elements should also be skipped. This can apply to some composite overlapping foreign key configurations.

  References: #11995

• [orm] [bug] Fixed bug in ORM "update with WHERE clause" feature where an explicit .returning() would interfere with the "fetch" synchronize strategy due to an assumption that the ORM mapped class featured the primary key columns in a specific position within the RETURNING. This has been fixed to use appropriate ORM column targeting.

  References: #11997

sql

• [sql] [usecase] Datatypes that are binary based such as VARBINARY will resolve to LargeBinary when the TypeEngine.as_generic() method is called.

  References: #11978

• [sql] [bug] [regression] Fixed regression from 1.4 where some datatypes such as those derived from TypeDecorator could not be pickled when they were part of a larger SQL expression composition due to internal supporting structures themselves not being pickleable.

  References: #12002

schema

• [schema] [bug] Fixed bug where SQL functions passed to _schema.Column.server_default would not be rendered with the particular form of parenthesization now required by newer versions of MySQL and MariaDB. Pull request courtesy of huuya.

  References: #11317

postgresql

• [postgresql] [bug] [reflection] Fixed bug in reflection of table comments where unrelated text would be returned if an entry in the pg_description table happened to share the same oid (objoid) as the table being reflected.

  References: #11961

• [postgresql] [bug] The postgresql.JSON and postgresql.JSONB datatypes will now render a "bind cast" in all cases for all PostgreSQL backends, including psycopg2, whereas previously it was only enabled for some backends. This allows greater accuracy in allowing the database server to recognize when a string value is to be interpreted as JSON.

  References: #11994

mysql

• [mysql] [performance] Improved a query used for the MySQL 8 backend when reflecting foreign keys to be better optimized. Previously, for a database that had millions of columns across all tables, the query could be prohibitively slow; the query has been reworked to take better advantage of existing indexes.

  References: #11975

2.0.35

Released: September 16, 2024

orm

• [orm] [bug] [typing] Fixed issue where it was not possible to use typing.Literal with Mapped[] on Python 3.8 and 3.9. Pull request courtesy Frazer McLean.

  References: #11820

• [orm] [bug] Fixed issue in ORM evaluator where two datatypes being evaluated with the SQL concatenator operator would not be checked for UnevaluatableError based on their datatype; this missed the case of _postgresql.JSONB values being used in a concatenate operation which is supported by PostgreSQL as well as how SQLAlchemy renders the SQL for this operation, but does not work at the Python level. By implementing UnevaluatableError for this combination, ORM update statements will now fall back to "expire" when a concatenated JSON value used in a SET clause is to be synchronized to a Python object.

  References: #11849

• [orm] [bug] An warning is emitted if _orm.joinedload() or _orm.subqueryload() are used as a top level option against a statement that is not a SELECT statement, such as with an insert().returning(). There are no JOINs in INSERT statements nor is there a "subquery" that can be repurposed for subquery eager loading, and for UPDATE/DELETE joinedload does not support these either, so it is never appropriate for this use to pass silently.

  References: #11853

• [orm] [bug] Fixed issue where using loader options such as _orm.selectinload() with additional criteria in combination with ORM DML such as _sql.insert() with RETURNING would not correctly set up internal contexts required for caching to work correctly, leading to incorrect results.

  References: #11855

mysql

• [mysql] [bug] Fixed issue in mariadbconnector dialect where query string arguments that weren't checked integer or boolean arguments would be ignored, such as string arguments like unix_socket, etc. As part of this change, the argument parsing for particular elements such as client_flags, compress, local_infile has been made more consistent across all MySQL / MariaDB dialect which accept each argument. Pull request courtesy Tobias Alex-Petersen.

  References: #11870

sqlite

• [sqlite] [bug] [regression] The changes made for SQLite CHECK constraint reflection in versions 2.0.33 and 2.0.34 , #11832 and #11677, have now been fully reverted, as users continued to identify existing use cases that stopped working after this change. For the moment, because SQLite does not provide any consistent way of delivering information about CHECK constraints, SQLAlchemy is limited in what CHECK constraint syntaxes can be reflected, including that a CHECK constraint must be stated all on a single, independent line (or inline on a column definition) without newlines, tabs in the constraint definition or unusual characters in the constraint name. Overall, reflection for SQLite is tailored towards being able to reflect CREATE TABLE statements that were originally created by SQLAlchemy DDL constructs. Long term work on a DDL parser that does not rely upon regular expressions may eventually improve upon this situation. A wide range of additional cross-dialect CHECK constraint reflection tests have been added as it was also a bug that these changes did not trip any existing tests.

  References: #11840

2.0.34

Released: September 4, 2024

orm

• [orm] [bug] Fixed regression caused by issue #11814 which broke support for certain flavors of PEP 593 Annotated in the type_annotation_map when builtin types such as list, dict were used without an element type. While this is an incomplete style of typing, these types nonetheless previously would be located in the type_annotation_map correctly.

  References: #11831

sqlite

• [sqlite] [bug] Fixed regression in SQLite reflection caused by #11677 which interfered with reflection for CHECK constraints that were followed by other kinds of constraints within the same table definition. Pull request courtesy Harutaka Kawamura.

  References: #11832

2.0.33

Released: September 3, 2024

general

• [general] [change] The pin for setuptools<69.3 in pyproject.toml has been removed. This pin was to prevent a sudden change in setuptools to use PEP 625 from taking place, which would change the file name of SQLAlchemy's source distribution on pypi to be an all lower case name, which is likely to cause problems with various build environments that expected the previous naming style. However, the presence of this pin is holding back environments that otherwise want to use a newer setuptools, so we've decided to move forward with this change, with the assumption that build environments will have largely accommodated the setuptools change by now.

  References: #11818

orm

• [orm] [bug] [regression] Fixed regression from 1.3 where the column key used for a hybrid property might be populated with that of the underlying column that it returns, for a property that returns an ORM mapped column directly, rather than the key used by the hybrid property itself.

  This change is also backported to: 1.4.54

  References: #11728

• [orm] [bug] Correctly cleanup the internal top-level module registry when no inner modules or classes are registered into it.

  References: #11788

• [orm] [bug] Improvements to the ORM annotated declarative type map lookup dealing with composed types such as dict[str, Any] linking to JSON (or others) with or without "future annotations" mode.

  References: #11814

engine

• [engine] [bug] Fixed issue in internal reflection cache where particular reflection scenarios regarding same-named quoted_name() constructs would not be correctly cached. Pull request courtesy Felix Lüdin.

  References: #11687

sql

• [sql] [bug] [regression] Fixed regression in _sql.Select.with_statement_hint() and others where the generative behavior of the method stopped producing a copy of the object.

  References: #11703

schema

• [schema] [bug] Fixed bug where the metadata element of an Enum datatype would not be transferred to the new MetaData object when the type had been copied via a Table.to_metadata() operation, leading to inconsistent behaviors within create/drop sequences.

  References: #11802

typing

• [typing] [bug] Fixed typing issue with _sql.Select.with_only_columns().

  References: #11782

postgresql

• [postgresql] [bug] Fixed critical issue in the asyncpg driver where a rollback or commit that fails specifically for the MissingGreenlet condition or any other error that is not raised by asyncpg itself would discard the asyncpg transaction in any case, even though the transaction were still idle, leaving to a server side condition with an idle transaction that then goes back into the connection pool. The flags for "transaction closed" are now not reset for errors that are raised outside of asyncpg itself. When asyncpg itself raises an error for .commit() or .rollback(), asyncpg does then discard of this transaction.

  This change is also backported to: 1.4.54

  References: #11819

• [postgresql] [bug] Revising the asyncpg terminate() fix first made in #10717 which improved the resiliency of this call under all circumstances, adding asyncio.CancelledError to the list of exceptions that are intercepted as failing for a graceful .close() which will then proceed to call .terminate().

  References: #11821

mysql

• [mysql] [bug] Fixed issue in MySQL dialect where using INSERT..FROM SELECT in combination with ON DUPLICATE KEY UPDATE would erroneously render on MySQL 8 and above the "AS new" clause, leading to syntax failures. This clause is required on MySQL 8 to follow the VALUES clause if use of the "new" alias is present, however is not permitted to follow a FROM SELECT clause.

  References: #11731

sqlite

• [sqlite] [bug] Improvements to the regex used by the SQLite dialect to reflect the name and contents of a CHECK constraint. Constraints with newline, tab, or space characters in either or both the constraint text and constraint name are now properly reflected. Pull request courtesy Jeff Horemans.

  References: #11677

• [sqlite] [bug] Improvements to the regex used by the SQLite dialect to reflect the name and contents of a UNIQUE constraint that is defined inline within a column definition inside of a SQLite CREATE TABLE statement, accommodating for tab characters present within the column / constraint line. Pull request courtesy John A Stevenson.

  References: #11746

mssql

• [mssql] [bug] Added error "The server failed to resume the transaction" to the list of error strings for the pymssql driver in determining a disconnect scenario, as observed by one user using pymssql under otherwise unknown conditions as leaving an unusable connection in the connection pool which fails to ping cleanly.

  References: #11822

tests

• [tests] [bug] Added missing array_type property to the testing suite SuiteRequirements class.

2.0.32

Released: August 5, 2024

general

• [general] [bug] [regression] Restored legacy class names removed from sqlalalchemy.orm.collections.*, including _orm.MappedCollection, _orm.mapped_collection(), _orm.column_mapped_collection(), _orm.attribute_mapped_collection(). Pull request courtesy Takashi Kajinami.

  References: #11435

orm

• [orm] [usecase] The _orm.aliased.name parameter to _orm.aliased() may now be combined with the _orm.aliased.flat parameter, producing per-table names based on a name-prefixed naming convention. Pull request courtesy Eric Atkin.

  References: #11575

• [orm] [bug] [regression] Fixed regression going back to 1.4 where accessing a collection using the "dynamic" strategy on a transient object and attempting to query would raise an internal error rather than the expected NoResultFound that occurred in 1.3.

  This change is also backported to: 1.4.53

  References: #11562

• [orm] [bug] Fixed issue where using the _orm.Query.enable_eagerloads() and _orm.Query.yield_per() methods at the same time, in order to disable eager loading that's configured on the mapper directly, would be silently ignored, leading to errors or unexpected eager population of attributes.

  References: #10834

• [orm] [bug] [regression] Fixed regression appearing in 2.0.21 caused by #10279 where using a _sql.delete() or _sql.update() against an ORM class that is the base of an inheritance hierarchy, while also specifying that subclasses should be loaded polymorphically, would leak the polymorphic joins into the UPDATE or DELETE statement as well creating incorrect SQL.

  References: #11625

• [orm] [bug] [regression] Fixed regression from version 1.4 in _orm.Session.bulk_insert_mappings() where using the _orm.Session.bulk_insert_mappings.return_defaults parameter would not populate the passed in dictionaries with newly generated primary key values.

  References: #11661

• [orm] Added a warning noting when an _engine.ConnectionEvents.engine_connect() event may be leaving a transaction open, which can alter the behavior of a _orm.Session using such an engine as bind. On SQLAlchemy 2.1 _orm.Session.join_transaction_mode will instead be ignored in all cases when the session bind is an _engine.Engine.

  References: #11163

examples

• [examples] [bug] Fixed issue in history_meta example where the "version" column in the versioned table needs to default to the most recent version number in the history table on INSERT, to suit the use case of a table where rows are deleted, and can then be replaced by new rows that re-use the same primary key identity. This fix adds an additonal SELECT query per INSERT in the main table, which may be inefficient; for cases where primary keys are not re-used, the default function may be omitted. Patch courtesy Philipp H. v. Loewenfeld.

  References: #10267

engine

• [engine] [bug] Fixed issue in "insertmanyvalues" feature where a particular call to cursor.fetchall() were not wrapped in SQLAlchemy's exception wrapper, which apparently can raise a database exception during fetch when using pyodbc.

  References: #11532

sql

• [sql] [bug] Follow up of #11471 to fix caching issue where using the CompoundSelectState.add_cte() method of the CompoundSelectState construct would not set a correct cache key which distinguished between different CTE expressions. Also added tests that would detect issues similar to the one fixed in #11544.

  References: #11471

• [sql] [bug] Fixed bug where the Operators.nulls_first() and Operators.nulls_last() modifiers would not be treated the same way as Operators.desc() and Operators.asc() when determining if an ORDER BY should be against a label name already in the statement. All four modifiers are now treated the same within ORDER BY.

  References: #11592

schema

• [schema] [bug] Fixed additional issues in the event system triggered by unpickling of a Enum datatype, continuing from #11365 and #11360, where dynamically generated elements of the event structure would not be present when unpickling in a new process.

  References: #11530

typing

• [typing] [bug] Fixed internal typing issues to establish compatibility with mypy 1.11.0. Note that this does not include issues which have arisen with the deprecated mypy plugin used by SQLAlchemy 1.4-style code; see the addiional change note for this plugin indicating revised compatibility.

mypy

• [mypy] [bug] The deprecated mypy plugin is no longer fully functional with the latest series of mypy 1.11.0, as changes in the mypy interpreter are no longer compatible with the approach used by the plugin. If code is dependent on the mypy plugin with sqlalchemy2-stubs, it's recommended to pin mypy to be below the 1.11.0 series. Seek upgrading to the 2.0 series of SQLAlchemy and migrating to the modern type annotations.

  This change is also backported to: 1.4.53

postgresql

• [postgresql] [bug] It is now considered a pool-invalidating disconnect event when psycopg2 throws an "SSL SYSCALL error: Success" error message, which can occur when the SSL connection to Postgres is terminated abnormally.

  References: #11522

• [postgresql] [bug] Fixed issue where the _sql.collate() construct, which explicitly sets a collation for a given expression, would maintain collation settings for the underlying type object from the expression, causing SQL expressions to have both collations stated at once when used in further expressions for specific dialects that render explicit type casts, such as that of asyncpg. The _sql.collate() construct now assigns its own type to explicitly include the new collation, assuming it's a string type.

  References: #11576

mysql

• [mysql] [bug] Fixed issue in MySQL dialect where ENUM values that contained percent signs were not properly escaped for the driver.

  References: #11479

sqlite

• [sqlite] [bug] [reflection] Fixed reflection of computed column in SQLite to properly account for complex expressions.

  This change is also backported to: 1.4.53

  References: #11582

mssql

• [mssql] [bug] Fixed issue where SQL Server drivers don't support bound parameters when rendering the "frame specification" for a window function, e.g. "ROWS BETWEEN", etc.

  This change is also backported to: 1.4.53

  References: #11514

oracle

• [oracle] [usecase] Added API support for server-side cursors for the oracledb async dialect, allowing use of the _asyncio.AsyncConnection.stream() and similar stream methods.

  References: #10820

• [oracle] [usecase] Implemented two-phase transactions for the oracledb dialect. Historically, this feature never worked with the cx_Oracle dialect, however recent improvements to the oracledb successor now allow this to be possible. The two phase transaction API is available at the Core level via the _engine.Connection.begin_twophase() method.

  References: #11480

• [oracle] [bug] [sqlite] Implemented bitwise operators for Oracle which was previously non-functional due to a non-standard syntax used by this database. Oracle's support for bitwise "or" and "xor" starts with server version 21. Additionally repaired the implementation of "xor" for SQLite.

  As part of this change, the dialect compliance test suite has been enhanced to include support for server-side bitwise tests; third party dialect authors should refer to new "supports_bitwise" methods in the requirements.py file to enable these tests.

  References: #11663

• [oracle] [bug] Fixed table reflection on Oracle 10.2 and older where compression options are not supported.

  References: #11557

2.0.31

Released: June 18, 2024

general

• [general] [bug] Set up full Python 3.13 support to the extent currently possible, repairing issues within internal language helpers as well as the serializer extension module.

  References: #11417

orm

• [orm] [usecase] Added missing parameter _orm.with_polymorphic.name that allows specifying the name of returned _orm.AliasedClass.

  References: #11361

• [orm] [bug] Fixed issue where a MetaData collection would not be serializable, if an Enum or Boolean datatype were present which had been adapted. This specific scenario in turn could occur when using the Enum or Boolean within ORM Annotated Declarative form where type objects frequently get copied.

  References: #11365

• [orm] [bug] Fixed issue where the _orm.selectinload() and _orm.subqueryload() loader options would fail to take effect when made against an inherited subclass that itself included a subclass-specific _orm.Mapper.with_polymorphic setting.

  References: #11446

• [orm] [bug] Fixed very old issue involving the _orm.joinedload.innerjoin parameter where making use of this parameter mixed into a query that also included joined eager loads along a self-referential or other cyclical relationship, along with complicating factors like inner joins added for secondary tables and such, would have the chance of splicing a particular inner join to the wrong part of the query. Additional state has been added to the internal method that does this splice to make a better decision as to where splicing should proceed.

  References: #11449

• [orm] [bug] [regression] Fixed bug in ORM Declarative where the __table__ directive could not be declared as a class function with _orm.declared_attr() on a superclass, including an __abstract__ class as well as coming from the declarative base itself. This was a regression since 1.4 where this was working, and there were apparently no tests for this particular use case.

  References: #11509

sql

• [sql] [bug] Fixed issue when serializing an _sql.over() clause with unbounded range or rows.

  References: #11422

• [sql] [bug] Added missing methods _sql.FunctionFilter.within_group() and _sql.WithinGroup.filter()

  References: #11423

• [sql] [bug] Fixed bug in _sql.FunctionFilter.filter() that would mutate the existing function in-place. It now behaves like the rest of the SQLAlchemy API, returning a new instance instead of mutating the original one.

  References: #11426

schema

• [schema] [usecase] Added _schema.Column.insert_default as an alias of _schema.Column.default for compatibility with _orm.mapped_column().

  References: #11374

mysql

• [mysql] [usecase] [reflection] Added missing foreign key reflection option SET DEFAULT in the MySQL and MariaDB dialects. Pull request courtesy of Quentin Roche.

  References: #11285

2.0.30

Released: May 5, 2024

orm

• [orm] [bug] Added new attribute _orm.ORMExecuteState.is_from_statement to detect statements created using _sql.Select.from_statement(), and enhanced FromStatement to set _orm.ORMExecuteState.is_select, _orm.ORMExecuteState.is_insert, _orm.ORMExecuteState.is_update, and _orm.ORMExecuteState.is_delete according to the element that is sent to the _sql.Select.from_statement() method itself.

  References: #11220

• [orm] [bug] Fixed issue in _orm.selectin_polymorphic() loader option where attributes defined with _orm.composite() on a superclass would cause an internal exception on load.

  References: #11291

• [orm] [bug] [regression] Fixed regression from 1.4 where using _orm.defaultload() in conjunction with a non-propagating loader like _orm.contains_eager() would nonetheless propagate the _orm.contains_eager() to a lazy load operation, causing incorrect queries as this option is only intended to come from an original load.

  References: #11292

• [orm] [bug] Fixed issue in ORM Annotated Declarative where typing issue where literals defined using PEP 695 type aliases would not work with inference of Enum datatypes. Pull request courtesy of Alc-Alc.

  References: #11305

• [orm] [bug] Fixed issue in _orm.selectin_polymorphic() loader option where the SELECT emitted would only accommodate for the child-most class among the result rows that were returned, leading intermediary-class attributes to be unloaded if there were no concrete instances of that intermediary-class present in the result. This issue only presented itself for multi-level inheritance hierarchies.

  References: #11327

• [orm] [bug] Fixed issue in _orm.Session.bulk_save_objects() where the form of the identity key produced when using return_defaults=True would be incorrect. This could lead to an errors during pickling as well as identity map mismatches.

  References: #11332

• [orm] [bug] Fixed issue where attribute key names in _orm.Bundle would not be correct when using ORM enabled _sql.select vs. _orm.Query, when the statement contained duplicate column names.

  References: #11347

engine

• [engine] [bug] Fixed issue in the _engine.Connection.execution_options.logging_token option, where changing the value of logging_token on a connection that has already logged messages would not be updated to reflect the new logging token. This in particular prevented the use of _orm.Session.connection() to change the option on the connection, since the BEGIN logging message would already have been emitted.

  References: #11210

• [engine] [bug] Fixed issue in cursor handling which affected handling of duplicate _sql.Column or similar objcts in the columns clause of _sql.select(), both in combination with arbitary _sql.text() clauses in the SELECT list, as well as when attempting to retrieve _engine.Result.mappings() for the object, which would lead to an internal error.

  References: #11306

typing

• [typing] [bug] [regression] Fixed typing regression caused by #11055 in version 2.0.29 that added ParamSpec to the asyncio run_sync() methods, where using _asyncio.AsyncConnection.run_sync() with _schema.MetaData.reflect() would fail on mypy due to a mypy issue. Pull request courtesy of Francisco R. Del Roio.

  References: #11200

• [typing] [bug] Fixed issue in typing for _orm.Bundle where creating a nested _orm.Bundle structure were not allowed.

misc

• [bug] [test] Ensure the PYTHONPATH variable is properly initialized when using subprocess.run in the tests.

  References: #11268

• [bug] [installation] Fixed an internal class that was testing for unexpected attributes to work correctly under upcoming Python 3.13. Pull request courtesy Edgar Ramírez-Mondragón.

  References: #11334

2.0.29

Released: March 23, 2024

orm

• [orm] [usecase] Added support for the PEP 695 TypeAliasType construct as well as the python 3.12 native type keyword to work with ORM Annotated Declarative form when using these constructs to link to a PEP 593 Annotated container, allowing the resolution of the Annotated to proceed when these constructs are used in a _orm.Mapped typing container.

  References: #11130

• [orm] [bug] Fixed Declarative issue where typing a relationship using _orm.Relationship rather than _orm.Mapped would inadvertently pull in the "dynamic" relationship loader strategy for that attribute.

  References: #10611

• [orm] [bug] Fixed issue in ORM annotated declarative where using _orm.mapped_column() with an _orm.mapped_column.index or _orm.mapped_column.unique setting of False would be overridden by an incoming Annotated element that featured that parameter set to True, even though the immediate _orm.mapped_column() element is more specific and should take precedence. The logic to reconcile the booleans has been enhanced to accommodate a local value of False as still taking precedence over an incoming True value from the annotated element.

  References: #11091

• [orm] [bug] [regression] Fixed regression from version 2.0.28 caused by the fix for #11085 where the newer method of adjusting post-cache bound parameter values would interefere with the implementation for the _orm.subqueryload() loader option, which has some more legacy patterns in use internally, when the additional loader criteria feature were used with this loader option.

  References: #11173

engine

• [engine] [bug] Fixed issue in engine_insertmanyvalues feature where using a primary key column with an "inline execute" default generator such as an explicit Sequence with an explcit schema name, while at the same time using the _engine.Connection.execution_options.schema_translate_map feature would fail to render the sequence or the parameters properly, leading to errors.

  References: #11157

• [engine] [bug] Made a change to the adjustment made in version 2.0.10 for #9618, which added the behavior of reconciling RETURNING rows from a bulk INSERT to the parameters that were passed to it. This behavior included a comparison of already-DB-converted bound parameter values against returned row values that was not always "symmetrical" for SQL column types such as UUIDs, depending on specifics of how different DBAPIs receive such values versus how they return them, necessitating the need for additional "sentinel value resolver" methods on these column types. Unfortunately this broke third party column types such as UUID/GUID types in libraries like SQLModel which did not implement this special method, raising an error "Can't match sentinel values in result set to parameter sets". Rather than attempt to further explain and document this implementation detail of the "insertmanyvalues" feature including a public version of the new method, the approach is intead revised to no longer need this extra conversion step, and the logic that does the comparison now works on the pre-converted bound parameter value compared to the post-result-processed value, which should always be of a matching datatype. In the unusual case that a custom SQL column type that also happens to be used in a "sentinel" column for bulk INSERT is not receiving and returning the same value type, the "Can't match" error will be raised, however the mitigation is straightforward in that the same Python datatype should be passed as that returned.

  References: #11160

sql

• [sql] [bug] [regression] Fixed regression from the 1.4 series where the refactor of the _types.TypeEngine.with_variant() method introduced at change_6980 failed to accommodate for the .copy() method, which will lose the variant mappings that are set up. This becomes an issue for the very specific case of a "schema" type, which includes types such as Enum and ARRAY, when they are then used in the context of an ORM Declarative mapping with mixins where copying of types comes into play. The variant mapping is now copied as well.

  References: #11176

typing

• [typing] [bug] Fixed typing issue allowing asyncio run_sync() methods to correctly type the parameters according to the callable that was passed, making use of PEP 612 ParamSpec variables. Pull request courtesy Francisco R. Del Roio.

  References: #11055

postgresql

• [postgresql] [usecase] The PostgreSQL dialect now returns _postgresql.DOMAIN instances when reflecting a column that has a domain as type. Previously, the domain data type was returned instead. As part of this change, the domain reflection was improved to also return the collation of the text types. Pull request courtesy of Thomas Stephenson.

  References: #10693

tests

• [tests] [bug] Backported to SQLAlchemy 2.0 an improvement to the test suite with regards to how asyncio related tests are run, now using the newer Python 3.11 asyncio.Runner or a backported equivalent, rather than relying on the previous implementation based on asyncio.get_running_loop(). This should hopefully prevent issues with large suite runs on CPU loaded hardware where the event loop seems to become corrupted, leading to cascading failures.

  References: #11187

2.0.28

Released: March 4, 2024

orm

• [orm] [performance] [bug] [regression] Adjusted the fix made in #10570, released in 2.0.23, where new logic was added to reconcile possibly changing bound parameter values across cache key generations used within the _orm.with_expression() construct. The new logic changes the approach by which the new bound parameter values are associated with the statement, avoiding the need to deep-copy the statement which can result in a significant performance penalty for very deep / complex SQL constructs. The new approach no longer requires this deep-copy step.

  References: #11085

• [orm] [bug] [regression] Fixed regression caused by #9779 where using the "secondary" table in a relationship and_() expression would fail to be aliased to match how the "secondary" table normally renders within a _sql.Select.join() expression, leading to an invalid query.

  References: #11010

engine

• [engine] [usecase] Added new core execution option _engine.Connection.execution_options.preserve_rowcount. When set, the cursor.rowcount attribute from the DBAPI cursor will be unconditionally memoized at statement execution time, so that whatever value the DBAPI offers for any kind of statement will be available using the _engine.CursorResult.rowcount attribute from the _engine.CursorResult. This allows the rowcount to be accessed for statments such as INSERT and SELECT, to the degree supported by the DBAPI in use. The engine_insertmanyvalues also supports this option and will ensure _engine.CursorResult.rowcount is correctly set for a bulk INSERT of rows when set.

  References: #10974

asyncio

• [asyncio] [bug] An error is raised if a QueuePool or other non-asyncio pool class is passed to _asyncio.create_async_engine(). This engine only accepts asyncio-compatible pool classes including AsyncAdaptedQueuePool. Other pool classes such as NullPool are compatible with both synchronous and asynchronous engines as they do not perform any locking.

  References: #8771

tests

• [tests] [change] pytest support in the tox.ini file has been updated to support pytest 8.1.

2.0.27

Released: February 13, 2024

postgresql

• [postgresql] [bug] [regression] Fixed regression caused by just-released fix for #10863 where an invalid exception class were added to the "except" block, which does not get exercised unless such a catch actually happens. A mock-style test has been added to ensure this catch is exercised in unit tests.

  References: #11005

2.0.26

Released: February 11, 2024

orm

• [orm] [bug] Replaced the "loader depth is excessively deep" warning with a shorter message added to the caching badge within SQL logging, for those statements where the ORM disabled the cache due to a too-deep chain of loader options. The condition which this warning highlights is difficult to resolve and is generally just a limitation in the ORM's application of SQL caching. A future feature may include the ability to tune the threshold where caching is disabled, but for now the warning will no longer be a nuisance.

  References: #10896

• [orm] [bug] Fixed issue where it was not possible to use a type (such as an enum) within a _orm.Mapped container type if that type were declared locally within the class body. The scope of locals used for the eval now includes that of the class body itself. In addition, the expression within _orm.Mapped may also refer to the class name itself, if used as a string or with future annotations mode.

  References: #10899

• [orm] [bug] Fixed issue where using _orm.Session.delete() along with the _orm.Mapper.version_id_col feature would fail to use the correct version identifier in the case that an additional UPDATE were emitted against the target object as a result of the use of _orm.relationship.post_update on the object. The issue is similar to #10800 just fixed in version 2.0.25 for the case of updates alone.

  References: #10967

• [orm] [bug] Fixed issue where an assertion within the implementation for _orm.with_expression() would raise if a SQL expression that was not cacheable were used; this was a 2.0 regression since 1.4.

  References: #10990

examples

• [examples] [bug] Fixed regression in history_meta example where the use of _schema.MetaData.to_metadata() to make a copy of the history table would also copy indexes (which is a good thing), but causing naming conflicts indexes regardless of naming scheme used for those indexes. A "_history" suffix is now added to these indexes in the same way as is achieved for the table name.

  References: #10920

• [examples] [bug] Fixed the performance example scripts in examples/performance to mostly work with the Oracle database, by adding the Identity construct to all the tables and allowing primary generation to occur on this backend. A few of the "raw DBAPI" cases still are not compatible with Oracle.

sql

• [sql] [bug] Fixed issues in _sql.case() where the logic for determining the type of the expression could result in NullType if the last element in the "whens" had no type, or in other cases where the type could resolve to None. The logic has been updated to scan all given expressions so that the first non-null type is used, as well as to always ensure a type is present. Pull request courtesy David Evans.

  References: #10843

typing

• [typing] [bug] Fixed the type signature for the PoolEvents.checkin() event to indicate that the given DBAPIConnection argument may be None in the case where the connection has been invalidated.

postgresql

• [postgresql] [usecase] [reflection] Added support for reflection of PostgreSQL CHECK constraints marked with "NO INHERIT", setting the key no_inherit=True in the reflected data. Pull request courtesy Ellis Valentiner.

  References: #10777

• [postgresql] [usecase] Support the USING <method> option for PostgreSQL CREATE TABLE to specify the access method to use to store the contents for the new table. Pull request courtesy Edgar Ramírez-Mondragón.

  References: #10904

• [postgresql] [usecase] Correctly type PostgreSQL RANGE and MULTIRANGE types as Range[T] and Sequence[Range[T]]. Introduced utility sequence _postgresql.MultiRange to allow better interoperability of MULTIRANGE types.

  References: #9736

• [postgresql] [usecase] Differentiate between INT4 and INT8 ranges and multi-ranges types when inferring the database type from a _postgresql.Range or _postgresql.MultiRange instance, preferring INT4 if the values fit into it.

• [postgresql] [bug] [regression] Fixed regression in the asyncpg dialect caused by #10717 in release 2.0.24 where the change that now attempts to gracefully close the asyncpg connection before terminating would not fall back to terminate() for other potential connection-related exceptions other than a timeout error, not taking into account cases where the graceful .close() attempt fails for other reasons such as connection errors.

  References: #10863

• [postgresql] [bug] Fixed an issue regarding the use of the Uuid datatype with the Uuid.as_uuid parameter set to False, when using PostgreSQL dialects. ORM-optimized INSERT statements (e.g. the "insertmanyvalues" feature) would not correctly align primary key UUID values for bulk INSERT statements, resulting in errors. Similar issues were fixed for the pymssql driver as well.

mysql

• [mysql] [bug] Fixed issue where NULL/NOT NULL would not be properly reflected from a MySQL column that also specified the VIRTUAL or STORED directives. Pull request courtesy Georg Wicke-Arndt.

  References: #10850

• [mysql] [bug] Fixed issue in asyncio dialects asyncmy and aiomysql, where their .close() method is apparently not a graceful close. replace with non-standard .ensure_closed() method that's awaitable and move .close() to the so-called "terminate" case.

  References: #10893

mssql

• [mssql] [bug] Fixed an issue regarding the use of the Uuid datatype with the Uuid.as_uuid parameter set to False, when using the pymssql dialect. ORM-optimized INSERT statements (e.g. the "insertmanyvalues" feature) would not correctly align primary key UUID values for bulk INSERT statements, resulting in errors. Similar issues were fixed for the PostgreSQL drivers as well.

oracle

• [oracle] [performance] [bug] Changed the default arraysize of the Oracle dialects so that the value set by the driver is used, that is 100 at the time of writing for both cx_oracle and oracledb. Previously the value was set to 50 by default. The setting of 50 could cause significant performance regressions compared to when using cx_oracle/oracledb alone to fetch many hundreds of rows over slower networks.

  References: #10877

2.0.25

Released: January 2, 2024

orm

• [orm] [usecase] Added preliminary support for Python 3.12 pep-695 type alias structures, when resolving custom type maps for ORM Annotated Declarative mappings.

  References: #10807

• [orm] [bug] Fixed issue where when making use of the _orm.relationship.post_update feature at the same time as using a mapper version_id_col could lead to a situation where the second UPDATE statement emitted by the post-update feature would fail to make use of the correct version identifier, assuming an UPDATE was already emitted in that flush which had already bumped the version counter.

  References: #10800

• [orm] [bug] Fixed issue where ORM Annotated Declarative would mis-interpret the left hand side of a relationship without any collection specified as uselist=True if the left type were given as a class and not a string, without using future-style annotations.

  References: #10815

sql

• [sql] [bug] Improved compilation of _sql.any_() / _sql.all_() in the context of a negation of boolean comparison, will now render NOT (expr) rather than reversing the equality operator to not equals, allowing finer-grained control of negations for these non-typical operators.

  References: #10817

typing

• [typing] [bug] Fixed regressions caused by typing added to the sqlalchemy.sql.functions module in version 2.0.24, as part of #6810:

  -   Further enhancements to pep-484 typing to allow SQL functions from
      `_sql.func` derived elements to work more effectively with ORM-mapped
      attributes ([#10801](https://www.sqlalchemy.org/trac/ticket/10801))
  
  -   Fixed the argument types passed to functions so that literal expressions
      like strings and ints are again interpreted correctly ([#10818](https://www.sqlalchemy.org/trac/ticket/10818))
  

  References: #10801, #10818

asyncio

• [asyncio] [bug] Fixed critical issue in asyncio version of the connection pool where calling _asyncio.AsyncEngine.dispose() would produce a new connection pool that did not fully re-establish the use of asyncio-compatible mutexes, leading to the use of a plain threading.Lock() which would then cause deadlocks in an asyncio context when using concurrency features like asyncio.gather().

  This change is also backported to: 1.4.51

  References: #10813

oracle

• [oracle] [asyncio] Added support for oracledb in asyncio mode, using the newly released version of the oracledb DBAPI that includes asyncio support. For the 2.0 series, this is a preview release, where the current implementation does not yet have include support for _asyncio.AsyncConnection.stream(). Improved support is planned for the 2.1 release of SQLAlchemy.

  References: #10679

2.0.24

Released: December 28, 2023

orm

• [orm] [bug] Improved a fix first implemented for #3208 released in version 0.9.8, where the registry of classes used internally by declarative could be subject to a race condition in the case where individual mapped classes are being garbage collected at the same time while new mapped classes are being constructed, as can happen in some test suite configurations or dynamic class creation environments. In addition to the weakref check already added, the list of items being iterated is also copied first to avoid "list changed while iterating" errors. Pull request courtesy Yilei Yang.

  This change is also backported to: 1.4.51

  References: #10782

• [orm] [bug] Fixed issue where use of _orm.foreign() annotation on a non-initialized _orm.mapped_column() construct would produce an expression without a type, which was then not updated at initialization time of the actual column, leading to issues such as relationships not determining use_get appropriately.

  References: #10597

• [orm] [bug] Improved the error message produced when the unit of work process sets the value of a primary key column to NULL due to a related object with a dependency rule on that column being deleted, to include not just the destination object and column name but also the source column from which the NULL value is originating. Pull request courtesy Jan Vollmer.

  References: #10668

• [orm] [bug] Modified the __init_subclass__() method used by _orm.MappedAsDataclass, _orm.DeclarativeBase and _orm.DeclarativeBaseNoMeta to accept arbitrary **kw and to propagate them to the super() call, allowing greater flexibility in arranging custom superclasses and mixins which make use of __init_subclass__() keyword arguments. Pull request courtesy Michael Oliver.

  References: #10732

• [orm] [bug] Ensured the use case of Bundle objects used in the returning() portion of ORM-enabled INSERT, UPDATE and DELETE statements is tested and works fully. This was never explicitly implemented or tested previously and did not work correctly in the 1.4 series; in the 2.0 series, ORM UPDATE/DELETE with WHERE criteria was missing an implementation method preventing Bundle objects from working.

  References: #10776

• [orm] [bug] Fixed 2.0 regression in MutableList where a routine that detects sequences would not correctly filter out string or bytes instances, making it impossible to assign a string value to a specific index (while non-sequence values would work fine).

  References: #10784

engine

• [engine] [bug] Fixed URL-encoding of the username and password components of engine.URL objects when converting them to string using the _engine.URL.render_as_string() method, by using Python standard library urllib.parse.quote while allowing for plus signs and spaces to remain unchanged as supported by SQLAlchemy's non-standard URL parsing, rather than the legacy home-grown routine from many years ago. Pull request courtesy of Xavier NUNN.

  References: #10662

sql

• [sql] [bug] Fixed issue in stringify for SQL elements, where a specific dialect is not passed, where a dialect-specific element such as the PostgreSQL "on conflict do update" construct is encountered and then fails to provide for a stringify dialect with the appropriate state to render the construct, leading to internal errors.

  References: #10753

• [sql] [bug] Fixed issue where stringifying or compiling a CTE that was against a DML construct such as an _sql.insert() construct would fail to stringify, due to a mis-detection that the statement overall is an INSERT, leading to internal errors.

schema

• [schema] [bug] Fixed issue where error reporting for unexpected schema item when creating objects like _schema.Table would incorrectly handle an argument that was itself passed as a tuple, leading to a formatting error. The error message has been modernized to use f-strings.

  References: #10654

typing

• [typing] [bug] Completed pep-484 typing for the sqlalchemy.sql.functions module. _sql.select() constructs made against func elements should now have filled-in return types.

  References: #6810

asyncio

• [asyncio] [change] The async_fallback dialect argument is now deprecated, and will be removed in SQLAlchemy 2.1. This flag has not been used for SQLAlchemy's test suite for some time. asyncio dialects can still run in a synchronous style by running code within a greenlet using _util.greenlet_spawn().

postgresql

• [postgresql] [bug] Adjusted the asyncpg dialect such that when the terminate() method is used to discard an invalidated connection, the dialect will first attempt to gracefully close the connection using .close() with a timeout, if the operation is proceeding within an async event loop context only. This allows the asyncpg driver to attend to finalizing a TimeoutError including being able to close a long-running query server side, which otherwise can keep running after the program has exited.

  References: #10717

mysql

• [mysql] [bug] Fixed regression introduced by the fix in ticket #10492 when using pool pre-ping with PyMySQL version older than 1.0.

  This change is also backported to: 1.4.51

  References: #10650

• [mysql] [bug] Fixed regression introduced by the fix in ticket #10492 when using pool pre-ping with PyMySQL version older than 1.0.

  This change is also backported to: 1.4.51

  References: #10650

tests

• [tests] [bug] Improvements to the test suite to further harden its ability to run when Python greenlet is not installed. There is now a tox target that includes the token "nogreenlet" that will run the suite with greenlet not installed (note that it still temporarily installs greenlet as part of the tox config, however).

  References: #10747

2.0.23

Released: November 2, 2023

orm

• [orm] [usecase] Implemented the _orm.Session.bulk_insert_mappings.render_nulls parameter for new style bulk ORM inserts, allowing render_nulls=True as an execution option. This allows for bulk ORM inserts with a mixture of None values in the parameter dictionaries to use a single batch of rows for a given set of dicationary keys, rather than breaking up into batches that omit the NULL columns from each INSERT.

  References: #10575

• [orm] [bug] Fixed issue where the __allow_unmapped__ directive failed to allow for legacy Column / deferred() mappings that nonetheless had annotations such as Any or a specific type without Mapped[] as their type, without errors related to locating the attribute name.

  References: #10516

• [orm] [bug] Fixed caching bug where using the _orm.with_expression() construct in conjunction with loader options _orm.selectinload(), _orm.lazyload() would fail to substitute bound parameter values correctly on subsequent caching runs.

  References: #10570

• [orm] [bug] Fixed bug in ORM annotated declarative where using a ClassVar that nonetheless referred in some way to an ORM mapped class name would fail to be interpreted as a ClassVar that's not mapped.

  References: #10472

sql

• [sql] [usecase] Implemented "literal value processing" for the Interval datatype for both the PostgreSQL and Oracle dialects, allowing literal rendering of interval values. Pull request courtesy Indivar Mishra.

  References: #9737

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

  This change is also backported to: 1.4.50

  References: #10142

• [sql] [bug] Added compiler-level None/NULL handling for the "literal processors" of all datatypes that include literal processing, that is, where a value is rendered inline within a SQL statement rather than as a bound parameter, for all those types that do not feature explicit "null value" handling. Previously this behavior was undefined and inconsistent.

  References: #10535

• [sql] Removed unused placeholder method TypeEngine.compare_against_backend() This method was used by very old versions of Alembic. See https://github.com/sqlalchemy/alembic/issues/1293 for details.

asyncio

• [asyncio] [bug] Fixed bug with method _asyncio.AsyncSession.close_all() that was not working correctly. Also added function _asyncio.close_all_sessions() that's the equivalent of _orm.close_all_sessions(). Pull request courtesy of Bryan不可思议.

  References: #10421

postgresql

• [postgresql] [bug] Fixed 2.0 regression caused by #7744 where chains of expressions involving PostgreSQL JSON operators combined with other operators such as string concatenation would lose correct parenthesization, due to an implementation detail specific to the PostgreSQL dialect.

  References: #10479

• [postgresql] [bug] Fixed SQL handling for "insertmanyvalues" when using the postgresql.BIT datatype with the asyncpg backend. The postgresql.BIT on asyncpg apparently requires the use of an asyncpg-specific BitString type which is currently exposed when using this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all work with plain bitstrings here. A future fix in version 2.1 will normalize this datatype across all PG backends. Pull request courtesy Sören Oldag.

  References: #10532

mysql

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

  This change is also backported to: 1.4.50

  References: #10492

mariadb

• [mariadb] [bug] Adjusted the MySQL / MariaDB dialects to default a generated column to NULL when using MariaDB, if _schema.Column.nullable was not specified with an explicit True or False value, as MariaDB does not support the "NOT NULL" phrase with a generated column. Pull request courtesy Indivar.

  References: #10056

• [mariadb] [bug] [regression] Established a workaround for what seems to be an intrinsic issue across MySQL/MariaDB drivers where a RETURNING result for DELETE DML which returns no rows using SQLAlchemy's "empty IN" criteria fails to provide a cursor.description, which then yields result that returns no rows, leading to regressions for the ORM that in the 2.0 series uses RETURNING for bulk DELETE statements for the "synchronize session" feature. To resolve, when the specific case of "no description when RETURNING was given" is detected, an "empty result" with a correct cursor description is generated and used in place of the non-working cursor.

  References: #10505

mssql

• [mssql] [usecase] Added support for the aioodbc driver implemented for SQL Server, which builds on top of the pyodbc and general aio* dialect architecture.

  References: #6521

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

  This change is also backported to: 1.4.50

  References: #10504

oracle

• [oracle] [bug] Fixed issue in Interval datatype where the Oracle implementation was not being used for DDL generation, leading to the day_precision and second_precision parameters to be ignored, despite being supported by this dialect. Pull request courtesy Indivar.

  References: #10509

• [oracle] [bug] Fixed issue where the cx_Oracle dialect claimed to support a lower cx_Oracle version (7.x) than was actually supported in practice within the 2.0 series of SQLAlchemy. The dialect imports symbols that are only in cx_Oracle 8 or higher, so runtime dialect checks as well as setup.cfg requirements have been updated to reflect this compatibility.

  References: #10470

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

Released: September 18, 2023

orm

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

  References: #10279

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

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

  References: #10348

engine

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

  References: #10275

sql

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

  References: #10269

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

  References: #9873

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

  References: #9610

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

  References: #10342

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

  References: #10280

schema

• [schema] [bug] Modified the rendering of the Oracle only Identity.order parameter that's part of both Sequence and Identity to only take place for the Oracle backend, and not other backends such as that of PostgreSQL. A future release will rename the Identity.order, Sequence.order and Identity.on_null parameters to Oracle-specific names, deprecating the old names, these parameters only apply to Oracle.

  This change is also backported to: 1.4.50

  References: #10207

• [schema] [bug] Modified the rendering of the Oracle only Identity.order parameter that's part of both Sequence and Identity to only take place for the Oracle backend, and not other backends such as that of PostgreSQL. A future release will rename the Identity.order, Sequence.order and Identity.on_null parameters to Oracle-specific names, deprecating the old names, these parameters only apply to Oracle.

  This change is also backported to: 1.4.50

  References: #10207

typing

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

  References: #10288

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

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

  References: #10264, #9284

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

  References: #9878

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

  References: #10353

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

  References: #10337

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

  References: #10131

postgresql

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

  References: #10226

misc

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

  References: #10321

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

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

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

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

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

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

2.0.14

Released: May 18, 2023

orm

• [orm] [bug] Modified the JoinedLoader implementation to use a simpler approach in one particular area where it previously used a cached structure that would be shared among threads. The rationale is to avoid a potential race condition which is suspected of being the cause of a particular crash that's been reported multiple times. The cached structure in question is still ultimately "cached" via the compiled SQL cache, so a performance degradation is not anticipated.

  References: #9777

• [orm] [bug] [regression] Fixed regression where use of _dml.update() or _dml.delete() within a _sql.CTE construct, then used in a _sql.select(), would raise a CompileError as a result of ORM related rules for performing ORM-level update/delete statements.

  References: #9767

• [orm] [bug] Fixed issue in new ORM Annotated Declarative where using a _schema.ForeignKey (or other column-level constraint) inside of _orm.mapped_column() which is then copied out to models via pep-593 Annotated would apply duplicates of each constraint to the _schema.Column as produced in the target _schema.Table, leading to incorrect CREATE TABLE DDL as well as migration directives under Alembic.

  References: #9766

• [orm] [bug] Fixed issue where using additional relationship criteria with the _orm.joinedload() loader option, where the additional criteria itself contained correlated subqueries that referred to the joined entities and therefore also required "adaption" to aliased entities, would be excluded from this adaption, producing the wrong ON clause for the joinedload.

  References: #9779

sql

• [sql] [usecase] Generalized the MSSQL _sql.try_cast() function into the sqlalchemy. import namespace so that it may be implemented by third party dialects as well. Within SQLAlchemy, the _sql.try_cast() function remains a SQL Server-only construct that will raise CompileError if used with backends that don't support it.

  _sql.try_cast() implements a CAST where un-castable conversions are returned as NULL, instead of raising an error. Theoretically, the construct could be implemented by third party dialects for Google BigQuery, DuckDB, and Snowflake, and possibly others.

  Pull request courtesy Nick Crews.

  References: #9752

• [sql] [bug] Fixed issue in _sql.values() construct where an internal compilation error would occur if the construct were used inside of a scalar subquery.

  References: #9772

postgresql

• [postgresql] [bug] Fixed apparently very old issue where the _postgresql.ENUM.create_type parameter, when set to its non-default of False, would not be propagated when the _schema.Column which it's a part of were copied, as is common when using ORM Declarative mixins.

  References: #9773

tests

• [tests] [bug] [pypy] Fixed test that relied on the sys.getsizeof() function to not run on pypy, where this function appears to have different behavior than it does on cpython.

  References: #9789