def query_create_feedback(userId: Union[int, str], movieId: Union[int, str], rating: Union[int, str]):
"""
Create a new feedback row for a movie from a user
:param Union[int, str] userId: The user id to retrieve
:param Union[int, str] movieId: The movie id to retrieve
:param Union[int, str] rating: The rating to apply to the movie
:return: JSON object of feedback id
"""
con, cursor = db_connection()
try:
cursor.execute(f"INSERT INTO movie_feedback (rating, movie_id, user_id) VALUES ({rating}, {movieId}, {userId})")
if cursor.rowcount == 1:
con.commit()
data = {
"id": cursor.lastrowid
}
return data
else:
con.rollback()
return None
finally:
cursor.close()
con.close()
def query_get_tag(id: Union[int, str]):
"""
Returns a single tag by tag id
:param Union[int, str] id: The tag id to retrieve
:return: JSON object with tag id, name, and movie id
"""
con, cursor = db_connection()
try:
cursor.execute("SELECT name, movie_id FROM tags WHERE id=%s", (id,))
result = cursor.fetchmany(size=1)
if len(result) != 1:
return None
else:
name = result[0][0]
movie_id = result[0][1]
data = {
"id": id,
"name": name,
"movie_id": movie_id
}
return data
finally:
con.close()
cursor.close()
def query_create_feedback_tag(userId: Union[int, str], movieId: Union[int,
str],
tagId: Union[int, str], rating: Union[int, str]):
"""
Create a new feedback row for a movie's tag from a user
:param Union[int, str] userId: The user id to retrieve
:param Union[int, str] movieId: The movie id to retrieve
:param Union[int, str] tagId: The tag id to retrieve
:param Union[int, str] rating: The rating to apply to the tag
:return: JSON object of feedback id
"""
con, cursor = db_connection()
try:
cursor.execute(
"INSERT INTO tag_feedback (rating, movie_id, user_id, tag_id) VALUES ({r}, {m}, {u}, {t})"
.format(r=rating, m=movieId, u=userId, t=tagId))
if cursor.rowcount == 1:
con.commit()
data = {"id": cursor.lastrowid}
return data
else:
con.rollback()
return None
finally:
cursor.close()
con.close()
def query_get_feedback_tags(userId: Union[int, str], movieId: Union[int, str]):
"""
Return the user’s feedback on tags of a specific movie
:param Union[int, str] userId: The user id to retrieve
:param Union[int, str] movieId: The movie id to retrieve
:return: JSON object of feedbacks array containing movie id, tag id, and rating
"""
con, cursor = db_connection()
try:
cursor.execute(
"SELECT movie_feedback.movie_id, tag_feedback.tag_id, tag_feedback.rating FROM movie_feedback JOIN tag_feedback ON movie_feedback.movie_id = tag_feedback.movie_id WHERE movie_feedback.user_id={u} AND movie_feedback.movie_id={m}"
.format(u=userId, m=movieId))
result = cursor.fetchall()
if cursor.rowcount > 0:
data = {}
feedbacks = []
for row in result:
feedbacks.append({
"movie_id": row[0],
"tag_id": row[1],
"rating": row[2]
})
data.update({"feedbacks": feedbacks})
return data
else:
return None
finally:
cursor.close()
con.close()
def authenticate(email: str, token: str) -> Union[Dict, None]:
"""
Validate the user has the ability to login with the given token.
:param str email: Email address to attempt to validate against
:param str token: OAuth token to attempt to validate
:return: Authorization status of the request.
"""
from server import app
con, cursor = db_connection()
try:
token_first_name, token_last_name, token_email, token_expiration = _fetch_token_info(
token)
assert email == token_email
verify_user(token_first_name, token_last_name, token_email)
cursor.execute("SELECT id, isAdmin FROM users WHERE email=%s",
(token_email, ))
result = cursor.fetchmany(size=1)
user = {
'id': result[0][0],
'firstName': token_first_name,
'lastName': token_last_name,
'isAdmin': result[0][1] == 1,
'email': token_email,
'expiration': token_expiration
}
session['user'] = user
app.open_session(request)
print(
f"Login success: {user['lastName']}, {user['firstName']} ({user['email']})"
)
return user
except (AssertionError, ValueError):
print(
f'Warning: Invalid login attempt for {email} from {request.remote_addr}.'
)
return None
finally:
print('*' * 50)
cursor.close()
con.close()
def query_get_movie(id: Union[int, str]):
"""
Returns a single movie by movie id
:param Union[int, str] id: The movie id to retrieve
:return: JSON object with movie id, title, tags, and genres
"""
con, cursor = db_connection()
try:
cursor.execute(
'''SELECT distinct movies.id, movies.name, group_concat(DISTINCT tags.name ORDER BY tags.name ASC SEPARATOR ',') as 'Tags', '''
'''group_concat(DISTINCT genre.genre ORDER BY genre.genre ASC SEPARATOR ',') as 'Genres' FROM FlickPick.movies '''
'''LEFT JOIN tags ON tags.movie_id = movies.id '''
'''LEFT JOIN genre ON genre.movie_id = movies.id '''
'''WHERE movies.id = %s;''', (id, ))
result = cursor.fetchall()
if (len(result)) == 0:
return None
else:
movie_id = result[0][0]
movie_name = result[0][1]
if result[0][2] is None:
tags = [[]]
else:
tags = [tags[2].split(',') for tags in result]
if result[0][3] is None:
genres = [[]]
else:
genres = [genres[3].split(',') for genres in result]
data = {
"id": movie_id,
"title": movie_name,
"tags": tags[0],
"genres": genres[0]
}
return data
finally:
con.close()
cursor.close()
def verify_user(firstName: str, lastName: str, email: str):
"""
Check if the user signing in is a user in the database. If not,
an entry is created in the user table.
:param str firstName: First name of the user signing in
:param str lastName: Last name of the user signing in
:param str email: Email address of the user signing in
:return: Authorization status of the request.
"""
con, cursor = db_connection()
try:
cursor.execute("SELECT * FROM users WHERE email=%s", (email, ))
result = cursor.fetchmany(size=1)
if len(result) == 1:
return {
'id': result[0][0],
'firstName': result[0][1],
'lastName': result[0][2],
'email': result[0][3],
'isAdmin': result[0][4] == 1,
}
else:
cursor.execute(
"INSERT INTO users (firstName, lastName, email, isAdmin) VALUES (%s, %s, %s, 0)",
(firstName, lastName, email))
con.commit()
return {
'id': cursor.lastrowid,
'firstName': firstName,
'lastName': lastName,
'email': email,
'isAdmin': False,
}
except Exception as e:
print(f'Exception in verify_user')
print(e)
return None
finally:
cursor.close()
con.close()
def get_movie_autocomplete(name: str):
"""
Get a list of auto-complete suggestions for a partial movie title
:param str name: The movie title to find suggestions for
:return: JSON object of movies array containing movie id, title, tags and genres
"""
con, cursor = db_connection()
name = unquote(name)
try:
if not is_user():
return Response(json.dumps({}), mimetype='application/json', status=403)
else:
cursor.execute(
f"SELECT movies.id, movies.name, group_concat(DISTINCT tags.name ORDER BY tags.name ASC SEPARATOR ',') as 'Tags',"
f"group_concat(DISTINCT genre.genre ORDER BY genre.genre ASC SEPARATOR ',') as 'Genres' FROM FlickPick.movies "
f"LEFT JOIN tags ON tags.movie_id = movies.id "
f"LEFT JOIN genre ON genre.movie_id = movies.id "
f"WHERE movies.name LIKE '{name}__%'"
f"GROUP BY movies.id ORDER BY movies.id ASC")
result = cursor.fetchall()
if len(result) == 0:
return []
else:
distances = [(distance(a[1], name), a[0], a[1], a[2], a[3]) for a in result]
titles = list(
map(lambda movie: {'id': movie[1], 'title': movie[2], 'tags': movie[3], 'genres': movie[4]},
sorted(distances)))[:10]
for x in titles:
x['tags'] = [] if x['tags'] is None else x['tags'].split(',')
x['genres'] = [] if x['genres'] is None else x['genres'].split(',')
return Response(json.dumps(titles), mimetype='application/json', status=200)
except Exception:
return Response(json.dumps({}), mimetype='application/json', status=500)
finally:
cursor.close()
con.close()
def query_update_feedback(feedbackId: Union[int, str], rating: Union[float, str]):
"""
Replace a user's feedback of a specific movie
:param Union[int, str] feedbackId: The feedback id to retrieve
:param Union[int, str] rating: The rating to apply to the movie
:return: Nothing
"""
con, cursor = db_connection()
try:
cursor.execute("UPDATE movie_feedback SET rating={r} WHERE id={f}".format(r = rating, f = feedbackId))
if cursor.rowcount == 1:
con.commit()
return True
else:
con.rollback()
return False
finally:
cursor.close()
con.close()
def get_tag_autocomplete(name: str, movieId: int):
"""
Get a list of auto-complete suggestions for a partial tag. The same tag may exist across multiple movies,
this method does not return every instance of a tag, only unique tags
:param str name: The tag name to find suggestions for
:param int movieId: The movie id to retrieve
:return: JSON object of tags array containing tag name
"""
con, cursor = db_connection()
result_set = {}
name = unquote(name)
try:
if not is_user():
return Response(json.dumps({}), mimetype='application/json', status=403)
else:
cursor.execute(f"SELECT id, name FROM tags WHERE movie_id=%s AND name LIKE '{name}__%'", (movieId,))
result = cursor.fetchall()
for a in result: # creates a unique set from result list
result_set[a[1]] = a
result_set = list(result_set.values()) # turns set back to a list
if len(result_set) == 0:
return []
else:
distances = [(distance(a[1], name), a[1], a[0]) for a in result_set]
tag = list(map(lambda movie: {'id': movie[2], 'tag': movie[1]}, sorted(distances)))[:10]
return Response({
"tags": tag
}, mimetype='application/json', status=200)
except Exception:
return Response(json.dumps({}), mimetype='application/json', status=500)
finally:
cursor.close()
con.close()
def query_get_feedback(userId: int, movieId: int):
"""
Return the user’s feedback of the specified movie id
:param Union[int, str] userId: The user id to retrieve
:param Union[int, str] movieId: The movie id to retrieve
:return: JSON object of movie id and rating
"""
con, cursor = db_connection()
try:
cursor.execute(
"SELECT rating FROM movie_feedback WHERE user_id={u} AND movie_id={m}"
.format(u=userId, m=movieId))
result = cursor.fetchone()
if cursor.rowcount == 1:
data = {"id": movieId, "rating": result[0]}
return data
else:
return None
finally:
cursor.close()
con.close()
def del_feedback(userId: int):
"""
Delete all user feedback for tags and movies
:param int userId: The user whose feedback is being deleted
:return: Nothing
"""
con, cursor = db_connection()
try:
if not isinstance(userId, int):
return Response(json.dumps({}),
mimetype='application/json',
status=400)
elif not is_current_user(userId):
return Response(json.dumps({}),
mimetype='application/json',
status=401)
else:
cursor.execute(
"DELETE m.*, t.* FROM movie_feedback m LEFT JOIN tag_feedback t ON m.user_id = t.user_id "
"WHERE m.user_id = %s", (userId, ))
if cursor.rowcount == 0:
return Response(json.dumps({}),
mimetype='application/json',
status=404)
else:
con.commit()
return Response(json.dumps({}),
mimetype='application/json',
status=200)
except Exception:
return Response(json.dumps({}),
mimetype='application/json',
status=500)
finally:
cursor.close()
con.close()
def del_user(id: int):
"""
Delete a user by id
:param int id: The user id to delete
:return: Nothing
"""
if not isinstance(id, int):
return Response(json.dumps({}),
mimetype='application/json',
status=400)
con, cursor = db_connection()
try:
if not (is_admin() or is_current_user(id)):
return Response(json.dumps({}),
mimetype='application/json',
status=401)
else:
cursor.execute("DELETE FROM users WHERE id=%s", (id, ))
if cursor.rowcount == 1:
con.commit()
return Response(json.dumps({}),
mimetype='application/json',
status=200)
else:
return Response(json.dumps({}),
mimetype='application/json',
status=404)
except Exception as e:
print(f'Error in del_user')
print(e)
return Response(json.dumps({}),
mimetype='application/json',
status=500)
finally:
cursor.close()
con.close()
def check_update(firstName: str, lastName: str, email: str):
"""
Check if the user signing in has updated personal details and update values
in the database if need be.
NOTE: Only firstName and lastName can be updated
:param str firstName: First name of the user signing in
:param str lastName: Last name of the user signing in
:param str email: Email address of the user signing in
:return: Authorization status of the request.
"""
con, cursor = db_connection()
try:
cursor.execute("SELECT firstName, lastName FROM users WHERE email=%s",
(email, ))
result = cursor.fetchmany(size=1)
if result[0][0] != firstName:
cursor.execute("UPDATE users SET firstName=%s WHERE email=%s", (
firstName,
email,
))
if result[0][1] != lastName:
cursor.execute("UPDATE users SET lastName=%s WHERE email=%s", (
lastName,
email,
))
con.commit()
except Exception as e:
print(f"Error in check_update")
print(e)
finally:
cursor.close()
con.close()
def get_recommendations(user_id: int):
"""
Retrieves the top 10 best movies to fit a user's current taste.
:param int user_id: The user to make a recommendation for
:return: A list of recommended movies and all of their data
"""
if not (is_admin() or is_current_user(user_id)):
return Response(json.dumps({}),
mimetype='application/json',
status=401)
else:
connection, cursor = db_connection()
limit = 10
def magnitude(a: np.ndarray):
return np.linalg.norm(a)
def similarity(a: np.ndarray, b: np.ndarray):
return np.dot(a, b) / (magnitude(a) * magnitude(b))
with open(f"model/mf/{model_name}_d.mat.pickle", 'rb') as file:
ratings_mat = pickle.loads(file.read())
predict_mat = np.load(f"model/mf/{model_name}_r.npy")
# TODO: Slow. Should be loaded from disk.
cursor.execute("SELECT MAX(movie_id) FROM movie_feedback;")
model_size = cursor.fetchone()[0]
cursor.execute(
f"SELECT movie_id-1 FROM movie_feedback WHERE user_id={user_id};")
user_ratings = cursor.fetchall()
user_ratings = np.array(user_ratings).flat
user_ratings_dense = np.zeros(model_size)
np.put(user_ratings_dense, user_ratings, [1])
user_ratings = set([int(rating) + 1 for rating in user_ratings])
# Calculate which users are the most similar to the requested user
similarities = [(idx, similarity(user_ratings_dense,
row.todense().flat))
for idx, row in enumerate(ratings_mat)]
similarities = sorted(similarities, key=lambda x: x[1], reverse=True)
# Calculate the candidates based on the top 5 most similar users
# Some movies will overlap, we'll weight those stronger
candidates = np.zeros_like(user_ratings_dense)
for sim_id, sim_val in similarities[:5]:
candidates += ratings_mat[sim_id -
1].todense().flat * predict_mat[sim_id -
1]
candidates = list(
filter(
lambda x: x not in user_ratings,
map(
lambda x: x[0],
sorted(enumerate(candidates),
key=lambda x: x[1],
reverse=True))))
candidates = list(map(query_get_movie, candidates[:limit]))
return json.dumps(candidates)
