[Maintenance/Bug Report] Restrict cx_Oracle to version 5.2 or lower.

[bdorney]

Brief summary of issue

We might need to add a restriction on the allowed versions of cx_Oracle due to incompatibilities between newer/older versions of the package.

Types of issue

• [x] Bug report (report an issue with the code)
• [ ] Feature request (request for change which adds functionality)

Expected Behavior

TypeError shouldn't be generated at runtime.

Current Behavior

QC7 team reported issue in QC7 machine:

https://mattermost.web.cern.ch/cms-gem-ops/pl/bruym98zg3d8tdczrm856pwpca

@jsturdy traced this to an incompatible version of cx_Oracle package:

https://mattermost.web.cern.ch/cms-gem-ops/pl/f3o8tbfawigrmfpa6r94wsn7pc

Possible Solution (for bugs)

Restrict versions allowed for the cx_Oracle.

Context (for feature requests)

We should not install packages that do not track their dependencies adequately leading to problems at runtime.

Your Environment

• Version used: a51324ac284f894979aa5f53260411437dcf6068
• Shell used:

[jsturdy]

Don't know about "lower", I just know that there is a problem with the version of the oracle client that is pulled in with 7.1.2

[lpetre-ulb]

On the ULB setup, we are using the following packages versions successfully:

(pyvenv) [lpetre@gem-daq ~]$ pip list | grep -e Alchemy -e pandas -e Oracle
cx-Oracle                        7.2.0
pandas                           0.24.2
SQLAlchemy                       1.3.6
(pyvenv) [lpetre@gem-daq ~]$ getCalInfoFromDB.py 1 4 4
Open pickled address table if available  /home/lpetre/data/address_table/amc_address_table_top.pickle...
Initializing AMC gem-shelf01-amc04
Length of returned view does not match length of input vfat List
VFATs not found: []
<class 'pandas.core.frame.DataFrame'>
Int64Index: 24 entries, 0 to 23
Data columns (total 10 columns):
vfatN            24 non-null int64
vfat3_ser_num    24 non-null object
vfat3_barcode    24 non-null object
iref             24 non-null int64
adc0m            24 non-null float64
adc1m            24 non-null float64
[...]
    vfatN vfat3_ser_num vfat3_barcode  iref    adc0m    adc1m    adc0b  \
0       0        0x1772          6002    34  1.91322  2.25226 -310.869
1       1        0x1738          5944    37  1.89441  2.22386 -326.678
2       2         0xb38          2872    31  1.89193  2.23571 -315.597
3       3         0xb37          2871    29  1.90077  2.21219 -311.144
4       4         0xe7c          3708    33  1.92268  2.20852 -314.080
5       5        0x1233          4659    33  1.91414  2.25785 -329.902
6       6        0x107e          4222    24  1.94357  2.22508 -318.317
[...]

Looks like the issue is the combination of sqlalchemy <= 1.1.10 and cx_Oracle >= 6.0; see the sqlalchemy changelog. The sqalchemy version provided by cc7 is therefore too old for the new cx_Oracle releases.

[jsturdy]

Indeed, and bypassing sqlalchemy(passing an actual DB connection object to pandas, rather than letting pandas use sqlalchemy to create the connection) restores proper functionality regardless of cx_Oracle version, e.g.,:

cx-Oracle==7.1.0
pandas==0.17.1
SQLAlchemy==0.9.8

ipython
In [1]: import pandas as pd
In [2]: import cx_Oracle
In [3]: import sqlalchemy
In [4]: gemdb = '{}/{}@{}'.format(os.getenv(DBUSER), os.getenv(DBPASS), os.getenv(DBNAME))
In [5]: db    = cx_Oracle.connect(gemdb)
In [6]: query = "select VFAT3_SER_NUM,VFAT3_BARCODE from CMS_GEM_MUON_VIEW.GEM_VFAT3_PROD_SUMMARY_V_RH data WHERE RUN_NUMBER = ( select max(RUN_NUMBER) as RUN_NUMBER from CMS_GEM_MUON_VIEW.GEM_VFAT3_PROD_SUMMARY_V_RH) AND (data.VFAT3_BARCODE='6001' OR data.VFAT3_SER_NUM='0x1a07')"
In [7]: cur   = db.cursor()
In [8]: cur.execute(query)
Out[8]: <cx_Oracle.Cursor on <cx_Oracle.Connection to CMS_GEM_APPUSER_R@cms_omds_lb>>
In [9]: for r in cur:
    print(r)
('0x1771', '6001')
In [10]: dfGEMView = pd.read_sql(query,db)
In [11]: dfGEMView.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1 entries, 0 to 0
Data columns (total 2 columns):
VFAT3_SER_NUM    1 non-null object
VFAT3_BARCODE    1 non-null object
dtypes: object(2)
memory usage: 24.0+ bytes
In [12]: print(dfGEMView)
  VFAT3_SER_NUM VFAT3_BARCODE
0        0x1771          6001