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
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 🎫`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.
Fixes: #13067
Change-Id: I2e0d0f45ebb820d2a8f214550f1d1a500bae223b
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
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
Changed the query style for ORM queries emitted by :meth:`.Session.get` as
well as many-to-one lazy load queries to use the default labeling style,
:attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`, which normally
does not apply labels to columns in a SELECT statement. Previously, the
older style :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`
that labels columns as `<tablename>_<columname>` was used for
:meth:`.Session.get` to maintain compatibility with :class:`_orm.Query`.
The change allows the string representation of ORM queries to be less
verbose in all cases outside of legacy :class:`_orm.Query` use. Pull
request courtesy Inada Naoki.
Fixes: #12932Closes: #12926
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12926
Pull-request-sha: 6738a73f63
Change-Id: I044a54226a4fcade07adc1a3f5f60b4b3e451a1e
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
it seems to have gotten lost in our newer docs that we're looking
at the first dict only for core insert. add sections to both
INSERT tutorials explaining this difference
references: #12962
Change-Id: Id2e6c7e7db57fba6aa7838d5c3c65dea1939445a
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
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
Fixed regression in PostgreSQL dialect where JSONB subscription syntax
would generate incorrect SQL for JSONB-returning functions, causing syntax
errors. The dialect now properly wraps function calls and expressions in
parentheses when using the ``[]`` subscription syntax, generating
``(function_call)[index]`` instead of ``function_call[index]`` to comply
with PostgreSQL syntax requirements.
Fixes: #12778
Change-Id: If1238457e6bba6a933023b26519a41aa5de4dbcd
### Description
Small updates for Oracle Database dialect documentation.
- prefer python-oracledb over cx_Oracle
- Prefer the product name 'Oracle Database' over the company name 'Oracle'
- update links
- modernize
This is a refresh of existing content.
I decided the apparently now duplicated sections between cx_Oracle and python-oracledb were justified for clarity due to the inevitable differences.
This pull request is:
- [x] A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
**Have a nice day!**
Closes: #12078
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12078
Pull-request-sha: 7c4dcf9403
Change-Id: I3678976f5524ee164dc31b3122b224ae37060b71
### Description
Simplifies language use in [Getting a Connection](https://docs.sqlalchemy.org/en/20/tutorial/dbapi_transactions.html#getting-a-connection) and [Committing Changes](https://docs.sqlalchemy.org/en/20/tutorial/dbapi_transactions.html#committing-changes)
### Checklist
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.
**Have a nice day!**
Closes: #11542
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/11542
Pull-request-sha: d706e69fb6
Change-Id: I7788f2a16a5127b3c9623f7b00f06f649b04e0fb
### Description
This is my first pull request to sqlalchemy. It changes the writing style of two paragraphs in the unified tutorial [here](https://docs.sqlalchemy.org/en/20/tutorial/dbapi_transactions.html#working-with-transactions-and-the-dbapi). My goals were to.
1. Make them easier to read
2. Not change the meaning of the text.
3. Get feedback on whether this type of contribution is considered useful for sqlalchemy.
If this is a useful type of contribution, it might be good to discuss some general guidelines for me to adhere to as I continue. For instance:
- Prefer using present simple tense
- Remove superfluous words where possible
- Keep the pull requests to one or two h2 sections at a time, to make the review easier
### Checklist
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.
I'm curious to hear what you all think. **Have a nice day!**
Closes: #11541
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/11541
Pull-request-sha: 3179690e6a
Change-Id: I9b47f6ce4fd00c44c4b0e19957acf250f5e46d2f
Added new core execution option
paramref:`_engine.Connection.execution_options.preserve_rowcount`
to unconditionally save the ``rowcount`` attribute from the cursor in the
class:`_engine.Result` returned from an execution, regardless of the
statement being executed.
When this option is provided the correct value is also set when
an INSERT makes use of the "insertmanyvalues" mode, that may use
more than one actualy cursor execution.
Fixes: #10974
Change-Id: Icecef6b7539be9f0a1a02b9539864f5f163dcfbc
this includes setup.cfg changes for asyncio and
3.8 min support. it doesnt have any code changes in support
of these adjusments.
Fixes: #10197Fixes: #10357
Change-Id: Ic4569c770d1b893a067a9a5dfe13a6e28aaf47fa
Comments by Mike <mike_mp@zzzcomputing.com>:
"Referred towards" is not correct English and can be replaced directly
with "referred to". However, this then introduces a dangling
preposition to sentences which I don't think is appropriate for this
style of writing. So instead, use phrases like "known as",
"references", "to which X refers".
To help me identify dangling prepositions I made use of ChatGPT,
here's the log of how that transpired:
https://chat.openai.com/share/60d42ff4-c1ac-4232-893a-415c2b6d7320
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Closes: #10210
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/10210
Pull-request-sha: 5d30e79c14
Change-Id: Ib6a98e5be441f5b25d3929a2efb7d873bcfef98e
Repaired a major shortcoming which was identified in the
:ref:`engine_insertmanyvalues` performance optimization feature first
introduced in the 2.0 series. This was a continuation of the change in
2.0.9 which disabled the SQL Server version of the feature due to a
reliance in the ORM on apparent row ordering that is not guaranteed to take
place. The fix applies new logic to all "insertmanyvalues" operations,
which takes effect when a new parameter
:paramref:`_dml.Insert.returning.sort_by_parameter_order` on the
:meth:`_dml.Insert.returning` or :meth:`_dml.UpdateBase.return_defaults`
methods, that through a combination of alternate SQL forms, direct
correspondence of client side parameters, and in some cases downgrading to
running row-at-a-time, will apply sorting to each batch of returned rows
using correspondence to primary key or other unique values in each row
which can be correlated to the input data.
Performance impact is expected to be minimal as nearly all common primary
key scenarios are suitable for parameter-ordered batching to be
achieved for all backends other than SQLite, while "row-at-a-time"
mode operates with a bare minimum of Python overhead compared to the very
heavyweight approaches used in the 1.x series. For SQLite, there is no
difference in performance when "row-at-a-time" mode is used.
It's anticipated that with an efficient "row-at-a-time" INSERT with
RETURNING batching capability, the "insertmanyvalues" feature can be later
be more easily generalized to third party backends that include RETURNING
support but not necessarily easy ways to guarantee a correspondence
with parameter order.
Fixes: #9618
References: #9603
Change-Id: I1d79353f5f19638f752936ba1c35e4dc235a8b7c
a new user spent many days misled by this paragraph thinking
they were required to use reflection for an existing database.
Change-Id: I4c6757b931481db7a8d4202334382143e1491935
* Make sure we have blue borders for all sections
* rewrite "blue border" text, refer to textual means of determining
subject matter for a section; "blue borders" are not a primary
source of information
* Add some more intro text that was missing
Change-Id: I4d599e13d23bad8bb3c199a11afb53e3e9100c59
References: #9450
Missing a word on the page `Working with Database Metadata`.
First paragraph under section 'Setting up MetaData with Table objects'.
"...the database which we query from is know [as] a table."
Co-authored-by: markie tee <cassette.head@gmail.com>
### Description
<!-- Describe your changes in detail -->
Fixes#9168
This PR replaces common occurrences of [PEP 585](https://peps.python.org/pep-0585/) style type annotations with annotations compatible with older versions of Python.
I searched for instances of the following supported types from the PEP and replaced with their legacy typing couterparts.
* tuple # typing.Tuple
* list # typing.List
* dict # typing.Dict
* set # typing.Set
* frozenset # typing.FrozenSet
* type # typing.Type
```
grep -r "list\[.*\]" ./build --exclude-dir="./build/venv/*" --exclude-dir="./build/output/*" --exclude="changelog_[0-9]*\.rst"
```
I excluded changelog files from being altered, I think some of these could be changed if necessary but others are likely to require manual checking as the change may target the new typing style specifically.
For any examples that included imports, I tried to ensure that the correct typing imports were included and properly ordered.
### 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 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: #9198
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/9198
Pull-request-sha: 05ad4651b5
Change-Id: I41b93b3dee85f9fe00cfbb3d3eb011212795de29
try to keep the wordiness down here, using sidebars
and topics for non-essential information. Sphinx seems
to read out attrs from under TYPE_CHECKING sections now
so link out the attrs in DeclarativeBase w/ docstrings,
not sure why we didn't think of this earlier. looks great
Change-Id: Ib2e07e3606185998561c2d77b2564fd3eddb4d75
Federico Caselli