def insert_latest_continents(db_path,
yesterday=False,
two_days_ago=False,
debug=True):
url = "{}?yesterday={}&twoDaysAgo={}".format(GET_LATEST_CONTINENT_URL,
1 if yesterday else 0,
1 if two_days_ago else 0)
latest_continents = get_json_from_web(url, debug=debug)
continents_tuples = []
date = 'twoDaysAgo' if two_days_ago else 'yesterday' if yesterday else 'today'
for continent in latest_continents:
continents_tuples.append(
(continent["continent"], date, continent["updated"],
continent["cases"], continent["todayCases"], continent["deaths"],
continent["todayDeaths"], continent["recovered"],
continent["todayRecovered"], continent["active"],
continent["critical"], continent["casesPerOneMillion"],
continent["deathsPerOneMillion"], continent["tests"],
continent["testsPerOneMillion"], continent["population"],
continent["activePerOneMillion"],
continent["recoveredPerOneMillion"],
continent["criticalPerOneMillion"]))
latest_continents_sql_script = """INSERT OR REPLACE INTO "continents_latest" ("id", "date", "updated", "cases", "today_cases", "deaths", "today_deaths", "recovered", "today_recovered", "active", "critical", "cases_per_one_million", "deaths_per_one_million", "tests", "tests_per_one_million", "population", "active_per_one_million", "recovered_per_one_million", "critical_per_one_million") VALUES ((SELECT id FROM continents WHERE name=?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """
execute_many(latest_continents_sql_script,
continents_tuples,
database_path=db_path,
message="latest continents",
debug=debug)
def insert_countries_history(db_path, days=None, debug=True):
url = "{}?lastdays={}".format(GET_HISTORICAL_COUNTRY_DATA_URL, days if days is not None else "all")
countries_history = get_json_from_web(url, debug=debug)
reformated_countries_history = []
for country in countries_history:
reformated_country_history = {"name": country["country"], "timeline": []}
for date in countries_history[0]["timeline"]["cases"].keys():
split = date.split('/')
new_date = "20{}/{}/{}".format(split[2], split[0], split[1])
reformated_country_history["timeline"].append({"date": new_date,
"cases": country["timeline"]["cases"][date],
"deaths": country["timeline"]["deaths"][date],
"recovered": country["timeline"]["recovered"][date]})
reformated_countries_history.append(reformated_country_history)
# print(reformated_countries_history)
country_id_sql_script = """SELECT id FROM "countries" WHERE name=? """
countries_history_tuples = []
countries_history_sql_script = """INSERT OR REPLACE INTO "countries_history" ("id", "date", "cases", "deaths", "recovered") VALUES (?, ?, ?, ?, ?) """
for country in reformated_countries_history:
try:
_id = query(country_id_sql_script, (country["name"],), db_path)[0][0]
# print(_id)
countries_history_tuples.extend([(_id, timeline["date"], timeline["cases"],
timeline["deaths"], timeline["recovered"])
for timeline in country["timeline"]])
except IndexError:
if debug:
print("Error inserting country: {}".format(country["name"]))
execute_many(countries_history_sql_script, countries_history_tuples, database_path=db_path,
message="insert countries history", debug=debug)
def insert_countries(db_path):
latest_countries = get_json_from_web(GET_LATEST_COUNTRY_URL)
countries_tuples = [(country["countryInfo"]["_id"], country["country"], country["countryInfo"]["iso2"],
country["countryInfo"]["iso3"], country["countryInfo"]["lat"], country["countryInfo"]["long"],
country["countryInfo"]["flag"], country["continent"])
for country in latest_countries]
countries_sql_script = """INSERT OR REPLACE INTO "countries" ("id", "name", "iso2", "iso3", "lat", "long", "flag", "continent_id") VALUES (?, ?, ?, ?, ?, ?, ?, (SELECT id FROM continents WHERE name=?)) """
execute_many(countries_sql_script, countries_tuples, database_path=db_path, message="countries")
def update_ro_counties_diacritics(db_path, debug=True):
counties = get_json_from_web(GET_COUNTIES_URL, debug=debug)
counties_tuples = [(change_old_diacritics(county["nume"]), county["auto"])
for county in counties["judete"]]
counties_sql_script = """UPDATE "counties" SET name=? WHERE county_code=? """
execute_many(counties_sql_script,
counties_tuples,
database_path=db_path,
message="counties")
def insert_continents(db_path):
latest_continents = get_json_from_web(GET_LATEST_CONTINENT_URL)
continents_tuples = [
(continent["continent"], continent["continentInfo"]["lat"],
continent["continentInfo"]["long"]) for continent in latest_continents
]
continents_sql_script = """INSERT OR REPLACE INTO "continents" ("name", "lat", "long") VALUES (?, ?, ?) """
execute_many(continents_sql_script,
continents_tuples,
database_path=db_path,
message="continents")
def insert_ro_counties(db_path, debug=True, update_diacritics=True):
counties = get_counties(debug)
counties_tuples = [(county["county_id"], county["county_name"],
county["county_population"]) for county in counties]
counties_sql_script = """INSERT OR REPLACE INTO "counties" ("county_code", "name", "population", "country_id")
VALUES (?, ?, ?, (SELECT id from countries where iso3="ROU")) """
execute_many(counties_sql_script,
counties_tuples,
database_path=db_path,
message="counties")
if update_diacritics:
update_ro_counties_diacritics(db_path, debug=debug)
def insert_latest_world(db_path, yesterday=False, two_days_ago=False, debug=True):
url = "{}?yesterday={}&twoDaysAgo={}".format(GET_LATEST_world_URL, 1 if yesterday else 0, 1 if two_days_ago else 0)
latest_world = get_json_from_web(url, debug=debug)
date = 'twoDaysAgo' if two_days_ago else 'yesterday' if yesterday else 'today'
world_tuples = [
(date, latest_world["updated"], latest_world["cases"], latest_world["todayCases"], latest_world["deaths"],
latest_world["todayDeaths"], latest_world["recovered"], latest_world["todayRecovered"],
latest_world["active"],
latest_world["critical"], latest_world["casesPerOneMillion"], latest_world["deathsPerOneMillion"],
latest_world["tests"], latest_world["testsPerOneMillion"], latest_world["population"],
latest_world["activePerOneMillion"], latest_world["recoveredPerOneMillion"],
latest_world["criticalPerOneMillion"], latest_world["affectedCountries"])]
latest_world_sql_script = """INSERT OR REPLACE INTO "world_latest" ("date", "updated", "cases", "today_cases", "deaths", "today_deaths", "recovered", "today_recovered", "active", "critical", "cases_per_one_million", "deaths_per_one_million", "tests", "tests_per_one_million", "population", "active_per_one_million", "recovered_per_one_million", "critical_per_one_million", "affected_countries") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """
execute_many(latest_world_sql_script, world_tuples, database_path=db_path, message="latest world", debug=debug)
def update_latest_ro_counties_data(database_path, web_driver_folder_path):
latest_counties = extract_romania_counties_data(web_driver_folder_path)
counties_tuples = []
counties_tuples.extend([
(county["name"], latest_counties["updated"], county["total_cases"],
county["new_cases"], county["recovered"], county["deaths"])
for county in latest_counties["counties"]
])
latest_counties_sql_script = """INSERT OR REPLACE INTO "counties_latest" ("id", "date", "cases", "today_cases", "deaths", "recovered") VALUES ((SELECT id FROM counties WHERE name=?), ?, ?, ?, ?, ?)"""
print(counties_tuples)
execute_many(latest_counties_sql_script,
counties_tuples,
database_path=database_path,
message="counties")
def insert_world_history(db_path, days=None, debug=True):
url = "{}?lastdays={}".format(GET_HISTORICAL_world_URL, days if days is not None else "all")
world_history = get_json_from_web(url, debug=debug)
timeline = []
for date in world_history["cases"].keys():
split = date.split('/')
new_date = "20{}/{}/{}".format(split[2], split[0], split[1])
timeline.append({"date": new_date, "cases": world_history["cases"][date],
"deaths": world_history["deaths"][date],
"recovered": world_history["recovered"][date]})
world_history_tuples = []
world_history_sql_script = """INSERT OR REPLACE INTO "world_history" ("date", "cases", "deaths", "recovered") VALUES (?, ?, ?, ?) """
world_history_tuples.extend([(date["date"], date["cases"],
date["deaths"], date["recovered"])
for date in timeline])
execute_many(world_history_sql_script, world_history_tuples, database_path=db_path,
message="insert world history", debug=debug)
def insert_ro_counties_history(db_path, debug=True):
covid_romania = get_json_from_web(GET_HISTORY_COUNTY_URL, debug=debug)
registrations = covid_romania["covid_romania"]
counties_tuples = []
for registration in registrations:
if "county_data" not in registration:
continue
date = registration["reporting_date"]
counties = registration["county_data"]
counties_tuples.extend([
(county["county_id"], date, county["total_cases"],
county["county_population"]) for county in counties
])
counties_sql_script = """INSERT OR REPLACE INTO "counties_history" ("id", "date", "total_cases", "population")
VALUES ((SELECT id from counties where county_code=?), ?, ?, ?) """
execute_many(counties_sql_script,
counties_tuples,
database_path=db_path,
message="counties",
debug=debug)
def insert_latest_countries(db_path, yesterday=False, two_days_ago=False, debug=True):
url = "{}?yesterday={}&twoDaysAgo={}".format(GET_LATEST_COUNTRY_URL, 1 if yesterday else 0,
1 if two_days_ago else 0)
latest_countries = get_json_from_web(url, debug=debug)
countries_tuples = []
date = 'twoDaysAgo' if two_days_ago else 'yesterday' if yesterday else 'today'
for country in latest_countries:
_id = country["countryInfo"]["_id"]
if _id is None:
print("Error inserting country: {}".format(country["country"]))
continue
countries_tuples.append(
(_id, date, country["updated"], country["cases"], country["todayCases"], country["deaths"],
country["todayDeaths"], country["recovered"], country["todayRecovered"], country["active"],
country["critical"], country["casesPerOneMillion"], country["deathsPerOneMillion"],
country["tests"], country["testsPerOneMillion"], country["population"],
country["activePerOneMillion"], country["recoveredPerOneMillion"], country["criticalPerOneMillion"]))
latest_countries_sql_script = """INSERT OR REPLACE INTO "countries_latest" ("id", "date", "updated", "cases", "today_cases", "deaths", "today_deaths", "recovered", "today_recovered", "active", "critical", "cases_per_one_million", "deaths_per_one_million", "tests", "tests_per_one_million", "population", "active_per_one_million", "recovered_per_one_million", "critical_per_one_million") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """
execute_many(latest_countries_sql_script, countries_tuples, database_path=db_path, message="latest countries",
debug=debug)