Backwards incompatibility between cx_Oracle 7.0.0 and 7.1.1 returning numbers

[datatravelgit]

Backwards incompatibility between cx_Oracle 7.0.0 and 7.1.1

There is a difference between Cx_Oracle 7.0.0 and 7.1.1 when returning numbers.
In 7.0.0 the fetchall returns 9.93527465e-06 while the version 7.1.1 returns Decimal('0.00000993527465') .

Details

The Oracle table is defined as follows:

CREATE TABLE DIVIDENDS
(
  ID              CHAR(8 CHAR)           NOT NULL,
  EXDATE          DATE                   NOT NULL,
  PD_ID           VARCHAR2(6 CHAR)       NOT NULL,
  PD              NUMBER                 NOT NULL,
  CURRENCY        CHAR(3 CHAR)           NOT NULL,
  PAYDATEC        DATE,
  RECDATEC        DATE,
  S_SPINOFF       INTEGER                NOT NULL,
  S_PD            INTEGER                NOT NULL,
  PD_TYPE_CODE    VARCHAR2(4 CHAR),
  PD_NGFLAG_CODE  CHAR(1 CHAR),
  PD_NGEQUIV      NUMBER,                -- << Column defined as number        
  PD_TAX_CODE     VARCHAR2(6 CHAR)
)

The select statement is a simple query:

SELECT  id,
		exdate,
		pd,
		s_spinoff,
		s_pd, 
		pd_ngflag_code, 
		pd_ngequiv,
		pd_tax_code
FROM dividends
WHERE id = 'H6'

A select statement using Toad shows

...
H6 26/06/2014    0.0139110005    0    0    N    0    LD
H6 26/09/2014    7.0000001E-6    0    0    G    9.93527465E-6    LG
H6 26/09/2014    0.000607000024    0    0    N    0    LI
...

Using cx_Oracle version 7.0.0, the returned value is 9.93527465e-06

cx_Oracle.__version__
'7.0.0'
cursor.execute(statement)
data = cursor.fetchall()
[v[6] for v in data]
[0, 0.00707948487, 1.55983817e-05, 0, 0, 0, 0, 9.93527465e-06, 0, 0, 0, 0, 0, 0, 0, 0.000239837551, 0, 5.39485445e-05, 0, 2.69245975e-05, 0, 0, 0, 0, 2.12614887e-05]

Using cx_Oracle version 7.1.1 the returned value is Decimal('0.00000993527465')

cx_Oracle.__version__
'7.1.1'
cursor.execute(statement)
data = cursor.fetchall()
[v[6] for v in data]
[0, 0.00707948487, 1.55983817e-05, 0, 0, 0, 0, Decimal('0.00000993527465'), 0, 0, 0, 0, 0, 0, 0, 0.000239837551, 0, 5.39485445e-05, 0, 2.69245975e-05, 0, 0, 0, 0, 2.12614887e-05]

OS Versions for cx_Oracle version 7.0.0

INSTALLED VERSIONS

python: 3.6.4.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel

OS Versions for cx_Oracle version 7.1.1

INSTALLED VERSIONS

python: 3.7.1.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel

Oracle Database version

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

[anthony-tuininga]

This is noted in the release notes:

Adjusted fetching of numeric values so that no precision is lost. If an Oracle number cannot be represented by a Python floating point number a decimal value is automatically returned instead.

From your example it looks like the code isn't quite as smart as it could be -- the leading zeroes in this case don't affect precision. Is that the concern? If you don't like the default behaviour you can always use an outputtypehandler to return whatever you wish, of course!

[datatravelgit]

Hi Anthony,
Sorry, the sample code was simplified for demonstration only.

The concern is not related to the precision. It is related to the data type that we get after loading the Oracle data into a pandas dataframe:

• Using version cx_oracle 7.0.0, after a fetch operation, a dataframe can be instantiated and the end result contains float64 as dtypes.
• Using version cx_oracle 7.1.1, the pandas dataframe ends up with an Object dtype (this is not a numeric data type) because of the inclusion of a Decimal('...'). Any numeric operation using these fields are failing. As a conclusion, we are forced to convert the fetched Oracle data as float64. An approach similar to the use of Cursor.outputtypehandler
  Thanks

[anthony-tuininga]

Thanks for the explanation. I'll discuss it internally and get back to you on this.

[anthony-tuininga]

Ok. I've discussed this internally and thought about it a bit more -- and decided that the decision to return decimal numbers when precision would be lost was a bit too much -- a nice idea but with undesirable consequences as you have noted! So I am reverting that and will release that change with 7.1.2. Thanks for letting me know.

[datatravelgit]

Thanks for let us know. We will upgrade to 7.1.2 when it is available. Thanks

[anthony-tuininga]

This was addressed in cx_Oracle 7.1.2 which was just released.