-
Notifications
You must be signed in to change notification settings - Fork 2
/
app.py
140 lines (109 loc) · 5.55 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
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
from flask import Flask, render_template, request, jsonify, Markup, Response
import numpy as np
import seaborn as sns
import json
import os
import sqlite3 #Database management library we used
import jinja2
import pandas as pd
#for row in c.execute('SELECT * FROM USIODB'):
# print row
app = Flask(__name__, static_folder='.', static_url_path='')
# Handler
@app.route("/")
def index():
return render_template('home.html')
@app.route("/pivot_table")
def pivot_table():
# Process the Database
conn = sqlite3.connect('USIODB.db')
c = conn.cursor()
table = html_table(c)
c.close()
conn.close()
return render_template('pivot_table.html',table = Markup(table))
def html_table(c):
# Generates table
conn = sqlite3.connect('USIODB.db')
df = pd.read_sql_query("SELECT * FROM USIODB", conn)
return df.to_html(classes="table table-striped").replace('border="1"','border="0"')
@app.route("/pivot_table_builder", methods = ['POST','GET'])
def pivot_table_builder():
if request.method == 'POST':
dic = request.form
# Check Validation
if len(dic)!=5:
raise ValueError
else:
table_block = build_table(request.form)
return table_block
return render_template('pivot_table_builder.html')
def build_table(dic):
colLabel = dic['colLabel']
filterName = dic['filterName']
filterQuery = dic['filterQuery']
filterValue = dic['filterValue']
aggregationCol = dic['aggregationCol']
conn = sqlite3.connect('USIODB.db')
if (filterQuery == 'contains'):
sql = "SELECT Period,SUM({}),AVG({}),MAX({}),MIN({}),COUNT({}),{} FROM USIODB WHERE {} LIKE '%{}%' ".format(to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(aggregationCol),
to_valid_query(filterName),
filterValue.strip())
elif (filterQuery == 'does not contain'):
sql = "SELECT Period,SUM({}),AVG({}),MAX({}),MIN({}),COUNT({}),{} FROM USIODB WHERE {} NOT LIKE '%{}%'".format(to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(aggregationCol),
to_valid_query(filterName),
filterValue.strip())
else:
sql = "SELECT Period,SUM({}),AVG({}),MAX({}),MIN({}),COUNT({}),{} FROM USIODB WHERE {} {} {}".format(to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(colLabel),
to_valid_query(aggregationCol),
to_valid_query(filterName),
filterQuery,
filterValue)
sql+=" GROUP BY {}".format(to_valid_query(aggregationCol))
print(sql)
cm = sns.light_palette("yellow", as_cmap=True)
df = (pd.read_sql_query(sql, conn)
.loc[:4]
.style
.background_gradient(cmap='viridis', low=.5, high=0)
.highlight_null('red')
.background_gradient(cmap=cm)
)
conn.close()
return df.render()
def to_valid_query(x):
return {
'Period' : 'Period',
'Total Balance' : 'Total_Balance',
'Goods Balance' : 'Goods_Balance',
'Service Balance' : 'Services_Balance',
'Total Exports' : 'Total_Exports',
'Export Inc/Dec' : 'Export_Inc_Dec',
'Goods Exports' : 'Goods_Exports',
'Services Exports' : 'Services_Exports',
'Total Imports' : 'Total_Imports',
'Import Inc/Dec' : 'Import_Inc_Dec',
'Goods Imports' : 'Goods_Imports',
'Services Imports' : 'Services_Imports'
}[x]
@app.route("/interesting_sights")
def interesting_sights():
return render_template('interesting_sights.html')
def table_selector():
return false
if __name__ == "__main__":
app.run(debug=True, host='127.0.0.1', port=8765)