{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# MySQL",
   "id": "3a0d35af529a11d8"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Install Dependencies",
   "id": "ff2a0e1809c2023c"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "! pip install pymysql\n",
    "! pip install plotly\n",
    "! pip install ipython-sql\n",
    "!pip install jupyter-server-proxy"
   ],
   "id": "5423164e-075e-4815-9ce2-5fec397756c4",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "49cc314e-6c00-4a8e-9d4e-512b2f5d2a21",
   "metadata": {},
   "source": "import pandas as pd",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "6a68f51a-1fe9-4707-a6e9-3bf2da2437c1",
   "metadata": {},
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "DB_USER = \"USER\"\n",
    "DB_PASSWORD = \"PASSWORD\"\n",
    "DB_URL = \"URL\"\n",
    "\n",
    "engine = create_engine(f'mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_URL}:3306/mysql')"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "e3481dc2-cd43-4754-8267-128ab0c2ddf8",
   "metadata": {},
   "source": [
    "df = pd.read_sql_query(\"show tables\", engine)\n",
    "df"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "59364484-0e9a-4672-8746-dec11323b832",
   "metadata": {},
   "source": [
    "df = pd.read_sql_query(\"select * from TABLE_NAME\", engine)\n",
    "\n",
    "df"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## D-Tale\n",
    "\n",
    "For this example, we create a Pandas DataFrame with random data and then save it to a MySQL database. We then use D-Tale to visualize the data."
   ],
   "id": "bc3271ce453943e8"
  },
  {
   "cell_type": "code",
   "id": "34bc00b5-2794-4106-9d60-eae3f423d58b",
   "metadata": {},
   "source": [
    "import random \n",
    "\n",
    "table = []\n",
    "metrics =  [\"Accuracy\",\"Precision\"]\n",
    "labels = [\"L0\",\"L1\",\"L2\"]\n",
    "for _ in range(100):\n",
    "    for metric in metrics:\n",
    "        for label in labels:\n",
    "            \n",
    "            table.append({\"Metric\": metric,\"Value\": random.uniform(0.0, 1.0), \"Label\": label})\n"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "eda5cd92-1fa3-464d-a358-a8b50da403dc",
   "metadata": {},
   "source": [
    "df = pd.DataFrame(table)"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "4c41e212-e5a3-4ac8-8c5d-74f6f748703c",
   "metadata": {},
   "source": [
    "df.to_sql(con = engine, name = \"Demo\", if_exists='replace', index = False)"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "ca2e7a57-5b21-4fed-a59c-6e9037577996",
   "metadata": {},
   "source": [
    "!pip install dtale"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "b8c603e3-d82f-4cb4-816f-9061caa56618",
   "metadata": {},
   "source": [
    "import dtale\n",
    "import dtale.app as dtale_app\n",
    "\n",
    "dtale_app.JUPYTER_SERVER_PROXY = True\n",
    "\n",
    "d = dtale.show(df,host=\"0.0.0.0\",)"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": [
    "from IPython.display import Markdown\n",
    "from IPython.core.magic import register_cell_magic\n",
    "import os\n",
    "\n",
    "\n",
    "DTALE_URL = d._main_url\n",
    "@register_cell_magic\n",
    "def markdown(line, cell):\n",
    "    return Markdown(cell.format(**globals()))"
   ],
   "id": "c8b63e7eba03e168"
  },
  {
   "cell_type": "code",
   "id": "14b5d253-2294-4bb6-bf31-cfbea400f2e8",
   "metadata": {},
   "source": [
    "%%markdown\n",
    "\n",
    "[DTale]({DTALE_URL})"
   ],
   "outputs": [],
   "execution_count": null
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}