From 952383f9ee04649282519b3287241c1336502fe5 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 28 Mar 2022 13:29:23 -0400 Subject: [PATCH] implement iso date literals for all backends Added modified ISO-8601 rendering (i.e. ISO-8601 with the T converted to a space) when using ``literal_binds`` with the SQL compilers provided by the PostgreSQL, MySQL, MariaDB, MSSQL, Oracle dialects. For Oracle, the ISO format is wrapped inside of an appropriate TO_DATE() function call. Previously this rendering was not implemented for dialect-specific compilation. Fixes: #5052 Change-Id: I7af15a51fedf5c5a8e76e645f7c3be997ece35f0 --- doc/build/changelog/unreleased_20/5052.rst | 10 +++ lib/sqlalchemy/dialects/mssql/base.py | 2 +- lib/sqlalchemy/dialects/mssql/pyodbc.py | 3 +- lib/sqlalchemy/dialects/mysql/base.py | 2 +- lib/sqlalchemy/dialects/mysql/types.py | 2 +- lib/sqlalchemy/dialects/oracle/base.py | 28 ++++++++- lib/sqlalchemy/dialects/oracle/cx_oracle.py | 62 +++++++++++-------- lib/sqlalchemy/dialects/postgresql/pg8000.py | 2 +- lib/sqlalchemy/dialects/postgresql/psycopg.py | 2 +- lib/sqlalchemy/engine/default.py | 30 +-------- lib/sqlalchemy/sql/sqltypes.py | 18 +++++- test/dialect/oracle/test_types.py | 4 +- test/requirements.py | 3 +- 13 files changed, 99 insertions(+), 69 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/5052.rst diff --git a/doc/build/changelog/unreleased_20/5052.rst b/doc/build/changelog/unreleased_20/5052.rst new file mode 100644 index 0000000000..235f547086 --- /dev/null +++ b/doc/build/changelog/unreleased_20/5052.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: usecase, datatypes + :tickets: 5052 + + Added modified ISO-8601 rendering (i.e. ISO-8601 with the T converted to a + space) when using ``literal_binds`` with the SQL compilers provided by the + PostgreSQL, MySQL, MariaDB, MSSQL, Oracle dialects. For Oracle, the ISO + format is wrapped inside of an appropriate TO_DATE() function call. + Previously this rendering was not implemented for dialect-specific + compilation. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index ac02b98a04..35428b659a 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -823,7 +823,6 @@ from ... import schema as sa_schema from ... import Sequence from ... import sql from ... import text -from ... import types as sqltypes from ... import util from ...engine import cursor as _cursor from ...engine import default @@ -835,6 +834,7 @@ from ...sql import expression from ...sql import func from ...sql import quoted_name from ...sql import roles +from ...sql import sqltypes from ...sql import util as sql_util from ...sql._typing import is_sql_compiler from ...types import BIGINT diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 530a0a4809..f6c9afc49b 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -310,6 +310,7 @@ import decimal import re import struct +from .base import _MSDateTime from .base import BINARY from .base import DATETIMEOFFSET from .base import MSDialect @@ -447,7 +448,7 @@ class _ODBCDateTimeBindProcessor: return process -class _ODBCDateTime(_ODBCDateTimeBindProcessor, sqltypes.DateTime): +class _ODBCDateTime(_ODBCDateTimeBindProcessor, _MSDateTime): pass diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index cfdc3deb2b..25f4c6945a 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1020,7 +1020,6 @@ from ... import exc from ... import log from ... import schema as sa_schema from ... import sql -from ... import types as sqltypes from ... import util from ...engine import default from ...engine import reflection @@ -1030,6 +1029,7 @@ from ...sql import elements from ...sql import functions from ...sql import operators from ...sql import roles +from ...sql import sqltypes from ...sql import util as sql_util from ...sql.sqltypes import Unicode from ...types import BINARY diff --git a/lib/sqlalchemy/dialects/mysql/types.py b/lib/sqlalchemy/dialects/mysql/types.py index 2a4b4ad5fb..95e85f602e 100644 --- a/lib/sqlalchemy/dialects/mysql/types.py +++ b/lib/sqlalchemy/dialects/mysql/types.py @@ -8,8 +8,8 @@ import datetime from ... import exc -from ... import types as sqltypes from ... import util +from ...sql import sqltypes class _NumericType: diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 39a542cce8..7cd4603a84 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -688,7 +688,28 @@ class LONG(sqltypes.Text): __visit_name__ = "LONG" -class DATE(sqltypes.DateTime): +class _OracleDateLiteralRender: + def literal_processor(self, dialect): + def process(value): + if value is not None: + if getattr(value, "microsecond", None): + value = ( + f"""TO_TIMESTAMP""" + f"""('{value.isoformat().replace("T", " ")}', """ + """'YYYY-MM-DD HH24:MI:SS.FF')""" + ) + else: + value = ( + f"""TO_DATE""" + f"""('{value.isoformat().replace("T", " ")}', """ + """'YYYY-MM-DD HH24:MI:SS')""" + ) + return value + + return process + + +class DATE(_OracleDateLiteralRender, sqltypes.DateTime): """Provide the oracle DATE type. This type has no special Python behavior, except that it subclasses @@ -705,6 +726,10 @@ class DATE(sqltypes.DateTime): return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) +class _OracleDate(_OracleDateLiteralRender, sqltypes.Date): + pass + + class INTERVAL(sqltypes.NativeForEmulated, sqltypes._AbstractInterval): __visit_name__ = "INTERVAL" @@ -763,6 +788,7 @@ colspecs = { sqltypes.Boolean: _OracleBoolean, sqltypes.Interval: INTERVAL, sqltypes.DateTime: DATE, + sqltypes.Date: _OracleDate, } ischema_names = { diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 9f33945331..f8cdd50e4e 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -440,11 +440,11 @@ from .base import OracleCompiler from .base import OracleDialect from .base import OracleExecutionContext from ... import exc -from ... import types as sqltypes from ... import util from ...engine import cursor as _cursor from ...engine import interfaces from ...engine import processors +from ...sql import sqltypes from ...sql._typing import is_sql_compiler @@ -563,7 +563,7 @@ class _OracleNUMBER(_OracleNumeric): is_number = True -class _OracleDate(sqltypes.Date): +class _CXOracleDate(oracle._OracleDate): def bind_processor(self, dialect): return None @@ -577,6 +577,10 @@ class _OracleDate(sqltypes.Date): return process +class _CXOracleTIMESTAMP(oracle._OracleDateLiteralRender, sqltypes.TIMESTAMP): + pass + + # TODO: the names used across CHAR / VARCHAR / NCHAR / NVARCHAR # here are inconsistent and not very good class _OracleChar(sqltypes.CHAR): @@ -847,31 +851,35 @@ class OracleDialect_cx_oracle(OracleDialect): driver = "cx_oracle" - colspecs = { - sqltypes.Numeric: _OracleNumeric, - sqltypes.Float: _OracleNumeric, - oracle.BINARY_FLOAT: _OracleBINARY_FLOAT, - oracle.BINARY_DOUBLE: _OracleBINARY_DOUBLE, - sqltypes.Integer: _OracleInteger, - oracle.NUMBER: _OracleNUMBER, - sqltypes.Date: _OracleDate, - sqltypes.LargeBinary: _OracleBinary, - sqltypes.Boolean: oracle._OracleBoolean, - sqltypes.Interval: _OracleInterval, - oracle.INTERVAL: _OracleInterval, - sqltypes.Text: _OracleText, - sqltypes.String: _OracleString, - sqltypes.UnicodeText: _OracleUnicodeTextCLOB, - sqltypes.CHAR: _OracleChar, - sqltypes.NCHAR: _OracleNChar, - sqltypes.Enum: _OracleEnum, - oracle.LONG: _OracleLong, - oracle.RAW: _OracleRaw, - sqltypes.Unicode: _OracleUnicodeStringCHAR, - sqltypes.NVARCHAR: _OracleUnicodeStringNCHAR, - oracle.NCLOB: _OracleUnicodeTextNCLOB, - oracle.ROWID: _OracleRowid, - } + colspecs = OracleDialect.colspecs + colspecs.update( + { + sqltypes.TIMESTAMP: _CXOracleTIMESTAMP, + sqltypes.Numeric: _OracleNumeric, + sqltypes.Float: _OracleNumeric, + oracle.BINARY_FLOAT: _OracleBINARY_FLOAT, + oracle.BINARY_DOUBLE: _OracleBINARY_DOUBLE, + sqltypes.Integer: _OracleInteger, + oracle.NUMBER: _OracleNUMBER, + sqltypes.Date: _CXOracleDate, + sqltypes.LargeBinary: _OracleBinary, + sqltypes.Boolean: oracle._OracleBoolean, + sqltypes.Interval: _OracleInterval, + oracle.INTERVAL: _OracleInterval, + sqltypes.Text: _OracleText, + sqltypes.String: _OracleString, + sqltypes.UnicodeText: _OracleUnicodeTextCLOB, + sqltypes.CHAR: _OracleChar, + sqltypes.NCHAR: _OracleNChar, + sqltypes.Enum: _OracleEnum, + oracle.LONG: _OracleLong, + oracle.RAW: _OracleRaw, + sqltypes.Unicode: _OracleUnicodeStringCHAR, + sqltypes.NVARCHAR: _OracleUnicodeStringNCHAR, + oracle.NCLOB: _OracleUnicodeTextNCLOB, + oracle.ROWID: _OracleRowid, + } + ) execute_sequence_format = list diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index 372b8639e0..1ae649ef9e 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -108,9 +108,9 @@ from .json import JSON from .json import JSONB from .json import JSONPathType from ... import exc -from ... import types as sqltypes from ... import util from ...engine import processors +from ...sql import sqltypes from ...sql.elements import quoted_name diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py index 33dc65afc5..9207221df6 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py @@ -69,9 +69,9 @@ from .json import JSON from .json import JSONB from .json import JSONPathType from ... import pool -from ... import types as sqltypes from ... import util from ...engine import AdaptedConnection +from ...sql import sqltypes from ...util.concurrency import await_fallback from ...util.concurrency import await_only diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 4d700866fe..b30cf9c088 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -47,10 +47,10 @@ from .interfaces import ExecutionContext from .. import event from .. import exc from .. import pool -from .. import types as sqltypes from .. import util from ..sql import compiler from ..sql import expression +from ..sql import type_api from ..sql._typing import is_tuple_type from ..sql.compiler import DDLCompiler from ..sql.compiler import SQLCompiler @@ -498,7 +498,7 @@ class DefaultDialect(Dialect): and passes on to :func:`_types.adapt_type`. """ - return sqltypes.adapt_type(typeobj, self.colspecs) + return type_api.adapt_type(typeobj, self.colspecs) def has_index(self, connection, table_name, index_name, schema=None): if not self.has_table(connection, table_name, schema=schema): @@ -746,26 +746,6 @@ class DefaultDialect(Dialect): return connection -class _RendersLiteral: - def literal_processor(self, dialect): - def process(value): - return "'%s'" % value - - return process - - -class _StrDateTime(_RendersLiteral, sqltypes.DateTime): - pass - - -class _StrDate(_RendersLiteral, sqltypes.Date): - pass - - -class _StrTime(_RendersLiteral, sqltypes.Time): - pass - - class StrCompileDialect(DefaultDialect): statement_compiler = compiler.StrSQLCompiler @@ -787,12 +767,6 @@ class StrCompileDialect(DefaultDialect): supports_multivalues_insert = True supports_simple_order_by_label = True - colspecs = { - sqltypes.DateTime: _StrDateTime, - sqltypes.Date: _StrDate, - sqltypes.Time: _StrTime, - } - class DefaultExecutionContext(ExecutionContext): isinsert = False diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 1a6de34b03..803e856548 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -700,7 +700,19 @@ class Double(Float[_N]): __visit_name__ = "double" -class DateTime(HasExpressionLookup, TypeEngine[dt.datetime]): +class _RenderISO8601NoT: + def literal_processor(self, dialect): + def process(value): + if value is not None: + value = f"""'{value.isoformat().replace("T", " ")}'""" + return value + + return process + + +class DateTime( + _RenderISO8601NoT, HasExpressionLookup, TypeEngine[dt.datetime] +): """A type for ``datetime.datetime()`` objects. @@ -762,7 +774,7 @@ class DateTime(HasExpressionLookup, TypeEngine[dt.datetime]): } -class Date(HasExpressionLookup, TypeEngine[dt.date]): +class Date(_RenderISO8601NoT, HasExpressionLookup, TypeEngine[dt.date]): """A type for ``datetime.date()`` objects.""" @@ -800,7 +812,7 @@ class Date(HasExpressionLookup, TypeEngine[dt.date]): } -class Time(HasExpressionLookup, TypeEngine[dt.time]): +class Time(_RenderISO8601NoT, HasExpressionLookup, TypeEngine[dt.time]): """A type for ``datetime.time()`` objects.""" diff --git a/test/dialect/oracle/test_types.py b/test/dialect/oracle/test_types.py index d3c522f259..8302ed5233 100644 --- a/test/dialect/oracle/test_types.py +++ b/test/dialect/oracle/test_types.py @@ -84,11 +84,11 @@ class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile(oracle.LONG(), "LONG") @testing.combinations( - (Date(), cx_oracle._OracleDate), + (Date(), cx_oracle._CXOracleDate), (oracle.OracleRaw(), cx_oracle._OracleRaw), (String(), String), (VARCHAR(), cx_oracle._OracleString), - (DATE(), cx_oracle._OracleDate), + (DATE(), cx_oracle._CXOracleDate), (oracle.DATE(), oracle.DATE), (String(50), cx_oracle._OracleString), (Unicode(), cx_oracle._OracleUnicodeStringCHAR), diff --git a/test/requirements.py b/test/requirements.py index df6b5d62f5..6f85aff2c4 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -1047,8 +1047,7 @@ class DefaultRequirements(SuiteRequirements): literal string, e.g. via the TypeEngine.literal_processor() method. """ - - return fails_on_everything_except("sqlite") + return exclusions.open() @property def datetime(self): -- 2.47.2