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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_season_episodes(cls, serial_id, season_number):
episodes_columns_string = qh.get_columns_string(
EpisodesMapping, 'episode')
season_episodes = qe.execute_mapped(
db, "SELECT {episodes_columns} "
" FROM {episodes_table} WHERE serial_id = {serial_id}"
" AND season_number = {season_number}", *[Episode], **{
'episodes_columns': episodes_columns_string,
'episodes_table': EpisodesMapping.description,
'serial_id': serial_id,
'season_number': season_number
})
return season_episodes
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_all_serials(cls, order_by_field=None) -> List[Serial]:
"""
:return: all serials from table Serials
"""
serials_columns_string = qh.get_columns_string(SerialsMapping)
if order_by_field is None:
all_serials_query = qe.execute_mapped(
db, "SELECT {serials_columns} FROM {serials_table}", *[Serial],
**{
'serials_columns': serials_columns_string,
'serials_table': SerialsMapping.description
})
else:
all_serials_query = qe.execute_mapped(
db,
"SELECT {serials_columns} FROM {serials_table} ORDER BY {order_by_field}",
*[Serial], **{
'serials_columns': serials_columns_string,
'serials_table': SerialsMapping.description,
'order_by_field': order_by_field
})
return cls._get_extended_serials(all_serials_query)
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_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_season_by_number(cls, serial_id, season_number):
serial_id = int(serial_id)
seasons_columns_string = qh.get_columns_string(SeasonsMapping,
'season')
seasons_result = qe.execute_mapped(
db, "SELECT {seasons_columns}"
" FROM {seasons_table} WHERE serial_id = {serial_id}"
" AND season_number = {season_number}", *[Season], **{
'seasons_columns': seasons_columns_string,
'seasons_table': SeasonsMapping.description,
'serial_id': serial_id,
'season_number': season_number
})
if len(seasons_result) > 0:
return cls._get_extended_seasons(seasons_result)[0]
return None
def get_serial_by_id(cls, serial_id, extended=True):
serial_id = int(serial_id)
serials_columns_string = qh.get_columns_string(SerialsMapping,
'serial')
serial_with_id = qe.execute_mapped(
db, "SELECT {serials_columns}"
" FROM {serials_table} WHERE serial_id = {serial_id}", *[Serial],
**{
'serials_columns': serials_columns_string,
'serials_table': SerialsMapping.description,
'serial_id': serial_id
})
if len(serial_with_id) > 0:
return cls._get_extended_serials(
serial_with_id)[0] if extended else serial_with_id[0]
return None
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_episode_by_number(cls, serial_id, season_number, episode_number):
episodes_columns_string = qh.get_columns_string(
EpisodesMapping, 'episode')
episode_result = qe.execute_mapped(
db, "SELECT {columns}"
" FROM {table} WHERE serial_id = {serial_id}"
" AND season_number = {season_number}"
" AND episode_number = {episode_number}", *[Episode], **{
'columns': episodes_columns_string,
'table': EpisodesMapping.description,
'serial_id': serial_id,
'season_number': season_number,
'episode_number': episode_number
})
if len(episode_result) > 0:
return cls._get_extended_episodes(episode_result)[0]
return None
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
]
def get_serial_episodes(cls, serial_id: int) -> List[Episode]:
"""
Get episodes for the serial with serial_id
:param serial_id: id of the serial (should be a whole number)
:return: episodes of the serial
"""
serial_id = int(serial_id)
episodes_columns_string = qh.get_columns_string(
EpisodesMapping, 'episode')
serials_episodes = qe.execute_mapped(
db, "SELECT {episodes_columns} "
" FROM {episodes_table} WHERE serial_id = {serial_id}", *[Episode],
**{
'episodes_columns': episodes_columns_string,
'episodes_table': EpisodesMapping.description,
'serial_id': serial_id
})
return serials_episodes
def get_filtered_serials(cls, title_part: str, start_year: int,
end_year: int, start_rating: int, end_rating: int,
countries_list: List[str], actors_list: List[str],
genres_list: List[str], start_duration: int,
end_duration: int) -> List[Serial]:
"""
Get serials filtered by given params
:param start_duration:
:param end_duration:
:param title_part:
:param end_rating:
:param start_rating:
:param actors_list:
:param genres_list:
:param start_year: start year of the serials
:param end_year: end year of the serials
:param countries_list: list of the countries from wich serials needed
:return: list of serials
"""
serials_columns_string = qh.get_columns_string(SerialsMapping)
all_serials_query = qe.execute_mapped(
db, "SELECT {serials_columns}"
" FROM get_filtered_serials('{title_part}', {start_year}, "
" {end_year}, {start_rating}, {end_rating}, {countries}, {actors}, {genres},"
" {start_duration}, {end_duration})", *[Serial], **{
'serials_columns': serials_columns_string,
'title_part': title_part,
'start_year': start_year,
'end_year': end_year,
'start_rating': start_rating,
'end_rating': end_rating,
'countries': qh.get_sql_array(countries_list),
'actors': qh.get_sql_array(actors_list),
'genres': qh.get_sql_array(genres_list),
'start_duration': start_duration,
'end_duration': end_duration
})
return cls._get_extended_serials(all_serials_query)
