cx_Oracle executemany silent failure when inserting big dataset · Issue #153 · oracle/python-cx_Oracle (original) (raw)

Hi,

I am running the following versions:

import cx_Oracle
cx_Oracle.__version__
Out[32]: '6.1'
print(conn.version)
11.2.0.3.0
import sys
print(sys.version)
3.6.3 |Anaconda, Inc.| (default, Oct 15 2017, 03:27:45) [MSC v.1900 64 bit (AMD64)]

I have a dataframe with 225 columns and more than 3M records:

type(index_data)
Out[35]: pandas.core.frame.DataFrame

index_data.columns
Out[25]: 
Index(['12m_forward_eps', '12m_trailing_eps', '1_year_ADPS_growth_rate',
       ...
       'mk_usd', 'closing_weight'],
      dtype='object', length=225)

len(index_data)
Out[27]: 3355526

When I try to use the cx_Oracle executemany command, I have no errors but also, no data inserted in the database!

cursor = conn.cursor()
sql = 'INSERT INTO index (' + ', '.join(index_data.columns) + ') VALUES (' + ''. join([ ':' + str(v)+', ' for v in list(range(1, len(index_data.columns)))]) + ':' + str(len(index_data.columns)) + ')'
cursor.prepare(sql)
cursor.executemany(None, index_data.values.tolist())
conn.commit()

It seems that the library has some memory leak and the exception is cached without raising any error.
You can see, that when I am limiting the number of records to insert, I have a proper error:

cursor.executemany(None, index_data[:10].values.tolist())
Traceback (most recent call last):
  File "C:\UBS\Dev\anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 2862, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-28-66a03cab4f0e>", line 1, in <module>
    cursor.executemany(None, index_data[:10].values.tolist())
cx_Oracle.DatabaseError: DPI-1055: value is not a number (NaN) and cannot be used in Oracle numbers

Is this a dangerous behaviors that should prompt us to use another library?

Thanks
DT

PS: In case that you wonder, I tested my connection and steps using a small dataset (2 columns, 2 rows), and I was able to run and commit cx_Oracle executemany