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_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)
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_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_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_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_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)