-
Notifications
You must be signed in to change notification settings - Fork 0
/
server.py
407 lines (347 loc) · 12.1 KB
/
server.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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from bottle import Bottle, route, run, template, get, post, debug, static_file, request, redirect, response
import time
import random
import string
import logging
import logging.handlers
import sqlite3
import hashlib
app = Bottle()
# Connecting to database
connection = sqlite3.connect("data/main_database.db")
c = connection.cursor()
# Loading static files
@app.route('/assets/:path#.+#', name='assets')
def static(path):
return static_file(path, root='./assets')
@app.route('/')
def index():
# Select all genres
c.execute("SELECT DISTINCT genres FROM movie_database_c")
genres = c.fetchall()
adults = ['True', 'False']
output = template('templates/index', genres=genres, adults=adults, title= "Main Page")
return output
@app.route('/my-movies')
def mymovies():
# Check if user wants to add or delete from watchlist
addWishlist = request.query.add_wishlist
removeWishlist = request.query.remove_wishlist
# Check if user is logged in
username_cookie = request.get_cookie("account", secret="guacamole")
# If user is not logged in redirect to sign in
if username_cookie == None:
redirect('/sign-in')
if addWishlist != "":
# Select the movie ID
c.execute("SELECT id FROM movie_database_c WHERE title = (?)", (addWishlist,))
movie_ID = c.fetchone()
# Select the user ID
c.execute("SELECT user_id FROM users WHERE username = (?)", (username_cookie,))
user_ID = c.fetchone()
# Check if movie is already added
c.execute("SELECT Wishlist_ID FROM Wishlist WHERE Movie_ID = (?) AND User_ID = (?)", (movie_ID[0],user_ID[0]))
Wishlist_ID = c.fetchone()
if Wishlist_ID == None:
c.execute("INSERT INTO Wishlist VALUES (?,?,?)", (None,int(movie_ID[0]),int(user_ID[0])))
connection.commit()
if removeWishlist != "":
# Select the movie ID
c.execute("SELECT id FROM movie_database_c WHERE title = (?)", (removeWishlist,))
movie_ID = c.fetchone()
if movie_ID != None:
c.execute("DELETE FROM Wishlist WHERE Movie_ID = (?)", (movie_ID[0],))
connection.commit()
# Select data for the table
c.execute('''
SELECT DISTINCT
m.title,
m.genres,
m.vote_average,
m.vote_count,
m.release_date,
m.adult
FROM
movie_database_c AS m,
Wishlist AS w,
users AS u
JOIN
Wishlist movie_database_c
ON w.Movie_ID = m.id
JOIN
Wishlist users
ON w.USER_ID = u.user_id
WHERE u.username = (?)''', (username_cookie,))
result = c.fetchall()
output = template('templates/my_watchlist', rows=result)
return output
@app.route('/list-of-movies')
def listofmovies():
# Select 5000 movie from database
# We have more, but the page would load very slowly
# Other queries have acess to the whole database
c.execute('''
SELECT
title,
genres,
vote_average,
vote_count,
release_date,
adult
FROM
movie_database_c
LIMIT 5000''')
result = c.fetchall()
output = template('templates/movie_table', rows=result, title= "All Movies")
return output
@app.route('/top-1000')
def top1000():
# Select top 1000 movies by rating, discard thoes with vote count < 500
c.execute('''
SELECT
title,
genres,
vote_average,
vote_count,
release_date,
adult
FROM
movie_database_c
WHERE
vote_count >500
ORDER BY
vote_average DESC
LIMIT 1000''')
result = c.fetchall()
output = template('templates/movie_table', rows=result, title= "Top 1000 Movies")
return output
@app.route('/top-100-by-year')
def top100y():
# Check the get parameter, if null show a list of all possible year periods
release_year = request.query.year
if release_year == "":
result = ["30","40","50","60","70","80","90","00","10"]
output = template('templates/years_main_page', rows=result)
return output
# If the year is returned (eg. as "80"), return a table of movies
else:
# Special case for 2000 and 2010
if release_year == "00" or release_year =="10":
release_year = "20"+release_year
else:
release_year = "19"+release_year
# Select movies between release_date, and release_date + 10
c.execute('''
SELECT
title,
genres,
vote_average,
vote_count,
release_date,
adult
FROM
movie_database_c
WHERE
CAST(SUBSTR(release_date, 1, 4) AS int) >= (?)
AND
CAST(SUBSTR(release_date, 1, 4) AS int) < (?)
AND vote_count > 100
ORDER BY
vote_average DESC
LIMIT 100''', (int(release_year), (int(release_year)+ 10)))
result = c.fetchall()
output = template('templates/movie_table', rows=result, title= "Top 100 Movies from " + release_year + " to " + str(int(release_year)+ 9))
return output
@app.route('/top-100-by-genre')
def top100g():
# If there in no genre parameter, return all genres
genre = request.query.genre
if genre == "":
c.execute("SELECT DISTINCT genres FROM movie_database_c")
query = c.fetchall()
# Convert from a list of touples to a list of strings, and sort
result = []
for tup in query:
result.append(tup[0])
result.sort()
output = template('templates/genres_main_page', rows=result)
return output
else:
# If the genre is provided, return a table
c.execute('''
SELECT
title,
genres,
vote_average,
vote_count,
release_date,
adult
FROM
movie_database_c
WHERE
genres = (?)
AND
vote_count > 1000
ORDER BY
vote_average DESC
LIMIT 100''', (genre,))
result = c.fetchall()
output = template('templates/movie_table', rows=result, title= "Top 100 Movies by Genre")
return output
@app.route('/search')
def search():
# Check all possible url parameters
title = request.query.title
genre = request.query.genre
adult = request.query.adult
rating = request.query.rating
date = request.query.date
# Set whole ranges when date and/or rating parameter is not provided
# When they are provided, set the end values
if date=="":
date = 1900
date_end = 2020
else:
date_end = int(date)+20
if rating=="":
rating = 0
rating_end = 10
else:
rating_end = int(rating)+2
# Select movies based on the criteria
c.execute('''
SELECT
title,
genres,
vote_average,
vote_count,
release_date,
adult
FROM
movie_database_c
WHERE
title LIKE '%' || (?) || '%'
AND
genres LIKE '%' || (?) || '%'
AND
adult LIKE '%' || (?) || '%'
AND
CAST(SUBSTR(release_date,1,4) as int)
BETWEEN
(?) AND (?)
AND
CAST(SUBSTR(vote_average,1) as int)
BETWEEN
(?) AND (?)
LIMIT 1000''',(title,genre,adult,int(date),int(date_end),int(rating),int(rating_end)))
result = c.fetchall()
output = template('templates/movie_table', rows=result, title= "Search")
return output
@app.route('/movie')
def single_movie():
# Check if the movie parameter was provided. If not, redirect to homepage
movieTitle = request.query.movie_title
if movieTitle == "":
redirect('/')
#To-do: size of letters
c.execute('''
SELECT
poster_path,
tagline,
title,
overview,
revenue,
budget,
vote_average,
vote_count,
release_date,
genres,
runtime,
[status]
FROM
movie_database_c
WHERE
title = (?)
LIMIT 1''', (movieTitle, ))
result = c.fetchone()
output = template('templates/single_movie', data = result)
return output
@app.route('/user-profile')
def user():
return template('templates/pages-user-profile')
@app.route('/sign-up')
def signup():
return template('templates/sign_up', warning='')
@app.route('/sign-in')
def signin():
# If user requested a logout, delete the cookie, and redirect to homepage
if request.query.logout == "true":
response.delete_cookie("account")
redirect('/')
return template('templates/sign_in', warning='')
################SIGNING IN################
@app.route('/sign-in', method='POST')
def do_login():
# Get values from all the fields, strip leading and trailing spaces
username = request.forms.get('username').strip()
password = request.forms.get('pwd')
# Check login
check = check_login(username,password)
# If login sucessful, return to home
if check == 'ok':
redirect('/')
# If check is unsucessful, reload and display a message
else:
return template('templates/sign_in', warning=check)
def check_login(username,password):
# Login is "admin", password is "password"
if username == "" or password == "":
return 'Please fill all fields to log in!'
# Select password_hash for this user (case-insensitive)
c.execute("SELECT password_hash FROM users WHERE username =?", (username.lower(),))
query = c.fetchone()
# If user does not exist
if query==None:
return 'User does not exist!'
# Hash the provided password
passwordHash = hashlib.md5(password.encode("utf8")).hexdigest()
# If the hashes do not match, return a warning
if passwordHash != query[0]:
return 'Wrong password!'
# If the hashes match, set cookie and return ok
response.set_cookie("account", username, secret="guacamole")
return 'ok'
################SIGNING UP################
@app.route('/sign-up', method='POST')
def registerUser():
# Get values from all the fields, strip leading and trailing spaces
username = request.forms.get('name').strip()
email = request.forms.get('email').strip()
password = request.forms.get('pwd')
password_confirm = request.forms.get('pwd_confirm')
# Return a warning if all fields are not filled
if username == "" or email == "" or password == "":
return template('templates/sign_up', warning='Please fill all the fields to register!')
# Check if passwords match.
# Should be done with javascript on client side, but this is a python project.
# Also the hashing should be client side
if password_confirm != password:
return template('templates/sign_up', warning='Passwords do not match!')
# Check if user exists (case-insensitive)
c.execute("SELECT user_id FROM users WHERE username = (?)", (username.lower(),))
user_ID = c.fetchone()
# If user exists, return a warning
if user_ID != None:
return template('templates/sign_up', warning='User already exists!')
# Hash the password (md5)
passwordHash = hashlib.md5(password.encode("utf8")).hexdigest()
# Insert in the database
c.execute("INSERT INTO users VALUES (?,?,?,?,?)", (None,username,passwordHash,email,None))
# Commit the changes
connection.commit()
# Log the user in and redirect to homepage
response.set_cookie("account", username, secret="guacamole")
redirect('/')
app.run(host='localhost', port=8585, debug=True, reloader=True)