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;""")