Skip to content

Commit b9d2318

Browse files
zeeeeebzzzeek
zeeeeeb
authored and
zzzeek
committed
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: #7674 Pull-request-sha: 5c68419 Change-Id: I831f4af3ee3b23fde02e8f6393c83e23dd7cd34d
1 parent 0353a9d commit b9d2318

25 files changed

Lines changed: 419 additions & 60 deletions

File tree

‎doc/build/changelog/migration_20.rst‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -340,6 +340,49 @@ customizable via the :paramref:`_sa.create_engine.poolclass` parameter.
340340

341341
:ticket:`7490`
342342

343+
.. _change_5465_oracle:
344+
345+
New Oracle FLOAT type with binary precision; decimal precision not accepted directly
346+
------------------------------------------------------------------------------------
347+
348+
A new datatype :class:`_oracle.FLOAT` has been added to the Oracle dialect, to
349+
accompany the addition of :class:`_sqltypes.Double` and database-specific
350+
:class:`_sqltypes.DOUBLE`, :class:`_sqltypes.DOUBLE_PRECISION` and
351+
:class:`_sqltypes.REAL` datatypes. Oracle's ``FLOAT`` accepts a so-called
352+
"binary precision" parameter that per Oracle documentation is roughly a
353+
standard "precision" value divided by 0.3103::
354+
355+
from sqlalchemy.dialects import oracle
356+
357+
Table(
358+
"some_table", metadata,
359+
Column("value", oracle.FLOAT(126))
360+
)
361+
362+
A binary precision value of 126 is synonymous with using the
363+
:class:`_sqltypes.DOUBLE_PRECISION` datatype, and a value of 63 is equivalent
364+
to using the :class:`_sqltypes.REAL` datatype. Other precision values are
365+
specific to the :class:`_oracle.FLOAT` type itself.
366+
367+
The SQLAlchemy :class:`_sqltypes.Float` datatype also accepts a "precision"
368+
parameter, but this is decimal precision which is not accepted by
369+
Oracle. Rather than attempting to guess the conversion, the Oracle dialect
370+
will now raise an informative error if :class:`_sqltypes.Float` is used with
371+
a precision value against the Oracle backend. To specify a
372+
:class:`_sqltypes.Float` datatype with an explicit precision value for
373+
supporting backends, while also supporting other backends, use
374+
the :meth:`_types.TypeEngine.with_variant` method as follows::
375+
376+
from sqlalchemy.types import Float
377+
from sqlalchemy.dialects import oracle
378+
379+
Table(
380+
"some_table", metadata,
381+
Column("value", Float(5).with_variant(oracle.FLOAT(16), "oracle"))
382+
)
383+
384+
385+
343386
.. _migration_20_overview:
344387

345388
1.x -> 2.x Migration Overview

‎doc/build/changelog/unreleased_20/5465.rst‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
.. change::
2+
:tags: feature, types
3+
:tickets: 5465
4+
5+
Added :class:`.Double`, :class:`.DOUBLE`, :class:`.DOUBLE_PRECISION`
6+
datatypes to the base ``sqlalchemy.`` module namespace, for explicit use of
7+
double/double precision as well as generic "double" datatypes. Use
8+
:class:`.Double` for generic support that will resolve to DOUBLE/DOUBLE
9+
PRECISION/FLOAT as needed for different backends.
10+
11+
12+
.. change::
13+
:tags: feature, oracle
14+
:tickets: 5465
15+
16+
Implemented DDL and reflection support for ``FLOAT`` datatypes which
17+
include an explicit "binary_precision" value. Using the Oracle-specific
18+
:class:`_oracle.FLOAT` datatype, the new parameter
19+
:paramref:`_oracle.FLOAT.binary_precision` may be specified which will
20+
render Oracle's precision for floating point types directly. This value is
21+
interpreted during reflection. Upon reflecting back a ``FLOAT`` datatype,
22+
the datatype returned is one of :class:`_types.DOUBLE_PRECISION` for a
23+
``FLOAT`` for a precision of 126 (this is also Oracle's default precision
24+
for ``FLOAT``), :class:`_types.REAL` for a precision of 63, and
25+
:class:`_oracle.FLOAT` for a custom precision, as per Oracle documentation.
26+
27+
As part of this change, the generic :paramref:`_sqltypes.Float.precision`
28+
value is explicitly rejected when generating DDL for Oracle, as this
29+
precision cannot be accurately converted to "binary precision"; instead, an
30+
error message encourages the use of
31+
:meth:`_sqltypes.TypeEngine.with_variant` so that Oracle's specific form of
32+
precision may be chosen exactly. This is a backwards-incompatible change in
33+
behavior, as the previous "precision" value was silently ignored for
34+
Oracle.
35+
36+
.. seealso::
37+
38+
:ref:`change_5465_oracle`

‎doc/build/conf.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -148,6 +148,7 @@
148148
"_row": "sqlalchemy.engine",
149149
"_schema": "sqlalchemy.schema",
150150
"_types": "sqlalchemy.types",
151+
"_sqltypes": "sqlalchemy.types",
151152
"_asyncio": "sqlalchemy.ext.asyncio",
152153
"_expression": "sqlalchemy.sql.expression",
153154
"_sql": "sqlalchemy.sql.expression",

‎doc/build/core/type_basics.rst‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,9 @@ type is emitted in ``CREATE TABLE``, such as ``VARCHAR`` see
5252
.. autoclass:: Enum
5353
:members: __init__, create, drop
5454

55+
.. autoclass:: Double
56+
:members:
57+
5558
.. autoclass:: Float
5659
:members:
5760

@@ -137,6 +140,9 @@ its exact name in DDL with ``CREATE TABLE`` is issued.
137140

138141
.. autoclass:: DECIMAL
139142

143+
.. autoclass:: DOUBLE
144+
145+
.. autoclass:: DOUBLE_PRECISION
140146

141147
.. autoclass:: FLOAT
142148

‎doc/build/dialects/oracle.rst‎

Lines changed: 1 addition & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -32,26 +32,21 @@ construction arguments, are as follows:
3232
.. autoclass:: DATE
3333
:members: __init__
3434

35-
.. autoclass:: DOUBLE_PRECISION
35+
.. autoclass:: FLOAT
3636
:members: __init__
3737

38-
3938
.. autoclass:: INTERVAL
4039
:members: __init__
4140

42-
4341
.. autoclass:: NCLOB
4442
:members: __init__
4543

46-
4744
.. autoclass:: NUMBER
4845
:members: __init__
4946

50-
5147
.. autoclass:: LONG
5248
:members: __init__
5349

54-
5550
.. autoclass:: RAW
5651
:members: __init__
5752

‎lib/sqlalchemy/__init__.py‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -223,6 +223,9 @@
223223
from .types import DATETIME as DATETIME
224224
from .types import DateTime as DateTime
225225
from .types import DECIMAL as DECIMAL
226+
from .types import DOUBLE as DOUBLE
227+
from .types import Double as Double
228+
from .types import DOUBLE_PRECISION as DOUBLE_PRECISION
226229
from .types import Enum as Enum
227230
from .types import FLOAT as FLOAT
228231
from .types import Float as Float

‎lib/sqlalchemy/dialects/mysql/base.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1082,6 +1082,7 @@ class MyClass(Base):
10821082
_FloatType: _FloatType,
10831083
sqltypes.Numeric: NUMERIC,
10841084
sqltypes.Float: FLOAT,
1085+
sqltypes.Double: DOUBLE,
10851086
sqltypes.Time: TIME,
10861087
sqltypes.Enum: ENUM,
10871088
sqltypes.MatchType: _MatchType,

‎lib/sqlalchemy/dialects/mysql/types.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -152,7 +152,7 @@ def __init__(self, precision=None, scale=None, asdecimal=True, **kw):
152152
)
153153

154154

155-
class DOUBLE(_FloatType):
155+
class DOUBLE(_FloatType, sqltypes.DOUBLE):
156156
"""MySQL DOUBLE type."""
157157

158158
__visit_name__ = "DOUBLE"

‎lib/sqlalchemy/dialects/oracle/__init__.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
from .base import NVARCHAR
2525
from .base import NVARCHAR2
2626
from .base import RAW
27+
from .base import REAL
2728
from .base import ROWID
2829
from .base import TIMESTAMP
2930
from .base import VARCHAR

‎lib/sqlalchemy/dialects/oracle/base.py‎

Lines changed: 94 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -558,10 +558,12 @@ class MyClass(Base):
558558
from ...types import BLOB
559559
from ...types import CHAR
560560
from ...types import CLOB
561+
from ...types import DOUBLE_PRECISION
561562
from ...types import FLOAT
562563
from ...types import INTEGER
563564
from ...types import NCHAR
564565
from ...types import NVARCHAR
566+
from ...types import REAL
565567
from ...types import TIMESTAMP
566568
from ...types import VARCHAR
567569

@@ -625,8 +627,49 @@ def _type_affinity(self):
625627
return sqltypes.Integer
626628

627629

628-
class DOUBLE_PRECISION(sqltypes.Float):
629-
__visit_name__ = "DOUBLE_PRECISION"
630+
class FLOAT(sqltypes.FLOAT):
631+
"""Oracle FLOAT.
632+
633+
This is the same as :class:`_sqltypes.FLOAT` except that
634+
an Oracle-specific :paramref:`_oracle.FLOAT.binary_precision`
635+
parameter is accepted, and
636+
the :paramref:`_sqltypes.Float.precision` parameter is not accepted.
637+
638+
Oracle FLOAT types indicate precision in terms of "binary precision", which
639+
defaults to 126. For a REAL type, the value is 63. This parameter does not
640+
cleanly map to a specific number of decimal places but is roughly
641+
equivalent to the desired number of decimal places divided by 0.3103.
642+
643+
.. versionadded:: 2.0
644+
645+
"""
646+
647+
__visit_name__ = "FLOAT"
648+
649+
def __init__(
650+
self,
651+
binary_precision=None,
652+
asdecimal=False,
653+
decimal_return_scale=None,
654+
):
655+
r"""
656+
Construct a FLOAT
657+
658+
:param binary_precision: Oracle binary precision value to be rendered
659+
in DDL. This may be approximated to the number of decimal characters
660+
using the formula "decimal precision = 0.30103 * binary precision".
661+
The default value used by Oracle for FLOAT / DOUBLE PRECISION is 126.
662+
663+
:param asdecimal: See :paramref:`_sqltypes.Float.asdecimal`
664+
665+
:param decimal_return_scale: See
666+
:paramref:`_sqltypes.Float.decimal_return_scale`
667+
668+
"""
669+
super().__init__(
670+
asdecimal=asdecimal, decimal_return_scale=decimal_return_scale
671+
)
672+
self.binary_precision = binary_precision
630673

631674

632675
class BINARY_DOUBLE(sqltypes.Float):
@@ -742,6 +785,7 @@ def get_dbapi_type(self, dbapi):
742785
"RAW": RAW,
743786
"FLOAT": FLOAT,
744787
"DOUBLE PRECISION": DOUBLE_PRECISION,
788+
"REAL": REAL,
745789
"LONG": LONG,
746790
"BINARY_DOUBLE": BINARY_DOUBLE,
747791
"BINARY_FLOAT": BINARY_FLOAT,
@@ -760,6 +804,9 @@ def visit_datetime(self, type_, **kw):
760804
def visit_float(self, type_, **kw):
761805
return self.visit_FLOAT(type_, **kw)
762806

807+
def visit_double(self, type_, **kw):
808+
return self.visit_DOUBLE_PRECISION(type_, **kw)
809+
763810
def visit_unicode(self, type_, **kw):
764811
if self.dialect._use_nchar_for_unicode:
765812
return self.visit_NVARCHAR2(type_, **kw)
@@ -795,24 +842,50 @@ def visit_BINARY_FLOAT(self, type_, **kw):
795842
return self._generate_numeric(type_, "BINARY_FLOAT", **kw)
796843

797844
def visit_FLOAT(self, type_, **kw):
798-
# don't support conversion between decimal/binary
799-
# precision yet
800-
kw["no_precision"] = True
845+
kw["_requires_binary_precision"] = True
801846
return self._generate_numeric(type_, "FLOAT", **kw)
802847

803848
def visit_NUMBER(self, type_, **kw):
804849
return self._generate_numeric(type_, "NUMBER", **kw)
805850

806851
def _generate_numeric(
807-
self, type_, name, precision=None, scale=None, no_precision=False, **kw
852+
self,
853+
type_,
854+
name,
855+
precision=None,
856+
scale=None,
857+
_requires_binary_precision=False,
858+
**kw,
808859
):
809860
if precision is None:
810-
precision = type_.precision
861+
862+
precision = getattr(type_, "precision", None)
863+
864+
if _requires_binary_precision:
865+
binary_precision = getattr(type_, "binary_precision", None)
866+
867+
if precision and binary_precision is None:
868+
# https://www.oracletutorial.com/oracle-basics/oracle-float/
869+
estimated_binary_precision = int(precision / 0.30103)
870+
raise exc.ArgumentError(
871+
"Oracle FLOAT types use 'binary precision', which does "
872+
"not convert cleanly from decimal 'precision'. Please "
873+
"specify "
874+
f"this type with a separate Oracle variant, such as "
875+
f"{type_.__class__.__name__}(precision={precision})."
876+
f"with_variant(oracle.FLOAT"
877+
f"(binary_precision="
878+
f"{estimated_binary_precision}), 'oracle'), so that the "
879+
"Oracle specific 'binary_precision' may be specified "
880+
"accurately."
881+
)
882+
else:
883+
precision = binary_precision
811884

812885
if scale is None:
813886
scale = getattr(type_, "scale", None)
814887

815-
if no_precision or precision is None:
888+
if precision is None:
816889
return name
817890
elif scale is None:
818891
n = "%(name)s(%(precision)s)"
@@ -1964,8 +2037,19 @@ def get_columns(self, connection, table_name, schema=None, **kw):
19642037
else:
19652038
coltype = NUMBER(precision, scale)
19662039
elif coltype == "FLOAT":
1967-
# TODO: support "precision" here as "binary_precision"
1968-
coltype = FLOAT()
2040+
# https://docs.oracle.com/cd/B14117_01/server.101/b10758/sqlqr06.htm
2041+
if precision == 126:
2042+
# The DOUBLE PRECISION datatype is a floating-point
2043+
# number with binary precision 126.
2044+
coltype = DOUBLE_PRECISION()
2045+
elif precision == 63:
2046+
# The REAL datatype is a floating-point number with a
2047+
# binary precision of 63, or 18 decimal.
2048+
coltype = REAL()
2049+
else:
2050+
# non standard precision
2051+
coltype = FLOAT(binary_precision=precision)
2052+
19692053
elif coltype in ("VARCHAR2", "NVARCHAR2", "CHAR", "NCHAR"):
19702054
coltype = self.ischema_names.get(coltype)(length)
19712055
elif "WITH TIME ZONE" in coltype:

0 commit comments

Comments
 (0)