def get_board_most_posted(user_id): games_to_rec = [] try: query = "SELECT name FROM user_most_posted_game WHERE id=" + str(user_id) most_posted = get_from_db(query, 'one') if most_posted: games_to_rec.append(('posted', most_posted[0])) except RuntimeError: print("user_most_posted_game table not found!") try: query = "SELECT name FROM user_subscribed_games WHERE id=" + str(user_id) recs = get_from_db(query, 'all') for game in recs: games_to_rec.append(('subscribed', game[0])) except: print("user_subscribed_games not found!") try: query = "SELECT game_name FROM collection_collection_has_games WHERE user_id=" + str(user_id) cols = get_from_db(query, 'all') for game in cols: games_to_rec.append(('owned', game[0])) # print(games_to_rec) except: print("collection database not found!") finally: return games_to_rec
def search_game(game_query): try: query = "SELECT \"details.name\",\"details.description\",\ \"stats.average\",\"stats.averageweight\",\ \"details.image\",\"game.id\" \ FROM boardgames \ WHERE LOWER(\"details.name\") \ LIKE LOWER(\'%" + game_query + "%\') \ ORDER BY \"stats.usersrated\" DESC LIMIT 2500;" return get_from_db(query, 'all') except KeyError: print("Sorry no database found for search_games!")
def get_table_from_sqlite(): try: query = '''SELECT * FROM boardgames WHERE "stats.usersrated"!=0 AND "game.type"='boardgame' ORDER BY "stats.usersrated" DESC LIMIT 2500;''' df = get_from_db(query,'all',to_pd=True) # numbers in incremental order needed to cosine similarity matrix to work df['index']=df.index # clean and process data first for feature in features: df[feature] = df[feature].fillna('') # filling missing vals with empty str # apply the function to each row in data set to store the combined strings into column called combined_features df['combined_features'] = df.apply(combine_features, axis=1) return df except RuntimeError: print("Sorry, database not found!")
def get_collection_details(name, user_id): get_from_db( 'SELECT * \ FROM user_collections \ WHERE collection_name = \"' + name + '\" \ AND user_id=' + str(user_id), 'one')
def get_collections(user_id): get_from_db( "SELECT collection_name \ FROM collection_collection \ WHERE user_id =" + str(user_id), 'all')
def get_collection_details(name, user_id): return get_from_db( 'SELECT * FROM collection_collection_has_games \ WHERE collection_name=\'' + name + '\' \ AND user_id=' + str(user_id) + ' LIMIT 6', 'all')