Mike Bayer [Wed, 21 Jan 2026 21:37:03 +0000 (16:37 -0500)]
remove 12240. rst (note space)
this file got successfully merged into changelog_21 for the 2.1.0b1
release however the file deletion phase missed the file itself,
causing it to render in both 2.1.0b1 and 2.1.0b2 on the site.
Mike Bayer [Tue, 20 Jan 2026 14:45:25 +0000 (09:45 -0500)]
change float -> Double in the _type_map.
The :class:`.Double` type is now used when a Python float value is detected
as a literal value to be sent as a bound parameter, rather than the
:class:`.Float` type. :class:`.Double` has the same implementation as
:class:`.Float`, but when rendered in a CAST, produces ``DOUBLE`` or
``DOUBLE PRECISION`` rather than ``FLOAT``. The former better matches
Python's ``float`` datatype which uses 8-byte double-precision storage.
Third party dialects which don't support the :class:`.Double` type directly
may need adjustment so that they render an appropriate keyword (e.g.
``FLOAT``) when the :class:`.Double` datatype is encountered.
Gord Thompson [Mon, 19 Jan 2026 12:34:28 +0000 (05:34 -0700)]
make qtoken for PostgreSQL _fk_regex_pattern less restrictive
Improved the foreign key reflection regular expression pattern used by the
PostgreSQL dialect to be more permissive in matching identifier characters,
allowing it to correctly handle unicode characters in table and column
names. This change improves compatibility with PostgreSQL variants such as
CockroachDB that may use different quoting patterns in combination with
unicode characters in their identifiers. Pull request courtesy Gord
Thompson.
Mike Bayer [Wed, 14 Jan 2026 16:06:50 +0000 (11:06 -0500)]
separate out mariadb/mysql implementations but remain monolithic
Fixes to the MySQL/MariaDB dialect so that mariadb-specific features such
as the :class:`.mariadb.INET4` and :class:`.mariadb.INET6` datatype may be
used with an :class:`.Engine` that uses a ``mysql://`` URL, if the backend
database is actually a mariadb database. Previously, support for MariaDB
features when ``mysql://`` URLs were used instead of ``mariadb://`` URLs
was ad-hoc; with this issue resolution, the full set of schema / compiler /
type features are now available regardless of how the URL was presented.
After much discussion it seems premature to formally separate the
mysql/mariadb dialects to be mutually exclusive, however we'd like
to standardize on how dialect-exclusive behaviors are architected.
For now, use an approach of MariaDB "shim" classes which provide
MariaDB behaviors into all relevant MySQL classes up front. Where
behaviors are mutually exclusive, support `_set_mariadb()` methods
that enable the mariadb version of things.
this approach may or may not have resilience against future
divergences in mysql/mariadb , but at least starts to separate the
source code for the two databases and hopefully provides a clearer
separation of concerns.
rusher [Wed, 14 Jan 2026 14:03:00 +0000 (09:03 -0500)]
correct mariadb sequence behavior when cycle=False
Fixed the SQL compilation for the mariadb sequence "NOCYCLE" keyword that
is to be emitted when the :paramref:`.Sequence.cycle` parameter is set to
False on a :class:`.Sequence`. Pull request courtesy Diego Dupin.
Mike Bayer [Tue, 13 Jan 2026 14:19:14 +0000 (09:19 -0500)]
typing updates to accept with_polymorphic(), aliases
Fixed typing issues where ORM mapped classes and aliased entities could not
be used as keys in result row mappings or as join targets in select
statements. Patterns such as ``row._mapping[User]``,
``row._mapping[aliased(User)]``, ``row._mapping[with_polymorphic(...)]``
(rejected by both mypy and Pylance), and ``.join(aliased(User))``
(rejected by Pylance) are documented and fully supported at runtime but
were previously rejected by type checkers. The type definitions for
:class:`._KeyType` and :class:`._FromClauseArgument` have been updated to
accept these ORM entity types.
Federico Caselli [Wed, 26 Nov 2025 21:13:50 +0000 (22:13 +0100)]
Deprecate DropConstraint.isolate_from_table
The the parameter :paramref:`_schema.DropConstraint.isolate_from_table`
was deprecated since it has no effect on the drop table behavior.
Its default values was also changed to ``False``.
Mike Bayer [Thu, 8 Jan 2026 21:37:38 +0000 (16:37 -0500)]
mark has()/any() as ORM; adjust criteria in WHERE without explicit FROM
A significant change to the ORM mechanics involved with both
:func:`.orm.with_loader_criteria` as well as single table inheritance, to
more aggressively locate WHERE criteria which should be augmented by either
the custom criteria or single-table inheritance criteria; SELECT statements
that do not include the entity within the columns clause or as an explicit
FROM, but still reference the entity within the WHERE clause, are now
covered, in particular this will allow subqueries using ``EXISTS (SELECT
1)`` such as those rendered by :meth:`.RelationshipProperty.Comparator.any`
and :meth:`.RelationshipProperty.Comparator.has`.
Mike Bayer [Wed, 7 Jan 2026 01:03:10 +0000 (20:03 -0500)]
apply Grouping on left side of JSONB subscript in compiler
Fixed regression in PostgreSQL dialect where JSONB subscription syntax
would generate incorrect SQL for :func:`.cast` expressions returning JSONB,
causing syntax errors. The dialect now properly wraps cast expressions in
parentheses when using the ``[]`` subscription syntax, generating
``(CAST(...))[index]`` instead of ``CAST(...)[index]`` to comply with
PostgreSQL syntax requirements. This extends the fix from :ticket:`12778`
which addressed the same issue for function calls.
This reverts how we did the fix for #12778 in Function.self_group()
and instead moves to a direct Grouping() applied in the PG compiler
based on isinstance of the left side.
in retrospect, when we first did #10927, we **definitely** made
the completely wrong choice in how to do this, the original idea
to detect when we were in an UPDATE and use [] only then was
by **far** what we should have done, given the fact that PG indexes
are based on exact syntax matches. but since we've made everyone
switch to [] format for their indexes now we can't keep going
back and forth. even though PG would like [] to be the defacto
syntax it simply is not. We should potentially pursue a dialect/
create_engine option to switch the use of [] back to -> for
all cases except UPDATE.
Mike Bayer [Thu, 1 Jan 2026 16:39:45 +0000 (11:39 -0500)]
we probably dont need ORDER BY for selectin_polymorphic
Removed the ``ORDER BY`` clause from queries generated by
:func:`_orm.selectin_polymorphic` and the
:paramref:`_orm.Mapper.polymorphic_load` parameter set to ``"selectin"``.
The ``ORDER BY`` clause appears to have been an unnecessary implementation
artifact.
LucasMalor [Tue, 23 Dec 2025 16:28:35 +0000 (11:28 -0500)]
improved immutabledict merge_with and union
Now `immutabledict.merge_with is an alias of `immutabledict.union`,
both accept multiple arguments.
The methods now avoid doing copies of not required: if the method is
called only one `immutabledict` that's not empty it's returned.
Federico Caselli [Wed, 24 Dec 2025 13:02:17 +0000 (14:02 +0100)]
Support aiosqlite 0.22.0+
Fixed issue in the aiosqlite driver where SQLAlchemy's setting of
aiosqlite's worker thread to "daemon" stopped working because the aiosqlite
architecture moved the location of the worker thread in version 0.22.0.
This "daemon" flag is necessary so that a program is able to exit if the
SQLite connection itself was not explicitly closed, which is particularly
likely with SQLAlchemy as it maintains SQLite connections in a connection
pool. While it's perfectly fine to call :meth:`.AsyncEngine.dispose`
before program exit, this is not historically or technically necessary for
any driver of any known backend, since a primary feature of relational
databases is durability. The change also implements support for
"terminate" with aiosqlite when using version version 0.22.1 or greater,
which implements a sync ``.stop()`` method.
Mike Bayer [Wed, 31 Dec 2025 20:48:44 +0000 (15:48 -0500)]
Fixed JSONB path_match and path_exists operators to use correct type coercion
Fixed issue where PostgreSQL JSONB operators
:meth:`_postgresql.JSONB.Comparator.path_match` and
:meth:`_postgresql.JSONB.Comparator.path_exists` were applying incorrect
``VARCHAR`` casts to the right-hand side operand when used with newer
PostgreSQL drivers such as psycopg. The operators now indicate the
right-hand type as ``JSONPATH``, which currently results in no casting
taking place, but is also compatible with explicit casts if the
implementation were require it at a later point.
Support for `IF EXISTS` in SQL Server 2016 (13.x) and later versions
Added support for the ``IF EXISTS`` clause when dropping indexes on SQL
Server 2016 (13.x) and later versions. The :paramref:`.DropIndex.if_exists`
parameter is now honored by the SQL Server dialect, allowing conditional
index drops that will not raise an error if the index does not exist.
Pull request courtesy Edgar RamÃrez Mondragón.
Mike Bayer [Thu, 18 Dec 2025 16:15:46 +0000 (11:15 -0500)]
handle polymorphic_discriminator in query_expression
Added support for using :func:`_orm.with_expression` to populate a
:func:`_orm.query_expression` attribute that is also configured as the
``polymorphic_on`` discriminator column. The ORM now detects when a query
expression column is serving as the polymorphic discriminator and updates
it to use the column provided via :func:`_orm.with_expression`, enabling
polymorphic loading to work correctly in this scenario. This allows for
patterns such as where the discriminator value is computed from a related
table.
Mike Bayer [Mon, 15 Dec 2025 19:53:48 +0000 (14:53 -0500)]
update default driver for oracle, postgresql
The default DBAPI driver for the Oracle Database dialect has been changed
to ``oracledb`` instead of ``cx_oracle``. The ``cx_oracle`` driver remains
fully supported and can be explicitly specified in the connection URL
using ``oracle+cx_oracle://``.
The default DBAPI driver for the PostgreSQL dialect has been changed to
``psycopg`` (psycopg version 3) instead of ``psycopg2``. The ``psycopg2``
driver remains fully supported and can be explicitly specified in the
connection URL using ``postgresql+psycopg2://``.
<!-- Provide a general summary of your proposed changes in the Title field above -->
<!-- Describe your changes in detail -->
`Query[_T].get(...)` should return `Optional[_T]` instead of `Optional[Any]`. This is typed correctly when migrating to `Session.get(_T, ...)`. By typing the legacy `Query.get(...)` call first, it should make migrations easier on developers, as it splits up the type checking improvements (and subsequent errors which may be discovered) from the `Query.get()`
to `Session.get()` migration.
<!-- 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.
Change continues here where we now use py314's built in
annotationlib for get_annotations; issue [1] was fixed long
ago before 3.14.0 was released.
change for now:
A change in the mechanics of how Python dataclasses are applied to classes
that use :class:`.MappedAsDataclass` or
:meth:`.registry.mapped_as_dataclass` to apply ``__annotations__`` that are
as identical as is possible to the original ``__annotations__`` given,
while also adding attributes that SQLAlchemy considers to be part of
dataclass ``__annotations__``, then restoring the previous annotations in
exactly the same format as they were, using patterns that work with
:pep:`649` as closely as possible.
G Allajmi [Tue, 9 Dec 2025 19:13:52 +0000 (14:13 -0500)]
Factor out constraints into separate methods
Fixed issue where PostgreSQL dialect options such as ``postgresql_include``
on :class:`.PrimaryKeyConstraint` and :class:`.UniqueConstraint` were
rendered in the wrong position when combined with constraint deferrability
options like ``deferrable=True``. Pull request courtesy G Allajmi.
G Allajmi [Mon, 8 Dec 2025 13:03:49 +0000 (08:03 -0500)]
Fix adding property to mapper before mapping is complete
Fixed issue where calling :meth:`.Mapper.add_property` within mapper event
hooks such as :meth:`.MapperEvents.instrument_class`,
:meth:`.MapperEvents.after_mapper_constructed`, or
:meth:`.MapperEvents.before_mapper_configured` would raise an
``AttributeError`` because the mapper's internal property collections were
not yet initialized. The :meth:`.Mapper.add_property` method now handles
early-stage property additions correctly, allowing properties including
column properties, deferred columns, and relationships to be added during
mapper initialization events. Pull request courtesy G Allajmi.
Mike Bayer [Tue, 9 Dec 2025 14:07:20 +0000 (09:07 -0500)]
use os.urandom() for CTE, aliased anon id
Fixed issue where anonymous label generation for :class:`.CTE` constructs
could produce name collisions when Python's garbage collector reused memory
addresses during complex query compilation. The anonymous name generation
for :class:`.CTE` and other aliased constructs like :class:`.Alias`,
:class:`.Subquery` and others now use :func:`os.urandom` to generate unique
identifiers instead of relying on object ``id()``, ensuring uniqueness even
in cases of aggressive garbage collection and memory reuse.
Mike Bayer [Mon, 1 Dec 2025 16:07:24 +0000 (11:07 -0500)]
support monotonic functions as sentinels
Added support for monotonic server-side functions such as PostgreSQL 18's
``uuidv7()`` to work with the :ref:`engine_insertmanyvalues` feature.
By passing ``monotonic=True`` to any :class:`.Function`, the function can
be used as a sentinel for tracking row order in batched INSERT operations
with RETURNING, allowing the ORM and Core to efficiently batch INSERT
statements while maintaining deterministic row ordering.
Reason for revert: everything has passed very well on jenkins, however GH actions is showing the two new test_cpython_142214 tests from 133f14dabed44f7398039 suddenly failing across hundreds of scenarios. given the risk of this change since dataclasses are very weird, need to see what this is about. im suspecting point release changes in older pythons like 3.10, 3.11, etc. which seems a bit ominous.
Mike Bayer [Fri, 5 Dec 2025 22:41:56 +0000 (17:41 -0500)]
fix / modernize short_selects example
Fixed the "short_selects" performance example where the cache was being
used in all the examples, making it impossible to compare performance with
and without the cache. Less important comparisons like "lambdas" and
"baked queries" have been removed.
A change in the mechanics of how Python dataclasses are applied to classes
that use :class:`.MappedAsDataclass` or
:meth:`.registry.mapped_as_dataclass`, to no longer modify the
`__annotations__` collection that's on the class, instead manipulating
regular class-bound attributes in order to satisfy the class requirements
for the dataclass creation function. This works around an issue that has
appeared in Python 3.14.1, provides for a much simpler implementation, and
also maintains accurate typing information about the attributes as the
dataclass is built.
Mike Bayer [Fri, 21 Nov 2025 15:41:40 +0000 (10:41 -0500)]
filter_by works across multiple entities
The :meth:`_sql.Select.filter_by`, :meth:`_sql.Update.filter_by` and
:meth:`_sql.Delete.filter_by` methods now search across all entities
present in the statement, rather than limiting their search to only the
last joined entity or the first FROM entity. This allows these methods
to locate attributes unambiguously across multiple joined tables,
resolving issues where changing the order of operations such as
:meth:`_sql.Select.with_only_columns` would cause the method to fail.
If an attribute name exists in more than one FROM clause entity, an
:class:`_exc.AmbiguousColumnError` is now raised, indicating that
:meth:`_sql.Select.filter` (or :meth:`_sql.Select.where`) should be used
instead with explicit table-qualified column references.
Mike Bayer [Wed, 3 Dec 2025 19:48:08 +0000 (14:48 -0500)]
Add a test for #13021
Confirmed the upstream fix for [1] given at [2] solves the issue
illustrated here, this patch adds a test for this case as our
existing tests did not catch this error in python 3.14.1.
Mike Bayer [Wed, 3 Dec 2025 04:10:10 +0000 (23:10 -0500)]
send same sub_stmt to after_cursor_execute as before
Fixed issue in the :meth:`.ConnectionEvents.after_cursor_execute` method
where the SQL statement and parameter list for an "insertmanyvalues"
operation sent to the event would not be the actual SQL / parameters just
emitted on the cursor, instead being the non-batched form of the statement
that's used as a template to generate the batched statements.
Mike Bayer [Mon, 1 Dec 2025 20:11:50 +0000 (15:11 -0500)]
run sentinel server side fns outside of VALUES
Fixed the structure of the SQL string used for the
:ref:`engine_insertmanyvalues` feature when an explicit sequence with
``nextval()`` is used. The SQL function invocation for the sequence has
been moved from being rendered inline within each tuple inside of VALUES to
being rendered once in the SELECT that reads from VALUES. This change
ensures the function is invoked in the correct order as rows are processed,
rather than assuming PostgreSQL will execute inline function calls within
VALUES in a particular order. While current PostgreSQL versions appear to
handle the previous approach correctly, the database does not guarantee
this behavior for future versions.
Yossi [Mon, 1 Dec 2025 17:06:12 +0000 (12:06 -0500)]
[typing] Fix type error when passing Mapped columns to values()
This adjusts the _DMLOnlyColumnArgument type to be a more
focused _OnlyColumnArgument type where we also add a more tightly
focused coercion, while still allowing ORM attributes to be used
as arguments.
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Closes: #13012
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13012
Pull-request-sha: 5ebb402c686abf1090e5b83e3489dfca4908efdf
Yeongbae Jeon [Sun, 23 Nov 2025 11:44:43 +0000 (06:44 -0500)]
Add native BOOLEAN type support for Oracle 23c and later versions
Added support for native BOOLEAN support in Oracle Database 23c and above.
The Oracle dialect now renders ``BOOLEAN`` automatically when
:class:`.Boolean` is used in DDL, and also now supports direct use of the
:class:`.BOOLEAN` datatype, when 23c and above is in use. For Oracle
versions prior to 23c, boolean values continue to be emulated using
SMALLINT as before. Special case handling is also present to ensure a
SMALLINT that's interpreted with the :class:`.Boolean` datatype on Oracle
Database 23c and above continues to return bool values. Pull request
courtesy Yeongbae Jeon.
Mike Bayer [Mon, 17 Nov 2025 18:11:24 +0000 (13:11 -0500)]
add TString support
Added support for Python 3.14+ template strings (t-strings) via the new
:func:`_sql.tstring` construct, as defined in :pep:`750`. This feature
allows for ergonomic SQL statement construction by automatically
interpolating Python values and SQLAlchemy expressions within template
strings.
Part of the challenge here is the syntax only works on py314, so we have
to exclude the test file at many levels when py314 is not used. not
sure yet how i want to adjust pep8 tests and rules for this.
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
