Sqlalchemy

:released: April 15, 2021

.. change::
:tags: change, mypy

Updated Mypy plugin to only use the public plugin interface of the
semantic analyzer.

.. change::
:tags: bug, mssql, regression
:tickets: 6265

Fixed an additional regression in the same area as that of :ticket:`6173`,
:ticket:`6184`, where using a value of 0 for OFFSET in conjunction with
LIMIT with SQL Server would create a statement using "TOP", as was the
behavior in 1.3, however due to caching would then fail to respond
accordingly to other values of OFFSET. If the "0" wasn't first, then it
would be fine. For the fix, the "TOP" syntax is now only emitted if the
OFFSET value is omitted entirely, that is, :meth:`_sql.Select.offset` is
not used. Note that this change now requires that if the "with_ties" or
"percent" modifiers are used, the statement can't specify an OFFSET of
zero, it now needs to be omitted entirely.

.. change::
:tags: bug, engine

The :meth:`_engine.Dialect.has_table` method now raises an informative
exception if a non-Connection is passed to it, as this incorrect behavior
seems to be common. This method is not intended for external use outside
of a dialect. Please use the :meth:`.Inspector.has_table` method
or for cross-compatibility with older SQLAlchemy versions, the
:meth:`_engine.Engine.has_table` method.


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

Fixed regression where the :class:`_sql.BindParameter` object would not
properly render for an IN expression (i.e. using the "post compile" feature
in 1.4) if the object were copied from either an internal cloning
operation, or from a pickle operation, and the parameter name contained
spaces or other special characters.

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

Revised the fix for ``OrderingList`` from version 1.4.7 which was testing
against the incorrect API.

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

Fix typo that prevented setting the ``bind`` attribute of an
:class:`_asyncio.AsyncSession` to the correct value.

.. change::
:tags: feature, sql
:tickets: 3314

The tuple returned by :attr:`.CursorResult.inserted_primary_key` is now a
:class:`_result.Row` object with a named tuple interface on top of the
existing tuple interface.




.. change::
:tags: bug, regression, sql, sqlite
:tickets: 6254

Fixed regression where the introduction of the INSERT syntax "INSERT...
VALUES (DEFAULT)" was not supported on some backends that do however
support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are
now each individually supported or non-supported for each dialect, for
example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES".
Support for Oracle has also been enabled.

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

Fixed a cache leak involving the :func:`_orm.with_expression` loader
option, where the given SQL expression would not be correctly considered as
part of the cache key.

Additionally, fixed regression involving the corresponding
:func:`_orm.query_expression` feature. While the bug technically exists in
1.3 as well, it was not exposed until 1.4. The "default expr" value of
``null()`` would be rendered when not needed, and additionally was also not
adapted correctly when the ORM rewrites statements such as when using
joined eager loading. The fix ensures "singleton" expressions like ``NULL``
and ``true`` aren't "adapted" to refer to columns in ORM statements, and
additionally ensures that a :func:`_orm.query_expression` with no default
expression doesn't render in the statement if a
:func:`_orm.with_expression` isn't used.

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

Fixed issue in the new feature of :meth:`_orm.Session.refresh` introduced
by :ticket:`1763` where eagerly loaded relationships are also refreshed,
where the ``lazy="raise"`` and ``lazy="raise_on_sql"`` loader strategies
would interfere with the :func:`_orm.immediateload` loader strategy, thus
breaking the feature for relationships that were loaded with
:func:`_orm.selectinload`, :func:`_orm.subqueryload` as well.

.. changelog::

:released: April 9, 2021

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

Enhanced the "expanding" feature used for :meth:`_sql.ColumnOperators.in_`
operations to infer the type of expression from the right hand list of
elements, if the left hand side does not have any explicit type set up.
This allows the expression to support stringification among other things.
In 1.3, "expanding" was not automatically used for
:meth:`_sql.ColumnOperators.in_` expressions, so in that sense this change
fixes a behavioral regression.


.. change::
:tags: bug, mypy

Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct
type for columns of subclasses that don’t directly descend from
``TypeEngine``, in particular that of ``TypeDecorator`` and
``UserDefinedType``.

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

Fixed regression where the :func:`_orm.subqueryload` loader strategy would
fail to correctly accommodate sub-options, such as a :func:`_orm.defer`
option on a column, if the "path" of the subqueryload were more than one
level deep.


.. change::
:tags: bug, sql

Fixed the "stringify" compiler to support a basic stringification
of a "multirow" INSERT statement, i.e. one with multiple tuples
following the VALUES keyword.


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

Fixed regression where the :func:`_orm.merge_frozen_result` function relied
upon by the dogpile.caching example was not included in tests and began
failing due to incorrect internal arguments.

.. change::
:tags: bug, engine, regression
:tickets: 6218

Fixed up the behavior of the :class:`_result.Row` object when dictionary
access is used upon it, meaning converting to a dict via ``dict(row)`` or
accessing members using strings or other objects i.e. ``row["some_key"]``
works as it would with a dictionary, rather than raising ``TypeError`` as
would be the case with a tuple, whether or not the C extensions are in
place. This was originally supposed to emit a 2.0 deprecation warning for
the "non-future" case using ``LegacyRow``, and was to raise
``TypeError`` for the "future" :class:`_result.Row` class. However, the C
version of :class:`_result.Row` was failing to raise this ``TypeError``,
and to complicate matters, the :meth:`_orm.Session.execute` method now
returns :class:`_result.Row` in all cases to maintain consistency with the
ORM result case, so users who didn't have C extensions installed would
see different behavior in this one case for existing pre-1.4 style
code.

Therefore, in order to soften the overall upgrade scheme as most users have
not been exposed to the more strict behavior of :class:`_result.Row` up
through 1.4.6, ``LegacyRow`` and :class:`_result.Row` both
provide for string-key access as well as support for ``dict(row)``, in all
cases emitting the 2.0 deprecation warning when ``SQLALCHEMY_WARN_20`` is
enabled. The :class:`_result.Row` object still uses tuple-like behavior for
``__contains__``, which is probably the only noticeable behavioral change
compared to ``LegacyRow``, other than the removal of
dictionary-style methods ``values()`` and ``items()``.

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

Fixed critical regression where the :class:`_orm.Session` could fail to
"autobegin" a new transaction when a flush occurred without an existing
transaction in place, implicitly placing the :class:`_orm.Session` into
legacy autocommit mode which commit the transaction. The
:class:`_orm.Session` now has a check that will prevent this condition from
occurring, in addition to repairing the flush issue.

Additionally, scaled back part of the change made as part of :ticket:`5226`
which can run autoflush during an unexpire operation, to not actually
do this in the case of a :class:`_orm.Session` using legacy
:paramref:`_orm.Session.autocommit` mode, as this incurs a commit within
a refresh operation.

.. change::
:tags: change, tests

Added a new flag to :class:`.DefaultDialect` called ``supports_schemas``;
third party dialects may set this flag to ``False`` to disable SQLAlchemy's
schema-level tests when running the test suite for a third party dialect.

.. change::
:tags: bug, regression, schema
:tickets: 6216

Fixed regression where usage of a token in the
:paramref:`_engine.Connection.execution_options.schema_translate_map`
dictionary which contained special characters such as braces would fail to
be substituted properly. Use of square bracket characters ``[]`` is now
explicitly disallowed as these are used as a delimiter character in the
current implementation.

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

Fixed regression where the ORM compilation scheme would assume the function
name of a hybrid property would be the same as the attribute name in such a
way that an ``AttributeError`` would be raised, when it would attempt to
determine the correct name for each element in a result tuple. A similar
issue exists in 1.3 but only impacts the names of tuple rows. The fix here
adds a check that the hybrid's function name is actually present in the
``__dict__`` of the class or its superclasses before assigning this name;
otherwise, the hybrid is considered to be "unnamed" and ORM result tuples
will use the naming scheme of the underlying expression.

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

Fixed critical regression caused by the new feature added as part of
:ticket:`1763`, eager loaders are invoked on unexpire operations. The new
feature makes use of the "immediateload" eager loader strategy as a
substitute for a collection loading strategy, which unlike the other
"post-load" strategies was not accommodating for recursive invocations
between mutually-dependent relationships, leading to recursion overflow
errors.


.. changelog::

:released: April 6, 2021

.. change::
:tags: bug, sql, regression, oracle, mssql
:tickets: 6202

Fixed further regressions in the same area as that of :ticket:`6173` released in
1.4.5, where a "postcompile" parameter, again most typically those used for
LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed
correctly if the same parameter rendered in multiple places in the
statement.



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

Fixed regression where a deprecated form of :meth:`_orm.Query.join` were
used, passing a series of entities to join from without any ON clause in a
single :meth:`_orm.Query.join` call, would fail to function correctly.

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

Applied a series of refactorings and fixes to accommodate for Mypy
"incremental" mode across multiple files, which previously was not taken
into account. In this mode the Mypy plugin has to accommodate Python
datatypes expressed in other files coming in with less information than
they have on a direct run.

Additionally, a new decorator :func:`_orm.declarative_mixin` is added,
which is necessary for the Mypy plugin to be able to definifitely identify
a Declarative mixin class that is otherwise not used inside a particular
Python file.

.. seealso::

:ref:`mypy_declarative_mixins`


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

Fixed issue where the Mypy plugin would fail to interpret the
"collection_class" of a relationship if it were a callable and not a class.
Also improved type matching and error reporting for collection-oriented
relationships.


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

Executing a :class:`_sql.Subquery` using :meth:`_engine.Connection.execute`
is deprecated and will emit a deprecation warning; this use case was an
oversight that should have been removed from 1.4. The operation will now
execute the underlying :class:`_sql.Select` object directly for backwards
compatibility. Similarly, the :class:`_sql.CTE` class is also not
appropriate for execution. In 1.3, attempting to execute a CTE would result
in an invalid "blank" SQL statement being executed; since this use case was
not working it now raises :class:`_exc.ObjectNotExecutableError`.
Previously, 1.4 was attempting to execute the CTE as a statement however it
was working only erratically.

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

Fixed critical regression where the :meth:`_orm.Query.yield_per` method in
the ORM would set up the internal :class:`_engine.Result` to yield chunks
at a time, however made use of the new :meth:`_engine.Result.unique` method
which uniques across the entire result. This would lead to lost rows since
the ORM is using ``id(obj)`` as the uniquing function, which leads to
repeated identifiers for new objects as already-seen objects are garbage
collected. 1.3's behavior here was to "unique" across each chunk, which
does not actually produce "uniqued" results when results are yielded in
chunks. As the :meth:`_orm.Query.yield_per` method is already explicitly
disallowed when joined eager loading is in place, which is the primary
rationale for the "uniquing" feature, the "uniquing" feature is now turned
off entirely when :meth:`_orm.Query.yield_per` is used.

This regression only applies to the legacy :class:`_orm.Query` object; when
using :term:`2.0 style` execution, "uniquing" is not automatically applied.
To prevent the issue from arising from explicit use of
:meth:`_engine.Result.unique`, an error is now raised if rows are fetched
from a "uniqued" ORM-level :class:`_engine.Result` if any
:ref:`yield per <orm_queryguide_yield_per>` API is also in use, as the
purpose of ``yield_per`` is to allow for arbitrarily large numbers of rows,
which cannot be uniqued in memory without growing the number of entries to
fit the complete result size.


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

Added accessors ``.sqlstate`` and synonym ``.pgcode`` to the ``.orig``
attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI
adapter, that is, the intermediary exception object that wraps on top of
that raised by the asyncpg library itself, but below the level of the
SQLAlchemy dialect.

.. changelog::

:released: April 2, 2021

.. change::
:tags: bug, sql, postgresql
:tickets: 6183

Fixed bug in new :meth:`_functions.FunctionElement.render_derived` feature
where column names rendered out explicitly in the alias SQL would not have
proper quoting applied for case sensitive names and other non-alphanumeric
names.

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

Fixed regression where the :func:`_orm.joinedload` loader strategy would
not successfully joinedload to a mapper that is mapper against a
:class:`.CTE` construct.

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

Fixed regression where use of the :meth:`.Operators.in_` method with a
:class:`_sql.Select` object against a non-table-bound column would produce
an ``AttributeError``, or more generally using a :class:`_sql.ScalarSelect`
that has no datatype in a binary expression would produce invalid state.


.. change::
:tags: bug, mypy
:tickets: sqlalchemy/sqlalchemy2-stubs/14

Fixed issue in mypy plugin where newly added support for
:func:`_orm.as_declarative` needed to more fully add the
``DeclarativeMeta`` class to the mypy interpreter's state so that it does
not result in a name not found error; additionally improves how global
names are setup for the plugin including the ``Mapped`` name.


.. change::
:tags: bug, mysql, regression
:tickets: 6163

Fixed regression in the MySQL dialect where the reflection query used to
detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions.

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

Added a new flag to the :class:`_engine.Dialect` class called
:attr:`_engine.Dialect.supports_statement_cache`. This flag now needs to be present
directly on a dialect class in order for SQLAlchemy's
:ref:`query cache <sql_caching>` to take effect for that dialect. The
rationale is based on discovered issues such as :ticket:`6173` revealing
that dialects which hardcode literal values from the compiled statement,
often the numerical parameters used for LIMIT / OFFSET, will not be
compatible with caching until these dialects are revised to use the
parameters present in the statement only. For third party dialects where
this flag is not applied, the SQL logging will show the message "dialect
does not support caching", indicating the dialect should seek to apply this
flag once they have verified that no per-statement literal values are being
rendered within the compilation phase.

.. seealso::

:ref:`engine_thirdparty_caching`

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

Fixed typo in the fix for :ticket:`6099` released in 1.4.4 that completely
prevented this change from working correctly, i.e. the error message did not match
what was actually emitted by pg8000.

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

Scaled back the warning message added in :ticket:`5171` to not warn for
overlapping columns in an inheritance scenario where a particular
relationship is local to a subclass and therefore does not represent an
overlap.

.. change::
:tags: bug, regression, oracle
:tickets: 6173

Fixed critical regression where the Oracle compiler would not maintain the
correct parameter values in the LIMIT/OFFSET for a select due to a caching
issue.


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

Fixed issue where the PostgreSQL :class:`.PGInspector`, when generated
against an :class:`_engine.Engine`, would fail for ``.get_enums()``,
``.get_view_names()``, ``.get_foreign_table_names()`` and
``.get_table_oid()`` when used against a "future" style engine and not the
connection directly.

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

Introduce a new parameter :paramref:`_types.Enum.omit_aliases` in
:class:`_types.Enum` type allow filtering aliases when using a pep435 Enum.
Previous versions of SQLAlchemy kept aliases in all cases, creating
database enum type with additional states, meaning that they were treated
as different values in the db. For backward compatibility this flag
defaults to ``False`` in the 1.4 series, but will be switched to ``True``
in a future version. A deprecation warning is raise if this flag is not
specified and the passed enum contains aliases.

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

Fixed a regression in MSSQL 2012+ that prevented the order by clause
to be rendered when ``offset=0`` is used in a subquery.

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


Fixed issue where the asyncio extension could not be loaded
if running Python 3.6 with the backport library of
``contextvars`` installed.

.. changelog::

:released: March 30, 2021

.. change::
:tags: bug, misc

Adjusted the usage of the ``importlib_metadata`` library for loading
setuptools entrypoints in order to accommodate for some deprecation
changes.


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

Modified the ``is_disconnect()`` handler for the pg8000 dialect, which now
accommodates for a new ``InterfaceError`` emitted by pg8000 1.19.0. Pull
request courtesy Hamdi Burak Usul.


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

Fixed critical issue in the new :meth:`_orm.PropComparator.and_` feature
where loader strategies that emit secondary SELECT statements such as
:func:`_orm.selectinload` and :func:`_orm.lazyload` would fail to
accommodate for bound parameters in the user-defined criteria in terms of
the current statement being executed, as opposed to the cached statement,
causing stale bound values to be used.

This also adds a warning for the case where an object that uses
:func:`_orm.lazyload` in conjunction with :meth:`_orm.PropComparator.and_`
is attempted to be serialized; the loader criteria cannot reliably
be serialized and deserialized and eager loading should be used for this
case.


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

Repair wrong arguments to exception handling method
in CursorResult.

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

Fixed missing method :meth:`_orm.Session.get` from the
:class:`_orm.ScopedSession` interface.


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

Modified the context manager used by :class:`_engine.Transaction` so that
an "already detached" warning is not emitted by the ending of the context
manager itself, if the transaction were already manually rolled back inside
the block. This applies to regular transactions, savepoint transactions,
and legacy "marker" transactions. A warning is still emitted if the
``.rollback()`` method is called explicitly more than once.

.. changelog::

:released: March 25, 2021

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

Fixed a bug where python 2.7.5 (default on CentOS 7) wasn't able to import
sqlalchemy, because on this version of Python ``exec "statement"`` and
``exec("statement")`` do not behave the same way. The compatibility
``exec_()`` function was used instead.

.. change::
:tags: sqlite, feature, asyncio
:tickets: 5920

Added support for the aiosqlite database driver for use with the
SQLAlchemy asyncio extension.

.. seealso::

:ref:`aiosqlite`

.. change::
:tags: bug, regression, orm, declarative
:tickets: 6128

Fixed regression where the ``.metadata`` attribute on a per class level
would not be honored, breaking the use case of per-class-hierarchy
:class:`.schema.MetaData` for abstract declarative classes and mixins.


.. seealso::

:ref:`declarative_metadata`

.. change::
:tags: bug, mypy

Added support for the Mypy extension to correctly interpret a declarative
base class that's generated using the :func:`_orm.as_declarative` function
as well as the :meth:`_orm.registry.as_declarative_base` method.

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

Fixed bug in Mypy plugin where the Python type detection
for the :class:`_types.Boolean` column type would produce
an exception; additionally implemented support for :class:`_types.Enum`,
including detection of a string-based enum vs. use of Python ``enum.Enum``.

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

Fixed reflection of identity columns in tables with mixed case names
in PostgreSQL.

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

Repaired the ``pysqlcipher`` dialect to connect correctly which had
regressed in 1.4, and added test + CI support to maintain the driver
in working condition. The dialect now imports the ``sqlcipher3`` module
for Python 3 by default before falling back to ``pysqlcipher3`` which
is documented as now being unmaintained.

.. seealso::

:ref:`pysqlcipher`


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

Fixed bug where ORM queries using a correlated subquery in conjunction with
:func:`_orm.column_property` would fail to correlate correctly to an
enclosing subquery or to a CTE when :meth:`_sql.Select.correlate_except`
were used in the property to control correlation, in cases where the
subquery contained the same selectables as ones within the correlated
subquery that were intended to not be correlated.

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

Fixed bug where combinations of the new "relationship with criteria"
feature could fail in conjunction with features that make use of the new
"lambda SQL" feature, including loader strategies such as selectinload and
lazyload, for more complicated scenarios such as polymorphic loading.

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

Repaired support so that the :meth:`_sql.ClauseElement.params` method can
work correctly with a :class:`_sql.Select` object that includes joins
across ORM relationship structures, which is a new feature in 1.4.


.. change::
:tags: bug, engine, regression
:tickets: 6119

Restored the :class:`_engine.ResultProxy` name back to the
``sqlalchemy.engine`` namespace. This name refers to the
``LegacyCursorResult`` object.

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

Fixed issue where a "removed in 2.0" warning were generated internally by
the relationship loader mechanics.


.. changelog::