def __init__(self):
self.connection = ConnectToDb()
self.query_select_cases_id_and_date = r"""
SELECT province, city, confirmed, recovered, deaths FROM cases WHERE country_id = ? and last_update LIKE ? ;
"""
self.query_insert_cases = (
r"INSERT INTO cases VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?);")
def __init__(self):
super().__init__()
self.connection = ConnectToDb()
self.interval = {
"#29a329": [1, 1000],
"#196619": [1000, 5000],
"#f2c718": [5000, 10000],
"#ffcc00": [10000, 25000],
"#ff9900": [25000, 50000],
"#ff5c33": [50000, 100000],
"#ff3300": [100000, 150000],
"#ff3333": [150000, 250000],
"#ff0000": [250000, 100**10],
}
class DataProcessing:
def __init__(self):
super().__init__()
self.connection = ConnectToDb()
self.interval = {
"#29a329": [1, 1000],
"#196619": [1000, 5000],
"#f2c718": [5000, 10000],
"#ffcc00": [10000, 25000],
"#ff9900": [25000, 50000],
"#ff5c33": [50000, 100000],
"#ff3300": [100000, 150000],
"#ff3333": [150000, 250000],
"#ff0000": [250000, 100**10],
}
def get_all_towns(self):
select = self.connection.select_all_records(
query=r"SELECT * from towns",
parameter="",
)
return select
def get_all_meetups(self):
select = self.connection.select_all_records(
query=
r"select T.town_id, M.meetup_id, M.created_at, M.name from towns as T "
r"JOIN meetups as M ON T.town_id = M.town_id ",
parameter="",
)
return select
def get_icon_color(self, number_of_cases):
for key, volume in self.interval.items():
if volume[1] > number_of_cases >= volume[0]:
return key
def slice_location(self, coordinates_str):
coordinates_str = coordinates_str.replace("[", "")
coordinates_str = coordinates_str.replace("]", "")
coordinates_split = coordinates_str.split(", ")
latitude = float(coordinates_split[0])
longitude = float(coordinates_split[1])
coordinates = [latitude, longitude]
return coordinates
class TestMethods:
def __init__(self):
self.connection = ConnectToDb()
def get_country_id(self):
query = r"SELECT country_id FROM cases where confirmed > 0 group by country_id"
data = self.connection.select_all_records(query=query, parameter="")
list_id = [x[0] for x in data]
choise = random.choice(list_id)
return choise
def get_location(self):
query = r"SELECT co.latlng FROM countries as co left join cases as ca on co.country_id = ca.country_id where ca.confirmed > 0 group by co.country_id"
data = self.connection.select_all_records(query=query, parameter="")
list_id = [x[0] for x in data]
choise = random.choice(list_id)
return choise
class ImporterOfCountries:
def __init__(self):
self.connection = ConnectToDb()
def load_countries_from_api(self, url):
with urlopen(url) as file:
response = file.read()
data_load = json.loads(response)
solution = []
for item in data_load:
name = item["name"]
alpha3code = item["alpha3Code"]
alpha2code = item["alpha2Code"]
population = item["population"]
latlag = item["latlng"]
flag = item["flag"]
row = {
"name": name,
"alpha3code": alpha3code,
"alpha2code": alpha2code,
"population": population,
"latlag": str(latlag),
"flag": str(flag),
}
solution.append(row)
print(
f"--> Script {ImporterOfCountries.load_countries_from_api.__name__} executed <--"
)
return solution
def insert_countries_to_db(self, data):
for row in data:
get_parameters = row.values()
parameters = tuple(get_parameters)
query = r"INSERT INTO countries VALUES(null, ?, ?, ?, ?, ?, ?);"
try:
ConnectToDb().insert_record(query=query, parameters=parameters)
print("Insert record: ", parameters)
except sqlite3.IntegrityError:
continue
self.connection.close_connect()
return print(
f"--> Script {ImporterOfCountries.insert_countries_to_db.__name__} executed <--"
)
def __init__(self):
super().__init__()
self.connection = ConnectToDb()
self.query_select_sum_of_cases_per_day_group_by_id = r"""
SELECT sum(ca.confirmed) as total_confirmed,
sum(ca.deaths) as total_deaths, sum(ca.recovered) as total_recovered,
max(ca.last_update)
FROM cases as ca
JOIN countries as co
ON ca.country_id = co.country_id
GROUP BY ca.country_id, ca.last_update
HAVING ca.country_id = ?
"""
self.query_select_sum_of_cases_current_day = r"""
SELECT ca.country_id, co.name, co.alpha_3_code, ca.confirmed as total_confirmed, ca.deaths as total_deaths,
ca.recovered as total_recovered, max(ca.last_update), co.latlng, co.flag_url
FROM cases as ca
JOIN countries as co
ON co.country_id = ca.country_id
GROUP BY ca.country_id
"""
self.query_select_total_cases_per_day = r"""
SELECT sum(confirmed) as total_confirmed, sum(deaths) as total_deaths, sum(recovered) as total_recovered,
datetime(last_update) as date_of_update
FROM cases
GROUP BY datetime(last_update)
"""
self.interval = {
"#29a329": [1, 1000],
"#196619": [1000, 5000],
"#f2c718": [5000, 10000],
"#ffcc00": [10000, 25000],
"#ff9900": [25000, 50000],
"#ff5c33": [50000, 100000],
"#ff3300": [100000, 150000],
"#ff3333": [150000, 250000],
"#ff0000": [250000, 100**10],
}
def insert_countries_to_db(self, data):
for row in data:
get_parameters = row.values()
parameters = tuple(get_parameters)
query = r"INSERT INTO countries VALUES(null, ?, ?, ?, ?, ?, ?);"
try:
ConnectToDb().insert_record(query=query, parameters=parameters)
print("Insert record: ", parameters)
except sqlite3.IntegrityError:
continue
self.connection.close_connect()
return print(
f"--> Script {ImporterOfCountries.insert_countries_to_db.__name__} executed <--"
)
def test_figure(self):
country_id = self.test_methods.get_country_id()
self.assertTrue(isinstance(country_id, int))
data = self.data_processing.all_cases_per_day_where_country_id_equal(
country_id=country_id)
self.assertTrue(isinstance(data, list))
self.assertTrue(isinstance(data[0], tuple))
assert len(data) > 0
dataframe = self.data_processing.get_dateframe(data=data)
self.assertTrue(isinstance(dataframe, pandas.DataFrame))
alpha_3_code = ConnectToDb().select_one_record(
query=
r"SELECT co.alpha_3_code from countries as co join cases as ca on co.country_id = ca.country_id group by co.country_id having co.country_id = ?",
parameter=(country_id, ),
)
self.assertTrue(isinstance(alpha_3_code[0], str))
figure = self.graphs.creating_figure_with_data(
figure=go.Figure(),
dataframe=dataframe,
alpha_3_code=alpha_3_code[0])
self.assertTrue(isinstance(figure, plotly.graph_objects.Figure))
class DataProcessing:
def __init__(self):
super().__init__()
self.connection = ConnectToDb()
self.query_select_sum_of_cases_per_day_group_by_id = r"""
SELECT sum(ca.confirmed) as total_confirmed,
sum(ca.deaths) as total_deaths, sum(ca.recovered) as total_recovered,
max(ca.last_update)
FROM cases as ca
JOIN countries as co
ON ca.country_id = co.country_id
GROUP BY ca.country_id, ca.last_update
HAVING ca.country_id = ?
"""
self.query_select_sum_of_cases_current_day = r"""
SELECT ca.country_id, co.name, co.alpha_3_code, ca.confirmed as total_confirmed, ca.deaths as total_deaths,
ca.recovered as total_recovered, max(ca.last_update), co.latlng, co.flag_url
FROM cases as ca
JOIN countries as co
ON co.country_id = ca.country_id
GROUP BY ca.country_id
"""
self.query_select_total_cases_per_day = r"""
SELECT sum(confirmed) as total_confirmed, sum(deaths) as total_deaths, sum(recovered) as total_recovered,
datetime(last_update) as date_of_update
FROM cases
GROUP BY datetime(last_update)
"""
self.interval = {
"#29a329": [1, 1000],
"#196619": [1000, 5000],
"#f2c718": [5000, 10000],
"#ffcc00": [10000, 25000],
"#ff9900": [25000, 50000],
"#ff5c33": [50000, 100000],
"#ff3300": [100000, 150000],
"#ff3333": [150000, 250000],
"#ff0000": [250000, 100**10],
}
def all_cases_per_day_where_country_id_equal(self, country_id):
data = self.connection.select_all_records(
query=self.query_select_sum_of_cases_per_day_group_by_id,
parameter=(country_id, ),
)
return data
def total_current_cases(self):
data = self.connection.select_all_records(
query=self.query_select_sum_of_cases_current_day, parameter="")
return data
def total_cases_per_day(self):
data = self.connection.select_all_records(
query=self.query_select_total_cases_per_day, parameter="")
return data
def get_name_and_3code_country(self, country_id):
select = self.connection.select_one_record(
query=
r"SELECT name, alpha_3_code from countries WHERE country_id = ?",
parameter=(country_id, ),
)
return select
def get_id_and_name_of_countries(self):
select = self.connection.select_all_records(
query=
r"SELECT co.country_id, co.name from countries as co join cases as ca on co.country_id = ca.country_id group by co.country_id having ca.confirmed > 0",
parameter="",
)
return select
def get_icon_color(self, number_of_cases):
for key, volume in self.interval.items():
if volume[1] > number_of_cases >= volume[0]:
return key
def slice_location(self, coordinates_str):
coordinates_str = coordinates_str.replace("[", "")
coordinates_str = coordinates_str.replace("]", "")
coordinates_split = coordinates_str.split(",")
latitude = float(coordinates_split[0])
longitude = float(coordinates_split[1])
coordinates = [latitude, longitude]
return coordinates
def get_dateframe(self, data):
dateframe = pd.DataFrame(
data, columns=["Confirmed", "Deaths", "Recovered", "Date"])
return dateframe
def get_dateframe_diff(self, data):
df = self.get_dateframe(data=data)
df_without_date = df.drop(labels="Date", axis=1)
df_date = df.drop(labels=["Confirmed", "Deaths", "Recovered"], axis=1)
df_diff = df_without_date.diff(axis=0)
df_diff_with_date = df_diff.join(df_date)
df_diff_with_date.drop(labels=[0], axis=0)
return df_diff_with_date
class ImporterCases:
def __init__(self):
self.connection = ConnectToDb()
self.query_select_cases_id_and_date = r"""
SELECT province, city, confirmed, recovered, deaths FROM cases WHERE country_id = ? and last_update LIKE ? ;
"""
self.query_insert_cases = (
r"INSERT INTO cases VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?);")
def creating_row_to_insert_db(self, row_dict):
parameters = row_dict.values()
return tuple(parameters)
def read_json_file(self, path):
with open(path, mode="r", encoding="utf-8") as f:
data = json.load(f)
return data
def read_json_api(self, api):
response = requests.get(api)
time.sleep(5)
data = response.json()
return data
def load_data_and_write_json(self, url, file_name):
response = requests.get(url)
data = response.json()
print(f'--> Success write of "{file_name}"" <--')
with open(file=file_name, mode="w", encoding="utf-8") as f:
json.dump(data, f)
def load_alpha2code_and_id_of_countries(self):
query = r"SELECT alpha_2_code, country_id FROM countries;"
list_of_data = self.connection.select_all_records(query=query,
parameter="")
self.connection.close_connect()
print(
f"--> Script {ImporterCases.load_alpha2code_and_id_of_countries.__name__} executed <--"
)
return list_of_data
def create_dict_of_countries_name_and_id(self, data):
countries_and_id_dict = {}
for row in data:
countries_and_id_dict[row[0]] = row[1]
print(
f"--> Script {ImporterCases.create_dict_of_countries_name_and_id.__name__} executed <--"
)
return countries_and_id_dict
def load_all_data_from_json_and_insert_to_db(self,
path,
api=True,
current_cases=True):
time_start = time.time()
if api is True:
data = self.read_json_api(path)
else:
data = self.read_json_file(path)
load_countries = self.load_alpha2code_and_id_of_countries()
time.sleep(3)
symbol_dict = self.create_dict_of_countries_name_and_id(load_countries)
time.sleep(3)
for element in data:
try:
coutry_code = element["CountryCode"]
country_id = symbol_dict[coutry_code]
row = {
"timestamp": int(time.time()),
"cod2": country_id,
"province": element["Province"],
"city": element["City"],
"confirmed": element["Confirmed"],
"recovery": element["Recovered"],
"deaths": element["Deaths"],
"last_update": element["Date"],
}
parameters = self.creating_row_to_insert_db(row_dict=row)
if parameters[2] != "":
continue
if current_cases:
date_element = element["Date"][:10]
yesterday = date.today() - timedelta(days=1)
yesterday_str = yesterday.__str__()
if date_element < yesterday_str:
continue
db_last_update = ConnectToDb().select_all_records(
query=self.query_select_cases_id_and_date,
parameter=(country_id, yesterday_str + "%"),
)
if db_last_update:
continue
row_cases = (
element["Province"],
element["City"],
element["Confirmed"],
element["Recovered"],
element["Deaths"],
)
verify_duplicate_zero = (element["Province"], element["City"],
0, 0, 0)
if row_cases != verify_duplicate_zero:
ConnectToDb().insert_record(query=self.query_insert_cases,
parameters=parameters)
print("Insert record: ", parameters)
except KeyError:
if KeyError == "AN":
continue
print(f"--> Insert cases from json {path} is done <--")
time_stop = time.time()
delta = time_stop - time_start
if current_cases:
print("Import time current cases = ", round(delta / 60, 2),
" minutes")
else:
print("Import time historical cases = ", round(delta / 60, 2),
" minutes")
self.connection.close_connect()
def load_all_data_from_json_and_insert_to_db(self,
path,
api=True,
current_cases=True):
time_start = time.time()
if api is True:
data = self.read_json_api(path)
else:
data = self.read_json_file(path)
load_countries = self.load_alpha2code_and_id_of_countries()
time.sleep(3)
symbol_dict = self.create_dict_of_countries_name_and_id(load_countries)
time.sleep(3)
for element in data:
try:
coutry_code = element["CountryCode"]
country_id = symbol_dict[coutry_code]
row = {
"timestamp": int(time.time()),
"cod2": country_id,
"province": element["Province"],
"city": element["City"],
"confirmed": element["Confirmed"],
"recovery": element["Recovered"],
"deaths": element["Deaths"],
"last_update": element["Date"],
}
parameters = self.creating_row_to_insert_db(row_dict=row)
if parameters[2] != "":
continue
if current_cases:
date_element = element["Date"][:10]
yesterday = date.today() - timedelta(days=1)
yesterday_str = yesterday.__str__()
if date_element < yesterday_str:
continue
db_last_update = ConnectToDb().select_all_records(
query=self.query_select_cases_id_and_date,
parameter=(country_id, yesterday_str + "%"),
)
if db_last_update:
continue
row_cases = (
element["Province"],
element["City"],
element["Confirmed"],
element["Recovered"],
element["Deaths"],
)
verify_duplicate_zero = (element["Province"], element["City"],
0, 0, 0)
if row_cases != verify_duplicate_zero:
ConnectToDb().insert_record(query=self.query_insert_cases,
parameters=parameters)
print("Insert record: ", parameters)
except KeyError:
if KeyError == "AN":
continue
print(f"--> Insert cases from json {path} is done <--")
time_stop = time.time()
delta = time_stop - time_start
if current_cases:
print("Import time current cases = ", round(delta / 60, 2),
" minutes")
else:
print("Import time historical cases = ", round(delta / 60, 2),
" minutes")
self.connection.close_connect()
def __init__(self):
self.connection = ConnectToDb()
import time
from connect_to_db import ConnectToDb
from map_of_the_world import CreatingMap
from map_of_the_world import CreatingMap
if __name__ == "__main__":
ConnectToDb().run_sql_script(scripts="db_init.sql")
time.sleep(3)
ConnectToDb().run_sql_script("towns.sql")
CreatingMap().map_of_the_world()
import time
from app import app
from connect_to_db import ConnectToDb
from importer_cases_json import ImporterCases
from importer_of_countries import ImporterOfCountries
from map_of_the_world import CreatingMap
from resources.path_and_api import JsonApi, Files
if __name__ == "__main__":
ConnectToDb().run_sql_script(Files.SQL_SCRIPT)
countries_data = ImporterOfCountries().load_countries_from_api(
JsonApi.API_COUNTRIES)
ImporterOfCountries().insert_countries_to_db(countries_data)
time.sleep(3)
ImporterCases().load_all_data_from_json_and_insert_to_db(
path=JsonApi.API_ALL_CASES, api=True, current_cases=False)
time.sleep(3)
CreatingMap().map_of_the_world()
def setUp(self):
self.conn = ConnectToDb()
self.data_processing = DataProcessing()
self.test_methods = TestMethods()
class DataProcessingTestCase(unittest.TestCase):
def setUp(self):
self.conn = ConnectToDb()
self.data_processing = DataProcessing()
self.test_methods = TestMethods()
def test_get_icon_color(self):
self.assertEqual(self.data_processing.get_icon_color(1), "#29a329")
self.assertEqual(self.data_processing.get_icon_color(999), "#29a329")
self.assertEqual(self.data_processing.get_icon_color(1000), "#196619")
self.assertEqual(self.data_processing.get_icon_color(4999), "#196619")
self.assertEqual(self.data_processing.get_icon_color(5000), "#f2c718")
self.assertEqual(self.data_processing.get_icon_color(9999), "#f2c718")
self.assertEqual(self.data_processing.get_icon_color(10000), "#ffcc00")
self.assertEqual(self.data_processing.get_icon_color(24999), "#ffcc00")
self.assertEqual(self.data_processing.get_icon_color(25000), "#ff9900")
self.assertEqual(self.data_processing.get_icon_color(49999), "#ff9900")
self.assertEqual(self.data_processing.get_icon_color(50000), "#ff5c33")
self.assertEqual(self.data_processing.get_icon_color(99999), "#ff5c33")
self.assertEqual(self.data_processing.get_icon_color(100000),
"#ff3300")
self.assertEqual(self.data_processing.get_icon_color(149999),
"#ff3300")
self.assertEqual(self.data_processing.get_icon_color(150000),
"#ff3333")
self.assertEqual(self.data_processing.get_icon_color(249999),
"#ff3333")
self.assertEqual(self.data_processing.get_icon_color(250000),
"#ff0000")
self.assertEqual(self.data_processing.get_icon_color(100**4),
"#ff0000")
def test_select_all_records_where_declare_id(self):
country_id = self.test_methods.get_country_id()
query = self.conn.select_all_records(
query="SELECT *, max(last_update) FROM cases WHERE country_id = ?",
parameter=(country_id, ),
)
self.assertIsNotNone(query)
self.assertEqual(query[0][2], country_id)
self.assertTrue(query, list)
def test_dataframe_diff(self):
country_id = self.test_methods.get_country_id()
data = self.data_processing.all_cases_per_day_where_country_id_equal(
country_id=country_id)
self.assertIsNotNone(data)
self.assertNotEqual(data, [])
df = self.data_processing.get_dateframe(data=data)
verify_df = str(type(df)).replace(">", "").replace("'", "").split(".")
self.assertIn("DataFrame", verify_df)
self.assertIsNotNone(df)
self.assertListEqual(["Confirmed", "Deaths", "Recovered", "Date"],
[x for x in df.columns])
df_diff = self.data_processing.get_dateframe_diff(data=data)
self.assertIsNotNone(df_diff)
self.assertEqual(df_diff.columns[-1], "Date")
df.to_csv(path_or_buf="tests/poland_df.csv", encoding="utf-8")
search = os.path.abspath("tests/poland_df.csv")
self.assertTrue(os.path.exists(search))
assert "poland_df.csv" in search
df_diff.to_csv(path_or_buf="tests/poland_diff.csv", encoding="utf-8")
search2 = os.path.abspath("tests/poland_diff.csv")
self.assertIn("poland_diff.csv", search2)
self.assertTrue(os.path.exists(search2))
def test_dataframe(self):
country_id = self.test_methods.get_country_id()
data = self.data_processing.all_cases_per_day_where_country_id_equal(
country_id=country_id)
df = self.data_processing.get_dateframe(data=data)
self.assertIsNotNone(df)
self.assertTrue(isinstance(df, pandas.DataFrame))
def test_coordinates(self):
location = self.test_methods.get_location()
test_location = self.data_processing.slice_location(location)
self.assertTrue(isinstance(test_location[0], float))
self.assertTrue(isinstance(test_location[1], float))
test_location_str = str(test_location)
self.assertEqual(test_location_str, location)
test_location2 = self.data_processing.slice_location("[54.0, -2.0]")
self.assertEqual(test_location2, [54.0, -2.0])
def test_total_current_cases(self):
data = self.data_processing.total_current_cases()
self.assertIsNotNone(data)
self.assertTrue(isinstance(data, list))
assert data.__len__() > 0
verify_data = data[0]
self.assertTrue(isinstance(verify_data[0], int))
self.assertTrue(isinstance(verify_data[1], str))
self.assertTrue(isinstance(verify_data[2], str))
self.assertTrue(isinstance(verify_data[3], int))
self.assertTrue(isinstance(verify_data[4], int))
self.assertTrue(isinstance(verify_data[5], int))
self.assertTrue(isinstance(verify_data[6], str))
self.assertTrue(isinstance(verify_data[7], str))
self.assertTrue(isinstance(verify_data[8], str))
self.assertIn("https", verify_data[8])
def test_total_per_day(self):
data = self.data_processing.total_cases_per_day()
self.assertIsNotNone(data)
self.assertTrue(isinstance(data, list))
self.assertTrue(isinstance(data[0][0], int))
self.assertTrue(isinstance(data[0][1], int))
self.assertTrue(isinstance(data[0][2], int))
self.assertTrue(isinstance(data[0][3], str))
self.assertTrue(isinstance(data[0], tuple))
assert data.__len__() > 0
self.assertTrue(time.strftime(data[0][3]))
def test_name_3code_country(self):
country_id = self.test_methods.get_country_id()
print(country_id)
data = self.data_processing.get_name_and_3code_country(
country_id=country_id)
self.assertTrue(isinstance(data[0], str))
self.assertTrue(isinstance(data[1], str))
self.assertTrue(isinstance(data, tuple))
self.assertIsNotNone(data)
def test_id_name_countries(self):
data = self.data_processing.get_id_and_name_of_countries()
self.assertIsNotNone(data)
self.assertTrue(isinstance(data, list))
self.assertTrue(isinstance(data[0], tuple))
self.assertTrue(data[0][0], int)
self.assertTrue(data[0][1], str)
assert data.__len__() > 0