Sqlalchemy

:released: June 28, 2021

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

Fixed regression in ORM regarding an internal reconstitution step for the
:func:`_orm.with_polymorphic` construct, when the user-facing object is
garbage collected as the query is processed. The reconstitution was not
ensuring the sub-entities for the "polymorphic" case were handled, leading
to an ``AttributeError``.

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

Add a impl parameter to :class:`_types.PickleType` constructor, allowing
any arbitrary type to be used in place of the default implementation of
:class:`_types.LargeBinary`. Pull request courtesy jason3gb.

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

Fixed an issue in the C extension for the :class:`_result.Row` class which
could lead to a memory leak in the unlikely case of a :class:`_result.Row`
object which referred to an ORM object that then was mutated to refer back
to the ``Row`` itself, creating a cycle. The Python C APIs for tracking GC
cycles has been added to the native :class:`_result.Row` implementation to
accommodate for this case.


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

Fixed old issue where a :func:`_sql.select()` made against the token "*",
which then yielded exactly one column, would fail to correctly organize the
``cursor.description`` column name into the keys of the result object.



.. change::
:tags: usecase, mysql
:tickets: 6659

Made a small adjustment in the table reflection feature of the MySQL
dialect to accommodate for alternate MySQL-oriented databases such as TiDB
which include their own "comment" directives at the end of a constraint
directive within "CREATE TABLE" where the format doesn't have the
additional space character after the comment, in this case the TiDB
"clustered index" feature. Pull request courtesy Daniël van Eeden.

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

Fixed issue where passing ``None`` for the value of
:paramref:`_schema.Table.prefixes` would not store an empty list, but
rather the constant ``None``, which may be unexpected by third party
dialects. The issue is revealed by a usage in recent versions of Alembic
that are passing ``None`` for this value. Pull request courtesy Kai
Mueller.

.. change::
:tags: bug, regression, ext
:tickets: 6679

Fixed regression in :mod:`sqlalchemy.ext.automap` extension such that the
use case of creating an explicit mapped class to a table that is also the
:paramref:`_orm.relationship.secondary` element of a
:func:`_orm.relationship` that automap will be generating would emit
the "overlaps" warnings introduced in 1.4 and discussed at
:ref:`error_qzyx`. While generating this case from automap is still
subject to the same caveats mentioned in the 'overlaps' warning,
since automap is primarily intended for more ad-hoc
use cases, the condition triggering the warning is disabled when a
many-to-many relationship with this specific pattern is
generated.


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

Adjusted :meth:`_orm.Query.union` and similar set operations to be
correctly compatible with the new capabilities just added in
:ticket:`6661`, with SQLAlchemy 1.4.19, such that the SELECT statements
rendered as elements of the UNION or other set operation will include
directly mapped columns that are mapped as deferred; this both fixes a
regression involving unions with multiple levels of nesting that would
produce a column mismatch, and also allows the :func:`_orm.undefer` option
to be used at the top level of such a :class:`_orm.Query` without having to
apply the option to each of the elements within the UNION.

.. change::
:tags: bug, sql, orm
:tickets: 6668

Fixed the class hierarchy for the :class:`_schema.Sequence` and the more
general :class:`_schema.DefaultGenerator` base, as these are "executable"
as statements they need to include :class:`_sql.Executable` in their
hierarchy, not just :class:`_roles.StatementRole` as was applied
arbitrarily to :class:`_schema.Sequence` previously. The fix allows
:class:`_schema.Sequence` to work in all ``.execute()`` methods including
with :meth:`_orm.Session.execute` which was not working in the case that a
:meth:`_orm.SessionEvents.do_orm_execute` handler was also established.


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

Adjusted the check in the mapper for a callable object that is used as a
``validates`` validator function or a ``reconstructor`` reconstruction
function, to check for "callable" more liberally such as to accommodate
objects based on fundamental attributes like ``__func__`` and
``__call__``, rather than testing for ``MethodType`` / ``FunctionType``,
allowing things like cython functions to work properly. Pull request
courtesy Miłosz Stypiński.

.. changelog::

:released: June 22, 2021

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

Fixed bug where the "schema_translate_map" feature would fail to function
correctly in conjunction with an INSERT into a table that has an IDENTITY
column, where the value of the IDENTITY column were specified in the values
of the INSERT thus triggering SQLAlchemy's feature of setting IDENTITY
INSERT to "on"; it's in this directive where the schema translate map would
fail to be honored.


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

Fixed issue in CTE constructs mostly relevant to ORM use cases where a
recursive CTE against "anonymous" labels such as those seen in ORM
``column_property()`` mappings would render in the
``WITH RECURSIVE xyz(...)`` section as their raw internal label and not a
cleanly anonymized name.

.. change::
:tags: mssql, change
:tickets: 6503, 6253

Made improvements to the server version regexp used by the pymssql dialect
to prevent a regexp overflow in case of an invalid version string.

.. change::
:tags: bug, orm, regression
:tickets: 6503, 6253

Fixed further regressions in the same area as that of :ticket:`6052` where
loader options as well as invocations of methods like
:meth:`_orm.Query.join` would fail if the left side of the statement for
which the option/join depends upon were replaced by using the
:meth:`_orm.Query.with_entities` method, or when using 2.0 style queries
when using the :meth:`_sql.Select.with_only_columns` method. A new set of
state has been added to the objects which tracks the "left" entities that
the options / join were made against which is memoized when the lead
entities are changed.

.. change::
:tags: bug, asyncio, postgresql
:tickets: 6652

Fixed bug in asyncio implementation where the greenlet adaptation system
failed to propagate ``BaseException`` subclasses, most notably including
``asyncio.CancelledError``, to the exception handling logic used by the
engine to invalidate and clean up the connection, thus preventing
connections from being correctly disposed when a task was cancelled.



.. change::
:tags: usecase, asyncio
:tickets: 6583

Implemented :class:`_asyncio.async_scoped_session` to address some
asyncio-related incompatibilities between :class:`_orm.scoped_session` and
:class:`_asyncio.AsyncSession`, in which some methods (notably the
:meth:`_asyncio.async_scoped_session.remove` method) should be used with
the ``await`` keyword.

.. seealso::

:ref:`asyncio_scoped_session`

.. change::
:tags: usecase, mysql
:tickets: 6132

Added new construct :class:`_mysql.match`, which provides for the full
range of MySQL's MATCH operator including multiple column support and
modifiers. Pull request courtesy Anton Kovalevich.

.. seealso::

:class:`_mysql.match`

.. change::
:tags: bug, postgresql, oracle
:tickets: 6649

Fixed issue where the ``INTERVAL`` datatype on PostgreSQL and Oracle would
produce an ``AttributeError`` when used in the context of a comparison
operation against a ``timedelta()`` object. Pull request courtesy
MajorDallas.

.. change::
:tags: bug, mypy
:tickets: 6476

Fixed issue in mypy plugin where class info for a custom declarative base
would not be handled correctly on a cached mypy pass, leading to an
AssertionError being raised.

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

Refined the behavior of ORM subquery rendering with regards to deferred
columns and column properties to be more compatible with that of 1.3 while
also providing for 1.4's newer features. As a subquery in 1.4 does not make
use of loader options, including :func:`_orm.undefer`, a subquery that is
against an ORM entity with deferred attributes will now render those
deferred attributes that refer directly to mapped table columns, as these
are needed in the outer SELECT if that outer SELECT makes use of these
columns; however a deferred attribute that refers to a composed SQL
expression as we normally do with :func:`_orm.column_property` will not be
part of the subquery, as these can be selected explicitly if needed in the
subquery. If the entity is being SELECTed from this subquery, the column
expression can still render on "the outside" in terms of the derived
subquery columns. This produces essentially the same behavior as when
working with 1.3. However in this case the fix has to also make sure that
the ``.selected_columns`` collection of an ORM-enabled :func:`_sql.select`
also follows these rules, which in particular allows recursive CTEs to
render correctly in this scenario, which were previously failing to render
correctly due to this issue.

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

Fixed issue where the pool "pre ping" feature would implicitly start a
transaction, which would then interfere with custom transactional flags
such as PostgreSQL's "read only" mode when used with the psycopg2 driver.


.. changelog::

:released: June 10, 2021

.. change::
:tags: bug, orm
:tickets: 6072, 6487

Clarified the current purpose of the
:paramref:`_orm.relationship.bake_queries` flag, which in 1.4 is to enable
or disable "lambda caching" of statements within the "lazyload" and
"selectinload" loader strategies; this is separate from the more
foundational SQL query cache that is used for most statements.
Additionally, the lazy loader no longer uses its own cache for many-to-one
SQL queries, which was an implementation quirk that doesn't exist for any
other loader scenario. Finally, the "lru cache" warning that the lazyloader
and selectinloader strategies could emit when handling a wide array of
class/relationship combinations has been removed; based on analysis of some
end-user cases, this warning doesn't suggest any significant issue. While
setting ``bake_queries=False`` for such a relationship will remove this
cache from being used, there's no particular performance gain in this case
as using no caching vs. using a cache that needs to refresh often likely
still wins out on the caching being used side.


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

Fixed an issue that presented itself when using the :class:`_pool.NullPool`
or the :class:`_pool.StaticPool` with an async engine. This mostly affected
the aiosqlite dialect.

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

The fix for pysqlcipher released in version 1.4.3 :ticket:`5848` was
unfortunately non-working, in that the new ``on_connect_url`` hook was
erroneously not receiving a ``URL`` object under normal usage of
:func:`_sa.create_engine` and instead received a string that was unhandled;
the test suite failed to fully set up the actual conditions under which
this hook is called. This has been fixed.

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

Fixed regression where using the PostgreSQL "INSERT..ON CONFLICT" structure
would fail to work with the psycopg2 driver if it were used in an
"executemany" context along with bound parameters in the "SET" clause, due
to the implicit use of the psycopg2 fast execution helpers which are not
appropriate for this style of INSERT statement; as these helpers are the
default in 1.4 this is effectively a regression. Additional checks to
exclude this kind of statement from that particular extension have been
added.

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

Adjusted the means by which classes such as :class:`_orm.scoped_session`
and :class:`_asyncio.AsyncSession` are generated from the base
:class:`_orm.Session` class, such that custom :class:`_orm.Session`
subclasses such as that used by Flask-SQLAlchemy don't need to implement
positional arguments when they call into the superclass method, and can
continue using the same argument styles as in previous releases.

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

Fixed issue where query production for joinedload against a complex left
hand side involving joined-table inheritance could fail to produce a
correct query, due to a clause adaption issue.

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

Fixed regression involving how the ORM would resolve a given mapped column
to a result row, where under cases such as joined eager loading, a slightly
more expensive "fallback" could take place to set up this resolution due to
some logic that was removed since 1.3. The issue could also cause
deprecation warnings involving column resolution to be emitted when using a
1.4 style query with joined eager loading.

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

Fixed issue in experimental "select ORM objects from INSERT/UPDATE" use
case where an error was raised if the statement were against a
single-table-inheritance subclass.

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

Added ``asyncio.exceptions.TimeoutError``,
``asyncio.exceptions.CancelledError`` as so-called "exit exceptions", a
class of exceptions that include things like ``GreenletExit`` and
``KeyboardInterrupt``, which are considered to be events that warrant
considering a DBAPI connection to be in an unusable state where it should
be recycled.

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

The warning that's emitted for :func:`_orm.relationship` when multiple
relationships would overlap with each other as far as foreign key
attributes written towards, now includes the specific "overlaps" argument
to use for each warning in order to silence the warning without changing
the mapping.

.. change::
:tags: usecase, asyncio
:tickets: 6319

Implemented a new registry architecture that allows the ``Async`` version
of an object, like ``AsyncSession``, ``AsyncConnection``, etc., to be
locatable given the proxied "sync" object, i.e. ``Session``,
``Connection``. Previously, to the degree such lookup functions were used,
an ``Async`` object would be re-created each time, which was less than
ideal as the identity and state of the "async" object would not be
preserved across calls.

From there, new helper functions :func:`_asyncio.async_object_session`,
:func:`_asyncio.async_session` as well as a new :class:`_orm.InstanceState`
attribute :attr:`_orm.InstanceState.async_session` have been added, which
are used to retrieve the original :class:`_asyncio.AsyncSession` associated
with an ORM mapped object, a :class:`_orm.Session` associated with an
:class:`_asyncio.AsyncSession`, and an :class:`_asyncio.AsyncSession`
associated with an :class:`_orm.InstanceState`, respectively.

This patch also implements new methods
:meth:`_asyncio.AsyncSession.in_nested_transaction`,
:meth:`_asyncio.AsyncSession.get_transaction`,
:meth:`_asyncio.AsyncSession.get_nested_transaction`.

.. changelog::

:released: May 29, 2021

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

Fixed regression caused by just-released performance fix mentioned in 6550
where a query.join() to a relationship could produce an AttributeError if
the query were made against non-ORM structures only, a fairly unusual
calling pattern.

.. changelog::

:released: May 28, 2021

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

Fixed issue where an ` sign in the database portion of a URL would not
be interpreted correctly if the URL also had a username:password section.


.. change::
:tags: bug, ext
:tickets: 6529

Fixed a deprecation warning that was emitted when using
:func:`_automap.automap_base` without passing an existing
``Base``.


.. change::
:tags: bug, pep484
:tickets: 6461

Remove pep484 types from the code.
Current effort is around the stub package, and having typing in
two places makes thing worse, since the types in the SQLAlchemy
source were usually outdated compared to the version in the stubs.

.. change::
:tags: usecase, mssql
:tickets: 6464

Implemented support for a :class:`_sql.CTE` construct to be used directly
as the target of a :func:`_sql.delete` construct, i.e. "WITH ... AS cte
DELETE FROM cte". This appears to be a useful feature of SQL Server.

.. change::
:tags: bug, general
:tickets: 6540, 6543

Resolved various deprecation warnings which were appearing as of Python
version 3.10.0b1.

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

Fixed issue when using :paramref:`_orm.relationship.cascade_backrefs`
parameter set to ``False``, which per :ref:`change_5150` is set to become
the standard behavior in SQLAlchemy 2.0, where adding the item to a
collection that uniquifies, such as ``set`` or ``dict`` would fail to fire
a cascade event if the object were already associated in that collection
via the backref. This fix represents a fundamental change in the collection
mechanics by introducing a new event state which can fire off for a
collection mutation even if there is no net change on the collection; the
action is now suited using a new event hook
:meth:`_orm.AttributeEvents.append_wo_mutation`.



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

Fixed regression involving clause adaption of labeled ORM compound
elements, such as single-table inheritance discriminator expressions with
conditionals or CASE expressions, which could cause aliased expressions
such as those used in ORM join / joinedload operations to not be adapted
correctly, such as referring to the wrong table in the ON clause in a join.

This change also improves a performance bump that was located within the
process of invoking :meth:`_sql.Select.join` given an ORM attribute
as a target.

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

Fixed regression where the full combination of joined inheritance, global
with_polymorphic, self-referential relationship and joined loading would
fail to be able to produce a query with the scope of lazy loads and object
refresh operations that also attempted to render the joined loader.

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

Fixed a long-standing issue with :class:`.URL` where query parameters
following the question mark would not be parsed correctly if the URL did
not contain a database portion with a backslash.

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

Fixed regression in dynamic loader strategy and :func:`_orm.relationship`
overall where the :paramref:`_orm.relationship.order_by` parameter were
stored as a mutable list, which could then be mutated when combined with
additional "order_by" methods used against the dynamic query object,
causing the ORDER BY criteria to continue to grow repetitively.

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

Enhanced the bind resolution rules for :meth:`_orm.Session.execute` so that
when a non-ORM statement such as an :func:`_sql.insert` construct
nonetheless is built against ORM objects, to the greatest degree possible
the ORM entity will be used to resolve the bind, such as for a
:class:`_orm.Session` that has a bind map set up on a common superclass
without specific mappers or tables named in the map.

.. change::
:tags: bug, regression, ext
:tickets: 6390

Fixed regression in the ``sqlalchemy.ext.instrumentation`` extension that
prevented instrumentation disposal from working completely. This fix
includes both a 1.4 regression fix as well as a fix for a related issue
that existed in 1.3 also. As part of this change, the
:class:`sqlalchemy.ext.instrumentation.InstrumentationManager` class now
has a new method ``unregister()``, which replaces the previous method
``dispose()``, which was not called as of version 1.4.


.. changelog::

:released: May 11, 2021

.. change::
:tags: bug, documentation, mysql
:tickets: 5397

Added support for the ``ssl_check_hostname=`` parameter in mysql connection
URIs and updated the mysql dialect documentation regarding secure
connections. Original pull request courtesy of Jerry Zhao.

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

Fixed additional regression caused by "eager loaders run on unexpire"
feature :ticket:`1763` where the feature would run for a
``contains_eager()`` eagerload option in the case that the
``contains_eager()`` were chained to an additional eager loader option,
which would then produce an incorrect query as the original query-bound
join criteria were no longer present.

.. change::
:tags: feature, general
:tickets: 6241

A new approach has been applied to the warnings system in SQLAlchemy to
accurately predict the appropriate stack level for each warning
dynamically. This allows evaluating the source of SQLAlchemy-generated
warnings and deprecation warnings to be more straightforward as the warning
will indicate the source line within end-user code, rather than from an
arbitrary level within SQLAlchemy's own source code.

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

Fixed issue in subquery loader strategy which prevented caching from
working correctly. This would have been seen in the logs as a "generated"
message instead of "cached" for all subqueryload SQL emitted, which by
saturating the cache with new keys would degrade overall performance; it
also would produce "LRU size alert" warnings.


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

Adjusted the logic added as part of :ticket:`6397` in 1.4.12 so that
internal mutation of the :class:`.BindParameter` object occurs within the
clause construction phase as it did before, rather than in the compilation
phase. In the latter case, the mutation still produced side effects against
the incoming construct and additionally could potentially interfere with
other internal mutation routines.

.. changelog::