Sqlalchemy

: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::

:released: September 4, 2024

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

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

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

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


.. changelog::

:released: September 3, 2024

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

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.



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

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.

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

Fixed regression in :meth:`_sql.Select.with_statement_hint` and others
where the generative behavior of the method stopped producing a copy of the
object.

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

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.


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

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.




.. change::
:tags: bug, typing
:tickets: 11782

Fixed typing issue with :meth:`_sql.Select.with_only_columns`.

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

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

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

Fixed bug where the ``metadata`` element of an ``Enum`` datatype would not
be transferred to the new :class:`.MetaData` object when the type had been
copied via a :meth:`.Table.to_metadata` operation, leading to inconsistent
behaviors within create/drop sequences.

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

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.



.. change::
:tags: change, general
:tickets: 11818

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.



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

Revising the asyncpg ``terminate()`` fix first made in :ticket:`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()``.

.. change::
:tags: bug, mssql
:tickets: 11822

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.

.. change::
:tags: bug, tests

Added missing ``array_type`` property to the testing suite
``SuiteRequirements`` class.

.. changelog::

:released: August 5, 2024

.. change::
:tags: bug, examples
:tickets: 10267

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.

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

Fixed table reflection on Oracle 10.2 and older where compression options
are not supported.

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

Added API support for server-side cursors for the oracledb async dialect,
allowing use of the :meth:`_asyncio.AsyncConnection.stream` and similar
stream methods.

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

Fixed issue where using the :meth:`_orm.Query.enable_eagerloads` and
:meth:`_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.

.. change::
:tags: orm
:tickets: 11163

Added a warning noting when an
:meth:`_engine.ConnectionEvents.engine_connect` event may be leaving
a transaction open, which can alter the behavior of a
:class:`_orm.Session` using such an engine as bind.
On SQLAlchemy 2.1 :paramref:`_orm.Session.join_transaction_mode` will
instead be ignored in all cases when the session bind is
an :class:`_engine.Engine`.

.. change::
:tags: bug, general, regression
:tickets: 11435

Restored legacy class names removed from
``sqlalalchemy.orm.collections.*``, including
:class:`_orm.MappedCollection`, :func:`_orm.mapped_collection`,
:func:`_orm.column_mapped_collection`,
:func:`_orm.attribute_mapped_collection`. Pull request courtesy Takashi
Kajinami.

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

Follow up of :ticket:`11471` to fix caching issue where using the
:meth:`.CompoundSelectState.add_cte` method of the
:class:`.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 :ticket:`11544`.

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

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


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

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
:meth:`_engine.Connection.begin_twophase` method.

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

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.

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

Fixed additional issues in the event system triggered by unpickling of a
:class:`.Enum` datatype, continuing from :ticket:`11365` and
:ticket:`11360`, where dynamically generated elements of the event
structure would not be present when unpickling in a new process.

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

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.

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

The :paramref:`_orm.aliased.name` parameter to :func:`_orm.aliased` may now
be combined with the :paramref:`_orm.aliased.flat` parameter, producing
per-table names based on a name-prefixed naming convention. Pull request
courtesy Eric Atkin.

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

Fixed issue where the :func:`_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 :func:`_sql.collate` construct now assigns its own type to explicitly
include the new collation, assuming it's a string type.

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

Fixed bug where the :meth:`.Operators.nulls_first()` and
:meth:`.Operators.nulls_last()` modifiers would not be treated the same way
as :meth:`.Operators.desc()` and :meth:`.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.

.. change::
:tags: bug, orm, regression
:tickets: 11625

Fixed regression appearing in 2.0.21 caused by :ticket:`10279` where using
a :func:`_sql.delete` or :func:`_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.

.. change::
:tags: bug, orm, regression
:tickets: 11661

Fixed regression from version 1.4 in
:meth:`_orm.Session.bulk_insert_mappings` where using the
:paramref:`_orm.Session.bulk_insert_mappings.return_defaults` parameter
would not populate the passed in dictionaries with newly generated primary
key values.


.. change::
:tags: bug, oracle, sqlite
:tickets: 11663

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.




.. change::
:tags: bug, typing

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.

.. changelog::

:released: June 18, 2024

.. change::
:tags: usecase, reflection, mysql
:tickets: 11285

Added missing foreign key reflection option ``SET DEFAULT``
in the MySQL and MariaDB dialects.
Pull request courtesy of Quentin Roche.

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

Added missing parameter :paramref:`_orm.with_polymorphic.name` that
allows specifying the name of returned :class:`_orm.AliasedClass`.

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

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

.. change::
:tags: schema, usecase
:tickets: 11374

Added :paramref:`_schema.Column.insert_default` as an alias of
:paramref:`_schema.Column.default` for compatibility with
:func:`_orm.mapped_column`.

.. change::
:tags: bug, general
:tickets: 11417

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.

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

Fixed issue when serializing an :func:`_sql.over` clause with
unbounded range or rows.

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

Added missing methods :meth:`_sql.FunctionFilter.within_group`
and :meth:`_sql.WithinGroup.filter`

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

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

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

Fixed issue where the :func:`_orm.selectinload` and
:func:`_orm.subqueryload` loader options would fail to take effect when
made against an inherited subclass that itself included a subclass-specific
:paramref:`_orm.Mapper.with_polymorphic` setting.

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

Fixed very old issue involving the :paramref:`_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.

.. change::
:tags: bug, orm, regression
:tickets: 11509

Fixed bug in ORM Declarative where the ``__table__`` directive could not be
declared as a class function with :func:`_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.

.. changelog::