Added :paramref:`.selectinload.chunksize` parameter to :func`.selectinload`
allowing users to configure the number of primary keys sent per IN clause
when loading reltaionships. Pull request courtesy bekapono.
Fixes: #11450Closes: #13235
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13235
Pull-request-sha: 360585a48c
Change-Id: Id09776b7ba53c630a780f128fc67dfdc085a4062
Add OrderByRole to acceptable arguments for order_by
Fixes: #13248
### Description
Add OrderByRole to acceptable arguments for order_by to solve typing regression
### 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
- [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: #13251
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13251
Pull-request-sha: 267e33f311
Change-Id: I0778cdecfebfb12bafc5b6027763ae5f5dcf02a2
Narrowed the scope of the internal workaround for MySQL bugs `#88718
<https://bugs.mysql.com/bug.php?id=88718>`_ and `#96365
<https://bugs.mysql.com/bug.php?id=96365>`_ so that it is only applied
where needed: MySQL 8.0.1 through 8.0.13 (where bug 88718 is present), and
on systems with ``lower_case_table_names=2`` (where bug 96365 applies,
typically macOS). Previously the workaround was applied unconditionally
for all MySQL 8.0+ versions, which caused a ``KeyError`` during foreign key
reflection when the database user lacked SELECT privileges on referred
tables.
Fixes: #13243
Change-Id: I7c29f67d1653c5cd32f29e098f038fea1d56117b
Improve handling of two phase transaction identifiers for PostgreSQL
when the identifier is provided by the user.
As part of this change the psycopg dialect was updated to use the DBAPI
two phase transaction API instead of executing the SQL directly.
Fixes: #13229
Change-Id: If8301a7253b4a0c88e5323c9a052c3a9fa258780
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
Fixed issue where using chained loader options such as
:func:`_orm.selectinload` after :func:`_orm.joinedload` with
:meth:`_orm.PropComparator.of_type` for a polymorphic relationship would
not properly apply the chained loader option. The loader option is now
correctly applied when using a call such as
``joinedload(A.b.of_type(poly)).selectinload(poly.SubClass.c)`` to eagerly
load related objects.
Fixes: #13209
Change-Id: I2d14838f1b1a9a2b18dc52137910dab0bccf0dd5
Fixed issue where using :meth:`_orm.Load.options` to apply a chained loader
option such as :func:`_orm.joinedload` or :func:`_orm.selectinload` with
:meth:`_orm.PropComparator.of_type` for a polymorphic relationship would
not generate the necessary clauses for the polymorphic subclasses. The
polymorphic loading strategy is now correctly propagated when using a call
such as ``joinedload(A.b).options(joinedload(B.c.of_type(poly)))`` to match
the behavior of direct chaining e.g.
``joinedload(A.b).joinedload(B.c.of_type(poly))``.
Fixes: #13202
Change-Id: I7b2ce2dd10a7f8583ff99495b0a65fa1a895ee29
Fixes#13176.
`Session.get()` checks `with_for_update is None` to decide whether to look up the identity map. Passing `with_for_update=False` fails this check and always hits the database, even though `ForUpdateArg._from_argument` already treats `False` and `None` identically (both return `None`). Changed to `with_for_update in (None, False)` to match.
Closes: #13199
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13199
Pull-request-sha: c639b87238
Change-Id: I0584873f46099afadcdd760c0a267ae4d30528eb
The ``metadata``, ``type_annotation_map``, or ``registry`` can now be
set up in a declarative base also via a mixin class, not only by
directly setting them on the subclass like before.
The declarative class setup now uses ``getattr()`` to look for these
attributes, instead of relying only on the class ``__dict__``.
Fixes: #13198
Change-Id: I032f0064aefb36e5c5a2f1186d7edd30d2863047
Fixed issue where chained :func:`_orm.joinedload` options would not be
applied correctly when the final relationship in the chain is declared on a
base mapper and accessed through a subclass mapper in a
:func:`_orm.with_polymorphic` query. The path registry now correctly
computes the natural path when a property declared on a base class is
accessed through a path containing a subclass mapper, ensuring the loader
option can be located during query compilation.
Fixes: #13193
Change-Id: I9ec83a0f184caed2bf6dd087b20c3538d6c23597
Fixed regression from version 2.0.42 caused by 🎫`12654` where the
updated column reflection query would receive SQL Server "type alias" names
for special types such as ``sysname``, whereas previously the base name
would be received (e.g. ``nvarchar`` for ``sysname``), leading to warnings
that such types could not be reflected and resulting in :class:`.NullType`,
rather than the expected :class:`.NVARCHAR` for a type like ``sysname``.
The column reflection query now joins ``sys.types`` a second time to look
up the base type when the user type name is not present in
:attr:`.MSDialect.ischema_names`, and both names are checked in
:attr:`.MSDialect.ischema_names` for a match. Pull request courtesy Carlos
Serrano.
Fixes: #13181Fixes: #13182Closes: #13178
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13178
Pull-request-sha: be5c385059
Change-Id: I7fe86b80dfa45b208f7d97003ee5b1df3f07bfe7
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
Issue: https://github.com/sqlalchemy/sqlalchemy/issues/10902
Hello! This is my first PR here, so please let me know what I may have missed in terms of having a valuable contribution. I was looking through issues to grab an easy first one, and found this. Looks like someone else was going to have a go at it, but never did.
I simply added a small change to the FK regex in for Postgres that allows anything not quotes alongside escaped double quotes. Test is included for the scenario mentioned in the issue. Alongside that, I didn't see a test for general quoted strings, so I added another one that includes spaces and dashes, in my experience common things to be used inside quoted identifiers.
A manual test as well:
DB setup:
```
austin_test_bug=# CREATE TABLE """test_parent_table-quoted""" (id SERIAL PRIMARY KEY, val INTEGER);
CREATE TABLE
austin_test_bug=# CREATE TABLE test_child_table_ref_quoted (id SERIAL, parent INTEGER, CONSTRAINT fk_parent FOREIGN KEY (parent) REFERENCES """test_parent_table-quoted"""(id));
CREATE TABLE
austin_test_bug=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------------------------------+----------+----------+-------------+---------------+------------+-------------
public | "test_parent_table-quoted" | table | postgres | permanent | heap | 0 bytes |
public | "test_parent_table-quoted"_id_seq | sequence | postgres | permanent | | 8192 bytes |
public | test_child_table_ref_quoted | table | postgres | permanent | heap | 0 bytes |
public | test_child_table_ref_quoted_id_seq | sequence | postgres | permanent | | 8192 bytes |
(4 rows)
```
And the python:
```
>>> from sqlalchemy import create_engine, inspect
>>> engine = create_engine('postgresql://scott:tiger@localhost:5432/austin_test_bug')
>>> connection = engine.connect()
>>> inspect(connection).get_multi_foreign_keys()
{(None, '"test_parent_table-quoted"'): [], (None, 'test_child_table_ref_quoted'): [{'name': 'fk_parent', 'constrained_columns': ['parent'], 'referred_schema': None, 'referred_table': '"test_parent_table-quoted"', 'referred_columns': ['id'], 'options': {}, 'comment': None}]}
```
### 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.
**Have a nice day!**
Fixes: #10902Closes: #13179
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13179
Pull-request-sha: 8890dc3250
Change-Id: I185c2cb062740551ab931368de602054eb5a4acd
Remove warning for SQL Server dialect when a new version is detected.
The warning was originally added more than 15 years ago due to an unexpected
value returned when using an old version of FreeTDS.
The assumption is that since then the issue has been resolved, so make the
SQL Server dialect behave like the other ones that don't have an upper bound
check on the version number.
This effectively reverts 64d92c836c
that was added for #1825Fixes: #13185
Change-Id: I79c6951b2fbb6da2caefca97dc3e1b9d8f48f0ab
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
Ensure the _FunctionGenerator method do not shadow the function class
of the same name
Fixed a typing issue where the typed members of :data:`.func` would return
the appropriate class of the same name, however this creates an issue for
typecheckers such as Zuban and pyrefly that assume :pep:`749` style
typechecking even if the file states that it's a :pep:`563` file; they see
the returned name as indicating the method object and not the class object.
These typecheckers are actually following along with an upcoming test
harness that insists on :pep:`749` style name resolution for this case
unconditionally. Since :pep:`749` is the way of the future regardless,
differently-named type aliases have been added for these return types.
Fixes: #13167
Change-Id: If58a3858001c78ab21b2ed343205dfd9ce868576
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
<!-- Provide a general summary of your proposed changes in the Title field above -->
### Description
<!-- Describe your changes in detail -->
According to [the documentation](https://docs.sqlalchemy.org/en/21/core/metadata.html#sqlalchemy.schema.MetaData.params.naming_convention), the values associated with user-defined “token” keys in `naming_convention` should be callables of the form `fn(constraint, table)`, which accepts the constraint/index object and Table. However, the type alias `_NamingSchemaCallable` accepts only constraint in the first argument. I propose to update `_NamingSchemaCallable` to accept also an index.
### Example
```python
import sqlalchemy
def include_0_N_name(
schema_item: sqlalchemy.Index | sqlalchemy.Constraint,
table: sqlalchemy.Table,
) -> str:
tokens = []
for column in schema_item.dialect_options.get('postgresql', {}).get('include', []):
tokens.append("_")
tokens.append(column.name)
return "".join(tokens)
metadata = sqlalchemy.MetaData(
naming_convention={
"include_0_N_name": include_0_N_name,
"ix": "%(table_name)s_%(column_0_N_name)s%(include_0_N_name)s_idx",
"uq": "%(table_name)s_%(column_0_N_name)s%(include_0_N_name)s_key",
"fk": "%(table_name)s_%(column_0_N_name)s%(include_0_N_name)s_fkey",
"pk": "%(table_name)s_pkey",
},
)
```
### 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.
Closes: #13161
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13161
Pull-request-sha: cf53bd7229
Change-Id: I0daf8a6eeb458aaa09f3392e00d98c27dbf8ca3c
Improved typing of :class:`_sqltypes.JSON` as well as dialect specific
variants like :class:`_postgresql.JSON` to include generic capabilities, so
that the types may be parameterized to indicate any specific type of
contents expected, e.g. ``JSONB[list[str]]()``.
Also types HSTORE
Fixes: #13131
Change-Id: Ia089ba4e3cebf6339a5420b2923cd267c4e6891a
Added support for the ``mssql-python`` driver, Microsoft's official Python
driver for SQL Server.
Fixes: #12869
Change-Id: I9ce0fef1dd1105c20833cc6a46f24ac9580c4b39
Enhanced the ``aioodbc`` dialect to expose the ``fast_executemany``
attribute of the pyodbc cursor. This allows the ``fast_executemany``
parameter to work with the ``mssql+aioodbc`` dialect. Pull request
courtesy Georg Sieber.
Fixes: #13152Closes: #13151
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13151
Pull-request-sha: c25aa581af
Change-Id: I6f27600b509f4881769ecca944fc0939e26626e6
Fixed issue in Oracle dialect where the :class:`_oracle.RAW` datatype would
not reflect the length parameter. Pull request courtesy Daniel Sullivan.
Fixes: #13150Closes: #13149
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13149
Pull-request-sha: f7488faaf8
Change-Id: I7db0219b6484317de15203b42f9ca5a84fe36bb7
Fixed a critical issue in :class:`.Engine` where connections created in
conjunction with the :meth:`.ConnectionEvents.do_connect` event listeners
would receive shared, mutable collections for the connection arguments,
leading to a variety of potential issues including unlimited growth of the
argument list as well as elements within the parameter dictionary being
shared among concurrent connection calls. In particular this could impact
do_connect routines making use of complex mutable authentication
structures.
Fixes: #13144
Change-Id: I1549dae36e8e7e6cf50fdaf796659b53e7b78234
Fixes#13128
### Description
`Query.__getitem__` and `AppenderQuery.__getitem__` previously returned Union[_T, List[_T]] for all inputs, making the return type inaccurate.
Added `@overload` signatures so that integer index returns _T and slice returns List[_T].
This pull request is:
- [x] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
Closes: #13142
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13142
Pull-request-sha: 9ba1f0145d
Change-Id: Ib37ab63d3d844491c34cc5ccfc4efc1591a1878c
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
### Description
`WeakSequence.__getitem__` catches `KeyError` but the internal `_storage` is a `list`, which raises `IndexError` for out-of-range access. This means the `except KeyError` handler never executes, and the custom error message is never shown.
### Current behavior
```python
def __getitem__(self, index):
try:
obj = self._storage[index] # _storage is a list
except KeyError: # lists don't raise KeyError
raise IndexError("Index %s out of range" % index)
else:
return obj()
```
On an out-of-range index, the raw `IndexError` from list access propagates directly (e.g., `list index out of range`) instead of the intended custom message.
### Fix
Changed `except KeyError` to `except IndexError` so the handler actually catches the exception raised by list indexing.
Closes: #13136
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13136
Pull-request-sha: ceb8f9fe46
Change-Id: Ia36c2b9b0f3aec97624bcd2a9e49f43b9ed786d9
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
Fixed issue where :meth:`_postgresql.Insert.on_conflict_do_update`
using parametrized bound parameters in the ``set_`` clause would fail
when used with executemany batching. For dialects that use the
``use_insertmanyvalues_wo_returning`` optimization (psycopg2),
insertmanyvalues is now disabled when there is an ON CONFLICT clause.
For cases with RETURNING, row-at-a-time mode is used when the SET
clause contains parametrized bindparams (bindparams that receive
values from the parameters dict), ensuring each row's parameters are
correctly applied. ON CONFLICT statements using expressions like
``excluded.<column>`` continue to batch normally.
Fixed issue where :meth:`_sqlite.Insert.on_conflict_do_update`
using parametrized bound parameters in the ``set_`` clause would fail
when used with executemany batching. Row-at-a-time mode is now used
for ON CONFLICT statements with RETURNING that contain parametrized
bindparams, ensuring each row's parameters are correctly applied. ON
CONFLICT statements using expressions like ``excluded.<column>``
continue to batch normally.
Fixes: #13130
Change-Id: I0c5a9142401c745d38e58d071c16e53610f9bfea
The connection object returned by :meth:`_engine.Engine.raw_connection`
now supports the context manager protocol, automatically returning the
connection to the pool when exiting the context.
Fixes: #13116
Change-Id: I51eb1fd61b772368f12a787e5f60db153a839e70
Fixed issue where :meth:`_postgresql.Insert.on_conflict_do_update`
as well as :meth:`_sqlite.Insert.on_conflict_do_update`
parameters were not respecting compilation options such as
``literal_binds=True``.
Pull request courtesy Loïc Simon.
Fixes: #13110Closes: #13111
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/13111
Pull-request-sha: 9ca251610b
Change-Id: Ice21e508210d682098104c78e77bad8d24e6c93f
Léo Gallot