-
Notifications
You must be signed in to change notification settings - Fork 0
/
App.py
141 lines (106 loc) · 4.19 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
141
from flask import Flask, render_template, url_for, request, redirect, flash
from flask_sqlalchemy import SQLAlchemy
import pymysql
import pandas as pd
app = Flask(__name__)
app.secret_key = "Secret Key"
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@localhost/application'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class records(db.Model):
id = db.Column(db.Integer, primary_key = True)
year = db.Column(db.String(100))
quarter = db.Column(db.String(100))
product_1_items = db.Column(db.Integer)
product_1_amount = db.Column(db.Float)
product_2_items = db.Column(db.Integer)
product_2_amount = db.Column(db.Float)
product_3_items = db.Column(db.Integer)
product_3_amount = db.Column(db.Float)
def __init__(self, year, quarter, product_1_items, product_1_amount,product_2_items, product_2_amount,product_3_items, product_3_amount):
self.year = year
self.quarter = quarter
self.product_1_items = product_1_items
self.product_1_amount = product_1_amount
self.product_2_items = product_2_items
self.product_2_amount = product_2_amount
self.product_3_items = product_3_items
self.product_3_amount = product_3_amount
@app.route('/')
def Index():
all_data = records.query.all()
quarters = ['Q1', 'Q2','Q3', 'Q4']
years = ['2000', '2001', '2002']
return render_template('index.html', records = all_data, quarters = quarters, years = years)
@app.route('/add', methods = ['POST'])
def add():
if request.method == 'POST':
year = request.form['year']
quarter = request.form['quarter']
product_1_items = request.form['product_1_items']
product_1_amount = request.form['product_1_amount']
product_2_items = request.form['product_2_items']
product_2_amount = request.form['product_2_amount']
product_3_items = request.form['product_3_items']
product_3_amount = request.form['product_3_amount']
product_data = records(year, quarter, product_1_items, product_1_amount, product_2_items, product_2_amount, product_3_items, product_3_amount)
db.session.add(product_data)
db.session.commit()
flash("Record Added Successfully!")
return redirect(url_for('Index'))
@app.route('/update', methods = ['GET', 'POST'])
def update():
if request.method == 'POST':
record_data = records.query.get(request.form.get('id'))
record_data.year = request.form['year']
record_data.quarter = request.form['quarter']
record_data.product_1_items = request.form['product_1_items']
record_data.product_1_amount = request.form['product_1_amount']
record_data.product_2_items = request.form['product_2_items']
record_data.product_2_amount = request.form['product_2_amount']
record_data.product_3_items = request.form['product_3_items']
record_data.product_3_amount = request.form['product_3_amount']
db.session.commit()
flash("Record Updated Successfully!")
return redirect(url_for('Index'))
@app.route('/delete/<id>/', methods = ['GET', 'POST'])
def delete(id):
record_data = records.query.get(id)
db.session.delete(record_data)
db.session.commit()
flash("Record Deleted Successfully!")
return redirect(url_for('Index'))
@app.route('/visualization')
def visualization():
all_data = records.query.all()
categories, series_data = graph_data()
return render_template('visualization.html', categories=categories, series_data=series_data)
def graph_data():
connection = pymysql.connect(host='localhost',
user='root',
password='root',
db='application')
cursor=connection.cursor()
df = pd.read_sql('SELECT * FROM records', con=connection)
categories = list(sorted(set(df['year'])))
series_data = []
grouped_by_quarter = df.groupby('quarter')
for quarter,quarter_data in grouped_by_quarter:
item={}
item['name']=quarter
item['data']=list(quarter_data['product_1_amount'])
item['stack']='product1'
series_data.append(item)
item={}
item['name']=quarter
item['data']=list(quarter_data['product_2_amount'])
item['stack']='product2'
series_data.append(item)
item={}
item['name']=quarter
item['data']=list(quarter_data['product_3_amount'])
item['stack']='product3'
series_data.append(item)
return categories, series_data
if __name__ == "__main__":
app.run(debug = True)