DB_practice.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9981365f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5924b3c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "03281eae",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('INSTRUCTOR.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0e5365ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10fdb14c0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor_obj = conn.cursor()\n",
    "# Drop the table if already exists.\n",
    "cursor_obj.execute(\"DROP TABLE IF EXISTS INSTRUCTOR\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "901bc9c5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table is Ready\n"
     ]
    }
   ],
   "source": [
    "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": "code",
   "execution_count": 8,
   "id": "7d98c9f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10fdb14c0>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')\n",
    "cursor_obj.execute('''insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4f187f5a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "(1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\n",
      "(2, 'Raul', 'Chong', 'Markham', 'CA')\n",
      "(3, 'Hima', 'Vasudevan', 'Chicago', 'US')\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": 10,
   "id": "db5d91e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "(1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\n",
      "(2, 'Raul', 'Chong', 'Markham', '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": 11,
   "id": "7d8f1243",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "('Rav',)\n",
      "('Raul',)\n",
      "('Hima',)\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": "code",
   "execution_count": 12,
   "id": "a735a12c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10fdb14c0>"
      ]
     },
     "execution_count": 12,
     "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": 13,
   "id": "066d0603",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All the data\n",
      "(1, 'Rav', 'Ahuja', 'MOOSETOWN', 'CA')\n",
      "(2, 'Raul', 'Chong', 'Markham', '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",
   "id": "8cde8c86",
   "metadata": {},
   "source": [
    "Retrieve data into Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7f4c9e64",
   "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": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "#retrieve the query results into a pandas dataframe\n",
    "df = pd.read_sql_query(\"select * from instructor;\", conn)\n",
    "\n",
    "#print the dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "891e37fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Ahuja'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print just the LNAME for first row in the pandas data frame\n",
    "df.LNAME[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "19fd7971",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 5)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6be0bccc",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e724fd68",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}