tkp.db.generic – generic data-query generation functions

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

tkp.db.generic.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.

Parameters:

• conn – database connection object
• table (string) – database table name

Kwargs:

keywords (list): 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:list of dicts. Each dict contains the given keywords, or all if keywords=None. Each element of the list corresponds to a table row. Return type:list

tkp.db.generic.convert_db_rows_to_dicts(results, cursor_description, alias_map=None)

Takes a list of rows as returned by cursor.fetchall(), converts to a list of dictionaries.

tkp.db.generic.get_db_rows_as_dicts(cursor, alias_map=None)

Grab results of cursor.fetchall(), convert to a list of dictionaries.

tkp.db.generic.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.