diff options
| -rw-r--r-- | doc/build/changelog/migration_13.rst | 20 | ||||
| -rw-r--r-- | doc/build/changelog/unreleased_13/4158.rst | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/pyodbc.py | 37 | ||||
| -rw-r--r-- | test/dialect/mssql/test_engine.py | 36 | ||||
| -rw-r--r-- | test/requirements.py | 15 |
5 files changed, 116 insertions, 3 deletions
diff --git a/doc/build/changelog/migration_13.rst b/doc/build/changelog/migration_13.rst index 86187a2f4..9f2b71818 100644 --- a/doc/build/changelog/migration_13.rst +++ b/doc/build/changelog/migration_13.rst @@ -200,3 +200,23 @@ Dialect Improvements and Changes - Oracle Dialect Improvements and Changes - SQL Server ============================================= + +.. _change_4158: + +Support for pyodbc fast_executemany +----------------------------------- + +Pyodbc's recently added "fast_executemany" mode, available when using the +Microsoft ODBC driver, is now an option for the pyodbc / mssql dialect. +Pass it via :func:`.create_engine`:: + + engine = create_engine( + "mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server", + fast_executemany=True) + +.. seealso:: + + :ref:`mssql_pyodbc_fastexecutemany` + + +:ticket:`4158` diff --git a/doc/build/changelog/unreleased_13/4158.rst b/doc/build/changelog/unreleased_13/4158.rst new file mode 100644 index 000000000..ed1c15976 --- /dev/null +++ b/doc/build/changelog/unreleased_13/4158.rst @@ -0,0 +1,11 @@ +.. change:: + :tags: feature, mssql + :tickets: 4158 + + Added ``fast_executemany=True`` parameter to the SQL Server pyodbc dialect, + which enables use of pyodbc's new performance feature of the same name + when using Microsoft ODBC drivers. + + .. seealso:: + + :ref:`change_4158` diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 36bcc49b3..34f81d6e8 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -85,6 +85,33 @@ Pyodbc only has partial support for rowcount. See the notes at :ref:`mssql_rowcount_versioning` for important notes when using ORM versioning. +.. _mssql_pyodbc_fastexecutemany: + +Fast Executemany Mode +--------------------- + +The Pyodbc driver has added support for a "fast executemany" mode of execution +which greatly reduces round trips for a DBAPI ``executemany()`` call when using +Microsoft ODBC drivers. The feature is enabled by setting the flag +``.fast_executemany`` on the DBAPI cursor when an executemany call is to be +used. The SQLAlchemy pyodbc SQL Server dialect supports setting this flag +automatically when the ``.fast_executemany`` flag is passed to +:func:`.create_engine`; note that the ODBC driver must be the Microsoft driver +in order to use this flag:: + + engine = create_engine( + "mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server", + fast_executemany=True) + +.. versionadded:: 1.3 + +.. seealso:: + + `fast executemany + <https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#fast_executemany>`_ + - on github + + """ from .base import MSExecutionContext, MSDialect, BINARY, VARBINARY @@ -264,7 +291,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect): } ) - def __init__(self, description_encoding=None, **params): + def __init__(self, description_encoding=None, fast_executemany=False, + **params): if 'description_encoding' in params: self.description_encoding = params.pop('description_encoding') super(MSDialect_pyodbc, self).__init__(**params) @@ -273,6 +301,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect): hasattr(self.dbapi.Cursor, 'nextset') self._need_decimal_fix = self.dbapi and \ self._dbapi_version() < (2, 1, 8) + self.fast_executemany = fast_executemany def _get_server_version_info(self, connection): try: @@ -296,6 +325,12 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect): pass return tuple(version) + def do_executemany(self, cursor, statement, parameters, context=None): + if self.fast_executemany: + cursor.fast_executemany = True + super(MSDialect_pyodbc, self).do_executemany( + cursor, statement, parameters, context=context) + def is_disconnect(self, e, connection, cursor): if isinstance(e, self.dbapi.Error): for code in ( diff --git a/test/dialect/mssql/test_engine.py b/test/dialect/mssql/test_engine.py index 3c6c479f9..973eb6dbb 100644 --- a/test/dialect/mssql/test_engine.py +++ b/test/dialect/mssql/test_engine.py @@ -10,6 +10,8 @@ from sqlalchemy.testing import assert_raises_message, \ assert_warnings, expect_warnings from sqlalchemy.testing.mock import Mock from sqlalchemy.dialects.mssql import base +from sqlalchemy import Integer, String, Table, Column +from sqlalchemy import event class ParseConnectTest(fixtures.TestBase): @@ -257,6 +259,40 @@ class EngineFromConfigTest(fixtures.TestBase): eq_(e.dialect.legacy_schema_aliasing, False) +class FastExecutemanyTest(fixtures.TestBase): + __only_on__ = 'mssql' + __backend__ = True + __requires__ = ('pyodbc_fast_executemany', ) + + @testing.provide_metadata + def test_flag_on(self): + t = Table( + 't', self.metadata, + Column('id', Integer, primary_key=True), + Column('data', String(50)) + ) + t.create() + + eng = engines.testing_engine(options={"fast_executemany": True}) + + @event.listens_for(eng, "after_cursor_execute") + def after_cursor_execute( + conn, cursor, statement, parameters, context, executemany): + if executemany: + assert cursor.fast_executemany + + with eng.connect() as conn: + conn.execute( + t.insert(), + [{"id": i, "data": "data_%d" % i} for i in range(100)] + ) + + conn.execute( + t.insert(), + {"id": 200, "data": "data_200"} + ) + + class VersionDetectionTest(fixtures.TestBase): def test_pymssql_version(self): dialect = pymssql.MSDialect_pymssql() diff --git a/test/requirements.py b/test/requirements.py index b11a6317f..f6ccbb6d8 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -1066,13 +1066,24 @@ class DefaultRequirements(SuiteRequirements): "works, but Oracle just gets tired with " "this much connection activity") - - @property def no_mssql_freetds(self): return self.mssql_freetds.not_() @property + def pyodbc_fast_executemany(self): + def has_fastexecutemany(config): + if not against(config, "mssql+pyodbc"): + return False + with config.db.connect() as conn: + drivername = conn.connection.connection.getinfo( + config.db.dialect.dbapi.SQL_DRIVER_NAME) + # on linux this is 'libmsodbcsql-13.1.so.9.2'. + # don't know what it is on windows + return "msodbc" in drivername + return only_if(has_fastexecutemany) + + @property def python_fixed_issue_8743(self): return exclusions.skip_if( lambda: sys.version_info < (2, 7, 8), |