Mike Bayer [Mon, 24 Feb 2025 20:10:54 +0000 (15:10 -0500)]
implement mysql limit() for UPDATE/DELETE DML (patch 2)
Added new construct :func:`_mysql.limit` which can be applied to any
:func:`_sql.update` or :func:`_sql.delete` to provide the LIMIT keyword to
UPDATE and DELETE. This new construct supersedes the use of the
"mysql_limit" dialect keyword argument.
Mike Bayer [Mon, 16 Dec 2024 22:29:22 +0000 (17:29 -0500)]
syntax extensions (patch 1)
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.
Mike Bayer [Tue, 4 Mar 2025 16:31:10 +0000 (11:31 -0500)]
group together with_polymorphic for single inh criteria
The behavior of :func:`_orm.with_polymorphic` when used with a single
inheritance mapping has been changed such that its behavior should match as
closely as possible to that of an equivalent joined inheritance mapping.
Specifically this means that the base class specified in the
:func:`_orm.with_polymorphic` construct will be the basemost class that is
loaded, as well as all descendant classes of that basemost class.
The change includes that the descendant classes named will no longer be
exclusively indicated in "WHERE polymorphic_col IN" criteria; instead, the
whole hierarchy starting with the given basemost class will be loaded. If
the query indicates that rows should only be instances of a specific
subclass within the polymorphic hierarchy, an error is raised if an
incompatible superclass is loaded in the result since it cannot be made to
match the requested class; this behavior is the same as what joined
inheritance has done for many years. The change also allows a single result
set to include column-level results from multiple sibling classes at once
which was not previously possible with single table inheritance.
Denis Laxalde [Tue, 4 Mar 2025 20:28:47 +0000 (15:28 -0500)]
Add type annotations to `postgresql.json`
(Same as https://github.com/sqlalchemy/sqlalchemy/pull/12384, but for `json`.)
### 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.
Mike Bayer [Thu, 27 Feb 2025 17:04:12 +0000 (12:04 -0500)]
allow control of constraint isolation w/ add/drop constraint
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.
Karol Gongola [Wed, 26 Feb 2025 10:06:16 +0000 (05:06 -0500)]
Add more `requires` to tests for easier dialect tests management
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
<!-- Describe your changes in detail -->
I am just going through starrocks dialect tests. I have figured out that adding some requires for tests may be useful also for other dialects. So this is a proposal of adding them to sqlalchemy. Please let me know if it is aligned with your approach.
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)
-->
This pull request is:
- [x] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
- [ ] A short code fix
- please include the issue number, and create an issue if none exists, which
must include a complete example of the issue. one line code fixes without an
issue and demonstration will not be accepted.
- Please include: `Fixes: #<issue number>` in the commit message
- please include tests. one line code fixes without tests will not be accepted.
- [ ] A new feature implementation
- please include the issue number, and create an issue if none exists, which must
include a complete example of how the feature would look.
- Please include: `Fixes: #<issue number>` in the commit message
- please include tests.
KingOfKaste [Thu, 20 Feb 2025 19:31:42 +0000 (14:31 -0500)]
Fix SQLite error for table with "WITHOUT ROWID" & "STRICT"
Fixed issue that omitted the comma between multiple SQLite table extension
clauses, currently ``WITHOUT ROWID`` and ``STRICT``, when both options
:paramref:`.Table.sqlite_with_rowid` and :paramref:`.Table.sqlite_strict`
were configured at their non-default settings at the same time. Pull
request courtesy david-fed.
Mike Bayer [Thu, 20 Feb 2025 17:50:25 +0000 (12:50 -0500)]
check that two CTEs aren't just annotated forms of the same thing
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.
Mike Bayer [Tue, 18 Feb 2025 15:20:32 +0000 (10:20 -0500)]
apply _propagate_attrs in _construct_for_list
Fixed issue where the "is ORM" flag of a :func:`.select` or other ORM
statement would not be propagated to the ORM :class:`.Session` based on a
multi-part operator expression alone, e.g. such as ``Cls.attr + Cls.attr +
Cls.attr`` or similar, leading to ORM behaviors not taking place for such
statements.
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.
Mingyu Park [Fri, 7 Feb 2025 19:45:26 +0000 (14:45 -0500)]
Support generic types for union and union_all
Support generic types for compound selects (:func:`_sql.union`,
:func:`_sql.union_all`, :meth:`_sql.Select.union`,
:meth:`_sql.Select.union_all`, etc) returning the type of the first select.
allenyuchen [Wed, 12 Feb 2025 17:35:58 +0000 (12:35 -0500)]
fix(AsyncResult): Fix scalar method error due to missing attribute
Fixed bug where :meth:`_asyncio.AsyncResult.scalar`,
:meth:`_asyncio.AsyncResult.scalar_one_or_none`, and
:meth:`_asyncio.AsyncResult.scalar_one` would raise an ``AttributeError``
due to a missing internal attribute. Pull request courtesy Allen Ho.
Mike Bayer [Mon, 10 Feb 2025 20:26:24 +0000 (15:26 -0500)]
reconcile #12326 and #12328
These two issues both involve ORM DML RETURNING. The looser
column inclusion rules given in #12328 then included a correlated
subquery column_property given in #12326, which does not work
in RETURNING. so re-tighten UPDATE/DELETE with a more specific
rule to cut out local mapped props that are not persisted columns,
but still allow other mapped props through without blocking them.
Mike Bayer [Sun, 9 Feb 2025 23:09:21 +0000 (18:09 -0500)]
only use _DMLReturningColFilter for "bulk insert", not other DML
Fixed bug in ORM enabled UPDATE (and theoretically DELETE) where using a
multi-table DML statement would not allow ORM mapped columns from mappers
other than the primary UPDATE mapper to be named in the RETURNING clause;
they would be omitted instead and cause a column not found exception.
Mike Bayer [Sat, 8 Feb 2025 16:38:53 +0000 (11:38 -0500)]
implement is_derived_from() for DML
Fixed bug where using DML returning such as :meth:`.Insert.returning` with
an ORM model that has :func:`_orm.column_property` constructs that contain
subqueries would fail with an internal error.
Mike Bayer [Wed, 5 Feb 2025 13:37:04 +0000 (08:37 -0500)]
remove None exception in IN
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.
Mike Bayer [Sat, 1 Feb 2025 19:39:57 +0000 (14:39 -0500)]
reorganize column collection init to be local
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.
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
Change default pool in `aiosqlite` from `NullPool` to `AsyncAdaptedQueuePool`.
This ensures consistency with pysqlite and least surprise when migrating from sync to async.
See discussion in https://github.com/sqlalchemy/sqlalchemy/discussions/12285
Non regression tested by existing tests.
### 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
- [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.
Nils Philippsen [Thu, 30 Jan 2025 13:10:55 +0000 (08:10 -0500)]
Skip mypy plugin tests if incompatible or missing
Fixes: #12287
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
<!-- Describe your changes in detail -->
This skips Mypy plugin tests if mypy is missing or an unsupported version.
### 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
- [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.
Mike Bayer [Wed, 29 Jan 2025 15:10:09 +0000 (10:10 -0500)]
support accept for chains of joineddispatchers
Fixed issue where creating an :class:`.Engine` using multiple calls to
:meth:`.Engine.execution_options` where a subsequent call involved certain
options such as ``isolation_level`` would lead to an internal error
involving event registration.
Mike Bayer [Thu, 16 Jan 2025 17:14:02 +0000 (12:14 -0500)]
set default iso to None for asyncpg pep-249 wrapper
Adjusted the asyncpg connection wrapper so that the asyncpg
``.transaction()`` call sends ``None`` for isolation_level if not otherwise
set in the SQLAlchemy dialect/wrapper, thereby allowing asyncpg to make use
of the server level setting for isolation_level in the absense of a
client-level setting. Previously, this behavior of asyncpg was blocked by a
hardcoded ``read_committed``.
Federico Caselli [Fri, 17 Jan 2025 20:16:51 +0000 (21:16 +0100)]
asyncpg: shield connection close in terminate to avoid connection leak
Added an additional ``shield()`` call within the connection terminate
process of the asyncpg driver, to mitigate an issue where terminate would
be prevented from completing under the anyio concurrency library.
CommanderKeynes [Tue, 7 Jan 2025 15:52:36 +0000 (10:52 -0500)]
Asyncpg null query fix
Adjusted the asyncpg dialect so that an empty SQL string, which is valid
for PostgreSQL server, may be successfully processed at the dialect level,
such as when using :meth:`.Connection.exec_driver_sql`. Pull request
courtesy Andrew Jackson.
Mike Bayer [Fri, 3 Jan 2025 20:40:26 +0000 (15:40 -0500)]
close unclosed sqlite result
this close is hoped to address failures that have been occurring
on github actions under python 3.13, although i am able to reproduce
the problem on other python versions as well when running
test/orm/test_events.py with the --random extension.
Mike Bayer [Fri, 3 Jan 2025 17:19:27 +0000 (12:19 -0500)]
guard against KeyError on subclass removal
Fixed issue in event system which prevented an event listener from being
attached and detached from multiple class-like objects, namely the
:class:`.sessionmaker` or :class:`.scoped_session` targets that assign to
:class:`.Session` subclasses.
Mike Bayer [Mon, 30 Dec 2024 18:17:29 +0000 (13:17 -0500)]
further fixes for _cleanup_mapped_str_annotation
Fixed issues in type handling within the ``type_annotation_map`` feature
which prevented the use of unions, using either pep-604 or ``Union``
syntaxes under future annotations mode, which contained multiple generic
types as elements from being correctly resolvable.
also adds some further tests to assert that None added into the type
map for pep695, typing.NewType etc. sets up nullability on the column
Mike Bayer [Thu, 19 Dec 2024 22:50:21 +0000 (17:50 -0500)]
fix SQLite on conflict tests
in I4d66ec1473321616a1707da324a7dfe7a61ec94e we added new tests
in the sqlite suite but we forgot to extend from fixtures.TestBase,
so these tests did not get run at all. repair tests
Mike Bayer [Wed, 18 Dec 2024 16:24:58 +0000 (11:24 -0500)]
harden typing / coercion for on conflict/on duplicate key
in 2.1 we want these structures to be cacheable, so start
by cleaning up types and adding coercions to enforce those types.
these will be more locked down in 2.1 as we will need to move
bound parameter coercion outside of compilation, but here
do some small starts and introduce in 2.0.
in one interest of cachability, a "literal_binds" that found
its way into SQLite's compiler is replaced with "literal_execute",
the difference being that the latter is cacheable. This literal
is apparently necessary to suit SQLite's query planner for
the "index criteria" portion of the on conflict clause that otherwise
can't work with a real bound parameter.
Mike Bayer [Mon, 16 Dec 2024 15:58:01 +0000 (10:58 -0500)]
harden HSTORE registration
* use the driver_connection when we register on the connection
* assert targets passed to register_hstore assert as boolean true;
psycopg docs say "if None, register globally" but looking in the
source it's actually registering globally if any false-evaluating
object is passed.
add delete limit to mysql; ensure int for update/delete limits
Added support for the ``LIMIT`` clause with ``DELETE`` for the MySQL and
MariaDB dialects, to complement the already present option for
``UPDATE``. The :meth:`.delete.with_dialect_options` method of the
`:func:`.delete` construct accepts parameters for ``mysql_limit`` and
``mariadb_limit``, allowing users to specify a limit on the number of rows
deleted. Pull request courtesy of Pablo Nicolás Estevez.
Added logic to ensure that the ``mysql_limit`` and ``mariadb_limit``
parameters of :meth:`.update.with_dialect_options` and
:meth:`.delete.with_dialect_options` when compiled to string will only
compile if the parameter is passed as an integer; a ``ValueError`` is
raised otherwise.
corrected mysql documentation for update/delete options which
must be specified using the ``with_dialect_options()`` method.
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
