def load_rating_movie_data_from_db():
user_ratings_query = "SELECT USER_ID, MOVIE_ID, RATING FROM user_ratings ORDER BY USER_ID;"
movies_query = "SELECT MOVIE_ID, MOVIE_TITLE, GENRE FROM movies;"
user_ratings = run_query(user_ratings_query, FetchType.FETCH_ALL)
movies = run_query(movies_query, FetchType.FETCH_ALL)
ratings_df = pd.DataFrame(user_ratings,
columns=['user_id', 'movie_id', 'rating'])
movies_df = pd.DataFrame(movies,
columns=['movie_id', 'movie_title', 'genre'])
return ratings_df, movies_df
def get(self):
cur_path = os.path.dirname(__file__)
sql_file = os.path.abspath(
os.path.join(cur_path, "..", "..", sqlfile_path.MOVIES_COUNT))
for line in open(sql_file):
result = run_query(line, FetchType.FETCH_ONE)
return {"totalMovies": result}
def get(self, movie_name):
search_query = "SELECT movie_title FROM movies WHERE movie_title LIKE '%{}%' LIMIT 10".format(
movie_name)
search_result = run_query(search_query, fetch_type=FetchType.FETCH_ALL)
final_list = []
for movies in search_result:
final_list.append(movies[0])
return final_list
def limited_load_rating_movie_data_from_db():
limited_movies = (242, 302, 377, 51, 346, 474, 265, 465, 451, 86, 257,
1014, 222, 40, 29, 785, 387, 274, 1042, 1184, 392, 486,
144, 118, 1, 546, 95, 768, 277, 234, 246, 98, 193, 88,
194, 1081, 603, 796, 32, 16, 304, 979, 564, 327, 201,
1137, 241, 4, 332, 100)
user_ratings_query = "SELECT USER_ID, MOVIE_ID, RATING FROM user_ratings WHERE movie_id in {} ORDER BY USER_ID;".format(
limited_movies)
movies_query = "SELECT MOVIE_ID, MOVIE_TITLE, GENRE FROM movies WHERE movie_id in {} ;".format(
limited_movies)
user_ratings = run_query(user_ratings_query, FetchType.FETCH_ALL)
movies = run_query(movies_query, FetchType.FETCH_ALL)
ratings_df = pd.DataFrame(user_ratings,
columns=['user_id', 'movie_id', 'value'])
ratings_df['movie_id'] = ratings_df['movie_id'].astype(float)
ratings_df['value'] = ratings_df['value'].astype(float)
movies_df = pd.DataFrame(movies, columns=['movie_id', 'title', 'genre'])
movies_df['movie_id'] = movies_df['movie_id'].astype(float)
return ratings_df, movies_df
def gather_movies_from_db():
db, cursor = get_cursor()
movies_count_query = """SELECT MAX(ID) FROM movies"""
movies_count = run_query(movies_count_query, FetchType.FETCH_ONE)
with open('movie_info.csv', 'a+', newline='') as file:
for idx in range(1, movies_count):
movies_info_query = """SELECT imdb_url FROM movies WHERE movie_id={}""".format(
idx)
try:
movies_info = run_query(movies_info_query, FetchType.FETCH_ONE)
m = re.search('title/(.+?)/', movies_info)
if m:
imdb_url_id = m.group(1)
info = "{},{}".format(idx, imdb_url_id)
file.writelines(info)
except:
continue
db.close_db()
def get(self):
query = "SELECT genre FROM movies;"
result = run_query(query, fetch_type=FetchType.FETCH_ALL)
genre_list = []
for genre in result:
lst = genre[0].split(",")
for gen in lst:
if gen != '':
genre_list.append(gen)
genres = list(set(genre_list))
return {"genres": genres}
def post(self):
current_user = get_jwt_identity()
user_id = current_user['user_id']
try:
rec_type = request.json['type']
except KeyError:
msg = {"error": "'type' parameter is missing in request"}
return msg, 400
# movie provided by user to show recommendation
if rec_type == "movie":
try:
movie_name = request.json['movie']
except KeyError:
msg = {"error": "'movie' parameter is missing in request"}
return msg, 400
print("Searching recommendation for movie: {}".format(movie_name))
top_5_rec_movies = recommend_top_5(movie_name)
response = {
"recommend": top_5_rec_movies,
"message": "Please find your recommendation"
}
return response, 200
# gather user provided genre selections & create recommendation based on those
elif rec_type == "genre":
usr = UserModel.query.filter_by(user_id=user_id).first()
genres = usr.preferred_genres.split(",")
rec_movies = []
for genre in genres:
recommend_dict = {}
proc_args = (genre, )
top_rtd_mov_gnr = run_procedure(
'highest_rated_movies_for_genre', proc_args,
FetchType.FETCH_ONE)[0]
print(top_rtd_mov_gnr)
top_5_rec_movies = recommend_top_5(top_rtd_mov_gnr)
recommend_dict["genre"] = genre
recommend_dict["recMovies"] = top_5_rec_movies
rec_movies.append(recommend_dict)
response = {
"recommend": rec_movies,
"message": "Please find your recommendation based on Genre"
}
return response, 200
# get user rated movies & provide recommendation based on those
elif rec_type == "rated":
rated_mov_qry = """SELECT movie_title,movie_id FROM movies WHERE movie_id IN(
SELECT movie_id FROM user_ratings WHERE user_id={user_id} ORDER BY rating) LIMIT 5""".format(
user_id=user_id)
user_rtd_movies = run_query(rated_mov_qry, FetchType.FETCH_ALL)
rec_movies = []
for movie in user_rtd_movies:
recommend_dict = {}
mov = movie[0]
top_5_rec_movies = recommend_top_5(mov)
recommend_dict["rated_movie"] = movie
recommend_dict["recMovies"] = top_5_rec_movies
rec_movies.append(recommend_dict)
response = {
"recommend": rec_movies,
"message": "Please find your recommendation based on Rated"
}
return response, 200
else:
msg = {"error": "unsupported 'type' parameter in request"}
return msg, 400