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.
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.
Before you can use dashDB in any application, you must set up and instantiate a dashDB service instance under your IBM Bluemix account.
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.
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.
sudo apt-get update sudo apt-get install python-2.7
sudo pip install ibm_db
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.
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.
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.
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,”email@example.com”,”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 = “firstname.lastname@example.org” 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
DELETE FROM tablename DELETE FROM tablename WHERE conditioncolumn = conditioncolumnvalue
DELETE FROM USERTABLE DELETE FROM USERTABLE WHERE EMAILID = “email@example.com” 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 “firstname.lastname@example.org” 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.
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.
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.
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.
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.
Worrying About Rising Energy Bills? Realtime IoT Energy Management Is At Your Disposal
How To Make Your Smart Bin Smarter With IBM Bluemix
Saving dogs with IBM Watson Visual Recognition
Model IoT Application For Tracking Kitchen Inventory
Microservice in action : Build a weather enabled chat
Build a real-time recommendation engine with IBM Bluemix and PubNub – Part 2
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.