Tip ten Brink [Mon, 15 Sep 2025 12:58:40 +0000 (08:58 -0400)]
Fix get_columns sqlite reflection rejecting tables with WITHOUT_ROWID and/or STRICT for generated column case
Fixed issue where SQLite table reflection would fail for tables using
``WITHOUT ROWID`` and/or ``STRICT`` table options when the table contained
generated columns. The regular expression used to parse ``CREATE TABLE``
statements for generated column detection has been updated to properly
handle these SQLite table options that appear after the column definitions.
Pull request courtesy Tip ten Brink.
Mike Bayer [Tue, 9 Sep 2025 19:16:45 +0000 (15:16 -0400)]
Add function mapped_as_dataclass
Added new decorator :func:`_orm.mapped_as_dataclass`, which is a function
based form of :meth:`_orm.registry.mapped_as_dataclass`; the method form
:meth:`_orm.registry.mapped_as_dataclass` does not seem to be correctly
recognized within the scope of :pep:`681` in recent mypy versions.
The new function is tested and mentioned in the docs, in 2.1 in a
subsequent patch (probably the one that adds unmapped_dataclass also)
we'll switch this new decorator to be the prominent one.
also alphabetize mapping_api.rst. while the summary box at the top
auto-sorts, have the sidebar alpha also, it's kind of weird how
these were in no order at all
Mike Bayer [Sun, 31 Aug 2025 22:53:01 +0000 (18:53 -0400)]
add RegistryEvents
Added :class:`_orm.RegistryEvents` event class that allows event listeners
to be established on a :class:`_orm.registry` object. The new class
provides three events: :meth:`_orm.RegistryEvents.resolve_type_annotation`
which allows customization of type annotation resolution that can
supplement or replace the use of the
:paramref:`.registry.type_annotation_map` dictionary, including that it can
be helpful with custom resolution for complex types such as those of
:pep:`695`, as well as :meth:`_orm.RegistryEvents.before_configured` and
:meth:`_orm.RegistryEvents.after_configured`, which are registry-local
forms of the mapper-wide version of these hooks.
Mike Bayer [Sun, 7 Sep 2025 00:20:00 +0000 (20:20 -0400)]
doc updates, localize test fixtures
testing with types is inherently awkward and subject
to changes in python interpreters (such as all the recent python 3.14
stuff we had them fix), but in this suite we already have a lot of
types that are defined inline inside of test methods. so since that's
how many of the tests work anyway, organize the big series of pep-695
and pep-593 structures into fixtures or individual tests to make
the whole suite easier to follow. pyright complains quite a lot
about this, so if this becomes a bigger issue for say mypy /pep484
target, we may have to revisit (which I'd likely do with more ignores)
or if function/method-local type declarations with global becomes a runtime
issue in py3.15 or something, we can revisit then where we would in
theory need to convert the entire suite, which I'd do with a more
consistent naming style for everything.
but for now try to go with fixtures / local type declarations so that
we dont have to wonder where all these types are used.
Mike Bayer [Thu, 4 Sep 2025 01:44:33 +0000 (21:44 -0400)]
liberalize pep695 matching
after many days of discussion we are moving to liberalize the
matching rules used for pep695 to the simple rule that we will resolve
a pep695 type to its immediate ``__value__`` without requiring that
it be present in the type map, however without any further recursive
checks (that is, we will not resolve ``__value__`` of ``__value__``).
This allows the vast majority of simple uses of pep695 types to not
require entries in the type map, including when the type points
to a simple Python type or any type that is present in the type_map.
Also supported is resolution of generic pep695 types against the
right side, including for Annotated types.
The change here in 2.1 will form the base for a revised approach
to the RegistryEvents patch for #9832, which will still provide
the RegistryEvents.resolve_type_annotation hook. In 2.0, we need
to scale back the warnings that are emitted so portions of this patch
will also be backported including similar changes to the test suite.
Mike Bayer [Fri, 5 Sep 2025 13:29:34 +0000 (09:29 -0400)]
interpret NULL in PG enum array values
Fixed issue where selecting an enum array column containing NULL values
would fail to parse properly in the PostgreSQL dialect. The
:func:`._split_enum_values` function now correctly handles NULL entries by
converting them to Python ``None`` values.
Mike Bayer [Tue, 2 Sep 2025 16:26:06 +0000 (12:26 -0400)]
remove upfront sanitization of entities from joins
ORM entities can now be involved within the SQL expressions used within
:paramref:`_orm.relationship.primaryjoin` and
:paramref:`_orm.relationship.secondaryjoin` parameters without the ORM
entity information being implicitly sanitized, allowing ORM-specific
features such as single-inheritance criteria in subqueries to continue
working even when used in this context. This is made possible by overall
ORM simplifications that occurred as of the 2.0 series. The changes here
also provide a performance boost (up to 20%) for certain query compilation
scenarios.
Here we see that we're not only able to remove the
relationships deannotation steps, but we can also change
context -> _get_current_adapter() to be an unconditional
adapter, since the only remaining case where it was conditional
was the polymorphic_adapter. that adapter is itself
only used for exotic joined inh cases against select
statements (totally not used by anyone) or by abstract
concrete setups. That lets us remove a whole host
of orm_annotate stuff that doesn't apply anymore.
if this does lead to user regressions in 2.1 it will be
a good reason for us to revisit the complexity here in
any case.
Mike Bayer [Tue, 2 Sep 2025 15:10:51 +0000 (11:10 -0400)]
support omission of standard event listen example
The required targets for before_configured() and after_configured()
are the Mapper (and soon to include registry things as well in 2.1),
update the automatic doc example thing to be able to be skipped
when there are special instructions for the target.
Also updates the event docs a bit, which were very old and
written in more of that "disorganized wall of details" style
that was very hard to unlearn
Mike Bayer [Fri, 8 Aug 2025 16:17:16 +0000 (12:17 -0400)]
warn on failed aliased
The :func:`_orm.aliased` object now emits warnings when an attribute is
accessed on an aliased class that cannot be located in the target
selectable, for those cases where the :func:`_orm.aliased` is against a
different FROM clause than the regular mapped table (such as a subquery).
This helps users identify cases where column names don't match between the
aliased class and the underlying selectable. When
:paramref:`_orm.aliased.adapt_on_names` is ``True``, the warning suggests
checking the column name; when ``False``, it suggests using the
``adapt_on_names`` parameter for name-based matching.
Mike Bayer [Tue, 26 Aug 2025 20:54:39 +0000 (16:54 -0400)]
add session-wide execution_options
Added support for per-session execution options that are merged into all
queries executed within that session. The :class:`_orm.Session`,
:class:`_orm.sessionmaker`, :class:`_orm.scoped_session`,
:class:`_ext.asyncio.AsyncSession`, and
:class:`_ext.asyncio.async_sessionmaker` constructors now accept an
:paramref:`_orm.Session.execution_options` parameter that will be applied
to all explicit query executions (e.g. using :meth:`_orm.Session.execute`,
:meth:`_orm.Session.get`, :meth:`_orm.Session.scalars`) for that session
instance.
Mike Bayer [Tue, 26 Aug 2025 23:14:20 +0000 (19:14 -0400)]
use fixture_session() fixture, remove "future" terminology
Tests here are sporadically failing on aiosqlite and it
seems the use of Session() rather than fixture_session() may be
causing connections to be cleaned up in GC, leading to table
exists race conditions.
Remove the concept of "future" since everything is "future" now
Mike Bayer [Tue, 26 Aug 2025 18:47:34 +0000 (14:47 -0400)]
use _generate_columns_plus_names for ddl returning c populate
Improved the implementation of :meth:`.UpdateBase.returning` to use more
robust logic in setting up the ``.c`` collection of a derived statement
such as a CTE. This fixes issues related to RETURNING clauses that feature
expressions based on returned columns with or without qualifying labels.
Co-authored-by: Juhyeong Ko <dury.ko@gmail.com> Fixes: #12271
Change-Id: Id0d486d4304002f1affdec2e7662ac2965936f2a
Mike Bayer [Mon, 5 May 2025 12:36:05 +0000 (08:36 -0400)]
support configurable None behavior for composites
Added new parameter :paramref:`_orm.composite.return_none_on` to
:func:`_orm.composite`, which allows control over if and when this
composite attribute should resolve to ``None`` when queried or retrieved
from the object directly. By default, a composite object is always present
on the attribute, including for a pending object which is a behavioral
change since 2.0. When :paramref:`_orm.composite.return_none_on` is
specified, a callable is passed that returns True or False to indicate if
the given arguments indicate the composite should be returned as None. This
parameter may also be set automatically when ORM Annotated Declarative is
used; if the annotation is given as ``Mapped[SomeClass|None]``, a
:paramref:`_orm.composite.return_none_on` rule is applied that will return
``None`` if all contained columns are themselves ``None``.
Mike Bayer [Wed, 16 Jul 2025 16:14:27 +0000 (12:14 -0400)]
add OperatorClasses to gate mismatched operator use
Added a new concept of "operator classes" to the SQL operators supported by
SQLAlchemy, represented within the enum :class:`.OperatorClass`. The
purpose of this structure is to provide an extra layer of validation when a
particular kind of SQL operation is used with a particular datatype, to
catch early the use of an operator that does not have any relevance to the
datatype in use; a simple example is an integer or numeric column used with
a "string match" operator.
Mike Bayer [Fri, 22 Aug 2025 22:12:13 +0000 (18:12 -0400)]
ensure datatype roundtrips for JSON dialects
Improved the behavior of JSON accessors :meth:`.JSON.Comparator.as_string`,
:meth:`.JSON.Comparator.as_boolean`, :meth:`.JSON.Comparator.as_float`,
:meth:`.JSON.Comparator.as_integer` to use CAST in a similar way that
the PostgreSQL, MySQL and SQL Server dialects do to help enforce the
expected Python type is returned.
The :meth:`.JSON.Comparator.as_boolean` method when used on a JSON value on
SQL Server will now force a cast to occur for values that are not simple
`true`/`false` JSON literals, forcing SQL Server to attempt to interpret
the given value as a 1/0 BIT, or raise an error if not possible. Previously
the expression would return NULL.
Mike Bayer [Tue, 29 Jul 2025 18:19:34 +0000 (14:19 -0400)]
more extensibility for asc/desc
Improved the behavior of standalone "operators" like :func:`_sql.desc`,
:func:`_sql.asc`, :func:`_sql.all_`, etc. so that they consult the given
expression object for an overriding method for that operator, even if the
object is not itself a ``ClauseElement``, such as if it's an ORM attribute.
This allows custom comparators for things like :func:`_orm.composite` to
provide custom implementations of methods like ``desc()``, ``asc()``, etc.
Added default implementations of :meth:`.ColumnOperators.desc`,
:meth:`.ColumnOperators.asc`, :meth:`.ColumnOperators.nulls_first`,
:meth:`.ColumnOperators.nulls_last` to :func:`_orm.composite` attributes,
by default applying the modifier to all contained columns. Can be
overridden using a custom comparator.
Mike Bayer [Thu, 21 Aug 2025 15:20:11 +0000 (11:20 -0400)]
ensure assoc proxy from aliased() is generated in correct context
Improved association proxy to behave slightly better when the parent class
is used in an :func:`_orm.aliased` construct, so that the proxy as
delivered by the :class:`.Aliased` behaves appropriate in terms of that
aliased construct, including operators like ``.any()`` and ``.has()`` work
correctly.
Micah Denbraver [Wed, 20 Aug 2025 20:58:02 +0000 (16:58 -0400)]
Fix typing for `hybrid_property.__set__` to properly validate setter values
While iterating on some typing improvements, my colleague @seamuswn pointed out mypy wasn't catching when values with invalid types were set using a `hybrid_property` setter. I believe this is all that's needed to fix the typing.
### Description
Adjust `hybrid_property.__set__` to expect a value of the type that matches the generic's type variable.
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)
-->
This pull request is:
- [x] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
- [ ] A short code fix
- please include the issue number, and create an issue if none exists, which
must include a complete example of the issue. one line code fixes without an
issue and demonstration will not be accepted.
- Please include: `Fixes: #<issue number>` in the commit message
- please include tests. one line code fixes without tests will not be accepted.
- [ ] A new feature implementation
- please include the issue number, and create an issue if none exists, which must
include a complete example of how the feature would look.
- Please include: `Fixes: #<issue number>` in the commit message
- please include tests.
Federico Caselli [Thu, 14 Aug 2025 22:05:07 +0000 (00:05 +0200)]
restore functionality in list
Fixed issue caused by an unwanted functional change while typing
the :class:`.MutableList` class.
This change also reverts all other functional changes done in
the same change, commit ba0e508141206efc55cdab91df21c18e7dd63c80
Mike Bayer [Mon, 18 Aug 2025 15:01:47 +0000 (11:01 -0400)]
We can't promise CursorResult from session.execute()
Fixed typing bug where the :meth:`.Session.execute` method advertised that
it would return a :class:`.CursorResult` if given an insert/update/delete
statement. This is not the general case as several flavors of ORM
insert/update do not actually yield a :class:`.CursorResult` which cannot
be differentiated at the typing overload level, so the method now yields
:class:`.Result` in all cases. For those cases where
:class:`.CursorResult` is known to be returned and the ``.rowcount``
attribute is required, please use ``typing.cast()``.
Mike Bayer [Tue, 12 Aug 2025 19:25:15 +0000 (15:25 -0400)]
close aio cursors etc. that require await close
Improved the base implementation of the asyncio cursor such that it
includes the option for the underlying driver's cursor to be actively
closed in those cases where it requires ``await`` in order to complete the
close sequence, rather than relying on garbage collection to "close" it,
when a plain :class:`.Result` is returned that does not use ``await`` for
any of its methods. The previous approach of relying on gc was fine for
MySQL and SQLite dialects but has caused problems with the aioodbc
implementation on top of SQL Server. The new option is enabled
for those dialects which have an "awaitable" ``cursor.close()``, which
includes the aioodbc, aiomysql, and asyncmy dialects (aiosqlite is also
modified for 2.1 only).
suraj [Mon, 11 Aug 2025 12:21:46 +0000 (08:21 -0400)]
Fixes: #12711 Added sparse vector support in Oracle
Extended :class:`_oracle.VECTOR` to support sparse vectors. This update
introduces :class:_oracle.VectorStorageType to specify sparse or dense
storage and added :class:`_oracle.SparseVector`. Pull request courtesy
Suraj Shaw.
Mike Bayer [Tue, 5 Aug 2025 14:46:21 +0000 (10:46 -0400)]
implement skip_autocommit_rollback
Added new parameter :paramref:`.create_engine.skip_autocommit_rollback`
which provides for a per-dialect feature of preventing the DBAPI
``.rollback()`` from being called under any circumstances, if the
connection is detected as being in "autocommit" mode. This improves upon
a critical performance issue identified in MySQL dialects where the network
overhead of the ``.rollback()`` call remains prohibitive even if autocommit
mode is set.
Mike Bayer [Wed, 6 Aug 2025 14:19:39 +0000 (10:19 -0400)]
add chunking to selectin_polymorphic
Improved the implementation of the :func:`_orm.selectin_polymorphic`
inheritance loader strategy to properly render the IN expressions using
chunks of 500 records each, in the same manner as that of the
:func:`_orm.selectinload` relationship loader strategy. Previously, the IN
expression would be arbitrarily large, leading to failures on databases
that have limits on the size of IN expressions including Oracle Database.
Mike Bayer [Tue, 5 Aug 2025 21:11:50 +0000 (17:11 -0400)]
apply correct pre-fetch params to post updated rows
Fixed issue where using the ``post_update`` feature would apply incorrect
"pre-fetched" values to the ORM objects after a multi-row UPDATE process
completed. These "pre-fetched" values would come from any column that had
an :paramref:`.Column.onupdate` callable or a version id generator used by
:paramref:`.orm.Mapper.version_id_generator`; for a version id generator
that delivered random identifiers like timestamps or UUIDs, this incorrect
data would lead to a DELETE statement against those same rows to fail in
the next step.
Mike Bayer [Tue, 5 Aug 2025 18:05:49 +0000 (14:05 -0400)]
Fix use_existing_column with Annotated mapped_column in polymorphic inheritance
Fixed issue where :paramref:`_orm.mapped_column.use_existing_column`
parameter in :func:`_orm.mapped_column` would not work when the
:func:`_orm.mapped_column` is used inside of an ``Annotated`` type alias in
polymorphic inheritance scenarios. The parameter is now properly recognized
and processed during declarative mapping configuration.
Mike Bayer [Fri, 1 Aug 2025 16:48:15 +0000 (12:48 -0400)]
Fix PostgreSQL JSONB subscripting regression with functions
Fixed regression in PostgreSQL dialect where JSONB subscription syntax
would generate incorrect SQL for JSONB-returning functions, causing syntax
errors. The dialect now properly wraps function calls and expressions in
parentheses when using the ``[]`` subscription syntax, generating
``(function_call)[index]`` instead of ``function_call[index]`` to comply
with PostgreSQL syntax requirements.
Added ``dataclass_metadata`` argument to a all column functions
used in the ORM that accept dataclasses parameters.
It's passed to the underlying dataclass ``metadata`` attribute
of the dataclass field.
Pull request courtesy Sigmund Lahn.
Mike Bayer [Mon, 14 Jul 2025 20:13:03 +0000 (16:13 -0400)]
extend Values from HasCTE
The :func:`_sql.values` construct gains a new method :meth:`_sql.Values.cte`,
which allows creation of a named, explicit-columns :class:`.CTE` against an
unnamed ``VALUES`` expression, producing a syntax that allows column-oriented
selection from a ``VALUES`` construct on modern versions of PostgreSQL, SQLite,
and MariaDB.
Federico Caselli [Fri, 29 Dec 2023 21:30:24 +0000 (22:30 +0100)]
Improve handling of sentinel columns
Fixed issue in "insertmanyvalues" feature where an INSERT..RETURNING
that also made use of a sentinel column to track results would fail to
filter out the additional column when :meth:`.Result.unique` were used
to uniquify the result set.
Mike Bayer [Thu, 10 Jul 2025 19:29:29 +0000 (15:29 -0400)]
Unconditionally flush session on all statement executions
This change simplifies session execution logic to always run autoflush
for all statement executions, regardless of whether they are ORM or Core
statements. Previously, autoflush was conditionally applied only when
ORM-related statements were detected, which had become difficult to define
clearly with the unified v2 syntax that blurs the lines between Core and
ORM execution patterns.
The implementation adds a call to self._autoflush() in the Core execution
path (when compile_state_cls is None) in Session._execute_internal(),
ensuring consistent autoflush behavior across all types of SQL execution.
This provides more predictable session behavior and eliminates the
confusion around which statements trigger autoflush.
Also included is some improvement to the migration_21 document
Mike Bayer [Fri, 11 Jul 2025 16:51:44 +0000 (12:51 -0400)]
support JSONB subscripting syntax
Added support for PostgreSQL 14+ JSONB subscripting syntax. When connected
to PostgreSQL 14 or later, JSONB columns now automatically use the native
subscript notation ``jsonb_col['key']`` instead of the arrow operator
``jsonb_col -> 'key'`` for both read and write operations. This provides
better compatibility with PostgreSQL's native JSONB subscripting feature
while maintaining backward compatibility with older PostgreSQL versions.
JSON columns continue to use the traditional arrow syntax regardless of
PostgreSQL version.
Mike Bayer [Thu, 3 Apr 2025 14:36:28 +0000 (10:36 -0400)]
generalize composite bulk insert to hybrids
Added new hybrid method :meth:`.hybrid_property.bulk_insert_setter` which
works in a similar way as :meth:`.hybrid_property.update_expression` for
bulk ORM operations. A user-defined class method can now populate a bulk
insert mapping dictionary using the desired hybrid mechanics. New
documentation is added showing how both of these methods can be used
including in combination with the new :func:`_sql.from_dml_column`
construct.
Fix the code examples and the grammatic in `declarative_tables.rst` (#12721)
* Fix the code example in `declarative_tables.rst`
Add an import of a `Optional` class from the `typing` library in the
code example of the "Union types inside the Type Map" chapter.
* Fix the code example in `declarative_tables.rst`
Replace the import of the `deferred` function to `column_property` from
the `sqlalchemy.orm` package in first code example of the "Applying
Load, Persistence and Mapping Options for Imperative Table Columns"
chapter.
* Fix the grammatic in `declarative_tables.rst`
Remove the unnecessary article in the second paragraph of the
"Mapping to an Explicit Set of Primary Key Columns" chapter in
`declarative_tables.rst`.
Mike Bayer [Thu, 3 Apr 2025 14:36:28 +0000 (10:36 -0400)]
add CRUD column marker
Added new Core feature :func:`_sql.from_dml_column` that may be used in
expressions inside of :meth:`.UpdateBase.values` for INSERT or UPDATE; this
construct will copy whatever SQL expression is used for the given target
column in the statement to be used with additional columns. The construct
is mostly intended to be a helper with ORM :class:`.hybrid_property` within
DML hooks.
This is the Core side of the feature being added to the ORM
for #12496
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
