escape single quotes in postgresql nextval() identifier rendering
Fixed bug in the PostgreSQL dialect where a single quote in a sequence,
table, or schema name, such as one supplied via a ``schema_translate_map``
or an explicit :class:`.Sequence`, could result in a malformed
``nextval()`` statement. The quote is now properly escaped. Pull request
courtesy dxbjavid.
Reason for revert: this first looked like a small place we needed a workaround, but nothing is ever like that. unfortunately changing it in this way implies that we aren't using `collation_schema` in other places as well, using the awkward quoted_name workaround, which then leads into more awkwardness as we want collation schemas to be reflected, too. would we be creating hand-quoted quoted_names for reflection also? obviously not. I assume I didnt consider these aspects when i tried to do a "less intrusive" change.
I would like to do just one gerrit that adds collation_schema in all places we need it, plus full reflection, at once, rather than the drip-drip that was not making the scope of the problem clear.
Mike Bayer [Mon, 20 Jul 2026 16:52:20 +0000 (12:52 -0400)]
Enable mypy ignore-without-code rule
Enable the mypy ``ignore-without-code`` error code in ``pyproject.toml``
so that bare ``# type: ignore`` comments are rejected, and add the
specific error code reported by mypy to each of the 330 remaining bare
ignore comments across 73 modules in ``lib/sqlalchemy``.
Codes were derived by running mypy against current main and applying the
code it reported for each location (e.g. ``[assignment]``,
``[no-any-return]``, ``[union-attr]``), rather than carrying over codes
from an earlier branch, since the underlying types and the set of ignores
already present on main have diverged substantially. 71 lines that
became longer than 79 characters after adding the code were annotated with
``# noqa: E501``, matching the existing convention used elsewhere in the
codebase.
Jan Vollmer [Wed, 15 Jul 2026 12:56:19 +0000 (08:56 -0400)]
Improve ORM loader strategy error messages to use user-friendly representations
Improved error messages raised when ORM loader strategy options cannot be
applied to a query. Messages now render the offending option in a
user-friendly form such as ``joinedload(User.orders)`` rather than exposing
internal class and path representations, and the "does not apply to root
entities" message now includes the option that triggered the error. The
same user-friendly rendering is also applied to the "conflicting loader
strategy" message and to the ``of_type()`` representation in "does not
link" messages. Originating pull request courtesy Jan Vollmer.
Mike Bayer [Sat, 18 Jul 2026 15:59:14 +0000 (11:59 -0400)]
Fix ORM UPDATE..RETURNING cache adaptation with synchronize_session=fetch
Fixed result-metadata corruption affecting ORM-enabled UPDATE statements
that use .returning() together with synchronize_session="fetch". In this
configuration the RETURNING clause is rendered in mapper/table column order
rather than the user-requested .returning() order; on a compiled-cache hit,
the cursor result metadata was positionally adapted against the cached
statement's user-requested column order, causing column values to be
returned under the wrong keys (e.g. row[T.a] returning T.b's value).
The mis-adaptation was frequently masked by cached row-getter functions
produced by a prior, correctly-adapted execution, so the wrong values
surfaced most reliably under concurrent execution racing on the shared
compiled cache. The fix disables result-set adapt_to_context for this
path, mirroring the ORM INSERT and ORM SELECT load paths, since the cached
keymap already carries the correct Column objects.
ORM DELETE statements are not affected: DELETE does not pass through
crud._get_crud_params() and therefore retains the user-requested RETURNING
order; a non-regression DELETE case is included in the test.
Use coercion rules for aliased() against select/union constructs
Calling :func:`_orm.aliased` against a :func:`_sql.select` or
:func:`_sql.union` / :class:`_sql.CompoundSelect` construct, which
previously failed with an obscure ``AttributeError`` regarding a missing
``.mapper`` attribute, now raises when using SQLAlchemy 2.1, and emits a
deprecation warning under SQLAlchemy 2.0 as it coerces the construct into a
subquery instead. This matches the behavior of other similar implicit
SELECT-to-FROM coercions. Pull request courtesy Rens Groothuijsen.
Mike Bayer [Mon, 13 Jul 2026 21:29:50 +0000 (17:29 -0400)]
PostgreSQL WITH options: proper bool/None rendering; support CreateView
Added ``postgresql_with`` support to :class:`.CreateView` for specifying
PostgreSQL view options such as ``security_invoker``, ``security_barrier``,
and ``check_option``, rendered as a ``WITH (...)`` clause between the view
name and the ``AS`` keyword.
Additionally, the ``postgresql_with`` parameter accepted by
:class:`.Table` and :class:`.Index` now correctly renders Python boolean
values as ``true``/``false`` (lowercase), and ``None`` values as the
parameter name alone without an ``= value`` portion.
A shared ``_prepare_withclause_opts`` helper in ``PGDDLCompiler``
is used by all three constructs for consistent formatting.
Use identifier preparer for string type collations
Adjusted string type collation rendering to use the dialect identifier
preparer rather than unconditional quoting. This allows PostgreSQL
schema-qualified collation names to be passed using quoted_name with
quote=False. Added a PostgreSQL dialect documentation section with
examples for schema-qualified collation usage.
Mike Bayer [Sun, 12 Jul 2026 22:11:21 +0000 (18:11 -0400)]
rewrite SQLite in-memory database docs for concurrency
Rewrote the "Using a Memory Database in Multiple Threads" section
in the pysqlite dialect docs to lead with the fundamental constraint
that a :memory: database is scoped to a single connection, and is
not suitable for concurrent use without shared cache or full
serialization.
Added a new "Using a Shared-Cache Memory Database" subsection
recommending the file::memory:?cache=shared&uri=true URI approach,
which gives each checkout its own DBAPI connection with independent
transaction state while sharing one in-memory database. Documented
process-global scoping and named databases for isolation.
Demoted the StaticPool approach to a secondary subsection with a
prominent warning about its single-connection limitation and silent
data loss under concurrent sessions.
Added a corresponding "Using a Memory Database with Multiple
Coroutines" section to the aiosqlite dialect docs, cross-referencing
the pysqlite shared-cache documentation.
jonathan vanasco [Sat, 11 Jul 2026 17:16:58 +0000 (13:16 -0400)]
migration note on subqueries
Under 2.0, calls to `in_` and `not_in` no longer accept an explicit `.subquery()`.
Passing a `.subquery()` will cause typing issues from MyPy AND will raise runtime warnings.
There was no note of this in the migration guide. This may be an effect of another change that is disclosed in the migration guide. If so, I suggest nesting this text (or improved text describing this) under that section for ease in discovery and migration.
Added a note to the 2.0 migration guide.
<!-- 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.
Mike Bayer [Thu, 9 Jul 2026 20:44:16 +0000 (16:44 -0400)]
Allow overriding @validates for inheritance
When a subclass overrides a :func:`_orm.validates` method using the
same method name as the parent class, only the subclass validator is
now invoked for instances of the subclass. The subclass validator
may call ``super()`` to also invoke the parent class validator.
Previously, the parent validator was always used regardless of
whether the subclass provided an override. Pull request courtesy
Indivar Mishra.
as an aside, also sets -j auto for the sphinx autobuild
utility in the makefile, seems to work. I would assume this was
not working when I originally added this option.
Use _effective_decimal_return_scale in Numeric.result_processor
Fixed an issue in :class:`.Numeric` where the
:paramref:`.Numeric.decimal_return_scale` parameter was ignored when the
DBAPI does not support native decimal objects (i.e.
``dialect.supports_native_decimal`` is ``False``). In this path the result
processor was computing the conversion scale from
:paramref:`.Numeric.scale` directly, bypassing
:paramref:`.Numeric.decimal_return_scale` entirely. The behavior now
matches :class:`.Float`, which already used the correct
``_effective_decimal_return_scale`` property. Pull request courtesy Kadir
Can Ozden.
w-Jessamine [Mon, 29 Jun 2026 10:30:16 +0000 (06:30 -0400)]
Allow inspection registrations from module reloads
Allowed the inspection registry to replace an existing registration with a
reloaded callable from the same module and name. This avoids an assertion
failure for tooling that unloads and reloads SQLAlchemy modules while still
rejecting conflicting registrations. Pull request courtersy w-Jessamine.
update oracledb async dialect for oracledb __aenter__()
Updated the oracledb async dialect where the async cursor adapter invoked
``__enter__()`` rather than ``__aenter__()`` on the underlying cursor.
While these are equivalent in oracledb itself, the correct async form is
now used for correctness. As ``AsyncCursor.__aenter__()`` was added in
oracledb 2.0.1, the minimum supported oracledb version is now 2.0.1,
declared via the ``oracle-oracledb`` extra. Pull request courtesy AVRC26.
fix catastrophic backtracking in sqlite inline unique reflection regex
Fixes: #13419
### Description
While reflecting a SQLite table, `get_unique_constraints` scans the stored
`CREATE TABLE` text (taken verbatim from `sqlite_master.sql`) with an
`INLINE_UNIQUE_PATTERN` to spot inline `UNIQUE` columns. The tail of that
pattern is `[\t ]+[a-z0-9_ ]+?[\t ]+UNIQUE`, where the lazy middle class
itself contains a space, so all three quantifiers can lay claim to the same
space character. When a column definition contains a run of whitespace that
isn't followed by `UNIQUE`, the engine tries every way of splitting that run
across the three quantifiers, which is cubic in the length of the run.
SQLite keeps the original whitespace of a `CREATE TABLE` statement in
`sqlite_master`, so any account that can create a table can leave a long gap
in a column definition and make later reflection of that schema hang.
A small reproduction:
```python
from sqlalchemy import create_engine, inspect
e = create_engine("sqlite://")
with e.begin() as c:
c.exec_driver_sql(
"CREATE TABLE t (x INTEGER" + " " * 1000 + "NOT NULL, "
"y INTEGER NOT NULL UNIQUE)"
)
inspect(e).get_unique_constraints("t") # ~11s before, instant after
```
The fix tokenises the inter-keyword whitespace with disjoint classes,
`[\t ]+[a-z0-9_]+(?:[\t ]+[a-z0-9_]+)*?[\t ]+UNIQUE`, so a space only ever
belongs to a separator and never to a token. Valid inline `UNIQUE` columns
reflect exactly as before; I have added a regression test that reflects a
table carrying a long whitespace run and runs in linear time on the new
pattern.
Ilan Keshet [Wed, 8 Jul 2026 21:49:35 +0000 (17:49 -0400)]
include rollback exception in closed transaction context manager error message
Improved the error message raised when a Session is used inside a context
manager after the transaction has been rolled back due to an exception.
The InvalidRequestError now includes the original exception that triggered
the rollback, making it clearer why the transaction is no longer active.
Pull request courtesy Ilan Keshet.
James Addison [Sat, 4 Jul 2026 08:02:35 +0000 (04:02 -0400)]
SQL codestyle: trim spacing around 'INHERITS' table list
### Description
This is _pedantic_ consistency with other comma-separated SQL lists - e.g. `VALUES`, `IN`, `DISTINCT ON` - and subqueries, where generally parantheses are not inner-padded by space characters.
The test cases updated illustrate the difference:
```diff
- "CREATE TABLE atable (id INTEGER) INHERITS ( i1 )",
+ "CREATE TABLE atable (id INTEGER) INHERITS (i1)",
```
I noticed during source code diff review between [v2.0.50 and v2.0.51](https://github.com/sqlalchemy/sqlalchemy/compare/rel_2_0_50...rel_2_0_51) that spaces are added on each side within the parentheses containing the inherited table list.
If it's intentional to draw attention to the fact that these are tables and not column names -- then my apologies, that would seem sorta reasonable.
The parsing/interpretation of these spaces seems unlikely to be the change that microscopically moves the performance needle enough to offset the climate crisis -- indeed the overhead of reading this and responding may be larger -- this is purely me being pedantic.
### Checklist
This pull request is:
- [x] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
Mike Bayer [Sat, 27 Jun 2026 20:20:17 +0000 (16:20 -0400)]
set PYTHONUTF8=1 for attestation step to fix encoding on Windows
pypi_attestations sign writes attestation JSON files using the Windows
default encoding (CP1252) rather than UTF-8, causing twine to fail with
a UnicodeDecodeError when reading them back. PYTHONUTF8=1 forces UTF-8
for all file I/O on Windows.
Mike Bayer [Sat, 27 Jun 2026 19:59:35 +0000 (15:59 -0400)]
continue-on-error for upload-release-assets to handle already_exists on re-runs
When re-running the wheel build, wheels already uploaded to the GitHub release
cause upload-release-assets to fail with already_exists. With continue-on-error,
the attestation and PyPI upload steps still proceed since the wheel files remain
in ./wheelhouse/ regardless.
Mike Bayer [Sat, 27 Jun 2026 19:50:02 +0000 (15:50 -0400)]
add shell: bash to attestations step to fix glob expansion on Windows
PowerShell does not expand ./wheelhouse/* globs, causing pypi_attestations
sign to receive a literal asterisk and fail. Explicit bash shell ensures
glob expansion works on all platforms.
Mike Bayer [Sat, 27 Jun 2026 19:46:41 +0000 (15:46 -0400)]
fall back to twine upload without attestations if attestation signing fails
On some platforms (e.g. windows-11-arm) pypi-attestations cannot install
due to missing cryptography binary wheels. Use continue-on-error on the
attestation step and conditionally pass --attestations to twine only when
signing succeeded.
Mike Bayer [Sat, 27 Jun 2026 19:17:29 +0000 (15:17 -0400)]
fix create-wheels.yaml for cibuildwheel 4.x and release asset upload
Remove CIBW_ENABLE cpython-freethreading which is no longer a valid enable
group in cibuildwheel 4.x; free-threaded builds are now included by default
when specifying cp314t-* in CIBW_BUILD.
Change contents permission from read to write so that the upload-release-assets
step can attach wheels to the GitHub release.
Mike Bayer [Sat, 27 Jun 2026 16:15:59 +0000 (12:15 -0400)]
fixes for m2momitjointest
in 808fd28297f36bf932443bae77ca5bb16bcbd4dd , the new test suite
created per-column `ForeignKey` constructs instead of composite
ForeignKeyConstraint objects, leading to failures on all backends
other than SQLite. The test now runs with backend, and also does not
need AssertsCompiledSQL directives.
Mike Bayer [Fri, 26 Jun 2026 14:09:38 +0000 (10:09 -0400)]
wrap before/after_cursor_execute event hooks in error handling
Expanded try/except error handling in _exec_single_context(),
_exec_insertmany_context(), and _cursor_execute() to encompass the
before_cursor_execute and after_cursor_execute event hooks. This
ensures that exceptions raised within these hooks, including
BaseException subclasses such as asyncio.CancelledError, are
properly handled via _handle_dbapi_exception(), providing correct
connection invalidation and pool notification.
Also added a guard in _handle_dbapi_exception to avoid
double-wrapping exceptions that are already StatementError
instances, which could occur when _cursor_execute's error handling
propagates up through _execute_context.
As part of this change, DBAPI errors raised from within these event
hooks will now be wrapped as SQLAlchemy exceptions.
Mike Bayer [Wed, 24 Jun 2026 12:49:30 +0000 (08:49 -0400)]
override get_select_precolumns() in StrSQLCompiler
Fixed issue where :meth:`_sql.Select.get_final_froms` would emit a
deprecation warning when the statement made use of the PostgreSQL-specific
expression argument to :meth:`_sql.Select.distinct`; the same spurious
warning would be emitted when stringifying such a statement without
explicitly using a PostgreSQL dialect. The fix ensures that this 1.4-era
warning is suppressed under both 2.0 and 2.1.
Note that under SQLAlchemy 2.1, passing an expression to
:meth:`_sql.Select.distinct` is deprecated overall, and is replaced by a
new PostgreSQL-specific construct (see :ticket:`12342`).
dxbjavid [Mon, 22 Jun 2026 15:30:40 +0000 (11:30 -0400)]
fix catastrophic backtracking in mysql index comment reflection regex
Improved the regular expression used to parse index COMMENT clauses
in MySQL SHOW CREATE TABLE reflection to use an unambiguous
single-quoted-string pattern; the previous pattern was theoretically
subject to backtracking on malformed input, though such input is not
producible by MySQL itself. Fix courtesy of Javid Khan.
cjc0013 [Thu, 11 Jun 2026 21:23:04 +0000 (17:23 -0400)]
Add explicit USING support to DELETE
Added :meth:`_sql.Delete.using`, allowing explicit FROM expressions such as
joins to be rendered in backend-specific multiple-table DELETE forms
including MySQL/MariaDB ``DELETE .. USING``. Pull request courtesy
cjc0013.
Mike Bayer [Mon, 22 Jun 2026 15:36:02 +0000 (11:36 -0400)]
use @classmethod per pytest guidance
Fixed class-scoped pytest fixtures that were defined as instance methods
using ``self``, which is deprecated as of pytest 9.1 and will be removed in
pytest 10. Fixtures are now decorated with a compatibility ``@classmethod``
decorator and use ``cls`` as the first parameter.
dxbjavid [Wed, 17 Jun 2026 12:49:06 +0000 (08:49 -0400)]
quote driver name and pass-through keys in pyodbc connect string
Tightened the construction of the ODBC connection string in the pyodbc
connector (as well as the mssql-python connector in 2.1) so that the
driver name, the names of pass-through connection parameters, and values
containing ``}`` are brace-quoted. Previously a ``}`` in the driver name
or in a pass-through value, or a ``;`` in the name of a pass-through
parameter, could close the surrounding token early and allow the
remainder of the string to be interpreted as additional connection
attributes. Pull request courtesy dxbjavid.
Mike Bayer [Wed, 17 Jun 2026 22:06:56 +0000 (18:06 -0400)]
Fix is_pep695 misidentifying Annotated[TypeAliasType] as PEP 695
The is_pep695() function incorrectly identified
Annotated[TypeAliasType, ...] as a PEP 695 type alias because
Annotated's __origin__ attribute returns the first type argument
(the TypeAliasType) rather than Annotated itself. This caused
_init_column_for_annotation to crash with AttributeError when
attempting to access __value__ on the Annotated wrapper.
Added a check for is_pep593() before recursing through __origin__
in is_pep695(), so Annotated types are correctly excluded.
Oliver Parker [Wed, 17 Jun 2026 15:15:18 +0000 (11:15 -0400)]
Improve performance of selectinload result handling by up to ~30%
* in selectinloader, dont use Bundle() to represent the PK portion
* use more efficient mapper._state_ident_getter() method in selectinloader
which pre-resolves keys and only calls upon _get_state_attr_by_column when
an attribute is not locally present
* removed use of groupby() + lambda against Row objects in subqueryloader; converts
to tuple and builds lists via append()
Adds tests pinning behavior of the rewritten result handling: the uselist=False multiple-rows warning, and many-to-one loads where the foreign key value matches no row or is NULL.
Benchmarked on an in-memory SQLite database (median of 30 runs, ms):
Oliver Parker [Mon, 15 Jun 2026 14:41:06 +0000 (10:41 -0400)]
Fixes: 13363 Process ORM result rows as plain tuples without Row construction
ORM result row fetching now processes rows as plain tuples rather than
constructing :class:`.Row` objects, as ORM loaders use position-based
access and do not require the :class:`.Row` interface. :class:`.Row`
construction is still used when engine-level debug logging is enabled so
that individual rows can be logged. Benchmarks show a 3-16% improvement in
ORM entity load times depending on query shape. Pull request courtesy
Oliver Parker.
Adds Result._all_interim_rows(), which returns the remaining rows as processed plain tuples, applying result processors and tuple filters but skipping Row object construction. ORM loading uses this for its row fetch; its row getters are position-based itemgetters that accept any tuple-like row. Results that require row logging or have scalar sources fall back to Row construction.
Adds tests covering the new behavior: rows are plain tuples with result processors applied, and Row construction still occurs when engine-level row logging is enabled, at both the Result and ORM loading level.
Benchmarked on an in-memory SQLite database with this change alone (median of 30 runs, ms); this path is used by all ORM entity loads:
plain_small (500 rows x 5 cols) 2.43 -> 2.28 -6%
plain_wide (2000 rows x 25 cols) 8.17 -> 7.24 -11%
joined_o2m (500 x 10) 18.56 -> 16.95 -9%
selectin_o2m (500 x 10) 18.45 -> 17.79 -4%
selectin_nested (50 x 10 x 10) 18.67 -> 17.33 -7%
selectin_m2m (500 x 10) 12.70 -> 11.53 -9%
selectin_m2o (5000 -> 200) 15.83 -> 15.29 -3%
selectin_o2m_few_big (20 x 500) 32.96 -> 31.29 -5%
subquery_o2m (500 x 10) 21.51 -> 18.16 -16%
dxbjavid [Fri, 12 Jun 2026 11:19:04 +0000 (07:19 -0400)]
fix backtracking hang in hstore literal parser
Fixed regular expression in the pure Python hstore result processor,
used when ``use_native_hstore=False`` is set, which could hang on
malformed hstore text containing unterminated quoted segments with
backslashes. Pull request courtesy dxbjavid.
Mike Bayer [Fri, 5 Jun 2026 20:02:02 +0000 (16:02 -0400)]
factor single-table reflection wrappers into common mixin
Introduced _BackendsMultiReflection mixin in engine/default.py
that provides the get_columns(), get_pk_constraint(),
get_foreign_keys(), get_indexes(), get_unique_constraints(),
get_check_constraints(), get_table_comment(), and
get_table_options() single-table methods, each delegating to
the corresponding get_multi_* method with
filter_names=[table_name].
PostgreSQL, Oracle, and MSSQL dialects now inherit from this
mixin instead of duplicating the wrapper pattern. Oracle
retains its _value_or_raise() override which applies
normalize_name() for case-folding. MSSQL overrides
get_unique_constraints(), get_check_constraints(), and
get_table_options() with NotImplementedError since it has no
native get_multi_* for those yet.
Mike Bayer [Tue, 9 Jun 2026 18:47:46 +0000 (14:47 -0400)]
allow rollback within _prepare_impl on twophase prepare failure
When tpc_prepare() raised during SessionTransaction._prepare_impl(),
the error handler's call to self.rollback() was blocked by the
@declare_states decorator, which had set _next_state to
CHANGE_IN_PROGRESS. This caused IllegalStateChangeError to be raised
instead of the original database exception, masking the real error
and preventing proper cleanup.
Used _expect_state(SessionTransactionState.CLOSED) to temporarily
allow the rollback state transition, matching the existing pattern
used in commit() for the close() call.
Repaired bug introduced in :ticket:`13229` where a two-phase
transaction recovery would not return the correct transaction
identifier when generating the identifiers using the ``xid()``
method of the psycopg connection.
Mike Bayer [Fri, 5 Jun 2026 16:35:35 +0000 (12:35 -0400)]
use trusted publishing for PyPI wheel uploads
Replace token-based PyPI authentication with OIDC trusted publishing.
Add workflow-level id-token: write permission, generate PEP 740
attestations using pypi-attestations, and upload with
twine --attestations. Removes the pypi_token secret dependency.
Removed the legacy ``include_columns`` key from the dictionary returned
by the index reflection methods of some dialects.
This information is now part of the ``dialect_options`` dictionary under the key
``{dialect_name}_include``, such as ``postgresql_include`` or ``mssql_include``.
Gaurav Sharma [Wed, 3 Jun 2026 12:52:09 +0000 (08:52 -0400)]
Implement native multi-table reflection API for the mssql dialect
### Description
Adds 5 native `get_multi_*` reflection methods (columns, pk, fk, indexes, table_comment) for the MSSQL dialect, replacing the per-table loop in `_default_multi_reflect`. Single-table methods now delegate to the multi versions (PG/Oracle pattern); legacy per-table SQL is retained as `_internal_get_*` helpers, used only for tempdb reflection.
Not implemented here: `get_multi_unique_constraints`, `get_multi_check_constraints`, `get_multi_table_options` -MSSQL has no single-table counterparts to delegate from. Happy to add as a follow-up.
### Performance
Measured with `test/perf/many_table_reflection.py` against SQL Server 2022 (Docker, localhost, pyodbc + ODBC Driver 18) on a 250-table fixture, 15-50 cols, with PKs/FKs/indexes/comments:
Mike Bayer [Wed, 3 Jun 2026 13:46:19 +0000 (09:46 -0400)]
send execution options to connection also
Session level :paramref:`_orm.Session.execution_options` now take
effect for Core level SQL emitted by unit of work operations, in
addition to their existing use within ORM statement executions.
This is to provide for Core options such as
:paramref:`_engine.Connection.execution_options.schema_translate_map`
to be applicable to a :class:`.Session` overall.
Mike Bayer [Thu, 4 Jun 2026 14:17:06 +0000 (10:17 -0400)]
Register func.any(), func.all(), func.some() as collection aggregates
Added CollectionAggregateFunction base class that sets
_is_collection_aggregate = True, and registered any_, all_, some_
as subclasses so that func.any(), func.all(), and func.some() correctly
prevent operator flipping on negation. Previously ~(col == func.any(arr))
would incorrectly compile to col != any(arr) instead of
NOT (col = any(arr)), which has different semantics for collection
aggregate comparison modifiers.
Also extended the _construct_for_op guard to check both left and right
operands for _is_collection_aggregate, since func.any(arr) can appear
on either side of a comparison unlike the standalone any_() construct
which auto-reverses operands.
Oliver Parker [Wed, 3 Jun 2026 06:51:13 +0000 (02:51 -0400)]
Perf/conditional unique selectinload
Optimized :func:`_orm.selectinload` to skip the ``.unique()`` call on inner
result sets when no nested :func:`_orm.joinedload` on a collection is
present. The uniquing pass is only required when a joined eager load
inflates rows due to a one-to-many or many-to-many JOIN; in the common case
of a leaf selectin load, rows are already unique by construction and the
per-row hashing overhead can be avoided. As a side effect, ``yield_per``
set in a ``do_orm_execute`` event for a :func:`_orm.selectinload`
relationship load no longer raises ``InvalidRequestError`` when no nested
collection joinedload is in effect, since ``.unique()`` is no longer called
in that path. Pull request courtesy Oliver Parker.
`_SelectInLoader._load_via_parent` and `_load_via_child` currently call
`.unique()` unconditionally on the inner `Result`. The uniqueness pass is only
required when a nested `joinedload` on a collection is in effect — in that case
the `JOIN` inflates rows (one row per `(child, grandchild)` instead of one per
child) and the outer `groupby` would produce duplicated entries without dedup.
`loading.instances()` already signals exactly this condition: it sets
`Result._unique_filter_state` to a `require_unique` guard when the inner
compile state has `multi_row_eager_loaders=True`. When that flag is unset (no
nested collection `joinedload`), `_unique_filter_state` stays `None`, meaning
the inner query produces unique rows by construction:
- `omit_join` 1:N — one row per child
- `omit_join` M2O — one row per parent
- `omit_join` M2M — one row per (parent, entity)
- `load_with_join` (non-omit) — one row per (parent, child)
This PR makes the `.unique()` call conditional, via a new `_has_unique_filter`
property on `Result` that exposes this state without reaching into the private
`_unique_filter_state` attribute directly:
```python
if result._has_unique_filter:
result = result.unique()
```
Per-row hashing in `_iterator_getter` is avoided in the common leaf-load case.
On an in-memory SQLite bench (2000 parents × 5 children + M:N tags, Python
3.14, n=1000 iterations):
| Workload | before avg | after avg | delta | before sd | after sd |
|---|---|---|---|---|---|
| selectin children (1:N) | 56.0 ms | 41.4 ms | **−26%** | 5.2 ms | 1.9 ms |
| selectin tags M2M | 25.6 ms | 20.0 ms | **−22%** | 4.2 ms | 3.1 ms |
| joined children (1:N) | 42.9 ms | 37.6 ms | ~0% | — | — |
| plain parent load | 4.2 ms | 3.6 ms | ~0% | — | — |
**Behaviour change:** `yield_per` set in a `do_orm_execute` event for a
relationship load no longer raises
`InvalidRequestError("Can't use yield_per in conjunction with unique")` for
`selectinload` without a nested collection `joinedload` — because `.unique()`
is no longer called in that path. `immediateload` is unaffected (it still calls
`.unique()` unconditionally).
sebastianbreguel [Sun, 31 May 2026 20:12:19 +0000 (16:12 -0400)]
Hoist loop-invariant set intersection in _get_display_froms
Fixes #13336.
`SelectState._get_display_froms` recomputed a loop-invariant `_cloned_intersection(...)` once per FROM element in each of the three correlation comprehensions, making each branch O(N²) in the number of FROM elements. This hoists the call so it runs once, which is O(N).
`_cloned_intersection` / `_cloned_difference` are pure and return a set, and neither argument changes during the comprehension, so the result is identical. A function-level benchmark asserts `old == new` at every N (full numbers in #13336), and `test/sql/` plus the ORM compilation/query tests pass: 7442 passed, 359 skipped. Net -14 lines.
Per the issue discussion, no changelog entry is included.
### Checklist
This pull request is:
- [x] A short code fix
- Issue with a runnable demonstration: #13336
- Behavior-preserving (no logic change), so it is covered by the existing `test/sql/` and ORM compilation/query suites rather than adding new tests.
me-saurabhkohli [Fri, 29 May 2026 20:03:40 +0000 (16:03 -0400)]
Add ambiguous column support to SimpleResultMetaData
Fixed issue where :meth:`.Result.freeze` would lose track of ambiguous
column names present in the original :class:`.CursorResult`, causing
key-based access on the thawed result to silently return a value instead of
raising :class:`.InvalidRequestError`. The
:class:`.SimpleResultMetaData` now accepts and propagates ambiguous key
information so that frozen, thawed, and pickled results raise consistently
for duplicate column names. Pull request courtesy Saurabh Kohli.
Mike Bayer [Thu, 28 May 2026 14:25:39 +0000 (10:25 -0400)]
allow backref named 'metadata' to not break _metadata_for_cls
Fixed regression caused by :ticket:`8068` where a ``backref``
named ``'metadata'`` on a mapped class would cause an
``AssertionError`` when the class also used string-based
relationship references (e.g. ``secondary="some_table"``).
The ``_metadata_for_cls()`` helper now checks
``isinstance(meta, MetaData)`` as a condition rather than
asserting, falling back to ``registry.metadata`` when the
class attribute has been overwritten by a backref.
A warning is now emitted when a Declarative attribute name is named
``metadata`` or ``registry``. Previously, no warning was emitted for
``registry``, and using the name ``metadata`` would raise an
InvalidRequestError. Since these names can be used for attributes
that are mapped as backrefs or using imperative mappings, usage
under Declarative has been relaxed for ``metadata`` but also warns
for both names as they may have unintended interactions with the
Declarative reserved names.
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
