Changelog
### 2.0.4
```
:released: February 17, 2023
.. change::
:tags: bug, orm, regression
:tickets: 9273
Fixed regression introduced in version 2.0.2 due to :ticket:`9217` where
using DML RETURNING statements, as well as
:meth:`_sql.Select.from_statement` constructs as was "fixed" in
:ticket:`9217`, in conjunction with ORM mapped classes that used
expressions such as with :func:`_orm.column_property`, would lead to an
internal error within Core where it would attempt to match the expression
by name. The fix repairs the Core issue, and also adjusts the fix in
:ticket:`9217` to not take effect for the DML RETURNING use case, where it
adds unnecessary overhead.
.. change::
:tags: usecase, typing
:tickets: 9321
Improved the typing support for the :ref:`hybrids_toplevel`
extension, updated all documentation to use ORM Annotated Declarative
mappings, and added a new modifier called :attr:`.hybrid_property.inplace`.
This modifier provides a way to alter the state of a :class:`.hybrid_property`
**in place**, which is essentially what very early versions of hybrids
did, before SQLAlchemy version 1.2.0 :ticket:`3912` changed this to
remove in-place mutation. This in-place mutation is now restored on an
**opt-in** basis to allow a single hybrid to have multiple methods
set up, without the need to name all the methods the same and without the
need to carefully "chain" differently-named methods in order to maintain
the composition. Typing tools such as Mypy and Pyright do not allow
same-named methods on a class, so with this change a succinct method
of setting up hybrids with typing support is restored.
.. seealso::
:ref:`hybrid_pep484_naming`
.. change::
:tags: bug, orm
Marked the internal ``EvaluatorCompiler`` module as private to the ORM, and
renamed it to ``_EvaluatorCompiler``. For users that may have been relying
upon this, the name ``EvaluatorCompiler`` is still present, however this
use is not supported and will be removed in a future release.
.. change::
:tags: orm, use_case
:tickets: 9297
To accommodate a change in column ordering used by ORM Declarative in
SQLAlchemy 2.0, a new parameter :paramref:`_orm.mapped_column.sort_order`
has been added that can be used to control the order of the columns defined
in the table by the ORM, for common use cases such as mixins with primary
key columns that should appear first in tables. The change notes at
:ref:`change_9297` illustrate the default change in ordering behavior
(which is part of all SQLAlchemy 2.0 releases) as well as use of the
:paramref:`_orm.mapped_column.sort_order` to control column ordering when
using mixins and multiple classes (new in 2.0.4).
.. seealso::
:ref:`change_9297`
.. change::
:tags: sql
:tickets: 9277
Added public property :attr:`_schema.Table.autoincrement_column` that
returns the column identified as autoincrementing in the column.
.. change::
:tags: oracle, bug
:tickets: 9295
Adjusted the behavior of the ``thick_mode`` parameter for the
:ref:`oracledb` dialect to correctly accept ``False`` as a value.
Previously, only ``None`` would indicate that thick mode should be
disabled.
.. change::
:tags: usecase, orm
:tickets: 9298
The :meth:`_orm.Session.refresh` method will now immediately load a
relationship-bound attribute that is explicitly named within the
:paramref:`_orm.Session.refresh.attribute_names` collection even if it is
currently linked to the "select" loader, which normally is a "lazy" loader
that does not fire off during a refresh. The "lazy loader" strategy will
now detect that the operation is specifically a user-initiated
:meth:`_orm.Session.refresh` operation which named this attribute
explicitly, and will then call upon the "immediateload" strategy to
actually emit SQL to load the attribute. This should be helpful in
particular for some asyncio situations where the loading of an unloaded
lazy-loaded attribute must be forced, without using the actual lazy-loading
attribute pattern not supported in asyncio.
.. change::
:tags: bug, sql
:tickets: 9313
Fixed issue where element types of a tuple value would be hardcoded to take
on the types from a compared-to tuple, when the comparison were using the
:meth:`.ColumnOperators.in_` operator. This was inconsistent with the usual
way that types are determined for a binary expression, which is that the
actual element type on the right side is considered first before applying
the left-hand-side type.
.. change::
:tags: usecase, orm declarative
:tickets: 9266
Added new parameter ``dataclasses_callable`` to both the
:class:`_orm.MappedAsDataclass` class as well as the
:meth:`_orm.registry.mapped_as_dataclass` method which allows an
alternative callable to Python ``dataclasses.dataclass`` to be used in
order to produce dataclasses. The use case here is to drop in Pydantic's
dataclass function instead. Adjustments have been made to the mixin support
added for :ticket:`9179` in version 2.0.1 so that the ``__annotations__``
collection of the mixin is rewritten to not include the
:class:`_orm.Mapped` container, in the same way as occurs with mapped
classes, so that the Pydantic dataclasses constructor is not exposed to
unknown types.
.. seealso::
:ref:`dataclasses_pydantic`
.. changelog::
```
### 2.0.3
```
:released: February 9, 2023
.. change::
:tags: typing, bug
:tickets: 9254
Remove ``typing.Self`` workaround, now using :pep:`673` for most methods
that return ``Self``. As a consequence of this change ``mypy>=1.0.0`` is
now required to type check SQLAlchemy code.
Pull request courtesy Yurii Karabas.
.. change::
:tags: bug, sql, regression
:tickets: 9271
Fixed critical regression in SQL expression formulation in the 2.0 series
due to :ticket:`7744` which improved support for SQL expressions that
contained many elements against the same operator repeatedly; parenthesis
grouping would be lost with expression elements beyond the first two
elements.
.. changelog::
```
### 2.0.2
```
:released: February 6, 2023
.. change::
:tags: bug, orm declarative
:tickets: 9249
Fixed regression caused by the fix for :ticket:`9171`, which itself was
fixing a regression, involving the mechanics of ``__init__()`` on classes
that extend from :class:`_orm.DeclarativeBase`. The change made it such
that ``__init__()`` was applied to the user-defined base if there were no
``__init__()`` method directly on the class. This has been adjusted so that
``__init__()`` is applied only if no other class in the hierarchy of the
user-defined base has an ``__init__()`` method. This again allows
user-defined base classes based on :class:`_orm.DeclarativeBase` to include
mixins that themselves include a custom ``__init__()`` method.
.. change::
:tags: bug, mysql, regression
:tickets: 9251
Fixed regression caused by issue :ticket:`9058` which adjusted the MySQL
dialect's ``has_table()`` to again use "DESCRIBE", where the specific error
code raised by MySQL version 8 when using a non-existent schema name was
unexpected and failed to be interpreted as a boolean result.
.. change::
:tags: bug, sqlite
:tickets: 9251
Fixed the SQLite dialect's ``has_table()`` function to correctly report
False for queries that include a non-None schema name for a schema that
doesn't exist; previously, a database error was raised.
.. change::
:tags: bug, orm declarative
:tickets: 9226
Fixed issue in ORM Declarative Dataclass mappings related to newly added
support for mixins added in 2.0.1 via :ticket:`9179`, where a combination
of using mixins plus ORM inheritance would mis-classify fields in some
cases leading to field-level dataclass arguments such as ``init=False`` being
lost.
.. change::
:tags: bug, orm, regression
:tickets: 9232
Fixed obscure ORM inheritance issue caused by :ticket:`8705` where some
scenarios of inheriting mappers that indicated groups of columns from the
local table and the inheriting table together under a
:func:`_orm.column_property` would nonetheless warn that properties of the
same name were being combined implicitly.
.. change::
:tags: orm, bug, regression
:tickets: 9228
Fixed regression where using the :paramref:`_orm.Mapper.version_id_col`
feature with a regular Python-side incrementing column would fail to work
for SQLite and other databases that don't support "rowcount" with
"RETURNING", as "RETURNING" would be assumed for such columns even though
that's not what actually takes place.
.. change::
:tags: bug, orm declarative
:tickets: 9240
Repaired ORM Declarative mappings to allow for the
:paramref:`_orm.Mapper.primary_key` parameter to be specified within
``__mapper_args__`` when using :func:`_orm.mapped_column`. Despite this
usage being directly in the 2.0 documentation, the :class:`_orm.Mapper` was
not accepting the :func:`_orm.mapped_column` construct in this context. Ths
feature was already working for the :paramref:`_orm.Mapper.version_id_col`
and :paramref:`_orm.Mapper.polymorphic_on` parameters.
As part of this change, the ``__mapper_args__`` attribute may be specified
without using :func:`_orm.declared_attr` on a non-mapped mixin class,
including a ``"primary_key"`` entry that refers to :class:`_schema.Column`
or :func:`_orm.mapped_column` objects locally present on the mixin;
Declarative will also translate these columns into the correct ones for a
particular mapped class. This again was working already for the
:paramref:`_orm.Mapper.version_id_col` and
:paramref:`_orm.Mapper.polymorphic_on` parameters. Additionally,
elements within ``"primary_key"`` may be indicated as string names of
existing mapped properties.
.. change::
:tags: usecase, sql
:tickets: 8780
Added a full suite of new SQL bitwise operators, for performing
database-side bitwise expressions on appropriate data values such as
integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich.
.. seealso::
:ref:`operators_bitwise`
.. change::
:tags: bug, orm declarative
:tickets: 9211
An explicit error is raised if a mapping attempts to mix the use of
:class:`_orm.MappedAsDataclass` with
:meth:`_orm.registry.mapped_as_dataclass` within the same class hierarchy,
as this produces issues with the dataclass function being applied at the
wrong time to the mapped class, leading to errors during the mapping
process.
.. change::
:tags: bug, orm, regression
:tickets: 9217
Fixed regression when using :meth:`_sql.Select.from_statement` in an ORM
context, where matching of columns to SQL labels based on name alone was
disabled for ORM-statements that weren't fully textual. This would prevent
arbitrary SQL expressions with column-name labels from matching up to the
entity to be loaded, which previously would work within the 1.4
and previous series, so the previous behavior has been restored.
.. change::
:tags: bug, asyncio
:tickets: 9237
Repaired a regression caused by the fix for :ticket:`8419` which caused
asyncpg connections to be reset (i.e. transaction ``rollback()`` called)
and returned to the pool normally in the case that the connection were not
explicitly returned to the connection pool and was instead being
intercepted by Python garbage collection, which would fail if the garbage
collection operation were being called outside of the asyncio event loop,
leading to a large amount of stack trace activity dumped into logging
and standard output.
The correct behavior is restored, which is that all asyncio connections
that are garbage collected due to not being explicitly returned to the
connection pool are detached from the pool and discarded, along with a
warning, rather than being returned the pool, as they cannot be reliably
reset. In the case of asyncpg connections, the asyncpg-specific
``terminate()`` method will be used to end the connection more gracefully
within this process as opposed to just dropping it.
This change includes a small behavioral change that is hoped to be useful
for debugging asyncio applications, where the warning that's emitted in the
case of asyncio connections being unexpectedly garbage collected has been
made slightly more aggressive by moving it outside of a ``try/except``
block and into a ``finally:`` block, where it will emit unconditionally
regardless of whether the detach/termination operation succeeded or not. It
will also have the effect that applications or test suites which promote
Python warnings to exceptions will see this as a full exception raise,
whereas previously it was not possible for this warning to actually
propagate as an exception. Applications and test suites which need to
tolerate this warning in the interim should adjust the Python warnings
filter to allow these warnings to not raise.
The behavior for traditional sync connections remains unchanged, that
garbage collected connections continue to be returned to the pool normally
without emitting a warning. This will likely be changed in a future major
release to at least emit a similar warning as is emitted for asyncio
drivers, as it is a usage error for pooled connections to be intercepted by
garbage collection without being properly returned to the pool.
.. change::
:tags: usecase, orm
:tickets: 9220
Added new event hook :meth:`_orm.MapperEvents.after_mapper_constructed`,
which supplies an event hook to take place right as the
:class:`_orm.Mapper` object has been fully constructed, but before the
:meth:`_orm.registry.configure` call has been called. This allows code that
can create additional mappings and table structures based on the initial
configuration of a :class:`_orm.Mapper`, which also integrates within
Declarative configuration. Previously, when using Declarative, where the
:class:`_orm.Mapper` object is created within the class creation process,
there was no documented means of running code at this point. The change
is to immediately benefit custom mapping schemes such as that
of the :ref:`examples_versioned_history` example, which generate additional
mappers and tables in response to the creation of mapped classes.
.. change::
:tags: usecase, orm
:tickets: 9220
The infrequently used :attr:`_orm.Mapper.iterate_properties` attribute and
:meth:`_orm.Mapper.get_property` method, which are primarily used
internally, no longer implicitly invoke the :meth:`_orm.registry.configure`
process. Public access to these methods is extremely rare and the only
benefit to having :meth:`_orm.registry.configure` would have been allowing
"backref" properties be present in these collections. In order to support
the new :meth:`_orm.MapperEvents.after_mapper_constructed` event, iteration
and access to the internal :class:`_orm.MapperProperty` objects is now
possible without triggering an implicit configure of the mapper itself.
The more-public facing route to iteration of all mapper attributes, the
:attr:`_orm.Mapper.attrs` collection and similar, will still implicitly
invoke the :meth:`_orm.registry.configure` step thus making backref
attributes available.
In all cases, the :meth:`_orm.registry.configure` is always available to
be called directly.
.. change::
:tags: bug, examples
:tickets: 9220
Reworked the :ref:`examples_versioned_history` to work with
version 2.0, while at the same time improving the overall working of
this example to use newer APIs, including a newly added hook
:meth:`_orm.MapperEvents.after_mapper_constructed`.
.. change::
:tags: bug, mysql
:tickets: 8626
Added support for MySQL 8's new ``AS <name> ON DUPLICATE KEY`` syntax when
using :meth:`_mysql.Insert.on_duplicate_key_update`, which is required for
newer versions of MySQL 8 as the previous syntax using ``VALUES()`` now
emits a deprecation warning with those versions. Server version detection
is employed to determine if traditional MariaDB / MySQL < 8 ``VALUES()``
syntax should be used, vs. the newer MySQL 8 required syntax. Pull request
courtesy Caspar Wylie.
.. changelog::
```
### 2.0.1
```
:released: February 1, 2023
.. change::
:tags: bug, typing
:tickets: 9174
Opened up typing on :paramref:`.Select.with_for_update.of` to also accept
table and mapped class arguments, as seems to be available for the MySQL
dialect.
.. change::
:tags: bug, orm, regression
:tickets: 9164
Fixed regression where ORM models that used joined table inheritance with a
composite foreign key would encounter an internal error in the mapper
internals.
.. change::
:tags: bug, sql
:tickets: 7664
Corrected the fix for :ticket:`7664`, released in version 2.0.0, to also
include :class:`.DropSchema` which was inadvertently missed in this fix,
allowing stringification without a dialect. The fixes for both constructs
is backported to the 1.4 series as of 1.4.47.
.. change::
:tags: bug, orm declarative
:tickets: 9175
Added support for :pep:`484` ``NewType`` to be used in the
:paramref:`_orm.registry.type_annotation_map` as well as within
:class:`.Mapped` constructs. These types will behave in the same way as
custom subclasses of types right now; they must appear explicitly within
the :paramref:`_orm.registry.type_annotation_map` to be mapped.
.. change::
:tags: bug, typing
:tickets: 9183
Fixed typing for limit/offset methods including :meth:`.Select.limit`,
:meth:`.Select.offset`, :meth:`_orm.Query.limit`, :meth:`_orm.Query.offset`
to allow ``None``, which is the documented API to "cancel" the current
limit/offset.
.. change::
:tags: bug, orm declarative
:tickets: 9179
When using the :class:`.MappedAsDataclass` superclass, all classes within
the hierarchy that are subclasses of this class will now be run through the
``dataclasses.dataclass`` function whether or not they are actually
mapped, so that non-ORM fields declared on non-mapped classes within the
hierarchy will be used when mapped subclasses are turned into dataclasses.
This behavior applies both to intermediary classes mapped with
``__abstract__ = True`` as well as to the user-defined declarative base
itself, assuming :class:`.MappedAsDataclass` is present as a superclass for
these classes.
This allows non-mapped attributes such as ``InitVar`` declarations on
superclasses to be used, without the need to run the
``dataclasses.dataclass`` decorator explicitly on each non-mapped class.
The new behavior is considered as correct as this is what the :pep:`681`
implementation expects when using a superclass to indicate dataclass
behavior.
.. change::
:tags: bug, typing
:tickets: 9170
Fixed typing issue where :func:`_orm.mapped_column` objects typed as
:class:`_orm.Mapped` wouldn't be accepted in schema constraints such as
:class:`_schema.ForeignKey`, :class:`_schema.UniqueConstraint` or
:class:`_schema.Index`.
.. change::
:tags: bug, orm declarative
:tickets: 9187
Added support for :pep:`586` ``Literal[]`` to be used in the
:paramref:`_orm.registry.type_annotation_map` as well as within
:class:`.Mapped` constructs. To use custom types such as these, they must
appear explicitly within the :paramref:`_orm.registry.type_annotation_map`
to be mapped. Pull request courtesy Frederik Aalund.
As part of this change, the support for :class:`.sqltypes.Enum` in the
:paramref:`_orm.registry.type_annotation_map` has been expanded to include
support for ``Literal[]`` types consisting of string values to be used,
in addition to ``enum.Enum`` datatypes. If a ``Literal[]`` datatype
is used within ``Mapped[]`` that is not linked in
:paramref:`_orm.registry.type_annotation_map` to a specific datatype,
a :class:`.sqltypes.Enum` will be used by default.
.. seealso::
:ref:`orm_declarative_mapped_column_enums`
.. change::
:tags: bug, orm declarative
:tickets: 9200
Fixed issue involving the use of :class:`.sqltypes.Enum` within the
:paramref:`_orm.registry.type_annotation_map` where the
:paramref:`_sqltypes.Enum.native_enum` parameter would not be correctly
copied to the mapped column datatype, if it were overridden
as stated in the documentation to set this parameter to False.
.. change::
:tags: bug, orm declarative, regression
:tickets: 9171
Fixed regression in :class:`.DeclarativeBase` class where the registry's
default constructor would not be applied to the base itself, which is
different from how the previous :func:`_orm.declarative_base` construct
works. This would prevent a mapped class with its own ``__init__()`` method
from calling ``super().__init__()`` in order to access the registry's
default constructor and automatically populate attributes, instead hitting
``object.__init__()`` which would raise a ``TypeError`` on any arguments.
.. change::
:tags: bug, sql, regression
:tickets: 9173
Fixed regression related to the implementation for the new
"insertmanyvalues" feature where an internal ``TypeError`` would occur in
arrangements where a :func:`_sql.insert` would be referred towards inside
of another :func:`_sql.insert` via a CTE; made additional repairs for this
use case for positional dialects such as asyncpg when using
"insertmanyvalues".
.. change::
:tags: bug, typing
:tickets: 9156
Fixed typing for :meth:`_expression.ColumnElement.cast` to accept
both ``Type[TypeEngine[T]]`` and ``TypeEngine[T]``; previously
only ``TypeEngine[T]`` was accepted. Pull request courtesy Yurii Karabas.
.. change::
:tags: bug, orm declarative
:tickets: 9177
Improved the ruleset used to interpret :pep:`593` ``Annotated`` types when
used with Annotated Declarative mapping, the inner type will be checked for
"Optional" in all cases which will be added to the criteria by which the
column is set as "nullable" or not; if the type within the ``Annotated``
container is optional (or unioned with ``None``), the column will be
considered nullable if there are no explicit
:paramref:`_orm.mapped_column.nullable` parameters overriding it.
.. change::
:tags: bug, orm
:tickets: 9182
Improved the error reporting when linking strategy options from a base
class to another attribute that's off a subclass, where ``of_type()``
should be used. Previously, when :meth:`.Load.options` is used, the message
would lack informative detail that ``of_type()`` should be used, which was
not the case when linking the options directly. The informative detail now
emits even if :meth:`.Load.options` is used.
.. changelog::
```
### 2.0.0
```
:released: January 26, 2023
.. change::
:tags: bug, sql
:tickets: 7664
Fixed stringify for a the :class:`.CreateSchema` DDL construct, which
would fail with an ``AttributeError`` when stringified without a
dialect. Update: Note this fix failed to accommodate for
:class:`.DropSchema`; a followup fix in version 2.0.1 repairs this
case. The fix for both elements is backported to 1.4.47.
.. change::
:tags: usecase, orm extensions
:tickets: 5145
Added new feature to :class:`.AutomapBase` for autoload of classes across
multiple schemas which may have overlapping names, by providing a
:paramref:`.AutomapBase.prepare.modulename_for_table` parameter which
allows customization of the ``__module__`` attribute of newly generated
classes, as well as a new collection :attr:`.AutomapBase.by_module`, which
stores a dot-separated namespace of module names linked to classes based on
the ``__module__`` attribute.
Additionally, the :meth:`.AutomapBase.prepare` method may now be invoked
any number of times, with or without reflection enabled; only newly
added tables that were not previously mapped will be processed on each
call. Previously, the :meth:`.MetaData.reflect` method would need to be
called explicitly each time.
.. seealso::
:ref:`automap_by_module` - illustrates use of both techniques at once.
.. change::
:tags: orm, bug
:tickets: 7305
Improved the notification of warnings that are emitted within the configure
mappers or flush process, which are often invoked as part of a different
operation, to add additional context to the message that indicates one of
these operations as the source of the warning within operations that may
not be obviously related.
.. change::
:tags: bug, typing
:tickets: 9129
Added typing for the built-in generic functions that are available from the
:data:`_sql.func` namespace, which accept a particular set of arguments and
return a particular type, such as for :class:`_sql.count`,
:class:`_sql.current_timestamp`, etc.
.. change::
:tags: bug, typing
:tickets: 9120
Corrected the type passed for "lambda statements" so that a plain lambda is
accepted by mypy, pyright, others without any errors about argument types.
Additionally implemented typing for more of the public API for lambda
statements and ensured :class:`.StatementLambdaElement` is part of the
:class:`.Executable` hierarchy so it's typed as accepted by
:meth:`_engine.Connection.execute`.
.. change::
:tags: typing, bug
:tickets: 9122
The :meth:`_sql.ColumnOperators.in_` and
:meth:`_sql.ColumnOperators.not_in` methods are typed to include
``Iterable[Any]`` rather than ``Sequence[Any]`` for more flexibility in
argument type.
.. change::
:tags: typing, bug
:tickets: 9123
The :func:`_sql.or_` and :func:`_sql.and_` from a typing perspective
require the first argument to be present, however these functions still
accept zero arguments which will emit a deprecation warning at runtime.
Typing is also added to support sending the fixed literal ``False`` for
:func:`_sql.or_` and ``True`` for :func:`_sql.and_` as the first argument
only, however the documentation now indicates sending the
:func:`_sql.false` and :func:`_sql.true` constructs in these cases as a
more explicit approach.
.. change::
:tags: typing, bug
:tickets: 9125
Fixed typing issue where iterating over a :class:`_orm.Query` object
was not correctly typed.
.. change::
:tags: typing, bug
:tickets: 9136
Fixed typing issue where the object type when using :class:`_engine.Result`
as a context manager were not preserved, indicating :class:`_engine.Result`
in all cases rather than the specific :class:`_engine.Result` sub-type.
Pull request courtesy Martin Baláž.
.. change::
:tags: typing, bug
:tickets: 9150
Fixed issue where using the :paramref:`_orm.relationship.remote_side`
and similar parameters, passing an annotated declarative object typed as
:class:`_orm.Mapped`, would not be accepted by the type checker.
.. change::
:tags: typing, bug
:tickets: 9148
Added typing to legacy operators such as ``isnot()``, ``notin_()``, etc.
which previously were referencing the newer operators but were not
themselves typed.
.. change::
:tags: feature, orm extensions
:tickets: 7226
Added new option to horizontal sharding API
:class:`_horizontal.set_shard_id` which sets the effective shard identifier
to query against, for both the primary query as well as for all secondary
loaders including relationship eager loaders as well as relationship and
column lazy loaders.
.. change::
:tags: bug, mssql, regression
:tickets: 9142
The newly added comment reflection and rendering capability of the MSSQL
dialect, added in :ticket:`7844`, will now be disabled by default if it
cannot be determined that an unsupported backend such as Azure Synapse may
be in use; this backend does not support table and column comments and does
not support the SQL Server routines in use to generate them as well as to
reflect them. A new parameter ``supports_comments`` is added to the dialect
which defaults to ``None``, indicating that comment support should be
auto-detected. When set to ``True`` or ``False``, the comment support is
either enabled or disabled unconditionally.
.. seealso::
:ref:`mssql_comment_support`
.. changelog::
```
### 2.0.0rc3
```
:released: January 26, 2023
: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 26, 2023
: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: January 26, 2023
: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: January 26, 2023
: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
pyup-bot
commented
1 year ago
This PR updates SQLAlchemy from 1.4.46 to 2.0.4.
Changelog
### 2.0.4 ``` :released: February 17, 2023 .. change:: :tags: bug, orm, regression :tickets: 9273 Fixed regression introduced in version 2.0.2 due to :ticket:`9217` where using DML RETURNING statements, as well as :meth:`_sql.Select.from_statement` constructs as was "fixed" in :ticket:`9217`, in conjunction with ORM mapped classes that used expressions such as with :func:`_orm.column_property`, would lead to an internal error within Core where it would attempt to match the expression by name. The fix repairs the Core issue, and also adjusts the fix in :ticket:`9217` to not take effect for the DML RETURNING use case, where it adds unnecessary overhead. .. change:: :tags: usecase, typing :tickets: 9321 Improved the typing support for the :ref:`hybrids_toplevel` extension, updated all documentation to use ORM Annotated Declarative mappings, and added a new modifier called :attr:`.hybrid_property.inplace`. This modifier provides a way to alter the state of a :class:`.hybrid_property` **in place**, which is essentially what very early versions of hybrids did, before SQLAlchemy version 1.2.0 :ticket:`3912` changed this to remove in-place mutation. This in-place mutation is now restored on an **opt-in** basis to allow a single hybrid to have multiple methods set up, without the need to name all the methods the same and without the need to carefully "chain" differently-named methods in order to maintain the composition. Typing tools such as Mypy and Pyright do not allow same-named methods on a class, so with this change a succinct method of setting up hybrids with typing support is restored. .. seealso:: :ref:`hybrid_pep484_naming` .. change:: :tags: bug, orm Marked the internal ``EvaluatorCompiler`` module as private to the ORM, and renamed it to ``_EvaluatorCompiler``. For users that may have been relying upon this, the name ``EvaluatorCompiler`` is still present, however this use is not supported and will be removed in a future release. .. change:: :tags: orm, use_case :tickets: 9297 To accommodate a change in column ordering used by ORM Declarative in SQLAlchemy 2.0, a new parameter :paramref:`_orm.mapped_column.sort_order` has been added that can be used to control the order of the columns defined in the table by the ORM, for common use cases such as mixins with primary key columns that should appear first in tables. The change notes at :ref:`change_9297` illustrate the default change in ordering behavior (which is part of all SQLAlchemy 2.0 releases) as well as use of the :paramref:`_orm.mapped_column.sort_order` to control column ordering when using mixins and multiple classes (new in 2.0.4). .. seealso:: :ref:`change_9297` .. change:: :tags: sql :tickets: 9277 Added public property :attr:`_schema.Table.autoincrement_column` that returns the column identified as autoincrementing in the column. .. change:: :tags: oracle, bug :tickets: 9295 Adjusted the behavior of the ``thick_mode`` parameter for the :ref:`oracledb` dialect to correctly accept ``False`` as a value. Previously, only ``None`` would indicate that thick mode should be disabled. .. change:: :tags: usecase, orm :tickets: 9298 The :meth:`_orm.Session.refresh` method will now immediately load a relationship-bound attribute that is explicitly named within the :paramref:`_orm.Session.refresh.attribute_names` collection even if it is currently linked to the "select" loader, which normally is a "lazy" loader that does not fire off during a refresh. The "lazy loader" strategy will now detect that the operation is specifically a user-initiated :meth:`_orm.Session.refresh` operation which named this attribute explicitly, and will then call upon the "immediateload" strategy to actually emit SQL to load the attribute. This should be helpful in particular for some asyncio situations where the loading of an unloaded lazy-loaded attribute must be forced, without using the actual lazy-loading attribute pattern not supported in asyncio. .. change:: :tags: bug, sql :tickets: 9313 Fixed issue where element types of a tuple value would be hardcoded to take on the types from a compared-to tuple, when the comparison were using the :meth:`.ColumnOperators.in_` operator. This was inconsistent with the usual way that types are determined for a binary expression, which is that the actual element type on the right side is considered first before applying the left-hand-side type. .. change:: :tags: usecase, orm declarative :tickets: 9266 Added new parameter ``dataclasses_callable`` to both the :class:`_orm.MappedAsDataclass` class as well as the :meth:`_orm.registry.mapped_as_dataclass` method which allows an alternative callable to Python ``dataclasses.dataclass`` to be used in order to produce dataclasses. The use case here is to drop in Pydantic's dataclass function instead. Adjustments have been made to the mixin support added for :ticket:`9179` in version 2.0.1 so that the ``__annotations__`` collection of the mixin is rewritten to not include the :class:`_orm.Mapped` container, in the same way as occurs with mapped classes, so that the Pydantic dataclasses constructor is not exposed to unknown types. .. seealso:: :ref:`dataclasses_pydantic` .. changelog:: ``` ### 2.0.3 ``` :released: February 9, 2023 .. change:: :tags: typing, bug :tickets: 9254 Remove ``typing.Self`` workaround, now using :pep:`673` for most methods that return ``Self``. As a consequence of this change ``mypy>=1.0.0`` is now required to type check SQLAlchemy code. Pull request courtesy Yurii Karabas. .. change:: :tags: bug, sql, regression :tickets: 9271 Fixed critical regression in SQL expression formulation in the 2.0 series due to :ticket:`7744` which improved support for SQL expressions that contained many elements against the same operator repeatedly; parenthesis grouping would be lost with expression elements beyond the first two elements. .. changelog:: ``` ### 2.0.2 ``` :released: February 6, 2023 .. change:: :tags: bug, orm declarative :tickets: 9249 Fixed regression caused by the fix for :ticket:`9171`, which itself was fixing a regression, involving the mechanics of ``__init__()`` on classes that extend from :class:`_orm.DeclarativeBase`. The change made it such that ``__init__()`` was applied to the user-defined base if there were no ``__init__()`` method directly on the class. This has been adjusted so that ``__init__()`` is applied only if no other class in the hierarchy of the user-defined base has an ``__init__()`` method. This again allows user-defined base classes based on :class:`_orm.DeclarativeBase` to include mixins that themselves include a custom ``__init__()`` method. .. change:: :tags: bug, mysql, regression :tickets: 9251 Fixed regression caused by issue :ticket:`9058` which adjusted the MySQL dialect's ``has_table()`` to again use "DESCRIBE", where the specific error code raised by MySQL version 8 when using a non-existent schema name was unexpected and failed to be interpreted as a boolean result. .. change:: :tags: bug, sqlite :tickets: 9251 Fixed the SQLite dialect's ``has_table()`` function to correctly report False for queries that include a non-None schema name for a schema that doesn't exist; previously, a database error was raised. .. change:: :tags: bug, orm declarative :tickets: 9226 Fixed issue in ORM Declarative Dataclass mappings related to newly added support for mixins added in 2.0.1 via :ticket:`9179`, where a combination of using mixins plus ORM inheritance would mis-classify fields in some cases leading to field-level dataclass arguments such as ``init=False`` being lost. .. change:: :tags: bug, orm, regression :tickets: 9232 Fixed obscure ORM inheritance issue caused by :ticket:`8705` where some scenarios of inheriting mappers that indicated groups of columns from the local table and the inheriting table together under a :func:`_orm.column_property` would nonetheless warn that properties of the same name were being combined implicitly. .. change:: :tags: orm, bug, regression :tickets: 9228 Fixed regression where using the :paramref:`_orm.Mapper.version_id_col` feature with a regular Python-side incrementing column would fail to work for SQLite and other databases that don't support "rowcount" with "RETURNING", as "RETURNING" would be assumed for such columns even though that's not what actually takes place. .. change:: :tags: bug, orm declarative :tickets: 9240 Repaired ORM Declarative mappings to allow for the :paramref:`_orm.Mapper.primary_key` parameter to be specified within ``__mapper_args__`` when using :func:`_orm.mapped_column`. Despite this usage being directly in the 2.0 documentation, the :class:`_orm.Mapper` was not accepting the :func:`_orm.mapped_column` construct in this context. Ths feature was already working for the :paramref:`_orm.Mapper.version_id_col` and :paramref:`_orm.Mapper.polymorphic_on` parameters. As part of this change, the ``__mapper_args__`` attribute may be specified without using :func:`_orm.declared_attr` on a non-mapped mixin class, including a ``"primary_key"`` entry that refers to :class:`_schema.Column` or :func:`_orm.mapped_column` objects locally present on the mixin; Declarative will also translate these columns into the correct ones for a particular mapped class. This again was working already for the :paramref:`_orm.Mapper.version_id_col` and :paramref:`_orm.Mapper.polymorphic_on` parameters. Additionally, elements within ``"primary_key"`` may be indicated as string names of existing mapped properties. .. change:: :tags: usecase, sql :tickets: 8780 Added a full suite of new SQL bitwise operators, for performing database-side bitwise expressions on appropriate data values such as integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich. .. seealso:: :ref:`operators_bitwise` .. change:: :tags: bug, orm declarative :tickets: 9211 An explicit error is raised if a mapping attempts to mix the use of :class:`_orm.MappedAsDataclass` with :meth:`_orm.registry.mapped_as_dataclass` within the same class hierarchy, as this produces issues with the dataclass function being applied at the wrong time to the mapped class, leading to errors during the mapping process. .. change:: :tags: bug, orm, regression :tickets: 9217 Fixed regression when using :meth:`_sql.Select.from_statement` in an ORM context, where matching of columns to SQL labels based on name alone was disabled for ORM-statements that weren't fully textual. This would prevent arbitrary SQL expressions with column-name labels from matching up to the entity to be loaded, which previously would work within the 1.4 and previous series, so the previous behavior has been restored. .. change:: :tags: bug, asyncio :tickets: 9237 Repaired a regression caused by the fix for :ticket:`8419` which caused asyncpg connections to be reset (i.e. transaction ``rollback()`` called) and returned to the pool normally in the case that the connection were not explicitly returned to the connection pool and was instead being intercepted by Python garbage collection, which would fail if the garbage collection operation were being called outside of the asyncio event loop, leading to a large amount of stack trace activity dumped into logging and standard output. The correct behavior is restored, which is that all asyncio connections that are garbage collected due to not being explicitly returned to the connection pool are detached from the pool and discarded, along with a warning, rather than being returned the pool, as they cannot be reliably reset. In the case of asyncpg connections, the asyncpg-specific ``terminate()`` method will be used to end the connection more gracefully within this process as opposed to just dropping it. This change includes a small behavioral change that is hoped to be useful for debugging asyncio applications, where the warning that's emitted in the case of asyncio connections being unexpectedly garbage collected has been made slightly more aggressive by moving it outside of a ``try/except`` block and into a ``finally:`` block, where it will emit unconditionally regardless of whether the detach/termination operation succeeded or not. It will also have the effect that applications or test suites which promote Python warnings to exceptions will see this as a full exception raise, whereas previously it was not possible for this warning to actually propagate as an exception. Applications and test suites which need to tolerate this warning in the interim should adjust the Python warnings filter to allow these warnings to not raise. The behavior for traditional sync connections remains unchanged, that garbage collected connections continue to be returned to the pool normally without emitting a warning. This will likely be changed in a future major release to at least emit a similar warning as is emitted for asyncio drivers, as it is a usage error for pooled connections to be intercepted by garbage collection without being properly returned to the pool. .. change:: :tags: usecase, orm :tickets: 9220 Added new event hook :meth:`_orm.MapperEvents.after_mapper_constructed`, which supplies an event hook to take place right as the :class:`_orm.Mapper` object has been fully constructed, but before the :meth:`_orm.registry.configure` call has been called. This allows code that can create additional mappings and table structures based on the initial configuration of a :class:`_orm.Mapper`, which also integrates within Declarative configuration. Previously, when using Declarative, where the :class:`_orm.Mapper` object is created within the class creation process, there was no documented means of running code at this point. The change is to immediately benefit custom mapping schemes such as that of the :ref:`examples_versioned_history` example, which generate additional mappers and tables in response to the creation of mapped classes. .. change:: :tags: usecase, orm :tickets: 9220 The infrequently used :attr:`_orm.Mapper.iterate_properties` attribute and :meth:`_orm.Mapper.get_property` method, which are primarily used internally, no longer implicitly invoke the :meth:`_orm.registry.configure` process. Public access to these methods is extremely rare and the only benefit to having :meth:`_orm.registry.configure` would have been allowing "backref" properties be present in these collections. In order to support the new :meth:`_orm.MapperEvents.after_mapper_constructed` event, iteration and access to the internal :class:`_orm.MapperProperty` objects is now possible without triggering an implicit configure of the mapper itself. The more-public facing route to iteration of all mapper attributes, the :attr:`_orm.Mapper.attrs` collection and similar, will still implicitly invoke the :meth:`_orm.registry.configure` step thus making backref attributes available. In all cases, the :meth:`_orm.registry.configure` is always available to be called directly. .. change:: :tags: bug, examples :tickets: 9220 Reworked the :ref:`examples_versioned_history` to work with version 2.0, while at the same time improving the overall working of this example to use newer APIs, including a newly added hook :meth:`_orm.MapperEvents.after_mapper_constructed`. .. change:: :tags: bug, mysql :tickets: 8626 Added support for MySQL 8's new ``AS <name> ON DUPLICATE KEY`` syntax when using :meth:`_mysql.Insert.on_duplicate_key_update`, which is required for newer versions of MySQL 8 as the previous syntax using ``VALUES()`` now emits a deprecation warning with those versions. Server version detection is employed to determine if traditional MariaDB / MySQL < 8 ``VALUES()`` syntax should be used, vs. the newer MySQL 8 required syntax. Pull request courtesy Caspar Wylie. .. changelog:: ``` ### 2.0.1 ``` :released: February 1, 2023 .. change:: :tags: bug, typing :tickets: 9174 Opened up typing on :paramref:`.Select.with_for_update.of` to also accept table and mapped class arguments, as seems to be available for the MySQL dialect. .. change:: :tags: bug, orm, regression :tickets: 9164 Fixed regression where ORM models that used joined table inheritance with a composite foreign key would encounter an internal error in the mapper internals. .. change:: :tags: bug, sql :tickets: 7664 Corrected the fix for :ticket:`7664`, released in version 2.0.0, to also include :class:`.DropSchema` which was inadvertently missed in this fix, allowing stringification without a dialect. The fixes for both constructs is backported to the 1.4 series as of 1.4.47. .. change:: :tags: bug, orm declarative :tickets: 9175 Added support for :pep:`484` ``NewType`` to be used in the :paramref:`_orm.registry.type_annotation_map` as well as within :class:`.Mapped` constructs. These types will behave in the same way as custom subclasses of types right now; they must appear explicitly within the :paramref:`_orm.registry.type_annotation_map` to be mapped. .. change:: :tags: bug, typing :tickets: 9183 Fixed typing for limit/offset methods including :meth:`.Select.limit`, :meth:`.Select.offset`, :meth:`_orm.Query.limit`, :meth:`_orm.Query.offset` to allow ``None``, which is the documented API to "cancel" the current limit/offset. .. change:: :tags: bug, orm declarative :tickets: 9179 When using the :class:`.MappedAsDataclass` superclass, all classes within the hierarchy that are subclasses of this class will now be run through the ``dataclasses.dataclass`` function whether or not they are actually mapped, so that non-ORM fields declared on non-mapped classes within the hierarchy will be used when mapped subclasses are turned into dataclasses. This behavior applies both to intermediary classes mapped with ``__abstract__ = True`` as well as to the user-defined declarative base itself, assuming :class:`.MappedAsDataclass` is present as a superclass for these classes. This allows non-mapped attributes such as ``InitVar`` declarations on superclasses to be used, without the need to run the ``dataclasses.dataclass`` decorator explicitly on each non-mapped class. The new behavior is considered as correct as this is what the :pep:`681` implementation expects when using a superclass to indicate dataclass behavior. .. change:: :tags: bug, typing :tickets: 9170 Fixed typing issue where :func:`_orm.mapped_column` objects typed as :class:`_orm.Mapped` wouldn't be accepted in schema constraints such as :class:`_schema.ForeignKey`, :class:`_schema.UniqueConstraint` or :class:`_schema.Index`. .. change:: :tags: bug, orm declarative :tickets: 9187 Added support for :pep:`586` ``Literal[]`` to be used in the :paramref:`_orm.registry.type_annotation_map` as well as within :class:`.Mapped` constructs. To use custom types such as these, they must appear explicitly within the :paramref:`_orm.registry.type_annotation_map` to be mapped. Pull request courtesy Frederik Aalund. As part of this change, the support for :class:`.sqltypes.Enum` in the :paramref:`_orm.registry.type_annotation_map` has been expanded to include support for ``Literal[]`` types consisting of string values to be used, in addition to ``enum.Enum`` datatypes. If a ``Literal[]`` datatype is used within ``Mapped[]`` that is not linked in :paramref:`_orm.registry.type_annotation_map` to a specific datatype, a :class:`.sqltypes.Enum` will be used by default. .. seealso:: :ref:`orm_declarative_mapped_column_enums` .. change:: :tags: bug, orm declarative :tickets: 9200 Fixed issue involving the use of :class:`.sqltypes.Enum` within the :paramref:`_orm.registry.type_annotation_map` where the :paramref:`_sqltypes.Enum.native_enum` parameter would not be correctly copied to the mapped column datatype, if it were overridden as stated in the documentation to set this parameter to False. .. change:: :tags: bug, orm declarative, regression :tickets: 9171 Fixed regression in :class:`.DeclarativeBase` class where the registry's default constructor would not be applied to the base itself, which is different from how the previous :func:`_orm.declarative_base` construct works. This would prevent a mapped class with its own ``__init__()`` method from calling ``super().__init__()`` in order to access the registry's default constructor and automatically populate attributes, instead hitting ``object.__init__()`` which would raise a ``TypeError`` on any arguments. .. change:: :tags: bug, sql, regression :tickets: 9173 Fixed regression related to the implementation for the new "insertmanyvalues" feature where an internal ``TypeError`` would occur in arrangements where a :func:`_sql.insert` would be referred towards inside of another :func:`_sql.insert` via a CTE; made additional repairs for this use case for positional dialects such as asyncpg when using "insertmanyvalues". .. change:: :tags: bug, typing :tickets: 9156 Fixed typing for :meth:`_expression.ColumnElement.cast` to accept both ``Type[TypeEngine[T]]`` and ``TypeEngine[T]``; previously only ``TypeEngine[T]`` was accepted. Pull request courtesy Yurii Karabas. .. change:: :tags: bug, orm declarative :tickets: 9177 Improved the ruleset used to interpret :pep:`593` ``Annotated`` types when used with Annotated Declarative mapping, the inner type will be checked for "Optional" in all cases which will be added to the criteria by which the column is set as "nullable" or not; if the type within the ``Annotated`` container is optional (or unioned with ``None``), the column will be considered nullable if there are no explicit :paramref:`_orm.mapped_column.nullable` parameters overriding it. .. change:: :tags: bug, orm :tickets: 9182 Improved the error reporting when linking strategy options from a base class to another attribute that's off a subclass, where ``of_type()`` should be used. Previously, when :meth:`.Load.options` is used, the message would lack informative detail that ``of_type()`` should be used, which was not the case when linking the options directly. The informative detail now emits even if :meth:`.Load.options` is used. .. changelog:: ``` ### 2.0.0 ``` :released: January 26, 2023 .. change:: :tags: bug, sql :tickets: 7664 Fixed stringify for a the :class:`.CreateSchema` DDL construct, which would fail with an ``AttributeError`` when stringified without a dialect. Update: Note this fix failed to accommodate for :class:`.DropSchema`; a followup fix in version 2.0.1 repairs this case. The fix for both elements is backported to 1.4.47. .. change:: :tags: usecase, orm extensions :tickets: 5145 Added new feature to :class:`.AutomapBase` for autoload of classes across multiple schemas which may have overlapping names, by providing a :paramref:`.AutomapBase.prepare.modulename_for_table` parameter which allows customization of the ``__module__`` attribute of newly generated classes, as well as a new collection :attr:`.AutomapBase.by_module`, which stores a dot-separated namespace of module names linked to classes based on the ``__module__`` attribute. Additionally, the :meth:`.AutomapBase.prepare` method may now be invoked any number of times, with or without reflection enabled; only newly added tables that were not previously mapped will be processed on each call. Previously, the :meth:`.MetaData.reflect` method would need to be called explicitly each time. .. seealso:: :ref:`automap_by_module` - illustrates use of both techniques at once. .. change:: :tags: orm, bug :tickets: 7305 Improved the notification of warnings that are emitted within the configure mappers or flush process, which are often invoked as part of a different operation, to add additional context to the message that indicates one of these operations as the source of the warning within operations that may not be obviously related. .. change:: :tags: bug, typing :tickets: 9129 Added typing for the built-in generic functions that are available from the :data:`_sql.func` namespace, which accept a particular set of arguments and return a particular type, such as for :class:`_sql.count`, :class:`_sql.current_timestamp`, etc. .. change:: :tags: bug, typing :tickets: 9120 Corrected the type passed for "lambda statements" so that a plain lambda is accepted by mypy, pyright, others without any errors about argument types. Additionally implemented typing for more of the public API for lambda statements and ensured :class:`.StatementLambdaElement` is part of the :class:`.Executable` hierarchy so it's typed as accepted by :meth:`_engine.Connection.execute`. .. change:: :tags: typing, bug :tickets: 9122 The :meth:`_sql.ColumnOperators.in_` and :meth:`_sql.ColumnOperators.not_in` methods are typed to include ``Iterable[Any]`` rather than ``Sequence[Any]`` for more flexibility in argument type. .. change:: :tags: typing, bug :tickets: 9123 The :func:`_sql.or_` and :func:`_sql.and_` from a typing perspective require the first argument to be present, however these functions still accept zero arguments which will emit a deprecation warning at runtime. Typing is also added to support sending the fixed literal ``False`` for :func:`_sql.or_` and ``True`` for :func:`_sql.and_` as the first argument only, however the documentation now indicates sending the :func:`_sql.false` and :func:`_sql.true` constructs in these cases as a more explicit approach. .. change:: :tags: typing, bug :tickets: 9125 Fixed typing issue where iterating over a :class:`_orm.Query` object was not correctly typed. .. change:: :tags: typing, bug :tickets: 9136 Fixed typing issue where the object type when using :class:`_engine.Result` as a context manager were not preserved, indicating :class:`_engine.Result` in all cases rather than the specific :class:`_engine.Result` sub-type. Pull request courtesy Martin Baláž. .. change:: :tags: typing, bug :tickets: 9150 Fixed issue where using the :paramref:`_orm.relationship.remote_side` and similar parameters, passing an annotated declarative object typed as :class:`_orm.Mapped`, would not be accepted by the type checker. .. change:: :tags: typing, bug :tickets: 9148 Added typing to legacy operators such as ``isnot()``, ``notin_()``, etc. which previously were referencing the newer operators but were not themselves typed. .. change:: :tags: feature, orm extensions :tickets: 7226 Added new option to horizontal sharding API :class:`_horizontal.set_shard_id` which sets the effective shard identifier to query against, for both the primary query as well as for all secondary loaders including relationship eager loaders as well as relationship and column lazy loaders. .. change:: :tags: bug, mssql, regression :tickets: 9142 The newly added comment reflection and rendering capability of the MSSQL dialect, added in :ticket:`7844`, will now be disabled by default if it cannot be determined that an unsupported backend such as Azure Synapse may be in use; this backend does not support table and column comments and does not support the SQL Server routines in use to generate them as well as to reflect them. A new parameter ``supports_comments`` is added to the dialect which defaults to ``None``, indicating that comment support should be auto-detected. When set to ``True`` or ``False``, the comment support is either enabled or disabled unconditionally. .. seealso:: :ref:`mssql_comment_support` .. changelog:: ``` ### 2.0.0rc3 ``` :released: January 26, 2023 :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 26, 2023 :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: January 26, 2023 :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: January 26, 2023 :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