diff options
| -rw-r--r-- | doc/build/changelog/changelog_14.rst | 2 | ||||
| -rw-r--r-- | doc/build/changelog/unreleased_14/6366.rst | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/pyodbc.py | 10 | ||||
| -rw-r--r-- | test/dialect/mssql/test_types.py | 34 |
4 files changed, 53 insertions, 3 deletions
diff --git a/doc/build/changelog/changelog_14.rst b/doc/build/changelog/changelog_14.rst index 4482058c1..5bef3856a 100644 --- a/doc/build/changelog/changelog_14.rst +++ b/doc/build/changelog/changelog_14.rst @@ -63,7 +63,7 @@ This document details individual issue-level changes made throughout .. change:: - :tags: usecase, schema, mssql + :tags: usecase, mssql :tickets: 6306 The :paramref:`_types.DateTime.timezone` parameter when set to ``True`` diff --git a/doc/build/changelog/unreleased_14/6366.rst b/doc/build/changelog/unreleased_14/6366.rst new file mode 100644 index 000000000..1a26d733f --- /dev/null +++ b/doc/build/changelog/unreleased_14/6366.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, mssql, regression + :tickets: 6366 + + Fixed regression caused by :ticket:`6306` which added support for + ``DateTime(timezone=True)``, where the previous behavior of the pyodbc + driver of implicitly dropping the tzinfo from a timezone-aware date when + INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL + Server error when inserting timezone-aware datetime objects into + timezone-native database columns. diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 424adadfc..95b2bb48e 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -353,6 +353,8 @@ class _ms_binary_pyodbc(object): class _ODBCDateTime(sqltypes.DateTime): """Add bind processors to handle datetimeoffset behaviors""" + has_tz = False + def bind_processor(self, dialect): def process(value): if value is None: @@ -360,7 +362,7 @@ class _ODBCDateTime(sqltypes.DateTime): elif isinstance(value, util.string_types): # if a string was passed directly, allow it through return value - elif isinstance(value, datetime.datetime) and value.tzinfo is None: + elif not value.tzinfo or (not self.timezone and not self.has_tz): # for DateTime(timezone=False) return value else: @@ -379,6 +381,10 @@ class _ODBCDateTime(sqltypes.DateTime): return process +class _ODBCDATETIMEOFFSET(_ODBCDateTime): + has_tz = True + + class _VARBINARY_pyodbc(_ms_binary_pyodbc, VARBINARY): pass @@ -453,7 +459,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect): BINARY: _BINARY_pyodbc, # support DateTime(timezone=True) sqltypes.DateTime: _ODBCDateTime, - DATETIMEOFFSET: _ODBCDateTime, + DATETIMEOFFSET: _ODBCDATETIMEOFFSET, # SQL Server dialect has a VARBINARY that is just to support # "deprecate_large_types" w/ VARBINARY(max), but also we must # handle the usual SQL standard VARBINARY diff --git a/test/dialect/mssql/test_types.py b/test/dialect/mssql/test_types.py index 7fb2f05ac..4e78ec122 100644 --- a/test/dialect/mssql/test_types.py +++ b/test/dialect/mssql/test_types.py @@ -805,6 +805,40 @@ class TypeRoundTripTest( (d1, d2, t1, d2.time(), d3), ) + @testing.combinations( + ( + datetime.datetime( + 2007, + 10, + 30, + 11, + 2, + 32, + tzinfo=util.timezone(datetime.timedelta(hours=-5)), + ), + ), + (datetime.datetime(2007, 10, 30, 11, 2, 32)), + argnames="date", + ) + def test_tz_present_or_non_in_dates(self, date_fixture, connection, date): + t, (d1, t1, d2, d3) = date_fixture + connection.execute( + t.insert(), + dict( + adatetime=date, + adatetimewithtimezone=date, + ), + ) + + row = connection.execute( + select(t.c.adatetime, t.c.adatetimewithtimezone) + ).first() + + if not date.tzinfo: + eq_(row, (date, date.replace(tzinfo=util.timezone.utc))) + else: + eq_(row, (date.replace(tzinfo=None), date)) + @testing.metadata_fixture() def datetimeoffset_fixture(self, metadata): t = Table( |