Implemented initial support for free-threaded Python by adding new tests
and reworking the test harness and GitHub Actions to include Python 3.13t
and Python 3.14t in test runs. Two concurrency issues have been identified
and fixed: the first involves initialization of the ``.c`` collection on a
``FromClause``, a continuation of 🎫`12302`, where an optional mutex
under free-threading is added; the second involves synchronization of the
pool "first_connect" event, which first received thread synchronization in
🎫`2964`, however under free-threading the creation of the mutex
itself runs under the same free-threading mutex. Initial pull request and
test suite courtesy Lysandros Nikolaou.
py313t: yes
py314t: yes
Fixes: #12881Closes: #12882
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12882
Pull-request-sha: 53d65d96b9
Co-authored-by: Mike Bayer <mike_mp@zzzcomputng.com>
Change-Id: I2e4f2e9ac974ab6382cb0520cc446b396d9680a6
The :meth:`_types.ARRAY.Comparator.any` and
:meth:`_types.ARRAY.Comparator.all` methods for the :class:`_types.ARRAY`
type are now deprecated for removal; these two methods along with
:func:`_postgresql.Any` and :func:`_postgresql.All` have been legacy for
some time as they are superseded by the :func:`_sql.any_` and
:func:`_sql.all_` functions, which feature more intutive use.
Fixes: #10821
Change-Id: I8eb3bbcb98af4ee60a21767dc3bdac771cbc0b4c
Fixed issue where the :func:`_sql.any_` and :func:`_sql.all_` aggregation
operators would not correctly coerce the datatype of the compared value, in
those cases where the compared value were not a simple int/str etc., such
as a Python ``Enum`` or other custom value. This would lead to execution
time errors for these values. This issue is essentially the same as
🎫`6515` which was for the now-legacy :meth:`.ARRAY.any` and
:meth:`.ARRAY.all` methods.
Fixes: #12874
Change-Id: I980894c23b9974bc84d584a1a4c5fae72dded6d3
Added new generalized aggregate function ordering to functions via the
:func:`_functions.FunctionElement.aggregate_order_by` method, which
receives an expression and generates the appropriate embedded "ORDER BY" or
"WITHIN GROUP (ORDER BY)" phrase depending on backend database. This new
function supersedes the use of the PostgreSQL
:func:`_postgresql.aggregate_order_by` function, which remains present for
backward compatibility. To complement the new parameter, the
:paramref:`_functions.aggregate_strings.order_by` which adds ORDER BY
capability to the :class:`_functions.aggregate_strings` dialect-agnostic
function which works for all included backends. Thanks much to Reuven
Starodubski with help on this patch.
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Fixes: #12853Closes: #12856
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12856
Pull-request-sha: d93fb591751227eb1f96052ea3ad449f511f70b3
Change-Id: I8eb41ff2d57695963a358b5f0017ca9372f15f70
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
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.
Fixes: #12736
Change-Id: I44f46d7326aef6847dbf0cf7a325833f8e347da6
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.
Fixes: #12769
Change-Id: I055ce79bf7ac31fb61d48bc3ab34799d42fb6336
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.
Fixes: #12734
Change-Id: I4a0146418420cce3cbbda4e50f5eb32206dc696b
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.
Fixes: #10802
Change-Id: Ie4f9dab96193099002088c5219cc41a543a00f62
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
Change-Id: Ic568638a8ce3607deea44af988b6451b30cde36c
Added method :meth:`.TableClause.insert_column` to complement
:meth:`.TableClause.append_column`, which inserts the given column at a
specific index. This can be helpful for prepending primary key columns to
tables, etc.
Fixes: #7910
Change-Id: Ife1eb8ad90aa61d38c457a86312cfe5d0d471106
Hardening of the compiler's actions for UPDATE statements that access
multiple tables to report more specifically when tables or aliases are
referenced in the SET clause; on cases where the backend does not support
secondary tables in the SET clause, an explicit error is raised, and on the
MySQL or similar backends that support such a SET clause, more specific
checking for not-properly-included tables is performed. Overall the change
is preventing these erroneous forms of UPDATE statements from being
compiled, whereas previously it was relied on the database to raise an
error, which was not always guaranteed to happen, or to be non-ambiguous,
due to cases where the parent table included the same column name as the
secondary table column being updated.
Fixed bug where the ORM would pull in the wrong column into an UPDATE when
a key name inside of the :meth:`.ValuesBase.values` method could be located
from an ORM entity mentioned in the statement, but where that ORM entity
was not the actual table that the statement was inserting or updating. An
extra check for this edge case is added to avoid this problem.
Fixes: #12692
Change-Id: I342832b09dda7ed494caaad0cbb81b93fc10fe18
Fixed issue where :func:`.select` of a free-standing, unnamed scalar expression that
has a unary operator applied, such as negation, would not apply result
processors to the selected column even though the correct type remains in
place for the unary expression.
This change opened up a typing rabbithole where we were led to also
improve and harden the typing for the Exists element, in particular
in that the Exists now always refers to a ScalarSelect object, and
no longer a SelectStatementGrouping within the _regroup() cases; there
did not seem to be any reason for this inconsistency.
Fixes: #12681
Change-Id: If9131807941030c627ab31ede4ccbd86e44e707f
Since I want to get rid of util.portable_instancemethod, first
make sure we are testing pickle extensively including going through
all protocols for all metadata-oriented tests.
Change-Id: I0064bc16033939780e50c7a8a4ede60ef5835b38
Changed the default value of :paramref:`_types.Enum.inherit_schema` to
``True`` when :paramref:`_types.Enum.schema` and
:paramref:`_types.Enum.metadata` parameters are not provided.
The same behavior has been applied also to PostgreSQL
:class:`_postgresql.DOMAIN` type.
Fixes: #10594
Change-Id: Id3d819e3608974353e365cd063d9c5e40a071e73
Allow custom operator systems to use the @ Python operator (#12479).
### Description
Add a dummy implementation for the `__matmul__` operator rasing `NotImplementedError` by default.
### 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:
- [ ] 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.
- [X] 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.
**Have a nice day!**
Closes: #12583
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12583
Pull-request-sha: 7e69d23610
Change-Id: Ia0d565decd437b940efd3b97478c16d7a0377bc6
Type the `create` and `drop` functions for `NamedType`s
Also partially type the SchemaType create/drop functions more generally
One change to this is that the default parameter of `None` is removed. It doesn't work and will fail with a `AttributeError` at runtime since it immediately tries to access a property of `None` which doesn't exist.
Fixes#12557
This pull request is:
- [X] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
- [X] 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.
**Have a nice day!**
Closes: #12558
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12558
Pull-request-sha: 75c8d81bfb
Change-Id: I173771d365f34f54ab474b9661e1cdc70cc4de84
Added new datatype :class:`_oracle.VECTOR` and accompanying DDL and DQL
support to fully support this type for Oracle Database. This change
includes the base :class:`_oracle.VECTOR` type that adds new type-specific
methods ``l2_distance``, ``cosine_distance``, ``inner_product`` as well as
new parameters ``oracle_vector`` for the :class:`.Index` construct,
allowing vector indexes to be configured, and ``oracle_fetch_approximate``
for the :meth:`.Select.fetch` clause. Pull request courtesy Suraj Shaw.
Fixes: #12317Closes: #12321
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12321
Pull-request-sha: a72a18a45c
Change-Id: I6f3af4623ce439d0820c14582cd129df293f0ba8
Added support for the pow operator (``**``), with a default SQL
implementation of the ``POW()`` function. On Oracle Database, PostgreSQL
and MSSQL it renders as ``POWER()``. As part of this change, the operator
routes through a new first class ``func`` member :class:`_functions.pow`,
which renders on Oracle Database, PostgreSQL and MSSQL as ``POWER()``.
Fixes: #8579Closes: #8580
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/8580
Pull-request-sha: 041b2ef474
Change-Id: I371bd44ed3e58f2d55ef705aeec7d04710c97f23
towards some refactorings I will need to do for #12496, this
factors out the "_ordered_values" list of tuples that was used to
track UPDATE VALUES in a specific order. The rationale for this
separate collection was due to Python dictionaries not maintaining
insert order. Now that this is standard behavior in Python 3
we can use the same `statement._values` for param-ordered and
table-column-ordered UPDATE rendering.
Change-Id: Id6024ab06e5e3ba427174e7ba3630ff83d81f603
Implemented support for the GROUPS frame specification in window functions
by adding :paramref:`_sql.over.groups` option to :func:`_sql.over`
and :meth:`.FunctionElement.over`. Pull request courtesy Kaan Dikmen.
Fixes: #12450Closes: #12445
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12445
Pull-request-sha: c0808e135f
Change-Id: I9ff504a9c9650485830c4a0eaf44162898a3a2ad
When building a PostgreSQL ``ARRAY`` literal using
:class:`_postgresql.array` with an empty ``clauses`` argument, the
:paramref:`_postgresql.array.type_` parameter is now significant in that it
will be used to render the resulting ``ARRAY[]`` SQL expression with a
cast, such as ``ARRAY[]::INTEGER``. Pull request courtesy Denis Laxalde.
Fixes: #12432Closes: #12435
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12435
Pull-request-sha: 9633d3c15d
Change-Id: I29ed7bd0562b82351d22de0658fb46c31cfe44f6
Fixed regression caused by 🎫`7471` leading to a SQL compilation
issue where name disambiguation for two same-named FROM clauses with table
aliasing in use at the same time would produce invalid SQL in the FROM
clause with two "AS" clauses for the aliased table, due to double aliasing.
Fixes: #12451
Change-Id: I981823f8f2cdf3992d65ace93a21fc20d1d74cda
A significant behavioral change has been made to the behavior of the
:paramref:`_orm.mapped_column.default` and
:paramref:`_orm.relationship.default` parameters, when used with
SQLAlchemy's :ref:`orm_declarative_native_dataclasses` feature introduced
in 2.0, where the given value (assumed to be an immutable scalar value) is
no longer passed to the ``@dataclass`` API as a real default, instead a
token that leaves the value un-set in the object's ``__dict__`` is used, in
conjunction with a descriptor-level default. This prevents an un-set
default value from overriding a default that was actually set elsewhere,
such as in relationship / foreign key assignment patterns as well as in
:meth:`_orm.Session.merge` scenarios. See the full writeup in the
:ref:`whatsnew_21_toplevel` document which includes guidance on how to
re-enable the 2.0 version of the behavior if needed.
This adds a new implicit default field to ScalarAttributeImpl
so that we can have defaults that are not in the dictionary but
are instead passed through to the class-level descriptor, effectively
allowing custom defaults that are not used in INSERT or merge
Fixes: #12168
Change-Id: Ia327d18d6ec47c430e926ab7658e7b9f0666206e
Added syntax extension :func:`_postgresql.distinct_on` to build ``DISTINCT
ON`` clauses. The old api, that passed columns to
:meth:`_sql.Select.distinct`, is now deprecated.
Fixes: #12342
Change-Id: Ia6a7e647a11e57b6ac2f50848778c20dc55eaf54
Added support for specifying a list of columns for ``SET NULL`` and ``SET
DEFAULT`` actions of ``ON DELETE`` clause of foreign key definition on
PostgreSQL. Pull request courtesy Denis Laxalde.
Fixes: #11595Closes: #12421
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12421
Pull-request-sha: d0394db706
Change-Id: I036a559ae4a8efafe9ba64d776a840bd785a7397
Fixed issue in :class:`.CTE` constructs involving multiple DDL
:class:`.Insert` statements with multiple VALUES parameter sets where the
bound parameter names generated for these parameter sets would conflict,
generating a compile time error.
Fixes: #12363
Change-Id: If8344ff725d4e0ec58d3ff61f38a0edcfc5bdebd
Added the ability to create custom SQL constructs that can define new
clauses within SELECT, INSERT, UPDATE, and DELETE statements without
needing to modify the construction or compilation code of of
:class:`.Select`, :class:`.Insert`, :class:`.Update`, or :class:`.Delete`
directly. Support for testing these constructs, including caching support,
is present along with an example test suite. The use case for these
constructs is expected to be third party dialects for NewSQL or other novel
styles of database that introduce new clauses to these statements. A new
example suite is included which illustrates the ``QUALIFY`` SQL construct
used by several NewSQL databases which includes a cachable implementation
as well as a test suite.
Since these extensions start to make it a bit crowded with how many
kinds of "options" we have on statements, did some naming /
documentation changes with existing constructs on Executable, in
particular to distinguish ExecutableOption from SyntaxExtension.
Fixes: #12195
Change-Id: I4a44ee5bbc3d8b1b640837680c09d25b1b7077af
Added new parameters :paramref:`.AddConstraint.isolate_from_table` and
:paramref:`.DropConstraint.isolate_from_table`, defaulting to True, which
both document and allow to be controllable the long-standing behavior of
these two constructs blocking the given constraint from being included
inline within the "CREATE TABLE" sequence, under the assumption that
separate add/drop directives were to be used.
Fixes: #12382
Change-Id: I53c4170ccb5803f69945ba7aa3d3a143131508eb
Fixed issue where using :func:`_orm.aliased` around a :class:`.CTE`
construct could cause inappropriate "duplicate CTE" errors in cases where
that aliased construct appeared multiple times in a single statement.
Fixes: #12364
Change-Id: I9625cd83e9baf5312cdc644b38951353708d3b86
The default implementation of :attr:`_sql.TypeEngine.python_type` now
returns ``object`` instead of ``NotImplementedError``, since that's the
base for all types in Python3.
The ``python_type`` of :class:`_sql.JSON` no longer returns ``dict``,
but instead fallbacks to the generic implementation.
Fixes: #10646
Change-Id: I2233e4a3d35a67b520a860d70afba8e5b22fd72d
Fixed SQL composition bug which impacted caching where using a ``None``
value inside of an ``in_()`` expression would bypass the usual "expanded
bind parameter" logic used by the IN construct, which allows proper caching
to take place.
Fixes: #12314
References: #12312
Change-Id: I0d2fc4e15c73407379ba368dd4ee32660fc66259
Reorganized the internals by which the `.c` collection on a
:class:`.FromClause` gets generated so that it is resilient against the
collection being accessed in concurrent fashion. An example is creating a
:class:`.Alias` or :class:`.Subquery` and accessing it as a module level
variable. This impacts the Oracle dialect which uses such module-level
global alias objects but is of general use as well.
Fixes: #12302
Change-Id: I30cb07c286affce24e2d85e49f9df5b787438d86
the :class:`.Numeric` and :class:`.Float` SQL types have been separated out
so that :class:`.Float` no longer inherits from :class:`.Numeric`; instead,
they both extend from a common mixin :class:`.NumericCommon`. This
corrects for some architectural shortcomings where numeric and float types
are typically separate, and establishes more consistency with
:class:`.Integer` also being a distinct type. The change should not have
any end-user implications except for code that may be using
``isinstance()`` to test for the :class:`.Numeric` datatype; third party
dialects which rely upon specific implementation types for numeric and/or
float may also require adjustment to maintain compatibility.
Fixes: #5252
Change-Id: Iadc841340b3d97e3eb5f7e63f0a0cc3cb4e30f74
Lysandros Nikolaou