Access dashDB (or DB2) using ibmdbPy from Python notebook

IBM dashDB is a fully managed cloud data warehouse, purpose-built for analytics. In this article we will explore how to connect to it from a Python notebook and perform data access using ibmdbPy.

The ibmdbpy library provides a Python interface for data manipulation and access to in-database algorithms in IBM dashDB and IBM DB2. It accelerates Python analytics by seamlessly pushing operations written in Python into the underlying database for execution, thereby benefiting from in-database performance-enhancing features, such as columnar storage and parallel processing.

To get started you will need credentials for a dashDB database. If you don't already have an instance of dashDB you can get one for free by following steps in this article.

The steps outlined below are included in a tutorial notebook  available on the Jupyter notebook Welcome screen of your Workbench.

Lets start by loading ibmdbPy. We will also need to import JayDeBeAPI package to connect to the database using JDBC from ibmdbPy.
import jaydebeapi
from ibmdbpy import IdaDataBase
from ibmdbpy import IdaDataFrame
Next you will need to enter your database credentials:
#Enter the values for you database connection
dsn_database = "BLUDB"           # e.g. "BLUDB"
dsn_hostname = "<Enter hostname>" # e.g.: "bluemix05.bluforcloud.com"
dsn_port = "50000"               # e.g. "50000"
dsn_uid = "<Enter userID>"       # e.g. "dash104434"
dsn_pwd = "<Enter password"     # e.g. "7dBZ3jWt9xN6$o0JiX!m"
Then create the connection:
connection_string='jdbc:db2://'+dsn_hostname+':'+dsn_port+'/'+dsn_database+':user='+dsn_uid+';password='+dsn_pwd+";" 
idadb=IdaDataBase(dsn=connection_string)
Now lets read some data into a dataframe and display first 5 rows:
df=idadb.show_tables(show_all = True)
df.head(5)
The results will look like:

TABSCHEMA TABNAME OWNER TYPE
0 DASH013382 ACCIDENTS DB2INST1 T
1 DASH013382 ACCIDENTS_NEW DB2INST1 T
2 DB2GSE ST_COORDINATE_SYSTEMS DB2INST1 V
3 DB2GSE ST_GEOCODERS DB2INST1 V
4 DB2GSE ST_GEOCODER_PARAMETERS DB2INST1 V
The tutorial notebook includes additional examples for data manipulation. To ensure expected behaviors, IdaDataBase instances need to be closed. Closing the IdaDataBase is equivalent to closing the connection: once the connection is closed, it is not possible to use the IdaDataBase instance and any IdaDataFrame instances that were opened on this connection anymore.
idadb.close()

Good luck!

Feedback and Knowledge Base