-
Notifications
You must be signed in to change notification settings - Fork 0
/
app.py
98 lines (85 loc) · 2.98 KB
/
app.py
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
# import necessary libraries
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
import psycopg2
from psycopg2.extras import RealDictCursor
import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy import func
import pandas as pd
from configs import password
from flask_migrate import Migrate
import numpy as np
import json
#################################################
# Database Setup
#################################################
conn = psycopg2.connect(
host="localhost",
port = 5432,
database="S_Korean_Covid_db",
user="postgres",
password=password)
cur = conn.cursor()
# create instance of Flask app
app = Flask(__name__)
# create route that renders index.html template
@app.route("/")
def index():
# creates cursor
cur = conn.cursor()
# creates list of arrays of the provinces/cities, confirmed cases, deceased, their average temperature
# should be the data for the temp vs cases scatter plot
cur.execute("""
SELECT time_province.province, time_province.confirmed, time_province.deceased, Avg_temp
FROM time_province
INNER JOIN
(SELECT province, AVG(avg_temp) as Avg_temp
FROM weather
GROUP BY province) weather
ON weather.province = time_province.province
""")
weather_results = cur.fetchall() # query results is a list of arrays
# creates list of arrays of the latitude and longitude of cases
# should be the data for the heatmap
cur.execute("""
SELECT province, confirmed, deceased
FROM time_province
""")
heatmap_results = cur.fetchall() # query results is a list of arrays
# creates list of arrays of the province totals with confirmed and deceased included
# should be the data for the province barchart
cur.execute("""
SELECT province, confirmed, deceased
FROM time_province
""")
barchar_results = cur.fetchall() # query results is a list of arrays
# creates list of arrays of the breakdown of cases and deaths by age group
# should be the data for both of the pie charts
cur.execute("""
SELECT age, confirmed, deceased
FROM time_age
""")
pie_results = cur.fetchall() # query results in a list of arrays
# makes all of the data accessible in the html
return render_template("index.html",
scatter_data = weather_results,
heatmap_data=heatmap_results,
barchar_data = barchar_results,
pie_data = pie_results
)
# close the cursor
cur.close()
# close the connection
conn.close()
if __name__ == "__main__":
app.run(debug=True)
# multiple csv files - done
# pull csvs to db - done
# do calculations inside db alchemy etl - done
# pull data into flask - converts db useable form - done
# push information to html dashboard
# note: unit 10 day 3 / check out titanic data as well
# heroku - more support - instead of github pages