def get_filtered_persons(cls, person_name_part: str, person_type: str): """ Retrieve all persons with given type which names partially equals to given person_name_part :param person_name_part: part of the person's name :param person_type: type of the person from PersonsTypes :return: List of Persons serialized to json format """ if person_type == pt.ACTOR: function_name = "get_filtered_actors" elif person_type == pt.DIRECTOR: function_name = "get_filtered_directors" elif person_type == pt.WRITER: function_name = "get_filtered_writers" else: raise ValueError("Error getting filtered persons: wrong person type '%s'" % person_type) query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM {function_name}('{person_name_part}')", **{'function_name': function_name, 'person_name_part': person_name_part}) return [Person(str(row['person_id']), str(row['person_name']).rstrip(), row['person_birth_date'], row['person_gender']).serialize_json() for row in query_result]
def get_top_episodes_of(cls, person_id: int, person_type: str): """ Return list of dict-serialized top rated episodes in which person participated info :param person_id: persond id :param person_type: type of the person from PersonsTypes :return: List of dicts represented episodes info """ if person_type == pt.ACTOR: function_name = "get_top5_best_actor_episodes" elif person_type == pt.DIRECTOR: function_name = "get_top5_best_director_episodes" elif person_type == pt.WRITER: function_name = "get_top5_best_writer_episodes" else: raise ValueError("Error getting top episodes of person: wrong person type '%s'" % person_type) query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM {function_name}({person_id})", **{'function_name': function_name, 'person_id': person_id}) return [Episode(str(row['episode_title']).rstrip(), row['release_date'], row['rating'], row['serial_title'], row['episode_number'], row['season_number'], row['serial_id'],) for row in query_result]
def get_person_by_id(cls, person_id: int, person_type: str) -> Person: """ Retrieve person with person_id and person_type. :param person_id: id of the person :param person_type: type of the person from PersonsTypes :return: single Person instance """ if person_type == pt.ACTOR: function_name = "get_actor_by_id" elif person_type == pt.DIRECTOR: function_name = "get_director_by_id" elif person_type == pt.WRITER: function_name = "get_writer_by_id" else: raise ValueError("Error getting person by id: wrong person type '%s'" % person_type) query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM {function_name}({person_id})", **{'function_name': function_name, 'person_id': person_id}) if len(query_result) > 0: row = query_result[0] return Person(person_id, str(row['person_name']).rstrip(), row['person_birth_date'], row['person_gender']) return None
def get_top5_serials_with_longest_average_episode(cls): query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM get_top5_serials_with_longest_average_episode()") return [{"title": str(row['serial_title']).rstrip(), "avg_episode_length": round(row['average_episode_length'], 1)} for row in query_result]
def get_season_actors_names(cls, serial_id, season_number): serial_actors = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_actors_names_of({serial_id}, {season_number})", **{ 'serial_id': serial_id, 'season_number': season_number }) return [str(row['actor_name']).rstrip() for row in serial_actors]
def get_top5_creators(cls): query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM get_top5_creators()") return [{"name": str(row['creator_name']).rstrip(), "rating": float(round(row['average_serials_rating'], 2))} for row in query_result]
def get_reviews_of(cls, serial_id): query_result = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_reviews_of({serial_id})", **{'serial_id': serial_id}) return [ Review(serial_id, None, str(row['title']), str(row['review_text']), row['review_date'], str(row['user_login'])) for row in query_result ]
def get_season_rating(cls, serial_id, season_number): season_rating = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_rating_of({serial_id}, {season_number})", **{ 'serial_id': serial_id, 'season_number': season_number }) if len(season_rating) > 0: return round(season_rating[0]['get_rating_of'], 2) return None
def get_season_date(cls, serial_id, season_number): season_date = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_season_date({serial_id}, {season_number})", **{ 'serial_id': serial_id, 'season_number': season_number }) if len(season_date) > 0: return season_date[0]['get_season_date'] return None
def get_season_duration(cls, serial_id, season_number): season_duration = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_duration_of({serial_id}, {season_number})", **{ 'serial_id': serial_id, 'season_number': season_number }) if len(season_duration) > 0: return season_duration[0]['get_duration_of'] return None
def get_serials_in_genres_counts(cls) -> Dict: genres = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_serials_in_genres_counts()", ) return { str(row['genre_title']): row['serials_count'] for row in genres }
def get_top5_serials_in_genre(cls, genre_title): query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM get_top5_serials_in_genre('{genre_title}')", **{ "genre_title": genre_title }) return [{"title": str(row['serial_title']).rstrip(), "rating": float(round(row['serial_rating'], 2))} for row in query_result]
def get_shortest_serials_in_genres(cls, genres_list: List[str]): query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM get_shortest_serials_in_genres({genres_list})", **{ "genres_list": qh.get_sql_array(genres_list) }) return [{"serial_title": str(row['serial_title']).rstrip(), "duration": row['serial_duration']} for row in query_result]
def get_comments_of(cls, serial_id, season_number): query_result = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_comments_of({serial_id}, {season_number})", **{ 'serial_id': serial_id, 'season_number': season_number }) return [ Comment(serial_id, season_number, str(row['comment_text']), row['comment_date'], str(row['user_login'])) for row in query_result ]
def get_serial_played(cls, serial_id): serial_played = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_serial_played({serial_id})", **{'serial_id': serial_id}) return [ Played(serial_id, None, None, row['actor_id'], str(row['actor_name']).rstrip(), str(row['role_title']).rstrip(), str(row['award_title']).rstrip(), row['award_year']) for row in serial_played ]
def get_actor_roles(cls, actor_name): query_result = qe.execute_arbitrary(db_engine, "SELECT *" " FROM get_actor_roles('{actor_name}')", **{ "actor_name": actor_name }) return [{"serial_title": str(row['serial_title']).rstrip(), "episode_title": str(row['episode_title']).rstrip(), "role_name": str(row['role_name']).rstrip()} for row in query_result]
def get_serial_genres_titles(cls, serial_id: int) -> List[str]: """ Get titles of serial's genres :param serial_id: id of the serial (should be a whole number) :return: titles of the serial's genres ['Title1', 'Title2', ...] """ serial_id = int(serial_id) serial_genres = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_genres_of_serial_titles({serial_id})", **{'serial_id': serial_id}) return [row['genre_title'].rstrip() for row in serial_genres]
def get_serial_creators_names(cls, serial_id): """ Get creators names of the serial :param serial_id: id of the serial (should be a whole number) :return: names of the serial's creators """ serial_id = int(serial_id) serial_actors = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_creators_of_serial_names({serial_id})", **{'serial_id': serial_id}) return [str(row['creator_name']).rstrip() for row in serial_actors]
def get_serial_seasons(cls, serial_id: int) -> List[Season]: """ Get seasons for the serial with serial_id :param serial_id: id of the serial (should be a whole number) :return: seasons of the serial """ serial_seasons = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_seasons_of_serial({serial_id})", **{'serial_id': serial_id}) return [ Season(row['season_number'], row['serial_id'], row['release_date']) for row in serial_seasons ]
def get_episode_writers_names(cls, serial_id, season_number, episode_number): episode_writers = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_episode_writers_names({serial_id}, {season_number}, {episode_number})", **{ 'serial_id': serial_id, 'season_number': season_number, 'episode_number': episode_number }) return { row['writer_id']: str(row['writer_name']).rstrip() for row in episode_writers }
def get_episode_played(cls, serial_id, season_number, episode_number): episode_played = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_episode_played({serial_id}, {season_number}, {episode_number})", **{ 'serial_id': serial_id, 'season_number': season_number, 'episode_number': episode_number }) return [ Played(serial_id, season_number, episode_number, row['actor_id'], str(row['actor_name']).rstrip(), str(row['role_title']).rstrip(), str(row['award_title']).rstrip(), row['award_year']) for row in episode_played ]
def get_serial_awards(cls, serial_id: int) -> List[SerialAward]: """ Get serial Awards :param serial_id: id of the serial (should be a whole number) :return: List of SerialAward """ serial_id = int(serial_id) serial_actors = qe.execute_arbitrary( db_engine, "SELECT *" " FROM get_serial_awards({serial_id})", **{'serial_id': serial_id}) return [ SerialAward(serial_id, str(row['award_title']).rstrip(), str(row['award_year']).rstrip()) for row in serial_actors ]