Sqlalchemy

:released: April 30, 2023

.. change::
:tags: bug, mysql, mariadb
:tickets: 9722

Fixed issues regarding reflection of comments for :class:`_schema.Table`
and :class:`_schema.Column` objects, where the comments contained control
characters such as newlines. Additional testing support for these
characters as well as extended Unicode characters in table and column
comments (the latter of which aren't supported by MySQL/MariaDB) added to
testing overall.

.. changelog::

:released: April 26, 2023

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

Fixed regression which prevented the :attr:`_engine.URL.normalized_query`
attribute of :class:`_engine.URL` from functioning.

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

Fixed critical regression caused by :ticket:`9618`, which modified the
architecture of the :term:`insertmanyvalues` feature for 2.0.10, which
caused floating point values to lose all decimal places when being inserted
using the insertmanyvalues feature with either the psycopg2 or psycopg
drivers.


.. change::
:tags: bug, mssql

Implemented the :class:`_sqltypes.Double` type for SQL Server, where it
will render ``DOUBLE PRECISION`` at DDL time. This is implemented using
a new MSSQL datatype :class:`_mssql.DOUBLE_PRECISION` which also may
be used directly.


.. change::
:tags: bug, oracle

Fixed issue in Oracle dialects where ``Decimal`` returning types such as
:class:`_sqltypes.Numeric` would return floating point values, rather than
``Decimal`` objects, when these columns were used in the
:meth:`_dml.Insert.returning` clause to return INSERTed values.

.. change::
:tags: bug, orm
:tickets: 9583, 9595

Fixed 2.0 regression where use of :func:`_sql.bindparam()` inside of
:meth:`_dml.Insert.values` would fail to be interpreted correctly when
executing the :class:`_dml.Insert` statement using the ORM
:class:`_orm.Session`, due to the new
:ref:`ORM-enabled insert feature <orm_queryguide_bulk_insert>` not
implementing this use case.

.. change::
:tags: usecase, orm
:tickets: 9583, 9595

The :ref:`ORM bulk INSERT and UPDATE <orm_expression_update_delete>`
features now add these capabilities:

* The requirement that extra parameters aren't passed when using ORM
INSERT using the "orm" dml_strategy setting is lifted.
* The requirement that additional WHERE criteria is not passed when using
ORM UPDATE using the "bulk" dml_strategy setting is lifted. Note that
in this case, the check for expected row count is turned off.

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

Added support for slice access with :class:`.ColumnCollection`, e.g.
``table.c[0:5]``, ``subquery.c[:-1]`` etc. Slice access returns a sub
:class:`.ColumnCollection` in the same way as passing a tuple of keys. This
is a natural continuation of the key-tuple access added for :ticket:`8285`,
where it appears to be an oversight that the slice access use case was
omitted.

.. change::
:tags: bug, typing
:tickets: 9644

Improved typing of :class:`_engine.RowMapping` to indicate that it
support also :class:`_schema.Column` as index objects, not only
string names. Pull request courtesy Andy Freeland.

.. change::
:tags: engine, performance
:tickets: 9678, 9680

A series of performance enhancements to :class:`_engine.Row`:

* ``__getattr__`` performance of the row's "named tuple" interface has
been improved; within this change, the :class:`_engine.Row`
implementation has been streamlined, removing constructs and logic
that were specific to the 1.4 and prior series of SQLAlchemy.
As part of this change, the serialization format of :class:`_engine.Row`
has been modified slightly, however rows which were pickled with previous
SQLAlchemy 2.0 releases will be recognized within the new format.
Pull request courtesy J. Nick Koston.

* Improved row processing performance for "binary" datatypes by making the
"bytes" handler conditional on a per driver basis. As a result, the
"bytes" result handler has been removed for nearly all drivers other than
psycopg2, all of which in modern forms support returning Python "bytes"
directly. Pull request courtesy J. Nick Koston.

* Additional refactorings inside of :class:`_engine.Row` to improve
performance by Federico Caselli.




.. changelog::

:released: April 21, 2023

.. change::
:tags: bug, typing
:tickets: 9650

Added typing information for recently added operators
:meth:`.ColumnOperators.icontains`, :meth:`.ColumnOperators.istartswith`,
:meth:`.ColumnOperators.iendswith`, and bitwise operators
:meth:`.ColumnOperators.bitwise_and`, :meth:`.ColumnOperators.bitwise_or`,
:meth:`.ColumnOperators.bitwise_xor`, :meth:`.ColumnOperators.bitwise_not`,
:meth:`.ColumnOperators.bitwise_lshift`
:meth:`.ColumnOperators.bitwise_rshift`. Pull request courtesy Martijn
Pieters.


.. change::
:tags: bug, oracle

Fixed issue where the :class:`_sqltypes.Uuid` datatype could not be used in
an INSERT..RETURNING clause with the Oracle dialect.

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

Added :func:`_sa.create_pool_from_url` and
:func:`_asyncio.create_async_pool_from_url` to create
a :class:`_pool.Pool` instance from an input url passed as string
or :class:`_sa.URL`.

.. change::
:tags: bug, engine
:tickets: 9618, 9603

Repaired a major shortcoming which was identified in the
:ref:`engine_insertmanyvalues` performance optimization feature first
introduced in the 2.0 series. This was a continuation of the change in
2.0.9 which disabled the SQL Server version of the feature due to a
reliance in the ORM on apparent row ordering that is not guaranteed to take
place. The fix applies new logic to all "insertmanyvalues" operations,
which takes effect when a new parameter
:paramref:`_dml.Insert.returning.sort_by_parameter_order` on the
:meth:`_dml.Insert.returning` or :meth:`_dml.UpdateBase.return_defaults`
methods, that through a combination of alternate SQL forms, direct
correspondence of client side parameters, and in some cases downgrading to
running row-at-a-time, will apply sorting to each batch of returned rows
using correspondence to primary key or other unique values in each row
which can be correlated to the input data.

Performance impact is expected to be minimal as nearly all common primary
key scenarios are suitable for parameter-ordered batching to be
achieved for all backends other than SQLite, while "row-at-a-time"
mode operates with a bare minimum of Python overhead compared to the very
heavyweight approaches used in the 1.x series. For SQLite, there is no
difference in performance when "row-at-a-time" mode is used.

It's anticipated that with an efficient "row-at-a-time" INSERT with
RETURNING batching capability, the "insertmanyvalues" feature can be later
be more easily generalized to third party backends that include RETURNING
support but not necessarily easy ways to guarantee a correspondence
with parameter order.

.. seealso::

:ref:`engine_insertmanyvalues_returning_order`


.. change::
:tags: bug, mssql
:tickets: 9618, 9603

Restored the :term:`insertmanyvalues` feature for Microsoft SQL Server.
This feature was disabled in version 2.0.9 due to an apparent reliance
on the ordering of RETURNING that is not guaranteed. The architecture of
the "insertmanyvalues" feature has been reworked to accommodate for
specific organizations of INSERT statements and result row handling that
can guarantee the correspondence of returned rows to input records.

.. seealso::

:ref:`engine_insertmanyvalues_returning_order`


.. change::
:tags: usecase, postgresql
:tickets: 9608

Added ``prepared_statement_name_func`` connection argument option in the
asyncpg dialect. This option allows passing a callable used to customize
the name of the prepared statement that will be created by the driver
when executing queries. Pull request courtesy Pavel Sirotkin.

.. seealso::

:ref:`asyncpg_prepared_statement_name`

.. change::
:tags: typing, bug

Updates to the codebase to pass typing with Mypy 1.2.0.

.. change::
:tags: bug, typing
:tickets: 9669

Fixed typing issue where :meth:`_orm.PropComparator.and_` expressions would
not be correctly typed inside of loader options such as
:func:`_orm.selectinload`.

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

Fixed issue where the :meth:`_orm.declared_attr.directive` modifier was not
correctly honored for subclasses when applied to the ``__mapper_args__``
special method name, as opposed to direct use of
:class:`_orm.declared_attr`. The two constructs should have identical
runtime behaviors.

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

Restored the :paramref:`_postgresql.ENUM.name` parameter as optional in the
signature for :class:`_postgresql.ENUM`, as this is chosen automatically
from a given pep-435 ``Enum`` type.


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

Fixed issue where the comparison for :class:`_postgresql.ENUM` against a
plain string would cast that right-hand side type as VARCHAR, which due to
more explicit casting added to dialects such as asyncpg would produce a
PostgreSQL type mismatch error.


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

Made an improvement to the :func:`_orm.with_loader_criteria` loader option
to allow it to be indicated in the :meth:`.Executable.options` method of a
top-level statement that is not itself an ORM statement. Examples include
:func:`_sql.select` that's embedded in compound statements such as
:func:`_sql.union`, within an :meth:`_dml.Insert.from_select` construct, as
well as within CTE expressions that are not ORM related at the top level.

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

Fixed bug in ORM bulk insert feature where additional unnecessary columns
would be rendered in the INSERT statement if RETURNING of individual columns
were requested.

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

Fixed issue that prevented reflection of expression based indexes
with long expressions in PostgreSQL. The expression where erroneously
truncated to the identifier length (that's 63 bytes by default).

.. change::
:tags: usecase, postgresql
:tickets: 9509

Add missing :meth:`_postgresql.Range.intersection` method.
Pull request courtesy Yurii Karabas.

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

Fixed bug in ORM Declarative Dataclasses where the
:func:`_orm.query_expression` and :func:`_orm.column_property`
constructs, which are documented as read-only constructs in the context of
a Declarative mapping, could not be used with a
:class:`_orm.MappedAsDataclass` class without adding ``init=False``, which
in the case of :func:`_orm.query_expression` was not possible as no
``init`` parameter was included. These constructs have been modified from a
dataclass perspective to be assumed to be "read only", setting
``init=False`` by default and no longer including them in the pep-681
constructor. The dataclass parameters for :func:`_orm.column_property`
``init``, ``default``, ``default_factory``, ``kw_only`` are now deprecated;
these fields don't apply to :func:`_orm.column_property` as used in a
Declarative dataclasses configuration where the construct would be
read-only. Also added read-specific parameter
:paramref:`_orm.query_expression.compare` to
:func:`_orm.query_expression`; :paramref:`_orm.query_expression.repr`
was already present.



.. change::
:tags: bug, orm

Added missing :paramref:`_orm.mapped_column.active_history` parameter
to :func:`_orm.mapped_column` construct.

.. changelog::

:released: April 5, 2023

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

The SQLAlchemy "insertmanyvalues" feature which allows fast INSERT of
many rows while also supporting RETURNING is temporarily disabled for
SQL Server. As the unit of work currently relies upon this feature such
that it matches existing ORM objects to returned primary key
identities, this particular use pattern does not work with SQL Server
in all cases as the order of rows returned by "OUTPUT inserted" may not
always match the order in which the tuples were sent, leading to
the ORM making the wrong decisions about these objects in subsequent
operations.

The feature will be re-enabled in an upcoming release and will again
take effect for multi-row INSERT statements, however the unit-of-work's
use of the feature will be disabled, possibly for all dialects, unless
ORM-mapped tables also include a "sentinel" column so that the
returned rows can be referenced back to the original data passed in.


.. change::
:tags: bug, mariadb
:tickets: 9588

Added ``row_number`` as reserved word in MariaDb.

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

Changed the bulk INSERT strategy used for SQL Server "executemany" with
pyodbc when ``fast_executemany`` is set to ``True`` by using
``fast_executemany`` / ``cursor.executemany()`` for bulk INSERT that does
not include RETURNING, restoring the same behavior as was used in
SQLAlchemy 1.4 when this parameter is set.

New performance details from end users have shown that ``fast_executemany``
is still much faster for very large datasets as it uses ODBC commands that
can receive all rows in a single round trip, allowing for much larger
datasizes than the batches that can be sent by "insertmanyvalues"
as was implemented for SQL Server.

While this change was made such that "insertmanyvalues" continued to be
used for INSERT that includes RETURNING, as well as if ``fast_executemany``
were not set, due to :ticket:`9603`, the "insertmanyvalues" strategy has
been disabled for SQL Server across the board in any case.

.. changelog::

:released: March 31, 2023

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

Fixed issue in ORM Annotated Declarative where using a recursive type (e.g.
using a nested Dict type) would result in a recursion overflow in the ORM's
annotation resolution logic, even if this datatype were not necessary to
map the column.

.. change::
:tags: bug, examples

Fixed issue in "versioned history" example where using a declarative base
that is derived from :class:`_orm.DeclarativeBase` would fail to be mapped.
Additionally, repaired the given test suite so that the documented
instructions for running the example using Python unittest now work again.

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

Fixed issue where the :func:`_orm.mapped_column` construct would raise an
internal error if used on a Declarative mixin and included the
:paramref:`_orm.mapped_column.deferred` parameter.

.. change::
:tags: bug, mysql
:tickets: 9544

Fixed issue where string datatypes such as :class:`_sqltypes.CHAR`,
:class:`_sqltypes.VARCHAR`, :class:`_sqltypes.TEXT`, as well as binary
:class:`_sqltypes.BLOB`, could not be produced with an explicit length of
zero, which has special meaning for MySQL. Pull request courtesy J. Nick
Koston.

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

Expanded the warning emitted when a plain :func:`_sql.column` object is
present in a Declarative mapping to include any arbitrary SQL expression
that is not declared within an appropriate property type such as
:func:`_orm.column_property`, :func:`_orm.deferred`, etc. These attributes
are otherwise not mapped at all and remain unchanged within the class
dictionary. As it seems likely that such an expression is usually not
what's intended, this case now warns for all such otherwise ignored
expressions, rather than just the :func:`_sql.column` case.

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

Fixed regression where accessing the expression value of a hybrid property
on a class that was either unmapped or not-yet-mapped (such as calling upon
it within a :func:`_orm.declared_attr` method) would raise an internal
error, as an internal fetch for the parent class' mapper would fail and an
instruction for this failure to be ignored were inadvertently removed in
2.0.

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

Fields that are declared on Declarative Mixins and then combined with
classes that make use of :class:`_orm.MappedAsDataclass`, where those mixin
fields are not themselves part of a dataclass, now emit a deprecation
warning as these fields will be ignored in a future release, as Python
dataclasses behavior is to ignore these fields. Type checkers will not see
these fields under pep-681.

.. seealso::

:ref:`error_dcmx` - background on rationale

:ref:`orm_declarative_dc_mixins`

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

Fixed critical regression in PostgreSQL dialects such as asyncpg which rely
upon explicit casts in SQL in order for datatypes to be passed to the
driver correctly, where a :class:`.String` datatype would be cast along
with the exact column length being compared, leading to implicit truncation
when comparing a ``VARCHAR`` of a smaller length to a string of greater
length regardless of operator in use (e.g. LIKE, MATCH, etc.). The
PostgreSQL dialect now omits the length from ``VARCHAR`` when rendering
these casts.

.. change::
:tags: bug, util
:tickets: 9487

Implemented missing methods ``copy`` and ``pop`` in
OrderedSet class.

.. change::
:tags: bug, typing
:tickets: 9536

Fixed typing for :func:`_orm.deferred` and :func:`_orm.query_expression`
to work correctly with 2.0 style mappings.

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

Fixed issue where the :meth:`_sql.BindParameter.render_literal_execute`
method would fail when called on a parameter that also had ORM annotations
associated with it. In practice, this would be observed as a failure of SQL
compilation when using some combinations of a dialect that uses "FETCH
FIRST" such as Oracle along with a :class:`_sql.Select` construct that uses
:meth:`_sql.Select.limit`, within some ORM contexts, including if the
statement were embedded within a relationship primaryjoin expression.


.. change::
:tags: usecase, orm
:tickets: 9563

Exceptions such as ``TypeError`` and ``ValueError`` raised by Python
dataclasses when making use of the :class:`_orm.MappedAsDataclass` mixin
class or :meth:`_orm.registry.mapped_as_dataclass` decorator are now
wrapped within an :class:`.InvalidRequestError` wrapper along with
informative context about the error message, referring to the Python
dataclasses documentation as the authoritative source of background
information on the cause of the exception.

.. seealso::

:ref:`error_dcte`


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

Towards maintaining consistency with unit-of-work changes made for
:ticket:`5984` and :ticket:`8862`, both of which disable "lazy='raise'"
handling within :class:`_orm.Session` processes that aren't triggered by
attribute access, the :meth:`_orm.Session.delete` method will now also
disable "lazy='raise'" handling when it traverses relationship paths in
order to process the "delete" and "delete-orphan" cascade rules.
Previously, there was no easy way to generically call
:meth:`_orm.Session.delete` on an object that had "lazy='raise'" set up
such that only the necessary relationships would be loaded. As
"lazy='raise'" is primarily intended to catch SQL loading that emits on
attribute access, :meth:`_orm.Session.delete` is now made to behave like
other :class:`_orm.Session` methods including :meth:`_orm.Session.merge` as
well as :meth:`_orm.Session.flush` along with autoflush.

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

Fixed issue where an annotation-only :class:`_orm.Mapped` directive could
not be used in a Declarative mixin class, without that attribute attempting
to take effect for single- or joined-inheritance subclasses of mapped
classes that had already mapped that attribute on a superclass, producing
conflicting column errors and/or warnings.


.. change::
:tags: bug, orm, typing
:tickets: 9514

Properly type :paramref:`_dml.Insert.from_select.names` to accept
a list of string or columns or mapped attributes.

.. changelog::

:released: March 18, 2023

.. change::
:tags: usecase, postgresql
:tickets: 9416

Added new PostgreSQL type :class:`_postgresql.CITEXT`. Pull request
courtesy Julian David Rath.

.. change::
:tags: bug, typing
:tickets: 9502

Fixed typing issue where :func:`_orm.composite` would not allow an
arbitrary callable as the source of the composite class.

.. change::
:tags: usecase, postgresql
:tickets: 9442

Modifications to the base PostgreSQL dialect to allow for better integration with the
sqlalchemy-redshift third party dialect for SQLAlchemy 2.0. Pull request courtesy
matthewgdv.

.. changelog::