{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b5a3c360-ddd6-4cdf-b177-a670253fb7f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cb1e52fa-09c5-4e13-83e2-e33849f81aae",
   "metadata": {},
   "outputs": [
    {
     "ename": "Exception",
     "evalue": "Configuration file 'school.ini' doesn't exist.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/s1/ksqw692x33n0d4mn6kwcpdl00000gr/T/ipykernel_36606/32224757.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmake_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconfig_file\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'school.ini'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/~mak/DATA225/NOTEBOOKS/1009/DATA225utils.py\u001b[0m in \u001b[0;36mmake_connection\u001b[0;34m(config_file, section)\u001b[0m\n\u001b[1;32m     45\u001b[0m     \"\"\"\n\u001b[1;32m     46\u001b[0m     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 47\u001b[0;31m         \u001b[0mdb_config\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mread_config\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconfig_file\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     48\u001b[0m         \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMySQLConnection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mdb_config\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/~mak/DATA225/NOTEBOOKS/1009/DATA225utils.py\u001b[0m in \u001b[0;36mread_config\u001b[0;34m(config_file, section)\u001b[0m\n\u001b[1;32m     19\u001b[0m         \u001b[0mparser\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconfig_file\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     20\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 21\u001b[0;31m         raise Exception(f\"Configuration file '{config_file}' \"\n\u001b[0m\u001b[1;32m     22\u001b[0m                         \"doesn't exist.\")\n\u001b[1;32m     23\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mException\u001b[0m: Configuration file 'school.ini' doesn't exist."
     ]
    }
   ],
   "source": [
    "conn = make_connection(config_file = 'school.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72f8e3ef-eb7c-4718-bc42-4e1c3d1891ba",
   "metadata": {},
   "source": [
    "## Nested `SELECT`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "721c69f9-d1fa-4879-bdc1-fdc753813a90",
   "metadata": {},
   "source": [
    "#### Who are the classmates of Tim Novak?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe831143-a36f-41a1-bcff-c4e2a78141c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT s.first, s.last, c.code, c.subject\n",
    "    FROM student s, takes t, class c\n",
    "    WHERE c.code IN (\n",
    "        SELECT c.code \n",
    "        FROM student s, class c, takes t\n",
    "        WHERE s.first = \"Tim\"\n",
    "        AND   s.last = \"Novak\"\n",
    "        AND   s.id = t.student_id\n",
    "        AND   c.code = t.class_code\n",
    "    )\n",
    "    AND (c.code = t.class_code)\n",
    "    AND (s.id = student_id)\n",
    "    AND NOT (    (s.first = \"Tim\")\n",
    "             AND (s.last = \"Novak\"))\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b79978af-27cb-41f4-a313-b388803b798d",
   "metadata": {},
   "source": [
    "## Use of a view instead\n",
    "\n",
    "#### Create a view from the nested `SELECT`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1047b666-2fc0-44f0-a8af-6d4a147ac67f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP VIEW IF EXISTS class_codes_of_novak')\n",
    "\n",
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE VIEW class_codes_of_novak AS\n",
    "    SELECT c.code \n",
    "    FROM student s, class c, takes t\n",
    "    WHERE s.first = \"Tim\"\n",
    "    AND   s.last = \"Novak\"\n",
    "    AND   s.id = t.student_id\n",
    "    AND   c.code = t.class_code\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45d8f283-3458-4524-bebd-29998ae26629",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, 'SELECT * FROM class_codes_of_novak')\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e46c2a9-3d08-4302-89e0-8aeba1f7f5bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT s.first, s.last, c.code, c.subject\n",
    "    FROM student s, takes t, class c\n",
    "    WHERE c.code IN (\n",
    "        SELECT * FROM class_codes_of_novak\n",
    "    )\n",
    "    AND (c.code = t.class_code)\n",
    "    AND (s.id = student_id)\n",
    "    AND NOT (    (s.first = \"Tim\")\n",
    "             AND (s.last = \"Novak\"))\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c61ebb9-3a58-4b65-b16a-6b2cc61fbd32",
   "metadata": {},
   "source": [
    "## A better use of the view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e83fe3e7-01a9-4979-8750-ad56fb0635e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT s.first, s.last, c.code, c.subject\n",
    "    FROM student s, takes t, class c, \n",
    "         class_codes_of_novak ccn\n",
    "    WHERE (c.code = ccn.code)\n",
    "    AND (c.code = t.class_code)\n",
    "    AND (s.id = student_id)\n",
    "    AND NOT (    (s.first = \"Tim\")\n",
    "             AND (s.last = \"Novak\"))\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6956dc99-a167-4517-b5f0-e8a5d38745db",
   "metadata": {},
   "source": [
    "## A classmates function\n",
    "\n",
    "#### In this case, we don't bother with creating a view since we are inserting the student's first and last names into the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e62c4952-c1de-4d7f-9240-bd4978bf87ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "def classmates_of(first, last):\n",
    "    _, df = dataframe_query(conn, \n",
    "        \"\"\"\n",
    "        SELECT s.first, s.last, c.code, c.subject\n",
    "        FROM student s, takes t, class c\n",
    "        WHERE c.code IN (\n",
    "            SELECT c.code \n",
    "            FROM student s, class c, takes t\n",
    "            \"\"\"\n",
    "          f'WHERE s.first = \"{first}\"'\n",
    "          f'AND   s.last = \"{last}\"'\n",
    "            \"\"\"\n",
    "            AND   s.id = t.student_id\n",
    "            AND   c.code = t.class_code\n",
    "        )\n",
    "        AND (c.code = t.class_code)\n",
    "        AND (s.id = student_id)\n",
    "        \"\"\"\n",
    "      f'AND NOT (    (s.first = \"{first}\")'\n",
    "      f'         AND (s.last = \"{last}\"))'\n",
    "                       )\n",
    "                       \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dddd8b74-f6f8-455e-ae20-e9d081236ccd",
   "metadata": {},
   "outputs": [],
   "source": [
    "classmates_of('Tim', 'Novak')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11df64bc-e65e-4381-88dc-57b0370fabb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('SELECT first, last FROM student')\n",
    "\n",
    "students = cursor.fetchall()\n",
    "students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6c4388b-0f9c-4fe4-87f0-c7a2dda03d1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "for first, last in students:\n",
    "    print()\n",
    "    print(f\"Classmates of {first + ' ' + last}:\")\n",
    "    \n",
    "    display(classmates_of(first, last))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23fc777e-1e5d-4d4b-9787-99cef56ce553",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "840a4f11-4c7f-4e61-bc95-872f4ab26b72",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}