def retrieve_scores_by_id(self, panelist_id: int) -> List[int]:
"""Returns a list of panelist scores for appearances for the
requested panelist ID.
:param panelist_id: Panelist ID
:return: List containing panelist scores. If panelist scores
could not be retrieved, an empty list is returned.
"""
if not valid_int_id(panelist_id):
return []
scores = []
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT pm.panelistscore AS score "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE panelistid = %s "
"AND s.bestof = 0 and s.repeatshowid IS NULL "
"ORDER BY s.showdate ASC;"
)
cursor.execute(query, (panelist_id,))
result = cursor.fetchall()
cursor.close()
if not result:
return []
for appearance in result:
if appearance.score:
scores.append(appearance.score)
return scores
def retrieve_by_id(self, host_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing host ID, name and
slug string for the requested host ID.
:param host_id: Host ID
:return: Dictionary containing host information. If host
information could not be retrieved, an empty dictionary is
returned.
"""
if not valid_int_id(host_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT hostid AS id, host AS name, hostslug AS slug, "
"hostgender AS gender "
"FROM ww_hosts "
"WHERE hostid = %s "
"LIMIT 1;")
cursor.execute(query, (host_id, ))
result = cursor.fetchone()
cursor.close()
if not result:
return {}
return {
"id": result.id,
"name": result.name,
"gender": result.gender,
"slug": result.slug if result.slug else slugify(result.name),
}
def retrieve_by_id(self, scorekeeper_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing scorekeeper ID, name
and slug string for the requested scorekeeper ID.
:param scorekeeper_id: Scorekeeper ID
:return: Dictionary containing scorekeeper information. If
scorekeeper information could not be retrieved, an empty
dictionary will be returned.
"""
if not valid_int_id(scorekeeper_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT scorekeeperid AS id, scorekeeper AS name, "
"scorekeeperslug AS slug, scorekeepergender AS gender "
"FROM ww_scorekeepers "
"WHERE scorekeeperid = %s "
"LIMIT 1;"
)
cursor.execute(query, (scorekeeper_id,))
result = cursor.fetchone()
cursor.close()
if not result:
return {}
return {
"id": result.id,
"name": result.name,
"slug": result.slug if result.slug else slugify(result.name),
"gender": result.gender,
}
def retrieve_scores_grouped_list_by_id(
self, panelist_id: int
) -> Dict[str, List[int]]:
"""Returns a panelist's score grouping for the requested
panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing two lists, one containing scores
and one containing counts of those scores. If panelist
scores could not be retrieved, an empty dictionary is
returned.
"""
if not valid_int_id(panelist_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT MIN(pm.panelistscore) AS min, "
"MAX(pm.panelistscore) AS max "
"FROM ww_showpnlmap pm "
"LIMIT 1;"
)
cursor.execute(query)
result = cursor.fetchone()
if not result:
return {}
min_score = result.min
max_score = result.max
scores = {}
for score in range(min_score, max_score + 1):
scores[score] = 0
query = (
"SELECT pm.panelistscore AS score, "
"COUNT(pm.panelistscore) AS score_count "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s "
"AND s.bestof = 0 AND s.repeatshowid IS NULL "
"AND pm.panelistscore IS NOT NULL "
"GROUP BY pm.panelistscore "
"ORDER BY pm.panelistscore ASC;"
)
cursor.execute(query, (panelist_id,))
results = cursor.fetchall()
cursor.close()
if not results:
return {}
for row in results:
scores[row.score] = row.score_count
return {
"score": list(scores.keys()),
"count": list(scores.values()),
}
def retrieve_rank_info_by_id(self, panelist_id: int) -> Dict[str, int]:
"""Returns a dictionary with ranking information for the
requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing panelist ranking information. If
panelist ranking information could not be returned, an empty
dictionary will be returned.
"""
if not valid_int_id(panelist_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT ( "
"SELECT COUNT(pm.showpnlrank) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s AND pm.showpnlrank = '1' AND "
"s.bestof = 0 and s.repeatshowid IS NULL) as 'first', ( "
"SELECT COUNT(pm.showpnlrank) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s AND pm.showpnlrank = '1t' AND "
"s.bestof = 0 and s.repeatshowid IS NULL) as 'first_tied', ( "
"SELECT COUNT(pm.showpnlrank) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s AND pm.showpnlrank = '2' AND "
"s.bestof = 0 and s.repeatshowid IS NULL) as 'second', ( "
"SELECT COUNT(pm.showpnlrank) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s AND pm.showpnlrank = '2t' AND "
"s.bestof = 0 and s.repeatshowid IS NULL) as 'second_tied', ( "
"SELECT COUNT(pm.showpnlrank) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s AND pm.showpnlrank = '3' AND "
"s.bestof = 0 and s.repeatshowid IS NULL "
") as 'third';")
cursor.execute(
query,
(
panelist_id,
panelist_id,
panelist_id,
panelist_id,
panelist_id,
),
)
result = cursor.fetchone()
cursor.close()
if not result:
return {}
return {
"first": result.first,
"first_tied": result.first_tied,
"second": result.second,
"second_tied": result.second_tied,
"third": result.third,
}
def retrieve_panelist_info_by_ids(
self, show_ids: List[int]
) -> Dict[int, List[Dict[str, Any]]]:
"""Returns a list of dictionary objects containing panelist
information for the requested show IDs.
:param show_ids: List of show IDs
:return: List of panelists with corresponding scores and
ranking information. If panelist information could not be
retrieved, an empty list will be returned.
"""
for show_id in show_ids:
if not valid_int_id(show_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT s.showid AS show_id, pm.panelistid AS panelist_id, "
"p.panelist AS name, p.panelistslug AS slug, "
"pm.panelistlrndstart AS start, "
"pm.panelistlrndcorrect AS correct, "
"pm.panelistscore AS score, pm.showpnlrank AS pnl_rank "
"FROM ww_showpnlmap pm "
"JOIN ww_panelists p ON p.panelistid = pm.panelistid "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.showid IN ({ids}) "
"ORDER by s.showdate ASC, pm.panelistscore DESC, "
"pm.showpnlmapid ASC;".format(ids=", ".join(str(v) for v in show_ids))
)
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
if not results:
return {}
panelists = {}
for panelist in results:
if panelist.show_id not in panelists:
panelists[panelist.show_id] = []
panelists[panelist.show_id].append(
{
"id": panelist.panelist_id,
"name": panelist.name,
"slug": panelist.slug if panelist.slug else slugify(panelist.name),
"lightning_round_start": panelist.start if panelist.start else None,
"lightning_round_correct": panelist.correct
if panelist.correct
else None,
"score": panelist.score,
"rank": panelist.pnl_rank if panelist.pnl_rank else None,
}
)
return panelists
def retrieve_scores_grouped_ordered_pair_by_id(
self, panelist_id: int
) -> List[Tuple[int, int]]:
"""Returns a list of tuples containing a score and the
corresponding number of instances a panelist has scored that amount
for the requested panelist ID.
:param panelist_id: Panelist ID
:return: List of tuples containing scores and score counts. If
panelist scores could not be retrieved, an empty list is
returned.
"""
if not valid_int_id(panelist_id):
return []
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT MIN(pm.panelistscore) AS min, "
"MAX(pm.panelistscore) AS max "
"FROM ww_showpnlmap pm;"
)
cursor.execute(query)
result = cursor.fetchone()
if not result:
return []
min_score = result.min
max_score = result.max
scores = {}
for score in range(min_score, max_score + 1):
scores[score] = 0
query = (
"SELECT pm.panelistscore AS score, "
"COUNT(pm.panelistscore) AS score_count "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s "
"AND s.bestof = 0 AND s.repeatshowid IS NULL "
"AND pm.panelistscore IS NOT NULL "
"GROUP BY pm.panelistscore "
"ORDER BY pm.panelistscore ASC;"
)
cursor.execute(query, (panelist_id,))
results = cursor.fetchall()
cursor.close()
if not results:
return []
for row in results:
scores[row.score] = row.score_count
return list(scores.items())
def retrieve_statistics_by_id(self, panelist_id: int) -> Dict[str, Any]:
"""Returns a dictionary containing panelist statistics, ranking
data, and scoring data for the requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing panelist statistics. If panelist
statistics could not be returned, an empty dictionary will
be returned.
"""
if not valid_int_id(panelist_id):
return {}
score_data = self.scores.retrieve_scores_by_id(panelist_id)
ranks = self.retrieve_rank_info_by_id(panelist_id)
if not score_data or not ranks:
return {}
appearance_count = len(score_data)
scoring = {
"minimum": int(numpy.amin(score_data)),
"maximum": int(numpy.amax(score_data)),
"mean": round(numpy.mean(score_data), 4),
"median": int(numpy.median(score_data)),
"standard_deviation": round(numpy.std(score_data), 4),
"total": int(numpy.sum(score_data)),
}
ranks_first = round(100 * (ranks["first"] / appearance_count), 4)
ranks_first_tied = round(
100 * (ranks["first_tied"] / appearance_count), 4)
ranks_second = round(100 * (ranks["second"] / appearance_count), 4)
ranks_second_tied = round(
100 * (ranks["second_tied"] / appearance_count), 4)
ranks_third = round(100 * (ranks["third"] / appearance_count), 4)
ranks_percentage = {
"first": ranks_first,
"first_tied": ranks_first_tied,
"second": ranks_second,
"second_tied": ranks_second_tied,
"third": ranks_third,
}
ranking = {
"rank": ranks,
"percentage": ranks_percentage,
}
return {
"scoring": scoring,
"ranking": ranking,
}
def retrieve_panelist_info_by_id(self,
show_id: int) -> List[Dict[str, Any]]:
"""Returns a list of dictionary objects containing panelist
information for the requested show ID.
:param show_id: Show ID
:return: List of panelists with corresponding scores and
ranking information. If panelist information could not be
retrieved, an empty list will be returned.
"""
if not valid_int_id(show_id):
return []
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT pm.panelistid AS id, p.panelist AS name, "
"p.panelistslug AS slug, "
"pm.panelistlrndstart AS start, "
"pm.panelistlrndcorrect AS correct, "
"pm.panelistscore AS score, pm.showpnlrank AS pnl_rank "
"FROM ww_showpnlmap pm "
"JOIN ww_panelists p on p.panelistid = pm.panelistid "
"WHERE pm.showid = %s "
"ORDER by pm.panelistscore DESC, pm.showpnlmapid ASC;")
cursor.execute(query, (show_id, ))
results = cursor.fetchall()
cursor.close()
if not results:
return []
panelists = []
for row in results:
panelists.append({
"id":
row.id,
"name":
row.name,
"slug":
row.slug if row.slug else slugify(row.name),
"lightning_round_start":
row.start,
"lightning_round_correct":
row.correct,
"score":
row.score,
"rank":
row.pnl_rank if row.pnl_rank else None,
})
return panelists
def retrieve_guest_info_by_ids(
self, show_ids: List[int]
) -> Dict[int, List[Dict[str, Any]]]:
"""Returns a list of dictionary objects containing Not My Job
guest information for the requested show IDs.
:param show_ids: List of show IDs
:return: Dictionary containing Not My Job guest information. If
Not My Job information could not be retrieved, an empty list
will be returned.
"""
for show_id in show_ids:
if not valid_int_id(show_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT s.showid AS show_id, gm.guestid AS guest_id, "
"g.guest AS name, g.guestslug AS slug, "
"gm.guestscore AS score, gm.exception AS score_exception "
"FROM ww_showguestmap gm "
"JOIN ww_guests g ON g.guestid = gm.guestid "
"JOIN ww_shows s ON s.showid = gm.showid "
"WHERE gm.showid IN ({ids}) "
"ORDER BY s.showdate ASC, "
"gm.showguestmapid ASC;".format(ids=", ".join(str(v) for v in show_ids))
)
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
if not results:
return []
shows = {}
for guest in results:
if guest.show_id not in shows:
shows[guest.show_id] = []
shows[guest.show_id].append(
{
"id": guest.guest_id,
"name": guest.name,
"slug": guest.slug if guest.slug else slugify(guest.name),
"score": guest.score,
"score_exception": bool(guest.score_exception),
}
)
return shows
def id_exists(self, show_id: int) -> bool:
"""Checks to see if a show ID exists.
:param show_id: Show ID
:return: True or False, based on whether the show ID exists
"""
if not valid_int_id(show_id):
return False
cursor = self.database_connection.cursor(dictionary=False)
query = "SELECT showid FROM ww_shows " "WHERE showid = %s " "LIMIT 1;"
cursor.execute(query, (show_id, ))
result = cursor.fetchone()
cursor.close()
return bool(result)
def retrieve_yearly_appearances_by_id(self,
panelist_id: int) -> Dict[int, int]:
"""Returns a dictionary containing panelist appearances broken
down by year, for the requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing scoring breakdown by year. If
panelist appearances could not be retrieved, an empty
dictionary is returned.
"""
if not valid_int_id(panelist_id):
return {}
years = {}
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT DISTINCT YEAR(s.showdate) AS year "
"FROM ww_shows s "
"ORDER BY YEAR(s.showdate) ASC;")
cursor.execute(query)
results = cursor.fetchall()
if not results:
return {}
for row in results:
years[row.year] = 0
query = ("SELECT YEAR(s.showdate) AS year, "
"COUNT(p.panelist) AS count "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"JOIN ww_panelists p ON p.panelistid = pm.panelistid "
"WHERE pm.panelistid = %s AND s.bestof = 0 "
"AND s.repeatshowid IS NULL "
"GROUP BY p.panelist, YEAR(s.showdate) "
"ORDER BY p.panelist ASC, YEAR(s.showdate) ASC;")
cursor.execute(query, (panelist_id, ))
results = cursor.fetchall()
cursor.close()
if not results:
return {}
for row in results:
years[row.year] = row.count
return years
def id_exists(self, panelist_id: int) -> bool:
"""Checks to see if a panelist ID exists.
:param panelist_id: Panelist ID
:return: True or False, based on whether the panelist ID exists
"""
if not valid_int_id(panelist_id):
return False
cursor = self.database_connection.cursor(dictionary=False)
query = ("SELECT panelistid FROM ww_panelists "
"WHERE panelistid = %s "
"LIMIT 1;")
cursor.execute(query, (panelist_id, ))
result = cursor.fetchone()
cursor.close()
return bool(result)
def retrieve_guest_info_by_id(self, show_id: int) -> List[Dict[str, Any]]:
"""Returns a list of dictionary objects containing Not My Job
guest information for the requested show ID.
:param show_id: Show ID
:return: Dictionary containing Not My Job guest information. If
Not My Job information could not be retrieved, an empty list
will be returned.
"""
if not valid_int_id(show_id):
return []
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT gm.guestid AS id, g.guest AS name, "
"g.guestslug AS slug, gm.guestscore AS score, "
"gm.exception AS score_exception "
"FROM ww_showguestmap gm "
"JOIN ww_guests g on g.guestid = gm.guestid "
"JOIN ww_shows s on s.showid = gm.showid "
"WHERE gm.showid = %s "
"ORDER by gm.showguestmapid ASC;")
cursor.execute(query, (show_id, ))
results = cursor.fetchall()
cursor.close()
if not results:
return []
guests = []
for guest in results:
guests.append({
"id":
guest.id,
"name":
guest.name,
"slug":
guest.slug if guest.slug else slugify(guest.name),
"score":
guest.score,
"score_exception":
bool(guest.score_exception),
})
return guests
def convert_id_to_slug(self, guest_id: int) -> Optional[str]:
"""Converts a guest's ID to the matching guest slug string.
:param guest_id: Guest ID
:return: Guest slug string, if a match is found
"""
if not valid_int_id(guest_id):
return None
cursor = self.database_connection.cursor(dictionary=False)
query = "SELECT guestslug FROM ww_guests " "WHERE guestid = %s " "LIMIT 1;"
cursor.execute(query, (guest_id, ))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
return None
def retrieve_scores_list_by_id(
self,
panelist_id: int,
) -> Dict[str, List]:
"""Returns a dictionary containing two lists, one with show
dates and one with corresponding scores for the requested
panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing a list show dates and a list
of scores. If panelist scores could not be retrieved, an
empty dictionary is returned.
"""
if not valid_int_id(panelist_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT s.showdate AS date, pm.panelistscore AS score "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s "
"AND s.bestof = 0 AND s.repeatshowid IS NULL "
"AND pm.panelistscore IS NOT NULL "
"ORDER BY s.showdate ASC;"
)
cursor.execute(query, (panelist_id,))
results = cursor.fetchall()
cursor.close()
if not results:
return {}
show_list = []
score_list = []
for shows in results:
show_list.append(shows.date.isoformat())
score_list.append(shows.score)
return {
"shows": show_list,
"scores": score_list,
}
def convert_id_to_date(self, show_id: int) -> Optional[str]:
"""Converts a show's ID to the matching show date.
:param show_id: Show ID
:return: Show date, if a match is found
"""
if not valid_int_id(show_id):
return None
cursor = self.database_connection.cursor(dictionary=False)
query = "SELECT showdate FROM ww_shows " "WHERE showid = %s " "LIMIT 1;"
cursor.execute(query, (show_id, ))
result = cursor.fetchone()
cursor.close()
if result:
return result[0].isoformat()
return None
def retrieve_by_id(self, location_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing location ID, venue,
city, state and slug string for the requested location ID.
:param location_id: Location ID
:return: Dictionary containing location information. If
location information could not be retrieved, an empty
dictionary is returned.
"""
if not valid_int_id(location_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT locationid AS id, city, state, venue, "
"locationslug AS slug "
"FROM ww_locations "
"WHERE locationid = %s "
"LIMIT 1;")
cursor.execute(query, (location_id, ))
result = cursor.fetchone()
cursor.close()
if not result:
return {}
return {
"id":
result.id,
"city":
result.city,
"state":
result.state,
"venue":
result.venue,
"slug":
result.slug if result.slug else self.utility.slugify_location(
location_id=result.id,
venue=result.venue,
city=result.city,
state=result.state,
),
}
def retrieve_details_by_id(self, host_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing host ID, name, slug
string and appearance information for the requested host ID.
:param host_id: Host ID
:return: Dictionary containing host information and their
appearances. If host information could be retrieved, an
empty dictionary is returned.
"""
if not valid_int_id(host_id):
return {}
info = self.retrieve_by_id(host_id)
if not info:
return {}
info["appearances"] = self.appearances.retrieve_appearances_by_id(
host_id)
return info
def retrieve_details_by_id(self, location_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing location ID, venue,
city, state, slug string and a list of recordings for the
requested location ID.
:param location_id: Location ID
:return: Dictionary containing location information and their
recordings. If location information could not be retrieved,
an empty dictionary is returned.
"""
if not valid_int_id(location_id):
return {}
info = self.retrieve_by_id(location_id)
if not info:
return {}
info["recordings"] = self.recordings.retrieve_recordings_by_id(
location_id)
return info
def convert_id_to_slug(self, scorekeeper_id: int) -> Optional[str]:
"""Converts a scorekeeper's ID to the matching scorekeeper slug
string value.
:param scorekeeper_id: Scorekeeper ID
:return: Scorekeeper slug string, if a match is found
"""
if not valid_int_id(scorekeeper_id):
return None
cursor = self.database_connection.cursor(dictionary=False)
query = ("SELECT scorekeeperslug FROM ww_scorekeepers "
"WHERE scorekeeperid = %s "
"LIMIT 1;")
cursor.execute(query, (scorekeeper_id, ))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
return None
def retrieve_details_by_id(self, show_id: int) -> Dict[str, Any]:
"""Returns a list of dictionary objects containing show ID,
show date, host, scorekeeper, panelist and guest information
for the requested show ID.
:param show_id: Show ID
:return: Dictionary containing show information and details. If
show information could not be retrieved, an empty dictionary
will be returned.
"""
if not valid_int_id(show_id):
return {}
info = self.info.retrieve_core_info_by_id(show_id)
if not info:
return {}
info["panelists"] = self.info.retrieve_panelist_info_by_id(show_id)
info["bluff"] = self.info.retrieve_bluff_info_by_id(show_id)
info["guests"] = self.info.retrieve_guest_info_by_id(show_id)
return info
def convert_id_to_slug(self, panelist_id: int) -> Optional[str]:
"""Converts a panelist's ID to the matching panelist slug
string value.
:param panelist_id: Panelist ID
:return: Panelist slug string, if a match is found
"""
if not valid_int_id(panelist_id):
return None
cursor = self.database_connection.cursor(dictionary=False)
query = ("SELECT panelistslug FROM ww_panelists "
"WHERE panelistid = %s "
"LIMIT 1;")
cursor.execute(query, (panelist_id, ))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
return None
def retrieve_details_by_id(self, scorekeeper_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing scorekeeper ID, name,
slug string and appearance information for the requested
scorekeeper ID.
:param scorekeeper_id: Scorekeeper ID
:return: Dictionary containing scorekeeper information and
their appearances. If scorekeeper information could not be
retrieved, an empty dictionary will be returned.
"""
if not valid_int_id(scorekeeper_id):
return {}
info = self.retrieve_by_id(scorekeeper_id)
if not info:
return {}
info["appearances"] = self.appearances.retrieve_appearances_by_id(
scorekeeper_id
)
return info
def retrieve_by_id(self, show_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing show ID, show date,
Best Of and Repeat Show information for the requested show ID.
:param show_id: Show ID
:return: Dictionary containing show information. If show
information could not be retrieved, an empty dictionary will
be returned.
"""
if not valid_int_id(show_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT showid AS id, showdate AS date, "
"bestof AS best_of, repeatshowid AS repeat_show_id "
"FROM ww_shows "
"WHERE showid = %s "
"LIMIT 1;")
cursor.execute(query, (show_id, ))
result = cursor.fetchone()
cursor.close()
if not result:
return {}
info = {
"id": result.id,
"date": result.date.isoformat(),
"best_of": bool(result.best_of),
"repeat_show": bool(result.repeat_show_id),
}
if result.repeat_show_id:
info["original_show_id"] = result.repeat_show_id
info["original_show_date"] = self.utility.convert_id_to_date(
result.repeat_show_id)
return info
def retrieve_scores_ordered_pair_by_id(
self, panelist_id: int
) -> List[Tuple[str, int]]:
"""Returns a list of tuples containing a show date and the
corresponding score for the requested panelist ID.
:param panelist_id: Panelist ID
:return: List of tuples containing show dates and scores. If
panelist scores could not be retrieved, an empty list is
returned.
"""
if not valid_int_id(panelist_id):
return []
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT s.showdate AS date, pm.panelistscore AS score "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s "
"AND s.bestof = 0 AND s.repeatshowid IS NULL "
"AND pm.panelistscore IS NOT NULL "
"ORDER BY s.showdate ASC;"
)
cursor.execute(query, (panelist_id,))
results = cursor.fetchall()
cursor.close()
if not results:
return []
scores = []
for show in results:
show_date = show.date.isoformat()
score = show.score
scores.append((show_date, score))
return scores
def retrieve_details_by_id(self, panelist_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing panelist ID, name, slug
string and appearance information for the requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing panelist information and their
appearances. If panelist information could not be retrieved,
an empty dictionary is returned.
"""
if not valid_int_id(panelist_id):
return {}
info = self.retrieve_by_id(panelist_id)
if not info:
return {}
info["statistics"] = self.statistics.retrieve_statistics_by_id(
panelist_id)
info["bluffs"] = self.statistics.retrieve_bluffs_by_id(panelist_id)
info["appearances"] = self.appearances.retrieve_appearances_by_id(
panelist_id)
return info
def retrieve_appearances_by_id(self, host_id: int) -> Dict[str, Any]:
"""Returns a list of dictionary objects containing appearance
information for the requested host ID.
:param host_id: Host ID
:return: Dictionary containing appearance counts and list of
appearances for a host. If host appearances could not be
retrieved, an empty dictionary is returned.
"""
if not valid_int_id(host_id):
return {}
cursor = self.database_connection.cursor(named_tuple=True)
query = ("SELECT ( "
"SELECT COUNT(hm.showid) FROM ww_showhostmap hm "
"JOIN ww_shows s ON s.showid = hm.showid "
"WHERE s.bestof = 0 AND s.repeatshowid IS NULL AND "
"hm.hostid = %s ) AS regular_shows, ( "
"SELECT COUNT(hm.showid) FROM ww_showhostmap hm "
"JOIN ww_shows s ON s.showid = hm.showid "
"WHERE hm.hostid = %s ) AS all_shows;")
cursor.execute(
query,
(
host_id,
host_id,
),
)
result = cursor.fetchone()
if result:
appearance_counts = {
"regular_shows": result.regular_shows,
"all_shows": result.all_shows,
}
else:
appearance_counts = {
"regular_shows": 0,
"all_shows": 0,
}
query = ("SELECT hm.showid AS show_id, s.showdate AS date, "
"s.bestof AS best_of, s.repeatshowid AS repeat_show_id, "
"hm.guest FROM ww_showhostmap hm "
"JOIN ww_hosts h ON h.hostid = hm.hostid "
"JOIN ww_shows s ON s.showid = hm.showid "
"WHERE hm.hostid = %s "
"ORDER BY s.showdate ASC;")
cursor.execute(query, (host_id, ))
results = cursor.fetchall()
cursor.close()
if results:
appearances = []
for appearance in results:
info = {
"show_id": appearance.show_id,
"date": appearance.date.isoformat(),
"best_of": bool(appearance.best_of),
"repeat_show": bool(appearance.repeat_show_id),
"guest": bool(appearance.guest),
}
appearances.append(info)
return {
"count": appearance_counts,
"shows": appearances,
}
else:
return {
"count": appearance_counts,
"shows": [],
}
def test_validation_valid_int_id(test_id: int):
"""Testing for :py:meth:`wwdtm.validation.valid_int_id`
:param test_id: ID to test ID validation
"""
assert valid_int_id(test_id), f"Provided ID {test_id} was not valid"
def test_validation_invalid_int_id(test_id: int):
"""Negative testing for :py:meth:`wwdtm.validation.valid_int_id`
:param test_id: ID to test failing ID validation
"""
assert not valid_int_id(test_id), f"Provided ID {test_id} was valid"
