def get_game_ids_for_category(category_name):
"""Get the ids of games in database."""
query = ("select game_id from games_categories "
"JOIN categories ON categories.id = games_categories.category_id "
"WHERE categories.name=?")
return [
game["game_id"]
for game in sql.db_query(settings.PGA_DB, query, (category_name, ))
]
def get_categories_in_game(game_id):
"""Get the categories of a game in database."""
query = (
"select categories.name from categories "
"JOIN games_categories ON categories.id = games_categories.category_id "
"JOIN games ON games.id = games_categories.game_id "
"WHERE games.id=?")
return [
category["name"]
for category in sql.db_query(settings.PGA_DB, query, (game_id, ))
]
def get_games_where(**conditions):
"""
Query games table based on conditions
Args:
conditions (dict): named arguments with each field matches its desired value.
Special values for field names can be used:
<field>__isnull will return rows where `field` is NULL if the value is True
<field>__not will invert the condition using `!=` instead of `=`
<field>__in will match rows for every value of `value`, which should be an iterable
Returns:
list: Rows matching the query
"""
query = "select * from games"
condition_fields = []
condition_values = []
for field, value in conditions.items():
field, *extra_conditions = field.split("__")
if extra_conditions:
extra_condition = extra_conditions[0]
if extra_condition == "isnull":
condition_fields.append("{} is {} null".format(
field, "" if value else "not"))
if extra_condition == "not":
condition_fields.append("{} != ?".format(field))
condition_values.append(value)
if extra_condition == "in":
if not hasattr(value, "__iter__"):
raise ValueError("Value should be an iterable (%s given)" %
value)
if len(value) > 999:
raise ValueError(
"SQLite limnited to a maximum of 999 parameters.")
if value:
condition_fields.append("{} in ({})".format(
field, ", ".join("?" * len(value)) or ""))
condition_values = list(chain(condition_values, value))
else:
condition_fields.append("{} = ?".format(field))
condition_values.append(value)
condition = " AND ".join(condition_fields)
if condition:
query = " WHERE ".join((query, condition))
else:
# Inspect and document why we should return
# an empty list when no condition is present.
return []
return sql.db_query(PGA_DB, query, tuple(condition_values))