# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # <CENTER>Code for web scraping and getting the required data from the webpages and saving it to MySQL</CENTER>
# <hr>

# ### Importing libraries

import requests
import time
from bs4 import BeautifulSoup
import re
import pandas as pd
from sqlalchemy import create_engine
import pymysql
import warnings
import mysql.connector

# ### Jobs to be scarped list

# We have used "Data+Sc", "Data+Ana", "Business+Ana" becasue they encompass all the required positions such as 'Data Scientist', 'Data Scientist 1', 'Data Scientist Ii', for "Data+Sc".

job_titles = ("Data+Sc", "Data+Ana", "Business+Ana")

# ### Years to be scraped list

year_list = ("2019", "2018", "2017", "2016", "2015")

# ### Loop to download the required pages for 5 years and 3 job titles

for title in job_titles :
    
    for year in year_list :
        print(title+", "+year)
    
    ## Setting the url code to save for the title and the year
        url = "https://h1bdata.info/index.php?em=&job="+title+"&city=&year="+year

        source = requests.get(url, headers = {'user-agent':'Mozilla/5.0'})
        file_name = title+"_"+year+".htm"
        save_file = open(file_name, "wb")
        save_file.write(source.content)
        save_file.close()

        print("Search page :"+url+"\nSaved as file: "+file_name+"\n\n")

        ## The for loop pauses or sleeps for 10 seconds to mimic human interaction with the website
        time.sleep(10)

# ## Code to read the table from the saved files and save it into a DataFrame.

## Creating an empty dataframe to which we will keep appending the data
table_data=pd.DataFrame()

for title in job_titles :
    
    for year in year_list :
        print("Adding "+title+", "+year+" to the Dataframe")
    
        file_name = title+"_"+year+".htm"
        
        ## Opening and reading the file
        html_file = open(file_name, "rb")
        
        ## Saving the tables of the HTML file into a list
        temp_table_list=pd.read_html(html_file)
        
        table_data = table_data.append(temp_table_list[0], ignore_index = True)
        
        print(title+", "+year+" added to the dataframe\n\n")

table_data.head()

len(table_data)

table_data.dtypes

table_data.tail()

table_data.describe

table_data.info()

table_data.loc[table_data["EMPLOYER"] == None]

# ## Writing the Dataframe to Excel file - h1bdata.xlsx

writer = pd.ExcelWriter('h1bdata.xlsx')
table_data.to_excel(writer)
writer.save()
print('DataFrame is written successfully to Excel File.')

# ### Code to read excel and then push it to MySQL
#
# #### Make changes to the password and username here in this code to push it to Excel

h1b_data = pd.read_excel('h1bdata.xlsx')

h1b_data.head()

h1b_data.drop(['Sr.No.'], axis = 1, inplace = True)

h1b_data.head()

h1b_data['SUBMIT DATE'] = pd.to_datetime(h1b_data['SUBMIT DATE'])

h1b_data['START DATE'] = pd.to_datetime(h1b_data['START DATE'])

h1b_data.info()

h1b_data.head()

# ### Creating a connection to MySQL and creating a database called "h1b_SQL"

# +
mydb = mysql.connector.connect(
    host="localhost",
    user="",
    passwd=""
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE IF NOT EXISTS h1b_sql")

mycursor.close()

mydb.close
# -

# ### Creating a connection to MySQL and creating a table called "h1b_data"

# +
mydb = mysql.connector.connect(
    host="localhost",
    user="",
    passwd="",
    database="h1b_sql"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE IF NOT EXISTS h1b_data (DataID int NOT NULL AUTO_INCREMENT PRIMARY KEY , EMPLOYER VARCHAR(50), JOB_TITLE VARCHAR(100), BASE_SALARY BIGINT, LOCATION VARCHAR(100), SUBMIT_DATE DATE, START_DATE DATE,CASE_STATUS VARCHAR(30));")

mycursor.close()

mydb.close
# -

engine = create_engine('mysql+pymysql://root:My1234SQL!@#$@localhost/h1b_sql')

dbConnection = engine.connect()

h1b_data.to_sql('h1b_data', dbConnection, index = True, if_exists='replace')

results = pd.read_sql("select * from h1b_data", dbConnection);

pd.set_option('display.expand_frame_repr', False)
dbConnection.close()

results.head()

StarcoderdataJupyterScriptsDedupFiltered