start db code into its own file, update migrations with backfill

1 files changed, 20 insertions, 81 deletions

diff --git a/migrations.py b/migrations.py
index b87a82a..5f692cb 100644
--- a/migrations.py
+++ b/migrations.py
@@ -1,29 +1,10 @@
 import os
-import csv
+import json
 import sqlite3
 
-LATEST_DATA={}
+from poller.db import create_tables, get_latest_from_db
 
-def create_tables():
-    print('creating tables')
-    db_conn = sqlite3.connect('./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()
+LATEST_DATA={}
 
 def update_db():
     db_conn = sqlite3.connect('./data/data.sqlite3')
@@ -45,44 +26,6 @@ def update_db():
     db_conn.commit()
     db_conn.close()
 
-def get_latest_from_db():
-
-    db_conn = sqlite3.connect('./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()
@@ -98,29 +41,25 @@ if not os.path.exists('./data'):
 
 create_tables()
 
-with open('historical_data.csv', 'r') as csvfile:
+with open('history/history.json', 'r') as fd:
     print('importing data...')
-    csvreader = csv.reader(csvfile)
-    firstRow = True
-    for row in csvreader:
-        if firstRow: 
-            firstRow = False
-            continue
+    data = json.loads(fd.read())
+    for day in data:
+        print(day)
         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'
+            'alert_level': str(day['alert_level']),        
+            'total_students': int(day['total_students']),
+            'total_staff': int(day['total_staff']),
+            'new_students': int(day['new_students']),
+            'new_staff': int(day['new_staff']),
+            'quarantine_on_campus': int(day['quarantine_on_campus']),
+            'quarantine_off_campus': int(day['quarantine_off_campus']),
+            'isolation_on_campus': int(day['isolation_on_campus']),
+            'isolation_off_campus': int(day['isolation_off_campus']),
+            'beds_available': int(day['beds_available']),
+            'tests_administered': int(day['tests_administered']),
+            'last_updated': day['last_updated']
         }
-        if not db_is_same():
-            update_db()
+        update_db()
     print('data imported!')