From b9d231869d7e39decabdec12478e359c4dcb95ee Mon Sep 17 00:00:00 2001 From: zeeeeeb <5767468+zeeeeeb@users.noreply.github.com> Date: Sat, 12 Feb 2022 14:00:02 -0500 Subject: [PATCH] Implement generic Double and related fixed types Added :class:`.Double`, :class:`.DOUBLE`, :class:`.DOUBLE_PRECISION` datatypes to the base ``sqlalchemy.`` module namespace, for explicit use of double/double precision as well as generic "double" datatypes. Use :class:`.Double` for generic support that will resolve to DOUBLE/DOUBLE PRECISION/FLOAT as needed for different backends. Implemented DDL and reflection support for ``FLOAT`` datatypes which include an explicit "binary_precision" value. Using the Oracle-specific :class:`_oracle.FLOAT` datatype, the new parameter :paramref:`_oracle.FLOAT.binary_precision` may be specified which will render Oracle's precision for floating point types directly. This value is interpreted during reflection. Upon reflecting back a ``FLOAT`` datatype, the datatype returned is one of :class:`_types.DOUBLE_PRECISION` for a ``FLOAT`` for a precision of 126 (this is also Oracle's default precision for ``FLOAT``), :class:`_types.REAL` for a precision of 63, and :class:`_oracle.FLOAT` for a custom precision, as per Oracle documentation. As part of this change, the generic :paramref:`_sqltypes.Float.precision` value is explicitly rejected when generating DDL for Oracle, as this precision cannot be accurately converted to "binary precision"; instead, an error message encourages the use of :meth:`_sqltypes.TypeEngine.with_variant` so that Oracle's specific form of precision may be chosen exactly. This is a backwards-incompatible change in behavior, as the previous "precision" value was silently ignored for Oracle. Fixes: #5465 Closes: #7674 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7674 Pull-request-sha: 5c68419e5aee2e27bf21a8ac9eb5950d196c77e5 Change-Id: I831f4af3ee3b23fde02e8f6393c83e23dd7cd34d --- doc/build/changelog/migration_20.rst | 43 ++++++ doc/build/changelog/unreleased_20/5465.rst | 38 ++++++ doc/build/conf.py | 1 + doc/build/core/type_basics.rst | 6 + doc/build/dialects/oracle.rst | 7 +- lib/sqlalchemy/__init__.py | 3 + lib/sqlalchemy/dialects/mysql/base.py | 1 + lib/sqlalchemy/dialects/mysql/types.py | 2 +- lib/sqlalchemy/dialects/oracle/__init__.py | 1 + lib/sqlalchemy/dialects/oracle/base.py | 104 +++++++++++++-- lib/sqlalchemy/dialects/postgresql/base.py | 9 +- lib/sqlalchemy/dialects/sqlite/base.py | 2 +- lib/sqlalchemy/sql/compiler.py | 9 ++ lib/sqlalchemy/sql/sqltypes.py | 125 ++++++++++++++++-- .../testing/suite/test_reflection.py | 4 +- lib/sqlalchemy/testing/suite/test_types.py | 14 +- lib/sqlalchemy/types.py | 6 + test/dialect/mysql/test_compiler.py | 6 + test/dialect/oracle/test_compiler.py | 10 ++ test/dialect/oracle/test_reflection.py | 29 ++-- test/dialect/oracle/test_types.py | 25 +++- test/dialect/postgresql/test_compiler.py | 12 ++ test/dialect/postgresql/test_types.py | 13 +- test/engine/test_reflection.py | 4 +- test/sql/test_types.py | 5 + 25 files changed, 419 insertions(+), 60 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/5465.rst diff --git a/doc/build/changelog/migration_20.rst b/doc/build/changelog/migration_20.rst index 380651a30a..b8415d61a9 100644 --- a/doc/build/changelog/migration_20.rst +++ b/doc/build/changelog/migration_20.rst @@ -340,6 +340,49 @@ customizable via the :paramref:`_sa.create_engine.poolclass` parameter. :ticket:`7490` +.. _change_5465_oracle: + +New Oracle FLOAT type with binary precision; decimal precision not accepted directly +------------------------------------------------------------------------------------ + +A new datatype :class:`_oracle.FLOAT` has been added to the Oracle dialect, to +accompany the addition of :class:`_sqltypes.Double` and database-specific +:class:`_sqltypes.DOUBLE`, :class:`_sqltypes.DOUBLE_PRECISION` and +:class:`_sqltypes.REAL` datatypes. Oracle's ``FLOAT`` accepts a so-called +"binary precision" parameter that per Oracle documentation is roughly a +standard "precision" value divided by 0.3103:: + + from sqlalchemy.dialects import oracle + + Table( + "some_table", metadata, + Column("value", oracle.FLOAT(126)) + ) + +A binary precision value of 126 is synonymous with using the +:class:`_sqltypes.DOUBLE_PRECISION` datatype, and a value of 63 is equivalent +to using the :class:`_sqltypes.REAL` datatype. Other precision values are +specific to the :class:`_oracle.FLOAT` type itself. + +The SQLAlchemy :class:`_sqltypes.Float` datatype also accepts a "precision" +parameter, but this is decimal precision which is not accepted by +Oracle. Rather than attempting to guess the conversion, the Oracle dialect +will now raise an informative error if :class:`_sqltypes.Float` is used with +a precision value against the Oracle backend. To specify a +:class:`_sqltypes.Float` datatype with an explicit precision value for +supporting backends, while also supporting other backends, use +the :meth:`_types.TypeEngine.with_variant` method as follows:: + + from sqlalchemy.types import Float + from sqlalchemy.dialects import oracle + + Table( + "some_table", metadata, + Column("value", Float(5).with_variant(oracle.FLOAT(16), "oracle")) + ) + + + .. _migration_20_overview: 1.x -> 2.x Migration Overview diff --git a/doc/build/changelog/unreleased_20/5465.rst b/doc/build/changelog/unreleased_20/5465.rst new file mode 100644 index 0000000000..2bf9f01a9f --- /dev/null +++ b/doc/build/changelog/unreleased_20/5465.rst @@ -0,0 +1,38 @@ +.. change:: + :tags: feature, types + :tickets: 5465 + + Added :class:`.Double`, :class:`.DOUBLE`, :class:`.DOUBLE_PRECISION` + datatypes to the base ``sqlalchemy.`` module namespace, for explicit use of + double/double precision as well as generic "double" datatypes. Use + :class:`.Double` for generic support that will resolve to DOUBLE/DOUBLE + PRECISION/FLOAT as needed for different backends. + + +.. change:: + :tags: feature, oracle + :tickets: 5465 + + Implemented DDL and reflection support for ``FLOAT`` datatypes which + include an explicit "binary_precision" value. Using the Oracle-specific + :class:`_oracle.FLOAT` datatype, the new parameter + :paramref:`_oracle.FLOAT.binary_precision` may be specified which will + render Oracle's precision for floating point types directly. This value is + interpreted during reflection. Upon reflecting back a ``FLOAT`` datatype, + the datatype returned is one of :class:`_types.DOUBLE_PRECISION` for a + ``FLOAT`` for a precision of 126 (this is also Oracle's default precision + for ``FLOAT``), :class:`_types.REAL` for a precision of 63, and + :class:`_oracle.FLOAT` for a custom precision, as per Oracle documentation. + + As part of this change, the generic :paramref:`_sqltypes.Float.precision` + value is explicitly rejected when generating DDL for Oracle, as this + precision cannot be accurately converted to "binary precision"; instead, an + error message encourages the use of + :meth:`_sqltypes.TypeEngine.with_variant` so that Oracle's specific form of + precision may be chosen exactly. This is a backwards-incompatible change in + behavior, as the previous "precision" value was silently ignored for + Oracle. + + .. seealso:: + + :ref:`change_5465_oracle` \ No newline at end of file diff --git a/doc/build/conf.py b/doc/build/conf.py index 2284db58eb..d5b0a8b11d 100644 --- a/doc/build/conf.py +++ b/doc/build/conf.py @@ -148,6 +148,7 @@ zzzeeksphinx_module_prefixes = { "_row": "sqlalchemy.engine", "_schema": "sqlalchemy.schema", "_types": "sqlalchemy.types", + "_sqltypes": "sqlalchemy.types", "_asyncio": "sqlalchemy.ext.asyncio", "_expression": "sqlalchemy.sql.expression", "_sql": "sqlalchemy.sql.expression", diff --git a/doc/build/core/type_basics.rst b/doc/build/core/type_basics.rst index 3ec50cc003..c5c57db329 100644 --- a/doc/build/core/type_basics.rst +++ b/doc/build/core/type_basics.rst @@ -52,6 +52,9 @@ type is emitted in ``CREATE TABLE``, such as ``VARCHAR`` see .. autoclass:: Enum :members: __init__, create, drop +.. autoclass:: Double + :members: + .. autoclass:: Float :members: @@ -137,6 +140,9 @@ its exact name in DDL with ``CREATE TABLE`` is issued. .. autoclass:: DECIMAL +.. autoclass:: DOUBLE + +.. autoclass:: DOUBLE_PRECISION .. autoclass:: FLOAT diff --git a/doc/build/dialects/oracle.rst b/doc/build/dialects/oracle.rst index 988a698e82..bf25ea7ad3 100644 --- a/doc/build/dialects/oracle.rst +++ b/doc/build/dialects/oracle.rst @@ -32,26 +32,21 @@ construction arguments, are as follows: .. autoclass:: DATE :members: __init__ -.. autoclass:: DOUBLE_PRECISION +.. autoclass:: FLOAT :members: __init__ - .. autoclass:: INTERVAL :members: __init__ - .. autoclass:: NCLOB :members: __init__ - .. autoclass:: NUMBER :members: __init__ - .. autoclass:: LONG :members: __init__ - .. autoclass:: RAW :members: __init__ diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index dc1c536c8d..7ceb33c7ca 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -223,6 +223,9 @@ from .types import Date as Date from .types import DATETIME as DATETIME from .types import DateTime as DateTime from .types import DECIMAL as DECIMAL +from .types import DOUBLE as DOUBLE +from .types import Double as Double +from .types import DOUBLE_PRECISION as DOUBLE_PRECISION from .types import Enum as Enum from .types import FLOAT as FLOAT from .types import Float as Float diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 7ec2b3dc26..5c2de09115 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1082,6 +1082,7 @@ colspecs = { _FloatType: _FloatType, sqltypes.Numeric: NUMERIC, sqltypes.Float: FLOAT, + sqltypes.Double: DOUBLE, sqltypes.Time: TIME, sqltypes.Enum: ENUM, sqltypes.MatchType: _MatchType, diff --git a/lib/sqlalchemy/dialects/mysql/types.py b/lib/sqlalchemy/dialects/mysql/types.py index 855fac1b36..2a4b4ad5fb 100644 --- a/lib/sqlalchemy/dialects/mysql/types.py +++ b/lib/sqlalchemy/dialects/mysql/types.py @@ -152,7 +152,7 @@ class DECIMAL(_NumericType, sqltypes.DECIMAL): ) -class DOUBLE(_FloatType): +class DOUBLE(_FloatType, sqltypes.DOUBLE): """MySQL DOUBLE type.""" __visit_name__ = "DOUBLE" diff --git a/lib/sqlalchemy/dialects/oracle/__init__.py b/lib/sqlalchemy/dialects/oracle/__init__.py index c83e0573d6..8994b4a108 100644 --- a/lib/sqlalchemy/dialects/oracle/__init__.py +++ b/lib/sqlalchemy/dialects/oracle/__init__.py @@ -24,6 +24,7 @@ from .base import NUMBER from .base import NVARCHAR from .base import NVARCHAR2 from .base import RAW +from .base import REAL from .base import ROWID from .base import TIMESTAMP from .base import VARCHAR diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 578450d248..1ae58b8f47 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -558,10 +558,12 @@ from ...sql import visitors from ...types import BLOB from ...types import CHAR from ...types import CLOB +from ...types import DOUBLE_PRECISION from ...types import FLOAT from ...types import INTEGER from ...types import NCHAR from ...types import NVARCHAR +from ...types import REAL from ...types import TIMESTAMP from ...types import VARCHAR @@ -625,8 +627,49 @@ class NUMBER(sqltypes.Numeric, sqltypes.Integer): return sqltypes.Integer -class DOUBLE_PRECISION(sqltypes.Float): - __visit_name__ = "DOUBLE_PRECISION" +class FLOAT(sqltypes.FLOAT): + """Oracle FLOAT. + + This is the same as :class:`_sqltypes.FLOAT` except that + an Oracle-specific :paramref:`_oracle.FLOAT.binary_precision` + parameter is accepted, and + the :paramref:`_sqltypes.Float.precision` parameter is not accepted. + + Oracle FLOAT types indicate precision in terms of "binary precision", which + defaults to 126. For a REAL type, the value is 63. This parameter does not + cleanly map to a specific number of decimal places but is roughly + equivalent to the desired number of decimal places divided by 0.3103. + + .. versionadded:: 2.0 + + """ + + __visit_name__ = "FLOAT" + + def __init__( + self, + binary_precision=None, + asdecimal=False, + decimal_return_scale=None, + ): + r""" + Construct a FLOAT + + :param binary_precision: Oracle binary precision value to be rendered + in DDL. This may be approximated to the number of decimal characters + using the formula "decimal precision = 0.30103 * binary precision". + The default value used by Oracle for FLOAT / DOUBLE PRECISION is 126. + + :param asdecimal: See :paramref:`_sqltypes.Float.asdecimal` + + :param decimal_return_scale: See + :paramref:`_sqltypes.Float.decimal_return_scale` + + """ + super().__init__( + asdecimal=asdecimal, decimal_return_scale=decimal_return_scale + ) + self.binary_precision = binary_precision class BINARY_DOUBLE(sqltypes.Float): @@ -742,6 +785,7 @@ ischema_names = { "RAW": RAW, "FLOAT": FLOAT, "DOUBLE PRECISION": DOUBLE_PRECISION, + "REAL": REAL, "LONG": LONG, "BINARY_DOUBLE": BINARY_DOUBLE, "BINARY_FLOAT": BINARY_FLOAT, @@ -760,6 +804,9 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler): def visit_float(self, type_, **kw): return self.visit_FLOAT(type_, **kw) + def visit_double(self, type_, **kw): + return self.visit_DOUBLE_PRECISION(type_, **kw) + def visit_unicode(self, type_, **kw): if self.dialect._use_nchar_for_unicode: return self.visit_NVARCHAR2(type_, **kw) @@ -795,24 +842,50 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler): return self._generate_numeric(type_, "BINARY_FLOAT", **kw) def visit_FLOAT(self, type_, **kw): - # don't support conversion between decimal/binary - # precision yet - kw["no_precision"] = True + kw["_requires_binary_precision"] = True return self._generate_numeric(type_, "FLOAT", **kw) def visit_NUMBER(self, type_, **kw): return self._generate_numeric(type_, "NUMBER", **kw) def _generate_numeric( - self, type_, name, precision=None, scale=None, no_precision=False, **kw + self, + type_, + name, + precision=None, + scale=None, + _requires_binary_precision=False, + **kw, ): if precision is None: - precision = type_.precision + + precision = getattr(type_, "precision", None) + + if _requires_binary_precision: + binary_precision = getattr(type_, "binary_precision", None) + + if precision and binary_precision is None: + # https://www.oracletutorial.com/oracle-basics/oracle-float/ + estimated_binary_precision = int(precision / 0.30103) + raise exc.ArgumentError( + "Oracle FLOAT types use 'binary precision', which does " + "not convert cleanly from decimal 'precision'. Please " + "specify " + f"this type with a separate Oracle variant, such as " + f"{type_.__class__.__name__}(precision={precision})." + f"with_variant(oracle.FLOAT" + f"(binary_precision=" + f"{estimated_binary_precision}), 'oracle'), so that the " + "Oracle specific 'binary_precision' may be specified " + "accurately." + ) + else: + precision = binary_precision if scale is None: scale = getattr(type_, "scale", None) - if no_precision or precision is None: + if precision is None: return name elif scale is None: n = "%(name)s(%(precision)s)" @@ -1964,8 +2037,19 @@ class OracleDialect(default.DefaultDialect): else: coltype = NUMBER(precision, scale) elif coltype == "FLOAT": - # TODO: support "precision" here as "binary_precision" - coltype = FLOAT() + # https://docs.oracle.com/cd/B14117_01/server.101/b10758/sqlqr06.htm + if precision == 126: + # The DOUBLE PRECISION datatype is a floating-point + # number with binary precision 126. + coltype = DOUBLE_PRECISION() + elif precision == 63: + # The REAL datatype is a floating-point number with a + # binary precision of 63, or 18 decimal. + coltype = REAL() + else: + # non standard precision + coltype = FLOAT(binary_precision=precision) + elif coltype in ("VARCHAR2", "NVARCHAR2", "CHAR", "NCHAR"): coltype = self.ischema_names.get(coltype)(length) elif "WITH TIME ZONE" in coltype: diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 698ea277f5..e265cd0f7f 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1449,6 +1449,7 @@ from ...types import BIGINT from ...types import BOOLEAN from ...types import CHAR from ...types import DATE +from ...types import DOUBLE_PRECISION from ...types import FLOAT from ...types import INTEGER from ...types import NUMERIC @@ -1575,10 +1576,6 @@ class BYTEA(sqltypes.LargeBinary): __visit_name__ = "BYTEA" -class DOUBLE_PRECISION(sqltypes.Float): - __visit_name__ = "DOUBLE_PRECISION" - - class INET(sqltypes.TypeEngine): __visit_name__ = "INET" @@ -2896,8 +2893,8 @@ class PGTypeCompiler(compiler.GenericTypeCompiler): else: return "FLOAT(%(precision)s)" % {"precision": type_.precision} - def visit_DOUBLE_PRECISION(self, type_, **kw): - return "DOUBLE PRECISION" + def visit_double(self, type_, **kw): + return self.visit_DOUBLE_PRECISION(type, **kw) def visit_BIGINT(self, type_, **kw): return "BIGINT" diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 385de373ea..79068c75f0 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1179,7 +1179,7 @@ ischema_names = { "DATE_CHAR": sqltypes.DATE, "DATETIME": sqltypes.DATETIME, "DATETIME_CHAR": sqltypes.DATETIME, - "DOUBLE": sqltypes.FLOAT, + "DOUBLE": sqltypes.DOUBLE, "DECIMAL": sqltypes.DECIMAL, "FLOAT": sqltypes.FLOAT, "INT": sqltypes.INTEGER, diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index b140f92975..131281a165 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -4888,6 +4888,12 @@ class GenericTypeCompiler(TypeCompiler): def visit_FLOAT(self, type_, **kw): return "FLOAT" + def visit_DOUBLE(self, type_, **kw): + return "DOUBLE" + + def visit_DOUBLE_PRECISION(self, type_, **kw): + return "DOUBLE PRECISION" + def visit_REAL(self, type_, **kw): return "REAL" @@ -5006,6 +5012,9 @@ class GenericTypeCompiler(TypeCompiler): def visit_float(self, type_, **kw): return self.visit_FLOAT(type_, **kw) + def visit_double(self, type_, **kw): + return self.visit_DOUBLE(type_, **kw) + def visit_numeric(self, type_, **kw): return self.visit_NUMERIC(type_, **kw) diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 819f1dc9a8..d022a24caa 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -578,7 +578,30 @@ class Float(Numeric): Construct a Float. :param precision: the numeric precision for use in DDL ``CREATE - TABLE``. + TABLE``. Backends **should** attempt to ensure this precision + indicates a number of digits for the generic + :class:`_sqltypes.Float` datatype. + + .. note:: For the Oracle backend, the + :paramref:`_sqltypes.Float.precision` parameter is not accepted + when rendering DDL, as Oracle does not support float precision + specified as a number of decimal places. Instead, use the + Oracle-specific :class:`_oracle.FLOAT` datatype and specify the + :paramref:`_oracle.FLOAT.binary_precision` parameter. This is new + in version 2.0 of SQLAlchemy. + + To create a database agnostic :class:`_types.Float` that + separately specifies binary precision for Oracle, use + :meth:`_types.TypeEngine.with_variant` as follows:: + + from sqlalchemy import Column + from sqlalchemy import Float + from sqlalchemy.dialects import oracle + + Column( + "float_data", + Float(5).with_variant(oracle.FLOAT(binary_precision=16), "oracle") + ) :param asdecimal: the same flag as that of :class:`.Numeric`, but defaults to ``False``. Note that setting this flag to ``True`` @@ -595,7 +618,7 @@ class Float(Numeric): .. versionadded:: 0.9.0 - """ + """ # noqa: E501 self.precision = precision self.asdecimal = asdecimal self.decimal_return_scale = decimal_return_scale @@ -611,6 +634,20 @@ class Float(Numeric): return None +class Double(Float): + """A type for double ``FLOAT`` floating point types. + + Typically generates a ``DOUBLE`` or ``DOUBLE_PRECISION`` in DDL, + and otherwise acts like a normal :class:`.Float` on the Python + side. + + .. versionadded:: 2.0 + + """ + + __visit_name__ = "double" + + class DateTime(_LookupExpressionAdapter, TypeEngine[dt.datetime]): """A type for ``datetime.datetime()`` objects. @@ -2769,35 +2806,93 @@ class TupleType(TypeEngine[Tuple[Any]]): class REAL(Float): - """The SQL REAL type.""" + """The SQL REAL type. + + .. seealso:: + + :class:`_types.Float` - documentation for the base type. + + """ __visit_name__ = "REAL" class FLOAT(Float): - """The SQL FLOAT type.""" + """The SQL FLOAT type. + + .. seealso:: + + :class:`_types.Float` - documentation for the base type. + + """ __visit_name__ = "FLOAT" +class DOUBLE(Double): + """The SQL DOUBLE type. + + .. versionadded:: 2.0 + + .. seealso:: + + :class:`_types.Double` - documentation for the base type. + + """ + + __visit_name__ = "DOUBLE" + + +class DOUBLE_PRECISION(Double): + """The SQL DOUBLE PRECISION type. + + .. versionadded:: 2.0 + + .. seealso:: + + :class:`_types.Double` - documentation for the base type. + + """ + + __visit_name__ = "DOUBLE_PRECISION" + + class NUMERIC(Numeric): - """The SQL NUMERIC type.""" + """The SQL NUMERIC type. + + .. seealso:: + + :class:`_types.Numeric` - documentation for the base type. + + """ __visit_name__ = "NUMERIC" class DECIMAL(Numeric): - """The SQL DECIMAL type.""" + """The SQL DECIMAL type. + + .. seealso:: + + :class:`_types.Numeric` - documentation for the base type. + + """ __visit_name__ = "DECIMAL" class INTEGER(Integer): - """The SQL INT or INTEGER type.""" + """The SQL INT or INTEGER type. + + .. seealso:: + + :class:`_types.Integer` - documentation for the base type. + + """ __visit_name__ = "INTEGER" @@ -2807,14 +2902,26 @@ INT = INTEGER class SMALLINT(SmallInteger): - """The SQL SMALLINT type.""" + """The SQL SMALLINT type. + + .. seealso:: + + :class:`_types.SmallInteger` - documentation for the base type. + + """ __visit_name__ = "SMALLINT" class BIGINT(BigInteger): - """The SQL BIGINT type.""" + """The SQL BIGINT type. + + .. seealso:: + + :class:`_types.BigInteger` - documentation for the base type. + + """ __visit_name__ = "BIGINT" diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py index 6f02d55577..fb12d23c8c 100644 --- a/lib/sqlalchemy/testing/suite/test_reflection.py +++ b/lib/sqlalchemy/testing/suite/test_reflection.py @@ -344,7 +344,7 @@ class ComponentReflectionTest(fixtures.TablesTest): metadata, Column("user_id", sa.INT, primary_key=True), Column("test1", sa.CHAR(5), nullable=False), - Column("test2", sa.Float(5), nullable=False), + Column("test2", sa.Float(), nullable=False), Column( "parent_user_id", sa.Integer, @@ -361,7 +361,7 @@ class ComponentReflectionTest(fixtures.TablesTest): metadata, Column("user_id", sa.INT, primary_key=True), Column("test1", sa.CHAR(5), nullable=False), - Column("test2", sa.Float(5), nullable=False), + Column("test2", sa.Float(), nullable=False), schema=schema, test_needs_fk=True, ) diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py index 94bab009a8..0940eab9b9 100644 --- a/lib/sqlalchemy/testing/suite/test_types.py +++ b/lib/sqlalchemy/testing/suite/test_types.py @@ -731,7 +731,7 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase): def test_render_literal_float(self, literal_round_trip): literal_round_trip( - Float(4), + Float(), [15.7563, decimal.Decimal("15.7563")], [15.7563], filter_=lambda n: n is not None and round(n, 5) or None, @@ -783,17 +783,17 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase): @testing.requires.floats_to_four_decimals def test_float_as_decimal(self, do_numeric_test): do_numeric_test( - Float(precision=8, asdecimal=True), - [15.7563, decimal.Decimal("15.7563"), None], - [decimal.Decimal("15.7563"), None], + Float(asdecimal=True), + [15.756, decimal.Decimal("15.756"), None], + [decimal.Decimal("15.756"), None], filter_=lambda n: n is not None and round(n, 4) or None, ) def test_float_as_float(self, do_numeric_test): do_numeric_test( - Float(precision=8), - [15.7563, decimal.Decimal("15.7563")], - [15.7563], + Float(), + [15.756, decimal.Decimal("15.756")], + [15.756], filter_=lambda n: n is not None and round(n, 5) or None, ) diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py index 9464cc9c4e..45e31aaf7f 100644 --- a/lib/sqlalchemy/types.py +++ b/lib/sqlalchemy/types.py @@ -28,6 +28,9 @@ from .sql.sqltypes import Date from .sql.sqltypes import DATETIME from .sql.sqltypes import DateTime from .sql.sqltypes import DECIMAL +from .sql.sqltypes import DOUBLE +from .sql.sqltypes import Double +from .sql.sqltypes import DOUBLE_PRECISION from .sql.sqltypes import Enum from .sql.sqltypes import FLOAT from .sql.sqltypes import Float @@ -105,6 +108,9 @@ __all__ = [ "BigInteger", "Numeric", "Float", + "Double", + "DOUBLE", + "DOUBLE_PRECISION", "DateTime", "Date", "Time", diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py index ba162b4902..fa8f37c1f8 100644 --- a/test/dialect/mysql/test_compiler.py +++ b/test/dialect/mysql/test_compiler.py @@ -15,6 +15,8 @@ from sqlalchemy import Date from sqlalchemy import DATETIME from sqlalchemy import DateTime from sqlalchemy import DECIMAL +from sqlalchemy import DOUBLE +from sqlalchemy import Double from sqlalchemy import exc from sqlalchemy import extract from sqlalchemy import FLOAT @@ -776,6 +778,8 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL): (Float, "t.col"), (m.MSFloat, "t.col"), (m.MSDouble, "t.col"), + (DOUBLE, "t.col"), + (Double, "t.col"), (m.MSReal, "t.col"), (m.MSYear, "t.col"), (m.MSYear(2), "t.col"), @@ -798,6 +802,8 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL): (m.FLOAT, "CAST(t.col AS FLOAT)"), (Float, "CAST(t.col AS FLOAT)"), (FLOAT, "CAST(t.col AS FLOAT)"), + (Double, "CAST(t.col AS DOUBLE)"), + (DOUBLE, "CAST(t.col AS DOUBLE)"), (m.DOUBLE, "CAST(t.col AS DOUBLE)"), (m.FLOAT, "CAST(t.col AS FLOAT)"), argnames="type_,expected", diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py index 22ffc888ab..c506c306e2 100644 --- a/test/dialect/oracle/test_compiler.py +++ b/test/dialect/oracle/test_compiler.py @@ -1,6 +1,7 @@ # coding: utf-8 from sqlalchemy import and_ from sqlalchemy import bindparam +from sqlalchemy import cast from sqlalchemy import Computed from sqlalchemy import exc from sqlalchemy import except_ @@ -23,6 +24,7 @@ from sqlalchemy import testing from sqlalchemy import text from sqlalchemy import type_coerce from sqlalchemy import TypeDecorator +from sqlalchemy import types as sqltypes from sqlalchemy import union from sqlalchemy.dialects.oracle import base as oracle from sqlalchemy.dialects.oracle import cx_oracle @@ -1380,6 +1382,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): dialect=dd, ) + def test_double_to_oracle_double(self): + """test #5465""" + d1 = sqltypes.Double + + self.assert_compile( + cast(column("foo"), d1), "CAST(foo AS DOUBLE PRECISION)" + ) + class SequenceTest(fixtures.TestBase, AssertsCompiledSQL): def test_basic(self): diff --git a/test/dialect/oracle/test_reflection.py b/test/dialect/oracle/test_reflection.py index df8dff30f8..215f4ecd5d 100644 --- a/test/dialect/oracle/test_reflection.py +++ b/test/dialect/oracle/test_reflection.py @@ -1,8 +1,10 @@ # coding: utf-8 +from sqlalchemy import Double from sqlalchemy import exc from sqlalchemy import FLOAT +from sqlalchemy import Float from sqlalchemy import ForeignKey from sqlalchemy import ForeignKeyConstraint from sqlalchemy import func @@ -19,10 +21,12 @@ from sqlalchemy import testing from sqlalchemy import text from sqlalchemy import Unicode from sqlalchemy import UniqueConstraint +from sqlalchemy.dialects import oracle from sqlalchemy.dialects.oracle.base import BINARY_DOUBLE from sqlalchemy.dialects.oracle.base import BINARY_FLOAT from sqlalchemy.dialects.oracle.base import DOUBLE_PRECISION from sqlalchemy.dialects.oracle.base import NUMBER +from sqlalchemy.dialects.oracle.base import REAL from sqlalchemy.testing import assert_warns from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ @@ -803,17 +807,24 @@ class TypeReflectionTest(fixtures.TestBase): connection, ): specs = [ - (DOUBLE_PRECISION(), FLOAT()), - # when binary_precision is supported - # (DOUBLE_PRECISION(), oracle.FLOAT(binary_precision=126)), + (DOUBLE_PRECISION(), DOUBLE_PRECISION()), + (Double(), DOUBLE_PRECISION()), + (REAL(), REAL()), (BINARY_DOUBLE(), BINARY_DOUBLE()), (BINARY_FLOAT(), BINARY_FLOAT()), - (FLOAT(5), FLOAT()), - # when binary_precision is supported - # (FLOAT(5), oracle.FLOAT(binary_precision=5),), - (FLOAT(), FLOAT()), - # when binary_precision is supported - # (FLOAT(5), oracle.FLOAT(binary_precision=126),), + (oracle.FLOAT(5), oracle.FLOAT(5)), + ( + Float(5).with_variant( + oracle.FLOAT(binary_precision=16), "oracle" + ), + oracle.FLOAT(16), + ), # using conversion + (FLOAT(), DOUBLE_PRECISION()), + # from https://docs.oracle.com/cd/B14117_01/server.101/b10758/sqlqr06.htm # noqa E501 + # DOUBLE PRECISION == precision 126 + # REAL == precision 63 + (oracle.FLOAT(126), DOUBLE_PRECISION()), + (oracle.FLOAT(63), REAL()), ] self._run_test(metadata, connection, specs, ["precision"]) diff --git a/test/dialect/oracle/test_types.py b/test/dialect/oracle/test_types.py index 715a90000c..d3c522f259 100644 --- a/test/dialect/oracle/test_types.py +++ b/test/dialect/oracle/test_types.py @@ -12,7 +12,10 @@ from sqlalchemy import CHAR from sqlalchemy import DATE from sqlalchemy import Date from sqlalchemy import DateTime +from sqlalchemy import Double +from sqlalchemy import DOUBLE_PRECISION from sqlalchemy import event +from sqlalchemy import exc from sqlalchemy import FLOAT from sqlalchemy import Float from sqlalchemy import Integer @@ -41,6 +44,7 @@ from sqlalchemy.sql.sqltypes import NullType from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import eq_ +from sqlalchemy.testing import expect_raises_message from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ from sqlalchemy.testing import mock @@ -300,6 +304,18 @@ class TypesTest(fixtures.TestBase): datetime.timedelta(days=35, seconds=5743), ) + def test_no_decimal_float_precision(self): + with expect_raises_message( + exc.ArgumentError, + "Oracle FLOAT types use 'binary precision', which does not " + "convert cleanly from decimal 'precision'. Please specify this " + "type with a separate Oracle variant, such as " + r"FLOAT\(precision=5\).with_variant\(oracle.FLOAT\(" + r"binary_precision=16\), 'oracle'\), so that the Oracle " + "specific 'binary_precision' may be specified accurately.", + ): + FLOAT(5).compile(dialect=oracle.dialect()) + def test_numerics(self, metadata, connection): m = metadata t1 = Table( @@ -309,7 +325,8 @@ class TypesTest(fixtures.TestBase): Column("numericcol", Numeric(precision=9, scale=2)), Column("floatcol1", Float()), Column("floatcol2", FLOAT()), - Column("doubleprec", oracle.DOUBLE_PRECISION), + Column("doubleprec1", DOUBLE_PRECISION), + Column("doubleprec2", Double()), Column("numbercol1", oracle.NUMBER(9)), Column("numbercol2", oracle.NUMBER(9, 3)), Column("numbercol3", oracle.NUMBER), @@ -322,7 +339,8 @@ class TypesTest(fixtures.TestBase): numericcol=5.2, floatcol1=6.5, floatcol2=8.5, - doubleprec=9.5, + doubleprec1=9.5, + doubleprec2=14.5, numbercol1=12, numbercol2=14.85, numbercol3=15.76, @@ -343,6 +361,7 @@ class TypesTest(fixtures.TestBase): (6.5, float), (8.5, float), (9.5, float), + (14.5, float), (12, int), (decimal.Decimal("14.85"), decimal.Decimal), (15.76, float), @@ -1154,7 +1173,7 @@ class SetInputSizesTest(fixtures.TestBase): (SmallInteger, 25, int, False), (Integer, 25, int, False), (Numeric(10, 8), decimal.Decimal("25.34534"), None, False), - (Float(15), 25.34534, None, False), + (oracle.FLOAT(15), 25.34534, None, False), (oracle.BINARY_DOUBLE, 25.34534, "NATIVE_FLOAT", False), (oracle.BINARY_FLOAT, 25.34534, "NATIVE_FLOAT", False), (oracle.DOUBLE_PRECISION, 25.34534, None, False), diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index b98d0fac6a..98b974ea53 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -243,6 +243,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "CAST(bar AS someschema.somename) AS bar", ) + def test_cast_double_pg_double(self): + """test #5465: + + test sqlalchemy Double/DOUBLE to PostgreSQL DOUBLE PRECISION + """ + d1 = sqltypes.Double + + stmt = select(cast(column("foo"), d1)) + self.assert_compile( + stmt, "SELECT CAST(foo AS DOUBLE PRECISION) AS foo" + ) + def test_cast_enum_schema_translate(self): """test #6739""" e1 = Enum("x", "y", "z", name="somename") diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index 0c00c76333..a59dd0ac7e 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -12,6 +12,7 @@ from sqlalchemy import cast from sqlalchemy import Column from sqlalchemy import column from sqlalchemy import DateTime +from sqlalchemy import Double from sqlalchemy import Enum from sqlalchemy import exc from sqlalchemy import Float @@ -127,14 +128,16 @@ class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults): Column("x", postgresql.ARRAY(Float)), Column("y", postgresql.ARRAY(REAL)), Column("z", postgresql.ARRAY(postgresql.DOUBLE_PRECISION)), + Column("w", postgresql.ARRAY(Double)), Column("q", postgresql.ARRAY(Numeric)), ) metadata.create_all(connection) connection.execute( - t1.insert(), dict(x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")]) + t1.insert(), + dict(x=[5], y=[5], z=[6], w=[7], q=[decimal.Decimal("6.4")]), ) row = connection.execute(t1.select()).first() - eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")])) + eq_(row, ([5], [5], [6], [7], [decimal.Decimal("6.4")])) def test_arrays_base(self, connection, metadata): t1 = Table( @@ -143,14 +146,16 @@ class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults): Column("x", sqltypes.ARRAY(Float)), Column("y", sqltypes.ARRAY(REAL)), Column("z", sqltypes.ARRAY(postgresql.DOUBLE_PRECISION)), + Column("w", sqltypes.ARRAY(Double)), Column("q", sqltypes.ARRAY(Numeric)), ) metadata.create_all(connection) connection.execute( - t1.insert(), dict(x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")]) + t1.insert(), + dict(x=[5], y=[5], z=[6], w=[7], q=[decimal.Decimal("6.4")]), ) row = connection.execute(t1.select()).first() - eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")])) + eq_(row, ([5], [5], [6], [7], [decimal.Decimal("6.4")])) class EnumTest(fixtures.TestBase, AssertsExecutionResults): diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py index 42c83d87b4..d2e3d5f40b 100644 --- a/test/engine/test_reflection.py +++ b/test/engine/test_reflection.py @@ -50,7 +50,7 @@ class ReflectionTest(fixtures.TestBase, ComparesTables): Column("user_id", sa.INT, primary_key=True), Column("user_name", sa.VARCHAR(20), nullable=False), Column("test1", sa.CHAR(5), nullable=False), - Column("test2", sa.Float(5), nullable=False), + Column("test2", sa.Float(), nullable=False), Column("test3", sa.Text), Column("test4", sa.Numeric(10, 2), nullable=False), Column("test5", sa.Date), @@ -1830,7 +1830,7 @@ def createTables(meta, schema=None): Column("user_id", sa.INT, primary_key=True), Column("user_name", sa.VARCHAR(20), nullable=False), Column("test1", sa.CHAR(5), nullable=False), - Column("test2", sa.Float(5), nullable=False), + Column("test2", sa.Float(), nullable=False), Column("test3", sa.Text), Column("test4", sa.Numeric(10, 2), nullable=False), Column("test5", sa.Date), diff --git a/test/sql/test_types.py b/test/sql/test_types.py index f441c3b463..4e1048cb69 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -23,6 +23,7 @@ from sqlalchemy import DateTime from sqlalchemy import DECIMAL from sqlalchemy import dialects from sqlalchemy import distinct +from sqlalchemy import Double from sqlalchemy import Enum from sqlalchemy import exc from sqlalchemy import FLOAT @@ -277,6 +278,7 @@ class AdaptTest(fixtures.TestBase): eq_(types.Numeric(asdecimal=False).python_type, float) eq_(types.LargeBinary().python_type, bytes) eq_(types.Float().python_type, float) + eq_(types.Double().python_type, float) eq_(types.Interval().python_type, datetime.timedelta) eq_(types.Date().python_type, datetime.date) eq_(types.DateTime().python_type, datetime.datetime) @@ -3455,6 +3457,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): dialects.postgresql.FLOAT(), "FLOAT", allow_dialect_select=True ) + def test_default_compile_double(self): + self.assert_compile(Double(), "DOUBLE") + def test_default_compile_mysql_integer(self): self.assert_compile( dialects.mysql.INTEGER(display_width=5), -- 2.47.2