Added support for the :class:`_sqltypes.JSON` datatype when using the
Oracle database with the oracledb dialect. JSON values are serialized and
deserialized using configurable strategies that accommodate Oracle's native
JSON type available as of Oracle 21c. Pull request courtesy Abdallah
Alhadad.
This fix also includes new dialect-level indicators for JSON support;
some attention given to issue #13213 indicates we can close that issue.
Fixes: #10375Closes: #13065
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13065
Pull-request-sha: 9a89237f4f
Change-Id: I8cbe35bc632dc9419642ddca8bf4ba9c20c0ae37
A warning is emitted when using the standalone :func:`_.sql.distinct`
function in a :func:`_sql.select` columns list outside of an aggregate
function; this function is not intended as a replacement for the use of
:meth:`.Select.distinct`. Pull request courtesy bekapono.
Fixes: #11526Closes: #13162
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13162
Pull-request-sha: c6e8be7a49
Change-Id: Ibbdd64a922c62a7a9ead566590ad854db4066565
Add the `exclude` parameter to the `Over` construct and all `.over()`
methods, enabling SQL standard frame exclusion clauses
`EXCLUDE CURRENT ROW`, `EXCLUDE GROUP`, `EXCLUDE TIES`,
`EXCLUDE NO OTHERS` in window functions.
Pull request courtesy of Varun Chawla.
Fixes#11671Closes: #13117
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13117
Pull-request-sha: 23f9d34dd8
Change-Id: I8efdb06876d5a11a9f5ed9abec2c187c6c9b7e5e
Most :class:`_sql.FromClause` subclasses are not generic on
:class:`.TypedColumns` subclasses, that can be used to type their
:attr:`_sql.FromClause.c` collection.
This applied to :class:`_schema.Table`, :class:`_sql.Join`,
:class:`_sql.Subquery`, :class:`_sql.CTE` and more.
Fixes: #13085
Change-Id: I724aca887a85c4a401df875903eda12125066680
Added a new :class:`.GenericRepr` class and :meth:`.TypeEngine.repr_struct`
method to provide better control over type representation. The
:class:`.TypeDecorator` class now properly displays its decorated type's
parameters in its ``__repr__()``, allowing introspection tools and
libraries like Alembic to better understand the structure of decorated
types, in particular for complex "schema" types such as :class:`.Enum` and
:class:`.Boolean`. Type classes can override
:meth:`.TypeEngine.repr_struct` to customize their representation
structure, and the returned :class:`.GenericRepr` object allows for
modifications such as changing the displayed class name.
Fixes: #13140
Change-Id: Ie41d249cfea56686b16c895b74ae03721207170b
Fixed issue where DDL compilation options were registered to the hard-coded
dialect name ``mysql``. This made it awkward for MySQL-derived dialects
like MariaDB, StarRocks, etc. to work with such options when different sets
of options exist for different platforms. Options are now registered under
the actual dialect name, and a fallback was added to help avoid errors when
an option does not exist for that dialect. Pull request courtesy Tiansu Yu.
Fixes: #13134Closes: #13138
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13138
Pull-request-sha: 1bc953a2a1
Change-Id: Ifa700a4e34da4d1923e9473dd8f0d2417dcfded4
the new addition of c names like "add" "extend" test
needed to be adjusted to target a single dialect
Change-Id: I94ab20314c8bc312135706ecc3d9aaad89b3caf4
Fixed issue in the :ref:`engine_insertmanyvalues` feature where using
PostgreSQL's ``ON CONFLICT`` clause with
:paramref:`_dml.Insert.returning.sort_by_parameter_order` enabled would
generate invalid SQL when the insert used an implicit sentinel (server-side
autoincrement primary key). The generated SQL would incorrectly declare a
sentinel counter column in the ``imp_sen`` table alias without providing
corresponding values in the ``VALUES`` clause, leading to a
``ProgrammingError`` indicating column count mismatch. The fix allows batch
execution mode when ``embed_values_counter`` is active, as the embedded
counter provides the ordering capability needed even with upsert behaviors,
rather than unnecessarily downgrading to row-at-a-time execution.
Fixes: #13107
Change-Id: I382472b2cf2991b520344adea5783584e27425d0
The :class:`.ColumnCollection` class hierarchy has been refactored to allow
column names such as ``add``, ``remove``, ``update``, ``extend``, and
``clear`` to be used without conflicts. :class:`.ColumnCollection` is now
an abstract base class, with mutation operations moved to
:class:`.WriteableColumnCollection` and :class:`.DedupeColumnCollection`
subclasses. The :class:`.ReadOnlyColumnCollection` exposed as attributes
such as :attr:`.Table.c` no longer includes mutation methods that raised
:class:`.NotImplementedError`, allowing these common column names to be
accessed naturally, e.g. ``table.c.add``, ``table.c.remove``,
``table.c.update``, etc.
Change-Id: I22da8314fe7c451003e948d774040d86901bbca4
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.
Fixes: #10300
Change-Id: I6d221ca9206a10c73fefad55f7ab7a6da5c81d6b
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``.
Fixes: #13006
Change-Id: Ibaa68e78da301a4f2a200f8c7abb2dc099fef37a
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://``.
Fixes: #13010
Change-Id: Ie75810f4c3af609d20da63289d2662dfa2385ca2
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.
Fixes: #12867Closes: #13003
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13003
Pull-request-sha: 1a9216062f
Change-Id: I8c55d8faae25d56ff63c9126d569c01d8ee6c7dd
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.
Fixes: #8601
Change-Id: I6a46b8f4784801f95f7980ca8ef92f1947653572
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.
Fixes: #12548
Change-Id: Ia060d1387ff452fe4f5d924f683529a22a8e1f72
this DB is extremely erratic in being able to connect. Add
a brute force connection retrier to all engines everywhere
(which for oracledb we can fortunately use their built-in feature
that also works).
This actually works and I can see it pausing under load, reconnecting,
and succeeding. the problem is that absolutely every engine everywhere
needs this routine otherwise an engine without a retrier in it will
crash. That then necessitates digging into testing_engine(),
making sure testing_engine() is used everywhere an engine that's going
to connect is used, then dealing with the fallout from that.
We also simplify some older workarounds for cx_oracle and
hack into config/provision to make oracledb seem like the primary
DBAPI for most tests.
testing_engine has been completely overhauled, making use of a new
post_configure_testing_engine() hook which moves and refines
the SQLite pool sharing and savepoint logic all into sqlite/provision.py
and also allows for cx_oracle to apply a retry event handler.
Change-Id: I4ea4c523effb878290d28b94d8925eb32fc5ae3b
one particular vector test wont run on oracle 23c free, so
just disable it.
added better skips for the rest of the vector tests and
fixed a deprecation issue.
this will be the first run on the new oracle23 on CI so we'll have to
see how this goes.
Also adjust for mariabdb12 being overly helpful with regards
to stale row updates.
as we are having trouble getting 23c to pass throug transaction
tests, i noted we have an explosion of tests due to the multiple
drivers, so this patch introduces __sparse_driver_backend__
for all tests where we want variety of
database server but there's no need to test every driver.
This should dramatically reduce the size of the test suite run
Change-Id: Ic8d3eb0a60e76b4c54c6bb4a721f90c81ede782b
Fixed issue where using the :meth:`.ColumnOperators.in_` operator with a
nested :class:`.CompoundSelect` statement (e.g. an ``INTERSECT`` of
``UNION`` queries) would raise a :class:`NotImplementedError` when the
nested compound select was the first argument to the outer compound select.
The ``_scalar_type()`` internal method now properly handles nested compound
selects.
Fixes: #12987
Change-Id: I6aa1b38863588d371bbac74b3531b99ccd5fcaec
Added new implementation for the :meth:`.Select.params` method and that of
similar statements, via a new statement-only
:meth:`.ExecutableStatement.params` method which works more efficiently and
correctly than the previous implementations available from
:class:`.ClauseElement`, by assocating the given parameter dictionary with
the statement overall rather than cloning the statement and rewriting its
bound parameters. The :meth:`_sql.ClauseElement.params` and
:meth:`_sql.ClauseElement.unique_params` methods, when called on an object
that does not implement :class:`.ExecutableStatement`, will continue to
work the old way of cloning the object, and will emit a deprecation
warning. This issue both resolves the architectural / performance
concerns of 🎫`7066` and also provides correct ORM compatibility for
functions like :func:`_orm.aliased`, reported by 🎫`12915`.
Fixes: #7066
Change-Id: I6543c7d0f4da3232b3641fb172c24c446f02f52a
Added support for the SQL ``CREATE VIEW`` statement via the new
:class:`.CreateView` DDL class. The new class allows creating database
views from SELECT statements, with support for options such as
``TEMPORARY``, ``IF NOT EXISTS``, and ``MATERIALIZED`` where supported by
the target database. Views defined with :class:`.CreateView` integrate with
:class:`.MetaData` for automated DDL generation and provide a
:class:`.Table` object for querying.
this alters the CreateTableAs commit:
* adds the ability for Table to be associated with Create and Drop
DDL constructs
* Adds CreateView variant of CreateTableAs
* Both associate themselves with Table so they take place in
create_all/create/drop_all/drop
Fixes: #181
Change-Id: If3e568d3d6a6ce19e3d15198c3fbbe06bd847c83
Added support for PostgreSQL 14+ HSTORE subscripting syntax. When connected
to PostgreSQL 14 or later, HSTORE columns now automatically use the native
subscript notation ``hstore_col['key']`` instead of the arrow operator
``hstore_col -> 'key'`` for both read and write operations. This provides
better compatibility with PostgreSQL's native HSTORE subscripting feature
while maintaining backward compatibility with older PostgreSQL versions.
as part of this change we add a new parameter to custom_op "visit_name"
which allows a custom op to refer to a specific visit method in a
dialect's compiler.
Fixes: #12948
Change-Id: Id98d333fe78e31d9c7679cb2902f1c7e458d6e11
use pyproject.toml so the options are safe from "rm .coverage*"
types of commands
omit some files
add test coverage for a big section of cache-key
Change-Id: Ia540c632d91ec09284e187e3edeb8ccf0214e1a6
Added support for the SQL ``CREATE TABLE ... AS SELECT`` construct via the
new :class:`_sql.CreateTableAs` DDL construct and the
:meth:`_sql.SelectBase.into` method. The new construct allows creating a
table directly from the results of a SELECT statement, with support for
options such as ``TEMPORARY`` and ``IF NOT EXISTS`` where supported by the
target database. Pull request courtesy Greg Jarzab.
Fixes: #4950Closes: #12860
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12860
Pull-request-sha: 7de8a109b8
Change-Id: Id9c8e4a3c520ffc61de1e48e331b6220e3d52fc9
Updated the :func:`_sql.over` clause to allow non-integer values in
:paramref:`_sql.over.range_` clause. Previously, only integer values
were allowed and any other values would lead to a failure.
To specify a non-integer value, use the new :class:`_sql.FrameClause`
construct along with the new :class:`_sql.FrameClauseType` enum to specify
the frame boundaries.
Fixes#12596.
Closes: #12695
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12695
Pull-request-sha: 8063cec65d
Change-Id: I248a938f6502d72555c005d86791c992822117d4
this isn't used internally and only allows execute(stmt.compile())
to work, which is not something I want to support.
Since people are definitely using this [1] we might want to think about
either deprectation or having a recipe for people who really want
to still do this
[1] https://github.com/sqlalchemy/sqlalchemy/discussions/11108?converting=1
Change-Id: I5f0fe800e31aac052926cebe9206763eef9a804c
Fixed an issue in :meth:`_sql.Select.join_from` where the join condition
between the left and right tables specified in the method call could be
incorrectly determined based on an intermediate table already present in
the FROM clause, rather than matching the foreign keys between the
immediate left and right arguments. The join condition is now determined by
matching primary keys between the two tables explicitly passed to
:meth:`_sql.Select.join_from`, ensuring consistent and predictable join
behavior regardless of the order of join operations or other tables present
in the query. The fix is applied to both the Core and ORM implementations
of :meth:`_sql.Select.join_from`.
Fixes: #12931
Change-Id: Id457d441ee8a1bd64a002e57a9dcfd6fc56ff15e
The ORM side of this issue can't be fixed without rewriting
.params() which will be 2.1 only, so clarify changelog
References: #12915
Change-Id: I2e524d5390241aa289786f524b6a51f39bc09876
Fixed issue where using :meth:`_sql.Select.params` to replace bound
parameters in a query could fail for some cases where the parameters
were embedded in subqueries or CTEs when ORM classes were involved,
due to issues with internal query traversal for these cases.
Fixes: #12915
Change-Id: Ib63bca786a541682f6b2144fd5dd43350411ae9d
Fixed a caching issue where :func:`_orm.with_loader_criteria` would
incorrectly reuse cached bound parameter values when used with
:class:`_sql.CompoundSelect` constructs such as :func:`_sql.union`. The
issue was caused by the cache key for compound selects not including the
execution options that are part of the :class:`_sql.Executable` base class,
which :func:`_orm.with_loader_criteria` uses to apply its criteria
dynamically. The fix ensures that compound selects and other executable
constructs properly include execution options in their cache key traversal.
Fixes: #12905
Change-Id: I24bbd96233cddabe42eb716f078eea4c84b1af05
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
Named types such as :class:`_postgresql.ENUM` and
:class:`_postgresql.DOMAIN` (as well as the dialect-agnostic
:class:`_types.Enum` version) are now more strongly associated with the
:class:`_schema.MetaData` at the top of the table hierarchy and are
de-associated with any particular :class:`_schema.Table` they may be a part
of. This better represents how PostgreSQL named types exist independently
of any particular table, and that they may be used across many tables
simultaneously. The change impacts the behavior of the "default schema"
for a named type, as well as the CREATE/DROP behavior in relationship to
the :class:`.MetaData` and :class:`.Table` construct. The change also
includes a new :class:`.CheckFirst` enumeration which allows fine grained
control over "check" queries during DDL operations, as well as that the
:paramref:`_types.SchemaType.inherit_schema` parameter is deprecated and
will emit a deprecation warning when used. See the migration notes for
full details.
Fixes: #12690
Change-Id: I9752a9d52774ae760ec3448f62fc5046c58d68f5
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
abdallah elhdad