Changelog
### 2.0.0rc3
```
:released: January 18, 2023
.. change::
:tags: bug, typing
:tickets: 9096
Fixes to the annotations within the ``sqlalchemy.ext.hybrid`` extension for
more effective typing of user-defined methods. The typing now uses
:pep:`612` features, now supported by recent versions of Mypy, to maintain
argument signatures for :class:`.hybrid_method`. Return values for hybrid
methods are accepted as SQL expressions in contexts such as
:meth:`_sql.Select.where` while still supporting SQL methods.
.. change::
:tags: bug, orm
:tickets: 9099
Fixed issue where using a pep-593 ``Annotated`` type in the
:paramref:`_orm.registry.type_annotation_map` which itself contained a
generic plain container or ``collections.abc`` type (e.g. ``list``,
``dict``, ``collections.abc.Sequence``, etc. ) as the target type would
produce an internal error when the ORM were trying to interpret the
``Annotated`` instance.
.. change::
:tags: bug, orm
:tickets: 9100
Added an error message when a :func:`_orm.relationship` is mapped against
an abstract container type, such as ``Mapped[Sequence[B]]``, without
providing the :paramref:`_orm.relationship.container_class` parameter which
is necessary when the type is abstract. Previously the the abstract
container would attempt to be instantiated at a later step and fail.
.. change::
:tags: orm, feature
:tickets: 9060
Added a new parameter to :class:`_orm.Mapper` called
:paramref:`_orm.Mapper.polymorphic_abstract`. The purpose of this directive
is so that the ORM will not consider the class to be instantiated or loaded
directly, only subclasses. The actual effect is that the
:class:`_orm.Mapper` will prevent direct instantiation of instances
of the class and will expect that the class does not have a distinct
polymorphic identity configured.
In practice, the class that is mapped with
:paramref:`_orm.Mapper.polymorphic_abstract` can be used as the target of a
:func:`_orm.relationship` as well as be used in queries; subclasses must of
course include polymorphic identities in their mappings.
The new parameter is automatically applied to classes that subclass
the :class:`.AbstractConcreteBase` class, as this class is not intended
to be instantiated.
.. seealso::
:ref:`orm_inheritance_abstract_poly`
.. change::
:tags: bug, postgresql
:tickets: 9106
Fixed regression where psycopg3 changed an API call as of version 3.1.8 to
expect a specific object type that was previously not enforced, breaking
connectivity for the psycopg3 dialect.
.. change::
:tags: oracle, usecase
:tickets: 9086
Added support for the Oracle SQL type ``TIMESTAMP WITH LOCAL TIME ZONE``,
using a newly added Oracle-specific :class:`_oracle.TIMESTAMP` datatype.
.. changelog::
```
### 2.0.0rc2
```
:released: January 9, 2023
.. change::
:tags: bug, typing
:tickets: 9067
The Data Class Transforms argument ``field_descriptors`` was renamed
to ``field_specifiers`` in the accepted version of PEP 681.
.. change::
:tags: bug, oracle
:tickets: 9059
Supported use case for foreign key constraints where the local column is
marked as "invisible". The errors normally generated when a
:class:`.ForeignKeyConstraint` is created that check for the target column
are disabled when reflecting, and the constraint is skipped with a warning
in the same way which already occurs for an :class:`.Index` with a similar
issue.
.. change::
:tags: bug, orm
:tickets: 9071
Fixed issue where an overly restrictive ORM mapping rule were added in 2.0
which prevented mappings against :class:`.TableClause` objects, such as
those used in the view recipe on the wiki.
.. change::
:tags: bug, mysql
:tickets: 9058
Restored the behavior of :meth:`.Inspector.has_table` to report on
temporary tables for MySQL / MariaDB. This is currently the behavior for
all other included dialects, but was removed for MySQL in 1.4 due to no
longer using the DESCRIBE command; there was no documented support for temp
tables being reported by the :meth:`.Inspector.has_table` method in this
version or on any previous version, so the previous behavior was undefined.
As SQLAlchemy 2.0 has added formal support for temp table status via
:meth:`.Inspector.has_table`, the MySQL /MariaDB dialect has been reverted
to use the "DESCRIBE" statement as it did in the SQLAlchemy 1.3 series and
previously, and test support is added to include MySQL / MariaDB for
this behavior. The previous issues with ROLLBACK being emitted which
1.4 sought to improve upon don't apply in SQLAlchemy 2.0 due to
simplifications in how :class:`.Connection` handles transactions.
DESCRIBE is necessary as MariaDB in particular has no consistently
available public information schema of any kind in order to report on temp
tables other than DESCRIBE/SHOW COLUMNS, which rely on throwing an error
in order to report no results.
.. change::
:tags: json, postgresql
:tickets: 7147
Implemented missing ``JSONB`` operations:
* ` using :meth:`_postgresql.JSONB.Comparator.path_match`
* ``?`` using :meth:`_postgresql.JSONB.Comparator.path_exists`
* ``-`` using :meth:`_postgresql.JSONB.Comparator.delete_path`
Pull request curtesy of Guilherme Martins Crocetti.
.. changelog::
```
### 2.0.0rc1
```
:released: December 28, 2022
.. change::
:tags: bug, typing
:tickets: 6810, 9025
pep-484 typing has been completed for the
``sqlalchemy.ext.horizontal_shard`` extension as well as the
``sqlalchemy.orm.events`` module. Thanks to Gleb Kisenkov for their
efforts.
.. change::
:tags: postgresql, bug
:tickets: 8977
:versions: 2.0.0rc1
Added support for explicit use of PG full text functions with asyncpg and
psycopg (SQLAlchemy 2.0 only), with regards to the ``REGCONFIG`` type cast
for the first argument, which previously would be incorrectly cast to a
VARCHAR, causing failures on these dialects that rely upon explicit type
casts. This includes support for :class:`_postgresql.to_tsvector`,
:class:`_postgresql.to_tsquery`, :class:`_postgresql.plainto_tsquery`,
:class:`_postgresql.phraseto_tsquery`,
:class:`_postgresql.websearch_to_tsquery`,
:class:`_postgresql.ts_headline`, each of which will determine based on
number of arguments passed if the first string argument should be
interpreted as a PostgreSQL "REGCONFIG" value; if so, the argument is typed
using a newly added type object :class:`_postgresql.REGCONFIG` which is
then explicitly cast in the SQL expression.
.. change::
:tags: bug, orm
:tickets: 4629
A warning is emitted if a backref name used in :func:`_orm.relationship`
names an attribute on the target class which already has a method or
attribute assigned to that name, as the backref declaration will replace
that attribute.
.. change::
:tags: bug, postgresql
:tickets: 9020
Fixed regression where newly revised PostgreSQL range types such as
:class:`_postgresql.INT4RANGE` could not be set up as the impl of a
:class:`.TypeDecorator` custom type, instead raising a ``TypeError``.
.. change::
:tags: usecase, orm
:tickets: 7837
Adjustments to the :class:`_orm.Session` in terms of extensibility,
as well as updates to the :class:`.ShardedSession` extension:
* :meth:`_orm.Session.get` now accepts
:paramref:`_orm.Session.get.bind_arguments`, which in particular may be
useful when using the horizontal sharding extension.
* :meth:`_orm.Session.get_bind` accepts arbitrary kw arguments, which
assists in developing code that uses a :class:`_orm.Session` class which
overrides this method with additional arguments.
* Added a new ORM execution option ``identity_token`` which may be used
to directly affect the "identity token" that will be associated with
newly loaded ORM objects. This token is how sharding approaches
(namely the :class:`.ShardedSession`, but can be used in other cases
as well) separate object identities across different "shards".
.. seealso::
:ref:`queryguide_identity_token`
* The :meth:`_orm.SessionEvents.do_orm_execute` event hook may now be used
to affect all ORM-related options, including ``autoflush``,
``populate_existing``, and ``yield_per``; these options are re-consumed
subsequent to event hooks being invoked before they are acted upon.
Previously, options like ``autoflush`` would have been already evaluated
at this point. The new ``identity_token`` option is also supported in
this mode and is now used by the horizontal sharding extension.
* The :class:`.ShardedSession` class replaces the
:paramref:`.ShardedSession.id_chooser` hook with a new hook
:paramref:`.ShardedSession.identity_chooser`, which no longer relies upon
the legacy :class:`_orm.Query` object.
:paramref:`.ShardedSession.id_chooser` is still accepted in place of
:paramref:`.ShardedSession.identity_chooser` with a deprecation warning.
.. change::
:tags: usecase, orm
:tickets: 9015
The behavior of "joining an external transaction into a Session" has been
revised and improved, allowing explicit control over how the
:class:`_orm.Session` will accommodate an incoming
:class:`_engine.Connection` that already has a transaction and possibly a
savepoint already established. The new parameter
:paramref:`_orm.Session.join_transaction_mode` includes a series of option
values which can accommodate the existing transaction in several ways, most
importantly allowing a :class:`_orm.Session` to operate in a fully
transactional style using savepoints exclusively, while leaving the
externally initiated transaction non-committed and active under all
circumstances, allowing test suites to rollback all changes that take place
within tests.
Additionally, revised the :meth:`_orm.Session.close` method to fully close
out savepoints that may still be present, which also allows the
"external transaction" recipe to proceed without warnings if the
:class:`_orm.Session` did not explicitly end its own SAVEPOINT
transactions.
.. seealso::
:ref:`change_9015`
.. change::
:tags: bug, sql
:tickets: 8988
Added test support to ensure that all compiler ``visit_xyz()`` methods
across all :class:`.Compiler` implementations in SQLAlchemy accept a
``**kw`` parameter, so that all compilers accept additional keyword
arguments under all circumstances.
.. change::
:tags: bug, postgresql
:tickets: 8984
The :meth:`_postgresql.Range.__eq___` will now return ``NotImplemented``
when comparing with an instance of a different class, instead of raising
an :exc:`AttributeError` exception.
.. change::
:tags: bug, sql
:tickets: 6114
The :meth:`.SQLCompiler.construct_params` method, as well as the
:attr:`.SQLCompiler.params` accessor, will now return the
exact parameters that correspond to a compiled statement that used
the ``render_postcompile`` parameter to compile. Previously,
the method returned a parameter structure that by itself didn't correspond
to either the original parameters or the expanded ones.
Passing a new dictionary of parameters to
:meth:`.SQLCompiler.construct_params` for a :class:`.SQLCompiler` that was
constructed with ``render_postcompile`` is now disallowed; instead, to make
a new SQL string and parameter set for an alternate set of parameters, a
new method :meth:`.SQLCompiler.construct_expanded_state` is added which
will produce a new expanded form for the given parameter set, using the
:class:`.ExpandedState` container which includes a new SQL statement
and new parameter dictionary, as well as a positional parameter tuple.
.. change::
:tags: bug, orm
:tickets: 8703, 8997, 8996
A series of changes and improvements regarding
:meth:`_orm.Session.refresh`. The overall change is that primary key
attributes for an object are now included in a refresh operation
unconditionally when relationship-bound attributes are to be refreshed,
even if not expired and even if not specified in the refresh.
* Improved :meth:`_orm.Session.refresh` so that if autoflush is enabled
(as is the default for :class:`_orm.Session`), the autoflush takes place
at an earlier part of the refresh process so that pending primary key
changes are applied without errors being raised. Previously, this
autoflush took place too late in the process and the SELECT statement
would not use the correct key to locate the row and an
:class:`.InvalidRequestError` would be raised.
* When the above condition is present, that is, unflushed primary key
changes are present on the object, but autoflush is not enabled,
the refresh() method now explicitly disallows the operation to proceed,
and an informative :class:`.InvalidRequestError` is raised asking that
the pending primary key changes be flushed first. Previously,
this use case was simply broken and :class:`.InvalidRequestError`
would be raised anyway. This restriction is so that it's safe for the
primary key attributes to be refreshed, as is necessary for the case of
being able to refresh the object with relationship-bound secondary
eagerloaders also being emitted. This rule applies in all cases to keep
API behavior consistent regardless of whether or not the PK cols are
actually needed in the refresh, as it is unusual to be refreshing
some attributes on an object while keeping other attributes "pending"
in any case.
* The :meth:`_orm.Session.refresh` method has been enhanced such that
attributes which are :func:`_orm.relationship`-bound and linked to an
eager loader, either at mapping time or via last-used loader options,
will be refreshed in all cases even when a list of attributes is passed
that does not include any columns on the parent row. This builds upon the
feature first implemented for non-column attributes as part of
:ticket:`1763` fixed in 1.4 allowing eagerly-loaded relationship-bound
attributes to participate in the :meth:`_orm.Session.refresh` operation.
If the refresh operation does not indicate any columns on the parent row
to be refreshed, the primary key columns will nonetheless be included
in the refresh operation, which allows the load to proceed into the
secondary relationship loaders indicated as it does normally.
Previously an :class:`.InvalidRequestError` error would be raised
for this condition (:ticket:`8703`)
* Fixed issue where an unnecessary additional SELECT would be emitted in
the case where :meth:`_orm.Session.refresh` were called with a
combination of expired attributes, as well as an eager loader such as
:func:`_orm.selectinload` that emits a "secondary" query, if the primary
key attributes were also in an expired state. As the primary key
attributes are now included in the refresh automatically, there is no
additional load for these attributes when a relationship loader
goes to select for them (:ticket:`8997`)
* Fixed regression caused by :ticket:`8126` released in 2.0.0b1 where the
:meth:`_orm.Session.refresh` method would fail with an
``AttributeError``, if passed both an expired column name as well as the
name of a relationship-bound attribute that was linked to a "secondary"
eagerloader such as the :func:`_orm.selectinload` eager loader
(:ticket:`8996`)
.. change::
:tags: bug, sql
:tickets: 8994
To accommodate for third party dialects with different character escaping
needs regarding bound parameters, the system by which SQLAlchemy "escapes"
(i.e., replaces with another character in its place) special characters in
bound parameter names has been made extensible for third party dialects,
using the :attr:`.SQLCompiler.bindname_escape_chars` dictionary which can
be overridden at the class declaration level on any :class:`.SQLCompiler`
subclass. As part of this change, also added the dot ``"."`` as a default
"escaped" character.
.. change::
:tags: orm, feature
:tickets: 8889
Added a new default value for the :paramref:`.Mapper.eager_defaults`
parameter "auto", which will automatically fetch table default values
during a unit of work flush, if the dialect supports RETURNING for the
INSERT being run, as well as
:ref:`insertmanyvalues <engine_insertmanyvalues>` available. Eager fetches
for server-side UPDATE defaults, which are very uncommon, continue to only
take place if :paramref:`.Mapper.eager_defaults` is set to ``True``, as
there is no batch-RETURNING form for UPDATE statements.
.. change::
:tags: usecase, orm
:tickets: 8973
Removed the requirement that the ``__allow_unmapped__`` attribute be used
on Declarative Dataclass Mapped class when non-``Mapped[]`` annotations are
detected; previously, an error message that was intended to support legacy
ORM typed mappings would be raised, which additionally did not mention
correct patterns to use with Dataclasses specifically. This error message
is now no longer raised if :meth:`_orm.registry.mapped_as_dataclass` or
:class:`_orm.MappedAsDataclass` is used.
.. seealso::
:ref:`orm_declarative_native_dataclasses_non_mapped_fields`
.. change::
:tags: bug, orm
:tickets: 8168
Improved a fix first made in version 1.4 for :ticket:`8456` which scaled
back the usage of internal "polymorphic adapters", that are used to render
ORM queries when the :paramref:`_orm.Mapper.with_polymorphic` parameter is
used. These adapters, which are very complex and error prone, are now used
only in those cases where an explicit user-supplied subquery is used for
:paramref:`_orm.Mapper.with_polymorphic`, which includes only the use case
of concrete inheritance mappings that use the
:func:`_orm.polymorphic_union` helper, as well as the legacy use case of
using an aliased subquery for joined inheritance mappings, which is not
needed in modern use.
For the most common case of joined inheritance mappings that use the
built-in polymorphic loading scheme, which includes those which make use of
the :paramref:`_orm.Mapper.polymorphic_load` parameter set to ``inline``,
polymorphic adapters are now no longer used. This has both a positive
performance impact on the construction of queries as well as a
substantial simplification of the internal query rendering process.
The specific issue targeted was to allow a :func:`_orm.column_property`
to refer to joined-inheritance classes within a scalar subquery, which now
works as intuitively as is feasible.
.. changelog::
```
### 2.0.0b4
```
:released: December 5, 2022
.. change::
:tags: usecase, orm
:tickets: 8859
Added support custom user-defined types which extend the Python
``enum.Enum`` base class to be resolved automatically
to SQLAlchemy :class:`.Enum` SQL types, when using the Annotated
Declarative Table feature. The feature is made possible through new
lookup features added to the ORM type map feature, and includes support
for changing the arguments of the :class:`.Enum` that's generated by
default as well as setting up specific ``enum.Enum`` types within
the map with specific arguments.
.. seealso::
:ref:`orm_declarative_mapped_column_enums`
.. change::
:tags: bug, typing
:tickets: 8783
Adjusted internal use of the Python ``enum.IntFlag`` class which changed
its behavioral contract in Python 3.11. This was not causing runtime
failures however caused typing runs to fail under Python 3.11.
.. change::
:tags: usecase, typing
:tickets: 8847
Added a new type :class:`.SQLColumnExpression` which may be indicated in
user code to represent any SQL column oriented expression, including both
those based on :class:`.ColumnElement` as well as on ORM
:class:`.QueryableAttribute`. This type is a real class, not an alias, so
can also be used as the foundation for other objects. An additional
ORM-specific subclass :class:`.SQLORMExpression` is also included.
.. change::
:tags: bug, typing
:tickets: 8667, 6810
The ``sqlalchemy.ext.mutable`` extension and ``sqlalchemy.ext.automap``
extensions are now fully pep-484 typed. Huge thanks to Gleb Kisenkov for
their efforts on this.
.. change::
:tags: bug, sql
:tickets: 8849
The approach to the ``numeric`` pep-249 paramstyle has been rewritten, and
is now fully supported, including by features such as "expanding IN" and
"insertmanyvalues". Parameter names may also be repeated in the source SQL
construct which will be correctly represented within the numeric format
using a single parameter. Introduced an additional numeric paramstyle
called ``numeric_dollar``, which is specifically what's used by the asyncpg
dialect; the paramstyle is equivalent to ``numeric`` except numeric
indicators are indicated by a dollar-sign rather than a colon. The asyncpg
dialect now uses ``numeric_dollar`` paramstyle directly, rather than
compiling to ``format`` style first.
The ``numeric`` and ``numeric_dollar`` paramstyles assume that the target
backend is capable of receiving the numeric parameters in any order,
and will match the given parameter values to the statement based on
matching their position (1-based) to the numeric indicator. This is the
normal behavior of "numeric" paramstyles, although it was observed that
the SQLite DBAPI implements a not-used "numeric" style that does not honor
parameter ordering.
.. change::
:tags: usecase, postgresql
:tickets: 8765
Complementing :ticket:`8690`, new comparison methods such as
:meth:`_postgresql.Range.adjacent_to`,
:meth:`_postgresql.Range.difference`, :meth:`_postgresql.Range.union`,
etc., were added to the PG-specific range objects, bringing them in par
with the standard operators implemented by the underlying
:attr:`_postgresql.AbstractRange.comparator_factory`.
In addition, the ``__bool__()`` method of the class has been corrected to
be consistent with the common Python containers behavior as well as how
other popular PostgreSQL drivers do: it now tells whether the range
instance is *not* empty, rather than the other way around.
Pull request courtesy Lele Gaifax.
.. change::
:tags: bug, sql
:tickets: 8770
Adjusted the rendering of ``RETURNING``, in particular when using
:class:`_sql.Insert`, such that it now renders columns using the same logic
as that of the :class:`.Select` construct to generate labels, which will
include disambiguating labels, as well as that a SQL function surrounding a
named column will be labeled using the column name itself. This establishes
better cross-compatibility when selecting rows from either :class:`.Select`
constructs or from DML statements that use :meth:`.UpdateBase.returning`. A
narrower scale change was also made for the 1.4 series that adjusted the
function label issue only.
.. change::
:tags: change, postgresql, asyncpg
:tickets: 8926
Changed the paramstyle used by asyncpg from ``format`` to
``numeric_dollar``. This has two main benefits since it does not require
additional processing of the statement and allows for duplicate parameters
to be present in the statements.
.. change::
:tags: bug, orm
:tickets: 8888
Fixed issue where use of an unknown datatype within a :class:`.Mapped`
annotation for a column-based attribute would silently fail to map the
attribute, rather than reporting an exception; an informative exception
message is now raised.
.. change::
:tags: bug, orm
:tickets: 8777
Fixed a suite of issues involving :class:`.Mapped` use with dictionary
types, such as ``Mapped[dict[str, str] | None]``, would not be correctly
interpreted in Declarative ORM mappings. Support to correctly
"de-optionalize" this type including for lookup in ``type_annotation_map``
has been fixed.
.. change::
:tags: feature, orm
:tickets: 8822
Added a new parameter :paramref:`_orm.mapped_column.use_existing_column` to
accommodate the use case of a single-table inheritance mapping that uses
the pattern of more than one subclass indicating the same column to take
place on the superclass. This pattern was previously possible by using
:func:`_orm.declared_attr` in conjunction with locating the existing column
in the ``.__table__`` of the superclass, however is now updated to work
with :func:`_orm.mapped_column` as well as with pep-484 typing, in a
simple and succinct way.
.. seealso::
:ref:`orm_inheritance_column_conflicts`
.. change::
:tags: bug, mssql
:tickets: 8917
Fixed regression caused by the combination of :ticket:`8177`, re-enable
setinputsizes for SQL server unless fast_executemany + DBAPI executemany is
used for a statement, along with :ticket:`6047`, implement
"insertmanyvalues", which bypasses DBAPI executemany in place of a custom
DBAPI execute for INSERT statements. setinputsizes would incorrectly not be
used for a multiple parameter-set INSERT statement that used
"insertmanyvalues" if fast_executemany were turned on, as the check would
incorrectly assume this is a DBAPI executemany call. The "regression"
would then be that the "insertmanyvalues" statement format is apparently
slightly more sensitive to multiple rows that don't use the same types
for each row, so in such a case setinputsizes is especially needed.
The fix repairs the fast_executemany check so that it only disables
setinputsizes if true DBAPI executemany is to be used.
.. change::
:tags: bug, orm, performance
:tickets: 8796
Additional performance enhancements within ORM-enabled SQL statements,
specifically targeting callcounts within the construction of ORM
statements, using combinations of :func:`_orm.aliased` with
:func:`_sql.union` and similar "compound" constructs, in addition to direct
performance improvements to the ``corresponding_column()`` internal method
that is used heavily by the ORM by constructs like :func:`_orm.aliased` and
similar.
.. change::
:tags: bug, postgresql
:tickets: 8884
Added additional type-detection for the new PostgreSQL
:class:`_postgresql.Range` type, where previous cases that allowed the
psycopg2-native range objects to be received directly by the DBAPI without
SQLAlchemy intercepting them stopped working, as we now have our own value
object. The :class:`_postgresql.Range` object has been enhanced such that
SQLAlchemy Core detects it in otherwise ambiguous situations (such as
comparison to dates) and applies appropriate bind handlers. Pull request
courtesy Lele Gaifax.
.. change::
:tags: bug, orm
:tickets: 8880
Fixed bug in :ref:`orm_declarative_native_dataclasses` feature where using
plain dataclass fields with the ``__allow_unmapped__`` directive in a
mapping would not create a dataclass with the correct class-level state for
those fields, copying the raw ``Field`` object to the class inappropriately
after dataclasses itself had replaced the ``Field`` object with the
class-level default value.
.. change::
:tags: usecase, orm extensions
:tickets: 8878
Added support for the :func:`.association_proxy` extension function to
take part within Python ``dataclasses`` configuration, when using
the native dataclasses feature described at
:ref:`orm_declarative_native_dataclasses`. Included are attribute-level
arguments including :paramref:`.association_proxy.init` and
:paramref:`.association_proxy.default_factory`.
Documentation for association proxy has also been updated to use
"Annotated Declarative Table" forms within examples, including type
annotations used for :class:`.AssocationProxy` itself.
.. change::
:tags: bug, typing
Corrected typing support for the :paramref:`_orm.relationship.secondary`
argument which may also accept a callable (lambda) that returns a
:class:`.FromClause`.
.. change::
:tags: bug, orm, regression
:tickets: 8812
Fixed regression where flushing a mapped class that's mapped against a
subquery, such as a direct mapping or some forms of concrete table
inheritance, would fail if the :paramref:`_orm.Mapper.eager_defaults`
parameter were used.
.. change::
:tags: bug, schema
:tickets: 8925
Stricter rules are in place for appending of :class:`.Column` objects to
:class:`.Table` objects, both moving some previous deprecation warnings to
exceptions, and preventing some previous scenarios that would cause
duplicate columns to appear in tables, when
:paramref:`.Table.extend_existing` were set to ``True``, for both
programmatic :class:`.Table` construction as well as during reflection
operations.
See :ref:`change_8925` for a rundown of these changes.
.. seealso::
:ref:`change_8925`
.. change::
:tags: usecase, orm
:tickets: 8905
Added :paramref:`_orm.mapped_column.compare` parameter to relevant ORM
attribute constructs including :func:`_orm.mapped_column`,
:func:`_orm.relationship` etc. to provide for the Python dataclasses
``compare`` parameter on ``field()``, when using the
:ref:`orm_declarative_native_dataclasses` feature. Pull request courtesy
Simon Schiele.
.. change::
:tags: sql, usecase
:tickets: 6289
Added :class:`_expression.ScalarValues` that can be used as a column
element allowing using :class:`_expression.Values` inside ``IN`` clauses
or in conjunction with ``ANY`` or ``ALL`` collection aggregates.
This new class is generated using the method
:meth:`_expression.Values.scalar_values`.
The :class:`_expression.Values` instance is now coerced to a
:class:`_expression.ScalarValues` when used in a ``IN`` or ``NOT IN``
operation.
.. change::
:tags: bug, orm
:tickets: 8853
Fixed regression in 2.0.0b3 caused by :ticket:`8759` where indicating the
:class:`.Mapped` name using a qualified name such as
``sqlalchemy.orm.Mapped`` would fail to be recognized by Declarative as
indicating the :class:`.Mapped` construct.
.. change::
:tags: bug, typing
:tickets: 8842
Improved the typing for :class:`.sessionmaker` and
:class:`.async_sessionmaker`, so that the default type of their return value
will be :class:`.Session` or :class:`.AsyncSession`, without the need to
type this explicitly. Previously, Mypy would not automaticaly infer these
return types from its generic base.
As part of this change, arguments for :class:`.Session`,
:class:`.AsyncSession`, :class:`.sessionmaker` and
:class:`.async_sessionmaker` beyond the initial "bind" argument have been
made keyword-only, which includes parameters that have always been
documented as keyword arguments, such as :paramref:`.Session.autoflush`,
:paramref:`.Session.class_`, etc.
Pull request courtesy Sam Bull.
.. change::
:tags: bug, typing
:tickets: 8776
Fixed issue where passing a callbale function returning an iterable
of column elements to :paramref:`_orm.relationship.order_by` was
flagged as an error in type checkers.
.. changelog::
```
### 2.0.0b3
```
:released: November 4, 2022
.. change::
:tags: bug, orm, declarative
:tickets: 8759
Added support in ORM declarative annotations for class names specified for
:func:`_orm.relationship`, as well as the name of the :class:`_orm.Mapped`
symbol itself, to be different names than their direct class name, to
support scenarios such as where :class:`_orm.Mapped` is imported as
``from sqlalchemy.orm import Mapped as M``, or where related class names
are imported with an alternate name in a similar fashion. Additionally, a
target class name given as the lead argument for :func:`_orm.relationship`
will always supersede the name given in the left hand annotation, so that
otherwise un-importable names that also don't match the class name can
still be used in annotations.
.. change::
:tags: bug, orm, declarative
:tickets: 8692
Improved support for legacy 1.4 mappings that use annotations which don't
include ``Mapped[]``, by ensuring the ``__allow_unmapped__`` attribute can
be used to allow such legacy annotations to pass through Annotated
Declarative without raising an error and without being interpreted in an
ORM runtime context. Additionally improved the error message generated when
this condition is detected, and added more documentation for how this
situation should be handled. Unfortunately the 1.4 WARN_SQLALCHEMY_20
migration warning cannot detect this particular configurational issue at
runtime with its current architecture.
.. change::
:tags: usecase, postgresql
:tickets: 8690
Refined the new approach to range objects described at :ref:`change_7156`
to accommodate driver-specific range and multirange objects, to better
accommodate both legacy code as well as when passing results from raw SQL
result sets back into new range or multirange expressions.
.. change::
:tags: usecase, engine
:tickets: 8717
Added new parameter :paramref:`.PoolEvents.reset.reset_state` parameter to
the :meth:`.PoolEvents.reset` event, with deprecation logic in place that
will continue to accept event hooks using the previous set of arguments.
This indicates various state information about how the reset is taking
place and is used to allow custom reset schemes to take place with full
context given.
Within this change a fix that's also backported to 1.4 is included which
re-enables the :meth:`.PoolEvents.reset` event to continue to take place
under all circumstances, including when :class:`.Connection` has already
"reset" the connection.
The two changes together allow custom reset schemes to be implemented using
the :meth:`.PoolEvents.reset` event, instead of the
:meth:`.PoolEvents.checkin` event (which continues to function as it always
has).
.. change::
:tags: bug, orm, declarative
:tickets: 8705
Changed a fundamental configuration behavior of :class:`.Mapper`, where
:class:`_schema.Column` objects that are explicitly present in the
:paramref:`_orm.Mapper.properties` dictionary, either directly or enclosed
within a mapper property object, will now be mapped within the order of how
they appear within the mapped :class:`.Table` (or other selectable) itself
(assuming they are in fact part of that table's list of columns), thereby
maintaining the same order of columns in the mapped selectable as is
instrumented on the mapped class, as well as what renders in an ORM SELECT
statement for that mapper. Previously (where "previously" means since
version 0.0.1), :class:`.Column` objects in the
:paramref:`_orm.Mapper.properties` dictionary would always be mapped first,
ahead of when the other columns in the mapped :class:`.Table` would be
mapped, causing a discrepancy in the order in which the mapper would
assign attributes to the mapped class as well as the order in which they
would render in statements.
The change most prominently takes place in the way that Declarative
assigns declared columns to the :class:`.Mapper`, specifically how
:class:`.Column` (or :func:`_orm.mapped_column`) objects are handled
when they have a DDL name that is explicitly different from the mapped
attribute name, as well as when constructs such as :func:`_orm.deferred`
etc. are used. The new behavior will see the column ordering within
the mapped :class:`.Table` being the same order in which the attributes
are mapped onto the class, assigned within the :class:`.Mapper` itself,
and rendered in ORM statements such as SELECT statements, independent
of how the :class:`_schema.Column` was configured against the
:class:`.Mapper`.
.. change::
:tags: feature, engine
:tickets: 8710
To better support the use case of iterating :class:`.Result` and
:class:`.AsyncResult` objects where user-defined exceptions may interrupt
the iteration, both objects as well as variants such as
:class:`.ScalarResult`, :class:`.MappingResult`,
:class:`.AsyncScalarResult`, :class:`.AsyncMappingResult` now support
context manager usage, where the result will be closed at the end of
the context manager block.
In addition, ensured that all the above
mentioned :class:`.Result` objects include a :meth:`.Result.close` method
as well as :attr:`.Result.closed` accessors, including
:class:`.ScalarResult` and :class:`.MappingResult` which previously did
not have a ``.close()`` method.
.. seealso::
:ref:`change_8710`
.. change::
:tags: bug, typing
Corrected various typing issues within the engine and async engine
packages.
.. change::
:tags: bug, orm, declarative
:tickets: 8718
Fixed issue in new dataclass mapping feature where a column declared on the
decalrative base / abstract base / mixin would leak into the constructor
for an inheriting subclass under some circumstances.
.. change::
:tags: bug, orm declarative
:tickets: 8742
Fixed issues within the declarative typing resolver (i.e. which resolves
``ForwardRef`` objects) where types that were declared for columns in one
particular source file would raise ``NameError`` when the ultimate mapped
class were in another source file. The types are now resolved in terms
of the module for each class in which the types are used.
.. change::
:tags: feature, postgresql
:tickets: 8706
Added new methods :meth:`_postgresql.Range.contains` and
:meth:`_postgresql.Range.contained_by` to the new :class:`.Range` data
object, which mirror the behavior of the PostgreSQL ``>`` and ``<``
operators, as well as the
:meth:`_postgresql.AbstractRange.comparator_factory.contains` and
:meth:`_postgresql.AbstractRange.comparator_factory.contained_by` SQL
operator methods. Pull request courtesy Lele Gaifax.
.. changelog::
```
### 2.0.0b2
```
:released: October 20, 2022
.. change::
:tags: bug, orm
:tickets: 8656
Removed the warning that emits when using ORM-enabled update/delete
regarding evaluation of columns by name, first added in :ticket:`4073`;
this warning actually covers up a scenario that otherwise could populate
the wrong Python value for an ORM mapped attribute depending on what the
actual column is, so this deprecated case is removed. In 2.0, ORM enabled
update/delete uses "auto" for "synchronize_session", which should do the
right thing automatically for any given UPDATE expression.
.. change::
:tags: bug, mssql
:tickets: 8661
Fixed regression caused by SQL Server pyodbc change :ticket:`8177` where we
now use ``setinputsizes()`` by default; for VARCHAR, this fails if the
character size is greater than 4000 (or 2000, depending on data) characters
as the incoming datatype is NVARCHAR, which has a limit of 4000 characters,
despite the fact that VARCHAR can handle unlimited characters. Additional
pyodbc-specific typing information is now passed to ``setinputsizes()``
when the datatype's size is > 2000 characters. The change is also applied
to the :class:`_types.JSON` type which was also impacted by this issue for large
JSON serializations.
.. change::
:tags: bug, typing
:tickets: 8645
Fixed typing issue where pylance strict mode would report "instance
variable overrides class variable" when using a method to define
``__tablename__``, ``__mapper_args__`` or ``__table_args__``.
.. change::
:tags: mssql, bug
:tickets: 7211
The :class:`.Sequence` construct restores itself to the DDL behavior it
had prior to the 1.4 series, where creating a :class:`.Sequence` with
no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction
**without** any additional parameters for "start value". For most backends,
this is how things worked previously in any case; **however**, for
MS SQL Server, the default value on this database is
``-2**63``; to prevent this generally impractical default
from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter
should be provided. As usage of :class:`.Sequence` is unusual
for SQL Server which for many years has standardized on ``IDENTITY``,
it is hoped that this change has minimal impact.
.. seealso::
:ref:`change_7211`
.. change::
:tags: bug, declarative, orm
:tickets: 8665
Improved the :class:`.DeclarativeBase` class so that when combined with
other mixins like :class:`.MappedAsDataclass`, the order of the classes may
be in either order.
.. change::
:tags: usecase, declarative, orm
:tickets: 8665
Added support for mapped classes that are also ``Generic`` subclasses,
to be specified as a ``GenericAlias`` object (e.g. ``MyClass[str]``)
within statements and calls to :func:`_sa.inspect`.
.. change::
:tags: bug, orm, declarative
:tickets: 8668
Fixed bug in new ORM typed declarative mappings where the ability
to use ``Optional[MyClass]`` or similar forms such as ``MyClass | None``
in the type annotation for a many-to-one relationship was not implemented,
leading to errors. Documentation has also been added for this use
case to the relationship configuration documentation.
.. change::
:tags: bug, typing
:tickets: 8644
Fixed typing issue where pylance strict mode would report "partially
unknown" datatype for the :func:`_orm.mapped_column` construct.
.. change::
:tags: bug, regression, sql
:tickets: 8639
Fixed bug in new "insertmanyvalues" feature where INSERT that included a
subquery with :func:`_sql.bindparam` inside of it would fail to render
correctly in "insertmanyvalues" format. This affected psycopg2 most
directly as "insertmanyvalues" is used unconditionally with this driver.
.. change::
:tags: bug, orm, declarative
:tickets: 8688
Fixed issue with new dataclass mapping feature where arguments passed to
the dataclasses API could sometimes be mis-ordered when dealing with mixins
that override :func:`_orm.mapped_column` declarations, leading to
initializer problems.
.. changelog::
```
### 2.0.0b1
```
:released: October 13, 2022
.. change::
:tags: bug, sql
:tickets: 7888
The FROM clauses that are established on a :func:`_sql.select` construct
when using the :meth:`_sql.Select.select_from` method will now render first
in the FROM clause of the rendered SELECT, which serves to maintain the
ordering of clauses as was passed to the :meth:`_sql.Select.select_from`
method itself without being affected by the presence of those clauses also
being mentioned in other parts of the query. If other elements of the
:class:`_sql.Select` also generate FROM clauses, such as the columns clause
or WHERE clause, these will render after the clauses delivered by
:meth:`_sql.Select.select_from` assuming they were not explictly passed to
:meth:`_sql.Select.select_from` also. This improvement is useful in those
cases where a particular database generates a desirable query plan based on
a particular ordering of FROM clauses and allows full control over the
ordering of FROM clauses.
.. change::
:tags: usecase, sql
:tickets: 7998
Altered the compilation mechanics of the :class:`_dml.Insert` construct
such that the "autoincrement primary key" column value will be fetched via
``cursor.lastrowid`` or RETURNING even if present in the parameter set or
within the :meth:`_dml.Insert.values` method as a plain bound value, for
single-row INSERT statements on specific backends that are known to
generate autoincrementing values even when explicit NULL is passed. This
restores a behavior that was in the 1.3 series for both the use case of
separate parameter set as well as :meth:`_dml.Insert.values`. In 1.4, the
parameter set behavior unintentionally changed to no longer do this, but
the :meth:`_dml.Insert.values` method would still fetch autoincrement
values up until 1.4.21 where :ticket:`6770` changed the behavior yet again
again unintentionally as this use case was never covered.
The behavior is now defined as "working" to suit the case where databases
such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key
value and nonetheless invoke an autoincrement generator.
.. change::
:tags: change, postgresql
SQLAlchemy now requires PostgreSQL version 9 or greater.
Older versions may still work in some limited use cases.
.. change::
:tags: bug, orm
Fixed issue where the :meth:`_orm.registry.map_declaratively` method
would return an internal "mapper config" object and not the
:class:`.Mapper` object as stated in the API documentation.
.. change::
:tags: sybase, removed
:tickets: 7258
Removed the "sybase" internal dialect that was deprecated in previous
SQLAlchemy versions. Third party dialect support is available.
.. seealso::
:ref:`external_toplevel`
.. change::
:tags: bug, orm
:tickets: 7463
Fixed performance regression which appeared at least in version 1.3 if not
earlier (sometime after 1.0) where the loading of deferred columns, those
explicitly mapped with :func:`_orm.defer` as opposed to non-deferred
columns that were expired, from a joined inheritance subclass would not use
the "optimized" query which only queried the immediate table that contains
the unloaded columns, instead running a full ORM query which would emit a
JOIN for all base tables, which is not necessary when only loading columns
from the subclass.
.. change::
:tags: bug, sql
:tickets: 7791
The :paramref:`.Enum.length` parameter, which sets the length of the
``VARCHAR`` column for non-native enumeration types, is now used
unconditionally when emitting DDL for the ``VARCHAR`` datatype, including
when the :paramref:`.Enum.native_enum` parameter is set to ``True`` for
target backends that continue to use ``VARCHAR``. Previously the parameter
would be erroneously ignored in this case. The warning previously emitted
for this case is now removed.
.. change::
:tags: bug, orm
:tickets: 6986
The internals for the :class:`_orm.Load` object and related loader strategy
patterns have been mostly rewritten, to take advantage of the fact that
only attribute-bound paths, not strings, are now supported. The rewrite
hopes to make it more straightforward to address new use cases and subtle
issues within the loader strategy system going forward.
.. change::
:tags: usecase, orm
Added :paramref:`_orm.load_only.raiseload` parameter to the
:func:`_orm.load_only` loader option, so that the unloaded attributes may
have "raise" behavior rather than lazy loading. Previously there wasn't
really a way to do this with the :func:`_orm.load_only` option directly.
.. change::
:tags: change, engine
:tickets: 7122
Some small API changes regarding engines and dialects:
* The :meth:`.Dialect.set_isolation_level`, :meth:`.Dialect.get_isolation_level`,
:meth:
dialect methods will always be passed the raw DBAPI connection
* The :class:`.Connection` and :class:`.Engine` classes no longer share a base
``Connectable`` superclass, which has been removed.
* Added a new interface class :class:`.PoolProxiedConnection` - this is the
public facing interface for the familiar :class:`._ConnectionFairy`
class which is nonetheless a private class.
.. change::
:tags: feature, sql
:tickets: 3482
Added long-requested case-insensitive string operators
:meth:`_sql.ColumnOperators.icontains`,
:meth:`_sql.ColumnOperators.istartswith`,
:meth:`_sql.ColumnOperators.iendswith`, which produce case-insensitive
LIKE compositions (using ILIKE on PostgreSQL, and the LOWER() function on
all other backends) to complement the existing LIKE composition operators
:meth:`_sql.ColumnOperators.contains`,
:meth:`_sql.ColumnOperators.startswith`, etc. Huge thanks to Matias
Martinez Rebori for their meticulous and complete efforts in implementing
these new methods.
.. change::
:tags: usecase, postgresql
:tickets: 8138
Added literal type rendering for the :class:`_sqltypes.ARRAY` and
:class:`_postgresql.ARRAY` datatypes. The generic stringify will render
using brackets, e.g. ``[1, 2, 3]`` and the PostgreSQL specific will use the
ARRAY literal e.g. ``ARRAY[1, 2, 3]``. Multiple dimensions and quoting
are also taken into account.
.. change::
:tags: bug, orm
:tickets: 8166
Made an improvement to the "deferred" / "load_only" set of strategy options
where if a certain object is loaded from two different logical paths within
one query, attributes that have been configured by at least one of the
options to be populated will be populated in all cases, even if other load
paths for that same object did not set this option. previously, it was
based on randomness as to which "path" addressed the object first.
.. change::
:tags: feature, orm, sql
:tickets: 6047
Added new feature to all included dialects that support RETURNING
called "insertmanyvalues". This is a generalization of the
"fast executemany" feature first introduced for the psycopg2 driver
in 1.4 at :ref:`change_5263`, which allows the ORM to batch INSERT
statements into a much more efficient SQL structure while still being
able to fetch newly generated primary key and SQL default values
using RETURNING.
The feature now applies to the many dialects that support RETURNING along
with multiple VALUES constructs for INSERT, including all PostgreSQL
drivers, SQLite, MariaDB, MS SQL Server. Separately, the Oracle dialect
also gains the same capability using native cx_Oracle or OracleDB features.
.. change::
:tags: bug, engine
:tickets: 8523
The :class:`_pool.QueuePool` now ignores ``max_overflow`` when
``pool_size=0``, properly making the pool unlimited in all cases.
.. change::
:tags: bug, sql
:tickets: 7909
The in-place type detection for Python integers, as occurs with an
expression such as ``literal(25)``, will now apply value-based adaption as
well to accommodate Python large integers, where the datatype determined
will be :class:`.BigInteger` rather than :class:`.Integer`. This
accommodates for dialects such as that of asyncpg which both sends implicit
typing information to the driver as well as is sensitive to numeric scale.
.. change::
:tags: postgresql, mssql, change
:tickets: 7225
The parameter :paramref:`_types.UUID.as_uuid` of :class:`_types.UUID`,
previously specific to the PostgreSQL dialect but now generalized for Core
(along with a new backend-agnostic :class:`_types.Uuid` datatype) now
defaults to ``True``, indicating that Python ``UUID`` objects are accepted
by this datatype by default. Additionally, the SQL Server
:class:`_mssql.UNIQUEIDENTIFIER` datatype has been converted to be a
UUID-receiving type; for legacy code that makes use of
:class:`_mssql.UNIQUEIDENTIFIER` using string values, set the
:paramref:`_mssql.UNIQUEIDENTIFIER.as_uuid` parameter to ``False``.
.. change::
:tags: bug, orm
:tickets: 8344
Fixed issue in ORM enabled UPDATE when the statement is created against a
joined-inheritance subclass, updating only local table columns, where the
"fetch" synchronization strategy would not render the correct RETURNING
clause for databases that use RETURNING for fetch synchronization.
Also adjusts the strategy used for RETURNING in UPDATE FROM and
DELETE FROM statements.
.. change::
:tags: usecase, mariadb
:tickets: 8344
Added a new execution option ``is_delete_using=True``, which is consumed
by the ORM when using an ORM-enabled DELETE statement in conjunction with
the "fetch" synchronization strategy; this option indicates that the
DELETE statement is expected to use multiple tables, which on MariaDB
is the DELETE..USING syntax. The option then indicates that
RETURNING (newly implemented in SQLAlchemy 2.0 for MariaDB
for :ticket:`7011`) should not be used for databases that are known
to not support "DELETE..USING..RETURNING" syntax, even though they
support "DELETE..USING", which is MariaDB's current capability.
The rationale for this option is that the current workings of ORM-enabled
DELETE doesn't know up front if a DELETE statement is against multiple
tables or not until compilation occurs, which is cached in any case, yet it
needs to be known so that a SELECT for the to-be-deleted row can be emitted
up front. Instead of applying an across-the-board performance penalty for
all DELETE statements by proactively checking them all for this
relatively unusual SQL pattern, the ``is_delete_using=True`` execution
option is requested via a new exception message that is raised
within the compilation step. This exception message is specifically
(and only) raised when: the statement is an ORM-enabled DELETE where
the "fetch" synchronization strategy has been requested; the
backend is MariaDB or other backend with this specific limitation;
the statement has been detected within the initial compilation
that it would otherwise emit "DELETE..USING..RETURNING". By applying
the execution option, the ORM knows to run a SELECT upfront instead.
A similar option is implemented for ORM-enabled UPDATE but there is not
currently a backend where it is needed.
.. change::
:tags: bug, orm, asyncio
:tickets: 7703
Removed the unused ``**kw`` arguments from
:class:`_asyncio.AsyncSession.begin` and
:class:`_asyncio.AsyncSession.begin_nested`. These kw aren't used and
appear to have been added to the API in error.
.. change::
:tags: feature, sql
:tickets: 8285
Added new syntax to the :attr:`.FromClause.c` collection on all
:class:`.FromClause` objects allowing tuples of keys to be passed to
``__getitem__()``, along with support for the :func:`_sql.select` construct
to handle the resulting tuple-like collection directly, allowing the syntax
``select(table.c['a', 'b', 'c'])`` to be possible. The sub-collection
returned is itself a :class:`.ColumnCollection` which is also directly
consumable by :func:`_sql.select` and similar now.
.. seealso::
:ref:`tutorial_selecting_columns`
.. change::
:tags: general, changed
:tickets: 7257
Migrated the codebase to remove all pre-2.0 behaviors and architectures
that were previously noted as deprecated for removal in 2.0, including,
but not limited to:
* removal of all Python 2 code, minimum version is now Python 3.7
* :class:`_engine.Engine` and :class:`_engine.Connection` now use the
new 2.0 style of working, which includes "autobegin", library level
autocommit removed, subtransactions and "branched" connections
removed
* Result objects use 2.0-style behaviors; :class:`_result.Row` is fully
a named tuple without "mapping" behavior, use :class:`_result.RowMapping`
for "mapping" behavior
* All Unicode encoding/decoding architecture has been removed from
SQLAlchemy. All modern DBAPI implementations support Unicode
transparently thanks to Python 3, so the ``convert_unicode`` feature
as well as related mechanisms to look for bytestrings in
DBAPI ``cursor.description`` etc. have been removed.
* The ``.bind`` attribute and parameter from :class:`.MetaData`,
:class:`.Table`, and from all DDL/DML/DQL elements that previously could
refer to a "bound engine"
* The standalone ``sqlalchemy.orm.mapper()`` function is removed; all
classical mapping should be done through the
:meth:`_orm.registry.map_imperatively` method of :class:`_orm.registry`.
* The :meth:`_orm.Query.join` method no longer accepts strings for
relationship names; the long-documented approach of using
``Class.attrname`` for join targets is now standard.
* :meth:`_orm.Query.join` no longer accepts the "aliased" and
"from_joinpoint" arguments
* :meth:`_orm.Query.join` no longer accepts chains of multiple join
targets in one method call.
* ``Query.from_self()``, ``Query.select_entity_from()`` and
``Query.with_polymorphic()`` are removed.
* The :paramref:`_orm.relationship.cascade_backrefs` parameter must now
remain at its new default of ``False``; the ``save-update`` cascade
no longer cascades along a backref.
* the :paramref:`_orm.Session.future` parameter must always be set to
``True``. 2.0-style transactional patterns for :class:`_orm.Session`
are now always in effect.
* Loader options no longer accept strings for attribute names. The
long-documented approach of using ``Class.attrname`` for loader option
targets is now standard.
* Legacy forms of :func:`_sql.select` removed, including
``select([cols])``, the "whereclause" and keyword parameters of
``some_table.select()``.
* Legacy "in-place mutator" methods on :class:`_sql.Select` such as
``append_whereclause()``, ``append_order_by()`` etc are removed.
* Removed the very old "dbapi_proxy" module, which in very early
SQLAlchemy releases was used to provide a transparent connection pool
over a raw DBAPI connection.
.. change::
:tags: feature, orm
:tickets: 8375
Added new parameter :paramref:`_orm.AttributeEvents.include_key`, which
will include the dictionary or list key for operations such as
``__setitem__()`` (e.g. ``obj[key] = value``) and ``__delitem__()`` (e.g.
``del obj[key]``), using a new keyword parameter "key" or "keys", depending
on event, e.g. :paramref:`_orm.AttributeEvents.append.key`,
:paramref:`_orm.AttributeEvents.bulk_replace.keys`. This allows event
handlers to take into account the key that was passed to the operation and
is of particular importance for dictionary operations working with
:class:`_orm.MappedCollection`.
.. change::
:tags: postgresql, usecase
:tickets: 7156, 8540
Adds support for PostgreSQL multirange types, introduced in PostgreSQL 14.
Support for PostgreSQL ranges and multiranges has now been generalized to
the psycopg3, psycopg2 and asyncpg backends, with room for further dialect
support, using a backend-agnostic :class:`_postgresql.Range` data object
that's constructor-compatible with the pre
This PR updates SQLAlchemy from 1.4.46 to 2.0.1.
Changelog
### 2.0.0rc3 ``` :released: January 18, 2023 .. change:: :tags: bug, typing :tickets: 9096 Fixes to the annotations within the ``sqlalchemy.ext.hybrid`` extension for more effective typing of user-defined methods. The typing now uses :pep:`612` features, now supported by recent versions of Mypy, to maintain argument signatures for :class:`.hybrid_method`. Return values for hybrid methods are accepted as SQL expressions in contexts such as :meth:`_sql.Select.where` while still supporting SQL methods. .. change:: :tags: bug, orm :tickets: 9099 Fixed issue where using a pep-593 ``Annotated`` type in the :paramref:`_orm.registry.type_annotation_map` which itself contained a generic plain container or ``collections.abc`` type (e.g. ``list``, ``dict``, ``collections.abc.Sequence``, etc. ) as the target type would produce an internal error when the ORM were trying to interpret the ``Annotated`` instance. .. change:: :tags: bug, orm :tickets: 9100 Added an error message when a :func:`_orm.relationship` is mapped against an abstract container type, such as ``Mapped[Sequence[B]]``, without providing the :paramref:`_orm.relationship.container_class` parameter which is necessary when the type is abstract. Previously the the abstract container would attempt to be instantiated at a later step and fail. .. change:: :tags: orm, feature :tickets: 9060 Added a new parameter to :class:`_orm.Mapper` called :paramref:`_orm.Mapper.polymorphic_abstract`. The purpose of this directive is so that the ORM will not consider the class to be instantiated or loaded directly, only subclasses. The actual effect is that the :class:`_orm.Mapper` will prevent direct instantiation of instances of the class and will expect that the class does not have a distinct polymorphic identity configured. In practice, the class that is mapped with :paramref:`_orm.Mapper.polymorphic_abstract` can be used as the target of a :func:`_orm.relationship` as well as be used in queries; subclasses must of course include polymorphic identities in their mappings. The new parameter is automatically applied to classes that subclass the :class:`.AbstractConcreteBase` class, as this class is not intended to be instantiated. .. seealso:: :ref:`orm_inheritance_abstract_poly` .. change:: :tags: bug, postgresql :tickets: 9106 Fixed regression where psycopg3 changed an API call as of version 3.1.8 to expect a specific object type that was previously not enforced, breaking connectivity for the psycopg3 dialect. .. change:: :tags: oracle, usecase :tickets: 9086 Added support for the Oracle SQL type ``TIMESTAMP WITH LOCAL TIME ZONE``, using a newly added Oracle-specific :class:`_oracle.TIMESTAMP` datatype. .. changelog:: ``` ### 2.0.0rc2 ``` :released: January 9, 2023 .. change:: :tags: bug, typing :tickets: 9067 The Data Class Transforms argument ``field_descriptors`` was renamed to ``field_specifiers`` in the accepted version of PEP 681. .. change:: :tags: bug, oracle :tickets: 9059 Supported use case for foreign key constraints where the local column is marked as "invisible". The errors normally generated when a :class:`.ForeignKeyConstraint` is created that check for the target column are disabled when reflecting, and the constraint is skipped with a warning in the same way which already occurs for an :class:`.Index` with a similar issue. .. change:: :tags: bug, orm :tickets: 9071 Fixed issue where an overly restrictive ORM mapping rule were added in 2.0 which prevented mappings against :class:`.TableClause` objects, such as those used in the view recipe on the wiki. .. change:: :tags: bug, mysql :tickets: 9058 Restored the behavior of :meth:`.Inspector.has_table` to report on temporary tables for MySQL / MariaDB. This is currently the behavior for all other included dialects, but was removed for MySQL in 1.4 due to no longer using the DESCRIBE command; there was no documented support for temp tables being reported by the :meth:`.Inspector.has_table` method in this version or on any previous version, so the previous behavior was undefined. As SQLAlchemy 2.0 has added formal support for temp table status via :meth:`.Inspector.has_table`, the MySQL /MariaDB dialect has been reverted to use the "DESCRIBE" statement as it did in the SQLAlchemy 1.3 series and previously, and test support is added to include MySQL / MariaDB for this behavior. The previous issues with ROLLBACK being emitted which 1.4 sought to improve upon don't apply in SQLAlchemy 2.0 due to simplifications in how :class:`.Connection` handles transactions. DESCRIBE is necessary as MariaDB in particular has no consistently available public information schema of any kind in order to report on temp tables other than DESCRIBE/SHOW COLUMNS, which rely on throwing an error in order to report no results. .. change:: :tags: json, postgresql :tickets: 7147 Implemented missing ``JSONB`` operations: * ` using :meth:`_postgresql.JSONB.Comparator.path_match` * ``?`` using :meth:`_postgresql.JSONB.Comparator.path_exists` * ``-`` using :meth:`_postgresql.JSONB.Comparator.delete_path` Pull request curtesy of Guilherme Martins Crocetti. .. changelog:: ``` ### 2.0.0rc1 ``` :released: December 28, 2022 .. change:: :tags: bug, typing :tickets: 6810, 9025 pep-484 typing has been completed for the ``sqlalchemy.ext.horizontal_shard`` extension as well as the ``sqlalchemy.orm.events`` module. Thanks to Gleb Kisenkov for their efforts. .. change:: :tags: postgresql, bug :tickets: 8977 :versions: 2.0.0rc1 Added support for explicit use of PG full text functions with asyncpg and psycopg (SQLAlchemy 2.0 only), with regards to the ``REGCONFIG`` type cast for the first argument, which previously would be incorrectly cast to a VARCHAR, causing failures on these dialects that rely upon explicit type casts. This includes support for :class:`_postgresql.to_tsvector`, :class:`_postgresql.to_tsquery`, :class:`_postgresql.plainto_tsquery`, :class:`_postgresql.phraseto_tsquery`, :class:`_postgresql.websearch_to_tsquery`, :class:`_postgresql.ts_headline`, each of which will determine based on number of arguments passed if the first string argument should be interpreted as a PostgreSQL "REGCONFIG" value; if so, the argument is typed using a newly added type object :class:`_postgresql.REGCONFIG` which is then explicitly cast in the SQL expression. .. change:: :tags: bug, orm :tickets: 4629 A warning is emitted if a backref name used in :func:`_orm.relationship` names an attribute on the target class which already has a method or attribute assigned to that name, as the backref declaration will replace that attribute. .. change:: :tags: bug, postgresql :tickets: 9020 Fixed regression where newly revised PostgreSQL range types such as :class:`_postgresql.INT4RANGE` could not be set up as the impl of a :class:`.TypeDecorator` custom type, instead raising a ``TypeError``. .. change:: :tags: usecase, orm :tickets: 7837 Adjustments to the :class:`_orm.Session` in terms of extensibility, as well as updates to the :class:`.ShardedSession` extension: * :meth:`_orm.Session.get` now accepts :paramref:`_orm.Session.get.bind_arguments`, which in particular may be useful when using the horizontal sharding extension. * :meth:`_orm.Session.get_bind` accepts arbitrary kw arguments, which assists in developing code that uses a :class:`_orm.Session` class which overrides this method with additional arguments. * Added a new ORM execution option ``identity_token`` which may be used to directly affect the "identity token" that will be associated with newly loaded ORM objects. This token is how sharding approaches (namely the :class:`.ShardedSession`, but can be used in other cases as well) separate object identities across different "shards". .. seealso:: :ref:`queryguide_identity_token` * The :meth:`_orm.SessionEvents.do_orm_execute` event hook may now be used to affect all ORM-related options, including ``autoflush``, ``populate_existing``, and ``yield_per``; these options are re-consumed subsequent to event hooks being invoked before they are acted upon. Previously, options like ``autoflush`` would have been already evaluated at this point. The new ``identity_token`` option is also supported in this mode and is now used by the horizontal sharding extension. * The :class:`.ShardedSession` class replaces the :paramref:`.ShardedSession.id_chooser` hook with a new hook :paramref:`.ShardedSession.identity_chooser`, which no longer relies upon the legacy :class:`_orm.Query` object. :paramref:`.ShardedSession.id_chooser` is still accepted in place of :paramref:`.ShardedSession.identity_chooser` with a deprecation warning. .. change:: :tags: usecase, orm :tickets: 9015 The behavior of "joining an external transaction into a Session" has been revised and improved, allowing explicit control over how the :class:`_orm.Session` will accommodate an incoming :class:`_engine.Connection` that already has a transaction and possibly a savepoint already established. The new parameter :paramref:`_orm.Session.join_transaction_mode` includes a series of option values which can accommodate the existing transaction in several ways, most importantly allowing a :class:`_orm.Session` to operate in a fully transactional style using savepoints exclusively, while leaving the externally initiated transaction non-committed and active under all circumstances, allowing test suites to rollback all changes that take place within tests. Additionally, revised the :meth:`_orm.Session.close` method to fully close out savepoints that may still be present, which also allows the "external transaction" recipe to proceed without warnings if the :class:`_orm.Session` did not explicitly end its own SAVEPOINT transactions. .. seealso:: :ref:`change_9015` .. change:: :tags: bug, sql :tickets: 8988 Added test support to ensure that all compiler ``visit_xyz()`` methods across all :class:`.Compiler` implementations in SQLAlchemy accept a ``**kw`` parameter, so that all compilers accept additional keyword arguments under all circumstances. .. change:: :tags: bug, postgresql :tickets: 8984 The :meth:`_postgresql.Range.__eq___` will now return ``NotImplemented`` when comparing with an instance of a different class, instead of raising an :exc:`AttributeError` exception. .. change:: :tags: bug, sql :tickets: 6114 The :meth:`.SQLCompiler.construct_params` method, as well as the :attr:`.SQLCompiler.params` accessor, will now return the exact parameters that correspond to a compiled statement that used the ``render_postcompile`` parameter to compile. Previously, the method returned a parameter structure that by itself didn't correspond to either the original parameters or the expanded ones. Passing a new dictionary of parameters to :meth:`.SQLCompiler.construct_params` for a :class:`.SQLCompiler` that was constructed with ``render_postcompile`` is now disallowed; instead, to make a new SQL string and parameter set for an alternate set of parameters, a new method :meth:`.SQLCompiler.construct_expanded_state` is added which will produce a new expanded form for the given parameter set, using the :class:`.ExpandedState` container which includes a new SQL statement and new parameter dictionary, as well as a positional parameter tuple. .. change:: :tags: bug, orm :tickets: 8703, 8997, 8996 A series of changes and improvements regarding :meth:`_orm.Session.refresh`. The overall change is that primary key attributes for an object are now included in a refresh operation unconditionally when relationship-bound attributes are to be refreshed, even if not expired and even if not specified in the refresh. * Improved :meth:`_orm.Session.refresh` so that if autoflush is enabled (as is the default for :class:`_orm.Session`), the autoflush takes place at an earlier part of the refresh process so that pending primary key changes are applied without errors being raised. Previously, this autoflush took place too late in the process and the SELECT statement would not use the correct key to locate the row and an :class:`.InvalidRequestError` would be raised. * When the above condition is present, that is, unflushed primary key changes are present on the object, but autoflush is not enabled, the refresh() method now explicitly disallows the operation to proceed, and an informative :class:`.InvalidRequestError` is raised asking that the pending primary key changes be flushed first. Previously, this use case was simply broken and :class:`.InvalidRequestError` would be raised anyway. This restriction is so that it's safe for the primary key attributes to be refreshed, as is necessary for the case of being able to refresh the object with relationship-bound secondary eagerloaders also being emitted. This rule applies in all cases to keep API behavior consistent regardless of whether or not the PK cols are actually needed in the refresh, as it is unusual to be refreshing some attributes on an object while keeping other attributes "pending" in any case. * The :meth:`_orm.Session.refresh` method has been enhanced such that attributes which are :func:`_orm.relationship`-bound and linked to an eager loader, either at mapping time or via last-used loader options, will be refreshed in all cases even when a list of attributes is passed that does not include any columns on the parent row. This builds upon the feature first implemented for non-column attributes as part of :ticket:`1763` fixed in 1.4 allowing eagerly-loaded relationship-bound attributes to participate in the :meth:`_orm.Session.refresh` operation. If the refresh operation does not indicate any columns on the parent row to be refreshed, the primary key columns will nonetheless be included in the refresh operation, which allows the load to proceed into the secondary relationship loaders indicated as it does normally. Previously an :class:`.InvalidRequestError` error would be raised for this condition (:ticket:`8703`) * Fixed issue where an unnecessary additional SELECT would be emitted in the case where :meth:`_orm.Session.refresh` were called with a combination of expired attributes, as well as an eager loader such as :func:`_orm.selectinload` that emits a "secondary" query, if the primary key attributes were also in an expired state. As the primary key attributes are now included in the refresh automatically, there is no additional load for these attributes when a relationship loader goes to select for them (:ticket:`8997`) * Fixed regression caused by :ticket:`8126` released in 2.0.0b1 where the :meth:`_orm.Session.refresh` method would fail with an ``AttributeError``, if passed both an expired column name as well as the name of a relationship-bound attribute that was linked to a "secondary" eagerloader such as the :func:`_orm.selectinload` eager loader (:ticket:`8996`) .. change:: :tags: bug, sql :tickets: 8994 To accommodate for third party dialects with different character escaping needs regarding bound parameters, the system by which SQLAlchemy "escapes" (i.e., replaces with another character in its place) special characters in bound parameter names has been made extensible for third party dialects, using the :attr:`.SQLCompiler.bindname_escape_chars` dictionary which can be overridden at the class declaration level on any :class:`.SQLCompiler` subclass. As part of this change, also added the dot ``"."`` as a default "escaped" character. .. change:: :tags: orm, feature :tickets: 8889 Added a new default value for the :paramref:`.Mapper.eager_defaults` parameter "auto", which will automatically fetch table default values during a unit of work flush, if the dialect supports RETURNING for the INSERT being run, as well as :ref:`insertmanyvalues <engine_insertmanyvalues>` available. Eager fetches for server-side UPDATE defaults, which are very uncommon, continue to only take place if :paramref:`.Mapper.eager_defaults` is set to ``True``, as there is no batch-RETURNING form for UPDATE statements. .. change:: :tags: usecase, orm :tickets: 8973 Removed the requirement that the ``__allow_unmapped__`` attribute be used on Declarative Dataclass Mapped class when non-``Mapped[]`` annotations are detected; previously, an error message that was intended to support legacy ORM typed mappings would be raised, which additionally did not mention correct patterns to use with Dataclasses specifically. This error message is now no longer raised if :meth:`_orm.registry.mapped_as_dataclass` or :class:`_orm.MappedAsDataclass` is used. .. seealso:: :ref:`orm_declarative_native_dataclasses_non_mapped_fields` .. change:: :tags: bug, orm :tickets: 8168 Improved a fix first made in version 1.4 for :ticket:`8456` which scaled back the usage of internal "polymorphic adapters", that are used to render ORM queries when the :paramref:`_orm.Mapper.with_polymorphic` parameter is used. These adapters, which are very complex and error prone, are now used only in those cases where an explicit user-supplied subquery is used for :paramref:`_orm.Mapper.with_polymorphic`, which includes only the use case of concrete inheritance mappings that use the :func:`_orm.polymorphic_union` helper, as well as the legacy use case of using an aliased subquery for joined inheritance mappings, which is not needed in modern use. For the most common case of joined inheritance mappings that use the built-in polymorphic loading scheme, which includes those which make use of the :paramref:`_orm.Mapper.polymorphic_load` parameter set to ``inline``, polymorphic adapters are now no longer used. This has both a positive performance impact on the construction of queries as well as a substantial simplification of the internal query rendering process. The specific issue targeted was to allow a :func:`_orm.column_property` to refer to joined-inheritance classes within a scalar subquery, which now works as intuitively as is feasible. .. changelog:: ``` ### 2.0.0b4 ``` :released: December 5, 2022 .. change:: :tags: usecase, orm :tickets: 8859 Added support custom user-defined types which extend the Python ``enum.Enum`` base class to be resolved automatically to SQLAlchemy :class:`.Enum` SQL types, when using the Annotated Declarative Table feature. The feature is made possible through new lookup features added to the ORM type map feature, and includes support for changing the arguments of the :class:`.Enum` that's generated by default as well as setting up specific ``enum.Enum`` types within the map with specific arguments. .. seealso:: :ref:`orm_declarative_mapped_column_enums` .. change:: :tags: bug, typing :tickets: 8783 Adjusted internal use of the Python ``enum.IntFlag`` class which changed its behavioral contract in Python 3.11. This was not causing runtime failures however caused typing runs to fail under Python 3.11. .. change:: :tags: usecase, typing :tickets: 8847 Added a new type :class:`.SQLColumnExpression` which may be indicated in user code to represent any SQL column oriented expression, including both those based on :class:`.ColumnElement` as well as on ORM :class:`.QueryableAttribute`. This type is a real class, not an alias, so can also be used as the foundation for other objects. An additional ORM-specific subclass :class:`.SQLORMExpression` is also included. .. change:: :tags: bug, typing :tickets: 8667, 6810 The ``sqlalchemy.ext.mutable`` extension and ``sqlalchemy.ext.automap`` extensions are now fully pep-484 typed. Huge thanks to Gleb Kisenkov for their efforts on this. .. change:: :tags: bug, sql :tickets: 8849 The approach to the ``numeric`` pep-249 paramstyle has been rewritten, and is now fully supported, including by features such as "expanding IN" and "insertmanyvalues". Parameter names may also be repeated in the source SQL construct which will be correctly represented within the numeric format using a single parameter. Introduced an additional numeric paramstyle called ``numeric_dollar``, which is specifically what's used by the asyncpg dialect; the paramstyle is equivalent to ``numeric`` except numeric indicators are indicated by a dollar-sign rather than a colon. The asyncpg dialect now uses ``numeric_dollar`` paramstyle directly, rather than compiling to ``format`` style first. The ``numeric`` and ``numeric_dollar`` paramstyles assume that the target backend is capable of receiving the numeric parameters in any order, and will match the given parameter values to the statement based on matching their position (1-based) to the numeric indicator. This is the normal behavior of "numeric" paramstyles, although it was observed that the SQLite DBAPI implements a not-used "numeric" style that does not honor parameter ordering. .. change:: :tags: usecase, postgresql :tickets: 8765 Complementing :ticket:`8690`, new comparison methods such as :meth:`_postgresql.Range.adjacent_to`, :meth:`_postgresql.Range.difference`, :meth:`_postgresql.Range.union`, etc., were added to the PG-specific range objects, bringing them in par with the standard operators implemented by the underlying :attr:`_postgresql.AbstractRange.comparator_factory`. In addition, the ``__bool__()`` method of the class has been corrected to be consistent with the common Python containers behavior as well as how other popular PostgreSQL drivers do: it now tells whether the range instance is *not* empty, rather than the other way around. Pull request courtesy Lele Gaifax. .. change:: :tags: bug, sql :tickets: 8770 Adjusted the rendering of ``RETURNING``, in particular when using :class:`_sql.Insert`, such that it now renders columns using the same logic as that of the :class:`.Select` construct to generate labels, which will include disambiguating labels, as well as that a SQL function surrounding a named column will be labeled using the column name itself. This establishes better cross-compatibility when selecting rows from either :class:`.Select` constructs or from DML statements that use :meth:`.UpdateBase.returning`. A narrower scale change was also made for the 1.4 series that adjusted the function label issue only. .. change:: :tags: change, postgresql, asyncpg :tickets: 8926 Changed the paramstyle used by asyncpg from ``format`` to ``numeric_dollar``. This has two main benefits since it does not require additional processing of the statement and allows for duplicate parameters to be present in the statements. .. change:: :tags: bug, orm :tickets: 8888 Fixed issue where use of an unknown datatype within a :class:`.Mapped` annotation for a column-based attribute would silently fail to map the attribute, rather than reporting an exception; an informative exception message is now raised. .. change:: :tags: bug, orm :tickets: 8777 Fixed a suite of issues involving :class:`.Mapped` use with dictionary types, such as ``Mapped[dict[str, str] | None]``, would not be correctly interpreted in Declarative ORM mappings. Support to correctly "de-optionalize" this type including for lookup in ``type_annotation_map`` has been fixed. .. change:: :tags: feature, orm :tickets: 8822 Added a new parameter :paramref:`_orm.mapped_column.use_existing_column` to accommodate the use case of a single-table inheritance mapping that uses the pattern of more than one subclass indicating the same column to take place on the superclass. This pattern was previously possible by using :func:`_orm.declared_attr` in conjunction with locating the existing column in the ``.__table__`` of the superclass, however is now updated to work with :func:`_orm.mapped_column` as well as with pep-484 typing, in a simple and succinct way. .. seealso:: :ref:`orm_inheritance_column_conflicts` .. change:: :tags: bug, mssql :tickets: 8917 Fixed regression caused by the combination of :ticket:`8177`, re-enable setinputsizes for SQL server unless fast_executemany + DBAPI executemany is used for a statement, along with :ticket:`6047`, implement "insertmanyvalues", which bypasses DBAPI executemany in place of a custom DBAPI execute for INSERT statements. setinputsizes would incorrectly not be used for a multiple parameter-set INSERT statement that used "insertmanyvalues" if fast_executemany were turned on, as the check would incorrectly assume this is a DBAPI executemany call. The "regression" would then be that the "insertmanyvalues" statement format is apparently slightly more sensitive to multiple rows that don't use the same types for each row, so in such a case setinputsizes is especially needed. The fix repairs the fast_executemany check so that it only disables setinputsizes if true DBAPI executemany is to be used. .. change:: :tags: bug, orm, performance :tickets: 8796 Additional performance enhancements within ORM-enabled SQL statements, specifically targeting callcounts within the construction of ORM statements, using combinations of :func:`_orm.aliased` with :func:`_sql.union` and similar "compound" constructs, in addition to direct performance improvements to the ``corresponding_column()`` internal method that is used heavily by the ORM by constructs like :func:`_orm.aliased` and similar. .. change:: :tags: bug, postgresql :tickets: 8884 Added additional type-detection for the new PostgreSQL :class:`_postgresql.Range` type, where previous cases that allowed the psycopg2-native range objects to be received directly by the DBAPI without SQLAlchemy intercepting them stopped working, as we now have our own value object. The :class:`_postgresql.Range` object has been enhanced such that SQLAlchemy Core detects it in otherwise ambiguous situations (such as comparison to dates) and applies appropriate bind handlers. Pull request courtesy Lele Gaifax. .. change:: :tags: bug, orm :tickets: 8880 Fixed bug in :ref:`orm_declarative_native_dataclasses` feature where using plain dataclass fields with the ``__allow_unmapped__`` directive in a mapping would not create a dataclass with the correct class-level state for those fields, copying the raw ``Field`` object to the class inappropriately after dataclasses itself had replaced the ``Field`` object with the class-level default value. .. change:: :tags: usecase, orm extensions :tickets: 8878 Added support for the :func:`.association_proxy` extension function to take part within Python ``dataclasses`` configuration, when using the native dataclasses feature described at :ref:`orm_declarative_native_dataclasses`. Included are attribute-level arguments including :paramref:`.association_proxy.init` and :paramref:`.association_proxy.default_factory`. Documentation for association proxy has also been updated to use "Annotated Declarative Table" forms within examples, including type annotations used for :class:`.AssocationProxy` itself. .. change:: :tags: bug, typing Corrected typing support for the :paramref:`_orm.relationship.secondary` argument which may also accept a callable (lambda) that returns a :class:`.FromClause`. .. change:: :tags: bug, orm, regression :tickets: 8812 Fixed regression where flushing a mapped class that's mapped against a subquery, such as a direct mapping or some forms of concrete table inheritance, would fail if the :paramref:`_orm.Mapper.eager_defaults` parameter were used. .. change:: :tags: bug, schema :tickets: 8925 Stricter rules are in place for appending of :class:`.Column` objects to :class:`.Table` objects, both moving some previous deprecation warnings to exceptions, and preventing some previous scenarios that would cause duplicate columns to appear in tables, when :paramref:`.Table.extend_existing` were set to ``True``, for both programmatic :class:`.Table` construction as well as during reflection operations. See :ref:`change_8925` for a rundown of these changes. .. seealso:: :ref:`change_8925` .. change:: :tags: usecase, orm :tickets: 8905 Added :paramref:`_orm.mapped_column.compare` parameter to relevant ORM attribute constructs including :func:`_orm.mapped_column`, :func:`_orm.relationship` etc. to provide for the Python dataclasses ``compare`` parameter on ``field()``, when using the :ref:`orm_declarative_native_dataclasses` feature. Pull request courtesy Simon Schiele. .. change:: :tags: sql, usecase :tickets: 6289 Added :class:`_expression.ScalarValues` that can be used as a column element allowing using :class:`_expression.Values` inside ``IN`` clauses or in conjunction with ``ANY`` or ``ALL`` collection aggregates. This new class is generated using the method :meth:`_expression.Values.scalar_values`. The :class:`_expression.Values` instance is now coerced to a :class:`_expression.ScalarValues` when used in a ``IN`` or ``NOT IN`` operation. .. change:: :tags: bug, orm :tickets: 8853 Fixed regression in 2.0.0b3 caused by :ticket:`8759` where indicating the :class:`.Mapped` name using a qualified name such as ``sqlalchemy.orm.Mapped`` would fail to be recognized by Declarative as indicating the :class:`.Mapped` construct. .. change:: :tags: bug, typing :tickets: 8842 Improved the typing for :class:`.sessionmaker` and :class:`.async_sessionmaker`, so that the default type of their return value will be :class:`.Session` or :class:`.AsyncSession`, without the need to type this explicitly. Previously, Mypy would not automaticaly infer these return types from its generic base. As part of this change, arguments for :class:`.Session`, :class:`.AsyncSession`, :class:`.sessionmaker` and :class:`.async_sessionmaker` beyond the initial "bind" argument have been made keyword-only, which includes parameters that have always been documented as keyword arguments, such as :paramref:`.Session.autoflush`, :paramref:`.Session.class_`, etc. Pull request courtesy Sam Bull. .. change:: :tags: bug, typing :tickets: 8776 Fixed issue where passing a callbale function returning an iterable of column elements to :paramref:`_orm.relationship.order_by` was flagged as an error in type checkers. .. changelog:: ``` ### 2.0.0b3 ``` :released: November 4, 2022 .. change:: :tags: bug, orm, declarative :tickets: 8759 Added support in ORM declarative annotations for class names specified for :func:`_orm.relationship`, as well as the name of the :class:`_orm.Mapped` symbol itself, to be different names than their direct class name, to support scenarios such as where :class:`_orm.Mapped` is imported as ``from sqlalchemy.orm import Mapped as M``, or where related class names are imported with an alternate name in a similar fashion. Additionally, a target class name given as the lead argument for :func:`_orm.relationship` will always supersede the name given in the left hand annotation, so that otherwise un-importable names that also don't match the class name can still be used in annotations. .. change:: :tags: bug, orm, declarative :tickets: 8692 Improved support for legacy 1.4 mappings that use annotations which don't include ``Mapped[]``, by ensuring the ``__allow_unmapped__`` attribute can be used to allow such legacy annotations to pass through Annotated Declarative without raising an error and without being interpreted in an ORM runtime context. Additionally improved the error message generated when this condition is detected, and added more documentation for how this situation should be handled. Unfortunately the 1.4 WARN_SQLALCHEMY_20 migration warning cannot detect this particular configurational issue at runtime with its current architecture. .. change:: :tags: usecase, postgresql :tickets: 8690 Refined the new approach to range objects described at :ref:`change_7156` to accommodate driver-specific range and multirange objects, to better accommodate both legacy code as well as when passing results from raw SQL result sets back into new range or multirange expressions. .. change:: :tags: usecase, engine :tickets: 8717 Added new parameter :paramref:`.PoolEvents.reset.reset_state` parameter to the :meth:`.PoolEvents.reset` event, with deprecation logic in place that will continue to accept event hooks using the previous set of arguments. This indicates various state information about how the reset is taking place and is used to allow custom reset schemes to take place with full context given. Within this change a fix that's also backported to 1.4 is included which re-enables the :meth:`.PoolEvents.reset` event to continue to take place under all circumstances, including when :class:`.Connection` has already "reset" the connection. The two changes together allow custom reset schemes to be implemented using the :meth:`.PoolEvents.reset` event, instead of the :meth:`.PoolEvents.checkin` event (which continues to function as it always has). .. change:: :tags: bug, orm, declarative :tickets: 8705 Changed a fundamental configuration behavior of :class:`.Mapper`, where :class:`_schema.Column` objects that are explicitly present in the :paramref:`_orm.Mapper.properties` dictionary, either directly or enclosed within a mapper property object, will now be mapped within the order of how they appear within the mapped :class:`.Table` (or other selectable) itself (assuming they are in fact part of that table's list of columns), thereby maintaining the same order of columns in the mapped selectable as is instrumented on the mapped class, as well as what renders in an ORM SELECT statement for that mapper. Previously (where "previously" means since version 0.0.1), :class:`.Column` objects in the :paramref:`_orm.Mapper.properties` dictionary would always be mapped first, ahead of when the other columns in the mapped :class:`.Table` would be mapped, causing a discrepancy in the order in which the mapper would assign attributes to the mapped class as well as the order in which they would render in statements. The change most prominently takes place in the way that Declarative assigns declared columns to the :class:`.Mapper`, specifically how :class:`.Column` (or :func:`_orm.mapped_column`) objects are handled when they have a DDL name that is explicitly different from the mapped attribute name, as well as when constructs such as :func:`_orm.deferred` etc. are used. The new behavior will see the column ordering within the mapped :class:`.Table` being the same order in which the attributes are mapped onto the class, assigned within the :class:`.Mapper` itself, and rendered in ORM statements such as SELECT statements, independent of how the :class:`_schema.Column` was configured against the :class:`.Mapper`. .. change:: :tags: feature, engine :tickets: 8710 To better support the use case of iterating :class:`.Result` and :class:`.AsyncResult` objects where user-defined exceptions may interrupt the iteration, both objects as well as variants such as :class:`.ScalarResult`, :class:`.MappingResult`, :class:`.AsyncScalarResult`, :class:`.AsyncMappingResult` now support context manager usage, where the result will be closed at the end of the context manager block. In addition, ensured that all the above mentioned :class:`.Result` objects include a :meth:`.Result.close` method as well as :attr:`.Result.closed` accessors, including :class:`.ScalarResult` and :class:`.MappingResult` which previously did not have a ``.close()`` method. .. seealso:: :ref:`change_8710` .. change:: :tags: bug, typing Corrected various typing issues within the engine and async engine packages. .. change:: :tags: bug, orm, declarative :tickets: 8718 Fixed issue in new dataclass mapping feature where a column declared on the decalrative base / abstract base / mixin would leak into the constructor for an inheriting subclass under some circumstances. .. change:: :tags: bug, orm declarative :tickets: 8742 Fixed issues within the declarative typing resolver (i.e. which resolves ``ForwardRef`` objects) where types that were declared for columns in one particular source file would raise ``NameError`` when the ultimate mapped class were in another source file. The types are now resolved in terms of the module for each class in which the types are used. .. change:: :tags: feature, postgresql :tickets: 8706 Added new methods :meth:`_postgresql.Range.contains` and :meth:`_postgresql.Range.contained_by` to the new :class:`.Range` data object, which mirror the behavior of the PostgreSQL ``>`` and ``<`` operators, as well as the :meth:`_postgresql.AbstractRange.comparator_factory.contains` and :meth:`_postgresql.AbstractRange.comparator_factory.contained_by` SQL operator methods. Pull request courtesy Lele Gaifax. .. changelog:: ``` ### 2.0.0b2 ``` :released: October 20, 2022 .. change:: :tags: bug, orm :tickets: 8656 Removed the warning that emits when using ORM-enabled update/delete regarding evaluation of columns by name, first added in :ticket:`4073`; this warning actually covers up a scenario that otherwise could populate the wrong Python value for an ORM mapped attribute depending on what the actual column is, so this deprecated case is removed. In 2.0, ORM enabled update/delete uses "auto" for "synchronize_session", which should do the right thing automatically for any given UPDATE expression. .. change:: :tags: bug, mssql :tickets: 8661 Fixed regression caused by SQL Server pyodbc change :ticket:`8177` where we now use ``setinputsizes()`` by default; for VARCHAR, this fails if the character size is greater than 4000 (or 2000, depending on data) characters as the incoming datatype is NVARCHAR, which has a limit of 4000 characters, despite the fact that VARCHAR can handle unlimited characters. Additional pyodbc-specific typing information is now passed to ``setinputsizes()`` when the datatype's size is > 2000 characters. The change is also applied to the :class:`_types.JSON` type which was also impacted by this issue for large JSON serializations. .. change:: :tags: bug, typing :tickets: 8645 Fixed typing issue where pylance strict mode would report "instance variable overrides class variable" when using a method to define ``__tablename__``, ``__mapper_args__`` or ``__table_args__``. .. change:: :tags: mssql, bug :tickets: 7211 The :class:`.Sequence` construct restores itself to the DDL behavior it had prior to the 1.4 series, where creating a :class:`.Sequence` with no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction **without** any additional parameters for "start value". For most backends, this is how things worked previously in any case; **however**, for MS SQL Server, the default value on this database is ``-2**63``; to prevent this generally impractical default from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter should be provided. As usage of :class:`.Sequence` is unusual for SQL Server which for many years has standardized on ``IDENTITY``, it is hoped that this change has minimal impact. .. seealso:: :ref:`change_7211` .. change:: :tags: bug, declarative, orm :tickets: 8665 Improved the :class:`.DeclarativeBase` class so that when combined with other mixins like :class:`.MappedAsDataclass`, the order of the classes may be in either order. .. change:: :tags: usecase, declarative, orm :tickets: 8665 Added support for mapped classes that are also ``Generic`` subclasses, to be specified as a ``GenericAlias`` object (e.g. ``MyClass[str]``) within statements and calls to :func:`_sa.inspect`. .. change:: :tags: bug, orm, declarative :tickets: 8668 Fixed bug in new ORM typed declarative mappings where the ability to use ``Optional[MyClass]`` or similar forms such as ``MyClass | None`` in the type annotation for a many-to-one relationship was not implemented, leading to errors. Documentation has also been added for this use case to the relationship configuration documentation. .. change:: :tags: bug, typing :tickets: 8644 Fixed typing issue where pylance strict mode would report "partially unknown" datatype for the :func:`_orm.mapped_column` construct. .. change:: :tags: bug, regression, sql :tickets: 8639 Fixed bug in new "insertmanyvalues" feature where INSERT that included a subquery with :func:`_sql.bindparam` inside of it would fail to render correctly in "insertmanyvalues" format. This affected psycopg2 most directly as "insertmanyvalues" is used unconditionally with this driver. .. change:: :tags: bug, orm, declarative :tickets: 8688 Fixed issue with new dataclass mapping feature where arguments passed to the dataclasses API could sometimes be mis-ordered when dealing with mixins that override :func:`_orm.mapped_column` declarations, leading to initializer problems. .. changelog:: ``` ### 2.0.0b1 ``` :released: October 13, 2022 .. change:: :tags: bug, sql :tickets: 7888 The FROM clauses that are established on a :func:`_sql.select` construct when using the :meth:`_sql.Select.select_from` method will now render first in the FROM clause of the rendered SELECT, which serves to maintain the ordering of clauses as was passed to the :meth:`_sql.Select.select_from` method itself without being affected by the presence of those clauses also being mentioned in other parts of the query. If other elements of the :class:`_sql.Select` also generate FROM clauses, such as the columns clause or WHERE clause, these will render after the clauses delivered by :meth:`_sql.Select.select_from` assuming they were not explictly passed to :meth:`_sql.Select.select_from` also. This improvement is useful in those cases where a particular database generates a desirable query plan based on a particular ordering of FROM clauses and allows full control over the ordering of FROM clauses. .. change:: :tags: usecase, sql :tickets: 7998 Altered the compilation mechanics of the :class:`_dml.Insert` construct such that the "autoincrement primary key" column value will be fetched via ``cursor.lastrowid`` or RETURNING even if present in the parameter set or within the :meth:`_dml.Insert.values` method as a plain bound value, for single-row INSERT statements on specific backends that are known to generate autoincrementing values even when explicit NULL is passed. This restores a behavior that was in the 1.3 series for both the use case of separate parameter set as well as :meth:`_dml.Insert.values`. In 1.4, the parameter set behavior unintentionally changed to no longer do this, but the :meth:`_dml.Insert.values` method would still fetch autoincrement values up until 1.4.21 where :ticket:`6770` changed the behavior yet again again unintentionally as this use case was never covered. The behavior is now defined as "working" to suit the case where databases such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key value and nonetheless invoke an autoincrement generator. .. change:: :tags: change, postgresql SQLAlchemy now requires PostgreSQL version 9 or greater. Older versions may still work in some limited use cases. .. change:: :tags: bug, orm Fixed issue where the :meth:`_orm.registry.map_declaratively` method would return an internal "mapper config" object and not the :class:`.Mapper` object as stated in the API documentation. .. change:: :tags: sybase, removed :tickets: 7258 Removed the "sybase" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available. .. seealso:: :ref:`external_toplevel` .. change:: :tags: bug, orm :tickets: 7463 Fixed performance regression which appeared at least in version 1.3 if not earlier (sometime after 1.0) where the loading of deferred columns, those explicitly mapped with :func:`_orm.defer` as opposed to non-deferred columns that were expired, from a joined inheritance subclass would not use the "optimized" query which only queried the immediate table that contains the unloaded columns, instead running a full ORM query which would emit a JOIN for all base tables, which is not necessary when only loading columns from the subclass. .. change:: :tags: bug, sql :tickets: 7791 The :paramref:`.Enum.length` parameter, which sets the length of the ``VARCHAR`` column for non-native enumeration types, is now used unconditionally when emitting DDL for the ``VARCHAR`` datatype, including when the :paramref:`.Enum.native_enum` parameter is set to ``True`` for target backends that continue to use ``VARCHAR``. Previously the parameter would be erroneously ignored in this case. The warning previously emitted for this case is now removed. .. change:: :tags: bug, orm :tickets: 6986 The internals for the :class:`_orm.Load` object and related loader strategy patterns have been mostly rewritten, to take advantage of the fact that only attribute-bound paths, not strings, are now supported. The rewrite hopes to make it more straightforward to address new use cases and subtle issues within the loader strategy system going forward. .. change:: :tags: usecase, orm Added :paramref:`_orm.load_only.raiseload` parameter to the :func:`_orm.load_only` loader option, so that the unloaded attributes may have "raise" behavior rather than lazy loading. Previously there wasn't really a way to do this with the :func:`_orm.load_only` option directly. .. change:: :tags: change, engine :tickets: 7122 Some small API changes regarding engines and dialects: * The :meth:`.Dialect.set_isolation_level`, :meth:`.Dialect.get_isolation_level`, :meth: dialect methods will always be passed the raw DBAPI connection * The :class:`.Connection` and :class:`.Engine` classes no longer share a base ``Connectable`` superclass, which has been removed. * Added a new interface class :class:`.PoolProxiedConnection` - this is the public facing interface for the familiar :class:`._ConnectionFairy` class which is nonetheless a private class. .. change:: :tags: feature, sql :tickets: 3482 Added long-requested case-insensitive string operators :meth:`_sql.ColumnOperators.icontains`, :meth:`_sql.ColumnOperators.istartswith`, :meth:`_sql.ColumnOperators.iendswith`, which produce case-insensitive LIKE compositions (using ILIKE on PostgreSQL, and the LOWER() function on all other backends) to complement the existing LIKE composition operators :meth:`_sql.ColumnOperators.contains`, :meth:`_sql.ColumnOperators.startswith`, etc. Huge thanks to Matias Martinez Rebori for their meticulous and complete efforts in implementing these new methods. .. change:: :tags: usecase, postgresql :tickets: 8138 Added literal type rendering for the :class:`_sqltypes.ARRAY` and :class:`_postgresql.ARRAY` datatypes. The generic stringify will render using brackets, e.g. ``[1, 2, 3]`` and the PostgreSQL specific will use the ARRAY literal e.g. ``ARRAY[1, 2, 3]``. Multiple dimensions and quoting are also taken into account. .. change:: :tags: bug, orm :tickets: 8166 Made an improvement to the "deferred" / "load_only" set of strategy options where if a certain object is loaded from two different logical paths within one query, attributes that have been configured by at least one of the options to be populated will be populated in all cases, even if other load paths for that same object did not set this option. previously, it was based on randomness as to which "path" addressed the object first. .. change:: :tags: feature, orm, sql :tickets: 6047 Added new feature to all included dialects that support RETURNING called "insertmanyvalues". This is a generalization of the "fast executemany" feature first introduced for the psycopg2 driver in 1.4 at :ref:`change_5263`, which allows the ORM to batch INSERT statements into a much more efficient SQL structure while still being able to fetch newly generated primary key and SQL default values using RETURNING. The feature now applies to the many dialects that support RETURNING along with multiple VALUES constructs for INSERT, including all PostgreSQL drivers, SQLite, MariaDB, MS SQL Server. Separately, the Oracle dialect also gains the same capability using native cx_Oracle or OracleDB features. .. change:: :tags: bug, engine :tickets: 8523 The :class:`_pool.QueuePool` now ignores ``max_overflow`` when ``pool_size=0``, properly making the pool unlimited in all cases. .. change:: :tags: bug, sql :tickets: 7909 The in-place type detection for Python integers, as occurs with an expression such as ``literal(25)``, will now apply value-based adaption as well to accommodate Python large integers, where the datatype determined will be :class:`.BigInteger` rather than :class:`.Integer`. This accommodates for dialects such as that of asyncpg which both sends implicit typing information to the driver as well as is sensitive to numeric scale. .. change:: :tags: postgresql, mssql, change :tickets: 7225 The parameter :paramref:`_types.UUID.as_uuid` of :class:`_types.UUID`, previously specific to the PostgreSQL dialect but now generalized for Core (along with a new backend-agnostic :class:`_types.Uuid` datatype) now defaults to ``True``, indicating that Python ``UUID`` objects are accepted by this datatype by default. Additionally, the SQL Server :class:`_mssql.UNIQUEIDENTIFIER` datatype has been converted to be a UUID-receiving type; for legacy code that makes use of :class:`_mssql.UNIQUEIDENTIFIER` using string values, set the :paramref:`_mssql.UNIQUEIDENTIFIER.as_uuid` parameter to ``False``. .. change:: :tags: bug, orm :tickets: 8344 Fixed issue in ORM enabled UPDATE when the statement is created against a joined-inheritance subclass, updating only local table columns, where the "fetch" synchronization strategy would not render the correct RETURNING clause for databases that use RETURNING for fetch synchronization. Also adjusts the strategy used for RETURNING in UPDATE FROM and DELETE FROM statements. .. change:: :tags: usecase, mariadb :tickets: 8344 Added a new execution option ``is_delete_using=True``, which is consumed by the ORM when using an ORM-enabled DELETE statement in conjunction with the "fetch" synchronization strategy; this option indicates that the DELETE statement is expected to use multiple tables, which on MariaDB is the DELETE..USING syntax. The option then indicates that RETURNING (newly implemented in SQLAlchemy 2.0 for MariaDB for :ticket:`7011`) should not be used for databases that are known to not support "DELETE..USING..RETURNING" syntax, even though they support "DELETE..USING", which is MariaDB's current capability. The rationale for this option is that the current workings of ORM-enabled DELETE doesn't know up front if a DELETE statement is against multiple tables or not until compilation occurs, which is cached in any case, yet it needs to be known so that a SELECT for the to-be-deleted row can be emitted up front. Instead of applying an across-the-board performance penalty for all DELETE statements by proactively checking them all for this relatively unusual SQL pattern, the ``is_delete_using=True`` execution option is requested via a new exception message that is raised within the compilation step. This exception message is specifically (and only) raised when: the statement is an ORM-enabled DELETE where the "fetch" synchronization strategy has been requested; the backend is MariaDB or other backend with this specific limitation; the statement has been detected within the initial compilation that it would otherwise emit "DELETE..USING..RETURNING". By applying the execution option, the ORM knows to run a SELECT upfront instead. A similar option is implemented for ORM-enabled UPDATE but there is not currently a backend where it is needed. .. change:: :tags: bug, orm, asyncio :tickets: 7703 Removed the unused ``**kw`` arguments from :class:`_asyncio.AsyncSession.begin` and :class:`_asyncio.AsyncSession.begin_nested`. These kw aren't used and appear to have been added to the API in error. .. change:: :tags: feature, sql :tickets: 8285 Added new syntax to the :attr:`.FromClause.c` collection on all :class:`.FromClause` objects allowing tuples of keys to be passed to ``__getitem__()``, along with support for the :func:`_sql.select` construct to handle the resulting tuple-like collection directly, allowing the syntax ``select(table.c['a', 'b', 'c'])`` to be possible. The sub-collection returned is itself a :class:`.ColumnCollection` which is also directly consumable by :func:`_sql.select` and similar now. .. seealso:: :ref:`tutorial_selecting_columns` .. change:: :tags: general, changed :tickets: 7257 Migrated the codebase to remove all pre-2.0 behaviors and architectures that were previously noted as deprecated for removal in 2.0, including, but not limited to: * removal of all Python 2 code, minimum version is now Python 3.7 * :class:`_engine.Engine` and :class:`_engine.Connection` now use the new 2.0 style of working, which includes "autobegin", library level autocommit removed, subtransactions and "branched" connections removed * Result objects use 2.0-style behaviors; :class:`_result.Row` is fully a named tuple without "mapping" behavior, use :class:`_result.RowMapping` for "mapping" behavior * All Unicode encoding/decoding architecture has been removed from SQLAlchemy. All modern DBAPI implementations support Unicode transparently thanks to Python 3, so the ``convert_unicode`` feature as well as related mechanisms to look for bytestrings in DBAPI ``cursor.description`` etc. have been removed. * The ``.bind`` attribute and parameter from :class:`.MetaData`, :class:`.Table`, and from all DDL/DML/DQL elements that previously could refer to a "bound engine" * The standalone ``sqlalchemy.orm.mapper()`` function is removed; all classical mapping should be done through the :meth:`_orm.registry.map_imperatively` method of :class:`_orm.registry`. * The :meth:`_orm.Query.join` method no longer accepts strings for relationship names; the long-documented approach of using ``Class.attrname`` for join targets is now standard. * :meth:`_orm.Query.join` no longer accepts the "aliased" and "from_joinpoint" arguments * :meth:`_orm.Query.join` no longer accepts chains of multiple join targets in one method call. * ``Query.from_self()``, ``Query.select_entity_from()`` and ``Query.with_polymorphic()`` are removed. * The :paramref:`_orm.relationship.cascade_backrefs` parameter must now remain at its new default of ``False``; the ``save-update`` cascade no longer cascades along a backref. * the :paramref:`_orm.Session.future` parameter must always be set to ``True``. 2.0-style transactional patterns for :class:`_orm.Session` are now always in effect. * Loader options no longer accept strings for attribute names. The long-documented approach of using ``Class.attrname`` for loader option targets is now standard. * Legacy forms of :func:`_sql.select` removed, including ``select([cols])``, the "whereclause" and keyword parameters of ``some_table.select()``. * Legacy "in-place mutator" methods on :class:`_sql.Select` such as ``append_whereclause()``, ``append_order_by()`` etc are removed. * Removed the very old "dbapi_proxy" module, which in very early SQLAlchemy releases was used to provide a transparent connection pool over a raw DBAPI connection. .. change:: :tags: feature, orm :tickets: 8375 Added new parameter :paramref:`_orm.AttributeEvents.include_key`, which will include the dictionary or list key for operations such as ``__setitem__()`` (e.g. ``obj[key] = value``) and ``__delitem__()`` (e.g. ``del obj[key]``), using a new keyword parameter "key" or "keys", depending on event, e.g. :paramref:`_orm.AttributeEvents.append.key`, :paramref:`_orm.AttributeEvents.bulk_replace.keys`. This allows event handlers to take into account the key that was passed to the operation and is of particular importance for dictionary operations working with :class:`_orm.MappedCollection`. .. change:: :tags: postgresql, usecase :tickets: 7156, 8540 Adds support for PostgreSQL multirange types, introduced in PostgreSQL 14. Support for PostgreSQL ranges and multiranges has now been generalized to the psycopg3, psycopg2 and asyncpg backends, with room for further dialect support, using a backend-agnostic :class:`_postgresql.Range` data object that's constructor-compatible with the pre