-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.py
163 lines (110 loc) · 5.33 KB
/
db.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
from flask import Flask, redirect, url_for, request, render_template
from sqlalchemy import create_engine
app = Flask(__name__)
engine = create_engine("mysql://admin:password@localhost/postgradb?host=localhost?port=3306")
conn = engine.connect()
@app.route('/', methods=['GET'])
def root():
return redirect(url_for('home'))
@app.route('/home', methods=['GET'])
def home():
return render_template('home.html')
@app.route('/search_page', methods=['GET'])
def search_page():
return render_template('search.html')
@app.route('/search_uni',methods = ['POST', 'GET'])
def search_uni():
if request.method == 'POST':
uni = request.form['uni']
#domain = request.form['domain']
result = conn.execute("SELECT * FROM University WHERE universityID='" + uni +"'").fetchall()
#print(result)
if(len(result)==0):
return redirect(url_for('search_page'))
return redirect(url_for('universities', name=result[0].universityID))
else:
uni = request.args.get('uni')
domain = request.args.get('domain')
return redirect(url_for('universities',name = uni))
@app.route('/universities/<name>')
def universities(name):
#load data
uni = conn.execute("SELECT * FROM University WHERE universityID='" + name +"'").fetchall()
uni_programs= conn.execute("SELECT * FROM programs_by_unis WHERE universityID='" + name +"'").fetchall()
#parse uni data
uni_name = uni[0].name
uni_rank = uni[0].ranking
uni_city = uni[0].city
uni_country = uni[0].country
programs = [None] *len(uni_programs)
temp_ids = [None] * len(uni_programs)
ids = [None] * len(uni_programs)
#parse uni_programs data
for msc in range(len(uni_programs)):
programs[msc]=uni_programs[msc].Msc
for program_ids in range(len(uni_programs)):
join_dept_program="SELECT Program.name, Department.university_id, Program.programID FROM postgradb.Program join postgradb.Department on department_id= departmentID"
program_name_to_id="SELECT q1.programID FROM (%s)as q1 where q1.university_id='%s' and q1.name ='%s'" %(join_dept_program, name, programs[program_ids])
temp_ids[program_ids]= conn.execute(program_name_to_id).first()
#convet program ids (from sqlalchemy.row.proxy type) in str
for i in range(len(uni_programs)):
ids[i]=(temp_ids[i][0])
print(ids[0])
return render_template('university.html', uni_country=uni_country ,uni_name=uni_name, uni_rank=uni_rank, uni_city=uni_city, programs=programs, program_ids=ids )
@app.route('/search_domain',methods = ['POST', 'GET'])
def search_domain():
if request.method == 'POST':
domain = request.form['domain']
result = conn.execute("SELECT * FROM programs_by_unis WHERE Msc like'%%"+domain+"%%'").fetchall()
#result = conn.execute("SELECT * FROM University WHERE universityID='" + uni +"'").fetchall()
programs = [None] *len(result)
program_plus_uni=[None] *len(result)
uni_id=[None] *len(result)
temp_ids = [None] * len(result)
ids = [None] * len(result)
#parse uni_programs data
for msc in range(len(result)):
programs[msc]=result[msc].Msc
program_plus_uni[msc]=programs[msc]+" at "+ result[msc].University
uni_id[msc]=result[msc].universityID
for program_ids in range(len(result)):
join_dept_program="SELECT Program.name, Department.university_id, Program.programID FROM postgradb.Program join postgradb.Department on department_id= departmentID"
program_name_to_id="SELECT q1.programID FROM ("+join_dept_program+") as q1 where q1.university_id='"+uni_id[program_ids]+"' and q1.name like '%%"+domain+"%%'"
temp_ids[program_ids]= conn.execute(program_name_to_id).first()
for i in range(len(result)):
ids[i]=(temp_ids[i][0])
print(type(program_plus_uni))
#print(" !!!!!!!!!!!!!!!!!!!!!!! \n"+ result[0].Msc)
return render_template('domain_programs.html', programs=program_plus_uni, program_ids=ids )
#return redirect(url_for('domains', name=result[0].universityID))
@app.route('/motivation_page', methods=['GET'])
def motivation_page():
return render_template('motivation.html')
@app.route('/developers', methods=['GET'])
def developers():
return render_template('developers.html')
@app.route('/programs/<program>', methods=['GET'])
def programs(program):
program = conn.execute("SELECT * FROM Program WHERE programID='" + program +"'").fetchall()
#parse program data
program_name=program[0].name
duration=program[0].duration
num_of_students=program[0].number_of_students
deadline=program[0].applications_deadline
fees=program[0].fees
description=program[0].description
attendancy=program[0].attendancy
print(program_name)
#get Univeristy from Department table
join_dept_program="SELECT Department.university_id, Program.programID FROM postgradb.Program join postgradb.Department on department_id= departmentID"
query="select q1.university_id from (%s) as q1 where q1.programID='%s'" %(join_dept_program, program[0].programID)
program_university=conn.execute(query).first()
uni_id= program_university[0]
uni_query="SELECT name,city FROM postgradb.University where universityID='%s'; "%(uni_id)
uni=conn.execute(uni_query).first()
uni_name=uni[0]
uni_city=uni[1]
#print(program_name)
return render_template('program.html', prog_name=program_name, dur=duration, num_of_students=num_of_students, deadline=deadline , fees=fees, description=description , uni_name=uni_name, uni_city=uni_city, attendancy=attendancy)
if __name__ == '__main__':
app.run(debug = True)