diff options
| -rw-r--r-- | doc/build/changelog/unreleased_20/8177.rst | 16 | ||||
| -rw-r--r-- | lib/sqlalchemy/connectors/pyodbc.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/pyodbc.py | 64 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 2 | ||||
| -rw-r--r-- | test/dialect/mssql/test_engine.py | 9 |
5 files changed, 87 insertions, 13 deletions
diff --git a/doc/build/changelog/unreleased_20/8177.rst b/doc/build/changelog/unreleased_20/8177.rst new file mode 100644 index 000000000..2d3ba453b --- /dev/null +++ b/doc/build/changelog/unreleased_20/8177.rst @@ -0,0 +1,16 @@ +.. change:: + :tags: mssql, bug + :tickets: 8177 + + The ``use_setinputsizes`` parameter for the ``mssql+pyodbc`` dialect now + defaults to ``True``; this is so that non-unicode string comparisons are + bound by pyodbc to pyodbc.SQL_VARCHAR rather than pyodbc.SQL_WVARCHAR, + allowing indexes against VARCHAR columns to take effect. In order for the + ``fast_executemany=True`` parameter to continue functioning, the + ``use_setinputsizes`` mode now skips the ``cursor.setinputsizes()`` call + specifically when ``fast_executemany`` is True and the specific method in + use is ``cursor.executemany()``, which doesn't support setinputsizes. The + change also adds appropriate pyodbc DBAPI typing to values that are typed + as :class:`_types.Unicode` or :class:`_types.UnicodeText`, as well as + altered the base :class:`_types.JSON` datatype to consider JSON string + values as :class:`_types.Unicode` rather than :class:`_types.String`. diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py index e24acda75..7c5e62fae 100644 --- a/lib/sqlalchemy/connectors/pyodbc.py +++ b/lib/sqlalchemy/connectors/pyodbc.py @@ -42,6 +42,8 @@ class PyODBCConnector(Connector): supports_native_decimal = True default_paramstyle = "named" + fast_executemany = False + # for non-DSN connections, this *may* be used to # hold the desired driver name pyodbc_driver_name: Optional[str] = None @@ -203,6 +205,13 @@ class PyODBCConnector(Connector): # parameter were not passed to the dialect, or if no types were # specified in list_of_tuples + # as of #8177 for 2.0 we assume use_setinputsizes=True and only + # omit the setinputsizes calls for .executemany() with + # fast_executemany=True + + if context.executemany and self.fast_executemany: + return + cursor.setinputsizes( [ (dbtype, None, None) diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 6d64fdc3e..9f73ed28c 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -291,12 +291,10 @@ driver in order to use this flag:: Setinputsizes Support ----------------------- -The pyodbc ``cursor.setinputsizes()`` method can be used if necessary. To -enable this hook, pass ``use_setinputsizes=True`` to :func:`_sa.create_engine`:: +As of version 2.0, the pyodbc ``cursor.setinputsizes()`` method is used by +default except for .executemany() calls when fast_executemany=True. - engine = create_engine("mssql+pyodbc://...", use_setinputsizes=True) - -The behavior of the hook can then be customized, as may be necessary +The behavior of setinputsizes can be customized, as may be necessary particularly if fast_executemany is in use, via the :meth:`.DialectEvents.do_setinputsizes` hook. See that method for usage examples. @@ -304,6 +302,9 @@ examples. .. versionchanged:: 1.4.1 The pyodbc dialects will not use setinputsizes unless ``use_setinputsizes=True`` is passed. +.. versionchanged:: 2.0 The mssql+pyodbc dialect now defaults to using + setinputsizes except for .executemany() calls when fast_executemany=True. + """ # noqa @@ -313,11 +314,16 @@ import re import struct from .base import _MSDateTime +from .base import _MSUnicode +from .base import _MSUnicodeText from .base import BINARY from .base import DATETIMEOFFSET from .base import MSDialect from .base import MSExecutionContext from .base import VARBINARY +from .json import JSON as _MSJson +from .json import JSONIndexType as _MSJsonIndexType +from .json import JSONPathType as _MSJsonPathType from ... import exc from ... import types as sqltypes from ... import util @@ -466,6 +472,36 @@ class _BINARY_pyodbc(_ms_binary_pyodbc, BINARY): pass +class _String_pyodbc(sqltypes.String): + def get_dbapi_type(self, dbapi): + return dbapi.SQL_VARCHAR + + +class _Unicode_pyodbc(_MSUnicode): + def get_dbapi_type(self, dbapi): + return dbapi.SQL_WVARCHAR + + +class _UnicodeText_pyodbc(_MSUnicodeText): + def get_dbapi_type(self, dbapi): + return dbapi.SQL_WVARCHAR + + +class _JSON_pyodbc(_MSJson): + def get_dbapi_type(self, dbapi): + return dbapi.SQL_WVARCHAR + + +class _JSONIndexType_pyodbc(_MSJsonIndexType): + def get_dbapi_type(self, dbapi): + return dbapi.SQL_WVARCHAR + + +class _JSONPathType_pyodbc(_MSJsonPathType): + def get_dbapi_type(self, dbapi): + return dbapi.SQL_WVARCHAR + + class MSExecutionContext_pyodbc(MSExecutionContext): _embedded_scope_identity = False @@ -541,11 +577,25 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect): VARBINARY: _VARBINARY_pyodbc, sqltypes.VARBINARY: _VARBINARY_pyodbc, sqltypes.LargeBinary: _VARBINARY_pyodbc, + sqltypes.String: _String_pyodbc, + sqltypes.Unicode: _Unicode_pyodbc, + sqltypes.UnicodeText: _UnicodeText_pyodbc, + sqltypes.JSON: _JSON_pyodbc, + sqltypes.JSON.JSONIndexType: _JSONIndexType_pyodbc, + sqltypes.JSON.JSONPathType: _JSONPathType_pyodbc, + # this excludes Enum from the string/VARCHAR thing for now + # it looks like Enum's adaptation doesn't really support the + # String type itself having a dialect-level impl + sqltypes.Enum: sqltypes.Enum, }, ) - def __init__(self, fast_executemany=False, **params): - super(MSDialect_pyodbc, self).__init__(**params) + def __init__( + self, fast_executemany=False, use_setinputsizes=True, **params + ): + super(MSDialect_pyodbc, self).__init__( + use_setinputsizes=use_setinputsizes, **params + ) self.use_scope_identity = ( self.use_scope_identity and self.dbapi diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 6adc6ca77..c67614070 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -2482,7 +2482,7 @@ class JSON(Indexable, TypeEngine[Any]): .. versionadded:: 1.3.11 """ - return self._binary_w_type(String(), "as_string") + return self._binary_w_type(Unicode(), "as_string") def as_integer(self): """Cast an indexed value as integer. diff --git a/test/dialect/mssql/test_engine.py b/test/dialect/mssql/test_engine.py index 296820539..967ca85fe 100644 --- a/test/dialect/mssql/test_engine.py +++ b/test/dialect/mssql/test_engine.py @@ -474,11 +474,10 @@ class FastExecutemanyTest(fixtures.TestBase): use_fastexecutemany, apply_setinputsizes_flag, ): - expect_failure = ( - apply_setinputsizes_flag - and not include_setinputsizes - and use_fastexecutemany - ) + + # changes for issue #8177 have eliminated all current expected + # failures, but we'll leave this here in case we need it again + expect_failure = False engine = fe_engine(use_fastexecutemany, apply_setinputsizes_flag) |