Using IBM_DB with Pandas

Question

I am trying to use the data analysis tool Pandas in Python Language. I am trying to read data from a IBM DB, using ibm_db package. According to the documentation in Pandas website we need to provide at least 2 arguments, one would be the sql that would be executed and other would be the connection object of the database. But when i do that, it gives me error that the connection object does not have a cursor() method in it. I figured maybe this is not how this particular DB Package worked. I tried to find a few workarounds but was not successfull.

Code:

print "hello PyDev"
con = db.connect("DATABASE=db;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=admin;PWD=admin;", "", "")
sql = "select * from Maximo.PLUSPCUSTOMER"
stmt = db.exec_immediate(con,sql)
pd.read_sql(sql, db)
print "done here"

Error:

hello PyDev
Traceback (most recent call last):
  File "C:\Users\ray\workspace\Firstproject\pack\test.py", line 15, in <module>
    pd.read_sql(sql, con)
  File "D:\etl\lib\site-packages\pandas\io\sql.py", line 478, in read_sql
    chunksize=chunksize)
  File "D:\etl\lib\site-packages\pandas\io\sql.py", line 1504, in read_query
    cursor = self.execute(*args)
  File "D:\etl\lib\site-packages\pandas\io\sql.py", line 1467, in execute
    cur = self.con.cursor()
AttributeError: 'ibm_db.IBM_DBConnection' object has no attribute 'cursor'

I am able to fetch data if i fetch it from the database but i need to read into a dataframe and need to write back to the database after processing data.

Code for fetching from DB

stmt = db.exec_immediate(con,sql)
 tpl=db.fetch_tuple(stmt)
 while tpl:
     print(tpl)
     tpl=db.fetch_tuple(stmt)

Underoos · Accepted Answer · 2020-12-22 11:01:21Z

22

On doing further studying the package, i found that I need to wrap the IBM_DB connection object in a ibm_db_dbi connection object, which is part of the https://pypi.org/project/ibm-db/ package.

So

conn = ibm_db_dbi.Connection(con)
df = pd.read_sql(sql, conn)

The above code works and pandas fetches data into dataframe successfully.

edited Dec 22, 2020 at 11:01

Underoos

5,2769 gold badges55 silver badges106 bronze badges

answered Nov 19, 2015 at 13:39

Shaurya Chaudhuri

3,8728 gold badges32 silver badges61 bronze badges

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

6 Comments

yasin mohammed Over a year ago

I tried using this solution but I am getting a database authorization error. My uset ID is in small but when it shows the error it takes my user ID in caps. Any suggestion on how to fix this ?

Shaurya Chaudhuri Over a year ago

Hmm, couldn't figure out exactly why this may happen. Would be helpful if you can share a code snippet.

yasin mohammed Over a year ago

It was my mistake.. I thought it was a user ID issue but actually my access to the view was revoked. Once the DBA corrected that it worked fine.

Nim J Over a year ago

make sure to import ibm_db_dbi

shawnzhu Over a year ago

ibm_db_dbi.Connection() takes one argument only. it should be an object of connection. so this code works:

conn = ibm_db_dbi.Connection(ibm_db.connect('DATABASE={};HOSTNAME={};PORT={};PROTOCOL=TCPIP;UID={};PWD={};'.format(...,...))); df = pd.read_sql(sql, conn)

|

Flavio Venturini · Accepted Answer · 2022-02-06 00:44:17Z

1

from ibm_db import connect
import pandas as pd
import ibm_db_dbi
cnxn = connect('DATABASE=YourDatabaseName;'
                 'HOSTNAME=YourHost;'  # localhost would work 
                 'PORT=50000;'
                 'PROTOCOL=TCPIP;'
                 'UID=UserName;'
                 'PWD=Password;', '', '')
sql = "SELECT * FROM Maximo.PLUSPCUSTOMER"
conn=ibm_db_dbi.Connection(cnxn)
df = pd.read_sql(sql, conn)
df.head()

edited Feb 6, 2022 at 0:44

Flavio Venturini

34 bronze badges

answered May 26, 2021 at 23:58

PolarVertex

571 silver badge10 bronze badges

Comments

Torsten Steinbach · Accepted Answer · 2015-11-26 07:23:25Z

0

you can also check out https://pypi.python.org/pypi/ibmdbpy

It provides Pandas style API without pulling out all data into Python memory.

Documentation is here: http://pythonhosted.org/ibmdbpy/index.html Here is a quick demo how to use it in Bluemix Notebooks: https://www.youtube.com/watch?v=tk9T1yPkn4c

answered Nov 26, 2015 at 7:23

Torsten Steinbach

3912 silver badges5 bronze badges

Comments

Jacob CUI · Accepted Answer · 2021-10-11 21:34:19Z

0

You can just use ibm_db_dbi.connect like this (tested)

import ibm_db_dbi
import pandas as pd

config = {
  'database:xxx, 'hostname':xxx, 'port': xxx, 
  'protocol':xxx, 'uid': xxx, 'password': xxx
}
conn = ibm_db_dbi.connect(
  'database={database};'
  'hostname={hostname};'
  'port={port};'
  'protocol={protocol};'
  'uid={uid};'
  'pwd={password}'.format(**config), '', '')

sql = 'select xxxx from xxxx'
df = pd.read_sql(sql, conn)

answered Oct 11, 2021 at 21:34

Jacob CUI

1,35515 silver badges11 bronze badges

Collectives™ on Stack Overflow

Using IBM_DB with Pandas

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related