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
```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,
deaths integer,
CONSTRAINT week_nation PRIMARY KEY(year, week, nation)
);
+'''
+with engine.connect() as connection:
+ connection.execute(query_string)
```
```python Collapsed="false"
%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:])
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
```
```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