data_prep_tables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e24874ed-4888-403f-b9ff-0ab3f925bcaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "from dotenv import load_dotenv, find_dotenv\n",
    "import glob\n",
    "import os\n",
    "import pandas as pd\n",
    "import re\n",
    "from sqlalchemy import create_engine, insert, Column, Integer, String, Table, Float, MetaData\n",
    "from sqlalchemy_utils import create_database, database_exists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "54de8ba2-bd0d-4903-b9c8-55d02cced57c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "load_dotenv(find_dotenv())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "472b8150-fd5c-4d70-afd8-ad9cfe5d8ced",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_pwd = os.environ['SQL_PASSWORD']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "adaa009b-989a-4113-874c-2535cadfbb38",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(f'mysql://root:{sql_pwd}@localhost:3306/chat-project')\n",
    "if not database_exists(engine.url):\n",
    "    create_database(engine.url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b79a522d-380a-452b-bc63-7ad357d018cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata_obj = MetaData()\n",
    "datatype_dict = {'int64': Integer, 'float64': Float, 'object': String(128)}\n",
    "files = glob.glob('./data/*.xlsx')\n",
    "\n",
    "for file in files:\n",
    "    table = pd.read_excel(file)\n",
    "    columns = table.columns\n",
    "    name = re.findall('(Table.+)\\.xlsx', file)[0]\n",
    "\n",
    "    table_sql = Table(\n",
    "        name,\n",
    "        metadata_obj,\n",
    "        *[Column(column, datatype_dict[str(table[column].dtype)]) for column in columns]\n",
    "    )\n",
    "\n",
    "    table_sql.create(engine)\n",
    "\n",
    "    for row in table.values:\n",
    "        data = {col_name: value for col_name, value in zip(columns, row)}\n",
    "        stmt = insert(metadata_obj.tables[name]).values(**data)\n",
    "        with engine.begin() as conn:\n",
    "            conn.execute(stmt)"
   ]
  }
 ],
 "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.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}