cx_Oracle executemany silent failure when inserting big dataset #153
Description
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