From afb26d79d7b9256ee26b4d3b8550f7088f4b6249 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 11 Nov 2020 10:42:09 -0500 Subject: [PATCH] Remove misleading correlation examples add links to tutorial docs Fixes: #5694 Change-Id: I10a8e3f46a115945ded36d4ee59165c056c10f7a --- doc/build/core/tutorial.rst | 1 + doc/build/tutorial/data.rst | 181 ++++++++++++++++++++---------------- lib/sqlalchemy/sql/dml.py | 78 +++++++--------- 3 files changed, 136 insertions(+), 124 deletions(-) diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst index 1248e00a38..e927a77cea 100644 --- a/doc/build/core/tutorial.rst +++ b/doc/build/core/tutorial.rst @@ -2390,6 +2390,7 @@ used to achieve this: COMMIT {stop} +.. _tutorial_1x_correlated_updates: Correlated Updates ------------------ diff --git a/doc/build/tutorial/data.rst b/doc/build/tutorial/data.rst index 6238e5e1f5..27a21b0978 100644 --- a/doc/build/tutorial/data.rst +++ b/doc/build/tutorial/data.rst @@ -1212,6 +1212,8 @@ from a Core-centric perspective. use are discussed in the sections :ref:`tutorial_orm_updating` and :ref:`tutorial_orm_deleting`. +.. _tutorial_core_update: + The update() SQL Expression Construct ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ @@ -1279,85 +1281,93 @@ that literal values would normally go: COMMIT{stop} -Other techniques which may be applied to UPDATE include: - -* **Correlated Updates**: a :ref:`correlated subquery ` - may be used anywhere a column expression might be - placed:: - - >>> scalar_subq = ( - ... select(address_table.c.email_address). - ... where(address_table.c.user_id == user_table.c.id). - ... order_by(address_table.c.id). - ... limit(1). - ... scalar_subquery() - ... ) - >>> update_stmt = update(user_table).values(fullname=scalar_subq) - >>> print(update_stmt) - {opensql}UPDATE user_account SET fullname=(SELECT address.email_address - FROM address - WHERE address.user_id = user_account.id ORDER BY address.id - LIMIT :param_1) - - .. - - -* **UPDATE..FROM**: Some databases such as PostgreSQL and MySQL support a syntax - "UPDATE FROM" where additional tables may be stated in the FROM clause. - This syntax will be generated implicitly when additional tables are located - in the WHERE clause of the statement:: - - >>> update_stmt = ( - ... update(user_table). - ... where(user_table.c.id == address_table.c.user_id). - ... where(address_table.c.email_address == 'patrick@aol.com'). - ... values(fullname='Pat') - ... ) - >>> print(update_stmt) - {opensql}UPDATE user_account SET fullname=:fullname FROM address - WHERE user_account.id = address.user_id AND address.email_address = :email_address_1 - - .. -* **UPDATE..FROM updating multiple tables**: this is a MySQL specific syntax which - requires we refer to :class:`_schema.Table` objects in the VALUES - clause in order to refer to additional tables:: +Other techniques which may be applied to UPDATE include: - >>> update_stmt = ( - ... update(user_table). - ... where(user_table.c.id == address_table.c.user_id). - ... where(address_table.c.email_address == 'patrick@aol.com'). - ... values( - ... { - ... user_table.c.fullname: "Pat", - ... address_table.c.email_address: "pat@aol.com" - ... } - ... ) - ... ) - >>> from sqlalchemy.dialects import mysql - >>> print(update_stmt.compile(dialect=mysql.dialect())) - {opensql}UPDATE user_account, address - SET address.email_address=%s, user_account.fullname=%s - WHERE user_account.id = address.user_id AND address.email_address = %s +.. _tutorial_correlated_updates: - .. +Correlated Updates +~~~~~~~~~~~~~~~~~~ -* **Parameter Ordered Updates**: Another MySQL-only behavior is that the order - of parameters in the SET clause of an UPDATE actually impacts the evaluation - of each expression. For this use case, the :meth:`_sql.Update.ordered_values` - method accepts a sequence of tuples so that this order may be controlled [1]_:: +An UPDATE statement can make use of rows in other tables by using a +:ref:`correlated subquery `. A subuqery may be used +anywhere a column expression might be placed:: + + >>> scalar_subq = ( + ... select(address_table.c.email_address). + ... where(address_table.c.user_id == user_table.c.id). + ... order_by(address_table.c.id). + ... limit(1). + ... scalar_subquery() + ... ) + >>> update_stmt = update(user_table).values(fullname=scalar_subq) + >>> print(update_stmt) + {opensql}UPDATE user_account SET fullname=(SELECT address.email_address + FROM address + WHERE address.user_id = user_account.id ORDER BY address.id + LIMIT :param_1) + + +.. _tutorial_update_from: + +UPDATE..FROM +~~~~~~~~~~~~~ + +Some databases such as PostgreSQL and MySQL support a syntax "UPDATE FROM" +where additional tables may be stated directly in a special FROM clause. This +syntax will be generated implicitly when additional tables are located in the +WHERE clause of the statement:: + + >>> update_stmt = ( + ... update(user_table). + ... where(user_table.c.id == address_table.c.user_id). + ... where(address_table.c.email_address == 'patrick@aol.com'). + ... values(fullname='Pat') + ... ) + >>> print(update_stmt) + {opensql}UPDATE user_account SET fullname=:fullname FROM address + WHERE user_account.id = address.user_id AND address.email_address = :email_address_1 + + +There is also a MySQL specific syntax that can UPDATE multiple tables. This +requires we refer to :class:`_schema.Table` objects in the VALUES clause in +order to refer to additional tables:: + + >>> update_stmt = ( + ... update(user_table). + ... where(user_table.c.id == address_table.c.user_id). + ... where(address_table.c.email_address == 'patrick@aol.com'). + ... values( + ... { + ... user_table.c.fullname: "Pat", + ... address_table.c.email_address: "pat@aol.com" + ... } + ... ) + ... ) + >>> from sqlalchemy.dialects import mysql + >>> print(update_stmt.compile(dialect=mysql.dialect())) + {opensql}UPDATE user_account, address + SET address.email_address=%s, user_account.fullname=%s + WHERE user_account.id = address.user_id AND address.email_address = %s + + +Parameter Ordered Updates +~~~~~~~~~~~~~~~~~~~~~~~~~~ - >>> update_stmt = ( - ... update(some_table). - ... ordered_values( - ... (some_table.c.y, 20), - ... (some_table.c.x, some_table.c.y + 10) - ... ) - ... ) - >>> print(update_stmt) - {opensql}UPDATE some_table SET y=:y, x=(some_table.y + :y_1) +Another MySQL-only behavior is that the order of parameters in the SET clause +of an UPDATE actually impacts the evaluation of each expression. For this use +case, the :meth:`_sql.Update.ordered_values` method accepts a sequence of +tuples so that this order may be controlled [1]_:: - .. + >>> update_stmt = ( + ... update(some_table). + ... ordered_values( + ... (some_table.c.y, 20), + ... (some_table.c.x, some_table.c.y + 10) + ... ) + ... ) + >>> print(update_stmt) + {opensql}UPDATE some_table SET y=:y, x=(some_table.y + :y_1) .. [1] While Python dictionaries are `guaranteed to be insert ordered @@ -1367,6 +1377,7 @@ Other techniques which may be applied to UPDATE include: measure of clarity of intent when it is essential that the SET clause of a MySQL UPDATE statement proceed in a specific way. +.. _tutorial_deletes: The delete() SQL Expression Construct ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ @@ -1388,19 +1399,25 @@ allowing for a RETURNING variant. >>> print(stmt) {opensql}DELETE FROM user_account WHERE user_account.name = :name_1 + +.. _tutorial_multi_table_deletes: + +Multiple Table Deletes +~~~~~~~~~~~~~~~~~~~~~~ + Like :class:`_sql.Update`, :class:`_sql.Delete` supports the use of correlated subqueries in the WHERE clause as well as backend-specific multiple table syntaxes, such as ``DELETE FROM..USING`` on MySQL:: - >>> delete_stmt = ( - ... delete(user_table). - ... where(user_table.c.id == address_table.c.user_id). - ... where(address_table.c.email_address == 'patrick@aol.com') - ... ) - >>> from sqlalchemy.dialects import mysql - >>> print(delete_stmt.compile(dialect=mysql.dialect())) - {opensql}DELETE FROM user_account USING user_account, address - WHERE user_account.id = address.user_id AND address.email_address = %s + >>> delete_stmt = ( + ... delete(user_table). + ... where(user_table.c.id == address_table.c.user_id). + ... where(address_table.c.email_address == 'patrick@aol.com') + ... ) + >>> from sqlalchemy.dialects import mysql + >>> print(delete_stmt.compile(dialect=mysql.dialect())) + {opensql}DELETE FROM user_account USING user_account, address + WHERE user_account.id = address.user_id AND address.email_address = %s Getting Affected Row Count from UPDATE, DELETE ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index 9b9a6153b7..5726cddc02 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -769,8 +769,6 @@ class Insert(ValuesBase): The :class:`_expression.Insert` object is created using the :func:`_expression.insert()` function. - .. note - the __init__() method delivers the docstring for this object - """ __visit_name__ = "insert" @@ -987,6 +985,31 @@ class DMLWhereBase(object): """Return a new construct with the given expression(s) added to its WHERE clause, joined to the existing clause via AND, if any. + Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where` + support multiple-table forms, including database-specific + ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that + don't have multiple-table support, a backend agnostic approach + to using multiple tables is to make use of correlated subqueries. + See the linked tutorial sections below for examples. + + .. seealso:: + + **1.x Tutorial Examples** + + :ref:`tutorial_1x_correlated_updates` + + :ref:`multi_table_updates` + + :ref:`multi_table_deletes` + + **2.0 Tutorial Examples** + + :ref:`tutorial_correlated_updates` + + :ref:`tutorial_update_from` + + :ref:`tutorial_multi_table_deletes` + """ for criterion in list(whereclause): @@ -1039,7 +1062,8 @@ class DMLWhereBase(object): class Update(DMLWhereBase, ValuesBase): """Represent an Update construct. - .. note - the __init__() method delivers the docstring for this object + The :class:`_expression.Update` object is created using the + :func:`_expression.update()` function. """ @@ -1118,23 +1142,9 @@ class Update(DMLWhereBase, ValuesBase): table to be updated. :param whereclause: Optional SQL expression describing the ``WHERE`` - condition of the ``UPDATE`` statement. Modern applications - may prefer to use the generative :meth:`~Update.where()` - method to specify the ``WHERE`` clause. - - The WHERE clause can refer to multiple tables. - For databases which support this, an ``UPDATE FROM`` clause will - be generated, or on MySQL, a multi-table update. The statement - will fail on databases that don't have support for multi-table - update statements. A SQL-standard method of referring to - additional tables in the WHERE clause is to use a correlated - subquery:: - - users.update().values(name='ed').where( - users.c.name==select(addresses.c.email_address).\ - where(addresses.c.user_id==users.c.id).\ - scalar_subquery() - ) + condition of the ``UPDATE`` statement; is equivalent to using the + more modern :meth:`~Update.where()` method to specify the ``WHERE`` + clause. :param values: Optional dictionary which specifies the ``SET`` conditions of the @@ -1291,7 +1301,8 @@ class Update(DMLWhereBase, ValuesBase): class Delete(DMLWhereBase, UpdateBase): """Represent a DELETE construct. - .. note - the __init__() method delivers the docstring for this object + The :class:`_expression.Delete` object is created using the + :func:`_expression.delete()` function. """ @@ -1349,27 +1360,10 @@ class Delete(DMLWhereBase, UpdateBase): :param table: The table to delete rows from. - :param whereclause: A :class:`_expression.ClauseElement` - describing the ``WHERE`` - condition of the ``DELETE`` statement. Note that the - :meth:`~Delete.where()` generative method may be used instead. - - The WHERE clause can refer to multiple tables. - For databases which support this, a ``DELETE..USING`` or similar - clause will be generated. The statement - will fail on databases that don't have support for multi-table - delete statements. A SQL-standard method of referring to - additional tables in the WHERE clause is to use a correlated - subquery:: - - users.delete().where( - users.c.name==select(addresses.c.email_address).\ - where(addresses.c.user_id==users.c.id).\ - scalar_subquery() - ) - - .. versionchanged:: 1.2.0 - The WHERE clause of DELETE can refer to multiple tables. + :param whereclause: Optional SQL expression describing the ``WHERE`` + condition of the ``DELETE`` statement; is equivalent to using the + more modern :meth:`~Delete.where()` method to specify the ``WHERE`` + clause. .. seealso:: -- 2.47.3