From d4b8b41832267918e74c114c26def5b38d15e9e2 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 11 Oct 2022 15:29:15 -0400 Subject: [PATCH] update executemany for new features also fixes issue in format_docs_code which didn't work with pre-commit for more than one file. will backport Fixes: #8597 Change-Id: I21b2625514987b1cd90f7c00f06e72e57e257390 --- doc/build/glossary.rst | 6 ++- doc/build/orm/mapping_api.rst | 2 +- doc/build/tutorial/dbapi_transactions.rst | 51 +++++++++++++---------- tools/format_docs_code.py | 14 +++++-- 4 files changed, 43 insertions(+), 30 deletions(-) diff --git a/doc/build/glossary.rst b/doc/build/glossary.rst index 7210f1a156..4baf6abcca 100644 --- a/doc/build/glossary.rst +++ b/doc/build/glossary.rst @@ -237,8 +237,10 @@ Glossary This term refers to a part of the :pep:`249` DBAPI specification indicating a single SQL statement that may be invoked against a database connection with multiple parameter sets. The specific - method is known as ``cursor.executemany()``, and it has many - behavioral differences in comparison to the ``cursor.execute()`` + method is known as + `cursor.executemany() `_, + and it has many behavioral differences in comparison to the + `cursor.execute() `_ method which is used for single-statement invocation. The "executemany" method executes the given SQL statement multiple times, once for each set of parameters passed. The general rationale for using diff --git a/doc/build/orm/mapping_api.rst b/doc/build/orm/mapping_api.rst index 921291a170..b6116bb9b3 100644 --- a/doc/build/orm/mapping_api.rst +++ b/doc/build/orm/mapping_api.rst @@ -66,7 +66,7 @@ Class Mapping API class MySubClass(MyClass): - """""" + """ """ # ... diff --git a/doc/build/tutorial/dbapi_transactions.rst b/doc/build/tutorial/dbapi_transactions.rst index 780b3166b7..68fc84bcbc 100644 --- a/doc/build/tutorial/dbapi_transactions.rst +++ b/doc/build/tutorial/dbapi_transactions.rst @@ -355,12 +355,12 @@ Sending Multiple Parameters In the example at :ref:`tutorial_committing_data`, we executed an INSERT statement where it appeared that we were able to INSERT multiple rows into the -database at once. For statements that **operate upon data, but do not return -result sets**, namely :term:`DML` statements such as "INSERT" which don't -include a phrase like "RETURNING", we can send **multi params** to the +database at once. For statements :term:`DML` statements such as "INSERT", +"UPDATE" and "DELETE", we can send **multiple parameter sets** to the :meth:`_engine.Connection.execute` method by passing a list of dictionaries -instead of a single dictionary, thus allowing the single SQL statement to -be invoked against each parameter set individually: +instead of a single dictionary, which indicates that the single SQL statement +should be invoked multiple times, once for each parameter set. This style +of execution is known as :term:`executemany`: .. sourcecode:: pycon+sql @@ -376,24 +376,29 @@ be invoked against each parameter set individually: COMMIT -Behind the scenes, the :class:`_engine.Connection` objects uses a DBAPI feature -known as `cursor.executemany() -`_. This method performs the -equivalent operation of invoking the given SQL statement against each parameter -set individually. The DBAPI may optimize this operation in a variety of ways, -by using prepared statements, or by concatenating the parameter sets into a -single SQL statement in some cases. Some SQLAlchemy dialects may also use -alternate APIs for this case, such as the :ref:`psycopg2 dialect for PostgreSQL -` which uses more performant APIs -for this use case. - -.. tip:: you may have noticed this section isn't tagged as an ORM concept. - That's because the "multiple parameters" use case is **usually** used - for INSERT statements, which when using the ORM are invoked in a different - way. Multiple parameters also may be used with UPDATE and DELETE - statements to emit distinct UPDATE/DELETE operations on a per-row basis, - however again when using the ORM, there is a different technique - generally used for updating or deleting many individual rows separately. +The above operation is equivalent to running the given INSERT statement once +for each parameter set, except that the operation will be optimized for +better performance across many rows. + +A key behavioral difference between "execute" and "executemany" is that the +latter doesn't support returning of result rows, even if the statement includes +the RETURNING clause. The one exception to this is when using a Core +:func:`_sql.insert` construct, introduced later in this tutorial at +:ref:`tutorial_core_insert`, which also indicates RETURNING using the +:meth:`_sql.Insert.returning` method. In that case, SQLAlchemy makes use of +special logic to reorganize the INSERT statement so that it can be invoked +for many rows while still supporting RETURNING. + +.. seealso:: + + :term:`executemany` - in the :doc:`Glossary `, describes the + DBAPI-level + `cursor.executemany() `_ + method that's used for most "executemany" executions. + + :ref:`engine_insertmanyvalues` - in :ref:`connections_toplevel`, describes + the specialized logic used by :meth:`_sql.Insert.returning` to deliver + result sets with "executemany" executions. .. rst-class:: orm-addin diff --git a/tools/format_docs_code.py b/tools/format_docs_code.py index 31a5b8e2ff..05e5e01f10 100644 --- a/tools/format_docs_code.py +++ b/tools/format_docs_code.py @@ -283,9 +283,11 @@ def iter_files(directory: str) -> Iterator[Path]: ) -def main(file: str | None, directory: str, exit_on_error: bool, check: bool): +def main( + file: list[str] | None, directory: str, exit_on_error: bool, check: bool +): if file is not None: - result = [format_file(Path(file), exit_on_error, check)] + result = [format_file(Path(f), exit_on_error, check) for f in file] else: result = [ format_file(doc, exit_on_error, check) @@ -327,7 +329,10 @@ Use --report-doctest to ignore errors on plain code blocks. formatter_class=RawDescriptionHelpFormatter, ) parser.add_argument( - "-f", "--file", help="Format only this file instead of all docs" + "-f", + "--file", + help="Format only this file instead of all docs", + nargs="+", ) parser.add_argument( "-d", @@ -357,7 +362,8 @@ Use --report-doctest to ignore errors on plain code blocks. action="store_true", ) parser.add_argument( - "-rd", "--report-doctest", + "-rd", + "--report-doctest", help="Report errors only when running doctest blocks. When active " "exit-on-error will be valid only on doctest blocks", action="store_true", -- 2.47.2