-
Notifications
You must be signed in to change notification settings - Fork 0
/
routes.py
181 lines (161 loc) · 8.23 KB
/
routes.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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
# -*- coding: utf-8 -*-
"""
Created on Sat Oct 1 16:22:03 2016
@author: libbyaiello
"""
from flask import Flask, render_template, request
app = Flask(__name__)
import sqlite3
conn = sqlite3.connect('schoolratings')
c1 = conn.cursor()
'''Functions that interface with database'''
#don't call this unless you want to reset all the databases!!
def set_up():
c1.execute('DROP TABLE t1_schools;')
c1.execute('DROP TABLE t2_ratings;')
c1.execute('CREATE TABLE t1_schools (school_id INTEGER PRIMARY KEY AUTOINCREMENT, school_name TEXT);')
c1.execute('CREATE TABLE t2_ratings (rating_id INTEGER PRIMARY KEY AUTOINCREMENT, school_id, overall INTEGER, physical INTEGER, academic INTEGER, resources INTEGER, rating TEXT, FOREIGN KEY (school_id) REFERENCES t1_schools(school_id));')
def number_of_schools():
for item in c1.execute('SELECT COUNT(*) FROM t1_schools'):
return item[0]
return 0
# For the schoolname inputted by the user, this function calculates the average ratings for each category for this school
def get_avg_ratings(schoolname):
schoolname = schoolname.lower()
school_id = 0
command = 'SELECT school_id FROM t1_schools WHERE school_name = \"'+schoolname+'\";'
for schooldata in c1.execute(command):
school_id = schooldata[0]
if school_id == 0:
return []
count = 0.0
overall_total = 0
physical_total = 0
academic_total = 0
resources_total = 0
ratings = {}
command = 'SELECT overall, physical, academic, resources, rating_id, rating FROM t2_ratings WHERE school_id = '+str(school_id)+';'
for data in c1.execute(command):
count += 1
overall_total += data[0]
physical_total += data[1]
academic_total += data[2]
resources_total += data[3]
rating_id = data[4]
rating = data[5]
ratings[rating_id] = rating
if count == 0:
return []
overall_average = overall_total/count
physical_average = physical_total/count
academic_average = academic_total/count
resources_average = resources_total/count
return [overall_average, physical_average, academic_average, resources_average, ratings]
def get_ratings(schoolname):
schoolname = schoolname.lower()
command = 'SELECT school_id FROM t1_schools WHERE school_name = \"'+schoolname+'\";'
school_id = 0
for schooldata in c1.execute(command):
school_id = schooldata[0]
if school_id == 0:
return []
ratings = []
command = 'SELECT overall, physical, academic, resources, rating_id, rating FROM t2_ratings WHERE school_id = '+str(school_id)+';'
for data in c1.execute(command):
ratings.append(data)
return ratings
categories = {'overall':0, 'physical':1, 'academic':2, 'resources':3}
categories_reverse = {0:'overall', 1:'physical', 2:'academic', 3:'resources'}
#this function takes in a category to sort by and returns a list of schools in descending order of their rankings in this category
def get_rankings(category):
category = category.lower()
rankings = []
#get all of the rankings
for schooldata in c1.execute('SELECT school_name FROM t1_schools;'):
schoolname = schooldata[0]
rankings.append((schoolname, get_avg_ratings(schoolname)[categories[category]]))
#use lambda function to sort in descending order by x[1]
return sorted(rankings, key = lambda x: x[1], reverse = True)
# Interacts with the gui to allow users to input ratings for a school
def add_rating(schoolname, overall, physical, academic, resources, rating):
schoolname = schoolname.lower()
school_id = 0
#if the school already exists in t1_schools, pull up it's school_id
for schooldata in c1.execute('SELECT school_id FROM t1_schools WHERE school_name = "'+schoolname+'";'):
school_id = schooldata[0]
c2 = conn.cursor()
#if the school does not exist in the database, add it to t1_schools
if school_id == 0:
command = 'INSERT INTO t1_schools (school_name) VALUES (\"'+schoolname+'\")'
c2.execute(command)
for schooldata in c1.execute('SELECT school_id FROM t1_schools WHERE school_name = "'+schoolname+'";'):
school_id = schooldata[0]
#add this rating into t2_ratings
c1.execute('INSERT INTO "t2_ratings" (school_id, overall, physical, academic, resources, rating) VALUES (?,?,?,?,?,?)',
(school_id, overall, physical, academic, resources, rating))
c2.close()
def get_random_school():
rankings = get_rankings('physical')
import random
rand = random.randint(0,len(rankings))
return rankings[rand]
'''Now interfacing with javascript'''
'''Lists of pages:
-- '/' = home
-- '/search/<query>/' = search result page --> query might pull up multiple schools (if not finished, or might say not found, or forwards to schoolname page)
-- '/<schoolname>' = school homepage --> shows averages of ratings on top, and all ratings below
-- '/bestSchoolsFor/<page>' = list of rankings of schools for specific category (page is an integer 0 - 3)
-- '/schoolReviewForm/' = page where you can fill out a review for a school.
'''
@app.route('/')
def home():
#get_random_school returns (school_name, [overall_average, physical_average, academic_average, resources_average, ratings])
featured_school = get_random_school()
school_name = featured_school[0]
overall_rating = featured_school[1][0]
physical_rating = featured_school[1][1]
academic_rating = featured_school[1][2]
resources_rating = featured_school[1][3]
num_of_schools = number_of_schools()
return render_template('home.html', num_of_schools = num_of_schools, school_name = school_name, overall_rating = overall_rating,
physical_rating = physical_rating, academic_rating = academic_rating, resources_rating = resources_rating)
#home.html should use variables num_of_schools, school_name, overall_rating, physical_rating, academic_rating, resources_rating
@app.route('/search/<query>/', methods=['POST'])
def Search(query):
if request.method == 'POST':
return School(query) #for now, until we can implement more advanced searching, such as finding schools that contain query string
@app.route('/<schoolname>/')
def School(schoolname):
schoolname = schoolname.lower()
#should return all reviews, and have a header with the average reviews.
avg_ratings = get_avg_ratings(schoolname) #could be [] if there were no reviews/if this school has not been reviewed yet.
all_ratings = get_ratings(schoolname) #could be [] if there were no reviews/if this school has not been reviewed yet.
return render_template('school_info.html', schoolname, avg_ratings, all_ratings) #avg_ratings/all_ratings good have size 0 if no reviews yet
#school_info.html should use variables schoolname, avg_ratings, all_ratings
@app.route('/bestSchoolsFor/<page>')
def bestSchoolsFor(page):
category = categories_reverse[page]
rankings = get_rankings(category) # formatted as schoolname, value for specific category
return render_template('bestSchoolsFor.html', category, rankings) #category name should go on the top of the page, rankings should be displayed in table (schoolname is a link to school page)
#bestSchoolsFor.html should use variables category (to put on top of page), rankings (pairs of college name and score)
@app.route('/schoolReviewForm/', methods=['GET', 'POST']) #really confused as to what 'GET' and 'POST' are doing
def SchoolReview():
if request.method == 'POST': #they submitted something
#get info from form
schoolname = request.form['schoolname']
overall = request.form['overall']
physical = request.form['physical']
academic = request.form['academic']
resources = request.form['resources']
rating = request.form['rating']
#add to database!
add_rating(schoolname, overall, physical, academic, resources, rating)
return render_template('PostSubmissionForm.html', test=' ') #says thank you for your submission
#for getting school review
return School(schoolname) #return as if you searched for it. (do we ever get here?)
if __name__ == '__main__':
app.run()
# commits (updates the database) then closes the connection
c1.close()
conn.commit()
conn.close()