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')