def get_show_seasons(id):
return data_manager.execute_select("""
SELECT *
FROM shows
JOIN seasons ON shows.id = seasons.show_id
WHERE shows.id = %(id)s;
""", {"id": id})
def get_all_shows_info():
return data_manager.execute_select(
sql.SQL("""
SELECT *
FROM shows
ORDER BY title""")
)
def search_characters(phrase):
phrase_words = phrase.split()
where_condition = ''
# { 'word_0': 'dan', 'word_1': 'laci' }
params = {}
for i, word in enumerate(phrase_words):
key = 'word_' + str(i)
if where_condition != '':
where_condition += ' AND '
where_condition += f'character_name ILIKE %({key})s'
params[key] = '%' + word + '%'
query = f'''
SELECT
show_characters.character_name,
shows.title,
actors.name
FROM show_characters
JOIN actors ON show_characters.actor_id = actors.id
JOIN shows ON show_characters.show_id = shows.id
WHERE {where_condition}
'''
print(query)
print(params)
return data_manager.execute_select(query, params)
def search_shows_by_title(search):
return data_manager.execute_select(
"""
SELECT shows.title,shows.rating,shows.year,shows.trailer FROM shows
WHERE UPPER(shows.title) LIKE UPPER(%(search)s)
GROUP BY shows.title, shows.rating, shows.year, shows.trailer
""", {"search": "%" + search + "%"})
def get_show_details(show_id):
return data_manager.execute_select(
f'''SELECT title, shows.year, runtime, ROUND(rating,2) AS rating,overview, homepage, trailer
FROM shows
WHERE id = {show_id}
'''
)
def insert_actor_of_show(show_id, actor):
actor_id = actor['person']['ids']['trakt']
existing_actor = execute_select(
"SELECT id FROM actors WHERE id=%(actor_id)s", {'actor_id': actor_id})
if len(existing_actor) == 0:
execute_dml_statement(
"""
INSERT INTO actors (id, name, birthday, death, biography)
VALUES (%(id)s, %(name)s, %(birthday)s, %(death)s, %(biography)s);
""", {
"id": actor_id,
"name": actor['person']['name'],
"birthday": actor['person']['birthday'],
"death": actor['person']['death'],
"biography": actor['person']['biography']
})
execute_dml_statement(
"""
INSERT INTO show_characters (show_id, actor_id, character_name)
VALUES (%(show_id)s, %(actor_id)s, %(character_name)s)
""", {
'show_id': show_id,
'actor_id': actor_id,
'character_name': actor['character']
})
def get_shows(page):
page_number = int(page)
data = data_manager.execute_select(
f'''SELECT * FROM shows ORDER BY rating DESC LIMIT 15 OFFSET {15 * page_number};
''')
return data
def get_shows_limited(order_by="rating", order="DESC", limit=0, offset=0):
return data_manager.execute_select(
sql.SQL("""
SELECT
shows.id,
shows.title,
shows.year,
shows.runtime,
to_char(shows.rating::float, '999.9') AS rating_string,
string_agg(genres.name, ', ' ORDER BY genres.name) AS genres_list,
shows.trailer,
shows.homepage
FROM shows
JOIN show_genres ON shows.id = show_genres.show_id
JOIN genres ON show_genres.genre_id = genres.id
GROUP BY shows.id
ORDER BY
CASE WHEN %(order)s = 'ASC' THEN {order_by} END ASC,
CASE WHEN %(order)s = 'DESC' THEN {order_by} END DESC
LIMIT %(limit)s
OFFSET %(offset)s;
""").format(order_by=sql.Identifier(order_by)), {
"order": order,
"limit": limit,
"offset": offset
})
def tv_show(show_id):
data = data_manager.execute_select(
f'''SELECT shows.id, shows.title as name, shows.runtime, shows.year, shows.trailer, shows.overview, seasons.title
FROM shows INNER JOIN seasons ON shows.id = seasons.show_id
WHERE shows.id = {show_id}
;''')
return data
def search_for_title(phrase):
return data_manager.execute_select(f"""
SELECT shows.id, shows.title
FROM shows
WHERE shows.title ILIKE '%{phrase}%'
""",
fetchall=True)
def get_actor(actor_id):
return data_manager.execute_select("""
SELECT actors.*
FROM actors
WHERE actors.id = %(actor_id)s
""", {'actor_id': actor_id},
fetchall=False)
def check_if_user_exist(username):
return data_manager.execute_select(
f'''SELECT username, password
FROM users
WHERE username = '******'
'''
)
def get_user(username):
return data_manager.execute_select(
"""
SELECT username, password
FROM users
WHERE username = %(username)s;
""", {'username': username}, False)
def get_third(season_number):
return data_manager.execute_select(
"""
SELECT shows.id, shows.title, shows.rating, seasons.title as season_name,seasons.overview FROM shows
JOIN seasons ON shows.id = seasons.show_id
WHERE seasons.season_number = %(season_number)s;
""", {'season_number': season_number})
def top_20_actors():
return data_manager.execute_select(f"""SELECT a.name, string_agg(title, ', ') actor_shows, COUNT(*) as shows_number from actors a
INNER JOIN show_characters sc ON a.id=sc.actor_id
INNER JOIN shows s on sc.show_id=s.id
GROUP by a.name
ORDER BY shows_number DESC
LIMIT 20""")
def search(episode_num, season_num):
return data_manager.execute_select(
'SELECT shows.title FROM shows INNER JOIN seasons ON seasons.show_id = shows.id INNER JOIN episodes ON episodes.season_id = seasons.id WHERE seasons.season_number >= %(season_num)s AND episodes.episode_number >= %(episode_num)s GROUP BY shows.title',
{
'episode_num': episode_num,
'season_num': season_num
})
def year_detailed(yearfrom, yearto):
return data_manager.execute_select(
'SELECT year, AVG(rating) AS rating, COUNT(id) AS shows FROM shows WHERE year > %(yearfrom)s AND year < %(yearto)s GROUP BY year ORDER BY year DESC ',
{
'yearfrom': yearfrom,
'yearto': yearto
})
def get_show_info(show_id):
try:
return data_manager.execute_select(
'SELECT shows.id, shows.title, shows.runtime, shows.overview, shows.trailer, shows.homepage, shows.year, shows.rating FROM shows WHERE id = %s',
(show_id, ))
except psycopg2.Error as e:
print(e)
def get_show_seasons(show_id):
try:
return data_manager.execute_select(
"SELECT id, title, overview FROM seasons WHERE show_id = %s",
(show_id, ))
except psycopg2.Error as e:
print(e)
def verify_user_if_exists(username):
query = """
SELECT password
FROM users
WHERE username = %s;
"""
return data_manager.execute_select(query, (username, ))
def get_20shows():
data = data_manager.execute_select(
f''' SELECT shows.id, shows.title, COUNT(seasons.show_id) as sezoane from shows INNER JOIN seasons on shows.id = seasons.show_id
WHERE shows.id = 1390
GROUP BY shows.id, shows.title
;''')
return data
def select_fav(user_id):
return data_manager.execute_select("""SELECT DISTINCT shows.title FROM favorites
JOIN shows
ON shows.id = favorites.show_id
JOIN users
ON favorites.user_id = users.id
WHERE users.id = %(user_id)s;""",
{'user_id': user_id})
def show_genre(id):
return data_manager.execute_select(
'SELECT g.name '
'FROM show_genres as sg '
'LEFT JOIN genres as g '
'ON sg.genre_id=g.id '
'WHERE sg.show_id = %(id)s '
'GROUP BY sg.show_id, g.name; ', {'id': id})
def get_shows_by_min_season_numb(number):
return data_manager.execute_select(
"""
SELECT shows.title, COUNT(season_number) as number_of_seasons FROM shows
LEFT JOIN seasons s on shows.id = s.show_id
GROUP BY shows.title
HAVING COUNT(season_number) >= %(number)s
""", {"number": number})
def get_seasons_by_show_id(show_id):
return data_manager.execute_select("""SELECT seasons.id, seasons.title, seasons.overview, string_agg(DISTINCT episodes.title, '@@') as episodes, string_agg(DISTINCT episodes.overview, '@@') as ep_overview
FROM seasons
JOIN episodes
ON seasons.id = episodes.season_id
GROUP BY seasons.id, seasons.title, seasons.overview, seasons.show_id
HAVING seasons.show_id = %(show_id)s;""",
{'show_id' : show_id})
def get_cast_for_show(show_id):
return data_manager.execute_select(
f" SELECT array_agg(actors.name) as actors, array_agg(show_characters.character_name) as characters"
f" FROM actors "
f" JOIN show_characters ON actors.id = show_characters.actor_id"
f" JOIN shows ON show_characters.show_id = shows.id"
f" WHERE shows.id = {show_id}"
f" GROUP BY shows.id;")
def get_top_actors_id():
return data_manager.execute_select("""
SELECT show_characters.actor_id, a.name, COUNT(show_characters.character_name) as number_of_roles FROM show_characters
LEFT JOIN actors a on show_characters.actor_id = a.id
GROUP BY actor_id ,a.name
ORDER BY number_of_roles desc
LIMIT 10
""")
def get_user_details_by_username(username):
return data_manager.execute_select(
f'''SELECT username, password
FROM users
WHERE username = '******'
'''
)
def get_username(username):
return data_manager.execute_select(
sql.SQL("""
SELECT username
FROM public.users
WHERE username='******'
""".format(username))
)
def get_actors():
return data_manager.execute_select("""SELECT a.name, string_agg(DISTINCT s.title, '#') AS titles, COUNT(*) as show_numbers
FROM actors a
JOIN show_characters sc on a.id = sc.actor_id
JOIN shows s on sc.show_id = s.id
GROUP BY a.name
ORDER BY show_numbers DESC
LIMIT 20;""")
