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