def __init__(
self,
connect_dict: Optional[Dict[str, Any]] = None,
database_connection: Optional[connect] = None,
):
"""Class initialization method."""
if connect_dict:
self.connect_dict = connect_dict
self.database_connection = connect(**connect_dict)
elif database_connection:
if not database_connection.is_connected():
database_connection.reconnect()
self.database_connection = database_connection
self.utility = PanelistUtility(database_connection=self.database_connection)
class PanelistScores:
"""This class contains functions used to retrieve panelist scores
from a copy of the Wait Wait Stats database.
:param connect_dict: Dictionary containing database connection
settings as required by mysql.connector.connect
:param database_connection: mysql.connector.connect database
connection
"""
def __init__(
self,
connect_dict: Optional[Dict[str, Any]] = None,
database_connection: Optional[connect] = None,
):
"""Class initialization method."""
if connect_dict:
self.connect_dict = connect_dict
self.database_connection = connect(**connect_dict)
elif database_connection:
if not database_connection.is_connected():
database_connection.reconnect()
self.database_connection = database_connection
self.utility = PanelistUtility(database_connection=self.database_connection)
@lru_cache(typed=True)
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
@lru_cache(typed=True)
def retrieve_scores_by_slug(self, panelist_slug: str) -> List[int]:
"""Returns a list of panelist scores for appearances for the
requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: List containing panelist scores. If panelist scores
could not be retrieved, an empty list is returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return []
return self.retrieve_scores_by_id(id_)
@lru_cache(typed=True)
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()),
}
@lru_cache(typed=True)
def retrieve_scores_grouped_list_by_slug(
self, panelist_slug: str
) -> Dict[str, List[int]]:
"""Returns a panelist's score grouping for the requested
panelist slug string.
:param panelist_slug: Panelist slug string
: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.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_scores_grouped_list_by_id(id_)
@lru_cache(typed=True)
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())
@lru_cache(typed=True)
def retrieve_scores_grouped_ordered_pair_by_slug(
self,
panelist_slug: str,
) -> 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 slug string.
:param panelist_slug: Panelist slug string
:return: List of tuples containing scores and score counts. If
panelist scores could not be retrieved, an empty list is
returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return []
return self.retrieve_scores_grouped_ordered_pair_by_id(id_)
@lru_cache(typed=True)
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,
}
@lru_cache(typed=True)
def retrieve_scores_list_by_slug(
self,
panelist_slug: str,
) -> Dict[str, List]:
"""Returns a dictionary containing two lists, one with show
dates and one with corresponding scores for the requested
panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing a list show dates and a list
of scores. If panelist scores could not be retrieved, an
empty dictionary is returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_scores_list_by_id(id_)
@lru_cache(typed=True)
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
@lru_cache(typed=True)
def retrieve_scores_ordered_pair_by_slug(
self,
panelist_slug: str,
) -> List[Tuple[str, int]]:
"""Returns a list of tuples containing a show date and the
corresponding score for the requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: List of tuples containing show dates and scores. If
panelist scores could not be retrieved, an empty list is
returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return []
return self.retrieve_scores_ordered_pair_by_id(id_)
class Panelist:
"""This class contains functions used to retrieve panelist data
from a copy of the Wait Wait Stats database.
:param connect_dict: Dictionary containing database connection
settings as required by mysql.connector.connect
:param database_connection: mysql.connector.connect database
connection
"""
def __init__(
self,
connect_dict: Optional[Dict[str, Any]] = None,
database_connection: Optional[connect] = None,
):
"""Class initialization method."""
if connect_dict:
self.connect_dict = connect_dict
self.database_connection = connect(**connect_dict)
elif database_connection:
if not database_connection.is_connected():
database_connection.reconnect()
self.database_connection = database_connection
self.appearances = PanelistAppearances(
database_connection=self.database_connection)
self.statistics = PanelistStatistics(
database_connection=self.database_connection)
self.utility = PanelistUtility(
database_connection=self.database_connection)
def retrieve_all(self) -> List[Dict[str, Any]]:
"""Returns a list of dictionary objects containing panelist ID,
name and slug string for all panelists.
:return: List of all panelists and their corresponding
information. If panelists could not be retrieved, an empty
list is returned.
"""
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT panelistid AS id, panelist AS name, panelistslug AS slug, "
"panelistgender AS gender "
"FROM ww_panelists "
"WHERE panelistslug != 'multiple' "
"ORDER BY panelist ASC;")
cursor.execute(query)
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),
"gender":
row.gender,
})
return panelists
def retrieve_all_details(self) -> List[Dict[str, Any]]:
"""Returns a list of dictionary objects containing panelist ID,
name, slug string and appearance information for all panelists.
:return: List of all panelists and their corresponding
information and appearances. If panelists could not be
retrieved, an empty list is returned.
"""
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT panelistid AS id, panelist AS name, panelistslug AS slug, "
"panelistgender AS gender "
"FROM ww_panelists "
"WHERE panelistslug != 'multiple' "
"ORDER BY panelist ASC;")
cursor.execute(query)
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),
"gender":
row.gender,
"statistics":
self.statistics.retrieve_statistics_by_id(row.id),
"bluffs":
self.statistics.retrieve_bluffs_by_id(row.id),
"appearances":
self.appearances.retrieve_appearances_by_id(row.id),
})
return panelists
def retrieve_all_ids(self) -> List[int]:
"""Returns a list of all panelist IDs from the database, sorted
by panelist name.
:return: List of all panelist IDs. If panelist IDs could not be
retrieved, an empty list is returned.
"""
cursor = self.database_connection.cursor(dictionary=False)
query = ("SELECT panelistid FROM ww_panelists "
"WHERE panelistslug != 'multiple' "
"ORDER BY panelist ASC;")
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
if not results:
return []
return [v[0] for v in results]
def retrieve_all_slugs(self) -> List[str]:
"""Returns a list of all panelist slug strings from the
database, sorted by panelist name.
:return: List of all panelist slug strings. If panelist slug
strings could not be retrieved, an empty list is returned.
"""
cursor = self.database_connection.cursor(dictionary=False)
query = ("SELECT panelistslug FROM ww_panelists "
"WHERE panelistslug != 'multiple' "
"ORDER BY panelist ASC;")
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
if not results:
return []
return [v[0] for v in results]
@lru_cache(typed=True)
def retrieve_by_id(self, panelist_id: int) -> Dict[str, Any]:
"""Returns a dictionary object containing panelist ID, name and
slug string for the requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing panelist information. If panelist
information 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 panelistid AS id, panelist AS name, panelistslug AS slug, "
"panelistgender AS gender "
"FROM ww_panelists "
"WHERE panelistid = %s "
"LIMIT 1;")
cursor.execute(query, (panelist_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,
}
@lru_cache(typed=True)
def retrieve_by_slug(self, panelist_slug: str) -> Dict[str, Any]:
"""Returns a dictionary object containing panelist ID, name and
slug string for the requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing panelist information. If panelist
information could not be retrieved, an empty dictionary is
returned.
"""
try:
slug = panelist_slug.strip()
if not slug:
return {}
except AttributeError:
return {}
id_ = self.utility.convert_slug_to_id(slug)
if not id_:
return {}
return self.retrieve_by_id(id_)
@lru_cache(typed=True)
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
@lru_cache(typed=True)
def retrieve_details_by_slug(self, panelist_slug: str) -> Dict[str, Any]:
"""Returns a dictionary object containing panelist ID, name, slug
string and appearance information for the requested Panelist slug
string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing panelist information and their
appearances. If panelist information could not be retrieved,
an empty dictionary is returned.
"""
try:
slug = panelist_slug.strip()
if not slug:
return {}
except AttributeError:
return {}
id_ = self.utility.convert_slug_to_id(slug)
if not id_:
return {}
return self.retrieve_details_by_id(id_)
class PanelistAppearances:
"""This class contains functions that retrieve panelist appearance
information from a copy of the Wait Wait Stats database.
:param connect_dict: Dictionary containing database connection
settings as required by mysql.connector.connect
:param database_connection: mysql.connector.connect database
connection
"""
def __init__(
self,
connect_dict: Optional[Dict[str, Any]] = None,
database_connection: Optional[connect] = None,
):
"""Class initialization method."""
if connect_dict:
self.connect_dict = connect_dict
self.database_connection = connect(**connect_dict)
elif database_connection:
if not database_connection.is_connected():
database_connection.reconnect()
self.database_connection = database_connection
self.utility = PanelistUtility(
database_connection=self.database_connection)
@lru_cache(typed=True)
def retrieve_appearances_by_id(self, panelist_id: int) -> Dict[str, Any]:
"""Returns a list of dictionary objects containing appearance
information for the requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing appearance counts and list of
appearances for a panelist. If panelist appearances 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 ( "
"SELECT COUNT(pm.showid) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE s.bestof = 0 AND s.repeatshowid IS NULL AND "
"pm.panelistid = %s ) AS regular_shows, ( "
"SELECT COUNT(pm.showid) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE pm.panelistid = %s ) AS all_shows, ( "
"SELECT COUNT(pm.panelistid) FROM ww_showpnlmap pm "
"JOIN ww_shows s ON pm.showid = s.showid "
"WHERE pm.panelistid = %s AND s.bestof = 0 AND "
"s.repeatshowid IS NULL "
"AND pm.panelistscore IS NOT NULL ) "
"AS shows_with_scores;")
cursor.execute(
query,
(
panelist_id,
panelist_id,
panelist_id,
),
)
result = cursor.fetchone()
if result:
appearance_counts = {
"regular_shows": result.regular_shows,
"all_shows": result.all_shows,
"shows_with_scores": result.shows_with_scores,
}
else:
appearance_counts = {
"regular_shows": 0,
"all_shows": 0,
"shows_with_scores": 0,
}
query = (
"SELECT MIN(s.showid) AS first_id, MIN(s.showdate) AS first, "
"MAX(s.showid) AS most_recent_id, MAX(s.showdate) AS most_recent "
"FROM ww_showpnlmap pm "
"JOIN ww_shows s ON s.showid = pm.showid "
"WHERE s.bestof = 0 AND s.repeatshowid IS NULL "
"AND pm.panelistid = %s "
"ORDER BY s.showdate ASC;")
cursor.execute(query, (panelist_id, ))
result = cursor.fetchone()
if result and result.first_id:
first = {
"show_id": result.first_id,
"show_date": result.first.isoformat(),
}
most_recent = {
"show_id": result.most_recent_id,
"show_date": result.most_recent.isoformat(),
}
milestones = {
"first": first,
"most_recent": most_recent,
}
appearance_info = {
"milestones": milestones,
}
else:
appearance_info = {
"milestones": None,
}
query = ("SELECT pm.showid AS show_id, s.showdate AS date, "
"s.bestof AS best_of, s.repeatshowid AS repeat_show_id, "
"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.panelistid = %s "
"ORDER BY s.showdate ASC;")
cursor.execute(query, (panelist_id, ))
results = cursor.fetchall()
cursor.close()
if result:
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),
"lightning_round_start":
appearance.start,
"lightning_round_correct":
appearance.correct,
"score":
appearance.score,
"rank":
appearance.pnl_rank
if appearance.pnl_rank is not None else None,
}
appearances.append(info)
appearance_info["count"] = appearance_counts
appearance_info["shows"] = appearances
else:
appearance_info["count"] = appearance_counts
appearance_info["shows"] = []
return appearance_info
@lru_cache(typed=True)
def retrieve_appearances_by_slug(self,
panelist_slug: str) -> Dict[str, Any]:
"""Returns a list of dictionary objects containing appearance
information for the requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing appearance counts and list of
appearances for a panelist. If panelist appearances could
not be retrieved, an empty dictionary is returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_appearances_by_id(id_)
@lru_cache(typed=True)
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
@lru_cache(typed=True)
def retrieve_yearly_appearances_by_slug(
self, panelist_slug: str) -> Dict[int, int]:
"""Returns a dictionary containing panelist appearances broken
down by year, for the requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing scoring breakdown by year. If
panelist appearances could not be retrieved, an empty
dictionary is returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_yearly_appearances_by_id(id_)
class PanelistStatistics:
"""This class contains functions used to retrieve data from a copy
of the Wait Wait Stats database and calculate statistics for
panelists.
:param connect_dict: Dictionary containing database connection
settings as required by mysql.connector.connect
:param database_connection: mysql.connector.connect database
connection
"""
def __init__(
self,
connect_dict: Optional[Dict[str, Any]] = None,
database_connection: Optional[connect] = None,
):
"""Class initialization method."""
if connect_dict:
self.connect_dict = connect_dict
self.database_connection = connect(**connect_dict)
elif database_connection:
if not database_connection.is_connected():
database_connection.reconnect()
self.database_connection = database_connection
self.scores = PanelistScores(
database_connection=self.database_connection)
self.utility = PanelistUtility(
database_connection=self.database_connection)
@lru_cache(typed=True)
def retrieve_bluffs_by_id(self, panelist_id: int) -> Dict[str, int]:
"""Returns a dictionary containing the number of chosen Bluffs
and correct Bluffs for the requested panelist ID.
:param panelist_id: Panelist ID
:return: Dictionary containing panelist Bluff counts. If
panelist Bluff counts could not be returned, an empty
dictionary will be returned.
"""
cursor = self.database_connection.cursor(named_tuple=True)
query = (
"SELECT ( "
"SELECT COUNT(blm.chosenbluffpnlid) FROM ww_showbluffmap blm "
"JOIN ww_shows s ON s.showid = blm.showid "
"WHERE s.repeatshowid IS NULL AND blm.chosenbluffpnlid = %s "
") AS chosen, ( "
"SELECT COUNT(blm.correctbluffpnlid) FROM ww_showbluffmap blm "
"JOIN ww_shows s ON s.showid = blm.showid "
"WHERE s.repeatshowid IS NULL AND blm.correctbluffpnlid = %s "
") AS correct;")
cursor.execute(
query,
(
panelist_id,
panelist_id,
),
)
result = cursor.fetchone()
cursor.close()
if not result:
return {}
return {
"chosen": result.chosen,
"correct": result.correct,
}
@lru_cache(typed=True)
def retrieve_bluffs_by_slug(self, panelist_slug: str) -> Dict[str, int]:
"""Returns a dictionary containing the number of chosen Bluffs
and correct Bluffs for the requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing panelist Bluff counts. If
panelist Bluff counts could not be returned, an empty
dictionary will be returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_bluffs_by_id(id_)
@lru_cache(typed=True)
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,
}
@lru_cache(typed=True)
def retrieve_rank_info_by_slug(self, panelist_slug: str) -> Dict[str, int]:
"""Returns a dictionary with ranking information for the
requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing panelist ranking information. If
panelist ranking information could not be returned, an empty
dictionary will be returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_rank_info_by_id(id_)
@lru_cache(typed=True)
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,
}
@lru_cache(typed=True)
def retrieve_statistics_by_slug(self,
panelist_slug: str) -> Dict[str, Any]:
"""Returns a dictionary containing panelist statistics, ranking
data, and scoring data for the requested panelist slug string.
:param panelist_slug: Panelist slug string
:return: Dictionary containing panelist statistics. If panelist
statistics could not be returned, an empty dictionary will
be returned.
"""
id_ = self.utility.convert_slug_to_id(panelist_slug)
if not id_:
return {}
return self.retrieve_statistics_by_id(id_)