Created on Cognitive Class Labs

Lab4 Access DB2 on Cloud Using Python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Task 1: Import the ibm_db Python library\n",
    "\n",
    "import ibm_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Task 2: Identify the database connection credentials\n",
    "\n",
    "#Replace the placeholder values with the actuals for your Db2 Service Credentials\n",
    "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n",
    "dsn_database = \"BLUDB\"            # e.g. \"BLUDB\"\n",
    "dsn_hostname = \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\"            # e.g.: \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\"\n",
    "dsn_port = \"50000\"                    # e.g. \"50000\" \n",
    "dsn_protocol = \"TCPIP\"            # i.e. \"TCPIP\"\n",
    "dsn_uid = \"dfk30111\"                 # e.g. \"abc12345\"\n",
    "dsn_pwd = \"b6q01rtcjz^q4sk8\"                 # e.g. \"7dBZ3wWt9XN6$o0J\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to database:  BLUDB as user:  dfk30111 on host:  dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\n"
     ]
    }
   ],
   "source": [
    "#Task 3: Create the database connection\n",
    "\n",
    "#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": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Task 4: Create a table in the database\n",
    "\n",
    "#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": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Task 5: Insert data into the table\n",
    "\n",
    "insertQuery = \"insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\"\n",
    "\n",
    "insertStmt = ibm_db.exec_immediate(conn, insertQuery)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "insertQuery2 = \"insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')\"\n",
    "\n",
    "insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Task 6: Query data in the table\n",
    "\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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Bonus: now write and execute an update statement that changes the Rav's CITY to MOOSETOWN\n",
    "\n",
    "updateQuery = \"update INSTRUCTOR set CITY='MOOSETOWN' where FNAME='Rav'\"\n",
    "updateStmt = ibm_db.exec_immediate(conn, updateQuery)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Task 7: Retrieve data into Pandas\n",
    "\n",
    "import pandas\n",
    "import ibm_db_dbi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#connection for pandas\n",
    "pconn = ibm_db_dbi.Connection(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Ahuja'"
      ]
     },
     "execution_count": 15,
     "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": 16,
   "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": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print the entire data frame\n",
    "pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 5)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Task 8: Close the Connection\n",
    "\n",
    "ibm_db.close(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}