covid19db 2.0.9

Utility for building and accessing COVID-19 datasets
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209

/* OWID loader

Copyright (c) 2019-2020 John Goerzen

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

pub use crate::dateutil::*;
pub use crate::dbschema::*;
pub use crate::loader::parseutil::*;
use chrono::NaiveDate;
use csv;
use serde::Deserialize;
use sqlx::Transaction;

#[derive(Debug, Deserialize, PartialEq, Clone)]
pub struct OWIDRecord {
    pub iso_code: Option<String>,
    pub continent: Option<String>,
    pub location: String,
    pub date: String,
    pub total_cases: Option<f64>,
    pub new_cases: Option<f64>,
    pub new_cases_smoothed: Option<f64>,
    pub total_deaths: Option<f64>,
    pub new_deaths: Option<f64>,
    pub new_deaths_smoothed: Option<f64>,
    pub total_cases_per_million: Option<f64>,
    pub new_cases_per_million: Option<f64>,
    pub new_cases_smoothed_per_million: Option<f64>,
    pub total_deaths_per_million: Option<f64>,
    pub new_deaths_per_million: Option<f64>,
    pub new_deaths_smoothed_per_million: Option<f64>,
    pub icu_patients: Option<f64>,
    pub icu_patients_per_million: Option<f64>,
    pub hosp_patients: Option<f64>,
    pub hosp_patients_per_million: Option<f64>,
    pub weekly_icu_admissions: Option<f64>,
    pub weekly_icu_admissions_per_million: Option<f64>,
    pub weekly_hosp_admissions: Option<f64>,
    pub weekly_hosp_admissions_per_million: Option<f64>,
    pub total_tests: Option<f64>,
    pub new_tests: Option<f64>,
    pub total_tests_per_thousand: Option<f64>,
    pub new_tests_per_thousand: Option<f64>,
    pub new_tests_smoothed: Option<f64>,
    pub new_tests_smoothed_per_thousand: Option<f64>,
    pub tests_per_case: Option<f64>,
    pub positive_rate: Option<f64>,
    pub tests_units: Option<String>,
    pub stringency_index: Option<f64>,
    pub population: Option<f64>,
    pub population_density: Option<f64>,
    pub median_age: Option<f64>,
    pub aged_65_older: Option<f64>,
    pub aged_70_older: Option<f64>,
    pub gdp_per_capita: Option<f64>,
    pub extreme_poverty: Option<f64>,
    pub cardiovasc_death_rate: Option<f64>,
    pub diabetes_prevalence: Option<f64>,
    pub female_smokers: Option<f64>,
    pub male_smokers: Option<f64>,
    pub handwashing_facilities: Option<f64>,
    pub hospital_beds_per_thousand: Option<f64>,
    pub life_expectancy: Option<f64>,
    pub human_development_index: Option<f64>,
}

pub fn parse_to_final<A: Iterator<Item = csv::StringRecord>>(
    striter: A,
) -> impl Iterator<Item = OWIDRecord> {
    striter.filter_map(|x| rec_to_struct(&x).expect("rec_to_struct"))
}

/** Parse the CSV, loading it into the database.
 * Will panic on parse error.  */
pub async fn load<'a, A: std::io::Read>(
    rdr: &'a mut csv::Reader<A>,
    mut transaction: Transaction<sqlx::pool::PoolConnection<sqlx::SqliteConnection>>,
) {
    assert_eq!(
        vec![
            "iso_code",
            "continent",
            "location",
            "date",
            "total_cases",
            "new_cases",
            "new_cases_smoothed",
            "total_deaths",
            "new_deaths",
            "new_deaths_smoothed",
            "total_cases_per_million",
            "new_cases_per_million",
            "new_cases_smoothed_per_million",
            "total_deaths_per_million",
            "new_deaths_per_million",
            "new_deaths_smoothed_per_million",
            "icu_patients",
            "icu_patients_per_million",
            "hosp_patients",
            "hosp_patients_per_million",
            "weekly_icu_admissions",
            "weekly_icu_admissions_per_million",
            "weekly_hosp_admissions",
            "weekly_hosp_admissions_per_million",
            "total_tests",
            "new_tests",
            "total_tests_per_thousand",
            "new_tests_per_thousand",
            "new_tests_smoothed",
            "new_tests_smoothed_per_thousand",
            "tests_per_case",
            "positive_rate",
            "tests_units",
            "stringency_index",
            "population",
            "population_density",
            "median_age",
            "aged_65_older",
            "aged_70_older",
            "gdp_per_capita",
            "extreme_poverty",
            "cardiovasc_death_rate",
            "diabetes_prevalence",
            "female_smokers",
            "male_smokers",
            "handwashing_facilities",
            "hospital_beds_per_thousand",
            "life_expectancy",
            "human_development_index"
        ],
        rdr.headers().unwrap().iter().collect::<Vec<&str>>()
    );
    let recs = parse_records(rdr.byte_records());
    let finaliter = parse_to_final(recs);
    for rec in finaliter {
        let nd = NaiveDate::parse_from_str(rec.date.as_str(), "%Y-%m-%d").unwrap();
        let dbrec = OWID {
            date_julian: nd_to_day(&nd),
            iso_code: rec.iso_code,
            continent: rec.continent,
            location: rec.location,
            total_cases: rec.total_cases,
            new_cases: rec.new_cases,
            new_cases_smoothed: rec.new_cases_smoothed,
            total_deaths: rec.total_deaths,
            new_deaths: rec.new_deaths,
            new_deaths_smoothed: rec.new_deaths_smoothed,
            total_cases_per_million: rec.total_cases_per_million,
            new_cases_per_million: rec.new_cases_per_million,
            new_cases_smoothed_per_million: rec.new_cases_smoothed_per_million,
            total_deaths_per_million: rec.total_deaths_per_million,
            new_deaths_per_million: rec.new_deaths_per_million,
            new_deaths_smoothed_per_million: rec.new_deaths_smoothed_per_million,
            new_tests: rec.new_tests,
            icu_patients: rec.icu_patients,
            icu_patients_per_million: rec.icu_patients_per_million,
            hosp_patients: rec.hosp_patients,
            hosp_patients_per_million: rec.hosp_patients_per_million,
            weekly_icu_admissions: rec.weekly_icu_admissions,
            weekly_icu_admissions_per_million: rec.weekly_icu_admissions_per_million,
            weekly_hosp_admissions: rec.weekly_hosp_admissions,
            weekly_hosp_admissions_per_million: rec.weekly_hosp_admissions_per_million,
            total_tests: rec.total_tests,
            new_tests_smoothed: rec.new_tests_smoothed,
            total_tests_per_thousand: rec.total_tests_per_thousand,
            new_tests_per_thousand: rec.new_tests_per_thousand,
            new_tests_smoothed_per_thousand: rec.new_tests_smoothed_per_thousand,
            tests_per_case: rec.tests_per_case,
            positive_rate: rec.positive_rate,
            tests_units: rec.tests_units,
            stringency_index: rec.stringency_index,
            population: rec.population,
            population_density: rec.population_density,
            median_age: rec.median_age,
            aged_65_older: rec.aged_65_older,
            aged_70_older: rec.aged_70_older,
            gdp_per_capita: rec.gdp_per_capita,
            extreme_poverty: rec.extreme_poverty,
            cardiovasc_death_rate: rec.cardiovasc_death_rate,
            diabetes_prevalence: rec.diabetes_prevalence,
            female_smokers: rec.female_smokers,
            male_smokers: rec.male_smokers,
            handwashing_facilities: rec.handwashing_facilities,
            hospital_beds_per_thousand: rec.hospital_beds_per_thousand,
            life_expectancy: rec.life_expectancy,
            human_development_index: rec.human_development_index,
        };
        let query = sqlx::query(OWID::insert_str());
        dbrec
            .bind_query(query)
            .execute(&mut transaction)
            .await
            .unwrap();
    }
    transaction.commit().await.unwrap();
}