import_sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\moham\\AppData\\Local\\Temp\\ipykernel_10956\\465980585.py:1: DeprecationWarning: \n",
      "Pyarrow will become a required dependency of pandas in the next major release of pandas (pandas 3.0),\n",
      "(to allow more performant data types, such as the Arrow string type, and better interoperability with other libraries)\n",
      "but was not found to be installed on your system.\n",
      "If this would cause problems for you,\n",
      "please provide us feedback at https://github.com/pandas-dev/pandas/issues/54466\n",
      "        \n",
      "  import pandas as pd\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to the database.\n",
      "Table `hrdata` created or already exists.\n",
      "Data from `./hrdata.csv` inserted successfully into `hrdata`.\n",
      "Database connection closed.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import psycopg2\n",
    "import os\n",
    "\n",
    "# List of CSV files and their corresponding table names\n",
    "csv_files = [\n",
    "    ('./hrdata.csv', 'hrdata'), # Added payments.csv for specific handling\n",
    "]\n",
    "\n",
    "# Folder containing the CSV files\n",
    "folder_path = 'C:/Users/moham/Desktop/Data Analysis/All_PowerBi Project/HR Analysis'\n",
    "# df = pd.read_csv(file_path, encoding='ISO-8859-1')\n",
    "\n",
    "\n",
    "# Define a function to map pandas data types to SQL types\n",
    "def get_sql_type(dtype):\n",
    "    if pd.api.types.is_integer_dtype(dtype):\n",
    "        return 'INTEGER'\n",
    "    elif pd.api.types.is_float_dtype(dtype):\n",
    "        return 'REAL'\n",
    "    elif pd.api.types.is_bool_dtype(dtype):\n",
    "        return 'BOOLEAN'\n",
    "    elif pd.api.types.is_datetime64_any_dtype(dtype):\n",
    "        return 'TIMESTAMP'\n",
    "    else:\n",
    "        return 'TEXT'\n",
    "\n",
    "try:\n",
    "    # Connect to the PostgreSQL database\n",
    "    conn = psycopg2.connect(\n",
    "        host='localhost',\n",
    "        user='postgres',\n",
    "        password='mohammad',\n",
    "        dbname='HR Analysis',\n",
    "        port='5432'  # typically 5432 for PostgreSQL\n",
    "    )\n",
    "    cursor = conn.cursor()\n",
    "    print(\"Connected to the database.\")\n",
    "\n",
    "    # Loop over CSV files and load each into PostgreSQL\n",
    "    for csv_file, table_name in csv_files:\n",
    "        file_path = os.path.join(folder_path, csv_file)\n",
    "\n",
    "        # Read the CSV file into a pandas DataFrame\n",
    "        df = pd.read_csv('./hrdata.csv')\n",
    "\n",
    "        # Replace NaN with None to handle SQL NULL\n",
    "        df = df.where(pd.notnull(df), None)\n",
    "        \n",
    "        # Clean column names\n",
    "        df.columns = [col.replace(' ', '_').replace('-', '_').replace('.', '_') for col in df.columns]\n",
    "\n",
    "        # Generate the CREATE TABLE statement with appropriate data types\n",
    "        columns = ', '.join([f'\"{col}\" {get_sql_type(df[col].dtype)}' for col in df.columns])\n",
    "        create_table_query = f'CREATE TABLE IF NOT EXISTS \"{table_name}\" ({columns})'\n",
    "        cursor.execute(create_table_query)\n",
    "        print(f\"Table `{table_name}` created or already exists.\")\n",
    "\n",
    "        # Insert DataFrame data into the PostgreSQL table\n",
    "        for _, row in df.iterrows():\n",
    "            # Convert row to tuple and handle NaN/None explicitly\n",
    "            values = tuple(None if pd.isna(x) else x for x in row)\n",
    "            sql = f\"INSERT INTO \\\"{table_name}\\\" ({', '.join(['\"' + col + '\"' for col in df.columns])}) VALUES ({', '.join(['%s'] * len(row))})\"\n",
    "            cursor.execute(sql, values)\n",
    "\n",
    "        # Commit the transaction for the current CSV file\n",
    "        conn.commit()\n",
    "        print(f\"Data from `{csv_file}` inserted successfully into `{table_name}`.\")\n",
    "\n",
    "except psycopg2.Error as err:\n",
    "    print(f\"Error: {err}\")\n",
    "finally:\n",
    "    # Close the cursor and connection\n",
    "    if cursor:\n",
    "        cursor.close()\n",
    "    if conn:\n",
    "        conn.close()\n",
    "    print(\"Database connection closed.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>emp_no</th>\n",
       "      <th>gender</th>\n",
       "      <th>marital_status</th>\n",
       "      <th>age_band</th>\n",
       "      <th>age</th>\n",
       "      <th>department</th>\n",
       "      <th>education</th>\n",
       "      <th>education_field</th>\n",
       "      <th>job_role</th>\n",
       "      <th>business_travel</th>\n",
       "      <th>employee_count</th>\n",
       "      <th>attrition</th>\n",
       "      <th>attrition_label</th>\n",
       "      <th>job_satisfaction</th>\n",
       "      <th>active_employee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>Female</td>\n",
       "      <td>Single</td>\n",
       "      <td>35 - 44</td>\n",
       "      <td>41</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Associates Degree</td>\n",
       "      <td>Life Sciences</td>\n",
       "      <td>Sales Executive</td>\n",
       "      <td>Travel_Rarely</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Ex-Employees</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>Male</td>\n",
       "      <td>Married</td>\n",
       "      <td>45 - 54</td>\n",
       "      <td>49</td>\n",
       "      <td>R&amp;D</td>\n",
       "      <td>High School</td>\n",
       "      <td>Life Sciences</td>\n",
       "      <td>Research Scientist</td>\n",
       "      <td>Travel_Frequently</td>\n",
       "      <td>1</td>\n",
       "      <td>No</td>\n",
       "      <td>Current Employees</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003</td>\n",
       "      <td>Male</td>\n",
       "      <td>Single</td>\n",
       "      <td>35 - 44</td>\n",
       "      <td>37</td>\n",
       "      <td>R&amp;D</td>\n",
       "      <td>Associates Degree</td>\n",
       "      <td>Other</td>\n",
       "      <td>Laboratory Technician</td>\n",
       "      <td>Travel_Rarely</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Ex-Employees</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   emp_no  gender marital_status age_band  age department          education  \\\n",
       "0   10001  Female         Single  35 - 44   41      Sales  Associates Degree   \n",
       "1   10002    Male        Married  45 - 54   49        R&D        High School   \n",
       "2   10003    Male         Single  35 - 44   37        R&D  Associates Degree   \n",
       "\n",
       "  education_field               job_role    business_travel  employee_count  \\\n",
       "0   Life Sciences        Sales Executive      Travel_Rarely               1   \n",
       "1   Life Sciences     Research Scientist  Travel_Frequently               1   \n",
       "2           Other  Laboratory Technician      Travel_Rarely               1   \n",
       "\n",
       "  attrition    attrition_label  job_satisfaction  active_employee  \n",
       "0       Yes       Ex-Employees                 4                0  \n",
       "1        No  Current Employees                 2                1  \n",
       "2       Yes       Ex-Employees                 3                0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(\"./hrdata.csv\")\n",
    "data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['emp_no', 'gender', 'marital_status', 'age_band', 'age', 'department',\n",
       "       'education', 'education_field', 'job_role', 'business_travel',\n",
       "       'employee_count', 'attrition', 'attrition_label', 'job_satisfaction',\n",
       "       'active_employee'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "emp_no\n",
      "gender\n",
      "marital_status\n",
      "age_band\n",
      "age\n",
      "department\n",
      "education\n",
      "education_field\n",
      "job_role\n",
      "business_travel\n",
      "employee_count\n",
      "attrition\n",
      "attrition_label\n",
      "job_satisfaction\n",
      "active_employee\n"
     ]
    }
   ],
   "source": [
    "\n",
    "for col in data.columns:\n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}