How to upload pandas dataframe to IBM Db2 database

Question

I am trying to upload pandas dataframe to IBM db2 dataframe. However, I could not manage to find the method to load the complete dataset at once.

import ibm_db
dsn_driver = "IBM DB2 ODBC DRIVER"
dsn_database = "BLUDB"
dsn_hostname= "dashdb-txn-xxxxx.eu-gb.bluemix.net"
dsn_port="5xx00"
dsn_protocol="TCPIP"
dsn_uid="xxxxx"
dsn_pwd="xxxx"

dsn = (
    "DRIVER={{IBM DB2 ODBC DRIVER}};"
    "DATABASE={0};"
    "HOSTNAME={1};"
    "PORT={2};"
    "PROTOCOL=TCPIP;"
    "UID={3};"
    "PWD={4};").format(dsn_database, dsn_hostname, dsn_port, dsn_uid, dsn_pwd)

try:
    conn = ibm_db.connect(dsn, "", "")
    print('Connected')
except:
    print('Unable to connect to database', dsn)

d = {'col1': [1, 2,3,4,5,6,7,8,9,10], 'col2': [3, 4,3,4,5,6,7,8,2,34], 'col3': [1, 2,3,14,5,36,72,8,9,10],}
import pandas as pd
df = pd.DataFrame(data=d)
df

So far, I manage to conect successfully to the ibmdb2 database, the rest steps of uploading the pandas dataframe is not clear to me, I tried several options from google, none seem to be working.

To make problem easy, I created a sample pandas dataframe (df, above). Any help page or documentation is appreciated.

thank you pooja

I tried several options from google, none seem to be working ... where are your trials and tribulations (i.e., reported errors or undesired results)? Not working is not helpful for us to troubleshoot. — Parfait
– Parfait, Commented Feb 6, 2019 at 14:57

mao · Accepted Answer · 2019-02-06 14:27:39Z

3

The code below worked for me with both python 3.5.2 and 2.7.12, ibm_db_sa 0.3.4, with Db2 v11.1.4.4

Adjust the parameters for .to_sql to suit your requirements.

Add exception handling as required.

import ibm_db
import pandas as pd
from sqlalchemy import create_engine


dsn_driver = "IBM DB2 ODBC DRIVER"
dsn_database = "..."
dsn_hostname= "..."
dsn_port="60000"
dsn_protocol="TCPIP"
dsn_uid="..."
dsn_pwd="..."

d = {'col1': [1, 2,3,4,5,6,7,8,9,10], 'col2': [3, 4,3,4,5,6,7,8,2,34], 'col3': [1, 2,3,14,5,36,72,8,9,10],}

df = pd.DataFrame(data=d)

engine = create_engine('ibm_db_sa://'+ dsn_uid + ':' + dsn_pwd + '@'+dsn_hostname+':'+dsn_port+'/' + dsn_database )

df.to_sql('pandas1', engine)

edited Feb 6, 2019 at 14:27

answered Feb 4, 2019 at 15:52

mao

12.4k2 gold badges15 silver badges32 bronze badges

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

2 Comments

Violet Over a year ago

thank you, I'll try. Meanwhile, I copied my pandas dataframe to Cloud object storage..

Gangula Over a year ago

Note that you need to install ibm_db==2.0.8a & ibm_db_sa. Higher versions of ibm_db did not work for me.

Collectives™ on Stack Overflow

How to upload pandas dataframe to IBM Db2 database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related