diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 28 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/pyodbc.py | 20 |
2 files changed, 22 insertions, 26 deletions
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 31745d600..808fdf16f 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -251,18 +251,11 @@ The process for fetching this value has several variants: INSERT INTO t (x) OUTPUT inserted.id VALUES (?) - As of SQLAlchemy 2.0, the :ref:`engine_insertmanyvalues` feature is also - used by default to optimize many-row INSERT statements; for SQL Server - the feature takes place for both RETURNING and-non RETURNING - INSERT statements. - -* The value of :paramref:`_sa.create_engine.insertmanyvalues_page_size` - defaults to 1000, however the ultimate page size for a particular INSERT - statement may be limited further, based on an observed limit of - 2100 bound parameters for a single statement in SQL Server. - The page size may also be modified on a per-engine - or per-statement basis; see the section - :ref:`engine_insertmanyvalues_page_size` for details. + .. note:: SQLAlchemy 2.0 introduced the :ref:`engine_insertmanyvalues` + feature for SQL Server, which is used by default to optimize many-row + INSERT statements; however as of SQLAlchemy 2.0.9 this feature had + to be turned off for SQL Server as the database does not support + deterministic RETURNING of INSERT rows for a multi-row INSERT statement. * When RETURNING is not available or has been disabled via ``implicit_returning=False``, either the ``scope_identity()`` function or @@ -3017,7 +3010,8 @@ class MSDialect(default.DefaultDialect): # may be changed at server inspection time for older SQL server versions supports_multivalues_insert = True - use_insertmanyvalues = True + # disabled due to #9603 + use_insertmanyvalues = False # note pyodbc will set this to False if fast_executemany is set, # as of SQLAlchemy 2.0.9 @@ -3085,6 +3079,14 @@ class MSDialect(default.DefaultDialect): super().__init__(**opts) + if self.use_insertmanyvalues: + raise exc.ArgumentError( + "The use_insertmanyvalues feature on SQL Server is currently " + "not safe to use, as returned result rows may be returned in " + "random order. Ensure use_insertmanyvalues is left at its " + "default of False (this setting changed in SQLAlchemy 2.0.9)" + ) + self._json_serializer = json_serializer self._json_deserializer = json_deserializer diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index f9f2479ad..08c6bc48f 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -290,25 +290,19 @@ Pyodbc have been resolved as of SQLAlchemy 2.0.5. See the notes at Fast Executemany Mode --------------------- -.. note:: SQLAlchemy 2.0 now includes an equivalent "fast executemany" - handler for INSERT statements that is more robust than the PyODBC feature - (but is not quite as performant particularly for very large datasets); - the feature is called :ref:`insertmanyvalues <engine_insertmanyvalues>` - and is enabled for all INSERT statements by default. - SQLAlchemy's feature integrates with the PyODBC ``setinputsizes()`` method - which allows for more accurate specification of datatypes, and additionally - uses a dynamically sized, batched approach that scales to any number of - columns and/or rows. - - The SQL Server ``fast_executemany`` parameter may be used at the same time - as ``insertmanyvalues`` is enabled; however, the parameter will not be used - for INSERT statements that include RETURNING. + .. note:: SQLAlchemy 2.0 introduced the :ref:`engine_insertmanyvalues` + feature for SQL Server, which is used by default to optimize many-row + INSERT statements; however as of SQLAlchemy 2.0.9 this feature had + to be turned off for SQL Server as the database does not support + deterministic RETURNING of INSERT rows for a multi-row INSERT statement. .. versionchanged:: 2.0.9 - ``fast_executemany`` executions will be used for INSERT statements that don't include RETURNING, when ``fast_executemany`` is set. Previously, ``use_insertmanyvalues`` would cause ``fast_executemany`` to not be used in most cases. + ``use_insertmanyvalues`` is disabled for SQL Server overall as of 2.0.9. + The PyODBC driver includes support for a "fast executemany" mode of execution which greatly reduces round trips for a DBAPI ``executemany()`` call when using Microsoft ODBC drivers, for **limited size batches that fit in memory**. The |