Sqlalchemy

:released: May 5, 2024

.. change::
:tags: bug, typing, regression
:tickets: 11200

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

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

Fixed issue in the
:paramref:`_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
:meth:`_orm.Session.connection` to change the option on the connection,
since the BEGIN logging message would already have been emitted.

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

Added new attribute :attr:`_orm.ORMExecuteState.is_from_statement` to
detect statements created using :meth:`_sql.Select.from_statement`, and
enhanced ``FromStatement`` to set :attr:`_orm.ORMExecuteState.is_select`,
:attr:`_orm.ORMExecuteState.is_insert`,
:attr:`_orm.ORMExecuteState.is_update`, and
:attr:`_orm.ORMExecuteState.is_delete` according to the element that is
sent to the :meth:`_sql.Select.from_statement` method itself.

.. change::
:tags: bug, test
:tickets: 11268

Ensure the ``PYTHONPATH`` variable is properly initialized when
using ``subprocess.run`` in the tests.

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

Fixed issue in :func:`_orm.selectin_polymorphic` loader option where
attributes defined with :func:`_orm.composite` on a superclass would cause
an internal exception on load.


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

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



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

Fixed issue in ORM Annotated Declarative where typing issue where literals
defined using :pep:`695` type aliases would not work with inference of
:class:`.Enum` datatypes. Pull request courtesy of Alc-Alc.

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

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



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

Fixed issue in :func:`_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.

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

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

.. change::
:tags: bug, installation
:tickets: 11334

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.

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

Fixed issue where attribute key names in :class:`_orm.Bundle` would not be
correct when using ORM enabled :class:`_sql.select` vs.
:class:`_orm.Query`, when the statement contained duplicate column names.

.. change::
:tags: bug, typing

Fixed issue in typing for :class:`_orm.Bundle` where creating a nested
:class:`_orm.Bundle` structure were not allowed.

.. changelog::

:released: March 23, 2024

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

Fixed Declarative issue where typing a relationship using
:class:`_orm.Relationship` rather than :class:`_orm.Mapped` would
inadvertently pull in the "dynamic" relationship loader strategy for that
attribute.

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

The PostgreSQL dialect now returns :class:`_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.

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

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.

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

Fixed issue in ORM annotated declarative where using
:func:`_orm.mapped_column()` with an :paramref:`_orm.mapped_column.index`
or :paramref:`_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
:func:`_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.

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

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 :class:`_orm.Mapped` typing container.

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

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

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

Made a change to the adjustment made in version 2.0.10 for :ticket:`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.

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

Fixed regression from version 2.0.28 caused by the fix for :ticket:`11085`
where the newer method of adjusting post-cache bound parameter values would
interefere with the implementation for the :func:`_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.

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

Fixed regression from the 1.4 series where the refactor of the
:meth:`_types.TypeEngine.with_variant` method introduced at
:ref:`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
:class:`.Enum` and :class:`_types.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.

.. change::
:tags: bug, tests
:tickets: 11187

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.


.. changelog::

:released: March 4, 2024

.. change::
:tags: engine, usecase
:tickets: 10974

Added new core execution option
:paramref:`_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 :attr:`_engine.CursorResult.rowcount` attribute from the
:class:`_engine.CursorResult`. This allows the rowcount to be accessed for
statements such as INSERT and SELECT, to the degree supported by the DBAPI
in use. The :ref:`engine_insertmanyvalues` also supports this option and
will ensure :attr:`_engine.CursorResult.rowcount` is correctly set for a
bulk INSERT of rows when set.

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

Fixed regression caused by :ticket:`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
:meth:`_sql.Select.join` expression, leading to an invalid query.

.. change::
:tags: bug, orm, performance, regression
:tickets: 11085

Adjusted the fix made in :ticket:`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 :func:`_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.

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

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

.. seealso::

:ref:`pool_api`


.. change::
:tags: change, tests

pytest support in the tox.ini file has been updated to support pytest 8.1.

.. changelog::

:released: February 13, 2024

.. change::
:tags: bug, postgresql, regression
:tickets: 11005

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


.. changelog::

:released: February 11, 2024

.. change::
:tags: usecase, postgresql, reflection
:tickets: 10777

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.

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

Fixed issues in :func:`_sql.case` where the logic for determining the
type of the expression could result in :class:`.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.

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

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.

.. change::
:tags: bug, regression, postgresql
:tickets: 10863

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


.. change::
:tags: oracle, bug, performance
:tickets: 10877

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.

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

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.

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

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.

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

Fixed issue where it was not possible to use a type (such as an enum)
within a :class:`_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
:class:`_orm.Mapped` may also refer to the class name itself, if used as a
string or with future annotations mode.

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

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.

.. seealso::

:ref:`postgresql_table_options`

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

Fixed regression in history_meta example where the use of
:meth:`_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.


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

Fixed issue where using :meth:`_orm.Session.delete` along with the
:paramref:`_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
:paramref:`_orm.relationship.post_update` on the object. The issue is
similar to :ticket:`10800` just fixed in version 2.0.25 for the case of
updates alone.

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

Fixed issue where an assertion within the implementation for
:func:`_orm.with_expression` would raise if a SQL expression that was not
cacheable were used; this was a 2.0 regression since 1.4.

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

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

.. change::
:tags: postgresql, usecase

Differentiate between INT4 and INT8 ranges and multi-ranges types when
inferring the database type from a :class:`_postgresql.Range` or
:class:`_postgresql.MultiRange` instance, preferring INT4 if the values
fit into it.

.. change::
:tags: bug, typing

Fixed the type signature for the :meth:`.PoolEvents.checkin` event to
indicate that the given :class:`.DBAPIConnection` argument may be ``None``
in the case where the connection has been invalidated.

.. change::
:tags: bug, examples

Fixed the performance example scripts in examples/performance to mostly
work with the Oracle database, by adding the :class:`.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.


.. change::
:tags: bug, mssql

Fixed an issue regarding the use of the :class:`.Uuid` datatype with the
:paramref:`.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.


.. change::
:tags: bug, postgresql

Fixed an issue regarding the use of the :class:`.Uuid` datatype with the
:paramref:`.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.

.. changelog::

:released: January 2, 2024

.. change::
:tags: oracle, asyncio
:tickets: 10679

Added support for :ref:`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
:meth:`_asyncio.AsyncConnection.stream`. Improved support is planned for
the 2.1 release of SQLAlchemy.

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

Fixed issue where when making use of the
:paramref:`_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.

.. change::
:tags: bug, typing
:tickets: 10801, 10818

Fixed regressions caused by typing added to the ``sqlalchemy.sql.functions``
module in version 2.0.24, as part of :ticket:`6810`:

* Further enhancements to pep-484 typing to allow SQL functions from
:attr:`_sql.func` derived elements to work more effectively with ORM-mapped
attributes (:ticket:`10801`)

* Fixed the argument types passed to functions so that literal expressions
like strings and ints are again interpreted correctly (:ticket:`10818`)


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

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


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

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.

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

Improved compilation of :func:`_sql.any_` / :func:`_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.

.. changelog::