From 72268e9387140df6444e9e88391ce604b9719639 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 3 Jan 2023 18:21:25 -0500 Subject: [PATCH] tutorial updates re: Core /ORM commonality updates for Insert / bulk insert, executemanyvalues, as well as beginning to describe Table / declared class more closely together, mentioning typing support. Fixed a long-standing issue where sphinx would complain about the Insert symbol being ambiguous. Change-Id: Id4cc09b9581e8fa39c9c00bc8f229636e626e9bc --- doc/build/tutorial/data_insert.rst | 49 +++++++++----------- doc/build/tutorial/metadata.rst | 17 ++++--- doc/build/tutorial/orm_data_manipulation.rst | 1 + lib/sqlalchemy/sql/selectable.py | 5 +- 4 files changed, 37 insertions(+), 35 deletions(-) diff --git a/doc/build/tutorial/data_insert.rst b/doc/build/tutorial/data_insert.rst index 36c3414c61..1aa1ca470c 100644 --- a/doc/build/tutorial/data_insert.rst +++ b/doc/build/tutorial/data_insert.rst @@ -11,22 +11,27 @@ .. _tutorial_core_insert: -Inserting Rows with Core -------------------------- +Inserting Rows with ``Insert()`` Constructs +------------------------------------------- -When using Core, a SQL INSERT statement is generated using the -:func:`_sql.insert` function - this function generates a new instance of -:class:`_sql.Insert` which represents an INSERT statement in SQL, that adds -new data into a table. +When using Core as well as within some ORM use cases, a SQL INSERT statement is +generated directly using the :func:`_sql.insert` function - this function +generates a new instance of :class:`_sql.Insert` which represents an INSERT +statement in SQL, that adds new data into a table. .. container:: orm-header - **ORM Readers** - The way that rows are INSERTed into the database from an ORM - perspective makes use of object-centric APIs on the :class:`_orm.Session` object known as the - :term:`unit of work` process, - and is fairly different from the Core-only approach described here. - The more ORM-focused sections later starting at :ref:`tutorial_inserting_orm` - subsequent to the Expression Language sections introduce this. + **ORM Readers** - + + The ORM's means of generating INSERT statements is described in + one of two ways; the most common is by using + the :term:`unit of work` process which automates the generation of + INSERT statements from object state, and is introduced + at :ref:`tutorial_inserting_orm`. The other is by using + the :class:`_sql.Insert` construct directly + in a manner very similar to that described in this section; this use + is introduced at :ref:`tutorial_orm_bulk`. + The insert() SQL Expression Construct ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ @@ -249,21 +254,13 @@ as in the example below that builds upon the example stated in The RETURNING feature is also supported by UPDATE and DELETE statements, which will be introduced later in this tutorial. - The RETURNING feature is generally [1]_ only - supported for statement executions that use a single set of bound - parameters; that is, it won't work with the "executemany" form introduced - at :ref:`tutorial_multiple_parameters`. Additionally, some dialects - such as the Oracle dialect only allow RETURNING to return a single row - overall, meaning it won't work with "INSERT..FROM SELECT" nor will it - work with multiple row :class:`_sql.Update` or :class:`_sql.Delete` - forms. - - .. [1] There is internal support for the - :mod:`_postgresql.psycopg2` dialect to INSERT many rows at once - and also support RETURNING, which is leveraged by the SQLAlchemy - ORM. However this feature has not been generalized to all dialects - and is not yet part of SQLAlchemy's regular API. + For INSERT statements, the RETURNING feature may be used + both for single-row statements as well as for statements that INSERT + multiple rows at once. Support for multiple-row INSERT with RETURNING + is dialect specific, however is supported for all the dialects + that are included in SQLAlchemy which support RETURNING. See the section + :ref:`engine_insertmanyvalues` for background on this feature. .. seealso:: diff --git a/doc/build/tutorial/metadata.rst b/doc/build/tutorial/metadata.rst index d8cc30d9b2..a1efb4b317 100644 --- a/doc/build/tutorial/metadata.rst +++ b/doc/build/tutorial/metadata.rst @@ -26,6 +26,9 @@ Core-oriented style as well as an ORM-oriented style. As with other sections, Core users can skip the ORM sections, but ORM users would best be familiar with these objects from both perspectives. + The :class:`.Table` object discussed here is declared in a more indirect + (and also fully Python-typed) way when using the ORM, however there is still + a :class:`.Table` object within the ORM's configuration. .. rst-class:: core-header @@ -35,9 +38,10 @@ Core-oriented style as well as an ORM-oriented style. Setting up MetaData with Table objects --------------------------------------- -When we work with a relational database, the basic structure that we create and -query from is known as a **table**. In SQLAlchemy, the "table" is represented -by a Python object similarly named :class:`_schema.Table`. +When we work with a relational database, the basic data-holding structure +in the database which we query from is known a **table**. +In SQLAlchemy, the database "table" may be represented +directly by a Python object similarly named :class:`_schema.Table`. To start using the SQLAlchemy Expression Language, we will want to have :class:`_schema.Table` objects constructed that represent @@ -45,7 +49,8 @@ all of the database tables we are interested in working with. Each :class:`_schema.Table` may be **declared**, meaning we explicitly spell out in source code what the table looks like, or may be **reflected**, which means we generate the object based on what's already present in a particular database. -The two approaches can also be blended in many ways. +The two approaches can also be blended in many ways, and also interact with +ORM-centric styles of table declaration. Whether we will declare or reflect our tables, we start out with a collection that will be where we place our tables known as the :class:`_schema.MetaData` @@ -177,8 +182,8 @@ In the next section we will emit the completed DDL for the ``user`` and Emitting DDL to the Database ---------------------------- -We've constructed a fairly elaborate object hierarchy to represent -two database tables, starting at the root :class:`_schema.MetaData` +We've constructed a an object structure that represents +two database tables in a database, starting at the root :class:`_schema.MetaData` object, then into two :class:`_schema.Table` objects, each of which hold onto a collection of :class:`_schema.Column` and :class:`_schema.Constraint` objects. This object structure will be at the center of most operations diff --git a/doc/build/tutorial/orm_data_manipulation.rst b/doc/build/tutorial/orm_data_manipulation.rst index 6cdbb9e3a9..3c4e55b3ef 100644 --- a/doc/build/tutorial/orm_data_manipulation.rst +++ b/doc/build/tutorial/orm_data_manipulation.rst @@ -388,6 +388,7 @@ we've made here is local to an ongoing transaction, which won't become permanent if we don't commit it. As rolling the transaction back is actually more interesting at the moment, we will do that in the next section. +.. _tutorial_orm_bulk: Bulk / Multi Row INSERT, upsert, UPDATE and DELETE diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 51f3b6a814..898b524adb 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -130,7 +130,6 @@ if TYPE_CHECKING: from .cache_key import _CacheKeyTraversalType from .compiler import SQLCompiler from .dml import Delete - from .dml import Insert from .dml import Update from .elements import KeyedColumnElement from .elements import Label @@ -3000,8 +2999,8 @@ class TableClause(roles.DMLTableRole, Immutable, NamedFromClause): c.table = self @util.preload_module("sqlalchemy.sql.dml") - def insert(self) -> Insert: - """Generate an :func:`_expression.insert` construct against this + def insert(self) -> util.preloaded.sql_dml.Insert: + """Generate an :class:`_sql.Insert` construct against this :class:`_expression.TableClause`. E.g.:: -- 2.47.2