Insert into DB2 table from a python dataframe

Question

I am using python library IBM_DB with which I am able to establish connection and read tables into dataframes. The problem comes when writing into a DB2 table (INSERT query) from a dataframe source in python.

Below is sample code for connection but can someone help me how to insert all records from a dataframe into the target table in DB2 ?

import pandas as pd
import ibm_db
ibm_db_conn = ibm_db.connect("DATABASE="+"database_name"+";HOSTNAME="+"localhost"+";PORT="+"50000"+";PROTOCOL=TCPIP;UID="+"db2user"+";PWD="+"password@123"+";", "","")
import ibm_db_dbi
conn = ibm_db_dbi.Connection(ibm_db_conn)

df=pd.read_sql("SELECT * FROM SCHEMA1.TEST_TABLE",conn)
print df

I am also able to insert a record manually if given SQL syntax with hard coded values :

query = "INSERT INTO SCHEMA1.TEST_TABLE (Col1, Col2, Col3) VALUES('A', 'B', 0)"
print query
stmt = ibm_db.exec_immediate(ibm_db_conn, query)
print stmt

What I am unable to achieve is to insert from a dataframe and append it to the table. I've tried DATAFRAME.to_SQL() as well but it errors out with the following :

df.to_sql(name='TEST_TABLE', con=conn, flavor=None, schema='SCHEMA1', if_exists='append', index=True, index_label=None, chunksize=None, dtype=None)

This errors out saying :

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "SCHEMA1.SQLITE_MASTER" is an undefined name.  SQLSTATE=42704 SQLCODE=-204

The error tells you that there is no such table sqlite_master" in the schema called SCHEMA1. Find the correct schema name or correct table name and use that. — mao
– mao, Commented Dec 11, 2017 at 11:22
I am having the same problem. I know this is an old post but did you manage to solve it? From what I can see atm in the pandas documentation you should replace: name=“Tablename” and con=conn, with just: “Tablename” and Conn @ShankarPandey — danielo
– danielo, Commented Jan 20, 2018 at 19:06

ochs.tobi · Accepted Answer · 2018-05-03 07:31:55Z

7

You can write a pandas data frame into ibm db2 using ibm_db.execute_many().

subset = df[['col1','col2', 'col3']]

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

sql = "INSERT INTO Schema.Table VALUES(?,?,?)"

cnn = ibm_db.connect("DATABASE=database;HOSTNAME=127.0.0.1;PORT=50000;PROTOCOL=TCPIP;UID=username;PWD=password;", "", "")

stmt = ibm_db.prepare(cnn, sql)

ibm_db.execute_many(stmt, tuple_of_tuples)

edited May 3, 2018 at 7:31

ochs.tobi

3,4727 gold badges34 silver badges53 bronze badges

answered May 3, 2018 at 6:45

Muhammad Usman

911 silver badge5 bronze badges

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

1 Comment

d0dulk0 Over a year ago

this does not work , the OP is creating the connection with ibm_db_dbi not ibm_db

Collectives™ on Stack Overflow

Insert into DB2 table from a python dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related