def getRecommendationByName(name):
with open('recommendations.json') as json_data:
games = json.load(json_data)
games = games[name]
# print(games)
#details = pd.read_csv("2019-05-02.csv")
conn = create_connection('Database')
details = pd.read_sql_query("SELECT * FROM Details;", conn)
df = details[details['Name'].isin(games)] # top200
#top200 = top200[["Game_ID","Name"]]
#player_detail = pd.read_csv("games_detailed_info.csv")
#df = pd.merge(left=details,right=top200,left_on='Game_ID',right_on='Game_ID')
#df = df[["ID","Name","boardgamepublisher","boardgamecategory","minplayers","maxplayers","minplaytime","maxplaytime","minage","description","boardgameexpansion","boardgamemechanic","thumbnail","yearpublished"]]
categories = [
'Game_ID', 'Name', 'Publisher', 'Category', 'Min_players',
'Max_players', 'Min_playtime', 'Max_playtime', 'Min_age',
'Description', 'Expansion', 'Mechanic', 'Thumbnail', 'Year_Published'
]
df = df[categories]
df['Thumbnail'] = df['Thumbnail'].fillna(
'https://via.placeholder.com/150x150?text=No+Image')
# to make compatible with old legacy code
compat_categories = [
"ID", "Name", "boardgamepublisher", "boardgamecategory", "minplayers",
"maxplayers", "minplaytime", "maxplaytime", "minage", "description",
"boardgameexpansion", "boardgamemechanic", "thumbnail", "yearpublished"
]
df.columns = compat_categories
return df
def post(self):
review = request.json
for key in review:
if key not in review_model.keys():
return {"message": "Property {} is invalid".format(key)}, 400
conn = create_connection('Database')
df = pd.read_sql_query("SELECT Name FROM Details WHERE Game_ID = ?;",
conn,
params=[review['Game_ID']])
if len(df) == 0:
api.abort(404, "Game {} doesn't exist".format(review['Game_ID']))
if (review['Rating'] < 1 or review['Rating'] > 10):
api.abort(400, "Rating must be between 1 and 10")
Name = df.loc[0][0]
c = conn.cursor()
c.execute(
"INSERT INTO Reviews(User, Rating, Game_ID, Comment, Name) VALUES(?,?,?,?,?)",
(review['User'], review['Rating'], review['Game_ID'],
review['Comment'], Name))
last_row = c.lastrowid
conn.commit()
mm.increment('/reviews/POST {}'.format(review['Game_ID']))
mm.save()
return {
"message":
"Review for '{}' Game_ID={} has been added with Review_ID={}".
format(Name, review['Game_ID'], last_row)
}, 201
def get(self, id):
details = request.args
# print(details)
conn = create_connection('Database')
df = pd.read_sql_query("SELECT Name FROM Details WHERE Game_ID = ?;",
conn,
params=[id])
if len(df) == 0:
api.abort(404, "Game {} doesn't exist".format(id))
name = df.loc[0][0]
# Get reviews
# Open recommendations
if not os.path.exists('recommendations.json'):
api.abort(404, "Recommendations file doesn't exist")
# Get list of recommendations
try:
result = getRecommendationByName(name)
except KeyError as e:
print('KEY ERROR:', e)
api.abort(404, "Name was not found")
# print(result)
# Do series of filters
try:
result = filterRecommendations(details, result)
except:
api.abort(400, 'Bad Request')
mm.increment('/recommendations/{}'.format(id))
mm.save()
return get_dict_entries(result)
def get(self):
conn = create_connection('Database')
df = pd.read_sql_query(
"select Year_Published as Year, count(*) as Number_Published from Details group by Year_Published order by Year_Published;",
conn)
mm.increment('/trends/num_published')
mm.save()
return get_dict_entries(df)
def get(self):
conn = create_connection('Database')
df = pd.read_sql_query(
"SELECT * FROM Details WHERE Board_Game_Rank != 'null' AND Board_Game_Rank > 0 ORDER BY Board_Game_Rank LIMIT 10;",
conn)
mm.increment('/details/top10')
mm.save()
return get_dict_entries(df)
def get(self, name):
conn = create_connection('Database')
df = pd.read_sql_query("SELECT * FROM Details WHERE Name LIKE ?;",
conn,
params=['%' + name + '%'])
if len(df) == 0:
api.abort(404, "No Match Found - {}".format(name))
mm.increment('/board_games_details/{}'.format(name))
mm.save()
return get_dict_entries(df), 200
def get(self):
conn = create_connection('Database')
sql = '''
select *
from (select Game_ID, Name, Year_Published as Year from Details) natural join
(select Game_ID, avg(Rating) as Average, count(*) as Num_Reviews from Reviews group by Game_ID)
group by Year
having max(Average)
order by Year asc;'''
df = pd.read_sql_query(sql, conn)
mm.increment('/details/top_yearly')
mm.save()
return get_dict_entries(df)
def delete(self, id):
conn = create_connection('Database')
cur = conn.cursor()
df = pd.read_sql_query("SELECT * from Reviews", conn)
before = len(df)
cur.execute("delete from Reviews where Game_ID = ?", (id, ))
conn.commit()
df = pd.read_sql_query("SELECT * from Reviews", conn)
if (before == len(df)):
api.abort(404, "Reviews about game:{} doesn't exist".format(id))
mm.increment('/reviews/{}'.format(id))
mm.save()
return {"message": "Reviews about Game:{} is removed.".format(id)}, 200
def get(self, id):
conn = create_connection('Database')
df = pd.read_sql_query("SELECT Name FROM Details WHERE Game_ID = ?;",
conn,
params=[id])
if len(df) == 0:
api.abort(404, "Game {} doesn't exist".format(id))
df = pd.read_sql_query("SELECT * FROM Reviews WHERE Game_ID = ?;",
conn,
params=[id])
if len(df) == 0:
api.abort(404, "Game {} has no reviews".format(id))
mm.increment('/reviews/{}'.format(id))
mm.save()
return get_dict_entries(df), 200
def get(self, id):
conn = create_connection('Database')
df = pd.read_sql_query("SELECT * FROM Details WHERE Game_ID = ?;",
conn,
params=[id])
if len(df) == 0:
api.abort(404, "Game {} doesn't exist".format(id))
details = df.loc[0].to_json()
details = json.loads(details)
price = get_price(details['Name'], True) #####
if price: #####
details['URL'] = price[1] #####
details['Price'] = price[2] #####
mm.increment('/board_games_details/{}'.format(id))
mm.save()
return details, 200
def get(self, year):
try:
year = int(year)
except Exception:
api.abort(400, "Invalid year")
conn = create_connection('Database')
df = pd.read_sql_query(
"SELECT * FROM Details WHERE Year_Published = ? ORDER BY Year_Published;",
conn,
params=[year])
print(df.head())
if len(df) == 0:
api.abort(404, "No Games Published In Year {}".format(year))
mm.increment('/board_games_year/{}'.format(year))
mm.save()
return get_dict_entries(df), 200
def get(self):
details = request.args
print(details)
page_number = 1
page_elements = 20
if details is not None:
if 'Page' in details:
page_number = int(details['Page'])
if 'PageElements' in details:
page_elements = int(details['PageElements'])
print(page_number, page_elements)
index_list = range((page_number - 1) * page_elements,
(page_number) * page_elements, 1)
print(index_list)
conn = create_connection('Database')
df = pd.read_sql_query("SELECT * FROM Details;", conn)
mm.increment('/details')
mm.save()
return get_dict_entries(df.iloc[index_list]), 200
from Create_db import create_connection, create_table_details
if __name__ == '__main__':
details_csv = 'games_detailed_info.csv'
conn = create_connection('Database')
create_table_details(details_csv, conn)
def put(self):
details = request.json
conn = create_connection('Database')
df = pd.read_sql_query(
"SELECT Detail_ID FROM Details WHERE Game_ID = ?",
conn,
params=[details['Game_ID']]
) # Check if the game exists (Can't update a game that doesn't exist)
if len(df) == 0:
api.abort(404, "Game {} doesn't exist".format(details['Game_ID']))
index = df.loc[0][0]
df = pd.read_sql_query(
"SELECT Game_ID FROM Details WHERE Name = ?;",
conn,
params=[
details['Name']
]) # Check if there is another game with the same updated Name
# if len(df) > 0:
# api.abort(400, "Game '{}' already exists with Game_ID={}".format(
# details['Name'], df.loc[0][0]))
if not (details['Name']):
api.abort(400, "Name field is missing")
details['Board_Game_Rank'] = details['Board_Game_Rank']
if not (details['Board_Game_Rank']):
pass
else:
try:
details['Board_Game_Rank'] = int(details['Board_Game_Rank'])
if details['Board_Game_Rank'] <= 0:
api.abort(400, "Rank can't be zero or negative")
except Exception:
api.abort(400, "Invalid Rank")
#if (details['Bayes_Average']) < 0:
#api.abort(400, "Bayes Average can't be negative")
if (details['Min_players'] > details['Max_players']):
api.abort(400,
"Maximum players can't be less than Minimum players")
if (details['Min_players'] <= 0 or details['Max_players'] <= 0):
api.abort(400, "Number of players can't be zero or negative")
if (details['Min_age'] < 0):
api.abort(400, "Invalid age")
if (details['Min_playtime'] < 0 or details['Max_playtime'] < 0):
api.abort(400, "Playtime can't be negative")
if (details['Min_playtime'] > details['Max_playtime']):
api.abort(400,
"Maximum playtime can't be less than Minimum playtime")
if (details['Year_Published'] > datetime.now().year):
api.abort(400, "Invalid Publishing Year")
if not (details['Thumbnail']):
details[
'Thumbnail'] = 'https://via.placeholder.com/150x150?text=No+Image'
for key in details:
if key not in detail_model.keys():
return {"message": "Property {} is invalid".format(key)}, 400
c = conn.cursor()
c.execute(
'UPDATE Details SET Name=?, Board_Game_Rank=?, Publisher=?, Category=?, Min_players=?, Max_players=?, Min_age=?, Min_playtime=?, Max_playtime=?, Description=?, Expansion=?, Board_Game_Family=?, Mechanic=?, Thumbnail=?, Year_Published=? WHERE Detail_ID=?;',
(
str(details['Name']),
str(details['Board_Game_Rank']),
#str(details['Bayes_Average']),
str(details['Publisher']),
str(details['Category']),
details['Min_players'],
details['Max_players'],
details['Min_age'],
details['Min_playtime'],
details['Max_playtime'],
str(details['Description']),
str(details['Expansion']),
str(details['Board_Game_Family']),
str(details['Mechanic']),
str(details['Thumbnail']),
details['Year_Published'],
int(index)))
conn.commit()
mm.increment('/board_games_details/PUT {}'.format(details['Game_ID']))
mm.save()
return {
"message":
"Game {} has been successfully updated".format(details['Game_ID'])
}, 200
def post(self):
details = request.json
for key in details:
if key not in detail_model.keys():
return {"message": "Property {} is invalid".format(key)}, 400
conn = create_connection('Database')
df = pd.read_sql_query(
"SELECT Name FROM Details WHERE Game_ID = ?;",
conn,
params=[details['Game_ID']]) # Check if the Game_ID already exists
if len(df) > 0:
api.abort(
400, "Game_ID {} already exists with Name '{}'".format(
details['Game_ID'], df.loc[0][0]))
df = pd.read_sql_query(
"SELECT Game_ID FROM Details WHERE Name = ?;",
conn,
params=[details['Name']
]) # Check if there is another game with the same Name
if len(df) > 0:
api.abort(
400, "Game '{}' already exists with Game_ID = {}".format(
details['Name'], df.loc[0][0]))
if not (details['Name']):
api.abort(400, "Name field is missing")
details['Board_Game_Rank'] = details['Board_Game_Rank']
if not (details['Board_Game_Rank']):
pass
else:
try:
details['Board_Game_Rank'] = int(details['Board_Game_Rank'])
if details['Board_Game_Rank'] <= 0:
api.abort(400, "Rank can't be zero or negative")
except Exception:
api.abort(400, "Invalid Rank")
#if (details['Bayes_Average']) < 0:
#api.abort(400, "Bayes Average can't be negative")
if (details['Min_players'] > details['Max_players']):
api.abort(400,
"Maximum players can't be less than Minimum players")
if (details['Min_players'] <= 0 or details['Max_players'] <= 0):
api.abort(400, "Number of players can't be zero or negative")
if (details['Min_age'] < 0):
api.abort(400, "Invalid age")
if (details['Min_playtime'] < 0 or details['Max_playtime'] < 0):
api.abort(400, "Playtime can't be negative")
if (details['Min_playtime'] > details['Max_playtime']):
api.abort(400,
"Maximum playtime can't be less than Minimum playtime")
# There are some games that have Year_Published = 2020
if (details['Year_Published'] > datetime.now().year):
# (maybe upcoming games, or someone invented a time machine. this condition may change)
api.abort(400, "Invalid Publishing Year")
if not (details['Thumbnail']):
details[
'Thumbnail'] = 'https://via.placeholder.com/150x150?text=No+Image'
c = conn.cursor()
c.execute(
"INSERT INTO Details(Game_ID, Name, Board_Game_Rank, Publisher, Category, Min_players, Max_players, Min_age, Min_playtime, Max_playtime, Description, Expansion, Board_Game_Family, Mechanic, Thumbnail, Year_Published) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
(
details['Game_ID'],
details['Name'],
details['Board_Game_Rank'],
#str(details['Bayes_Average']),
str(details['Publisher']),
str(details['Category']),
details['Min_players'],
details['Max_players'],
details['Min_age'],
details['Min_playtime'],
details['Max_playtime'],
details['Description'],
str(details['Expansion']),
str(details['Board_Game_Family']),
str(details['Mechanic']),
details['Thumbnail'],
details['Year_Published']))
conn.commit()
mm.increment('/board_games_details/POST {}'.format(details['Game_ID']))
mm.save()
return {
"message":
"Game {} is created with ID {}".format(details['Name'],
details['Game_ID'])
}, 201
