DOL_ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, sys, time\n",
    "working_dir = 'H:/UGA MASTERS/SDG/DOL'\n",
    "os.chdir(working_dir)\n",
    "import pandas as pd\n",
    "\n",
    "from bs4 import BeautifulSoup\n",
    "from urllib.request import urlopen\n",
    "import urllib.request\n",
    "\n",
    "#############################################################################\n",
    "# Extract / Transform\n",
    "#############################################################################\n",
    "def excel2csv(outfiles):\n",
    "    for file in outfiles:\n",
    "        df = pd.read_excel(file, header=0) #, sheetname='<your sheet>'\n",
    "        df.to_csv(file.replace('.xlsx','.csv'))        \n",
    "\n",
    "def get_DOL_data():\n",
    "    url = 'https://www.dol.gov/agencies/eta/foreign-labor/performance'\n",
    "    page = urlopen (url)\n",
    "    soup = BeautifulSoup(page.read())\n",
    "    links = soup.findAll('a', href=True)\n",
    "    outfiles = []\n",
    "    print('Downloading data')\n",
    "    for link in links:\n",
    "        if 'LCA_Disclosure' in link['href']: \n",
    "            print(f'https://www.dol.gov{link[\"href\"]}')\n",
    "            href = f'https://www.dol.gov{link[\"href\"]}'\n",
    "            outname = link[\"href\"].split('/')[-1]\n",
    "            urllib.request.urlretrieve(href, f'data/{outname}')\n",
    "            outfiles.append(f'data/{outname}')\n",
    "    print('Converting to csv')\n",
    "    excel2csv(outfiles)\n",
    "    return \"Data downloaded and written to .csv\"\n",
    "     \n",
    "                             \n",
    "#############################################################################\n",
    "# Load\n",
    "#############################################################################            \n",
    "def SqlLoad():\n",
    "\n",
    "    try:\n",
    "        dbconnect = MySQLdb.connect(\n",
    "                host='localhost',\n",
    "                user='root',\n",
    "                passwd='MAX!553MAX',\n",
    "                db='DOLdb')\n",
    "    except:\n",
    "        print('No connection')\n",
    "\n",
    "    cursor = dbconnect.cursor()\n",
    "    files = os.listdir(\"data\")    \n",
    "    files = list(filter(lambda f: f.endswith('.csv'), files))\n",
    "    for file in files:\n",
    "        with open(f'data/{file}') as csv_file:\n",
    "\n",
    "            csv_data = csv.reader(csv_file)\n",
    "            for row in csv_data:\n",
    "                cursor.execute(\n",
    "                'INSERT INTO testDB(CASE_NUMBER, PW_UNIT_OF_PAY, WAGE_RATE_OF_PAY_FROM, JOB_TITLE, \\\n",
    "                        PAY, EMPLOYER_STATE, EMPLOYER_CITY) ' \\\n",
    "                        'VALUES(\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\",)',\n",
    "                        row\n",
    "                        )\n",
    "\n",
    "        dbconnect.commit()\n",
    "    cursor.close()\n",
    "    return 'Read .csv and written to the MySQL database'                 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Downloading data\n",
      "https://www.dol.gov/sites/dolgov/files/ETA/oflc/pdfs/LCA_Disclosure_Data_FY2021_Q2.xlsx\n",
      "https://www.dol.gov/sites/dolgov/files/ETA/oflc/pdfs/LCA_Disclosure_Data_FY2020_Q1.xlsx\n",
      "https://www.dol.gov/sites/dolgov/files/ETA/oflc/pdfs/LCA_Disclosure_Data_FY2020_Q2.xlsx\n",
      "https://www.dol.gov/sites/dolgov/files/ETA/oflc/pdfs/LCA_Disclosure_Data_FY2020_Q3.xlsx\n",
      "https://www.dol.gov/sites/dolgov/files/ETA/oflc/pdfs/LCA_Disclosure_Data_FY2020_Q4.xlsx\n",
      "Converting to csv\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Data downloaded and written to .csv'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_DOL_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}