Sqlalchemy

:released: March 27, 2025

.. change::
:tags: usecase, postgresql
:tickets: 11595

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.

.. seealso::

:ref:`postgresql_constraint_options`

.. change::
:tags: bug, orm
:tickets: 12329

Fixed regression which occurred as of 2.0.37 where the checked
:class:`.ArgumentError` that's raised when an inappropriate type or object
is used inside of a :class:`.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 :class:`.Mapped` construct, these scenarios have all been unified
under a new subclass of :class:`.ArgumentError` called
:class:`.MappedAnnotationError`, to provide some continuity between these
different scenarios, even though specific messaging remains distinct.

.. change::
:tags: bug, mysql
:tickets: 12332

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.

.. change::
:tags: bug, sql
:tickets: 12363

Fixed issue in :class:`.CTE` constructs involving multiple DDL
:class:`_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.


.. change::
:tags: bug, sqlite
:tickets: 12425

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.

.. change::
:tags: bug, mysql
:tickets: 12425

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.


.. change::
:tags: usecase, postgresql
:tickets: 12432

When building a PostgreSQL ``ARRAY`` literal using
:class:`_postgresql.array` with an empty ``clauses`` argument, the
:paramref:`_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.

.. change::
:tags: sql, usecase
:tickets: 12450

Implemented support for the GROUPS frame specification in window functions
by adding :paramref:`_sql.over.groups` option to :func:`_sql.over`
and :meth:`.FunctionElement.over`. Pull request courtesy Kaan Dikmen.

.. change::
:tags: bug, sql
:tickets: 12451

Fixed regression caused by :ticket:`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.

.. change::
:tags: bug, asyncio
:tickets: 12471

Fixed issue where :meth:`.AsyncSession.get_transaction` and
:meth:`.AsyncSession.get_nested_transaction` would fail with
``NotImplementedError`` if the "proxy transaction" used by
:class:`.AsyncSession` were garbage collected and needed regeneration.

.. change::
:tags: bug, orm
:tickets: 12473

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.

.. changelog::

:released: March 11, 2025

.. change::
:tags: bug, postgresql
:tickets: 11751

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.

.. change::
:tags: bug, postgresql

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
:meth:`_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.

.. change::
:tags: typing, usecase
:tickets: 11922

Support generic types for compound selects (:func:`_sql.union`,
:func:`_sql.union_all`, :meth:`_sql.Select.union`,
:meth:`_sql.Select.union_all`, etc) returning the type of the first select.
Pull request courtesy of Mingyu Park.

.. change::
:tags: bug, postgresql
:tickets: 12060

Fixed issue in PostgreSQL network types :class:`_postgresql.INET`,
:class:`_postgresql.CIDR`, :class:`_postgresql.MACADDR`,
:class:`_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.

.. change::
:tags: bug, orm
:tickets: 12326

Fixed bug where using DML returning such as :meth:`.Insert.returning` with
an ORM model that has :func:`_orm.column_property` constructs that contain
subqueries would fail with an internal error.

.. change::
:tags: bug, orm
:tickets: 12328

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.

.. change::
:tags: bug, asyncio
:tickets: 12338

Fixed bug where :meth:`_asyncio.AsyncResult.scalar`,
:meth:`_asyncio.AsyncResult.scalar_one_or_none`, and
:meth:`_asyncio.AsyncResult.scalar_one` would raise an ``AttributeError``
due to a missing internal attribute. Pull request courtesy Allen Ho.

.. change::
:tags: bug, orm
:tickets: 12357

Fixed issue where the "is ORM" flag of a :func:`.select` or other ORM
statement would not be propagated to the ORM :class:`.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.

.. change::
:tags: bug, orm
:tickets: 12364

Fixed issue where using :func:`_orm.aliased` around a :class:`.CTE`
construct could cause inappropriate "duplicate CTE" errors in cases where
that aliased construct appeared multiple times in a single statement.

.. change::
:tags: bug, sqlite
:tickets: 12368

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

.. change::
:tags: bug, sql
:tickets: 12382

Added new parameters :paramref:`.AddConstraint.isolate_from_table` and
:paramref:`.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.

.. change::
:tags: bug, postgresql
:tickets: 12417

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

.. changelog::

:released: February 6, 2025

.. change::
:tags: postgresql, usecase, asyncio
:tickets: 12077

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.

.. change::
:tags: bug, dml, mariadb, mysql
:tickets: 12117

Fixed a bug where the MySQL statement compiler would not properly compile
statements where :meth:`_mysql.Insert.on_duplicate_key_update` was passed
values that included ORM-mapped attributes (e.g.
:class:`InstrumentedAttribute` objects) as keys. Pull request courtesy of
mingyu.

.. change::
:tags: bug, postgresql
:tickets: 12159

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

.. change::
:tags: bug, sqlite, aiosqlite, asyncio, pool
:tickets: 12285

Changed default connection pool used by the ``aiosqlite`` dialect
from :class:`.NullPool` to :class:`.AsyncAdaptedQueuePool`; this change
should have been made when 2.0 was first released as the ``pysqlite``
dialect was similarly changed to use :class:`.QueuePool` as detailed
in :ref:`change_7490`.


.. change::
:tags: bug, engine
:tickets: 12289

Fixed event-related issue where invoking :meth:`.Engine.execution_options`
on a :class:`.Engine` multiple times while making use of event-registering
parameters such as ``isolation_level`` would lead to internal errors
involving event registration.

.. change::
:tags: bug, sql
:tickets: 12302

Reorganized the internals by which the ``.c`` collection on a
:class:`.FromClause` gets generated so that it is resilient against the
collection being accessed in concurrent fashion. An example is creating a
:class:`.Alias` or :class:`.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.

.. change::
:tags: bug, sql
:tickets: 12314

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.


.. changelog::

:released: January 9, 2025

.. change::
:tags: usecase, mariadb
:tickets: 10720

Added sql types ``INET4`` and ``INET6`` in the MariaDB dialect. Pull
request courtesy Adam Žurek.

.. change::
:tags: bug, orm
:tickets: 11370

Fixed issue regarding ``Union`` types that would be present in the
:paramref:`_orm.registry.type_annotation_map` of a :class:`_orm.registry`
or declarative base class, where a :class:`.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
:paramref:`_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 :paramref:`_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.

.. change::
:tags: bug, postgresql
:tickets: 11724

Fixes issue in :meth:`.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.

.. change::
:tags: usecase, mysql, mariadb
:tickets: 11764

Added support for the ``LIMIT`` clause with ``DELETE`` for the MySQL and
MariaDB dialects, to complement the already present option for
``UPDATE``. The :meth:`.Delete.with_dialect_options` method of the
:func:`.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.


.. change::
:tags: bug, mysql, mariadb

Added logic to ensure that the ``mysql_limit`` and ``mariadb_limit``
parameters of :meth:`.Update.with_dialect_options` and
:meth:`.Delete.with_dialect_options` when compiled to string will only
compile if the parameter is passed as an integer; a ``ValueError`` is
raised otherwise.

.. change::
:tags: bug, orm
:tickets: 11944

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

.. change::
:tags: bug, orm
:tickets: 11955

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 :class:`.Mapped`. This change also revises the approach added in
:ticket:`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
:ref:`orm_declarative_mapped_column_type_map` section of the documentation.

.. change::
:tags: feature, oracle
:tickets: 12016

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.

.. change::
:tags: orm, bug
:tickets: 12019

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 :meth:`.ColumnOperators.in_` to no longer
be accepted.

.. change::
:tags: oracle, usecase
:tickets: 12032

Use the connection attribute ``max_identifier_length`` available
in oracledb since version 2.5 when determining the identifier length
in the Oracle dialect.

.. change::
:tags: bug, sql
:tickets: 12084

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.


.. change::
:tags: usecase, postgresql
:tickets: 12093

The :class:`_postgresql.Range` type now supports
:meth:`_postgresql.Range.__contains__`. Pull request courtesy of Frazer
McLean.

.. change::
:tags: bug, oracle
:tickets: 12100

Fixed compilation of ``TABLE`` function when used in a ``FROM`` clause in
Oracle Database dialect.

.. change::
:tags: bug, oracle
:tickets: 12150

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.


.. change::
:tags: bug, postgresql
:tickets: 12170

Fixed issue where creating a table with a primary column of
:class:`_sql.SmallInteger` and using the asyncpg driver would result in
the type being compiled to ``SERIAL`` rather than ``SMALLSERIAL``.

.. change::
:tags: bug, orm
:tickets: 12207

Fixed issues in type handling within the
:paramref:`_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.

.. change::
:tags: bug, orm
:tickets: 12216

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


.. change::
:tags: bug, postgresql
:tickets: 12220

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 :meth:`.Connection.exec_driver_sql`. Pull request
courtesy Andrew Jackson.


.. change::
:tags: usecase, sqlite
:tickets: 7398

Added SQLite table option to enable ``STRICT`` tables. Pull request
courtesy of Guilherme Crocetti.

.. changelog::

:released: October 15, 2024

.. change::
:tags: bug, schema
:tickets: 11317

Fixed bug where SQL functions passed to
:paramref:`_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.

.. change::
:tags: bug, orm
:tickets: 11912

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.

.. change::
:tags: bug, orm
:tickets: 11917

Continuing from :ticket:`11912`, columns marked with
:paramref:`.mapped_column.onupdate`,
:paramref:`.mapped_column.server_onupdate`, or :class:`.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``.

.. change::
:tags: usecase, orm
:tickets: 11923

Added new parameter :paramref:`_orm.mapped_column.hash` to ORM constructs
such as :meth:`_orm.mapped_column`, :meth:`_orm.relationship`, etc.,
which is interpreted for ORM Native Dataclasses in the same way as other
dataclass-specific field parameters.

.. change::
:tags: bug, postgresql, reflection
:tickets: 11961

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.

.. change::
:tags: bug, orm
:tickets: 11965

Fixed regression caused by fixes to joined eager loading in :ticket:`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.


.. change::
:tags: orm, bug
:tickets: 11973

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.

.. change::
:tags: mysql, performance
:tickets: 11975

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.

.. change::
:tags: usecase, sql
:tickets: 11978

Datatypes that are binary based such as :class:`.VARBINARY` will resolve to
:class:`.LargeBinary` when the :meth:`.TypeEngine.as_generic()` method is
called.

.. change::
:tags: postgresql, bug
:tickets: 11994

The :class:`.postgresql.JSON` and :class:`.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.

.. change::
:tags: bug, orm
:tickets: 11995

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
:paramref:`.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.


.. change::
:tags: bug, orm
:tickets: 11997

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.

.. change::
:tags: bug, sql, regression
:tickets: 12002

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

.. changelog::

:released: September 16, 2024

.. change::
:tags: bug, orm, typing
:tickets: 11820

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.

.. change::
:tags: bug, sqlite, regression
:tickets: 11840

The changes made for SQLite CHECK constraint reflection in versions 2.0.33
and 2.0.34 , :ticket:`11832` and :ticket:`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.

.. change::
:tags: orm, bug
:tickets: 11849

Fixed issue in ORM evaluator where two datatypes being evaluated with the
SQL concatenator operator would not be checked for
:class:`.UnevaluatableError` based on their datatype; this missed the case
of :class:`_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
:class:`.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.

.. change::
:tags: bug, orm
:tickets: 11853

An warning is emitted if :func:`_orm.joinedload` or
:func:`_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.

.. change::
:tags: bug, orm
:tickets: 11855

Fixed issue where using loader options such as :func:`_orm.selectinload`
with additional criteria in combination with ORM DML such as
:func:`_sql.insert` with RETURNING would not correctly set up internal
contexts required for caching to work correctly, leading to incorrect
results.

.. change::
:tags: bug, mysql
:tickets: 11870

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.


.. changelog::