{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a701441-7746-4ed0-8b53-88565eb0c1cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query\n",
    "from pandas import DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1385465-e65d-4c7d-89ea-10529b3934ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection(config_file = 'school.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba820f06-f09c-4845-8f83-991dda5fcfb4",
   "metadata": {},
   "source": [
    "#### A Python function that displays in a dataframe the results from a call to a stored procedure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "163737b4-5663-40e4-884d-97183a9d1978",
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_results(cursor):\n",
    "    \"\"\"\n",
    "    If there are results in the cursor from a call to a\n",
    "    stored procedure, display the results in a dataframe. \n",
    "    \"\"\"\n",
    "    columns = result.description\n",
    "    \n",
    "    if columns == None:\n",
    "        return \n",
    "    \n",
    "    else:\n",
    "        column_names = [column_info[0] \n",
    "                        for column_info in columns]\n",
    "\n",
    "        rows = cursor.fetchall()\n",
    "    \n",
    "        if len(rows) > 0:\n",
    "            df = DataFrame(rows)\n",
    "            df.columns = column_names\n",
    "            \n",
    "            display(df)\n",
    "        \n",
    "        else:\n",
    "            return"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "146af91b-35e2-463f-bb0f-944bca316948",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Call stored procedure `all_students()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd108609-4aff-457e-b471-e5e855206f43",
   "metadata": {},
   "source": [
    "#### There are two ways to call a stored procedure from Python: use SQL's `CALL` command, or use the cursor's `callproc()` method. Each requires a different way to retrieve the tabular results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e0cfa26-42d3-43de-aee4-0f013acfa27f",
   "metadata": {},
   "source": [
    "### Use SQL's `CALL` command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d18a017-c52e-4e79-ae54-319ce3f93adb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = 'CALL all_students'\n",
    "print(sql)\n",
    "print()\n",
    "\n",
    "generator = cursor.execute(sql, multi=True)\n",
    "\n",
    "for result in generator:\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82102ab9-db4e-4b96-aa7e-d74d05c17b32",
   "metadata": {},
   "source": [
    "### Use the cursor method `callproc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1362851c-51fc-4c74-b7fd-1061764f3067",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"cursor.callproc('all_students')\")\n",
    "print()\n",
    "\n",
    "cursor.callproc('all_students')\n",
    "\n",
    "for result in cursor.stored_results():\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dcb918b-43d4-4adc-b59c-5664c1c0695a",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Call stored procedure `class_codes_of()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c42edf7c-8246-4c74-8ac9-47f902933f71",
   "metadata": {},
   "source": [
    "#### First get a list of students' names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84c7a01a-06f4-4be5-9503-de473cedaf58",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('SELECT first, last FROM student')\n",
    "\n",
    "students = cursor.fetchall()\n",
    "students"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "193d5b1b-8f16-4ff7-8094-b1b82de4c0cc",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Use SQL's `CALL` command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78a4845d-f5fd-442a-8179-5dd33bbae389",
   "metadata": {},
   "outputs": [],
   "source": [
    "for first, last in students:\n",
    "    sql = f\"CALL class_codes_of('{first}', '{last}')\"\n",
    "    print()\n",
    "    print(sql)\n",
    "    print()\n",
    "    \n",
    "    generator = cursor.execute(sql, multi=True)\n",
    "    \n",
    "    for result in generator:\n",
    "        display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "600c5f50-5a48-4718-af2d-94d0edac2859",
   "metadata": {},
   "source": [
    "### Use the cursor method `callproc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddc45227-8731-470f-8798-4974d37db47c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for student in students:\n",
    "    first, last = student\n",
    "    \n",
    "    print()\n",
    "    print(f\"cursor.callproc('class_codes_of', {student})\")\n",
    "    print()\n",
    "    \n",
    "    cursor.callproc('class_codes_of', student)\n",
    "    \n",
    "    for result in cursor.stored_results():\n",
    "        display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85cb25fe-a9e3-4b4d-9cd7-0c569222dbf5",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Call stored procedure `classmates_of()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea672181-a1cb-45f0-ba1b-21ec30611d9a",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Use SQL's `CALL` command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34221da6-2752-4bb6-8b1e-793a7f4aea34",
   "metadata": {},
   "outputs": [],
   "source": [
    "for first, last in students:\n",
    "    sql = f\"CALL classmates_of('{first}', '{last}')\"\n",
    "    print()\n",
    "    print(sql)\n",
    "    print()\n",
    "    \n",
    "    generator = cursor.execute(sql, multi=True)\n",
    "    \n",
    "    for result in generator:\n",
    "        display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96025eef-a54d-4912-a147-554e12794c73",
   "metadata": {},
   "source": [
    "### Use the cursor method `callproc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f25fe985-af30-4508-b54b-8adb9835b65a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for student in students:\n",
    "    first, last = student\n",
    "    \n",
    "    print()\n",
    "    print(f\"cursor.callproc('classmates_of', {student})\")\n",
    "    print()\n",
    "    \n",
    "    cursor.callproc('classmates_of', student)\n",
    "    \n",
    "    for result in cursor.stored_results():\n",
    "        display_results(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9f2f764-4da8-4cab-8d47-34892816e0ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae3eab62-e0c2-47a1-bbeb-53cacb1e11db",
   "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
}