diff options
| -rw-r--r-- | doc/build/changelog/changelog_09.rst | 17 | ||||
| -rw-r--r-- | doc/build/changelog/migration_09.rst | 44 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 19 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/cx_oracle.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/pg8000.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/psycopg2.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/processors.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 52 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/requirements.py | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/suite/test_types.py | 10 | ||||
| -rw-r--r-- | test/dialect/mysql/test_types.py | 19 | ||||
| -rw-r--r-- | test/requirements.py | 7 |
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") |