Python /Pandas INSERT dataframe into DB2 table using ibm_db

Question

While executing the code, nearly 50% of the total records are getting inserted. All of a sudden program stops with the mentioned errors

CODE:

conn = ibm_db.connect('DRIVER={IBM DB2 DRIVER};DATABASE=DEV;HOSTNAME=cloud.ibm.com;PORT=30000;PROTOCOL=TCPIP;UID=userid;pwd=password','','')
conni = ibm_db_dbi.Connection(conn)
subset = df[['col1', 'col2', 'col3', 'col4', 'col5',
       'col6', 'col7', 'col8', 'col9']]

tuple_of_tuples = tuple([tuple(x) for x in subset.values])

load_db2_sql = "INSERT INTO edw.DETAILS VALUES(?,?,?,?,?,?,?,?,?)"

stmt = ibm_db.prepare(conn, load_db2_sql)

ibm_db.execute_many(stmt, tuple_of_tuples)

Error:

Error 1064: Value parameters array 3386 is not homogeneous with previous parameters array

SQLCODE=-302 BM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001

It seems like a data issue. Can you provide an example (limited) data set which fails? Most likely place to check is around the index of 3386 in tuple_of_tuples. — it's-yer-boy-chet
– it's-yer-boy-chet, Commented Sep 16, 2019 at 21:34

ertl · Accepted Answer · 2021-04-07 18:19:11Z

1

I ran into this same issue and used Tad's code but instead of 'No Value' I used None. It inserted those Null "values" into the DB2 table.

df = df.where((pd.notnull(df)), None)

answered Apr 7, 2021 at 18:19

ertl

131 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tad · Accepted Answer · 2021-04-08 07:40:56Z

1

The dataframe which I was trying to LOAD to DB2 table had 'nan' values. I have replaced it with "No Value'. It worked after that.

df = df.where((pd.notnull(df)), 'No Value')

edited Apr 8, 2021 at 7:40

answered Sep 17, 2019 at 7:12

Tad

9139 silver badges19 bronze badges

Collectives™ on Stack Overflow

Python /Pandas INSERT dataframe into DB2 table using ibm_db

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related