Repository navigation
Closed
Labels
Description
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
Activity
anthony-tuininga commentedon Mar 11, 2019
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 commentedon Mar 11, 2019
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:
Thanks
anthony-tuininga commentedon Mar 11, 2019
Thanks for the explanation. I'll discuss it internally and get back to you on this.
anthony-tuininga commentedon Mar 12, 2019
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.
Revert changes to return decimal numbers when the numeric precision w…
datatravelgit commentedon Mar 12, 2019
Thanks for let us know. We will upgrade to 7.1.2 when it is available. Thanks
anthony-tuininga commentedon Mar 13, 2019
This was addressed in cx_Oracle 7.1.2 which was just released.