From 87a06688bc29d26f8e4e695b99767b3627b12ee5 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 12 Feb 2012 18:45:17 -0500 Subject: [PATCH] - [feature] Added a new oracle create_engine() flag coerce_to_decimal=False, disables the precision numeric handling which can add lots of overhead by converting all numeric values to Decimal. [ticket:2399] --- CHANGES | 5 ++++ lib/sqlalchemy/dialects/oracle/cx_oracle.py | 32 ++++++++++++++++++--- test/dialect/test_oracle.py | 9 ++++++ 3 files changed, 42 insertions(+), 4 deletions(-) diff --git a/CHANGES b/CHANGES index c954c2de62..0c4267fb0a 100644 --- a/CHANGES +++ b/CHANGES @@ -92,6 +92,11 @@ CHANGES to mu_mind for the patch here. [ticket:2394] - oracle + - [feature] Added a new create_engine() flag + coerce_to_decimal=False, disables the precision + numeric handling which can add lots of overhead + by converting all numeric values to + Decimal. [ticket:2399] - [bug] Added missing compilation support for LONG [ticket:2401] diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 64526d213e..5001acca34 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -77,7 +77,7 @@ with this feature but it should be regarded as experimental. Precision Numerics ------------------ -The SQLAlchemy dialect goes thorugh a lot of steps to ensure +The SQLAlchemy dialect goes through a lot of steps to ensure that decimal numbers are sent and received with full accuracy. An "outputtypehandler" callable is associated with each cx_oracle connection object which detects numeric types and @@ -89,6 +89,21 @@ this behavior, and will coerce the ``Decimal`` to ``float`` if the ``asdecimal`` flag is ``False`` (default on :class:`.Float`, optional on :class:`.Numeric`). +Because the handler coerces to ``Decimal`` in all cases first, +the feature can detract significantly from performance. +If precision numerics aren't required, the decimal handling +can be disabled by passing the flag ``coerce_to_decimal=False`` +to :func:`.create_engine`:: + + engine = create_engine("oracle+cx_oracle://dsn", + coerce_to_decimal=False) + +The ``coerce_to_decimal`` flag is new in 0.7.6. + +Another alternative to performance is to use the +`cdecimal `_ library; +see :class:`.Numeric` for additional notes. + The handler attempts to use the "precision" and "scale" attributes of the result set column to best determine if subsequent incoming values should be received as ``Decimal`` as @@ -468,6 +483,7 @@ class OracleDialect_cx_oracle(OracleDialect): auto_convert_lobs=True, threaded=True, allow_twophase=True, + coerce_to_decimal=True, arraysize=50, **kwargs): OracleDialect.__init__(self, **kwargs) self.threaded = threaded @@ -491,7 +507,12 @@ class OracleDialect_cx_oracle(OracleDialect): self._cx_oracle_unicode_types = types("UNICODE", "NCLOB") self._cx_oracle_binary_types = types("BFILE", "CLOB", "NCLOB", "BLOB") self.supports_unicode_binds = self.cx_oracle_ver >= (5, 0) - self.supports_native_decimal = self.cx_oracle_ver >= (5, 0) + + self.supports_native_decimal = ( + self.cx_oracle_ver >= (5, 0) and + coerce_to_decimal + ) + self._cx_oracle_native_nvarchar = self.cx_oracle_ver >= (5, 0) if self.cx_oracle_ver is None: @@ -603,7 +624,9 @@ class OracleDialect_cx_oracle(OracleDialect): size, precision, scale): # convert all NUMBER with precision + positive scale to Decimal # this almost allows "native decimal" mode. - if defaultType == cx_Oracle.NUMBER and precision and scale > 0: + if self.supports_native_decimal and \ + defaultType == cx_Oracle.NUMBER and \ + precision and scale > 0: return cursor.var( cx_Oracle.STRING, 255, @@ -614,7 +637,8 @@ class OracleDialect_cx_oracle(OracleDialect): # make a decision based on each value received - the type # may change from row to row (!). This kills # off "native decimal" mode, handlers still needed. - elif defaultType == cx_Oracle.NUMBER \ + elif self.supports_native_decimal and \ + defaultType == cx_Oracle.NUMBER \ and not precision and scale <= 0: return cursor.var( cx_Oracle.STRING, diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index ad9220076e..6830295498 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -880,6 +880,15 @@ class TypesTest(fixtures.TestBase): finally: t1.drop() + + def test_numeric_no_decimal_mode(self): + engine = testing_engine(options=dict(coerce_to_decimal=False)) + value = engine.scalar("SELECT 5.66 FROM DUAL") + assert isinstance(value, float) + + value = testing.db.scalar("SELECT 5.66 FROM DUAL") + assert isinstance(value, decimal.Decimal) + @testing.provide_metadata def test_numerics_broken_inspection(self): """Numeric scenarios where Oracle type info is 'broken', -- 2.47.2