Batch-inserting via executemany() fails with NotSupportedError: Python value cannot be converted to a database value · Issue #236 · oracle/python-cx_Oracle (original) (raw)

Batch-inserting via executemany() fails with NotSupportedError: Python value cannot be converted to a database value

Batch-inserting many rows in chunks of 1000, 500, 100, 50, 20, 10 via executemany() fails with
NotSupportedError: Python value cannot be converted to a database value
If I insert every row line by line, it works.
maybe similar problem as in
https://stackoverflow.com/questions/50713180/cx-oracle-notsupportederror-python-value-cannot-be-converted-to-a-database-valu

Oracle Server

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Client Software

Client OS: Windows 7 Pro 64bit
Python 3.6.7, 64bit (via Anaconda)
cx_Oracle 7.0.0
Oracle Client Version: 11.2.0.1.0
(determined via https://stackoverflow.com/questions/1171643/whats-the-best-way-to-determine-which-version-of-oracle-client-im-running)
Windows PATH:

C:\ProgramData\Anaconda3\Library\bin;C:\oracle\product\11.2.0\client_1\bin;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Windows\System32\Windows System Resource Manager\bin;;C:\Windows\idmu\common;C:\Program Files (x86)\Attachmate\E!E2K\;C:\Program Files\TortoiseSVN\bin;C:\Program Files\R\R-3.1.2\bin\x64;C:\Program Files\Common Files\IBM\SPSS\DataCollection\7\DMOM\;C:\Program Files\IBM\SPSS\DataCollection\7\Accessories\;C:\Program Files (x86)\d.velop\d3client

Steps to reproduce

CREATE TABLE CX_ORACLE_ISSUE ( col_1 varchar2(75) DEFAULT NULL, col_2 number(19) NOT NULL, col_3 number(19) DEFAULT NULL, col_4 number(19) DEFAULT NULL, col_5 number(19) DEFAULT NULL, col_6 number(19) DEFAULT NULL, col_7 varchar2(75) DEFAULT NULL, col_8 timestamp(6) DEFAULT NULL, col_9 timestamp(6) DEFAULT NULL, col_10 number(19) DEFAULT NULL, col_11 number(19) DEFAULT NULL, col_12 number(19) DEFAULT NULL, col_13 clob, col_14 varchar2(75) DEFAULT NULL, col_15 binary_double DEFAULT NULL, col_16 clob, col_17 varchar2(150) DEFAULT NULL, col_18 clob, col_19 clob, col_20 varchar2(75) DEFAULT NULL, col_21 varchar2(75) DEFAULT NULL, col_22 varchar2(75) DEFAULT NULL, col_23 timestamp(6) DEFAULT NULL, col_24 timestamp(6) DEFAULT NULL, col_25 timestamp(6) DEFAULT NULL, col_26 number(3) DEFAULT NULL, col_27 number(3) DEFAULT NULL, col_28 number(19) DEFAULT NULL, col_29 clob, col_30 timestamp(6) DEFAULT NULL, col_31 number(10) DEFAULT NULL, col_32 number(19) DEFAULT NULL, col_33 varchar2(75) DEFAULT NULL, col_34 timestamp(6) DEFAULT NULL, PRIMARY KEY (col_2) )

import pickle import cx_Oracle

def chunks(l, n): """Yield successive n-sized chunks from l.""" for i in range(0, len(l), n): yield l[i:i + n]

connect to database server

pwd = "SECRET" connection = cx_Oracle.connect("USER", pwd, "SERVER") cursor = connection.cursor() del pwd

read data from pickle file

with open('01_data_anonymous.pckl', 'rb') as f: data = pickle.load(f)

sql statement to insert

sql = """INSERT INTO CX_ORACLE_ISSUE (col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9, col_10,col_11,col_12,col_13,col_14,col_15,col_16,col_17,col_18, col_19,col_20,col_21,col_22,col_23,col_24,col_25, col_26,col_27,col_28,col_29,col_30,col_31,col_32, col_33,col_34) VALUES (:col_1,:col_2,:col_3,:col_4,:col_5,:col_6,:col_7,:col_8,:col_9, :col_10,:col_11,:col_12,:col_13,:col_14,:col_15,:col_16,:col_17,:col_18, :col_19,:col_20,:col_21,:col_22,:col_23,:col_24,:col_25, :col_26,:col_27,:col_28,:col_29,:col_30,:col_31,:col_32, :col_33,:col_34)"""

insert data in chunks of 100 -> fail

chunk_size = 100 for i,rows_chunk in enumerate(chunks(data, chunk_size)): print("chunk nr. %i, rows=%i-%i" % ( i, chunk_sizei, chunk_size(i+1))) cursor.executemany(sql, rows_chunk)

commit inserts

connection.commit()

close connection

connection.close()

Outputs:

chunk nr. 0, rows=0-100
chunk nr. 1, rows=100-200
Traceback (most recent call last):

  File "<ipython-input-9-2b555f278a3a>", line 1, in <module>
    runfile('P:/Projects/MiVo/Data Analytics/Datenbank_MiVo_TMARK/99_cxOracle_Bug/reproduce/03_reproduce_anonym.py', wdir='P:/Projects/MiVo/Data Analytics/Datenbank_MiVo_TMARK/99_cxOracle_Bug/reproduce')

  File "C:\Users\601276\conda_envs\cx_Oracle7\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 668, in runfile
    execfile(filename, namespace)

  File "C:\Users\601276\conda_envs\cx_Oracle7\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 108, in execfile
    exec(compile(f.read(), filename, 'exec'), namespace)

  File "P:/Projects/MiVo/Data Analytics/Datenbank_MiVo_TMARK/99_cxOracle_Bug/reproduce/03_reproduce_anonym.py", line 48, in <module>
    cursor.executemany(sql, rows_chunk)

NotSupportedError: Python value cannot be converted to a database value