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
]