def relateInfections(self):
countrys_data = self.getCurrentStaticData()
querys = []
for country in countrys_data:
print(f"getting infectors of {country}")
if countrys_data[country]['first_outbreak'] != "unknown":
for infector in countrys_data[country]['first_outbreak']:
infector_uuid = self.__getCountryUuid(
infector, countrys_data[infector])
infected_uuid = self.__getCountryUuid(
country, countrys_data[country])
sql = f"INSERT INTO `infectedinfector`(infected, infector) VALUES ('{infected_uuid}', '{infector_uuid}');"
querys.append(sql)
mysql = getConnAndCursor('inserter', 'covid19')
try:
points = 1
for sql in querys:
print(f"\rInserting data{'.'*(points%3)}", end='')
mysql["cursor"].execute(sql)
mysql["conn"].commit()
points += 1
except Exception as e:
print(f"Error on query: {sql}")
raise e
finally:
mysql["conn"].close()
print('\n')
def insertDailyCases(self, data=None):
data = data if data else self.getCurrentStaticData()
countries_path = "./data_sources/countries_data/"
daily_cases = set(self.__getDailyCasesUuids())
#self.updateCasesData()
mysql = getConnAndCursor('inserter', 'covid19')
try:
for country in data:
if not os.path.exists(os.path.join(countries_path, country)):
print(f"Skipping {country}...")
continue
print(f"Updating cases of {country}...")
for case in os.scandir(os.path.join(countries_path, country)):
if case.name == '0001-01-01.json':
continue
uuid = self.hasher.get_hash(case.name + country)
if uuid in daily_cases:
continue
with open(case.path) as f:
case_data = json.load(f)
if not case_data:
continue
country_uuid = self.__getCountryUuid(
country, data[country])
sql = "INSERT INTO `daily_cases`(uuid, date, cases, deaths, recoverys, country) VALUES ("
sql += f"'{uuid}', "
sql += f"'{case_data['Date'].replace('T',' ').replace('Z',' ')}', "
sql += f"{case_data['Confirmed']}, "
sql += f"{case_data['Deaths']}, "
sql += f"{case_data['Recovered']}, "
sql += f"'{country_uuid}'"
sql += ");"
mysql["cursor"].execute(sql)
mysql["conn"].commit()
def insertStatus(self):
status_data = self.__getCurrentCountriesStatus()
countrys_data = self.getCurrentStaticData()
mysql = getConnAndCursor("inserter", "covid19")
try:
for country in status_data:
if country not in countrys_data:
continue
arrival = datetime.fromtimestamp(
countrys_data[country]['arrival_date']
) if countrys_data[country]['arrival_date'] != 0 else 'NULL'
uuid = self.hasher.get_hash(
country + str(countrys_data[country]['arrival_date']))
sql = "INSERT INTO `overall_status`(uuid, cases, total_recoverys, total_deaths, tests_made, arrival, index_case) VALUES ("
sql += f"'{uuid}', "
sql += f"{status_data[country]['cases']}, "
sql += f"{status_data[country]['recoverys']}, "
sql += f"{status_data[country]['deaths']}, "
sql += f"{countrys_data[country]['tests']}, "
sql += f"'{arrival}', " if arrival != "NULL" else f"{arrival}, "
sql += f"'{countrys_data[country]['index_case']}'"
sql += ");"
print(sql)
mysql["cursor"].execute(sql)
mysql["conn"].commit()
mysql["cursor"].execute(
f"UPDATE `countries` SET overall_status='{uuid}' WHERE uuid='{self.hasher.get_hash(country + str(countrys_data[country]['long']) + str(countrys_data[country]['lat']))}';"
)
mysql["conn"].commit()
except Exception as e:
raise e
finally:
mysql['conn'].close()
def insertStaticData(self):
data = self.getCurrentStaticData()
mysql = getConnAndCursor('inserter', 'covid19')
try:
print("Inserting static data...")
for country in data:
sql = "INSERT INTO `countries`(uuid, country_name, population, cca2, cca3, ccn3, hospibed_per_kp, slug, latitud, longitud) VALUES ("
sql += f"'{self.hasher.get_hash(country + str(data[country]['long']) + str(data[country]['lat']))}', "
sql += f"'{country}', "
sql += f"{data[country]['population']}, "
sql += f"'{data[country]['cca2']}', "
sql += f"'{data[country]['cca3']}', "
sql += f"{data[country]['ccn3']}, "
sql += f"{data[country]['hospital_bed']}, "
sql += f"'{data[country]['slug']}', "
sql += f"{data[country]['long']}, "
sql += f"{data[country]['lat']}"
sql += ");"
print(sql)
mysql['cursor'].execute(sql)
mysql['conn'].commit()
except Exception as e:
raise e
finally:
mysql['conn'].close()
def __getDailyCasesUuids(self):
mysql = getConnAndCursor("inserter", "covid19")
try:
mysql["cursor"].execute(f"SELECT uuid FROM `daily_cases`;")
daily_cases = [row['uuid'] for row in mysql["cursor"].fetchall()]
return daily_cases
except Exception as e:
raise e
finally:
mysql['conn'].close()
def updateData(self):
self.updateTests()
current_static_data = self.getCurrentStaticData()
mysql = getConnAndCursor("inserter", "covid19")
if not os.path.exists(self.__todays_wiki_data_path):
print(f"Creating data_source: {self.__todays_wiki_data_path}...")
self.createWikipediaSource()
with open(self.__todays_wiki_data_path) as f:
wiki_data = json.load(f)
print("Updating general Status...")
self.updateGeneralStatus(wiki_data, current_static_data, mysql)
print("Updating daily cases...")
self.updateCasesData()
self.insertDailyCases(current_static_data)
print("Done")