diff options
| -rw-r--r-- | historical_data.csv | 48 | ||||
| -rw-r--r-- | migrations.py | 128 |
2 files changed, 176 insertions, 0 deletions
diff --git a/historical_data.csv b/historical_data.csv new file mode 100644 index 0000000..0c88d4a --- /dev/null +++ b/historical_data.csv @@ -0,0 +1,48 @@ +date,alert_level,new_students,new_staff,total_students,total_staff,quarantine_on_campus,quarantine_off_campus,isolation_on_campus,isolation_off_campus,tests_administered,beds_available +2020-08-27,green,2,1,3,1,3,4,3,4,0,95 +2020-08-28,green,2,1,3,1,3,7,0,3,0,96 +2020-08-31,green,1,0,3,1,4,3,0,1,0,95 +2020-09-01,green,0,0,3,1,7,2,2,0,0,92 +2020-09-02,green,0,0,3,1,6,2,3,0,0,93 +2020-09-03,green,0,0,3,1,11,3,3,0,0,92 +2020-09-04,green,3,1,4,1,1,5,0,1,0,96 +2020-09-06,green,2,0,5,1,3,5,0,1,0,97 +2020-09-07,green,3,0,6,1,5,17,1,6,0,95 +2020-09-08,green,3,0,6,1,5,17,1,6,0,95 +2020-09-10,green,3,0,6,1,1,17,0,3,0,98 +2020-09-11,green,3,0,6,1,1,17,2,4,0,96 +2020-09-14,green,4,0,6,1,4,16,2,1,0,95 +2020-09-15,green,4,0,7,1,9,19,3,2,0,92 +2020-09-16,green,5,0,8,1,12,19,3,2,0,90 +2020-09-17,green,4,1,8,2,16,18,2,1,0,88 +2020-09-18,green,4,1,9,2,14,16,2,1,0,89 +2020-09-21,green,4,1,10,2,8,14,2,1,0,94 +2020-09-22,green,4,1,10,2,10,9,4,1,0,91 +2020-09-24,green,4,1,10,2,15,8,5,1,0,88 +2020-09-25,green,4,1,10,2,10,12,2,0,3747,92 +2020-09-26,green,4,1,10,2,10,12,2,0,3747,92 +2020-09-27,green,4,1,10,2,10,12,2,0,3747,92 +2020-09-28,green,2,1,10,2,1,8,1,0,3751,98 +2020-09-29,green,2,1,10,2,0,9,2,0,3753,98 +2020-09-30,green,2,1,10,2,0,9,2,0,3753,98 +2020-10-01,green,0,0,10,2,0,8,1,2,4038,99 +2020-10-02,green,0,0,10,2,1,6,1,0,4135,98 +2020-10-03,green,0,0,10,2,1,6,1,0,4135,98 +2020-10-04,green,0,0,10,2,1,6,1,0,4135,98 +2020-10-05,green,1,0,10,2,3,1,1,0,4136,97 +2020-10-06,green,1,0,10,2,11,5,5,0,4139,90 +2020-10-08,yellow,3,0,12,2,7,10,4,1,4482,93 +2020-10-09,yellow,3,0,12,2,8,14,4,0,4625,92 +2020-10-12,yellow,4,0,13,2,8,20,3,4,5053,93 +2020-10-13,yellow,4,0,13,2,9,24,4,2,5055,92 +2020-10-15,yellow,5,0,14,2,8,27,4,4,5471,93 +2020-10-16,yellow,6,0,15,2,5,25,1,5,5590,96 +2020-10-19,yellow,6,0,16,2,6,23,3,4,5591,95 +2020-10-20,yellow,7,0,17,2,11,29,3,6,5598,92 +2020-10-21,yellow,6,0,17,2,11,32,3,5,5887,92 +2020-10-23,yellow,7,0,19,2,8,33,1,5,6141,95 +2020-10-26,yellow,8,0,21,2,16,35,2,4,6146,90 +2020-10-27,yellow,9,0,22,2,14,36,2,4,6150,91 +2020-10-28,yellow,10,1,23,3,12,34,3,4,6392,92 +2020-10-29,yellow,10,2,24,4,13,31,3,9,6490,91 +2020-10-30,yellow,17,2,32,4,18,27,6,9,6638,86 diff --git a/migrations.py b/migrations.py new file mode 100644 index 0000000..c93fd0f --- /dev/null +++ b/migrations.py @@ -0,0 +1,128 @@ +import os +import csv +import sqlite3 + +LATEST_DATA={} + +def create_tables(): + print('creating tables') + db_conn = sqlite3.connect('./poller/data/data.sqlite3') + c = db_conn.cursor() + sql = f'CREATE TABLE IF NOT EXISTS `alertlevel` (time DATETIME PRIMARY KEY NOT NULL, color CHAR(50) NOT NULL);' + c.execute(sql) + sql = f'CREATE TABLE IF NOT EXISTS `total` (time DATETIME PRIMARY KEY NOT NULL, total_students INT NOT NULL, total_staff INT NOT NULL);' + c.execute(sql) + sql = f'CREATE TABLE IF NOT EXISTS `new` (time DATETIME PRIMARY KEY NOT NULL, new_students INT NOT NULL, new_staff INT NOT NULL);' + c.execute(sql) + sql = f'CREATE TABLE IF NOT EXISTS `quarantine` (time DATETIME PRIMARY KEY NOT NULL, quarantine_on_campus INT NOT NULL, quarantine_off_campus INT NOT NULL);' + c.execute(sql) + sql = f'CREATE TABLE IF NOT EXISTS `isolation` (time DATETIME PRIMARY KEY NOT NULL, isolation_on_campus INT NOT NULL, isolation_off_campus INT NOT NULL);' + c.execute(sql) + sql = f'CREATE TABLE IF NOT EXISTS `beds` (time DATETIME PRIMARY KEY NOT NULL, beds_available INT NOT NULL);' + c.execute(sql) + sql = f'CREATE TABLE IF NOT EXISTS `tests` (time DATETIME PRIMARY KEY NOT NULL, tests_administered INT NOT NULL);' + c.execute(sql) + db_conn.commit() + db_conn.close() + +def update_db(): + db_conn = sqlite3.connect('./poller/data/data.sqlite3') + c = db_conn.cursor() + sql = f'INSERT INTO `alertlevel` VALUES (\'{LATEST_DATA["last_updated"]}\', \'{LATEST_DATA["alert_level"]}\');' + c.execute(sql) + sql = f'INSERT INTO `total` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["total_students"]}, {LATEST_DATA["total_staff"]});' + c.execute(sql) + sql = f'INSERT INTO `new` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["new_students"]}, {LATEST_DATA["new_staff"]});' + c.execute(sql) + sql = f'INSERT INTO `quarantine` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["quarantine_on_campus"]}, {LATEST_DATA["quarantine_off_campus"]});' + c.execute(sql) + sql = f'INSERT INTO `isolation` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["isolation_on_campus"]}, {LATEST_DATA["isolation_off_campus"]});' + c.execute(sql) + sql = f'INSERT INTO `beds` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["beds_available"]});' + c.execute(sql) + sql = f'INSERT INTO `tests` VALUES (\'{LATEST_DATA["last_updated"]}\', {LATEST_DATA["tests_administered"]});' + c.execute(sql) + db_conn.commit() + db_conn.close() + +def get_latest_from_db(): + + db_conn = sqlite3.connect('./poller/data/data.sqlite3') + c = db_conn.cursor() + sql = 'SELECT max(alertlevel.time), alertlevel.color, total.total_students, total.total_staff, new.new_students, new.new_staff, ' + \ + 'quarantine.quarantine_on_campus, quarantine.quarantine_off_campus, isolation.isolation_on_campus, isolation.isolation_off_campus, ' + \ + 'beds.beds_available, tests.tests_administered ' + \ + 'FROM `alertlevel` ' + \ + 'INNER JOIN `total` ' + \ + 'ON alertlevel.time = total.time ' + \ + 'INNER JOIN `new` ' + \ + 'ON alertlevel.time = new.time ' + \ + 'INNER JOIN `quarantine` ' + \ + 'ON alertlevel.time = quarantine.time ' + \ + 'INNER JOIN `isolation` ' + \ + 'ON alertlevel.time = isolation.time ' + \ + 'INNER JOIN `beds` ' + \ + 'ON alertlevel.time = beds.time ' + \ + 'INNER JOIN `tests` ' + \ + 'ON alertlevel.time = tests.time' + c.execute(sql) + d = c.fetchone() + data = { + 'alert_level': d[1], + 'total_students': d[2], + 'total_staff': d[3], + 'new_students': d[4], + 'new_staff': d[5], + 'quarantine_on_campus': d[6], + 'quarantine_off_campus': d[7], + 'isolation_on_campus': d[8], + 'isolation_off_campus': d[9], + 'beds_available': d[10], + 'tests_administered': d[11], + 'last_updated': d[0] + } + return data + +def db_is_same(): + global LATEST_DATA + latest_data = get_latest_from_db() + if latest_data is None or LATEST_DATA is None: + return False + for key in list(latest_data.keys()): + if key != 'last_updated' and LATEST_DATA[key] != latest_data[key]: + return False + return True + +if not os.path.exists('./poller'): + os.mkdir('./poller') +if not os.path.exists('./poller/data') + os.mkdir('./poller/data') + +create_tables() + +with open('historical_data.csv', 'r') as csvfile: + print('importing data...') + csvreader = csv.reader(csvfile) + firstRow = True + for row in csvreader: + if firstRow: + firstRow = False + continue + LATEST_DATA = { + 'alert_level': str(row[1]), + 'total_students': int(row[4]), + 'total_staff': int(row[5]), + 'new_students': int(row[2]), + 'new_staff': int(row[3]), + 'quarantine_on_campus': int(row[6]), + 'quarantine_off_campus': int(row[7]), + 'isolation_on_campus': int(row[8]), + 'isolation_off_campus': int(row[9]), + 'beds_available': int(row[11]), + 'tests_administered': int(row[10]), + 'last_updated': f'{row[0]} 16:00:00' + } + if not db_is_same(): + update_db() + print('data imported!') + |