def get_runners(): with sql.db_cursor(PGA_DB) as cursor: query = ("select distinct runner from games " "where runner is not null order by runner") rows = cursor.execute(query) results = rows.fetchall() return [result[0] for result in results if result[0]]
def create_table(name, schema): fields = ", ".join([field_to_string(**f) for f in schema]) fields = "(%s)" % fields query = "CREATE TABLE IF NOT EXISTS %s %s" % (name, fields) LOGGER.debug("[PGAQuery] %s", query) with sql.db_cursor(PGA_DB) as cursor: cursor.execute(query)
def get_used_runners(): """Return a list of the runners in use by installed games.""" with sql.db_cursor(PGA_DB) as cursor: query = ("select distinct runner from games " "where runner is not null order by runner") rows = cursor.execute(query) results = rows.fetchall() return [result[0] for result in results if result[0]]
def get_used_platforms(): """Return a list of platforms currently in use""" with sql.db_cursor(PGA_DB) as cursor: query = ("select distinct platform from games " "where platform is not null and platform is not '' order by platform") rows = cursor.execute(query) results = rows.fetchall() return [result[0] for result in results if result[0]]
def get_used_runners_game_count(): """Return a dictionary listing for each runner in use, how many games are using it.""" with sql.db_cursor(PGA_DB) as cursor: query = ("select runner, count(*) from games " "where runner is not null " "group by runner " "order by runner") rows = cursor.execute(query) results = rows.fetchall() return {result[0]: result[1] for result in results if result[0]}
def get_used_platforms(): """Return a list of platforms currently in use""" with sql.db_cursor(PGA_DB) as cursor: query = ( "select distinct platform from games " "where platform is not null and platform is not '' order by platform" ) rows = cursor.execute(query) results = rows.fetchall() return [result[0] for result in results if result[0]]
def get_used_platforms_game_count(): """Return a dictionary listing for each platform in use, how many games are using it.""" with sql.db_cursor(PGA_DB) as cursor: # The extra check for 'installed is 1' is needed because # the platform lists don't show uninstalled games, but the platform of a game # is remembered even after the game is uninstalled. query = ("select platform, count(*) from games " "where platform is not null and platform is not '' and installed is 1 " "group by platform " "order by platform") rows = cursor.execute(query) results = rows.fetchall() return {result[0]: result[1] for result in results if result[0]}
def get_games(name_filter=None): """Get the list of every game in database.""" with sql.db_cursor(PGA_DB) as cursor: if name_filter is not None: query = "select * from games where name LIKE ?" rows = cursor.execute(query, (name_filter, )) else: query = "select * from games" rows = cursor.execute(query) results = rows.fetchall() column_names = [column[0] for column in cursor.description] game_list = [] for row in results: game_info = {} for index, column in enumerate(column_names): game_info[column] = row[index] game_list.append(game_info) return game_list
def get_schema(tablename): """ Fields: - position - name - type - not null - default - indexed """ tables = [] query = "pragma table_info('%s')" % tablename with sql.db_cursor(PGA_DB) as cursor: for row in cursor.execute(query).fetchall(): field = { 'name': row[1], 'type': row[2], 'not_null': row[3], 'default': row[4], 'indexed': row[5] } tables.append(field) return tables
def get_games(name_filter=None, filter_installed=False): """Get the list of every game in database.""" with sql.db_cursor(PGA_DB) as cursor: query = "select * from games" params = () filters = [] if name_filter: params = (name_filter, ) filters.append("name LIKE ?") if filter_installed: filters.append("installed = 1") if filters: query += " WHERE " + " AND ".join([f for f in filters]) rows = cursor.execute(query, params) results = rows.fetchall() column_names = [column[0] for column in cursor.description] game_list = [] for row in results: game_info = {} for index, column in enumerate(column_names): game_info[column] = row[index] game_list.append(game_info) return game_list
def get_games(name_filter=None, filter_installed=False): """Get the list of every game in database.""" with sql.db_cursor(PGA_DB) as cursor: query = "select * from games" params = () filters = [] if name_filter: params = (name_filter, ) filters.append("name LIKE ?") if filter_installed: filters.append("installed = 1") if filters: query += " WHERE " + " AND ".join([f for f in filters]) query += " ORDER BY slug" rows = cursor.execute(query, params) results = rows.fetchall() column_names = [column[0] for column in cursor.description] game_list = [] for row in results: game_info = {} for index, column in enumerate(column_names): game_info[column] = row[index] game_list.append(game_info) return game_list
def add_field(tablename, field): query = "ALTER TABLE %s ADD COLUMN %s %s" % ( tablename, field['name'], field['type'] ) with sql.db_cursor(PGA_DB) as cursor: cursor.execute(query)
def read_sources(): with sql.db_cursor(PGA_DB) as cursor: rows = cursor.execute("select uri from sources") results = rows.fetchall() return [row[0] for row in results]
def create(): """Create the local PGA database.""" logger.debug("Running CREATE statement...") with sql.db_cursor(PGA_DB) as cursor: create_games(cursor) create_sources(cursor)
def remove_category_from_game(game_id, category_id): """Remove a category from a game""" query = "DELETE FROM games_categories WHERE category_id=? AND game_id=?" with sql.db_cursor(PGA_DB) as cursor: sql.cursor_execute(cursor, query, (category_id, game_id))
def get_table_length(table='games'): with sql.db_cursor(PGA_DB) as cursor: query = "select count() from games" cursor.execute(query) return cursor.fetchone()[0]
def migrate(): """Convert the playtime to float from text, to allow sorting correctly""" with db_cursor(PGA_DB) as cursor: for sql_statement in SQL_STATEMENTS: cursor_execute(cursor, sql_statement)
def add_field(tablename, field): query = "ALTER TABLE %s ADD COLUMN %s %s" % (tablename, field['name'], field['type']) with sql.db_cursor(PGA_DB) as cursor: cursor.execute(query)