{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f637ded-b98e-4845-8782-3fdd6f065b72",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "from data201 import db_connection, df_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "365d9407-2861-409d-a6f4-b6287a2a6f07",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_connection(config_file = 'month.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b86c7262-2ac4-429c-9cc0-1db9e3cf1697",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TABLE IF EXISTS dates')\n",
    "\n",
    "sql = \"\"\"\n",
    "    CREATE TABLE dates\n",
    "    (\n",
    "        id                INT AUTO_INCREMENT,\n",
    "        method            VARCHAR(16),\n",
    "        full_date         DATE,\n",
    "        day_of_week_name  VARCHAR(9),\n",
    "        day_number        INT,\n",
    "        month_number      INT,\n",
    "        year_number       INT,\n",
    "        PRIMARY KEY(id)\n",
    "    )\n",
    "    \"\"\"\n",
    "\n",
    "cursor.execute(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbe86cd2-d737-4c75-9d3a-9bdfc47cca37",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Call stored procedure `while_proc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2a1781e-d628-4f25-88cc-d6aa448d819a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CALL while_proc('2025-03-1', 6)\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b77ba50-a198-489c-b000-3c445f1936d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM dates LIMIT 31')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d64a04d-6797-4443-a7fa-3707625532ae",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Call stored procedure `repeat_proc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5df402d-f1c2-43d9-9e32-3264d52a7a6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CALL repeat_proc('2025-03-1', 6)\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6480d27-d671-4736-96d2-aa725f8ee174",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM dates LIMIT 31 OFFSET 31')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30beb63a-df5a-48ee-bce2-b49d8c17360e",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Call stored procedure `loop_proc()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c8cb462-bd21-4928-af4c-ede5edfcb4b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CALL loop_proc('2025-03-1', 6)\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf6f56fb-0563-433c-986b-baf2bc800f95",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM dates LIMIT 31 OFFSET 62')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca263d23-4c6d-4368-bf10-1f6065817f14",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "df_query(conn, 'SELECT * FROM dates')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d522b75-7266-4d69-9fc7-dece394b0240",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de213df6-8ead-4d7f-9f5f-21516fa046c2",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}