{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95857572-807b-4136-bf3b-e1df99aa3f80",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query\n",
    "from pandas import DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8c478e9-2981-4901-837f-8cadf9d48cae",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection(config_file = 'titanic.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8afb778-8cee-4ea8-b0cf-d345421500bd",
   "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": "36402a44-b306-4278-b4f8-11896faba2db",
   "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": "c2f67dd8-96fd-4571-95c0-9c05b22485a0",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Call stored procedure `some_passengers()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a98dd51-d946-4ffe-93f3-95d9c3955479",
   "metadata": {},
   "source": [
    "### Use SQL's `CALL` command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f743829d-0a34-4a15-8f51-31a12c0fd5ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = 'CALL some_passengers'\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": "7a78a3b7-fddd-449d-80a9-b286d841e00b",
   "metadata": {},
   "source": [
    "### Use the cursor method `callproc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a37de98c-e041-4089-9fa2-0676832b2b8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"cursor.callproc('some_passengers')\")\n",
    "print()\n",
    "\n",
    "cursor.callproc('some_passengers')\n",
    "    \n",
    "for result in cursor.stored_results():\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fd26c2c-5a67-4b81-9b70-6659a5522504",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Call stored procedure `find_passengers()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3f6aa85-0a61-4674-82f5-0363f49a57ec",
   "metadata": {},
   "source": [
    "### Use SQL's `CALL` command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ada1ab17-896f-423c-ad22-0961c72c09ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"CALL find_passengers('female', 21, 23, 'yes', '3rd')\"\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": "44244db0-cf5e-422d-b25d-6d9c4aa63bb1",
   "metadata": {},
   "source": [
    "### Use the cursor method `callproc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14db64ee-c209-450f-8b05-93e4bb10f9b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "arguments = ['female', 21, 23, 'yes', '3rd']\n",
    "\n",
    "print(f\"cursor.callproc('find_passengers', {arguments})\")\n",
    "print()\n",
    "\n",
    "cursor.callproc('find_passengers', arguments)\n",
    "    \n",
    "for result in cursor.stored_results():\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89933a94-d2a1-4dc7-a201-a235847c2c17",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Call stored procedure `count_passengers()`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54e42505-dd2c-46dc-ab5f-5f99cc222693",
   "metadata": {},
   "source": [
    "#### Use user-defined variable `@count`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5706cfae-0f42-4909-ae1f-581e023c00b3",
   "metadata": {},
   "source": [
    "### Use SQL's `CALL` command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ab6c460-0f11-49a3-a825-f9b9aba69a34",
   "metadata": {},
   "outputs": [],
   "source": [
    "# @count is a user-defined variable.\n",
    "sql = \"CALL count_passengers('female', 21, 23, 'yes', '3rd', @count)\"\n",
    "print(sql)\n",
    "print()\n",
    "\n",
    "generator = cursor.execute(sql, multi=True)\n",
    "\n",
    "for result in generator:\n",
    "    for row in result.fetchall():\n",
    "        pass\n",
    "    \n",
    "_, df = dataframe_query(conn, 'SELECT @count AS passenger_count')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de3c4e08-f80e-4a10-a58f-06e2a5b7cc3c",
   "metadata": {},
   "source": [
    "### Use the cursor method `callproc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf4c1899-7c02-4857-928d-ba804d0f6435",
   "metadata": {},
   "outputs": [],
   "source": [
    "# aruments[5] = 0 is a placeholder for the OUT parameter.\n",
    "arguments = ['female', 21, 23, 'yes', '3rd', 0]\n",
    "\n",
    "print(f\"cursor.callproc('find_passengers', {arguments})\")\n",
    "print()\n",
    "\n",
    "results = cursor.callproc('count_passengers', arguments)\n",
    "\n",
    "print('Returned results =', results)\n",
    "print('Count is', results[5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c2a1081-3ebe-4db9-a1f1-cbd5e36c2437",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2e17e14-8459-41b6-b3f7-eb16d9e87af1",
   "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
}