Source code for tkp.db.generic

"""
A collection of generic functions used to generate SQL queries
and return data in an easy to use format such as dictionaries.
"""
import logging
import tkp.db


logger = logging.getLogger(__name__)


[docs]def columns_from_table(table, keywords=None, alias=None, where=None, order=None): """Obtain specific column (keywords) values from 'table', with kwargs limitations. A very simple helper function, that builds an SQL query to obtain the specified columns from 'table', and then executes that query. Optionally, the WHERE clause can be specified using the where dictionary. It returns a list of a dict (with the originally supplied keywords as dictionary keys), which can be empty. Example: >>> columns_from_table('image', \ keywords=['taustart_ts', 'tau_time', 'freq_eff', 'freq_bw'], where={'imageid': 1}) [{'freq_eff': 133984375.0, 'taustart_ts': datetime.datetime(2010, 10, 9, 9, 4, 2), 'tau_time': 14400.0, 'freq_bw': 1953125.0}] This builds the SQL query: "SELECT taustart_ts, tau_time, freq_eff, freq_bw FROM image WHERE imageid=1" This function is implemented mainly to abstract and hide the SQL functionality from the Python interface. Args: conn: database connection object table (string): database table name Kwargs: keywords (uple): column names to select from the table. None indicates all ('*') where (dict): where clause for the query, specified as a set of 'key = value' comparisons. Comparisons are and-ed together. Obviously, only 'is equal' comparisons are possible. alias (dict): Chosen aliases for the column names, used when constructing the returned list of dictionaries order (string): ORDER BY key. Returns: (tuple): list of dicts. Each dict contains the given keywords, or all if keywords=None. Each element of the list corresponds to a table row. """ if keywords is None: query = "SELECT * FROM " + table else: query = "SELECT " + ", ".join(keywords) + " FROM " + table if where is None: where = {} where_args = tuple(where.itervalues()) where = " AND ".join(["%s=%%s" % key for key in where.iterkeys()]) if where: query += " WHERE " + where if order: query += " ORDER BY " + order cursor = tkp.db.execute(query, where_args) results = cursor.fetchall() results_dict = convert_db_rows_to_dicts(results, alias_map=alias) return results_dict
[docs]def convert_db_rows_to_dicts(results, cursor_description=None, alias_map=None): """Converts a SQLAlchemy Resultproxy to a list of dicts. here for backward compatability reasons. A ResultProxy can already be used as a dict, but for now it is too much work to rewrite all code. Args: results: a SQLalchemy ResultProxy or just a list of rows cursor_description: Not required if results is a ResultProxy alias_map: a dict controlling the rewrite of specified columns """ dic_list = [] for row in results: if cursor_description: dict_row = dict(zip(cursor_description, row)) else: dict_row = dict(row.items()) if alias_map: for old_key, new_key in alias_map.items(): if old_key in row: dict_row[new_key] = dict_row.pop(old_key) dic_list.append(dict_row) return dic_list
[docs]def get_db_rows_as_dicts(cursor, alias_map=None): """Grab results of cursor.fetchall(), convert to a list of dictionaries.""" if hasattr(cursor, "description"): description = [col[0] for col in cursor.description] else: description = None return convert_db_rows_to_dicts(cursor.fetchall(), description, alias_map=alias_map)
[docs]def set_columns_for_table(table, data=None, where=None): """Set specific columns (keywords) for 'table', with 'where' limitations. A simple helper function, that builds an SQL query to update the specified columns given by data for 'table', and then executes that query. Optionally, the WHERE clause can be specified using the 'where' dictionary. The data argument is a dictionary with the names and corresponding values of the columns that need to be updated. """ query = "UPDATE " + table + " SET " + ", ".join(["%s=%%s" % key for key in data.iterkeys()]) if where is None: where = {} where_args = tuple(where.itervalues()) where = " AND ".join(["%s=%%s" % key for key in where.iterkeys()]) values = tuple(data.itervalues()) if where: query += " WHERE " + where tkp.db.execute(query, values + where_args, commit=True)