summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMike Bayer <mike_mp@zzzcomputing.com>2013-11-22 20:04:19 -0500
committerMike Bayer <mike_mp@zzzcomputing.com>2013-11-22 20:04:19 -0500
commit6b79d2ea7951abc2bb6083b541db0fbf71590dd3 (patch)
tree4c1edc6856fe743b44e69c1f70750d469b642ed5
parentf112dc1d533033f19186eb65227aba1660d03102 (diff)
downloadsqlalchemy-6b79d2ea7951abc2bb6083b541db0fbf71590dd3.tar.gz
- The precision used when coercing a returned floating point value to
Python ``Decimal`` via string is now configurable. The flag ``decimal_return_scale`` is now supported by all :class:`.Numeric` and :class:`.Float` types, which will ensure this many digits are taken from the native floating point value when it is converted to string. If not present, the type will make use of the value of ``.scale``, if the type supports this setting and it is non-None. Otherwise the original default length of 10 is used. [ticket:2867]
-rw-r--r--doc/build/changelog/changelog_09.rst17
-rw-r--r--doc/build/changelog/migration_09.rst44
-rw-r--r--lib/sqlalchemy/dialects/mysql/base.py19
-rw-r--r--lib/sqlalchemy/dialects/oracle/cx_oracle.py5
-rw-r--r--lib/sqlalchemy/dialects/postgresql/pg8000.py3
-rw-r--r--lib/sqlalchemy/dialects/postgresql/psycopg2.py3
-rw-r--r--lib/sqlalchemy/processors.py4
-rw-r--r--lib/sqlalchemy/sql/sqltypes.py52
-rw-r--r--lib/sqlalchemy/testing/requirements.py8
-rw-r--r--lib/sqlalchemy/testing/suite/test_types.py10
-rw-r--r--test/dialect/mysql/test_types.py19
-rw-r--r--test/requirements.py7
12 files changed, 172 insertions, 19 deletions
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index ae6206b2f..cb37cb990 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -15,6 +15,23 @@
:version: 0.9.0b2
.. change::
+ :tags: feature, sql
+ :tickets: 2867
+
+ The precision used when coercing a returned floating point value to
+ Python ``Decimal`` via string is now configurable. The
+ flag ``decimal_return_scale`` is now supported by all :class:`.Numeric`
+ and :class:`.Float` types, which will ensure this many digits are taken
+ from the native floating point value when it is converted to string.
+ If not present, the type will make use of the value of ``.scale``, if
+ the type supports this setting and it is non-None. Otherwise the original
+ default length of 10 is used.
+
+ .. seealso::
+
+ :ref:`feature_2867`
+
+ .. change::
:tags: bug, schema
:tickets: 2868
diff --git a/doc/build/changelog/migration_09.rst b/doc/build/changelog/migration_09.rst
index 52af78824..4e9112f87 100644
--- a/doc/build/changelog/migration_09.rst
+++ b/doc/build/changelog/migration_09.rst
@@ -877,6 +877,50 @@ rendering::
:ticket:`722`
+.. _feature_2867:
+
+Floating Point String-Conversion Precision Configurable for Native Floating Point Types
+---------------------------------------------------------------------------------------
+
+The conversion which SQLAlchemy does whenever a DBAPI returns a Python
+floating point type which is to be converted into a Python ``Decimal()``
+necessarily involves an intermediary step which converts the floating point
+value to a string. The scale used for this string conversion was previously
+hardcoded to 10, and is now configurable. The setting is available on
+both the :class:`.Numeric` as well as the :class:`.Float`
+type, as well as all SQL- and dialect-specific descendant types, using the
+parameter ``decimal_return_scale``. If the type supports a ``.scale`` parameter,
+as is the case with :class:`.Numeric` and some float types such as
+:class:`.mysql.DOUBLE`, the value of ``.scale`` is used as the default
+for ``.decimal_return_scale`` if it is not otherwise specified. If both
+``.scale`` and ``.decimal_return_scale`` are absent, then the default of
+10 takes place. E.g.::
+
+ from sqlalchemy.dialects.mysql import DOUBLE
+ import decimal
+
+ data = Table('data', metadata,
+ Column('double_value',
+ mysql.DOUBLE(decimal_return_scale=12, asdecimal=True))
+ )
+
+ conn.execute(
+ data.insert(),
+ double_value=45.768392065789,
+ )
+ result = conn.scalar(select([data.c.double_value]))
+
+ # previously, this would typically be Decimal("45.7683920658"),
+ # e.g. trimmed to 10 decimal places
+
+ # now we get 12, as requested, as MySQL can support this
+ # much precision for DOUBLE
+ assert result == decimal.Decimal("45.768392065789")
+
+
+:ticket:`2867`
+
+
.. _change_2824:
Column Bundles for ORM queries
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 6883be5af..6ffc1319a 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -398,7 +398,8 @@ class _FloatType(_NumericType, sqltypes.Float):
raise exc.ArgumentError(
"You must specify both precision and scale or omit "
"both altogether.")
-
+ if scale is not None:
+ kw.setdefault('decimal_return_scale', scale)
super(_FloatType, self).__init__(precision=precision, asdecimal=asdecimal, **kw)
self.scale = scale
@@ -490,6 +491,14 @@ class DOUBLE(_FloatType):
def __init__(self, precision=None, scale=None, asdecimal=True, **kw):
"""Construct a DOUBLE.
+ .. note::
+
+ The :class:`.DOUBLE` type by default converts from float
+ to Decimal, using a truncation that defaults to 10 digits. Specify
+ either ``scale=n`` or ``decimal_return_scale=n`` in order to change
+ this scale, or ``asdecimal=False`` to return values directly as
+ Python floating points.
+
:param precision: Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.
@@ -515,6 +524,14 @@ class REAL(_FloatType, sqltypes.REAL):
def __init__(self, precision=None, scale=None, asdecimal=True, **kw):
"""Construct a REAL.
+ .. note::
+
+ The :class:`.REAL` type by default converts from float
+ to Decimal, using a truncation that defaults to 10 digits. Specify
+ either ``scale=n`` or ``decimal_return_scale=n`` in order to change
+ this scale, or ``asdecimal=False`` to return values directly as
+ Python floating points.
+
:param precision: Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index d59aab8f7..0c6d257dc 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -232,10 +232,7 @@ class _OracleNumeric(sqltypes.Numeric):
if dialect.supports_native_decimal:
if self.asdecimal:
- if self.scale is None:
- fstring = "%.10f"
- else:
- fstring = "%%.%df" % self.scale
+ fstring = "%%.%df" % self.decimal_return_scale
def to_decimal(value):
if value is None:
diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py
index 0e503746c..cd9c545f3 100644
--- a/lib/sqlalchemy/dialects/postgresql/pg8000.py
+++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py
@@ -39,7 +39,8 @@ class _PGNumeric(sqltypes.Numeric):
def result_processor(self, dialect, coltype):
if self.asdecimal:
if coltype in _FLOAT_TYPES:
- return processors.to_decimal_processor_factory(decimal.Decimal)
+ return processors.to_decimal_processor_factory(
+ decimal.Decimal, self.decimal_return_scale)
elif coltype in _DECIMAL_TYPES or coltype in _INT_TYPES:
# pg8000 returns Decimal natively for 1700
return None
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 02eda094e..9995a1f5a 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -191,7 +191,8 @@ class _PGNumeric(sqltypes.Numeric):
def result_processor(self, dialect, coltype):
if self.asdecimal:
if coltype in _FLOAT_TYPES:
- return processors.to_decimal_processor_factory(decimal.Decimal)
+ return processors.to_decimal_processor_factory(
+ decimal.Decimal, self.decimal_return_scale)
elif coltype in _DECIMAL_TYPES or coltype in _INT_TYPES:
# pg8000 returns Decimal natively for 1700
return None
diff --git a/lib/sqlalchemy/processors.py b/lib/sqlalchemy/processors.py
index bf95d146b..f51bdfdee 100644
--- a/lib/sqlalchemy/processors.py
+++ b/lib/sqlalchemy/processors.py
@@ -66,7 +66,7 @@ def py_fallback():
return decoder(value, errors)[0]
return process
- def to_decimal_processor_factory(target_class, scale=10):
+ def to_decimal_processor_factory(target_class, scale):
fstring = "%%.%df" % scale
def process(value):
@@ -119,7 +119,7 @@ try:
else:
return UnicodeResultProcessor(encoding).process
- def to_decimal_processor_factory(target_class, scale=10):
+ def to_decimal_processor_factory(target_class, scale):
# Note that the scale argument is not taken into account for integer
# values in the C implementation while it is in the Python one.
# For example, the Python implementation might return
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 8f22ae81c..7cf5a6dca 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -409,6 +409,7 @@ class BigInteger(Integer):
__visit_name__ = 'big_integer'
+
class Numeric(_DateAffinity, TypeEngine):
"""A type for fixed precision numbers.
@@ -453,7 +454,10 @@ class Numeric(_DateAffinity, TypeEngine):
__visit_name__ = 'numeric'
- def __init__(self, precision=None, scale=None, asdecimal=True):
+ _default_decimal_return_scale = 10
+
+ def __init__(self, precision=None, scale=None,
+ decimal_return_scale=None, asdecimal=True):
"""
Construct a Numeric.
@@ -468,6 +472,18 @@ class Numeric(_DateAffinity, TypeEngine):
datatypes - the Numeric type will ensure that return values
are one or the other across DBAPIs consistently.
+ :param decimal_return_scale: Default scale to use when converting
+ from floats to Python decimals. Floating point values will typically
+ be much longer due to decimal inaccuracy, and most floating point
+ database types don't have a notion of "scale", so by default the
+ float type looks for the first ten decimal places when converting.
+ Specfiying this value will override that length. Types which
+ do include an explicit ".scale" value, such as the base :class:`.Numeric`
+ as well as the MySQL float types, will use the value of ".scale"
+ as the default for decimal_return_scale, if not otherwise specified.
+
+ .. versionadded:: 0.9.0
+
When using the ``Numeric`` type, care should be taken to ensure
that the asdecimal setting is apppropriate for the DBAPI in use -
when Numeric applies a conversion from Decimal->float or float->
@@ -487,6 +503,10 @@ class Numeric(_DateAffinity, TypeEngine):
"""
self.precision = precision
self.scale = scale
+ self.decimal_return_scale = decimal_return_scale \
+ if decimal_return_scale is not None \
+ else self.scale if self.scale is not None \
+ else self._default_decimal_return_scale
self.asdecimal = asdecimal
def get_dbapi_type(self, dbapi):
@@ -525,12 +545,10 @@ class Numeric(_DateAffinity, TypeEngine):
'storage.' % (dialect.name, dialect.driver))
# we're a "numeric", DBAPI returns floats, convert.
- if self.scale is not None:
- return processors.to_decimal_processor_factory(
- decimal.Decimal, self.scale)
- else:
- return processors.to_decimal_processor_factory(
- decimal.Decimal)
+ return processors.to_decimal_processor_factory(
+ decimal.Decimal,
+ self.scale if self.scale is not None
+ else self._default_decimal_return_scale)
else:
if dialect.supports_native_decimal:
return processors.to_float
@@ -576,7 +594,8 @@ class Float(Numeric):
scale = None
- def __init__(self, precision=None, asdecimal=False, **kwargs):
+ def __init__(self, precision=None, asdecimal=False,
+ decimal_return_scale=None, **kwargs):
"""
Construct a Float.
@@ -587,6 +606,17 @@ class Float(Numeric):
defaults to ``False``. Note that setting this flag to ``True``
results in floating point conversion.
+ :param decimal_return_scale: Default scale to use when converting
+ from floats to Python decimals. Floating point values will typically
+ be much longer due to decimal inaccuracy, and most floating point
+ database types don't have a notion of "scale", so by default the
+ float type looks for the first ten decimal places when converting.
+ Specfiying this value will override that length. Note that the
+ MySQL float types, which do include "scale", will use "scale"
+ as the default for decimal_return_scale, if not otherwise specified.
+
+ .. versionadded:: 0.9.0
+
:param \**kwargs: deprecated. Additional arguments here are ignored
by the default :class:`.Float` type. For database specific
floats that support additional arguments, see that dialect's
@@ -596,13 +626,17 @@ class Float(Numeric):
"""
self.precision = precision
self.asdecimal = asdecimal
+ self.decimal_return_scale = decimal_return_scale \
+ if decimal_return_scale is not None \
+ else self._default_decimal_return_scale
if kwargs:
util.warn_deprecated("Additional keyword arguments "
"passed to Float ignored.")
def result_processor(self, dialect, coltype):
if self.asdecimal:
- return processors.to_decimal_processor_factory(decimal.Decimal)
+ return processors.to_decimal_processor_factory(
+ decimal.Decimal, self.decimal_return_scale)
else:
return None
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 408c3705e..e48fa2c00 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -394,6 +394,14 @@ class SuiteRequirements(Requirements):
return exclusions.closed()
@property
+ def precision_generic_float_type(self):
+ """target backend will return native floating point numbers with at
+ least seven decimal places when using the generic Float type.
+
+ """
+ return exclusions.open()
+
+ @property
def floats_to_four_decimals(self):
"""target backend can return a floating-point number with four
significant digits (such as 15.7563) accurately
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 3eb105ba3..b147f891a 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -356,6 +356,16 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase):
filter_=lambda n: n is not None and round(n, 5) or None
)
+
+ @testing.requires.precision_generic_float_type
+ def test_float_custom_scale(self):
+ self._do_test(
+ Float(None, decimal_return_scale=7, asdecimal=True),
+ [15.7563827, decimal.Decimal("15.7563827")],
+ [decimal.Decimal("15.7563827"),],
+ check_scale=True
+ )
+
def test_numeric_as_decimal(self):
self._do_test(
Numeric(precision=8, scale=4),
diff --git a/test/dialect/mysql/test_types.py b/test/dialect/mysql/test_types.py
index ec7b69926..014d29d69 100644
--- a/test/dialect/mysql/test_types.py
+++ b/test/dialect/mysql/test_types.py
@@ -10,7 +10,7 @@ from sqlalchemy.testing import fixtures, AssertsCompiledSQL, AssertsExecutionRes
from sqlalchemy import testing
from sqlalchemy.testing.engines import utf8_engine
import datetime
-
+import decimal
class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
"Test MySQL column types"
@@ -147,6 +147,23 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
res
)
+ @testing.provide_metadata
+ def test_precision_float_roundtrip(self):
+ t = Table('t', self.metadata,
+ Column('scale_value', mysql.DOUBLE(precision=15, scale=12, asdecimal=True)),
+ Column('unscale_value', mysql.DOUBLE(decimal_return_scale=12, asdecimal=True))
+ )
+ t.create(testing.db)
+ testing.db.execute(
+ t.insert(), scale_value=45.768392065789,
+ unscale_value=45.768392065789
+ )
+ result = testing.db.scalar(select([t.c.scale_value]))
+ eq_(result, decimal.Decimal("45.768392065789"))
+
+ result = testing.db.scalar(select([t.c.unscale_value]))
+ eq_(result, decimal.Decimal("45.768392065789"))
+
@testing.exclude('mysql', '<', (4, 1, 1), 'no charset support')
def test_charset(self):
"""Exercise CHARACTER SET and COLLATE-ish options on string types."""
diff --git a/test/requirements.py b/test/requirements.py
index 4ed0a9289..b6fca06ed 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -541,6 +541,13 @@ class DefaultRequirements(SuiteRequirements):
)
@property
+ def precision_generic_float_type(self):
+ """target backend will return native floating point numbers with at
+ least seven decimal places when using the generic Float type."""
+
+ return fails_if('mysql', 'mysql FLOAT type only returns 4 decimals')
+
+ @property
def floats_to_four_decimals(self):
return fails_if("mysql+oursql", "Floating point error")