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.
- Introducing IBM dashDB
- Setting up dashDB Service on IBM Bluemix
- Setting up Python libraries for accessing dashDB
- Kickstart your first Python script with dashDB
- Error handling for dashDB
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.
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.
- 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.
- Login to the Bluemix console with your IBM Id credentials.
- Click on the Catalog, to view all the services.
- Under the Bluemix dashboard, go to the catalog and select Data and Analytics section.You can see that the dashDB service will be listed under Data & Analytics section or you can search for dashDB.
- 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).
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.
- In the dashDB service home page, under the Side Menu, select Connect → Connection Information.
- 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.
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).
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.
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.
- For creating a non-persistent connection use following syntax,
- For creating a persistent connection use following syntax,
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.
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.
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.
CREATE TABLE tablename (col1 datatype 1,col2 datatype 2) PRIMARY KEY (col1)
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.
The INSERT statement inserts rows into a table.
INSERT INTO tablename (columnname 1,columnname 2) VALUES (value1,value2)
INSERT INTO USERTABLE (ID,EMAILID,PASSWORD,USERNAME,DATEOFCREATION) VALUES (1,”firstname.lastname@example.org”,”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.
The update statement updates the values of specified columns in rows of a dashDB table.
UPDATE tablename SET column_name = update_value WHERE condtion_column_name = condition_column_value
UPDATE USERTABLE SET USERNAME = “MARK” WHERE EMAILID = “email@example.com” 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.
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.
SELECT fetchfieldname FROM tablename
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.
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).
DELETE FROM tablename DELETE FROM tablename WHERE conditioncolumn = conditioncolumnvalue
DELETE FROM USERTABLE DELETE FROM USERTABLE WHERE EMAILID = “firstname.lastname@example.org” 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@example.com” and PASSWORD value is “john123”.
You can find more information regarding Delete operation in the docs here.
We can close the DashDB connection.
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.
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
- Retrieve a descriptive error message appropriate for an application error log.
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.
- Retrieve a descriptive error message appropiate for an application error log.
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.