Access dashDB (or DB2) using ibmdbPy from Python notebook
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!