def test_update_from_list():
job_test_data = [{'state': 'Massachusetts', 'code': 99, 'title': 'testttl', 'employment': 5, 'salary': 1000}]
jobs_data_to_replace = [1, 'Rhode Island', 10, 'title', 10, 500]
conn, cursor = jobs.open_db("update_test.sqlite")
jobs.setup_db(cursor)
jobs.insert_data(job_test_data, "jobs", cursor)
jobs.close_db(conn)
conn, cursor = jobs.open_db("update_test.sqlite")
original_data = jobs.query_run("SELECT * " + "FROM jobs;", cursor)
for (element, query_data) in zip(job_test_data, original_data):
assert query_data[0] == 1
assert query_data[1] == element['state']
assert query_data[2] == element['code']
assert query_data[3] == element['title']
assert query_data[4] == element['employment']
assert query_data[5] == element['salary']
jobs.close_db(conn)
jobs.update_data_from_list(jobs_data_to_replace, "Jobs", "update_test.sqlite")
conn, cursor = jobs.open_db("update_test.sqlite")
updated_data = jobs.query_run("SELECT *"+" FROM jobs;", cursor)
for element in updated_data:
assert jobs_data_to_replace[0] == element[0]
assert jobs_data_to_replace[1] == element[1]
assert jobs_data_to_replace[2] == element[2]
assert jobs_data_to_replace[3] == element[3]
assert jobs_data_to_replace[4] == element[4]
assert jobs_data_to_replace[5] == element[5]
jobs.close_db(conn)
def test_excel_import_jobs():
jobs_data = jobs.excel_jobs_import("state_job_data.xlsx")
assert len(jobs_data) > 1000
conn, cursor = jobs.open_db("excel_import_test.sqlite")
jobs.setup_db(cursor)
jobs.insert_data(jobs_data, "jobs", cursor)
jobs.close_db(conn)
conn, cursor = jobs.open_db("excel_import_test.sqlite")
state_query = jobs.query_run("SELECT count(DISTINCT state_name) from" + " jobs;", cursor)
for element in state_query:
assert element[0] > 50
def test_open_db():
# Checking to see if the database exists, so we can delete it to check
# if the open_db function actually creates the database.
if os.path.exists(databaseFileName):
os.remove(databaseFileName)
connection, cursor = jobs.open_db(databaseFileName)
assert os.path.exists('jobs.db')
jobs.close_db(connection)
def test_db_creation():
school_test_data = [
{'school.name': 'Test School', '2017.student.size': 5, '2018.student.size': 1, 'school.state': 'NJ',
'id': 1, '2017.earnings.3_yrs_after_completion.overall_count_over_poverty_line': 1,
'2016.repayment.3_yr_repayment.overall': 1, '2016.repayment.repayment_cohort.3_year_declining_balance': 0.5},
{'school.name': 'Test School 1', '2017.student.size': 4, '2018.student.size': 6, 'school.state': 'MA',
'id': 2, '2017.earnings.3_yrs_after_completion.overall_count_over_poverty_line': 1,
'2016.repayment.3_yr_repayment.overall': 1, '2016.repayment.repayment_cohort.3_year_declining_balance': 0.5}]
job_test_data = [{'state': 'Massachusetts', 'code': '00-0001', 'title': 'testttl', 'employment': 5, 'salary': 1000}]
expected_tables = ['school', 'jobs']
conn, cursor = jobs.open_db("test_db.sqlite")
jobs.setup_db(cursor)
jobs.insert_data(job_test_data, "jobs", cursor)
jobs.insert_data(school_test_data, "school", cursor)
jobs.close_db(conn)
conn, cursor = jobs.open_db("test_db.sqlite")
tables_query = jobs.query_run("SELECT name FROM " + "sqlite_master WHERE type='table' and name NOT LIKE 'sqlite_%';"
"", cursor)
for (table, returned_tables) in zip(expected_tables, tables_query):
assert table == returned_tables[0]
school_query = jobs.query_run("SELECT * FROM" + " SCHOOL;", cursor)
for (row, element) in zip(school_query, school_test_data):
assert row[0] == element['id']
assert row[1] == element['school.name']
assert row[2] == element['school.state']
assert row[3] == element['2017.student.size']
assert row[4] == element['2018.student.size']
assert row[5] == element['2017.earnings.3_yrs_after_completion.overall_count_over_poverty_line']
assert row[6] == element['2016.repayment.3_yr_repayment.overall']
assert row[7] == element['2016.repayment.repayment_cohort.3_year_declining_balance']
jobs_query = jobs.query_run("SELECT * FROM" + " JOBS;", cursor)
for (row, element) in zip(jobs_query, job_test_data):
assert row[0] == 1
assert row[1] == element['state']
assert row[2] == element['code']
assert row[3] == element['title']
assert row[4] == element['employment']
assert row[5] == element['salary']
jobs.close_db(conn)
def text_visualization(self):
self.list_control.clear()
conn, cursor = jobs.open_db("jobs_db.sqlite")
if self.order_selector_text.currentText() == "ASC":
data_visualization_per_state = jobs.query_run('''SELECT state_abrev, state_name, ''' + '''
total(jobs.employment) as employment,
total(school.size_2018/4),
round(avg(school.repayment_cohort),3) as repayment_cohort,
round(avg(jobs.salary_25th_percentile)) as averge_entry_salary
FROM school
JOIN states using(state_abrev)
JOIN jobs using(state_name)
GROUP BY state_name
ORDER BY employment ASC;''', cursor)
elif self.order_selector_text.currentText() == "DESC":
data_visualization_per_state = jobs.query_run('''SELECT state_abrev, state_name, ''' + '''
total(jobs.employment) as employment,
total(school.size_2018/4),
round(avg(school.repayment_cohort),3) as repayment_cohort,
round(avg(jobs.salary_25th_percentile)) as averge_entry_salary
FROM school
JOIN states using(state_abrev)
JOIN jobs using(state_name)
GROUP BY state_name
ORDER BY employment DESC;''', cursor)
else:
data_visualization_per_state = jobs.query_run('''SELECT state_abrev, state_name, ''' + '''
total(jobs.employment) as employment,
total(school.size_2018/4),
round(avg(school.repayment_cohort),3) as repayment_cohort,
round(avg(jobs.salary_25th_percentile)) as averge_entry_salary
FROM school
JOIN states using(state_abrev)
JOIN jobs using(state_name)
GROUP BY state_name
;''', cursor)
QListWidgetItem("State", listview=self.list_control)
for state in data_visualization_per_state:
state_display_data = f"{state[0]}, {state[1]}"
grad_employ_data = f"Employment/Graduates: {state[2]/state[3]}"
repayment_data = f"Average Entry Salary/Average Declining Balance Percent: {state[5]/state[4]}"
state_item = QListWidgetItem(state_display_data, listview=self.list_control)
grad_item = QListWidgetItem(grad_employ_data, listview=self.list_control)
repayment_item = QListWidgetItem(repayment_data, listview=self.list_control)
grad_item.setForeground(Qt.darkGreen)
repayment_item.setForeground(Qt.blue)
state_item.setForeground(Qt.white)
state_item.setBackground(Qt.black)
self.list_control.show()
jobs.close_db(conn)
def test_specific_excel_data():
excel_test_data = ["massachusetts", 0, "test", "major", 5, 5]
excel_book = openpyxl.load_workbook(filename="test_workbook.xlsx")
test_sheet = excel_book.active
test_sheet['B2'] = excel_test_data[0]
test_sheet['H2'] = excel_test_data[1]
test_sheet['I2'] = excel_test_data[2]
test_sheet['J2'] = excel_test_data[3]
test_sheet['K2'] = excel_test_data[4]
test_sheet['Y2'] = excel_test_data[5]
excel_book.save(filename="test_workbook.xlsx")
test_dict = jobs.excel_jobs_import("test_workbook.xlsx")
conn, cursor = jobs.open_db("test_specific_excel_data.sqlite")
jobs.setup_db(cursor)
jobs.insert_data(test_dict, "jobs", cursor)
jobs.close_db(conn)
conn, cursor = jobs.open_db("test_specific_excel_data.sqlite")
specific_data_query = jobs.query_run("SELECT * FROM " + "JOBS;", cursor)
for element in specific_data_query:
assert element[1] == excel_test_data[0]
assert element[2] == excel_test_data[1]
assert element[3] == excel_test_data[2]
assert element[4] == excel_test_data[4]
assert element[5] == excel_test_data[5]
def test_update_from_excel():
test_data = [{'state': 'Massachusetts', 'code': 99, 'title': 'testttl', 'employment': 5, 'salary': 1000}]
data_to_replace = [1, 'Rhode Island', 10, 'title', 10, 500]
conn, cursor = jobs.open_db("test_excel_update.sqlite")
jobs.setup_db(cursor)
jobs.insert_data(test_data, "jobs", cursor)
jobs.close_db(conn)
conn, cursor = jobs.open_db("test_excel_update.sqlite")
original_data = jobs.query_run("SELECT * " + "FROM jobs;", cursor)
for (element, query_data) in zip(test_data, original_data):
assert query_data[0] == 1
assert query_data[1] == element['state']
assert query_data[2] == element['code']
assert query_data[3] == element['title']
assert query_data[4] == element['employment']
assert query_data[5] == element['salary']
jobs.close_db(conn)
excel_book = openpyxl.load_workbook(filename="update_by_excel.xlsx")
active_sheet = excel_book.active
active_sheet['A1'] = data_to_replace[0]
active_sheet['B1'] = data_to_replace[1]
active_sheet['C1'] = data_to_replace[2]
active_sheet['D1'] = data_to_replace[3]
active_sheet['E1'] = data_to_replace[4]
active_sheet['F1'] = data_to_replace[5]
excel_book.save("update_by_excel.xlsx")
jobs.update_data_from_excel("update_by_excel.xlsx", "Jobs", "test_excel_update.sqlite")
conn, cursor = jobs.open_db("test_excel_update.sqlite")
updated_data = jobs.query_run("SELECT * "+"FROM jobs", cursor)
for element in updated_data:
assert data_to_replace[0] == element[0]
assert data_to_replace[1] == element[1]
assert data_to_replace[2] == element[2]
assert data_to_replace[3] == element[3]
assert data_to_replace[4] == element[4]
assert data_to_replace[5] == element[5]
def test_table_exists():
"""
This test takes the 'test_table' table in the 'testonly.sqlite' database and injects a fake row with fake data.
The test attempts to see if the test can see if the fake table exists in the testing database. This test
will return a favorable answer.
"""
fake_table = 'test_table'
fake_row = {'id': 'F$RT%YH&', 'type': 'Remote', 'url': 'http://wwww.fakedata.com', 'created_at': '02-12-2020',
'company': "Don't Work Here Comp", 'company_url': None, 'location': "giant urban metro",
'title': 'Junior software peon', 'description': "blah blah, devops, scrum, hot tech",
'how_to_apply': "http://runaway.com", 'company_logo': None}
connection, cursor = jobs.open_db('testonly.sqlite')
jobs.create_table(cursor, jobs.make_column_description_from_json_dict(fake_row), fake_table)
result_cursor = cursor.execute(f"SELECT name from sqlite_master where (name = '{fake_table}')")
# results = result_cursor.rowcount
success = len(result_cursor.fetchall()) >= 1
assert success
def test_create_table():
# Checking to see if the database exists, so we can delete it to check
# if the create_table function actually creates the database table with the appropriate columns.
if os.path.exists(databaseFileName):
os.remove(databaseFileName)
connection, cursor = jobs.open_db(databaseFileName)
jobs.create_table(connection, cursor)
columnExists = False
try:
cursor.execute('ALTER TABLE jobs ADD COLUMN Description;')
except sqlite3.OperationalError:
columnExists = True
assert columnExists
jobs.close_db(connection)
def run_map_visualization(self):
conn, cursor = jobs.open_db("jobs_db.sqlite")
data_visualization_per_state = jobs.query_run('''SELECT state_abrev, state_name, ''' + '''
total(jobs.employment) as employment,
total(school.size_2018/4),
round(avg(school.repayment_cohort),3) as repayment_cohort,
round(avg(jobs.salary_25th_percentile)) as averge_entry_salary
FROM school
JOIN states using(state_abrev)
JOIN jobs using(state_name)
GROUP BY state_name
;''', cursor)
state_abrev = []
state_grads = []
state_repayment = []
for state in data_visualization_per_state:
state_abrev.append(state[0])
state_grads.append(state[2]/state[3])
state_repayment.append(state[5]/state[4])
if self.data_selector_map.currentText() == "Graduates to Employment":
us_map = px.Figure(data=px.Choropleth(locations=state_abrev, z=state_grads,
locationmode='USA-states', colorbar_title="Employment/Graduates"
))
us_map.update_layout(geo_scope='usa', title_text='Employment VS Graduates By State')
us_map.show()
elif self.data_selector_map.currentText() == "Average Declining Balance Percent":
us_map = px.Figure(data=px.Choropleth(locations=state_abrev, z=state_repayment,
locationmode='USA-states', colorbar_title="Salary/Average Percent"
))
us_map.update_layout(geo_scope='usa', title_text='Average Salary VS Average '
'Percent of People with Declining Loans')
us_map.show()
else:
us_map = px.Figure(data=px.Choropleth(locations=state_abrev, z=state_grads,
locationmode='USA-states', colorbar_title="Graduates"
))
us_map.update_layout(geo_scope='usa', title_text='Graduates By State')
us_map.show()
jobs.close_db(conn)
self.list_control.hide()
def test_add_to_database_with_good_data():
# Adding good data to the database.
additionalTestData = [
{"id": "TESTID1",
"type": "Full Time",
"url": "test1.com",
"created_at": "Sat Feb 01 12:53:40 UTC 2020",
"company": "TESTCOMPANY",
"company_url": "test1.com",
"location": "Boston, MA",
"title": "Senior Python/Django Developer ",
"description": "Test Description",
"how_to_apply": "Yes.",
"company_logo": "test.png"}]
# Checking if the new data is added to the database.
connection, cursor = jobs.open_db(databaseFileName)
jobs.save_to_database(additionalTestData, connection, cursor)
cursor.execute("SELECT * FROM jobs WHERE jobs.id = 'TESTID1'")
assert cursor.fetchone()
jobs.close_db(connection)
def test_add_to_database_with_bad_data():
# Adding bad data to the database.
additionalTestData = {
"id": "TESTID2",
"type": "Part Time",
"url": "test1.com"}
connection, cursor = jobs.open_db(databaseFileName)
# Checking if the new data is added to the database.
jobs.save_to_database(additionalTestData, connection, cursor)
cursor.execute("SELECT * FROM jobs WHERE jobs.id = 'TESTID2'")
assert cursor.fetchone() is None
# Throwing in some additional weird arguments to test the function.
jobs.save_to_database(102391, connection, cursor)
jobs.save_to_database('follow', connection, cursor)
jobs.save_to_database(['break'], connection, cursor)
jobs.save_to_database((1, 2, 3), connection, cursor)
jobs.save_to_database('', connection, cursor)
jobs.save_to_database([], connection, cursor)
jobs.save_to_database([[]], connection, cursor)
jobs.save_to_database([{'id': 'test23', "type": "full-time"}], connection, cursor)
jobs.save_to_database({'id': 'test23', "type": "full-time"}, connection, cursor)
def test_save_to_database(get_data_github, get_data_stackoverflow):
# Checking to see if the database exists, so we can delete it to check
# if the save_to_database function actually saves the data to a fresh, new database.
if os.path.exists(databaseFileName):
os.remove(databaseFileName)
# The amount of jobs is compressed to 30 per source, so the test doesn't take an eternity to run.
connection, cursor = jobs.open_db(databaseFileName)
jobs.create_table(connection, cursor)
jobs.save_to_database(get_data_github[:30], connection, cursor)
jobs.save_to_database(get_data_stackoverflow[:30], connection, cursor)
# Checking if the database has some values that should be expected there. In this case, I know
# that there is a job where the title is 'Lead Data Acquisition Design Engineer'.
# It also picks a random one from the retrieved list and checks if it exists in the database.
# UPDATE: It only checks for random titles from the StackOverFlow and Github lists.
testTitle1 = random.choice(get_data_github[:30])['title']
testTitle2 = random.choice(get_data_stackoverflow[:30])['title']
cursor.execute("SELECT * FROM jobs WHERE jobs.Title = ?", (testTitle1,))
assert cursor.fetchone()
cursor.execute("SELECT * FROM jobs WHERE jobs.Title = ?", (testTitle2,))
assert cursor.fetchone()
jobs.close_db(connection)
# Mark Pasquantonio
# Senior Design and Dev COMP490 Project 1
# JobsAssignment Sprint 4
# Filename: mapplot.py
"""
This file handles the ability to get geolocation information on the locations of the jobs
in the github jobs database only. When you click the green 'run' button, a map will load
and it will contain a map of the world with black dots on the map, which will signify a location
based on latitude-longitude information gained from the original JSON download of the GitHub
Jobs posting from Sprint 1.
"""
import plotly.express as px
import pandas as pd
import jobs
conn, cursor = jobs.open_db("job_demo.sqlite")
query = pd.read_sql_query('''SELECT * FROM github_jobs''', conn)
# query = pd.read_sql_query(f'''SELECT * FROM github_jobs UNION SELECT * FROM rss_feed;''', conn)
df_from_table = pd.DataFrame(query,
columns=[
'id', 'type', 'url', 'created_at', 'company',
'location', 'title', 'longitude', 'latitude'
])
pd.set_option('display.max_columns', None)
print(df_from_table)
fig = px.scatter_mapbox(df_from_table,
lat='latitude',
lon='longitude',
