Access_SQLite_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create & Access SQLite database using Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create database using SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connecting to sqlite\n",
    "# connection object\n",
    "\n",
    "conn = sqlite3.connect(\"INSTRUCTOR.db\")\n",
    "cursor_obj = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table is ready!\n"
     ]
    }
   ],
   "source": [
    "# Drop table if already exists\n",
    "\n",
    "cursor_obj.execute(\"DROP TABLE IF EXISTS INSTRUCTOR\")\n",
    "\n",
    "# Creating Table\n",
    "\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": [
    "### Insert data into the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1bfd938abc0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1bfd938abc0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor_obj.execute('''insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query data in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 8,
   "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 table\n",
    "statement = '''SELECT * FROM INSTRUCTOR'''\n",
    "cursor_obj.execute(statement)\n",
    "\n",
    "print(\"All the data\")\n",
    "# If you want to fecth 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": {},
   "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",
    "\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 col in output_column:\n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1bfd938abc0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "query_update = '''update INSTRUCTOR set CITY = \"MOOSETOWN\" where FNAME=\"Rav\"'''\n",
    "cursor_obj.execute(query_update)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All 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 Data\")\n",
    "output1 = cursor_obj.fetchmany(2)\n",
    "for row in output1:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Retrieve data into Pandas"
   ]
  },
  {
   "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",
       "    <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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# retrieve the query results into a pandas dataframe\n",
    "\n",
    "df = pd.read_sql_query(\"SELECT * FROM INSTRUCTOR;\", conn)\n",
    "\n",
    "# print the dataframe\n",
    "\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": "code",
   "execution_count": 14,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ID\n",
       "count  3.0\n",
       "mean   2.0\n",
       "std    1.0\n",
       "min    1.0\n",
       "25%    1.5\n",
       "50%    2.0\n",
       "75%    2.5\n",
       "max    3.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, 5)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 5 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   ID      3 non-null      int64 \n",
      " 1   FNAME   3 non-null      object\n",
      " 2   LNAME   3 non-null      object\n",
      " 3   CITY    3 non-null      object\n",
      " 4   CCODE   3 non-null      object\n",
      "dtypes: int64(1), object(4)\n",
      "memory usage: 252.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close the Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the connection\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}