Getting started with IBM dashDB using Python – RadioStudio
Menu

Getting started with IBM dashDB using Python

By Shyam Purkayastha | Cloud Computing

Dec 27
Getting Started with IBM dashDB

IBM dashDB is one of the popular hosted databases under IBM, also available as part of IBM Bluemix catalog of services. In this post, we will take you through a developer’s workflow and show you how to setup dashDB and access it from a python program.

Index


Introducing IBM dashDB

IBM dashDB is a fully managed cloud data warehousing service for the developers wanting to build a data-centric application on IBM cloud. dashDB puts business and analytics at your fingertips to build entirely new, self-service data warehousing infrastructure. It can also extend your existing applications deployed with DB2.

dashdbwebsite

If you are planning to build an application on IBM Bluemix, then dashDB can be your ideal choice to augment the database capabilities. Check out our Smart Highway Toll Collection System demo, which is one of the showcase applications that we built using IBM Bluemix & dashDB. Follow along this post to explore how you can get started with dashDB and deploy a sample dashDB service under Bluemix environment. But before you get started, here are some essential prerequisites.

Prerequisites

  • Sign up with IBM Bluemix on the following link.
  • You should have a Bluemix subscription and access to your Bluemix dashboard with at least one space created.
  • Clone the dashDB sample Python scripts from this GitHub link.

Setting up dashDB Service on IBM Bluemix

Before you can use dashDB in any application, you must set up and instantiate a dashDB service instance under your IBM Bluemix account.

  1. Login to the Bluemix console with your IBM Id credentials.
  2. Click on the Catalog, to view all the services.image1
  3. Under the Bluemix dashboard, go to the catalog and select Data and Analytics section.image2You can see that the dashDB service will be listed under Data & Analytics section or you can search for dashDB.
  4. Click on dashDB service icon and create a dashDB service instance for your space by filling following details (Refer the image below for numbered UI elements where you need to fill the details). 
    • On Connect to select “leave unbound” (1).
    • Under Service name enter a name for the service of your choice (2).
    • Under Credential name enter a name for the Credential of your choice (3).
    • Under Selected Plan choose ‘Entry’. ( This is the default option. Do not change it unless you want to really go for the business plan, in which case you will be charged for the service.)
    • Click on CREATE to create the dashDB service instance (4).image42

    After creating the service, go back to the dashboard. Now you can see the dashDB service added to your space. Click the service and click the open button and you can see your newly created dashDB service home page.

  5. In the dashDB service home page, under the Side Menu, select Connect → Connection Information.image5
  6. You can see your dashDB credentials such as Host name, Database name, Uuser ID  and Password. Make a note of  Host Name, Port number, Database name, User ID and Password. These are your credentials that you will require to programmatically access your dashDB instance from Python program.image6

 

Setting up Python libraries for accessing dashDB

We have used Linux Debian Ubuntu 16.04 for setting up the ibm_db which is a Python library for accessing dashDB. Follow the steps below to install ibm_db on your computer.

  • Make sure that Python 2.7 is installed. If not, follow the steps to install Python 2.7 .
sudo apt-get update
sudo apt-get install python-2.7
  • Install ibm_db Python package.
sudo pip install ibm_db

Kickstart your first Python script with dashDB

We have made a sample script for demonstrating the basic CRUD operations. Open the file  dashDB_Demo.py from the GitHub repo and modify the assignment of following Python variables with your dashDB credentials.

  • self.userid (Line Number 27): Provide your User ID 
  • self.password (Line Number 28): Provide your Password
  • self.hostname (Line Number 29): Provide the Host name
  • self.portnumber (Line Number 30): Provide the Port number  

Now we are all set to execute the program. You can run the program on a terminal by using the following command. Make sure that your terminal prompt points to the root folder of the cloned Github repo (current directory).

python dashDB_Demo.py

If all goes well and the program starts executing, then you will be presented with options to perform specific DB operations on dashDB. Follow along the options to try out all the DB operations.  A detailed explanation of each operation and its corresponding ibm_db API is presented next.

Walk through with dashDB SQL commands and APIs

Let us take a brief walkthrough of the various ibm_db APIs for performing DB operations on dashDb.

CONNECT

Before you can run any SQL statements to create, update, delete or fetch data, you must connect to the database. To improve performance, you can also create a persistent connection.

API  Syntax 
  • For creating a non-persistent connection use following syntax,
ibm_db.connect   
  • For creating a persistent connection use following syntax,
ibm_db.pconnect

A persistent connection remains open after the initial request, which allows subsequent API requests to reuse the connection. The subsequent requests must have an identical set of credentials.

API Example
ibm_db.connect(‘DATABASE=dbname;UID=username;PWD=password;HOSTNAME=hostname;PORT=portnumber;’,’’,’’)

If the connection attempt is successful, we can use the connection resource to execute SQL statements. Now we are ready to execute CRUD operations on the database.

CREATE

The CREATE table statement defines a table. The definition must include its name and the names and attributes of the columns. The definition can include other attributes of the table, such as its primary key or check constraints.

SQL Syntax 
CREATE TABLE tablename (col1 datatype 1,col2 datatype 2) PRIMARY KEY (col1)
SQL Example 
CREATE TABLE USERTABLE (ID INT, EMAILID VARCHAR(20),PASSWORD VARCHAR(20),USERNAME VARCHAR(20),DATEOFCREATION TIMESTAMP) PRIMARY KEY(EMAILID,PASSWORD)

 

The above SQL Statement creates a table with table name USERTABLE with columns ID of type integer and EMAILID, PASSWORD and USERNAME of type varchar and DATEOFCREATION as timestamp. Also, the EMAILID and PASSWORD  columns are chosen as primary keys. For more info about the various datatyes supported by dashDB, follow this documentation link.

The Python code snippet for creating table is given below. Make a note of the ibm_db.exec_immediate( ) API call. This is used for performing all the CRUD operations using the ibm_db library.

INSERT

The INSERT statement inserts rows into a table.

SQL Syntax 
INSERT INTO tablename (columnname 1,columnname 2) VALUES (value1,value2)
SQL Example 
INSERT INTO USERTABLE (ID,EMAILID,PASSWORD,USERNAME,DATEOFCREATION) VALUES (1,”[email protected]”,”john123”,”JOHN”,”2016-10-17 16:41:33”)

 

The above SQL Statement inserts new values for particular columns in the table. You can find more related information regarding insert in Dash DB documentation here. The Python code snippet for inserting rows is given below.

 

UPDATE

The update statement updates the values of specified columns in rows of a dashDB table.

SQL Syntax 
UPDATE tablename SET column_name = update_value WHERE condtion_column_name = condition_column_value
SQL Example 
UPDATE USERTABLE SET USERNAME = “MARK” WHERE EMAILID = “[email protected]” and PASSWORD = “john123”

The above SQL Statement updates the USERNAME for the ID 1. It’s USERNAME is set to “MARK”. You can find more related information regarding Update in the documentation here.

 

FETCH

The Fetch statement positions a cursor on the next row of its result table and assigns the values of that row to target variables. The fetch functions in the ibm_db API can iterate through the result set. If your set includes columns that contain large data(such as BLOB or CLOB data), you can retrieve the data on a column-by-column basis to avoid large memory usage.

Before using one of the following fetch functions first, we must have a statement resource returned by either the ibm_db.exec_immediate or ibm_db.execute function that has one or more associated result sets.

Here you can see few fetch type options in dashDB.

SQL Syntax 
SELECT fetchfieldname FROM tablename
SQL Example 
SELECT * FROM USERTABLE

In the above example, we are getting all the data from the Table USERTABLE. Here “ * “ signifies the selection of all rows from the table. If we want only one specific column,  then we can mention that in the place of fetchfieldname. We can even use WHERE clause for the more specific selections. For more information regarding fetch dashDB operations, refer here for the docs.

DELETE

The DELETE statement deletes rows from a table.

There are two forms of this statement

  • The Searched DELETE form is used to delete one or more rows (optionally determined by a search condition).
  • The Positioned DELETE form is used to delete exactly one row (as determined by the current position of a cursor).
SQL Syntax 
DELETE FROM tablename 
DELETE FROM tablename WHERE conditioncolumn = conditioncolumnvalue
SQL Example
DELETE FROM USERTABLE
DELETE FROM USERTABLE WHERE EMAILID = “[email protected]” and PASSWORD = “john123”

In the above example, the first statement deletes all the values in the table USERTABLE, whereas the second statement deletes specific rows based on a condition where EMAILID value is “[email protected]” and PASSWORD value is “john123”. 

You can find more information regarding Delete operation in the docs here.

 

DISCONNECT

We can close the DashDB connection.

API Syntax
ibm_db.close

The above operation will disconnect the dashdb connection. We have to pass the same connection variable which was returned from ibm_db.connect API call.

Error Handling  for dashDB

The ibm_db API  provides exceptions for handling scenarios arising due to either invalid input, database connection failure or internal failures. This helps the developers to recover gracefully from erroneous situations.

Connection Error

We can use one of the following functions to retrieve diagnostic information if a connection attempt fails.

  • Retrieve the SQLSTATE returned by the last connection attempt
ibm_db.conn_error
  • Retrieve a descriptive error message appropriate for an application error log. 
ibm_db.conn_errormsg

SQL Error

Use one of the following functions to retrieve diagnostic information if an attempt to prepare or execute an SQL statement or to fetch a result from a result  set fails.

  • Retrieve the SQL STATE returned by the last attempt to prepare or execute an SQL statement or to fetch a result from a result set.
ibm_db.stmt_error
  • Retrieve a descriptive error message appropiate for an application error log.
ibm_db.stmt_errormsg

Sample Script for Error Handling with ibm_db using Python

Open the following script from the repo, error_handling_examples.py.

You are now empowered will all the knowledge to get a  jumpstart with dashDB. We hope this post has provided you enough information on that and I look forward to your comments and queries. Stay tuned for more tutorials on cloud computing tools and resources.

Follow

About the Author

Shyam is the Creator-in-Chief at RadioStudio. He is a technology buff and is passionate about bringing forth emerging technologies to showcase their true potential to the world. Shyam guides the team at RadioStudio, a bunch of technoholiks, to imagine, conceptualize and build ideas around emerging trends in information and communication technologies.

>