Changelog
### 1.4.1
```
:released: March 17, 2021
.. change::
:tags: bug, orm, regression
:tickets: 6066
Fixed regression where producing a Core expression construct such as
:func:`_sql.select` using ORM entities would eagerly configure the mappers,
in an effort to maintain compatibility with the :class:`_orm.Query` object
which necessarily does this to support many backref-related legacy cases.
However, core :func:`_sql.select` constructs are also used in mapper
configurations and such, and to that degree this eager configuration is
more of an inconvenience, so eager configure has been disabled for the
:func:`_sql.select` and other Core constructs in the absence of ORM loading
types of functions such as :class:`_orm.Load`.
The change maintains the behavior of :class:`_orm.Query` so that backwards
compatibility is maintained. However, when using a :func:`_sql.select` in
conjunction with ORM entities, a "backref" that isn't explicitly placed on
one of the classes until mapper configure time won't be available unless
:func:`_orm.configure_mappers` or the newer :func:`_orm.registry.configure`
has been called elsewhere. Prefer using
:paramref:`_orm.relationship.back_populates` for more explicit relationship
configuration which does not have the eager configure requirement.
.. change::
:tags: bug, mssql, regression
:tickets: 6058
Fixed regression where a new setinputsizes() API that's available for
pyodbc was enabled, which is apparently incompatible with pyodbc's
fast_executemany() mode in the absence of more accurate typing information,
which as of yet is not fully implemented or tested. The pyodbc dialect and
connector has been modified so that setinputsizes() is not used at all
unless the parameter ``use_setinputsizes`` is passed to the dialect, e.g.
via :func:`_sa.create_engine`, at which point its behavior can be
customized using the :meth:`.DialectEvents.do_setinputsizes` hook.
.. seealso::
:ref:`mssql_pyodbc_setinputsizes`
.. change::
:tags: bug, orm, regression
:tickets: 6055
Fixed a critical regression in the relationship lazy loader where the SQL
criteria used to fetch a related many-to-one object could go stale in
relation to other memoized structures within the loader if the mapper had
configuration changes, such as can occur when mappers are late configured
or configured on demand, producing a comparison to None and returning no
object. Huge thanks to Alan Hamlett for their help tracking this down late
into the night.
.. change::
:tags: bug, regression
:tickets: 6068
Added back ``items`` and ``values`` to ``ColumnCollection`` class.
The regression was introduced while adding support for duplicate
columns in from clauses and selectable in ticket 4753.
.. change::
:tags: bug, engine, regression
:tickets: 6074
The Python ``namedtuple()`` has the behavior such that the names ``count``
and ``index`` will be served as tuple values if the named tuple includes
those names; if they are absent, then their behavior as methods of
``collections.abc.Sequence`` is maintained. Therefore the
:class:`_result.Row` and :class:`_result.LegacyRow` classes have been fixed
so that they work in this same way, maintaining the expected behavior for
database rows that have columns named "index" or "count".
.. change::
:tags: bug, orm, regression
:tickets: 6076
Fixed regression where the :meth:`_orm.Query.exists` method would fail to
create an expression if the entity list of the :class:`_orm.Query` were
an arbitrary SQL column expression.
.. change::
:tags: bug, orm, regression
:tickets: 6052
Fixed regression where calling upon :meth:`_orm.Query.count` in conjunction
with a loader option such as :func:`_orm.joinedload` would fail to ignore
the loader option. This is a behavior that has always been very specific to
the :meth:`_orm.Query.count` method; an error is normally raised if a given
:class:`_orm.Query` has options that don't apply to what it is returning.
.. change::
:tags: bug, orm, declarative, regression
:tickets: 6054
Fixed bug where user-mapped classes that contained an attribute named
"registry" would cause conflicts with the new registry-based mapping system
when using :class:`.DeclarativeMeta`. While the attribute remains
something that can be set explicitly on a declarative base to be
consumed by the metaclass, once located it is placed under a private
class variable so it does not conflict with future subclasses that use
the same name for other purposes.
.. change::
:tags: bug, orm, regression
:tickets: 6067
Fixed regression in :meth:`_orm.Session.identity_key`, including that the
method and related methods were not covered by any unit test as well as
that the method contained a typo preventing it from functioning correctly.
.. changelog::
```
### 1.4.0
```
:released: March 15, 2021
.. change::
:tags: bug, mssql
:tickets: 5919
Fix a reflection error for MSSQL 2005 introduced by the reflection of
filtered indexes.
.. change::
:tags: feature, mypy
:tickets: 4609
Rudimentary and experimental support for Mypy has been added in the form of
a new plugin, which itself depends on new typing stubs for SQLAlchemy. The
plugin allows declarative mappings in their standard form to both be
compatible with Mypy as well as to provide typing support for mapped
classes and instances.
.. seealso::
:ref:`mypy_toplevel`
.. change::
:tags: bug, sql
:tickets: 6016
Fixed bug where the "percent escaping" feature that occurs with dialects
that use the "format" or "pyformat" bound parameter styles was not enabled
for the :meth:`_sql.Operators.op` and :class:`_sql.custom_op` constructs,
for custom operators that use percent signs. The percent sign will now be
automatically doubled based on the paramstyle as necessary.
.. change::
:tags: bug, regression, sql
:tickets: 5979
Fixed regression where the "unsupported compilation error" for unknown
datatypes would fail to raise correctly.
.. change::
:tags: ext, usecase
:tickets: 5942
Add new parameter
:paramref:`_automap.AutomapBase.prepare.reflection_options`
to allow passing of :meth:`_schema.MetaData.reflect` options like ``only``
or dialect-specific reflection options like ``oracle_resolve_synonyms``.
.. change::
:tags: change, sql
Altered the compilation for the :class:`.CTE` construct so that a string is
returned representing the inner SELECT statement if the :class:`.CTE` is
stringified directly, outside of the context of an enclosing SELECT; This
is the same behavior of :meth:`_sql.FromClause.alias` and
:meth:`_sql.Select.subquery`. Previously, a blank string would be
returned as the CTE is normally placed above a SELECT after that SELECT has
been generated, which is generally misleading when debugging.
.. change::
:tags: bug, orm
:tickets: 5981
Fixed regression where the :paramref:`_orm.relationship.query_class`
parameter stopped being functional for "dynamic" relationships. The
``AppenderQuery`` remains dependent on the legacy :class:`_orm.Query`
class; users are encouraged to migrate from the use of "dynamic"
relationships to using :func:`_orm.with_parent` instead.
.. change::
:tags: bug, orm, regression
:tickets: 6003
Fixed regression where :meth:`_orm.Query.join` would produce no effect if
the query itself as well as the join target were against a
:class:`_schema.Table` object, rather than a mapped class. This was part of
a more systemic issue where the legacy ORM query compiler would not be
correctly used from a :class:`_orm.Query` if the statement produced had not
ORM entities present within it.
.. change::
:tags: bug, regression, sql
:tickets: 6008
Fixed regression where usage of the standalone :func:`_sql.distinct()` used
in the form of being directly SELECTed would fail to be locatable in the
result set by column identity, which is how the ORM locates columns. While
standalone :func:`_sql.distinct()` is not oriented towards being directly
SELECTed (use :meth:`_sql.select.distinct` for a regular
``SELECT DISTINCT..``) , it was usable to a limited extent in this way
previously (but wouldn't work in subqueries, for example). The column
targeting for unary expressions such as "DISTINCT <col>" has been improved
so that this case works again, and an additional improvement has been made
so that usage of this form in a subquery at least generates valid SQL which
was not the case previously.
The change additionally enhances the ability to target elements in
``row._mapping`` based on SQL expression objects in ORM-enabled
SELECT statements, including whether the statement was invoked by
``connection.execute()`` or ``session.execute()``.
.. change::
:tags: bug, orm, asyncio
:tickets: 5998
The API for :meth:`_asyncio.AsyncSession.delete` is now an awaitable;
this method cascades along relationships which must be loaded in a
similar manner as the :meth:`_asyncio.AsyncSession.merge` method.
.. change::
:tags: usecase, postgresql, mysql, asyncio
:tickets: 5967
Added an ``asyncio.Lock()`` within SQLAlchemy's emulated DBAPI cursor,
local to the connection, for the asyncpg and aiomysql dialects for the
scope of the ``cursor.execute()`` and ``cursor.executemany()`` methods. The
rationale is to prevent failures and corruption for the case where the
connection is used in multiple awaitables at once.
While this use case can also occur with threaded code and non-asyncio
dialects, we anticipate this kind of use will be more common under asyncio,
as the asyncio API is encouraging of such use. It's definitely better to
use a distinct connection per concurrent awaitable however as concurrency
will not be achieved otherwise.
For the asyncpg dialect, this is so that the space between
the call to ``prepare()`` and ``fetch()`` is prevented from allowing
concurrent executions on the connection from causing interface error
exceptions, as well as preventing race conditions when starting a new
transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level
so this intends to provide a similar behavior, outside the realm of server
side cursors.
For the aiomysql dialect, the mutex will provide safety such that
the statement execution and the result set fetch, which are two distinct
steps at the connection level, won't get corrupted by concurrent
executions on the same connection.
.. change::
:tags: bug, engine
:tickets: 6002
Improved engine logging to note ROLLBACK and COMMIT which is logged while
the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library
level and do not have any effect when AUTOCOMMIT is in effect, however it's
still worthwhile to log as these indicate where SQLAlchemy sees the
"transaction" demarcation.
.. change::
:tags: bug, regression, engine
:tickets: 6004
Fixed a regression where the "reset agent" of the connection pool wasn't
really being utilized by the :class:`_engine.Connection` when it were
closed, and also leading to a double-rollback scenario that was somewhat
wasteful. The newer architecture of the engine has been updated so that
the connection pool "reset-on-return" logic will be skipped when the
:class:`_engine.Connection` explicitly closes out the transaction before
returning the pool to the connection.
.. change::
:tags: bug, schema
:tickets: 5953
Deprecated all schema-level ``.copy()`` methods and renamed to
``_copy()``. These are not standard Python "copy()" methods as they
typically rely upon being instantiated within particular contexts
which are passed to the method as optional keyword arguments. The
:meth:`_schema.Table.tometadata` method is the public API that provides
copying for :class:`_schema.Table` objects.
.. change::
:tags: bug, ext
:tickets: 6020
The ``sqlalchemy.ext.mutable`` extension now tracks the "parents"
collection using the :class:`.InstanceState` associated with objects,
rather than the object itself. The latter approach required that the object
be hashable so that it can be inside of a ``WeakKeyDictionary``, which goes
against the behavioral contract of the ORM overall which is that ORM mapped
objects do not need to provide any particular kind of ``__hash__()`` method
and that unhashable objects are supported.
.. change::
:tags: bug, orm
:tickets: 5984
The unit of work process now turns off all "lazy='raise'" behavior
altogether when a flush is proceeding. While there are areas where the UOW
is sometimes loading things that aren't ultimately needed, the lazy="raise"
strategy is not helpful here as the user often does not have much control
or visibility into the flush process.
.. changelog::
```
### 1.4.0b3
```
:released: March 15, 2021
:released: February 15, 2021
.. change::
:tags: bug, orm
:tickets: 5933
Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label
style would not be preserved in the keys used by result rows; this has been
applied to all combinations of Core/ORM columns / session vs. connection
etc. so that the linkage from statement to result row is the same in all
cases. As part of this change, the labeling of column expressions
in rows has been improved to retain the original name of the ORM
attribute even if used in a subquery.
.. change::
:tags: bug, sql
:tickets: 5924
Fixed bug where the "cartesian product" assertion was not correctly
accommodating for joins between tables that relied upon the use of LATERAL
to connect from a subquery to another subquery in the enclosing context.
.. change::
:tags: bug, sql
:tickets: 5934
Fixed 1.4 regression where the :meth:`_functions.Function.in_` method was
not covered by tests and failed to function properly in all cases.
.. change::
:tags: bug, engine, postgresql
:tickets: 5941
Continued with the improvement made as part of :ticket:`5653` to further
support bound parameter names, including those generated against column
names, for names that include colons, parenthesis, and question marks, as
well as improved test support, so that bound parameter names even if they
are auto-derived from column names should have no problem including for
parenthesis in psycopg2's "pyformat" style.
As part of this change, the format used by the asyncpg DBAPI adapter (which
is local to SQLAlchemy's asyncpg dialect) has been changed from using
"qmark" paramstyle to "format", as there is a standard and internally
supported SQL string escaping style for names that use percent signs with
"format" style (i.e. to double percent signs), as opposed to names that use
question marks with "qmark" style (where an escaping system is not defined
by pep-249 or Python).
.. seealso::
:ref:`change_5941`
.. change::
:tags: sql, usecase, postgresql, sqlite
:tickets: 5939
Enhance ``set_`` keyword of :class:`.OnConflictDoUpdate` to accept a
:class:`.ColumnCollection`, such as the ``.c.`` collection from a
:class:`Selectable`, or the ``.excluded`` contextual object.
.. change::
:tags: feature, orm
The ORM used in :term:`2.0 style` can now return ORM objects from the rows
returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by
supplying the construct to :meth:`_sql.Select.from_statement` in an ORM
context.
.. seealso::
:ref:`orm_dml_returning_objects`
.. change::
:tags: bug, sql
:tickets: 5935
Fixed regression where use of an arbitrary iterable with the
:func:`_sql.select` function was not working, outside of plain lists. The
forwards/backwards compatibility logic here now checks for a wider range of
incoming "iterable" types including that a ``.c`` collection from a
selectable can be passed directly. Pull request compliments of Oliver Rice.
.. changelog::
```
### 1.4.0b2
```
:released: March 15, 2021
:released: February 3, 2021
.. change::
:tags: usecase, sql
:tickets: 5695
Multiple calls to "returning", e.g. :meth:`_sql.Insert.returning`,
may now be chained to add new columns to the RETURNING clause.
.. change::
:tags: bug, asyncio
:tickets: 5615
Adjusted the greenlet integration, which provides support for Python asyncio
in SQLAlchemy, to accommodate for the handling of Python ``contextvars``
(introduced in Python 3.7) for ``greenlet`` versions greater than 0.4.17.
Greenlet version 0.4.17 added automatic handling of contextvars in a
backwards-incompatible way; we've coordinated with the greenlet authors to
add a preferred API for this in versions subsequent to 0.4.17 which is now
supported by SQLAlchemy's greenlet integration. For greenlet versions prior
to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked
from the dependencies.
.. change::
:tags: bug, engine, sqlite
:tickets: 5845
Fixed bug in the 2.0 "future" version of :class:`.Engine` where emitting
SQL during the :meth:`.EngineEvents.begin` event hook would cause a
re-entrant (recursive) condition due to autobegin, affecting among other
things the recipe documented for SQLite to allow for savepoints and
serializable isolation support.
.. change::
:tags: bug, orm, regression
:tickets: 5845
Fixed issue in new :class:`_orm.Session` similar to that of the
:class:`_engine.Connection` where the new "autobegin" logic could be
tripped into a re-entrant (recursive) state if SQL were executed within the
:meth:`.SessionEvents.after_transaction_create` event hook.
.. change::
:tags: sql
:tickets: 4757
Replace :meth:`_orm.Query.with_labels` and
:meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and
setters :meth:`_sql.GenerativeSelect.get_label_style` and
:meth:`_sql.GenerativeSelect.set_label_style` to accommodate the three
supported label styles: :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`,
:data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and
:data:`_sql.LABEL_STYLE_NONE`.
In addition, for Core and "future style" ORM queries,
``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This
style differs from the existing "no labels" style in that labeling is
applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a
duplicate column name is not accessible via name in any case.
For cases where labeling is significant, namely that the ``.c`` collection
of a subquery is able to refer to all columns unambiguously, the behavior
of ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now sufficient for all
SQLAlchemy features across Core and ORM which involve this behavior.
Result set rows since SQLAlchemy 1.0 are usually aligned with column
constructs positionally.
For legacy ORM queries using :class:`_query.Query`, the table-plus-column
names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL``
continues to be used so that existing test suites and logging facilities
see no change in behavior by default.
.. change::
:tags: bug, orm, unitofwork
:tickets: 5735
Improved the unit of work topological sorting system such that the
toplogical sort is now deterministic based on the sorting of the input set,
which itself is now sorted at the level of mappers, so that the same inputs
of affected mappers should produce the same output every time, among
mappers / tables that don't have any dependency on each other. This further
reduces the chance of deadlocks as can be observed in a flush that UPDATEs
among multiple, unrelated tables such that row locks are generated.
.. change::
:tags: changed, orm
:tickets: 5897
Mapper "configuration", which occurs within the
:func:`_orm.configure_mappers` function, is now organized to be on a
per-registry basis. This allows for example the mappers within a certain
declarative base to be configured, but not those of another base that is
also present in memory. The goal is to provide a means of reducing
application startup time by only running the "configure" process for sets
of mappers that are needed. This also adds the
:meth:`_orm.registry.configure` method that will run configure for the
mappers local in a particular registry only.
.. change::
:tags: bug, orm
:tickets: 5702
Fixed regression where the :paramref:`.Bundle.single_entity` flag would
take effect for a :class:`.Bundle` even though it were not set.
Additionally, this flag is legacy as it only makes sense for the
:class:`_orm.Query` object and not 2.0 style execution. a deprecation
warning is emitted when used with new-style execution.
.. change::
:tags: bug, sql
:tickets: 5858
Fixed issue in new :meth:`_sql.Select.join` method where chaining from the
current JOIN wasn't looking at the right state, causing an expression like
"FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than
"FROM a JOIN b <onclause> JOIN c <onclause>".
.. change::
:tags: usecase, sql
Added :meth:`_sql.Select.outerjoin_from` method to complement
:meth:`_sql.Select.join_from`.
.. change::
:tags: usecase, sql
:tickets: 5888
Adjusted the "literal_binds" feature of :class:`_sql.Compiler` to render
NULL for a bound parameter that has ``None`` as the value, either
explicitly passed or omitted. The previous error message "bind parameter
without a renderable value" is removed, and a missing or ``None`` value
will now render NULL in all cases. Previously, rendering of NULL was
starting to happen for DML statements due to internal refactorings, but was
not explicitly part of test coverage, which it now is.
While no error is raised, when the context is within that of a column
comparison, and the operator is not "IS"/"IS NOT", a warning is emitted
that this is not generally useful from a SQL perspective.
.. change::
:tags: bug, orm
:tickets: 5750
Fixed regression where creating an :class:`_orm.aliased` construct against
a plain selectable and including a name would raise an assertionerror.
.. change::
:tags: bug, mssql, mysql, datatypes
:tickets: 5788
:versions: 1.4.0b2
Decimal accuracy and behavior has been improved when extracting floating
point and/or decimal values from JSON strings using the
:meth:`_sql.sqltypes.JSON.Comparator.as_float` method, when the numeric
value inside of the JSON string has many significant digits; previously,
MySQL backends would truncate values with many significant digits and SQL
Server backends would raise an exception due to a DECIMAL cast with
insufficient significant digits. Both backends now use a FLOAT-compatible
approach that does not hardcode significant digits for floating point
values. For precision numerics, a new method
:meth:`_sql.sqltypes.JSON.Comparator.as_numeric` has been added which
accepts arguments for precision and scale, and will return values as Python
``Decimal`` objects with no floating point conversion assuming the DBAPI
supports it (all but pysqlite).
.. change::
:tags: feature, orm, declarative
:tickets: 5745
Added an alternate resolution scheme to Declarative that will extract the
SQLAlchemy column or mapped property from the "metadata" dictionary of a
dataclasses.Field object. This allows full declarative mappings to be
combined with dataclass fields.
.. seealso::
:ref:`orm_declarative_dataclasses_declarative_table`
.. change::
:tags: bug, sql
:tickets: 5754
Deprecation warnings are emitted under "SQLALCHEMY_WARN_20" mode when
passing a plain string to :meth:`_orm.Session.execute`.
.. change::
:tags: bug, sql, orm
:tickets: 5760, 5763, 5765, 5768, 5770
A wide variety of fixes to the "lambda SQL" feature introduced at
:ref:`engine_lambda_caching` have been implemented based on user feedback,
with an emphasis on its use within the :func:`_orm.with_loader_criteria`
feature where it is most prominently used [ticket:5760]:
* fixed issue where boolean True/False values referred towards in the
closure variables of the lambda would cause failures [ticket:5763]
* Repaired a non-working detection for Python functions embedded in the
lambda that produce bound values; this case is likely not supportable
so raises an informative error, where the function should be invoked
outside the lambda itself. New documentation has been added to
further detail this behavior. [ticket:5770]
* The lambda system by default now rejects the use of non-SQL elements
within the closure variables of the lambda entirely, where the error
suggests the two options of either explicitly ignoring closure variables
that are not SQL parameters, or specifying a specific set of values to be
considered as part of the cache key based on hash value. This critically
prevents the lambda system from assuming that arbitrary objects within
the lambda's closure are appropriate for caching while also refusing to
ignore them by default, preventing the case where their state might
not be constant and have an impact on the SQL construct produced.
The error message is comprehensive and new documentation has been
added to further detail this behavior. [ticket:5765]
* Fixed support for the edge case where an ``in_()`` expression
against a list of SQL elements, such as :func:`_sql.literal` objects,
would fail to be accommodated correctly. [ticket:5768]
.. change::
:tags: bug, orm
:tickets: 5760, 5766, 5762, 5761, 5764
Related to the fixes for the lambda criteria system within Core, within the
ORM implemented a variety of fixes for the
:func:`_orm.with_loader_criteria` feature as well as the
:meth:`_orm.SessionEvents.do_orm_execute` event handler that is often
used in conjunction [ticket:5760]:
* fixed issue where :func:`_orm.with_loader_criteria` function would fail
if the given entity or base included non-mapped mixins in its descending
class hierarchy [ticket:5766]
* The :func:`_orm.with_loader_criteria` feature is now unconditionally
disabled for the case of ORM "refresh" operations, including loads
of deferred or expired column attributes as well as for explicit
operations like :meth:`_orm.Session.refresh`. These loads are necessarily
based on primary key identity where addiional WHERE criteria is
never appropriate. [ticket:5762]
* Added new attribute :attr:`_orm.ORMExecuteState.is_column_load` to indicate
that a :meth:`_orm.SessionEvents.do_orm_execute` handler that a particular
operation is a primary-key-directed column attribute load, where additional
criteria should not be added. The :func:`_orm.with_loader_criteria`
function as above ignores these in any case now. [ticket:5761]
* Fixed issue where the :attr:`_orm.ORMExecuteState.is_relationship_load`
attribute would not be set correctly for many lazy loads as well as all
selectinloads. The flag is essential in order to test if options should
be added to statements or if they would already have been propagated via
relationship loads. [ticket:5764]
.. change::
:tags: usecase, orm
Added :attr:`_orm.ORMExecuteState.bind_mapper` and
:attr:`_orm.ORMExecuteState.all_mappers` accessors to
:class:`_orm.ORMExecuteState` event object, so that handlers can respond to
the target mapper and/or mapped class or classes involved in an ORM
statement execution.
.. change::
:tags: bug, engine, postgresql, oracle
Adjusted the "setinputsizes" logic relied upon by the cx_Oracle, asyncpg
and pg8000 dialects to support a :class:`.TypeDecorator` that includes
an override the :meth:`.TypeDecorator.get_dbapi_type()` method.
.. change::
:tags: postgresql, performance
Enhanced the performance of the asyncpg dialect by caching the asyncpg
PreparedStatement objects on a per-connection basis. For a test case that
makes use of the same statement on a set of pooled connections this appears
to grant a 10-20% speed improvement. The cache size is adjustable and may
also be disabled.
.. seealso::
:ref:`asyncpg_prepared_statement_cache`
.. change::
:tags: feature, mysql
:tickets: 5747
Added support for the aiomysql driver when using the asyncio SQLAlchemy
extension.
.. seealso::
:ref:`aiomysql`
.. change::
:tags: bug, reflection
:tickets: 5684
Fixed bug where the now-deprecated ``autoload`` parameter was being called
internally within the reflection routines when a related table were
reflected.
.. change::
:tags: platform, performance
:tickets: 5681
Adjusted some elements related to internal class production at import time
which added significant latency to the time spent to import the library vs.
that of 1.3. The time is now about 20-30% slower than 1.3 instead of
200%.
.. change::
:tags: changed, schema
:tickets: 5775
Altered the behavior of the :class:`_schema.Identity` construct such that
when applied to a :class:`_schema.Column`, it will automatically imply that
the value of :paramref:`_sql.Column.nullable` should default to ``False``,
in a similar manner as when the :paramref:`_sql.Column.primary_key`
parameter is set to ``True``. This matches the default behavior of all
supporting databases where ``IDENTITY`` implies ``NOT NULL``. The
PostgreSQL backend is the only one that supports adding ``NULL`` to an
``IDENTITY`` column, which is here supported by passing a ``True`` value
for the :paramref:`_sql.Column.nullable` parameter at the same time.
.. change::
:tags: bug, postgresql
:tickets: 5698
Fixed a small regression where the query for "show
standard_conforming_strings" upon initialization would be emitted even if
the server version info were detected as less than version 8.2, previously
it would only occur for server version 8.2 or greater. The query fails on
Amazon Redshift which reports a PG server version older than this value.
.. change::
:tags: bug, sql, postgresql, mysql, sqlite
:tickets: 5169
An informative error message is now raised for a selected set of DML
methods (currently all part of :class:`_dml.Insert` constructs) if they are
called a second time, which would implicitly cancel out the previous
setting. The methods altered include:
:class:`_sqlite.Insert.on_conflict_do_update`,
:class:`_sqlite.Insert.on_conflict_do_nothing` (SQLite),
:class:`_postgresql.Insert.on_conflict_do_update`,
:class:`_postgresql.Insert.on_conflict_do_nothing` (PostgreSQL),
:class:`_mysql.Insert.on_duplicate_key_update` (MySQL)
.. change::
:tags: pool, tests, usecase
:tickets: 5582
Improve documentation and add test for sub-second pool timeouts.
Pull request courtesy Jordan Pittier.
.. change::
:tags: bug, general
Fixed a SQLite source file that had non-ascii characters inside of its
docstring without a source encoding, introduced within the "INSERT..ON
CONFLICT" feature, which would cause failures under Python 2.
.. change::
:tags: sqlite, usecase
:tickets: 4010
Implemented INSERT... ON CONFLICT clause for SQLite. Pull request courtesy
Ramon Williams.
.. seealso::
:ref:`sqlite_on_conflict_insert`
.. change::
:tags: bug, asyncio
:tickets: 5811
Implemented "connection-binding" for :class:`.AsyncSession`, the ability to
pass an :class:`.AsyncConnection` to create an :class:`.AsyncSession`.
Previously, this use case was not implemented and would use the associated
engine when the connection were passed. This fixes the issue where the
"join a session to an external transaction" use case would not work
correctly for the :class:`.AsyncSession`. Additionally, added methods
:meth:`.AsyncConnection.in_transaction`,
:meth:`.AsyncConnection.in_nested_transaction`,
:meth:`.AsyncConnection.get_transaction`,
:meth:`.AsyncConnection.get_nested_transaction` and
:attr:`.AsyncConnection.info` attribute.
.. change::
:tags: usecase, asyncio
The :class:`.AsyncEngine`, :class:`.AsyncConnection` and
:class:`.AsyncTransaction` objects may be compared using Python ``==`` or
``!=``, which will compare the two given objects based on the "sync" object
they are proxying towards. This is useful as there are cases particularly
for :class:`.AsyncTransaction` where multiple instances of
:class:`.AsyncTransaction` can be proxying towards the same sync
:class:`_engine.Transaction`, and are actually equivalent. The
:meth:`.AsyncConnection.get_transaction` method will currently return a new
proxying :class:`.AsyncTransaction` each time as the
:class:`.AsyncTransaction` is not otherwise statefully associated with its
originating :class:`.AsyncConnection`.
.. change::
:tags: bug, oracle
:tickets: 5884
Oracle two-phase transactions at a rudimentary level are now no longer
deprecated. After receiving support from cx_Oracle devs we can provide for
basic xid + begin/prepare support with some limitations, which will work
more fully in an upcoming release of cx_Oracle. Two phase "recovery" is not
currently supported.
.. change::
:tags: asyncio
The SQLAlchemy async mode now detects and raises an informative
error when an non asyncio compatible :term:`DBAPI` is used.
Using a standard ``DBAPI`` with async SQLAlchemy will cause
it to block like any sync call, interrupting the executing asyncio
loop.
.. change::
:tags: usecase, orm, asyncio
:tickets: 5796, 5797, 5802
Added :meth:`_asyncio.AsyncSession.scalar`,
:meth:`_asyncio.AsyncSession.get` as well as support for
:meth:`_orm.sessionmaker.begin` to work as an async context manager with
:class:`_asyncio.AsyncSession`. Also added
:meth:`_asyncio.AsyncSession.in_transaction` accessor.
.. change::
:tags: bug, sql
:tickets: 5785
Fixed issue in new :class:`_sql.Values` construct where passing tuples of
objects would fall back to per-value type detection rather than making use
of the :class:`_schema.Column` objects passed directly to
:class:`_sql.Values` that tells SQLAlchemy what the expected type is. This
would lead to issues for objects such as enumerations and numpy strings
that are not actually necessary since the expected type is given.
.. change::
:tags: bug, engine
Added the "future" keyword to the list of words that are known by the
:func:`_sa.engine_from_config` function, so that the values "true" and
"false" may be configured as "boolean" values when using a key such
as ``sqlalchemy.future = true`` or ``sqlalchemy.future = false``.
.. change::
:tags: usecase, schema
:tickets: 5712
The :meth:`_events.DDLEvents.column_reflect` event may now be applied to a
:class:`_schema.MetaData` object where it will take effect for the
:class:`_schema.Table` objects local to that collection.
.. seealso::
:meth:`_events.DDLEvents.column_reflect`
:ref:`mapper_automated_reflection_schemes` - in the ORM mapping documentation
:ref:`automap_intercepting_columns` - in the :ref:`automap_toplevel` documentation
.. change::
:tags: feature, engine
Dialect-specific constructs such as
:meth:`_postgresql.Insert.on_conflict_do_update` can now stringify in-place
without the need to specify an explicit dialect object. The constructs,
when called upon for ``str()``, ``print()``, etc. now have internal
direction to call upon their appropriate dialect rather than the
"default"dialect which doesn't know how to stringify these. The approach
is also adapted to generic schema-level create/drop such as
:class:`_schema.AddConstraint`, which will adapt its stringify dialect to
one indicated by the element within it, such as the
:class:`_postgresql.ExcludeConstraint` object.
.. change::
:tags: feature, engine
:tickets: 5911
Added new execution option
:paramref:`_engine.Connection.execution_options.logging_token`. This option
will add an additional per-message token to log messages generated by the
:class:`_engine.Connection` as it executes statements. This token is not
part of the logger name itself (that part can be affected using the
existing :paramref:`_sa.create_engine.logging_name` parameter), so is
appropriate for ad-hoc connection use without the side effect of creating
many new loggers. The option can be set at the level of
:class:`_engine.Connection` or :class:`_engine.Engine`.
.. seealso::
:ref:`dbengine_logging_tokens`
.. change::
:tags: bug, pool
:tickets: 5708
Fixed regression where a connection pool event specified with a keyword,
most notably ``insert=True``, would be lost when the event were set up.
This would prevent startup events that need to fire before dialect-level
events from working correctly.
.. change::
:tags: usecase, pool
:tickets: 5708, 5497
The internal mechanics of the engine connection routine has been altered
such that it's now guaranteed that a user-defined event handler for the
:meth:`_pool.PoolEvents.connect` handler, when established using
``insert=True``, will allow an event handler to run that is definitely
invoked **before** any dialect-specific initialization starts up, most
notably when it does things like detect default schema name.
Previously, this would occur in most cases but not unconditionally.
A new example is added to the schema documentation illustrating how to
establish the "default schema name" within an on-connect event.
.. change::
:tags: usecase, postgresql
Added a read/write ``.autocommit`` attribute to the DBAPI-adaptation layer
for the asyncpg dialect. This so that when working with DBAPI-specific
schemes that need to use "autocommit" directly with the DBAPI connection,
the same ``.autocommit`` attribute which works with both psycopg2 as well
as pg8000 is available.
.. change::
:tags: bug, oracle
:tickets: 5716
The Oracle dialect now uses
``select sys_context( 'userenv', 'current_schema' ) from dual`` to get
the default schema name, rather than ``SELECT USER FROM DUAL``, to
accommodate for changes to the session-local schema name under Oracle.
.. change::
:tags: schema, feature
:tickets: 5659
Added :meth:`_types.TypeEngine.as_generic` to map dialect-specific types,
such as :class:`sqlalchemy.dialects.mysql.INTEGER`, with the "best match"
generic SQLAlchemy type, in this case :class:`_types.Integer`. Pull
request courtesy Andrew Hannigan.
.. seealso::
:ref:`metadata_reflection_dbagnostic_types` - example usage
.. change::
:tags: bug, sql
:tickets: 5717
Fixed issue where a :class:`.RemovedIn20Warning` would erroneously emit
when the ``.bind`` attribute were accessed internally on objects,
particularly when stringifying a SQL construct.
.. change::
:tags: bug, orm
:tickets: 5781
Fixed 1.4 regression where the use of :meth:`_orm.Query.having` in
conjunction with queries with internally adapted SQL elements (common in
inheritance scenarios) would fail due to an incorrect function call. Pull
request courtesy esoh.
.. change::
:tags: bug, pool, pypy
:tickets: 5842
Fixed issue where connection pool would not return connections to the pool
or otherwise be finalized upon garbage collection under pypy if the checked
out connection fell out of scope without being closed. This is a long
standing issue due to pypy's difference in GC behavior that does not call
weakref finalizers if they are relative to another object that is also
being garbage collected. A strong reference to the related record is now
maintained so that the weakref has a strong-referenced "base" to trigger
off of.
.. change::
:tags: bug, sqlite
:tickets: 5699
Use python ``re.search()`` instead of ``re.match()`` as the operation
used by the :meth:`Column.regexp_match` method when using sqlite.
This matches the behavior of regular expressions on other databases
as well as that of well-known SQLite plugins.
.. change::
:tags: changed, postgresql
Fixed issue where the psycopg2 dialect would silently pass the
``use_native_unicode=False`` flag without actually having any effect under
Python 3, as the psycopg2 DBAPI uses Unicode unconditionally under Python
3. This usage now raises an :class:`_exc.ArgumentError` when used under
Python 3. Added test support for Python 2.
.. change::
:tags: bug, postgresql
:tickets: 5722
:versions: 1.4.0b2
Established support for :class:`_schema.Column` objects as well as ORM
instrumented attributes as keys in the ``set_`` dictionary passed to the
:meth:`_postgresql.Insert.on_conflict_do_update` and
:meth:`_sqlite.Insert.on_conflict_do_update` methods, which match to the
:class:`_schema.Column` objects in the ``.c`` collection of the target
:class:`_schema.Table`. Previously, only string column names were
expected; a column expression would be assumed to be an out-of-table
expression that would render fully along with a warning.
.. change::
:tags: feature, sql
:tickets: 3566
Implemented support for "table valued functions" along with additional
syntaxes supported by PostgreSQL, one of the most commonly requested
features. Table valued functions are SQL functions that return lists of
values or rows, and are prevalent in PostgreSQL in the area of JSON
functions, where the "table value" is commonly referred towards as the
"record" datatype. Table valued functions are also supported by Oracle and
SQL Server.
Features added include:
* the :meth:`_functions.FunctionElement.table_valued` modifier that creates a table-like
selectable object from a SQL function
* A :class:`_sql.TableValuedAlias` construct that renders a SQL function
as a named table
* Support for PostgreSQL's special "derived column" syntax that includes
column names and sometimes datatypes, such as for the
``json_to_recordset`` function, using the
:meth:`_sql.TableValuedAlias.render_derived` method.
* Support for PostgreSQL's "WITH ORDINALITY" construct using the
:paramref:`_functions.FunctionElement.table_valued.with_ordinality` parameter
* Support for selection FROM a SQL function as column-valued scalar, a
syntax supported by PostgreSQL and Oracle, via the
:meth:`_functions.FunctionElement.column_valued` method
* A way to SELECT a single column from a table-valued expression without
using a FROM clause via the :meth:`_functions.FunctionElement.scalar_table_valued`
method.
.. seealso::
:ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
.. change::
:tags: bug, asyncio
:tickets: 5827
Fixed bug in asyncio connection pool where ``asyncio.TimeoutError`` would
be raised rather than :class:`.exc.TimeoutError`. Also repaired the
:paramref:`_sa.create_engine.pool_timeout` parameter set to zero when using
the async engine, which previously would ignore the timeout and block
rather than timing out immediately as is the behavior with regular
:class:`.QueuePool`.
.. change::
:tags: bug, postgresql, asyncio
:tickets: 5824
Fixed bug in asyncpg dialect where a failure during a "commit" or less
likely a "rollback" should cancel the entire transaction; it's no longer
possible to emit rollback. Previously the connection would continue to
await a rollback that could not succeed as asyncpg would reject it.
.. change::
:tags: bug, orm
Fixed an issue where the API to create a custom executable SQL construct
using the ``sqlalchemy.ext.compiles`` extension according to the
documentation that's been up for many years would no longer function if
only ``Executable, ClauseElement`` were used as the base classes,
additional classes were needed if wanting to use
:meth:`_orm.Session.execute`. This has been resolved so that those extra
classes aren't needed.
.. change::
:tags: bug, regression, orm
:tickets: 5867
Fixed ORM unit of work regression where an errant "assert primary_key"
statement interferes with primary key generation sequences that don't
actually consider the columns in the table to use a real primary key
constraint, instead using :paramref:`_orm.mapper.primary_key` to establish
certain columns as "primary".
.. change::
:tags: bug, sql
:tickets: 5722
:versions: 1.4.0b2
Properly render ``cycle=False`` and ``order=False`` as ``NO CYCLE`` and
``NO ORDER`` in :class:`_sql.Sequence` and :class:`_sql.Identity`
objects.
.. change::
:tags: schema, usecase
:tickets: 2843
Added parameters :paramref:`_ddl.CreateTable.if_not_exists`,
:paramref:`_ddl.CreateIndex.if_not_exists`,
:paramref:`_ddl.DropTable.if_exists` and
:paramref:`_ddl.DropIndex.if_exists` to the :class:`_ddl.CreateTable`,
:class:`_ddl.DropTable`, :class:`_ddl.CreateIndex` and
:class:`_ddl.DropIndex` constructs which result in "IF NOT EXISTS" / "IF
EXISTS" DDL being added to the CREATE/DROP. These phrases are not accepted
by all databases and the operation will fail on a database that does not
support it as there is no similarly compatible fallback within the scope of
a single DDL statement. Pull request courtesy Ramon Williams.
.. change::
:tags: bug, pool, asyncio
:tickets: 5823
When using an asyncio engine, the connection pool will now detach and
discard a pooled connection that is was not explicitly closed/returned to
the pool when its tracking object is garbage collected, emitting a warning
that the connection was not properly closed. As this operation occurs
during Python gc finalizers, it's not safe to run any IO operations upon
the connection including transaction rollback or connection close as this
will often be outside of the event loop.
The ``AsyncAdaptedQueue`` used by default on async dpapis
should instantiate a queue only when it's first used
to avoid binding it to a possibly wrong event loop.
.. changelog::
```
### 1.4.0b1
```
:released: March 15, 2021
:released: November 2, 2020
.. change::
:tags: feature, orm
:tickets: 5159
The ORM can now generate queries previously only available when using
:class:`_orm.Query` using the :func:`_sql.select` construct directly.
A new system by which ORM "plugins" may establish themselves within a
Core :class:`_sql.Select` allow the majority of query building logic
previously inside of :class:`_orm.Query` to now take place within
a compilation-level extension for :class:`_sql.Select`. Similar changes
have been made for the :class:`_sql.Update` and :class:`_sql.Delete`
constructs as well. The constructs when invoked using :meth:`_orm.Session.execute`
now do ORM-related work within the method. For :class:`_sql.Select`,
the :class:`_engine.Result` object returned now contains ORM-level
entities and results.
.. seealso::
:ref:`change_5159`
.. change::
:tags: feature,sql
:tickets: 4737
Added "from linting" as a built-in feature to the SQL compiler. This
allows the compiler to maintain graph of all the FROM clauses in a
particular SELECT statement, linked by criteria in either the WHERE
or in JOIN clauses that link these FROM clauses together. If any two
FROM clauses have no path between them, a warning is emitted that the
query may be producing a cartesian product. As the Core expression
language as well as the ORM are built on an "implicit FROMs" model where
a particular FROM clause is automatically added if any part of the query
refers to it, it is easy for this to happen inadvertently and it is
hoped that the new feature helps with this issue.
.. seealso::
:ref:`change_4737`
.. change::
:tags: deprecated, orm
:tickets: 5606
The "slice index" feature used by :class:`_orm.Query` as well as by the
dynamic relationship loader will no longer accept negative indexes in
SQLAlchemy 2.0. These operations do not work efficiently and load the
entire collection in, which is both surprising and undesirable. These
will warn in 1.4 unless the :paramref:`_orm.Session.future` flag is set in
which case they will raise IndexError.
.. change::
:tags: sql, change
:tickets: 4617
The "clause coercion" system, which is SQLAlchemy Core's system of receiving
arguments and resolving them into :class:`_expression.ClauseElement` structures in order
to build up SQL expression objects, has been rewritten from a series of
ad-hoc functions to a fully consistent class-based system. This change
is internal and should have no impact on end users other than more specific
error messages when the wrong kind of argument is passed to an expression
object, however the change is part of a larger set of changes involving
the role and behavior of :func:`_expression.select` objects.
.. change::
:tags: bug, mysql
The MySQL and MariaDB dialects now query from the information_schema.tables
system view in order to determine if a particular table exists or not.
Previously, the "DESCRIBE" command was used with an exception catch to
detect non-existent, which would have the undesirable effect of emitting a
ROLLBACK on the connection. There appeared to be legacy encoding issues
which prevented the use of "SHOW TABLES", for this, but as MySQL support is
now at 5.0.2 or above due to :ticket:`4189`, the information_schema tables
are now available in all cases.
.. change::
:tags: bug, orm
:tickets: 5122
A query that is against a mapped inheritance subclass which also uses
:meth:`_query.Query.select_entity_from` or a similar technique in order to
provide an existing subquery to SELECT from, will now raise an error if the
given subquery returns entities that do not correspond to the given
subclass, that is, they are sibling or superclasses in the same hierarchy.
Previously, these would be returned without error. Additionally, if the
inheritance mapping is a single-inheritance mapping, the given subquery
must apply the appropriate filtering against the polymorphic discriminator
column in order to avoid this error; previously, the :class:`_query.Query` would
add this criteria to the outside query however this interferes with some
kinds of query that return other kinds of entities as well.
.. seealso::
:ref:`change_5122`
.. change::
:tags: bug, engine
:tickets: 5004
Revised the :paramref:`.Connection.execution_options.schema_translate_map`
feature such that the processing of the SQL statement to receive a specific
schema name occurs within the execution phase of the statement, rather than
at the compile phase. This is to support the statement being efficiently
cached. Previously, the current schema being rendered into the statement
for a particular run would be considered as part of the cache key itself,
meaning that for a run against hundreds of schemas, there would be hundreds
of cache keys, rendering the cache much less performant. The new behavior
is that the rendering is done in a similar manner as the "post compile"
rendering added in 1.4 as part of :ticket:`4645`, :ticket:`4808`.
.. change::
:tags: usecase, sql
:tickets: 527
The :meth:`.Index.create` and :meth:`.Index.drop` methods now have a
parameter :paramref:`.Index.create.checkfirst`, in the same way as that of
:class:`_schema.Table` and :class:`.Sequence`, which when enabled will cause the
operation to detect if the index exists (or not) before performing a create
or drop operation.
.. change::
:tags: sql, postgresql
:tickets: 5498
Allow specifying the data type when creating a :class:`.Sequence` in
PostgreSQL by using the parameter :paramref:`.Sequence.data_type`.
.. change::
:tags: change, mssql
:tickets: 5084
SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather
than using a window function, for SQL Server versions 11 and higher. TOP is
still used for a query that features only LIMIT. Pull request courtesy
Elkin.
.. change::
:tags: deprecated, engine
:tickets: 5526
The :class:`_engine.URL` object is now an immutable named tuple. To modify
a URL object, use the :meth:`_engine.URL.set` method to produce a new URL
object.
.. seealso::
:ref:`change_5526` - notes on migration
.. change::
:tags: change, postgresql
When using the psycopg2 dialect for PostgreSQL, psycopg2 minimum version is
set at 2.7. The psycopg2 dialect relies upon many features of psycopg2
released in the past few years, so to simplify the dialect, version 2.7,
released in March, 2017 is now the minimum version required.
.. change::
:tags: usecase, sql
The :func:`.true` and :func:`.false` operators may now be applied as the
"onclause" of a :func:`_expression.join` on a backend that does not support
"native boolean" expressions, e.g. Oracle or SQL Server, and the expression
will render as "1=1" for true and "1=0" false. This is the behavior that
was introduced many years ago in :ticket:`2804` for and/or expressions.
.. change::
:tags: feature, engine
:tickets: 5087, 4395, 4959
Implemented an all-new :class:`_result.Result` object that replaces the previous
``ResultProxy`` object. As implemented in Core, the subclass
:class:`_result.CursorResult` features a compatible calling interface with the
previous ``ResultProxy``, and additionally adds a great amount of new
functionality that can be applied to Core result sets as well as ORM result
sets, which are now integrated into the same model. :class:`_result.Result`
includes features such as column selection and rearrangement, improved
fetchmany patterns, uniquing, as well as a variety of implementations that
can be used to create database results from in-memory structures as well.
.. seealso::
:ref:`change_result_14_core`
.. change::
:tags: renamed, engine
:tickets: 5244
The :meth:`_reflection.Inspector.reflecttable` was renamed to
:meth:`_reflection.Inspector.reflect_table`.
.. change::
:tags: change, orm
:tickets: 4662
The condition where a pending object being flushed with an identity that
already exists in the identity map has been adjusted to emit a warning,
rather than throw a :class:`.FlushError`. The rationale is so that the
flush will proceed and raise a :class:`.IntegrityError` instead, in the
same way as if the existing object were not present in the identity map
already. This helps with schemes that are using the
:class:`.IntegrityError` as a means of catching whether or not a row
already exists in the table.
.. seealso::
:ref:`change_4662`
.. change::
:tags: bug, sql
:tickets: 5001
Fixed issue where when constructing constraints from ORM-bound columns,
primarily :class:`_schema.ForeignKey` objects but also :class:`.UniqueConstraint`,
:class:`.CheckConstraint` and others, the ORM-level
:class:`.InstrumentedAttribute` is discarded entirely, and all ORM-level
annotations from the columns are removed; this is so that the constraints
are still fully pickleable without the ORM-level entities being pulled in.
These annotations are not necessary to be present at the schema/metadata
level.
.. change::
:tags: bug, mysql
:tickets: 5568
The "skip_locked" keyword used with ``with_for_update()`` will render "SKIP
LOCKED" on all MySQL backends, meaning it will fail for MySQL less than
version 8 and on current MariaDB backends. This is because those backends
do not support "SKIP LOCKED" or any equivalent, so this error should not be
silently ignored. This is upgraded from a warning in the 1.3 series.
.. change::
:tags: performance, postgresql
:tickets: 5401
The psycopg2 dialect now defaults to using the very performant
``execute_values()`` psycopg2 extension for compiled INSERT statements,
and also implements RETURNING support when this extension is used. This
allows INSERT statements that even include an autoincremented SERIAL
or IDENTITY value to run very fast while still being able to return the
newly generated primary key values. The ORM will then integrate this
new feature in a separate change.
.. seealso::
:ref:`change_5401` - full list of changes regarding the
``executemany_mode`` parameter.
.. change::
:tags: feature, orm
:tickets: 4472
Added the ability to add arbitrary criteria to the ON clause generated
by a relationship attribute in a query, which applies to methods such
as :meth:`_query.Query.join` as well as loader options like
:func:`_orm.joinedload`. Additionally, a "global" version of the option
allows limiting criteria to be applied to particular entities in
a query globally.
.. seealso::
:ref:`loader_option_criteria`
:ref:`do_orm_execute_global_criteria`
:func:`_orm.with_loader_criteria`
.. change::
:tags: renamed, sql
:class:`_schema.Table` parameter ``mustexist`` has been renamed
to :paramref:`_schema.Table.must_exist` and will now warn when used.
.. change::
:tags: removed, sql
:tickets: 4632
The "threadlocal" execution strategy, deprecated in 1.3, has been
removed for 1.4, as well as the concept of "engine strategies" and the
``Engine.contextual_connect`` method. The "strategy='mock'" keyword
argument is still accepted for now with a deprecation warning; use
:func:`.create_mock_engine` instead for this use case.
.. seealso::
:ref:`change_4393_threadlocal` - from the 1.3 migration notes which
discusses the rationale for deprecation.
.. change::
:tags: mssql, postgresql, reflection, schema, usecase
:tickets: 4458
Improved support for covering indexes (with INCLUDE columns). Added the
ability for postgresql to render CREAT
pyup-bot
commented
3 years ago
This PR updates SQLAlchemy from 1.3.10 to 1.4.1.
Changelog
### 1.4.1 ``` :released: March 17, 2021 .. change:: :tags: bug, orm, regression :tickets: 6066 Fixed regression where producing a Core expression construct such as :func:`_sql.select` using ORM entities would eagerly configure the mappers, in an effort to maintain compatibility with the :class:`_orm.Query` object which necessarily does this to support many backref-related legacy cases. However, core :func:`_sql.select` constructs are also used in mapper configurations and such, and to that degree this eager configuration is more of an inconvenience, so eager configure has been disabled for the :func:`_sql.select` and other Core constructs in the absence of ORM loading types of functions such as :class:`_orm.Load`. The change maintains the behavior of :class:`_orm.Query` so that backwards compatibility is maintained. However, when using a :func:`_sql.select` in conjunction with ORM entities, a "backref" that isn't explicitly placed on one of the classes until mapper configure time won't be available unless :func:`_orm.configure_mappers` or the newer :func:`_orm.registry.configure` has been called elsewhere. Prefer using :paramref:`_orm.relationship.back_populates` for more explicit relationship configuration which does not have the eager configure requirement. .. change:: :tags: bug, mssql, regression :tickets: 6058 Fixed regression where a new setinputsizes() API that's available for pyodbc was enabled, which is apparently incompatible with pyodbc's fast_executemany() mode in the absence of more accurate typing information, which as of yet is not fully implemented or tested. The pyodbc dialect and connector has been modified so that setinputsizes() is not used at all unless the parameter ``use_setinputsizes`` is passed to the dialect, e.g. via :func:`_sa.create_engine`, at which point its behavior can be customized using the :meth:`.DialectEvents.do_setinputsizes` hook. .. seealso:: :ref:`mssql_pyodbc_setinputsizes` .. change:: :tags: bug, orm, regression :tickets: 6055 Fixed a critical regression in the relationship lazy loader where the SQL criteria used to fetch a related many-to-one object could go stale in relation to other memoized structures within the loader if the mapper had configuration changes, such as can occur when mappers are late configured or configured on demand, producing a comparison to None and returning no object. Huge thanks to Alan Hamlett for their help tracking this down late into the night. .. change:: :tags: bug, regression :tickets: 6068 Added back ``items`` and ``values`` to ``ColumnCollection`` class. The regression was introduced while adding support for duplicate columns in from clauses and selectable in ticket 4753. .. change:: :tags: bug, engine, regression :tickets: 6074 The Python ``namedtuple()`` has the behavior such that the names ``count`` and ``index`` will be served as tuple values if the named tuple includes those names; if they are absent, then their behavior as methods of ``collections.abc.Sequence`` is maintained. Therefore the :class:`_result.Row` and :class:`_result.LegacyRow` classes have been fixed so that they work in this same way, maintaining the expected behavior for database rows that have columns named "index" or "count". .. change:: :tags: bug, orm, regression :tickets: 6076 Fixed regression where the :meth:`_orm.Query.exists` method would fail to create an expression if the entity list of the :class:`_orm.Query` were an arbitrary SQL column expression. .. change:: :tags: bug, orm, regression :tickets: 6052 Fixed regression where calling upon :meth:`_orm.Query.count` in conjunction with a loader option such as :func:`_orm.joinedload` would fail to ignore the loader option. This is a behavior that has always been very specific to the :meth:`_orm.Query.count` method; an error is normally raised if a given :class:`_orm.Query` has options that don't apply to what it is returning. .. change:: :tags: bug, orm, declarative, regression :tickets: 6054 Fixed bug where user-mapped classes that contained an attribute named "registry" would cause conflicts with the new registry-based mapping system when using :class:`.DeclarativeMeta`. While the attribute remains something that can be set explicitly on a declarative base to be consumed by the metaclass, once located it is placed under a private class variable so it does not conflict with future subclasses that use the same name for other purposes. .. change:: :tags: bug, orm, regression :tickets: 6067 Fixed regression in :meth:`_orm.Session.identity_key`, including that the method and related methods were not covered by any unit test as well as that the method contained a typo preventing it from functioning correctly. .. changelog:: ``` ### 1.4.0 ``` :released: March 15, 2021 .. change:: :tags: bug, mssql :tickets: 5919 Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes. .. change:: :tags: feature, mypy :tickets: 4609 Rudimentary and experimental support for Mypy has been added in the form of a new plugin, which itself depends on new typing stubs for SQLAlchemy. The plugin allows declarative mappings in their standard form to both be compatible with Mypy as well as to provide typing support for mapped classes and instances. .. seealso:: :ref:`mypy_toplevel` .. change:: :tags: bug, sql :tickets: 6016 Fixed bug where the "percent escaping" feature that occurs with dialects that use the "format" or "pyformat" bound parameter styles was not enabled for the :meth:`_sql.Operators.op` and :class:`_sql.custom_op` constructs, for custom operators that use percent signs. The percent sign will now be automatically doubled based on the paramstyle as necessary. .. change:: :tags: bug, regression, sql :tickets: 5979 Fixed regression where the "unsupported compilation error" for unknown datatypes would fail to raise correctly. .. change:: :tags: ext, usecase :tickets: 5942 Add new parameter :paramref:`_automap.AutomapBase.prepare.reflection_options` to allow passing of :meth:`_schema.MetaData.reflect` options like ``only`` or dialect-specific reflection options like ``oracle_resolve_synonyms``. .. change:: :tags: change, sql Altered the compilation for the :class:`.CTE` construct so that a string is returned representing the inner SELECT statement if the :class:`.CTE` is stringified directly, outside of the context of an enclosing SELECT; This is the same behavior of :meth:`_sql.FromClause.alias` and :meth:`_sql.Select.subquery`. Previously, a blank string would be returned as the CTE is normally placed above a SELECT after that SELECT has been generated, which is generally misleading when debugging. .. change:: :tags: bug, orm :tickets: 5981 Fixed regression where the :paramref:`_orm.relationship.query_class` parameter stopped being functional for "dynamic" relationships. The ``AppenderQuery`` remains dependent on the legacy :class:`_orm.Query` class; users are encouraged to migrate from the use of "dynamic" relationships to using :func:`_orm.with_parent` instead. .. change:: :tags: bug, orm, regression :tickets: 6003 Fixed regression where :meth:`_orm.Query.join` would produce no effect if the query itself as well as the join target were against a :class:`_schema.Table` object, rather than a mapped class. This was part of a more systemic issue where the legacy ORM query compiler would not be correctly used from a :class:`_orm.Query` if the statement produced had not ORM entities present within it. .. change:: :tags: bug, regression, sql :tickets: 6008 Fixed regression where usage of the standalone :func:`_sql.distinct()` used in the form of being directly SELECTed would fail to be locatable in the result set by column identity, which is how the ORM locates columns. While standalone :func:`_sql.distinct()` is not oriented towards being directly SELECTed (use :meth:`_sql.select.distinct` for a regular ``SELECT DISTINCT..``) , it was usable to a limited extent in this way previously (but wouldn't work in subqueries, for example). The column targeting for unary expressions such as "DISTINCT <col>" has been improved so that this case works again, and an additional improvement has been made so that usage of this form in a subquery at least generates valid SQL which was not the case previously. The change additionally enhances the ability to target elements in ``row._mapping`` based on SQL expression objects in ORM-enabled SELECT statements, including whether the statement was invoked by ``connection.execute()`` or ``session.execute()``. .. change:: :tags: bug, orm, asyncio :tickets: 5998 The API for :meth:`_asyncio.AsyncSession.delete` is now an awaitable; this method cascades along relationships which must be loaded in a similar manner as the :meth:`_asyncio.AsyncSession.merge` method. .. change:: :tags: usecase, postgresql, mysql, asyncio :tickets: 5967 Added an ``asyncio.Lock()`` within SQLAlchemy's emulated DBAPI cursor, local to the connection, for the asyncpg and aiomysql dialects for the scope of the ``cursor.execute()`` and ``cursor.executemany()`` methods. The rationale is to prevent failures and corruption for the case where the connection is used in multiple awaitables at once. While this use case can also occur with threaded code and non-asyncio dialects, we anticipate this kind of use will be more common under asyncio, as the asyncio API is encouraging of such use. It's definitely better to use a distinct connection per concurrent awaitable however as concurrency will not be achieved otherwise. For the asyncpg dialect, this is so that the space between the call to ``prepare()`` and ``fetch()`` is prevented from allowing concurrent executions on the connection from causing interface error exceptions, as well as preventing race conditions when starting a new transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level so this intends to provide a similar behavior, outside the realm of server side cursors. For the aiomysql dialect, the mutex will provide safety such that the statement execution and the result set fetch, which are two distinct steps at the connection level, won't get corrupted by concurrent executions on the same connection. .. change:: :tags: bug, engine :tickets: 6002 Improved engine logging to note ROLLBACK and COMMIT which is logged while the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library level and do not have any effect when AUTOCOMMIT is in effect, however it's still worthwhile to log as these indicate where SQLAlchemy sees the "transaction" demarcation. .. change:: :tags: bug, regression, engine :tickets: 6004 Fixed a regression where the "reset agent" of the connection pool wasn't really being utilized by the :class:`_engine.Connection` when it were closed, and also leading to a double-rollback scenario that was somewhat wasteful. The newer architecture of the engine has been updated so that the connection pool "reset-on-return" logic will be skipped when the :class:`_engine.Connection` explicitly closes out the transaction before returning the pool to the connection. .. change:: :tags: bug, schema :tickets: 5953 Deprecated all schema-level ``.copy()`` methods and renamed to ``_copy()``. These are not standard Python "copy()" methods as they typically rely upon being instantiated within particular contexts which are passed to the method as optional keyword arguments. The :meth:`_schema.Table.tometadata` method is the public API that provides copying for :class:`_schema.Table` objects. .. change:: :tags: bug, ext :tickets: 6020 The ``sqlalchemy.ext.mutable`` extension now tracks the "parents" collection using the :class:`.InstanceState` associated with objects, rather than the object itself. The latter approach required that the object be hashable so that it can be inside of a ``WeakKeyDictionary``, which goes against the behavioral contract of the ORM overall which is that ORM mapped objects do not need to provide any particular kind of ``__hash__()`` method and that unhashable objects are supported. .. change:: :tags: bug, orm :tickets: 5984 The unit of work process now turns off all "lazy='raise'" behavior altogether when a flush is proceeding. While there are areas where the UOW is sometimes loading things that aren't ultimately needed, the lazy="raise" strategy is not helpful here as the user often does not have much control or visibility into the flush process. .. changelog:: ``` ### 1.4.0b3 ``` :released: March 15, 2021 :released: February 15, 2021 .. change:: :tags: bug, orm :tickets: 5933 Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label style would not be preserved in the keys used by result rows; this has been applied to all combinations of Core/ORM columns / session vs. connection etc. so that the linkage from statement to result row is the same in all cases. As part of this change, the labeling of column expressions in rows has been improved to retain the original name of the ORM attribute even if used in a subquery. .. change:: :tags: bug, sql :tickets: 5924 Fixed bug where the "cartesian product" assertion was not correctly accommodating for joins between tables that relied upon the use of LATERAL to connect from a subquery to another subquery in the enclosing context. .. change:: :tags: bug, sql :tickets: 5934 Fixed 1.4 regression where the :meth:`_functions.Function.in_` method was not covered by tests and failed to function properly in all cases. .. change:: :tags: bug, engine, postgresql :tickets: 5941 Continued with the improvement made as part of :ticket:`5653` to further support bound parameter names, including those generated against column names, for names that include colons, parenthesis, and question marks, as well as improved test support, so that bound parameter names even if they are auto-derived from column names should have no problem including for parenthesis in psycopg2's "pyformat" style. As part of this change, the format used by the asyncpg DBAPI adapter (which is local to SQLAlchemy's asyncpg dialect) has been changed from using "qmark" paramstyle to "format", as there is a standard and internally supported SQL string escaping style for names that use percent signs with "format" style (i.e. to double percent signs), as opposed to names that use question marks with "qmark" style (where an escaping system is not defined by pep-249 or Python). .. seealso:: :ref:`change_5941` .. change:: :tags: sql, usecase, postgresql, sqlite :tickets: 5939 Enhance ``set_`` keyword of :class:`.OnConflictDoUpdate` to accept a :class:`.ColumnCollection`, such as the ``.c.`` collection from a :class:`Selectable`, or the ``.excluded`` contextual object. .. change:: :tags: feature, orm The ORM used in :term:`2.0 style` can now return ORM objects from the rows returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by supplying the construct to :meth:`_sql.Select.from_statement` in an ORM context. .. seealso:: :ref:`orm_dml_returning_objects` .. change:: :tags: bug, sql :tickets: 5935 Fixed regression where use of an arbitrary iterable with the :func:`_sql.select` function was not working, outside of plain lists. The forwards/backwards compatibility logic here now checks for a wider range of incoming "iterable" types including that a ``.c`` collection from a selectable can be passed directly. Pull request compliments of Oliver Rice. .. changelog:: ``` ### 1.4.0b2 ``` :released: March 15, 2021 :released: February 3, 2021 .. change:: :tags: usecase, sql :tickets: 5695 Multiple calls to "returning", e.g. :meth:`_sql.Insert.returning`, may now be chained to add new columns to the RETURNING clause. .. change:: :tags: bug, asyncio :tickets: 5615 Adjusted the greenlet integration, which provides support for Python asyncio in SQLAlchemy, to accommodate for the handling of Python ``contextvars`` (introduced in Python 3.7) for ``greenlet`` versions greater than 0.4.17. Greenlet version 0.4.17 added automatic handling of contextvars in a backwards-incompatible way; we've coordinated with the greenlet authors to add a preferred API for this in versions subsequent to 0.4.17 which is now supported by SQLAlchemy's greenlet integration. For greenlet versions prior to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked from the dependencies. .. change:: :tags: bug, engine, sqlite :tickets: 5845 Fixed bug in the 2.0 "future" version of :class:`.Engine` where emitting SQL during the :meth:`.EngineEvents.begin` event hook would cause a re-entrant (recursive) condition due to autobegin, affecting among other things the recipe documented for SQLite to allow for savepoints and serializable isolation support. .. change:: :tags: bug, orm, regression :tickets: 5845 Fixed issue in new :class:`_orm.Session` similar to that of the :class:`_engine.Connection` where the new "autobegin" logic could be tripped into a re-entrant (recursive) state if SQL were executed within the :meth:`.SessionEvents.after_transaction_create` event hook. .. change:: :tags: sql :tickets: 4757 Replace :meth:`_orm.Query.with_labels` and :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and setters :meth:`_sql.GenerativeSelect.get_label_style` and :meth:`_sql.GenerativeSelect.set_label_style` to accommodate the three supported label styles: :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and :data:`_sql.LABEL_STYLE_NONE`. In addition, for Core and "future style" ORM queries, ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This style differs from the existing "no labels" style in that labeling is applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a duplicate column name is not accessible via name in any case. For cases where labeling is significant, namely that the ``.c`` collection of a subquery is able to refer to all columns unambiguously, the behavior of ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now sufficient for all SQLAlchemy features across Core and ORM which involve this behavior. Result set rows since SQLAlchemy 1.0 are usually aligned with column constructs positionally. For legacy ORM queries using :class:`_query.Query`, the table-plus-column names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` continues to be used so that existing test suites and logging facilities see no change in behavior by default. .. change:: :tags: bug, orm, unitofwork :tickets: 5735 Improved the unit of work topological sorting system such that the toplogical sort is now deterministic based on the sorting of the input set, which itself is now sorted at the level of mappers, so that the same inputs of affected mappers should produce the same output every time, among mappers / tables that don't have any dependency on each other. This further reduces the chance of deadlocks as can be observed in a flush that UPDATEs among multiple, unrelated tables such that row locks are generated. .. change:: :tags: changed, orm :tickets: 5897 Mapper "configuration", which occurs within the :func:`_orm.configure_mappers` function, is now organized to be on a per-registry basis. This allows for example the mappers within a certain declarative base to be configured, but not those of another base that is also present in memory. The goal is to provide a means of reducing application startup time by only running the "configure" process for sets of mappers that are needed. This also adds the :meth:`_orm.registry.configure` method that will run configure for the mappers local in a particular registry only. .. change:: :tags: bug, orm :tickets: 5702 Fixed regression where the :paramref:`.Bundle.single_entity` flag would take effect for a :class:`.Bundle` even though it were not set. Additionally, this flag is legacy as it only makes sense for the :class:`_orm.Query` object and not 2.0 style execution. a deprecation warning is emitted when used with new-style execution. .. change:: :tags: bug, sql :tickets: 5858 Fixed issue in new :meth:`_sql.Select.join` method where chaining from the current JOIN wasn't looking at the right state, causing an expression like "FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than "FROM a JOIN b <onclause> JOIN c <onclause>". .. change:: :tags: usecase, sql Added :meth:`_sql.Select.outerjoin_from` method to complement :meth:`_sql.Select.join_from`. .. change:: :tags: usecase, sql :tickets: 5888 Adjusted the "literal_binds" feature of :class:`_sql.Compiler` to render NULL for a bound parameter that has ``None`` as the value, either explicitly passed or omitted. The previous error message "bind parameter without a renderable value" is removed, and a missing or ``None`` value will now render NULL in all cases. Previously, rendering of NULL was starting to happen for DML statements due to internal refactorings, but was not explicitly part of test coverage, which it now is. While no error is raised, when the context is within that of a column comparison, and the operator is not "IS"/"IS NOT", a warning is emitted that this is not generally useful from a SQL perspective. .. change:: :tags: bug, orm :tickets: 5750 Fixed regression where creating an :class:`_orm.aliased` construct against a plain selectable and including a name would raise an assertionerror. .. change:: :tags: bug, mssql, mysql, datatypes :tickets: 5788 :versions: 1.4.0b2 Decimal accuracy and behavior has been improved when extracting floating point and/or decimal values from JSON strings using the :meth:`_sql.sqltypes.JSON.Comparator.as_float` method, when the numeric value inside of the JSON string has many significant digits; previously, MySQL backends would truncate values with many significant digits and SQL Server backends would raise an exception due to a DECIMAL cast with insufficient significant digits. Both backends now use a FLOAT-compatible approach that does not hardcode significant digits for floating point values. For precision numerics, a new method :meth:`_sql.sqltypes.JSON.Comparator.as_numeric` has been added which accepts arguments for precision and scale, and will return values as Python ``Decimal`` objects with no floating point conversion assuming the DBAPI supports it (all but pysqlite). .. change:: :tags: feature, orm, declarative :tickets: 5745 Added an alternate resolution scheme to Declarative that will extract the SQLAlchemy column or mapped property from the "metadata" dictionary of a dataclasses.Field object. This allows full declarative mappings to be combined with dataclass fields. .. seealso:: :ref:`orm_declarative_dataclasses_declarative_table` .. change:: :tags: bug, sql :tickets: 5754 Deprecation warnings are emitted under "SQLALCHEMY_WARN_20" mode when passing a plain string to :meth:`_orm.Session.execute`. .. change:: :tags: bug, sql, orm :tickets: 5760, 5763, 5765, 5768, 5770 A wide variety of fixes to the "lambda SQL" feature introduced at :ref:`engine_lambda_caching` have been implemented based on user feedback, with an emphasis on its use within the :func:`_orm.with_loader_criteria` feature where it is most prominently used [ticket:5760]: * fixed issue where boolean True/False values referred towards in the closure variables of the lambda would cause failures [ticket:5763] * Repaired a non-working detection for Python functions embedded in the lambda that produce bound values; this case is likely not supportable so raises an informative error, where the function should be invoked outside the lambda itself. New documentation has been added to further detail this behavior. [ticket:5770] * The lambda system by default now rejects the use of non-SQL elements within the closure variables of the lambda entirely, where the error suggests the two options of either explicitly ignoring closure variables that are not SQL parameters, or specifying a specific set of values to be considered as part of the cache key based on hash value. This critically prevents the lambda system from assuming that arbitrary objects within the lambda's closure are appropriate for caching while also refusing to ignore them by default, preventing the case where their state might not be constant and have an impact on the SQL construct produced. The error message is comprehensive and new documentation has been added to further detail this behavior. [ticket:5765] * Fixed support for the edge case where an ``in_()`` expression against a list of SQL elements, such as :func:`_sql.literal` objects, would fail to be accommodated correctly. [ticket:5768] .. change:: :tags: bug, orm :tickets: 5760, 5766, 5762, 5761, 5764 Related to the fixes for the lambda criteria system within Core, within the ORM implemented a variety of fixes for the :func:`_orm.with_loader_criteria` feature as well as the :meth:`_orm.SessionEvents.do_orm_execute` event handler that is often used in conjunction [ticket:5760]: * fixed issue where :func:`_orm.with_loader_criteria` function would fail if the given entity or base included non-mapped mixins in its descending class hierarchy [ticket:5766] * The :func:`_orm.with_loader_criteria` feature is now unconditionally disabled for the case of ORM "refresh" operations, including loads of deferred or expired column attributes as well as for explicit operations like :meth:`_orm.Session.refresh`. These loads are necessarily based on primary key identity where addiional WHERE criteria is never appropriate. [ticket:5762] * Added new attribute :attr:`_orm.ORMExecuteState.is_column_load` to indicate that a :meth:`_orm.SessionEvents.do_orm_execute` handler that a particular operation is a primary-key-directed column attribute load, where additional criteria should not be added. The :func:`_orm.with_loader_criteria` function as above ignores these in any case now. [ticket:5761] * Fixed issue where the :attr:`_orm.ORMExecuteState.is_relationship_load` attribute would not be set correctly for many lazy loads as well as all selectinloads. The flag is essential in order to test if options should be added to statements or if they would already have been propagated via relationship loads. [ticket:5764] .. change:: :tags: usecase, orm Added :attr:`_orm.ORMExecuteState.bind_mapper` and :attr:`_orm.ORMExecuteState.all_mappers` accessors to :class:`_orm.ORMExecuteState` event object, so that handlers can respond to the target mapper and/or mapped class or classes involved in an ORM statement execution. .. change:: :tags: bug, engine, postgresql, oracle Adjusted the "setinputsizes" logic relied upon by the cx_Oracle, asyncpg and pg8000 dialects to support a :class:`.TypeDecorator` that includes an override the :meth:`.TypeDecorator.get_dbapi_type()` method. .. change:: :tags: postgresql, performance Enhanced the performance of the asyncpg dialect by caching the asyncpg PreparedStatement objects on a per-connection basis. For a test case that makes use of the same statement on a set of pooled connections this appears to grant a 10-20% speed improvement. The cache size is adjustable and may also be disabled. .. seealso:: :ref:`asyncpg_prepared_statement_cache` .. change:: :tags: feature, mysql :tickets: 5747 Added support for the aiomysql driver when using the asyncio SQLAlchemy extension. .. seealso:: :ref:`aiomysql` .. change:: :tags: bug, reflection :tickets: 5684 Fixed bug where the now-deprecated ``autoload`` parameter was being called internally within the reflection routines when a related table were reflected. .. change:: :tags: platform, performance :tickets: 5681 Adjusted some elements related to internal class production at import time which added significant latency to the time spent to import the library vs. that of 1.3. The time is now about 20-30% slower than 1.3 instead of 200%. .. change:: :tags: changed, schema :tickets: 5775 Altered the behavior of the :class:`_schema.Identity` construct such that when applied to a :class:`_schema.Column`, it will automatically imply that the value of :paramref:`_sql.Column.nullable` should default to ``False``, in a similar manner as when the :paramref:`_sql.Column.primary_key` parameter is set to ``True``. This matches the default behavior of all supporting databases where ``IDENTITY`` implies ``NOT NULL``. The PostgreSQL backend is the only one that supports adding ``NULL`` to an ``IDENTITY`` column, which is here supported by passing a ``True`` value for the :paramref:`_sql.Column.nullable` parameter at the same time. .. change:: :tags: bug, postgresql :tickets: 5698 Fixed a small regression where the query for "show standard_conforming_strings" upon initialization would be emitted even if the server version info were detected as less than version 8.2, previously it would only occur for server version 8.2 or greater. The query fails on Amazon Redshift which reports a PG server version older than this value. .. change:: :tags: bug, sql, postgresql, mysql, sqlite :tickets: 5169 An informative error message is now raised for a selected set of DML methods (currently all part of :class:`_dml.Insert` constructs) if they are called a second time, which would implicitly cancel out the previous setting. The methods altered include: :class:`_sqlite.Insert.on_conflict_do_update`, :class:`_sqlite.Insert.on_conflict_do_nothing` (SQLite), :class:`_postgresql.Insert.on_conflict_do_update`, :class:`_postgresql.Insert.on_conflict_do_nothing` (PostgreSQL), :class:`_mysql.Insert.on_duplicate_key_update` (MySQL) .. change:: :tags: pool, tests, usecase :tickets: 5582 Improve documentation and add test for sub-second pool timeouts. Pull request courtesy Jordan Pittier. .. change:: :tags: bug, general Fixed a SQLite source file that had non-ascii characters inside of its docstring without a source encoding, introduced within the "INSERT..ON CONFLICT" feature, which would cause failures under Python 2. .. change:: :tags: sqlite, usecase :tickets: 4010 Implemented INSERT... ON CONFLICT clause for SQLite. Pull request courtesy Ramon Williams. .. seealso:: :ref:`sqlite_on_conflict_insert` .. change:: :tags: bug, asyncio :tickets: 5811 Implemented "connection-binding" for :class:`.AsyncSession`, the ability to pass an :class:`.AsyncConnection` to create an :class:`.AsyncSession`. Previously, this use case was not implemented and would use the associated engine when the connection were passed. This fixes the issue where the "join a session to an external transaction" use case would not work correctly for the :class:`.AsyncSession`. Additionally, added methods :meth:`.AsyncConnection.in_transaction`, :meth:`.AsyncConnection.in_nested_transaction`, :meth:`.AsyncConnection.get_transaction`, :meth:`.AsyncConnection.get_nested_transaction` and :attr:`.AsyncConnection.info` attribute. .. change:: :tags: usecase, asyncio The :class:`.AsyncEngine`, :class:`.AsyncConnection` and :class:`.AsyncTransaction` objects may be compared using Python ``==`` or ``!=``, which will compare the two given objects based on the "sync" object they are proxying towards. This is useful as there are cases particularly for :class:`.AsyncTransaction` where multiple instances of :class:`.AsyncTransaction` can be proxying towards the same sync :class:`_engine.Transaction`, and are actually equivalent. The :meth:`.AsyncConnection.get_transaction` method will currently return a new proxying :class:`.AsyncTransaction` each time as the :class:`.AsyncTransaction` is not otherwise statefully associated with its originating :class:`.AsyncConnection`. .. change:: :tags: bug, oracle :tickets: 5884 Oracle two-phase transactions at a rudimentary level are now no longer deprecated. After receiving support from cx_Oracle devs we can provide for basic xid + begin/prepare support with some limitations, which will work more fully in an upcoming release of cx_Oracle. Two phase "recovery" is not currently supported. .. change:: :tags: asyncio The SQLAlchemy async mode now detects and raises an informative error when an non asyncio compatible :term:`DBAPI` is used. Using a standard ``DBAPI`` with async SQLAlchemy will cause it to block like any sync call, interrupting the executing asyncio loop. .. change:: :tags: usecase, orm, asyncio :tickets: 5796, 5797, 5802 Added :meth:`_asyncio.AsyncSession.scalar`, :meth:`_asyncio.AsyncSession.get` as well as support for :meth:`_orm.sessionmaker.begin` to work as an async context manager with :class:`_asyncio.AsyncSession`. Also added :meth:`_asyncio.AsyncSession.in_transaction` accessor. .. change:: :tags: bug, sql :tickets: 5785 Fixed issue in new :class:`_sql.Values` construct where passing tuples of objects would fall back to per-value type detection rather than making use of the :class:`_schema.Column` objects passed directly to :class:`_sql.Values` that tells SQLAlchemy what the expected type is. This would lead to issues for objects such as enumerations and numpy strings that are not actually necessary since the expected type is given. .. change:: :tags: bug, engine Added the "future" keyword to the list of words that are known by the :func:`_sa.engine_from_config` function, so that the values "true" and "false" may be configured as "boolean" values when using a key such as ``sqlalchemy.future = true`` or ``sqlalchemy.future = false``. .. change:: :tags: usecase, schema :tickets: 5712 The :meth:`_events.DDLEvents.column_reflect` event may now be applied to a :class:`_schema.MetaData` object where it will take effect for the :class:`_schema.Table` objects local to that collection. .. seealso:: :meth:`_events.DDLEvents.column_reflect` :ref:`mapper_automated_reflection_schemes` - in the ORM mapping documentation :ref:`automap_intercepting_columns` - in the :ref:`automap_toplevel` documentation .. change:: :tags: feature, engine Dialect-specific constructs such as :meth:`_postgresql.Insert.on_conflict_do_update` can now stringify in-place without the need to specify an explicit dialect object. The constructs, when called upon for ``str()``, ``print()``, etc. now have internal direction to call upon their appropriate dialect rather than the "default"dialect which doesn't know how to stringify these. The approach is also adapted to generic schema-level create/drop such as :class:`_schema.AddConstraint`, which will adapt its stringify dialect to one indicated by the element within it, such as the :class:`_postgresql.ExcludeConstraint` object. .. change:: :tags: feature, engine :tickets: 5911 Added new execution option :paramref:`_engine.Connection.execution_options.logging_token`. This option will add an additional per-message token to log messages generated by the :class:`_engine.Connection` as it executes statements. This token is not part of the logger name itself (that part can be affected using the existing :paramref:`_sa.create_engine.logging_name` parameter), so is appropriate for ad-hoc connection use without the side effect of creating many new loggers. The option can be set at the level of :class:`_engine.Connection` or :class:`_engine.Engine`. .. seealso:: :ref:`dbengine_logging_tokens` .. change:: :tags: bug, pool :tickets: 5708 Fixed regression where a connection pool event specified with a keyword, most notably ``insert=True``, would be lost when the event were set up. This would prevent startup events that need to fire before dialect-level events from working correctly. .. change:: :tags: usecase, pool :tickets: 5708, 5497 The internal mechanics of the engine connection routine has been altered such that it's now guaranteed that a user-defined event handler for the :meth:`_pool.PoolEvents.connect` handler, when established using ``insert=True``, will allow an event handler to run that is definitely invoked **before** any dialect-specific initialization starts up, most notably when it does things like detect default schema name. Previously, this would occur in most cases but not unconditionally. A new example is added to the schema documentation illustrating how to establish the "default schema name" within an on-connect event. .. change:: :tags: usecase, postgresql Added a read/write ``.autocommit`` attribute to the DBAPI-adaptation layer for the asyncpg dialect. This so that when working with DBAPI-specific schemes that need to use "autocommit" directly with the DBAPI connection, the same ``.autocommit`` attribute which works with both psycopg2 as well as pg8000 is available. .. change:: :tags: bug, oracle :tickets: 5716 The Oracle dialect now uses ``select sys_context( 'userenv', 'current_schema' ) from dual`` to get the default schema name, rather than ``SELECT USER FROM DUAL``, to accommodate for changes to the session-local schema name under Oracle. .. change:: :tags: schema, feature :tickets: 5659 Added :meth:`_types.TypeEngine.as_generic` to map dialect-specific types, such as :class:`sqlalchemy.dialects.mysql.INTEGER`, with the "best match" generic SQLAlchemy type, in this case :class:`_types.Integer`. Pull request courtesy Andrew Hannigan. .. seealso:: :ref:`metadata_reflection_dbagnostic_types` - example usage .. change:: :tags: bug, sql :tickets: 5717 Fixed issue where a :class:`.RemovedIn20Warning` would erroneously emit when the ``.bind`` attribute were accessed internally on objects, particularly when stringifying a SQL construct. .. change:: :tags: bug, orm :tickets: 5781 Fixed 1.4 regression where the use of :meth:`_orm.Query.having` in conjunction with queries with internally adapted SQL elements (common in inheritance scenarios) would fail due to an incorrect function call. Pull request courtesy esoh. .. change:: :tags: bug, pool, pypy :tickets: 5842 Fixed issue where connection pool would not return connections to the pool or otherwise be finalized upon garbage collection under pypy if the checked out connection fell out of scope without being closed. This is a long standing issue due to pypy's difference in GC behavior that does not call weakref finalizers if they are relative to another object that is also being garbage collected. A strong reference to the related record is now maintained so that the weakref has a strong-referenced "base" to trigger off of. .. change:: :tags: bug, sqlite :tickets: 5699 Use python ``re.search()`` instead of ``re.match()`` as the operation used by the :meth:`Column.regexp_match` method when using sqlite. This matches the behavior of regular expressions on other databases as well as that of well-known SQLite plugins. .. change:: :tags: changed, postgresql Fixed issue where the psycopg2 dialect would silently pass the ``use_native_unicode=False`` flag without actually having any effect under Python 3, as the psycopg2 DBAPI uses Unicode unconditionally under Python 3. This usage now raises an :class:`_exc.ArgumentError` when used under Python 3. Added test support for Python 2. .. change:: :tags: bug, postgresql :tickets: 5722 :versions: 1.4.0b2 Established support for :class:`_schema.Column` objects as well as ORM instrumented attributes as keys in the ``set_`` dictionary passed to the :meth:`_postgresql.Insert.on_conflict_do_update` and :meth:`_sqlite.Insert.on_conflict_do_update` methods, which match to the :class:`_schema.Column` objects in the ``.c`` collection of the target :class:`_schema.Table`. Previously, only string column names were expected; a column expression would be assumed to be an out-of-table expression that would render fully along with a warning. .. change:: :tags: feature, sql :tickets: 3566 Implemented support for "table valued functions" along with additional syntaxes supported by PostgreSQL, one of the most commonly requested features. Table valued functions are SQL functions that return lists of values or rows, and are prevalent in PostgreSQL in the area of JSON functions, where the "table value" is commonly referred towards as the "record" datatype. Table valued functions are also supported by Oracle and SQL Server. Features added include: * the :meth:`_functions.FunctionElement.table_valued` modifier that creates a table-like selectable object from a SQL function * A :class:`_sql.TableValuedAlias` construct that renders a SQL function as a named table * Support for PostgreSQL's special "derived column" syntax that includes column names and sometimes datatypes, such as for the ``json_to_recordset`` function, using the :meth:`_sql.TableValuedAlias.render_derived` method. * Support for PostgreSQL's "WITH ORDINALITY" construct using the :paramref:`_functions.FunctionElement.table_valued.with_ordinality` parameter * Support for selection FROM a SQL function as column-valued scalar, a syntax supported by PostgreSQL and Oracle, via the :meth:`_functions.FunctionElement.column_valued` method * A way to SELECT a single column from a table-valued expression without using a FROM clause via the :meth:`_functions.FunctionElement.scalar_table_valued` method. .. seealso:: :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial` .. change:: :tags: bug, asyncio :tickets: 5827 Fixed bug in asyncio connection pool where ``asyncio.TimeoutError`` would be raised rather than :class:`.exc.TimeoutError`. Also repaired the :paramref:`_sa.create_engine.pool_timeout` parameter set to zero when using the async engine, which previously would ignore the timeout and block rather than timing out immediately as is the behavior with regular :class:`.QueuePool`. .. change:: :tags: bug, postgresql, asyncio :tickets: 5824 Fixed bug in asyncpg dialect where a failure during a "commit" or less likely a "rollback" should cancel the entire transaction; it's no longer possible to emit rollback. Previously the connection would continue to await a rollback that could not succeed as asyncpg would reject it. .. change:: :tags: bug, orm Fixed an issue where the API to create a custom executable SQL construct using the ``sqlalchemy.ext.compiles`` extension according to the documentation that's been up for many years would no longer function if only ``Executable, ClauseElement`` were used as the base classes, additional classes were needed if wanting to use :meth:`_orm.Session.execute`. This has been resolved so that those extra classes aren't needed. .. change:: :tags: bug, regression, orm :tickets: 5867 Fixed ORM unit of work regression where an errant "assert primary_key" statement interferes with primary key generation sequences that don't actually consider the columns in the table to use a real primary key constraint, instead using :paramref:`_orm.mapper.primary_key` to establish certain columns as "primary". .. change:: :tags: bug, sql :tickets: 5722 :versions: 1.4.0b2 Properly render ``cycle=False`` and ``order=False`` as ``NO CYCLE`` and ``NO ORDER`` in :class:`_sql.Sequence` and :class:`_sql.Identity` objects. .. change:: :tags: schema, usecase :tickets: 2843 Added parameters :paramref:`_ddl.CreateTable.if_not_exists`, :paramref:`_ddl.CreateIndex.if_not_exists`, :paramref:`_ddl.DropTable.if_exists` and :paramref:`_ddl.DropIndex.if_exists` to the :class:`_ddl.CreateTable`, :class:`_ddl.DropTable`, :class:`_ddl.CreateIndex` and :class:`_ddl.DropIndex` constructs which result in "IF NOT EXISTS" / "IF EXISTS" DDL being added to the CREATE/DROP. These phrases are not accepted by all databases and the operation will fail on a database that does not support it as there is no similarly compatible fallback within the scope of a single DDL statement. Pull request courtesy Ramon Williams. .. change:: :tags: bug, pool, asyncio :tickets: 5823 When using an asyncio engine, the connection pool will now detach and discard a pooled connection that is was not explicitly closed/returned to the pool when its tracking object is garbage collected, emitting a warning that the connection was not properly closed. As this operation occurs during Python gc finalizers, it's not safe to run any IO operations upon the connection including transaction rollback or connection close as this will often be outside of the event loop. The ``AsyncAdaptedQueue`` used by default on async dpapis should instantiate a queue only when it's first used to avoid binding it to a possibly wrong event loop. .. changelog:: ``` ### 1.4.0b1 ``` :released: March 15, 2021 :released: November 2, 2020 .. change:: :tags: feature, orm :tickets: 5159 The ORM can now generate queries previously only available when using :class:`_orm.Query` using the :func:`_sql.select` construct directly. A new system by which ORM "plugins" may establish themselves within a Core :class:`_sql.Select` allow the majority of query building logic previously inside of :class:`_orm.Query` to now take place within a compilation-level extension for :class:`_sql.Select`. Similar changes have been made for the :class:`_sql.Update` and :class:`_sql.Delete` constructs as well. The constructs when invoked using :meth:`_orm.Session.execute` now do ORM-related work within the method. For :class:`_sql.Select`, the :class:`_engine.Result` object returned now contains ORM-level entities and results. .. seealso:: :ref:`change_5159` .. change:: :tags: feature,sql :tickets: 4737 Added "from linting" as a built-in feature to the SQL compiler. This allows the compiler to maintain graph of all the FROM clauses in a particular SELECT statement, linked by criteria in either the WHERE or in JOIN clauses that link these FROM clauses together. If any two FROM clauses have no path between them, a warning is emitted that the query may be producing a cartesian product. As the Core expression language as well as the ORM are built on an "implicit FROMs" model where a particular FROM clause is automatically added if any part of the query refers to it, it is easy for this to happen inadvertently and it is hoped that the new feature helps with this issue. .. seealso:: :ref:`change_4737` .. change:: :tags: deprecated, orm :tickets: 5606 The "slice index" feature used by :class:`_orm.Query` as well as by the dynamic relationship loader will no longer accept negative indexes in SQLAlchemy 2.0. These operations do not work efficiently and load the entire collection in, which is both surprising and undesirable. These will warn in 1.4 unless the :paramref:`_orm.Session.future` flag is set in which case they will raise IndexError. .. change:: :tags: sql, change :tickets: 4617 The "clause coercion" system, which is SQLAlchemy Core's system of receiving arguments and resolving them into :class:`_expression.ClauseElement` structures in order to build up SQL expression objects, has been rewritten from a series of ad-hoc functions to a fully consistent class-based system. This change is internal and should have no impact on end users other than more specific error messages when the wrong kind of argument is passed to an expression object, however the change is part of a larger set of changes involving the role and behavior of :func:`_expression.select` objects. .. change:: :tags: bug, mysql The MySQL and MariaDB dialects now query from the information_schema.tables system view in order to determine if a particular table exists or not. Previously, the "DESCRIBE" command was used with an exception catch to detect non-existent, which would have the undesirable effect of emitting a ROLLBACK on the connection. There appeared to be legacy encoding issues which prevented the use of "SHOW TABLES", for this, but as MySQL support is now at 5.0.2 or above due to :ticket:`4189`, the information_schema tables are now available in all cases. .. change:: :tags: bug, orm :tickets: 5122 A query that is against a mapped inheritance subclass which also uses :meth:`_query.Query.select_entity_from` or a similar technique in order to provide an existing subquery to SELECT from, will now raise an error if the given subquery returns entities that do not correspond to the given subclass, that is, they are sibling or superclasses in the same hierarchy. Previously, these would be returned without error. Additionally, if the inheritance mapping is a single-inheritance mapping, the given subquery must apply the appropriate filtering against the polymorphic discriminator column in order to avoid this error; previously, the :class:`_query.Query` would add this criteria to the outside query however this interferes with some kinds of query that return other kinds of entities as well. .. seealso:: :ref:`change_5122` .. change:: :tags: bug, engine :tickets: 5004 Revised the :paramref:`.Connection.execution_options.schema_translate_map` feature such that the processing of the SQL statement to receive a specific schema name occurs within the execution phase of the statement, rather than at the compile phase. This is to support the statement being efficiently cached. Previously, the current schema being rendered into the statement for a particular run would be considered as part of the cache key itself, meaning that for a run against hundreds of schemas, there would be hundreds of cache keys, rendering the cache much less performant. The new behavior is that the rendering is done in a similar manner as the "post compile" rendering added in 1.4 as part of :ticket:`4645`, :ticket:`4808`. .. change:: :tags: usecase, sql :tickets: 527 The :meth:`.Index.create` and :meth:`.Index.drop` methods now have a parameter :paramref:`.Index.create.checkfirst`, in the same way as that of :class:`_schema.Table` and :class:`.Sequence`, which when enabled will cause the operation to detect if the index exists (or not) before performing a create or drop operation. .. change:: :tags: sql, postgresql :tickets: 5498 Allow specifying the data type when creating a :class:`.Sequence` in PostgreSQL by using the parameter :paramref:`.Sequence.data_type`. .. change:: :tags: change, mssql :tickets: 5084 SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather than using a window function, for SQL Server versions 11 and higher. TOP is still used for a query that features only LIMIT. Pull request courtesy Elkin. .. change:: :tags: deprecated, engine :tickets: 5526 The :class:`_engine.URL` object is now an immutable named tuple. To modify a URL object, use the :meth:`_engine.URL.set` method to produce a new URL object. .. seealso:: :ref:`change_5526` - notes on migration .. change:: :tags: change, postgresql When using the psycopg2 dialect for PostgreSQL, psycopg2 minimum version is set at 2.7. The psycopg2 dialect relies upon many features of psycopg2 released in the past few years, so to simplify the dialect, version 2.7, released in March, 2017 is now the minimum version required. .. change:: :tags: usecase, sql The :func:`.true` and :func:`.false` operators may now be applied as the "onclause" of a :func:`_expression.join` on a backend that does not support "native boolean" expressions, e.g. Oracle or SQL Server, and the expression will render as "1=1" for true and "1=0" false. This is the behavior that was introduced many years ago in :ticket:`2804` for and/or expressions. .. change:: :tags: feature, engine :tickets: 5087, 4395, 4959 Implemented an all-new :class:`_result.Result` object that replaces the previous ``ResultProxy`` object. As implemented in Core, the subclass :class:`_result.CursorResult` features a compatible calling interface with the previous ``ResultProxy``, and additionally adds a great amount of new functionality that can be applied to Core result sets as well as ORM result sets, which are now integrated into the same model. :class:`_result.Result` includes features such as column selection and rearrangement, improved fetchmany patterns, uniquing, as well as a variety of implementations that can be used to create database results from in-memory structures as well. .. seealso:: :ref:`change_result_14_core` .. change:: :tags: renamed, engine :tickets: 5244 The :meth:`_reflection.Inspector.reflecttable` was renamed to :meth:`_reflection.Inspector.reflect_table`. .. change:: :tags: change, orm :tickets: 4662 The condition where a pending object being flushed with an identity that already exists in the identity map has been adjusted to emit a warning, rather than throw a :class:`.FlushError`. The rationale is so that the flush will proceed and raise a :class:`.IntegrityError` instead, in the same way as if the existing object were not present in the identity map already. This helps with schemes that are using the :class:`.IntegrityError` as a means of catching whether or not a row already exists in the table. .. seealso:: :ref:`change_4662` .. change:: :tags: bug, sql :tickets: 5001 Fixed issue where when constructing constraints from ORM-bound columns, primarily :class:`_schema.ForeignKey` objects but also :class:`.UniqueConstraint`, :class:`.CheckConstraint` and others, the ORM-level :class:`.InstrumentedAttribute` is discarded entirely, and all ORM-level annotations from the columns are removed; this is so that the constraints are still fully pickleable without the ORM-level entities being pulled in. These annotations are not necessary to be present at the schema/metadata level. .. change:: :tags: bug, mysql :tickets: 5568 The "skip_locked" keyword used with ``with_for_update()`` will render "SKIP LOCKED" on all MySQL backends, meaning it will fail for MySQL less than version 8 and on current MariaDB backends. This is because those backends do not support "SKIP LOCKED" or any equivalent, so this error should not be silently ignored. This is upgraded from a warning in the 1.3 series. .. change:: :tags: performance, postgresql :tickets: 5401 The psycopg2 dialect now defaults to using the very performant ``execute_values()`` psycopg2 extension for compiled INSERT statements, and also implements RETURNING support when this extension is used. This allows INSERT statements that even include an autoincremented SERIAL or IDENTITY value to run very fast while still being able to return the newly generated primary key values. The ORM will then integrate this new feature in a separate change. .. seealso:: :ref:`change_5401` - full list of changes regarding the ``executemany_mode`` parameter. .. change:: :tags: feature, orm :tickets: 4472 Added the ability to add arbitrary criteria to the ON clause generated by a relationship attribute in a query, which applies to methods such as :meth:`_query.Query.join` as well as loader options like :func:`_orm.joinedload`. Additionally, a "global" version of the option allows limiting criteria to be applied to particular entities in a query globally. .. seealso:: :ref:`loader_option_criteria` :ref:`do_orm_execute_global_criteria` :func:`_orm.with_loader_criteria` .. change:: :tags: renamed, sql :class:`_schema.Table` parameter ``mustexist`` has been renamed to :paramref:`_schema.Table.must_exist` and will now warn when used. .. change:: :tags: removed, sql :tickets: 4632 The "threadlocal" execution strategy, deprecated in 1.3, has been removed for 1.4, as well as the concept of "engine strategies" and the ``Engine.contextual_connect`` method. The "strategy='mock'" keyword argument is still accepted for now with a deprecation warning; use :func:`.create_mock_engine` instead for this use case. .. seealso:: :ref:`change_4393_threadlocal` - from the 1.3 migration notes which discusses the rationale for deprecation. .. change:: :tags: mssql, postgresql, reflection, schema, usecase :tickets: 4458 Improved support for covering indexes (with INCLUDE columns). Added the ability for postgresql to render CREAT