Week4_Insert_Update_SQLite.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",
    "# Create & Access SQLite database using Python\n",
    "\n",
    "Estimated time needed: **15** minutes\n",
    "\n",
    "## Objectives\n",
    "\n",
    "After completing this lab you will be able to:\n",
    "\n",
    "*   Create a database\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": [
    "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.\n",
    "\n",
    "## Task 1: Create database using SQLite\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Install & load sqlite3\n",
    "#!pip install sqlite3  ##Uncomment the code to install sqlite3\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connecting to sqlite\n",
    "# connection object\n",
    "conn = sqlite3.connect('INSTRUCTOR.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cursor class is an instance using which you can invoke methods that execute SQLite statements, fetch data from the result sets of the queries. You can create Cursor object using the cursor() method of the Connection object/class.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# cursor object\n",
    "cursor_obj = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 2: 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://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/images/table.png\" align=\"center\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before creating a table, let's first if the table already exist or not. To drop the table from a database use **DROP** query. A cursor is an object which helps to execute the query and fetch the records from the database.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7fe7319fcc00>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop the table if already exists.\n",
    "cursor_obj.execute(\"DROP TABLE IF EXISTS INSTRUCTOR\")"
   ]
  },
  {
   "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",
    "<strong>Exception: \\[IBM]\\[CLI Driver]\\[DB2/LINUXX8664] SQL0204N  \"ABC12345.INSTRUCTOR\" is an undefined name.  SQLSTATE=42704 SQLCODE=-204</strong>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table is Ready\n"
     ]
    }
   ],
   "source": [
    "# Creating table\n",
    "table = \"\"\" create table IF NOT EXISTS INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2));\"\"\"\n",
    " \n",
    "cursor_obj.execute(table)\n",
    " \n",
    "print(\"Table is Ready\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 3: 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://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera_V5/images/table1.png\" 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": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7fe7319fcc00>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output you will get something as: <strong>sqlite3.Cursor at 0x27a1a491260</strong> which means mySql database has sqlite3.Cursor object at 0x27a1a49126 as output in table. But you may get the different number.\n",
    "\n",
    "Now use a single query to insert the remaining two rows of data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor_obj.execute('''insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 4: 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": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "(1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\n"
     ]
    }
   ],
   "source": [
    "statement = '''SELECT * FROM INSTRUCTOR'''\n",
    "cursor_obj.execute(statement)\n",
    "\n",
    "print(\"All the data\")\n",
    "output_all = cursor_obj.fetchall()\n",
    "for row_all in output_all:\n",
    "  print(row_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "(1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\n"
     ]
    }
   ],
   "source": [
    "## Fetch few rows from the table\n",
    "statement = '''SELECT * FROM INSTRUCTOR'''\n",
    "cursor_obj.execute(statement)\n",
    "  \n",
    "print(\"All the data\")\n",
    "# If you want to fetch few rows from the table we use fetchmany(numberofrows) and mention the number how many rows you want to fetch\n",
    "output_many = cursor_obj.fetchmany(2) \n",
    "for row_many in output_many:\n",
    "  print(row_many)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "('Rav',)\n"
     ]
    }
   ],
   "source": [
    "# Fetch only FNAME from the table\n",
    "statement = '''SELECT FNAME FROM INSTRUCTOR'''\n",
    "cursor_obj.execute(statement)\n",
    "  \n",
    "print(\"All the data\")\n",
    "output_column = cursor_obj.fetchall()\n",
    "for fetch in output_column:\n",
    "  print(fetch)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<Strong>Bonus: now write and execute an update statement that changes the Rav's CITY to MOOSETOWN</strong>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7fe7319fcc00>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_update='''update INSTRUCTOR set CITY='MOOSETOWN' where FNAME=\"Rav\"'''\n",
    "cursor_obj.execute(query_update)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "(1, 'Rav', 'Ahuja', 'MOOSETOWN', 'CA')\n"
     ]
    }
   ],
   "source": [
    "statement = '''SELECT * FROM INSTRUCTOR'''\n",
    "cursor_obj.execute(statement)\n",
    "  \n",
    "print(\"All the data\")\n",
    "output1 = cursor_obj.fetchmany(2)\n",
    "for row in output1:\n",
    "  print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 5: 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": 12,
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID FNAME  LNAME       CITY CCODE\n",
       "0   1   Rav  Ahuja  MOOSETOWN    CA"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#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",
    "df = pd.read_sql_query(selectQuery, conn)\n",
    "\n",
    "#print the dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Ahuja'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print just the LNAME for first row in the pandas data frame\n",
    "df.LNAME[0]"
   ]
  },
  {
   "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": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 5)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 6: 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": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "In this tutorial you created a database & table in Python notebook using SQLite3. 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/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Rav Ahuja</a>\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/malika-goyal-04798622/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Malika</a>\n",
    "\n",
    "## Other Contributor(s)\n",
    "\n",
    "<a href=\"https://www.linkedin.com/in/lakshmi-holla-b39062149/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01\" target=\"_blank\">Lakshmi Holla</a>\n",
    "\n",
    "## Change Log\n",
    "\n",
    "| Date (YYYY-MM-DD) | Version | Changed By | Change Description        |\n",
    "| ----------------- | ------- | ---------- | ------------------------- |\n",
    "| 2022-03-03        | 1.0     | Malika     | Created lab using sqlite3 |\n",
    "\n",
    "<hr>\n",
    "\n",
    "## <h3 align=\"center\"> © IBM Corporation 2022. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}