X-Git-Url: https://git.njae.me.uk/?p=covid19.git;a=blobdiff_plain;f=historical_deaths_import.md;h=1a226be4ee219a0bc584291853eb7ed0e8b4bb30;hp=9a7acd841efcf27c4477340aaa98d92877cfb783;hb=HEAD;hpb=5afedd66506be7575034ae6deebcfaa7c2ced978

diff --git a/historical_deaths_import.md b/historical_deaths_import.md
index 9a7acd8..1a226be 100644
--- a/historical_deaths_import.md
+++ b/historical_deaths_import.md
@@ -5,8 +5,8 @@ jupyter:
     text_representation:
       extension: .md
       format_name: markdown
-      format_version: '1.2'
-      jupytext_version: 1.9.1
+      format_version: '1.3'
+      jupytext_version: 1.11.1
   kernelspec:
     display_name: Python 3
     language: python
@@ -50,13 +50,15 @@ connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'
 
 ```python Collapsed="false"
 conn = create_engine(connection_string)
+engine = create_engine(connection_string)
 ```
 
 ```python Collapsed="false"
 england_wales_filename = 'uk-deaths-data/publishedweek532020.xlsx'
 ```
 
-```sql Collapsed="false"
+```python
+query_string = '''
 drop table if exists all_causes_deaths;
 create table all_causes_deaths (
     week integer,
@@ -66,6 +68,9 @@ create table all_causes_deaths (
     deaths integer,
     CONSTRAINT week_nation PRIMARY KEY(year, week, nation)
 );
+'''
+with engine.connect() as connection:
+    connection.execute(query_string)
 ```
 
 ```python Collapsed="false"
@@ -321,6 +326,14 @@ deaths_headlines_s
 %sql select * from all_causes_deaths limit 5
 ```
 
+```python
+query_string = '''
+delete from all_causes_deaths where nation = 'Scotland';
+'''
+with engine.connect() as connection:
+    connection.execute(query_string)
+```
+
 ```python Collapsed="false"
 for year, ser in deaths_headlines_s.items():
     year_i = int(year[-4:])
@@ -328,7 +341,11 @@ for year, ser in deaths_headlines_s.items():
     for week, deaths in ser.dropna().iteritems():
 #         print(datetime.date.fromisocalendar(year_i, week, 7), deaths)
         dut = datetime.date.fromisocalendar(year_i, week, 7)
-        %sql insert into all_causes_deaths(week, year, date_up_to, nation, deaths) values ({week}, {year_i}, :dut, 'Scotland', {deaths})
+        query_string = f'''insert into 
+            all_causes_deaths(week, year, date_up_to, nation, deaths) 
+            values ({week}, {year_i}, '{dut}', 'Scotland', {deaths});'''
+        with engine.connect() as connection:
+            connection.execute(query_string)
 ```
 
 ```python
@@ -411,7 +428,11 @@ rd.head()
 ```
 
 ```python
-%sql delete from all_causes_deaths where nation = 'England'
+query_string = '''
+delete from all_causes_deaths where nation = 'England';
+'''
+with engine.connect() as connection:
+    connection.execute(query_string)
 ```
 
 ```python