From 10db67be5b783b94b59dd7cf039f8c322cf837c8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 14 Jul 2010 14:50:45 -0400 Subject: [PATCH] - More tweaks to cx_oracle Decimal handling. "Ambiguous" numerics with no decimal place are coerced to int at the connection handler level. The advantage here is that ints come back as ints without SQLA type objects being involved and without needless conversion to Decimal first. Unfortunately, some exotic subquery cases can even see different types between individual result rows, so the Numeric handler, when instructed to return Decimal, can't take full advantage of "native decimal" mode and must run isinstance() on every value to check if its Decimal already. Reopen of [ticket:1840] --- CHANGES | 18 +++++ lib/sqlalchemy/dialects/oracle/base.py | 6 ++ lib/sqlalchemy/dialects/oracle/cx_oracle.py | 67 ++++++++++++---- test/dialect/test_oracle.py | 87 ++++++++++++++++----- 4 files changed, 142 insertions(+), 36 deletions(-) diff --git a/CHANGES b/CHANGES index ac180b0f0e..09f69a0690 100644 --- a/CHANGES +++ b/CHANGES @@ -37,6 +37,24 @@ CHANGES have their own reconnect feature, there was no symptom for these drivers here unless one watched the logs. [ticket:1848] + +- oracle + - More tweaks to cx_oracle Decimal handling. + "Ambiguous" numerics with no decimal place + are coerced to int at the connection handler + level. The advantage here is that ints + come back as ints without SQLA type + objects being involved and without needless + conversion to Decimal first. + + Unfortunately, some exotic subquery cases + can even see different types between + individual result rows, so the Numeric + handler, when instructed to return Decimal, + can't take full advantage of "native decimal" + mode and must run isinstance() on every value + to check if its Decimal already. Reopen of + [ticket:1840] 0.6.2 ===== diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 3318cfe49b..cb37f95583 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -161,6 +161,12 @@ class NUMBER(sqltypes.Numeric, sqltypes.Integer): super(NUMBER, self).__init__(precision=precision, scale=scale, asdecimal=asdecimal) + def adapt(self, impltype): + ret = super(NUMBER, self).adapt(impltype) + # leave a hint for the DBAPI handler + ret._is_oracle_number = True + return ret + @property def _type_affinity(self): if bool(self.scale and self.scale > 0): diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 629615ab4e..01bb663049 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -83,17 +83,37 @@ class _OracleNumeric(sqltypes.Numeric): return None def result_processor(self, dialect, coltype): - # we apply a connection output handler that - # returns Decimal for positive precision + scale NUMBER - # types + # we apply a cx_oracle type handler to all connections + # that converts floating point strings to Decimal(). + # However, in some subquery situations, Oracle doesn't + # give us enough information to determine int or Decimal. + # It could even be int/Decimal differently on each row, + # regardless of the scale given for the originating type. + # So we still need an old school isinstance() handler + # here for decimals. if dialect.supports_native_decimal: - if self.asdecimal and self.scale is None: - processors.to_decimal_processor_factory(Decimal) - elif not self.asdecimal and self.precision is None and self.scale is None: - return processors.to_float + if self.asdecimal: + if self.scale is None: + fstring = "%.10f" + else: + fstring = "%%.%df" % self.scale + def to_decimal(value): + if value is None: + return None + elif isinstance(value, Decimal): + return value + else: + return Decimal(fstring % value) + return to_decimal else: - return None + if self.precision is None and self.scale is None: + return processors.to_float + elif not getattr(self, '_is_oracle_number', False) \ + and self.scale is not None: + return processors.to_float + else: + return None else: # cx_oracle 4 behavior, will assume # floats @@ -463,22 +483,35 @@ class OracleDialect_cx_oracle(OracleDialect): if self.cx_oracle_ver < (5,): # no output type handlers before version 5 return - + + def maybe_decimal(value): + if "." in value: + return Decimal(value) + else: + return int(value) + cx_Oracle = self.dbapi def output_type_handler(cursor, name, defaultType, size, precision, scale): - # convert all NUMBER with precision + positive scale to Decimal, - # or zero precision and 0 or neg scale, indicates "don't know", - # this effectively allows "native decimal" mode. - if defaultType == cx_Oracle.NUMBER \ - and ( - (precision and scale > 0) or \ - (not precision and scale <= 0) - ): + # 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: return cursor.var( cx_Oracle.STRING, 255, outconverter=Decimal, arraysize=cursor.arraysize) + # if NUMBER with zero precision and 0 or neg scale, this appears + # to indicate "ambiguous". Use a slower converter that will + # 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 \ + and not precision and scale <= 0: + return cursor.var( + cx_Oracle.STRING, + 255, + outconverter=maybe_decimal, + arraysize=cursor.arraysize) # allow all strings to come back natively as Unicode elif defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR): return cursor.var(unicode, size, cursor.arraysize) diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index 7141c504f8..a65fe084a7 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -773,77 +773,126 @@ class TypesTest(TestBase, AssertsCompiledSQL): foo = Table('foo', metadata, Column('idata', Integer), Column('ndata', Numeric(20, 2)), + Column('ndata2', Numeric(20, 2)), + Column('nidata', Numeric(5, 0)), Column('fdata', Float()), ) foo.create() foo.insert().execute( - {'idata':5, 'ndata':Decimal("45.6"), 'fdata':45.68392} + {'idata':5, 'ndata':Decimal("45.6"), 'ndata2':Decimal("45.0"), + 'nidata':Decimal('53'), 'fdata':45.68392}, ) stmt = """ SELECT idata, ndata, + ndata2, + nidata, fdata FROM foo """ + + + row = testing.db.execute(stmt).fetchall()[0] + eq_([type(x) for x in row], [int, Decimal, Decimal, int, float]) eq_( - testing.db.execute(stmt).fetchall(), - [(5, Decimal('45.6'), 45.683920000000001)] + row, + (5, Decimal('45.6'), Decimal('45'), 53, 45.683920000000001) ) - + + # with a nested subquery, + # both Numeric values that don't have decimal places, regardless + # of their originating type, come back as ints with no useful + # typing information beyond "numeric". So native handler + # must convert to int. + # this means our Decimal converters need to run no matter what. + # totally sucks. + stmt = """ SELECT (SELECT (SELECT idata FROM foo) FROM DUAL) AS idata, (SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL) AS ndata, + (SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL) + AS ndata2, + (SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL) + AS nidata, (SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata FROM dual """ + row = testing.db.execute(stmt).fetchall()[0] + eq_([type(x) for x in row], [int, Decimal, int, int, Decimal]) eq_( - testing.db.execute(stmt).fetchall(), - [(Decimal('5'), Decimal('45.6'), Decimal('45.68392'))] + row, + (5, Decimal('45.6'), 45, 53, Decimal('45.68392')) ) - eq_( - testing.db.execute(text(stmt, + + row = testing.db.execute(text(stmt, typemap={ 'idata':Integer(), 'ndata':Numeric(20, 2), + 'ndata2':Numeric(20, 2), + 'nidata':Numeric(5, 0), 'fdata':Float() - })).fetchall(), - [(5, Decimal('45.6'), 45.683920000000001)] + })).fetchall()[0] + eq_([type(x) for x in row], [int, Decimal, Decimal, Decimal, float]) + eq_(row, + (5, Decimal('45.6'), Decimal('45'), Decimal('53'), 45.683920000000001) ) stmt = """ SELECT anon_1.idata AS anon_1_idata, anon_1.ndata AS anon_1_ndata, + anon_1.ndata2 AS anon_1_ndata2, + anon_1.nidata AS anon_1_nidata, anon_1.fdata AS anon_1_fdata - FROM (SELECT idata, ndata, fdata + FROM (SELECT idata, ndata, ndata2, nidata, fdata FROM ( SELECT (SELECT (SELECT idata FROM foo) FROM DUAL) AS idata, (SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL) AS ndata, + (SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) + FROM DUAL) AS ndata2, + (SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) + FROM DUAL) AS nidata, (SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata FROM dual ) WHERE ROWNUM >= 0) anon_1 """ - eq_( - testing.db.execute(stmt).fetchall(), - [(Decimal('5'), Decimal('45.6'), Decimal('45.68392'))] - ) - eq_( - testing.db.execute(text(stmt, + row =testing.db.execute(stmt).fetchall()[0] + eq_([type(x) for x in row], [int, Decimal, int, int, Decimal]) + eq_(row, (5, Decimal('45.6'), 45, 53, Decimal('45.68392'))) + + row = testing.db.execute(text(stmt, typemap={ 'anon_1_idata':Integer(), 'anon_1_ndata':Numeric(20, 2), + 'anon_1_ndata2':Numeric(20, 2), + 'anon_1_nidata':Numeric(5, 0), 'anon_1_fdata':Float() - })).fetchall(), - [(5, Decimal('45.6'), 45.683920000000001)] + })).fetchall()[0] + eq_([type(x) for x in row], [int, Decimal, Decimal, Decimal, float]) + eq_(row, + (5, Decimal('45.6'), Decimal('45'), Decimal('53'), 45.683920000000001) + ) + + row = testing.db.execute(text(stmt, + typemap={ + 'anon_1_idata':Integer(), + 'anon_1_ndata':Numeric(20, 2, asdecimal=False), + 'anon_1_ndata2':Numeric(20, 2, asdecimal=False), + 'anon_1_nidata':Numeric(5, 0, asdecimal=False), + 'anon_1_fdata':Float(asdecimal=True) + })).fetchall()[0] + eq_([type(x) for x in row], [int, float, float, float, Decimal]) + eq_(row, + (5, 45.6, 45, 53, Decimal('45.68392')) ) -- 2.47.2