def create_user(username, password, first_name, last_name, email):
conn = _database.connection()
with conn.cursor() as cursor:
sql = "INSERT INTO users (username, email, first_name, last_name, password) VALUE (%s, %s, %s, %s, MD5(%s));"
cursor.execute(sql, (username, email, first_name, last_name, password))
result = cursor.fetchone()
conn.close()
return result
def search(query='', limit=3):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM elements_search WHERE name LIKE %s LIMIT %s;"
cursor.execute(sql, ('%' + query + '%', limit))
result = cursor.fetchall()
finally:
conn.close()
return result
def user_exist(username, password):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT COUNT(*) AS count, id FROM users WHERE username = %s AND password = MD5(%s)"
cursor.execute(sql, (username, password))
result = cursor.fetchone()
finally:
conn.close()
return result
def getRandomElements(nb):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM elements WHERE picture IS NOT NULL ORDER BY RAND() LIMIT %s;"
cursor.execute(sql, (nb))
result = cursor.fetchall()
finally:
conn.close()
return result
def getLastReview(nb):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM elements_evaluate ORDER BY created DESC LIMIT %s;"
cursor.execute(sql, (nb))
result = cursor.fetchall()
finally:
conn.close()
return result
def get_user(id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM users WHERE id = %s"
cursor.execute(sql, (id))
result = cursor.fetchone()
conn.close()
except:
abort(404)
return result
def rate_game(id, user_id, rate): conn = _database.connection() try: with conn.cursor() as cursor: sql = " INSERT INTO evaluate_game (`users_id`, `games_id`, `comment`, `rate`) VALUE (%s, %s, NULL, %s) \ ON DUPLICATE KEY UPDATE `rate` = %s;" cursor.execute(sql, (user_id, id, rate, rate)) cursor.fetchone() result = True except Exception as e: result = False finally: cursor.close() conn.close() return result
def get_user_review(id, user_id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT comment, rate \
FROM evaluate_game \
WHERE users_id = %s AND games_id = %s;"
cursor.execute(sql, (user_id, id))
result = cursor.fetchone()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def delete_rate(movie_id, user_id):
conn = _database.connection()
result = 0
try:
with conn.cursor() as cursor:
sql = "UPDATE evaluate_movie SET rate = NULL WHERE movies_id = %s AND users_id = %s;"
cursor.execute(sql, (movie_id, user_id))
result = cursor.fetchall()
cursor.close()
except Exception as e:
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_genres(id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT id, name \
FROM genres \
JOIN genre_game gm ON genres.id = gm.genres_id \
WHERE games_id = %s;"
cursor.execute(sql, id)
result = cursor.fetchall()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_nb_comments(identifiant):
conn = _database.connection()
result = 0
try:
with conn.cursor() as cursor:
sql = "SELECT count(*) as total FROM evaluate_movie WHERE comment IS NOT NULL AND movies_id = %s;"
cursor.execute(sql, identifiant)
result = cursor.fetchone()['total']
cursor.close()
except Exception as e:
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_character(id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT fullname, picture \
FROM characters \
WHERE id = %s;"
cursor.execute(sql, (id))
result = cursor.fetchone()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_total_movie_number():
conn = _database.connection()
result = 0
try:
with conn.cursor() as cursor:
sql = "SELECT COUNT(*) AS total FROM movies"
cursor.execute(sql)
result = cursor.fetchone()['total']
cursor.close()
except Exception as e:
flash('The database is unavailable', 'error')
flash(e, 'error')
finally:
conn.close()
return result
def get_casting(id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT characters_id as id, fullname, name AS role, picture \
FROM character_game \
JOIN characters c ON character_game.characters_id = c.id \
JOIN roles r ON character_game.roles_id = r.id \
WHERE games_id = %s;"
cursor.execute(sql, id)
result = cursor.fetchall()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_reviews(id, nombre, page):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT u.id AS id, username, comment \
FROM evaluate_game \
JOIN games m ON evaluate_game.games_id = m.id \
JOIN users u ON evaluate_game.users_id = u.id \
WHERE games_id = %s LIMIT %s, %s;"
cursor.execute(sql, (id, int(page) * int(nombre), int(nombre)))
result = cursor.fetchall()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def show_game(id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT *, avg(rate) AS moyenne \
FROM games \
LEFT JOIN evaluate_game em ON games.id = em.games_id \
WHERE id = %s \
GROUP BY games_id;"
cursor.execute(sql, id)
result = cursor.fetchone()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_most_interested_games(nombre, page=0):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT id, name, picture, COUNT(*) AS nombre, release_year, description \
FROM games \
JOIN wish_list_games wlm ON games.id = wlm.games_id \
GROUP BY id \
ORDER BY COUNT(*) DESC \
LIMIT %s, %s;"
cursor.execute(sql, (page * nombre, nombre))
result = cursor.fetchall()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_top_rated_games(nombre, page=0):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT id, name, ROUND(AVG(rate), 1) AS moyenne, picture, release_year, description \
FROM evaluate_game \
JOIN games m ON evaluate_game.games_id = m.id \
GROUP BY id \
ORDER BY moyenne DESC \
LIMIT %s, %s;"
cursor.execute(sql, (int(page) * int(nombre), int(nombre)))
result = cursor.fetchall()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
flash(e, 'error')
finally:
conn.close()
return result
def get_user_wishlist(game_id, user_id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT COUNT(*) AS total \
FROM wish_list_games \
WHERE users_id = %s AND games_id = %s;"
cursor.execute(sql, (user_id, game_id))
row = cursor.fetchone()
if row['total'] == 1:
result = True
else:
result = False
cursor.close()
except Exception as e:
result = False
finally:
conn.close()
return result
def wishlist_game(game_id, user_id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "INSERT INTO wish_list_games (users_id, games_id) VALUES (%s, %s)"
cursor.execute(sql, (user_id, game_id))
cursor.close()
flash('This game have been added to your wishlist', 'success')
except Exception as e:
try:
with conn.cursor() as cursor:
sql = "DELETE FROM wish_list_games WHERE users_id = %s AND games_id = %s;"
cursor.execute(sql, (user_id, game_id))
cursor.close()
flash('This game have been removed from your wishlist', 'success')
except Exception:
flash('The database is unavailable', 'error')
finally:
conn.close()
def get_most_commented_games(nombre, page=0):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT id, name, picture, release_year, description \
FROM evaluate_game \
JOIN games m ON evaluate_game.games_id = m.id \
WHERE comment IS NOT NULL \
GROUP BY id \
ORDER BY COUNT(*) DESC \
LIMIT %s, %s;"
cursor.execute(sql, (page * nombre, nombre))
result = cursor.fetchall()
except Exception as e:
result = ()
flash(e, 'error')
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_all_comment(id):
conn = _database.connection()
result = 0
try:
with conn.cursor() as cursor:
sql = "SELECT id, name, comment, picture, release_year, 'game' AS type FROM evaluate_game \
JOIN games m ON evaluate_game.games_id = m.id WHERE users_id = %s AND comment IS NOT NULL AND comment != '' \
UNION SELECT id, name, comment, picture, release_year, 'game' AS type FROM evaluate_game \
JOIN games m ON evaluate_game.games_id = m.id WHERE users_id = %s AND comment IS NOT NULL AND comment != '' \
UNION SELECT id, name, comment, picture, `release`, 'serie' AS type FROM evaluate_serie \
JOIN series m ON evaluate_serie.series_id = m.id WHERE users_id = %s AND comment IS NOT NULL AND comment != ''"
cursor.execute(sql, (id, id, id))
result = cursor.fetchall()
cursor.close()
except Exception as e:
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_all_wishlisted(id):
conn = _database.connection()
result = 0
try:
with conn.cursor() as cursor:
sql = "SELECT id, name, release_year, 'game' AS type, picture FROM wish_list_games \
JOIN games m ON wish_list_games.games_id = m.id WHERE users_id = %s \
UNION SELECT id, name, `release`, 'serie' AS type, picture FROM wish_list_series \
JOIN series s ON wish_list_series.series_id = s.id WHERE users_id = %s \
UNION SELECT id, name, release_year, 'game' AS type, picture FROM wish_list_games \
JOIN games g ON wish_list_games.games_id = g.id WHERE users_id = %s;"
cursor.execute(sql, (id, id, id))
result = cursor.fetchall()
cursor.close()
except Exception as e:
flash('The database is unavailable', 'error')
finally:
conn.close()
return result
def get_all_elements(id):
conn = _database.connection()
try:
with conn.cursor() as cursor:
sql = "SELECT id, name, 'serie' AS type, picture \
FROM series \
JOIN character_serie cs ON series.id = cs.series_id \
WHERE characters_id = %s \
UNION \
SELECT id, name, 'movie' AS type, picture \
FROM movies \
JOIN character_movie m ON movies.id = m.movies_id \
WHERE characters_id = %s;"
cursor.execute(sql, (id, id))
result = cursor.fetchall()
except Exception as e:
result = ()
flash('The database is unavailable', 'error')
finally:
conn.close()
return result