X-Git-Url: https://git.njae.me.uk/?p=covid19.git;a=blobdiff_plain;f=publish.py;h=82a3a25dd53b07bfb4f0f08d527032c21620b952;hp=8c970560fa9fa4840c5e966cf7cb85e3f98c3ede;hb=HEAD;hpb=4feace1e2a5076f3efa0fd65b31411ac511efbb1

diff --git a/publish.py b/publish.py
index 8c97056..82a3a25 100644
--- a/publish.py
+++ b/publish.py
@@ -28,27 +28,15 @@ engine = sqlalchemy.create_engine(connection_string)
 
 
 # %%
-# 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]
 
 
 # %%
@@ -56,7 +44,7 @@ last_uk_date = singleton_sql_value(engine, 'select max(date) from uk_data')
 
 
 # %%
-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')
 
 
 # %%
@@ -64,7 +52,9 @@ thirty_days_ago = last_uk_date - datetime.timedelta(days=30)
 
 
 # %%
-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
@@ -95,11 +85,11 @@ with open('covid_summary.md', 'a') as f:
 
 
 # %%
-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))
@@ -144,7 +134,7 @@ with open('covid_summary.md', 'a') as f:
     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')
 
 # %%
@@ -244,7 +234,7 @@ transfer_files = [
     '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',
@@ -267,7 +257,7 @@ for f in transfer_files:
         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')
 
 # %%