# %%
-# DEATH_COUNT_THRESHOLD = 10
-COUNTRIES_CORE = tuple('IT DE UK ES IE FR BE'.split())
-# COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()
-# COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()
-# # COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()
+COUNTRIES_CORE = tuple(sorted('ITA DEU GBR ESP IRL FRA BEL'.split()))
+COUNTRIES_FRIENDS = tuple('ITA GBR ESP BEL SVN MEX'.split())
-# COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA
-# 'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',
-# 'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',
-# 'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']
-# COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))
-# COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))
-
-
-# # Write results to summary file
# %%
def singleton_sql_value(engine, query_string):
with engine.connect() as conn:
result = conn.execute(query_string)
- return result.next()[0]
+ return result.first()[0]
# %%
# %%
-last_intl_date = singleton_sql_value(engine, 'select max(report_date) from weekly_cases')
+last_intl_date = singleton_sql_value(engine, 'select max(date) from weekly_cases')
# %%
# %%
-total_uk_deaths = singleton_sql_value(engine, 'select sum(new_deaths) from uk_data')
+# total_uk_deaths = singleton_sql_value(engine, 'select (cum_deaths) from uk_data')
+total_uk_deaths = singleton_sql_value(engine,
+ 'select cum_deaths from uk_data where cum_deaths > 0 order by date desc limit 1')
deaths_in_past_month = singleton_sql_value(engine, f"select sum(new_deaths) from uk_data where date > '{thirty_days_ago.isoformat()}'")
cases_in_past_month = singleton_sql_value(engine, f"select sum(new_cases) from uk_data where date > '{thirty_days_ago.isoformat()}'")
total_uk_deaths, deaths_in_past_month, cases_in_past_month
# %%
-query_string = f'''select geo_id, country_name, culm_deaths
-from weekly_cases join countries using (geo_id)
-where geo_id in {COUNTRIES_CORE}
- and report_date = '{last_intl_date.isoformat()}'
-order by geo_id'''
+query_string = f'''select country_code, country, culm_deaths
+from weekly_cases join countries using (country_code)
+where country_code in {COUNTRIES_CORE}
+ and date = '{last_intl_date.isoformat()}'
+order by country_code'''
with engine.connect() as conn:
results = list(conn.execute(query_string))
f.write('\n')
f.write('![Cases and deaths in last 60 days](cases_and_deaths_last_60_days.png)\n')
f.write('\n')
- f.write('![Deaths compared to past five years](deaths-radar-2021.png)\n')
+ f.write('![Deaths compared to past five years](deaths_radar_2021.png)\n')
f.write('\n')
# %%
'covid_deaths_total_linear.png',
'cases_and_deaths.png',
'cases_and_deaths_last_60_days.png',
- 'deaths-radar-2021.png',
+ 'deaths_radar_2021.png',
'covid_deaths_per_week.png',
'fraction_positive_tests.png',
'tests_and_cases.png',
os.system(f'scp {f} neil@ogedei:/var/www/scripts.njae.me.uk/covid/')
print(f'Transferred {f}')
else:
- print('Cannot transfer {f}: file does not exist')
+ print(f'Cannot transfer {f}: file does not exist')
# %%