data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('jobs.json', 'r') as infile:\n",
    "    json_data = json.load(infile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.json_normalize(json_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>title</th>\n",
       "      <th>location</th>\n",
       "      <th>company_name</th>\n",
       "      <th>job_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Développeur backend senior  - Nantes (Java/Pyt...</td>\n",
       "      <td>Nantes</td>\n",
       "      <td>Artelys</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Software Engineer (Python / React)</td>\n",
       "      <td>Paris</td>\n",
       "      <td>Manty</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Trading Systems - Développeur Python - H/F</td>\n",
       "      <td>Paris</td>\n",
       "      <td>MARGO</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stage Développeur logiciel Python - Toulouse H/F</td>\n",
       "      <td>Toulouse</td>\n",
       "      <td>Capgemini</td>\n",
       "      <td>Stage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Développeur·euse Python - Alternance / Stage</td>\n",
       "      <td>Paris</td>\n",
       "      <td>Gymglish</td>\n",
       "      <td>Alternance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>Ingénieur développement logiciel F/H</td>\n",
       "      <td>Cesson-Sévigné</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>Administrateur Systèmes &amp; Réseaux F/H</td>\n",
       "      <td>Cesson-Sévigné</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>Data Engineer - Energies Renouvelables</td>\n",
       "      <td>Bordeaux…</td>\n",
       "      <td>MP DATA</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>Data scientist - Energies renouvelables</td>\n",
       "      <td>Boulogne-Billancourt…</td>\n",
       "      <td>MP DATA</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>Administrateur des plateformes Fluxvision F/H</td>\n",
       "      <td>Belfort</td>\n",
       "      <td>Orange</td>\n",
       "      <td>CDI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>302 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title               location  \\\n",
       "0    Développeur backend senior  - Nantes (Java/Pyt...                 Nantes   \n",
       "1                   Software Engineer (Python / React)                  Paris   \n",
       "2           Trading Systems - Développeur Python - H/F                  Paris   \n",
       "3     Stage Développeur logiciel Python - Toulouse H/F               Toulouse   \n",
       "4         Développeur·euse Python - Alternance / Stage                  Paris   \n",
       "..                                                 ...                    ...   \n",
       "297               Ingénieur développement logiciel F/H         Cesson-Sévigné   \n",
       "298              Administrateur Systèmes & Réseaux F/H         Cesson-Sévigné   \n",
       "299             Data Engineer - Energies Renouvelables              Bordeaux…   \n",
       "300            Data scientist - Energies renouvelables  Boulogne-Billancourt…   \n",
       "301      Administrateur des plateformes Fluxvision F/H                Belfort   \n",
       "\n",
       "    company_name    job_type  \n",
       "0        Artelys         CDI  \n",
       "1          Manty         CDI  \n",
       "2          MARGO         CDI  \n",
       "3      Capgemini       Stage  \n",
       "4       Gymglish  Alternance  \n",
       "..           ...         ...  \n",
       "297       Orange         CDI  \n",
       "298       Orange         CDI  \n",
       "299      MP DATA         CDI  \n",
       "300      MP DATA         CDI  \n",
       "301       Orange         CDI  \n",
       "\n",
       "[302 rows x 4 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Job Title'] = df['Job Title'].str.replace('cdi', '', case=False).str.strip()\n",
    "df = pd.DataFrame(df)\n",
    "\n",
    "# Correct the column names to match the database schema\n",
    "df.rename(columns={\n",
    "    'Job Title': 'title',\n",
    "    'Job Location': 'location',\n",
    "    'Company Name': 'company_name',\n",
    "    'Job Type': 'job_type'\n",
    "}, inplace=True)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data inserted successfully.\n"
     ]
    },
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31mThe Kernel crashed while executing code in the the current cell or a previous cell. Please review the code in the cell(s) to identify a possible cause of the failure. Click <a href='https://aka.ms/vscodeJupyterKernelCrash'>here</a> for more info. View Jupyter <a href='command:jupyter.viewOutput'>log</a> for further details."
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "database_url = 'postgresql://enesbarut:barut_admin@localhost:5432/jobscraping'\n",
    "\n",
    "engine = create_engine(database_url)\n",
    "\n",
    "try:\n",
    "    df.to_sql('job_listings', engine, if_exists='append', index=False)\n",
    "    print(\"Data inserted successfully.\")\n",
    "except Exception as e:\n",
    "    print(\"An error occurred: \", e)\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}