view_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import logging\n",
    "\n",
    "from sqlalchemy.exc import OperationalError\n",
    "from src.methods.extractors.databaseConnectors import DatabaseConnector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "def csv_to_dataframe(file_path: str, sep: str) -> pd.DataFrame:\n",
    "    \"\"\"Function to read csv files and \n",
    "    return as a pandas Dataframe\n",
    "\n",
    "    Args:\n",
    "        file_path (str): path of the csv files\n",
    "        sep (str): csv files separator\n",
    "\n",
    "    Returns:\n",
    "        pd.Dataframe: returns a concatenated dataframe\n",
    "    \"\"\"  \n",
    "    dfs = []\n",
    "\n",
    "    for filename in os.listdir(file_path):\n",
    "        if filename.endswith('.csv'):\n",
    "            try:\n",
    "                dfs.append(pd.read_csv(os.path.join(file_path, filename), sep=sep))\n",
    "            except Exception as e:\n",
    "                print(f'Error reading file {filename}: {e}')\n",
    "    if not dfs:\n",
    "        print(f'No CSV files were found. Check the file_path: {file_path}')\n",
    "    \n",
    "    return pd.concat(dfs, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def json_to_dataframe(file_path: str) -> pd.DataFrame:\n",
    "    \"\"\"Function to read json files and\n",
    "    return as a pandas Dataframe.\n",
    "\n",
    "    Args:\n",
    "        file_path (str): path of the json files\n",
    "\n",
    "    Returns:\n",
    "        pd.Dataframe: returns a concatenated dataframe\n",
    "    \"\"\"    \n",
    "    dfs = []\n",
    "    \n",
    "    for filename in os.listdir(file_path):\n",
    "        if filename.endswith('.json'):\n",
    "            try:\n",
    "                dfs.append(pd.read_json(os.path.join(file_path, filename)))\n",
    "            except Exception as e:\n",
    "                print(f'Erro reading file {filename}: {e}')\n",
    "    if not dfs:\n",
    "        print(f'Any Json file was found. Check the file_path: {file_path}')\n",
    "        \n",
    "    return pd.concat(dfs, ignore_index=True)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Ainda estou criando esta função\n",
    "\n",
    "def sql_to_dataframe(connection_string: str, \n",
    "                     table: str, \n",
    "                     columns: list = None,\n",
    "                     where: str = None, \n",
    "                     limit: int = None\n",
    ") -> pd.DataFrame:\n",
    "    \n",
    "    from sqlalchemy.exc import OperationalError\n",
    "    \n",
    "    db_connector = DatabaseConnector(connection_string)\n",
    "    db_connector.connect()\n",
    "\n",
    "    if db_connector._db_session:\n",
    "        try:\n",
    "            query = db_connector.build_query_string(table, columns, where, limit)\n",
    "            sanitized_query = db_connector.sanitize_query(query)\n",
    "            data = db_connector.query_data(sanitized_query)\n",
    "            if columns is None or columns == []:\n",
    "                columns = [column[0] for column in data.cursor.description]\n",
    "            df = pd.DataFrame(data, columns=columns)\n",
    "            return df\n",
    "        except OperationalError as e:\n",
    "            print(f\"Error executing query: {e}\")\n",
    "        finally:\n",
    "            db_connector.disconnect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_dataframe(df: pd.DataFrame, \n",
    "                   sep: str, \n",
    "                   output_path: str, \n",
    "                   output_filename: str\n",
    "):\n",
    "    \"\"\" Save a pandas dataframe as CSV format\n",
    "\n",
    "    Args:\n",
    "        df (pd.DataFrame): dataframe to be saved\n",
    "        sep (str): CSV separator\n",
    "        output_path (str): file landing path\n",
    "        output_filename (str): file output name\n",
    "    \"\"\"    \n",
    "    try:\n",
    "        output_file = os.path.join(output_path, f'{output_filename}.csv')\n",
    "        df.to_csv(output_file, sep=sep, index=False)\n",
    "        print(f'Dataframe created in {output_file}')\n",
    "    except Exception as e:\n",
    "        print(f'Error while compiling dataframe: {e}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hour</th>\n",
       "      <th>wave_height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00 AM</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02:00 AM</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>03:00 AM</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>04:00 AM</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1147</th>\n",
       "      <td>19:00 PM</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1148</th>\n",
       "      <td>20:00 PM</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149</th>\n",
       "      <td>21:00 PM</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1150</th>\n",
       "      <td>22:00 PM</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1151</th>\n",
       "      <td>23:00 PM</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1152 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          hour  wave_height\n",
       "0     00:00 AM          1.2\n",
       "1     01:00 AM          0.8\n",
       "2     02:00 AM          0.9\n",
       "3     03:00 AM          1.1\n",
       "4     04:00 AM          1.8\n",
       "...        ...          ...\n",
       "1147  19:00 PM          1.9\n",
       "1148  20:00 PM          2.1\n",
       "1149  21:00 PM          2.3\n",
       "1150  22:00 PM          2.4\n",
       "1151  23:00 PM          1.8\n",
       "\n",
       "[1152 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe created in C:\\Users\\Vegh\\Desktop\\teste\\teste_novo.csv\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "df = csv_to_dataframe(r'C:\\Users\\Vegh\\Desktop\\teste', ',')\n",
    "display(df)\n",
    "save_dataframe(df, ',', r'C:\\Users\\Vegh\\Desktop\\teste', 'teste_novo')\n",
    "\n",
    "df = sql_to_dataframe(\n",
    "        connection_string=\"postgresql://cdgdwiuoccdozrbanjrnukrm%40psql-mock-database-cloud:opuxfcqkturbouyhofzjnvqt@psql-mock-database-cloud.postgres.database.azure.com:5432/booking1689961160721moxzizfjzzlvnyyq\",\n",
    "        table='bookings',\n",
    "        columns=['*'],\n",
    "        where=None,\n",
    "        limit=100\n",
    "    )\n",
    "\n",
    "display(df)\n",
    "'''\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}