01Querying.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/Logos/organization_logo/organization_logo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n",
    "</center>\n",
    "\n",
    "# Access DB2 on Cloud using Python\n",
    "\n",
    "Estaimted time needed: **15** minutes\n",
    "\n",
    "## Objectives\n",
    "\n",
    "After complting this lab you will be able to:\n",
    "\n",
    "-   Create a table\n",
    "-   Insert data into the table\n",
    "-   Query data from the table\n",
    "-   Retrieve the result set into a pandas dataframe\n",
    "-   Close the database connection\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Notice:** Please follow the instructions given in the first Lab of this course to Create a database service instance of Db2 on Cloud.\n",
    "\n",
    "## Task 1: Import the `ibm_db` Python library\n",
    "\n",
    "The `ibm_db` [API ](https://pypi.python.org/pypi/ibm_db?cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ) provides a variety of useful Python functions for accessing and manipulating data in an IBM® data server database, including functions for connecting to a database, preparing and issuing SQL statements, fetching rows from result sets, calling stored procedures, committing and rolling back transactions, handling errors, and retrieving metadata.\n",
    "\n",
    "We import the ibm_db library into our Python Application\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibm_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When the command above completes, the `ibm_db` library is loaded in your notebook. \n",
    "\n",
    "## Task 2: Identify the database connection credentials\n",
    "\n",
    "Connecting to dashDB or DB2 database requires the following information:\n",
    "\n",
    "-   Driver Name\n",
    "-   Database name \n",
    "-   Host DNS name or IP address \n",
    "-   Host port\n",
    "-   Connection protocol\n",
    "-   User ID\n",
    "-   User Password\n",
    "\n",
    "**Notice:** To obtain credentials please refer to the instructions given in the first Lab of this course\n",
    "\n",
    "Now enter your database credentials below\n",
    "\n",
    "Replace the placeholder values in angular brackets <> below with your actual database credentials \n",
    "\n",
    "e.g. replace \"database\" with \"BLUDB\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace the placeholder values with the actuals for your Db2 Service Credentials\n",
    "dsn_hostname = \"dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net\" # e.g.: \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\"\n",
    "dsn_uid = \"dsx34122\"       # e.g. \"abc12345\"\n",
    "dsn_pwd = \"hf1+rqq140ccm6hn\"     # e.g. \"7dBZ3wWt9XN6$o0J\"\n",
    "\n",
    "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n",
    "dsn_database = \"BLUDB\"            # e.g. \"BLUDB\"\n",
    "dsn_port = \"50000\"                # e.g. \"50000\" \n",
    "dsn_protocol = \"TCPIP\"            # i.e. \"TCPIP\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 3: Create the database connection\n",
    "\n",
    "Ibm_db API uses the IBM Data Server Driver for ODBC and CLI APIs to connect to IBM DB2 and Informix.\n",
    "\n",
    "Create the database connection\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to database:  BLUDB as user:  dsx34122 on host:  dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net\n"
     ]
    }
   ],
   "source": [
    "#Create database connection\n",
    "#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter\n",
    "dsn = (\n",
    "    \"DRIVER={0};\"\n",
    "    \"DATABASE={1};\"\n",
    "    \"HOSTNAME={2};\"\n",
    "    \"PORT={3};\"\n",
    "    \"PROTOCOL={4};\"\n",
    "    \"UID={5};\"\n",
    "    \"PWD={6};\").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)\n",
    "\n",
    "try:\n",
    "    conn = ibm_db.connect(dsn, \"\", \"\")\n",
    "    print (\"Connected to database: \", dsn_database, \"as user: \", dsn_uid, \"on host: \", dsn_hostname)\n",
    "\n",
    "except:\n",
    "    print (\"Unable to connect: \", ibm_db.conn_errormsg() )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 4: Create a table in the database\n",
    "\n",
    "In this step we will create a table in the database with following details:\n",
    "\n",
    "<img src = \"https://ibm.box.com/shared/static/ztd2cn4xkdoj5erlk4hhng39kbp63s1h.jpg\" align=\"center\">\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Lets first drop the table INSTRUCTOR in case it exists from a previous attempt\n",
    "dropQuery = \"drop table INSTRUCTOR\"\n",
    "\n",
    "#Now execute the drop statment\n",
    "dropStmt = ibm_db.exec_immediate(conn, dropQuery)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dont worry if you get this error:\n",
    "\n",
    "If you see an exception/error similar to the following, indicating that INSTRUCTOR is an undefined name, that's okay. It just implies that the INSTRUCTOR table does not exist in the table - which would be the case if you had not created it previously.\n",
    "\n",
    "Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"ABC12345.INSTRUCTOR\" is an undefined name.  SQLSTATE=42704 SQLCODE=-204\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Construct the Create Table DDL statement - replace the ... with rest of the statement\n",
    "createQuery = \"create table INSTRUCTOR(id INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE VARCHAR(20));\"\n",
    "\n",
    "#Now fill in the name of the method and execute the statement\n",
    "createStmt = ibm_db.exec_immediate(conn, createQuery)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "createQuery = \"create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))\"\n",
    "\n",
    "createStmt = ibm_db.exec_immediate(conn,createQuery)\n",
    "\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 5: Insert data into the table\n",
    "\n",
    "In this step we will insert some rows of data into the table. \n",
    "\n",
    "The INSTRUCTOR table we created in the previous step contains 3 rows of data:\n",
    "\n",
    "<img src=\"https://ibm.box.com/shared/static/j5yjassxefrjknivfpekj7698dqe4d8i.jpg\" align=\"center\">\n",
    "\n",
    "We will start by inserting just the first row of data, i.e. for instructor Rav Ahuja \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Construct the query - replace ... with the insert statement\n",
    "insertQuery = \"INSERT INTO INSTRUCTOR(ID, FNAME, LNAME, CITY, CCODE) VALUES(1, 'Rav', 'Ahuja', 'TORONTO', 'CA');\"\n",
    "\n",
    "#execute the insert statement\n",
    "insertStmt = ibm_db.exec_immediate(conn, insertQuery)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "insertQuery = \"insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\"\n",
    "\n",
    "insertStmt = ibm_db.exec_immediate(conn, insertQuery)\n",
    "\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now use a single query to insert the remaining two rows of data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#replace ... with the insert statement that inerts the remaining two rows of data\n",
    "insertQuery2 = \"INSERT INTO INSTRUCTOR(ID, FNAME, LNAME, CITY, CCODE) VALUES(2, 'Raul', 'Chong', 'Markham', 'CA'),(3, 'Hima', 'Vasudevan', 'Chicago', 'US');\"\n",
    "\n",
    "#execute the statement\n",
    "insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "insertQuery2 = \"insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')\"\n",
    "\n",
    "insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)\n",
    "\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 6: Query data in the table\n",
    "\n",
    "In this step we will retrieve data we inserted into the INSTRUCTOR table. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ID': 1,\n",
       " 0: 1,\n",
       " 'FNAME': 'Rav',\n",
       " 1: 'Rav',\n",
       " 'LNAME': 'Ahuja',\n",
       " 2: 'Ahuja',\n",
       " 'CITY': 'TORONTO',\n",
       " 3: 'TORONTO',\n",
       " 'CCODE': 'CA',\n",
       " 4: 'CA'}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Construct the query that retrieves all rows from the INSTRUCTOR table\n",
    "selectQuery = \"select * from INSTRUCTOR\"\n",
    "\n",
    "#Execute the statement\n",
    "selectStmt = ibm_db.exec_immediate(conn, selectQuery)\n",
    "\n",
    "#Fetch the Dictionary (for the first row only) - replace ... with your code\n",
    "ibm_db.fetch_both(selectStmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "#Construct the query that retrieves all rows from the INSTRUCTOR table\n",
    "selectQuery = \"select * from INSTRUCTOR\"\n",
    "\n",
    "#Execute the statement\n",
    "selectStmt = ibm_db.exec_immediate(conn, selectQuery)\n",
    "\n",
    "#Fetch the Dictionary (for the first row only)\n",
    "ibm_db.fetch_both(selectStmt)\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " ID: 2  FNAME: Raul\n",
      " ID: 3  FNAME: Hima\n"
     ]
    }
   ],
   "source": [
    "#Fetch the rest of the rows and print the ID and FNAME for those rows\n",
    "while ibm_db.fetch_row(selectStmt) != False:\n",
    "   print (\" ID:\",  ibm_db.result(selectStmt, 0), \" FNAME:\",  ibm_db.result(selectStmt, \"FNAME\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "#Fetch the rest of the rows and print the ID and FNAME for those rows\n",
    "while ibm_db.fetch_row(selectStmt) != False:\n",
    "    print (\" ID:\",  ibm_db.result(selectStmt, 0), \" FNAME:\",  ibm_db.result(selectStmt, \"FNAME\"))\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bonus: now write and execute an update statement that changes the Rav's CITY to MOOSETOWN \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Enter your code below\n",
    "updateReq = \"update INSTRUCTOR set CITY='MOOSETOWN' where FNAME = 'Rav' \"\n",
    "updateStmt = ibm_db.exec_immediate(conn, updateReq)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double-click **here** for the solution.\n",
    "\n",
    "<!-- Hint:\n",
    "\n",
    "updateQuery = \"update INSTRUCTOR set CITY='MOOSETOWN' where FNAME='Rav'\"\n",
    "updateStmt = ibm_db.exec_immediate(conn, updateQuery))\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 7: Retrieve data into Pandas\n",
    "\n",
    "In this step we will retrieve the contents of the INSTRUCTOR table into a Pandas dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import ibm_db_dbi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#connection for pandas\n",
    "pconn = ibm_db_dbi.Connection(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Ahuja'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#query statement to retrieve all rows in INSTRUCTOR table\n",
    "selectQuery = \"select * from INSTRUCTOR\"\n",
    "\n",
    "#retrieve the query results into a pandas dataframe\n",
    "pdf = pandas.read_sql(selectQuery, pconn)\n",
    "\n",
    "#print just the LNAME for first row in the pandas data frame\n",
    "pdf.LNAME[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>FNAME</th>\n",
       "      <th>LNAME</th>\n",
       "      <th>CITY</th>\n",
       "      <th>CCODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Rav</td>\n",
       "      <td>Ahuja</td>\n",
       "      <td>MOOSETOWN</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Raul</td>\n",
       "      <td>Chong</td>\n",
       "      <td>Markham</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Hima</td>\n",
       "      <td>Vasudevan</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID FNAME      LNAME       CITY CCODE\n",
       "0   1   Rav      Ahuja  MOOSETOWN    CA\n",
       "1   2  Raul      Chong    Markham    CA\n",
       "2   3  Hima  Vasudevan    Chicago    US"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print the entire data frame\n",
    "pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once the data is in a Pandas dataframe, you can do the typical pandas operations on it. \n",
    "\n",
    "For example you can use the shape method to see how many rows and columns are in the dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 5)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pdf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 8: Close the Connection\n",
    "\n",
    "We free all resources by closing the connection. Remember that it is always important to close connections so that we can avoid unused connections taking up resources.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "ename": "Exception",
     "evalue": "Connection is not active",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-30-59fec21a31bd>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mibm_db\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mException\u001b[0m: Connection is not active"
     ]
    }
   ],
   "source": [
    "ibm_db.close(conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "In this tutorial you established a connection to a database instance of DB2 Warehouse on Cloud from a Python notebook using ibm_db API. Then created a table and insert a few rows of data into it. Then queried the data. You also retrieved the data into a pandas dataframe.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/ravahuja/\" target=\"_blank\">Rav Ahuja</a>\n",
    "\n",
    "## Change Log\n",
    "\n",
    "| Date (YYYY-MM-DD) | Version | Changed By | Change Description                 |\n",
    "| ----------------- | ------- | ---------- | ---------------------------------- |\n",
    "| 2020-08-28        | 2.0     | Lavanya    | Moved lab to course repo in GitLab |\n",
    "\n",
    "<hr>\n",
    "\n",
    "## <h3 align=\"center\"> © IBM Corporation 2020. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}