def retrieve(self, table, cols, col_rules):
""" Retrieves column values from a single table based on a given filtering rule.
Example:
<pre lang="python">
my_db.retrieve(some_table_table,["num1","num2"],{"remainder_div_3":"{}==1 or {}==2", "sum":"{}<200"})
</pre>
will retrieve:
<pre lang="python">
columns called "num1" and "num2" from some table. That have value 1 or 2 in the ramainder_div_3 column. Column
named "sum" of which would be less than 200. All columns are combined with an "AND" statement.
</pre>
:param table: string (name of the table to retrieve from)
:param columns: list of strings (names of the columns to retrieve)
:param column_rules: dictionary of rules that will be evaluated
:return:
Nested list in which is entry in a list a a column with filtered requested values
"""
# todo: add string comp support
cursor = self.conn.cursor()
# from the table get all the columns to retrieve
sql_cmd = "select " + " ,".join(cols) + " from \"" + table + "\""
cursor.execute(sql_cmd)
sel_sets = cursor.fetchall()
if len(col_rules)==0:
sel_vals = sel_sets
else:
# from the table select all the columns to filter for
sql_cmd = "select " + ", ".join([key for key in col_rules]) + " from \"" + table + "\""
cursor.execute(sql_cmd)
filter_sets = cursor.fetchall()
# repeat every argument number of times it appears in the selection
mult = [len(re.findall("{}", col_rules[key])) for key in col_rules]
def _repeat_vals(vals, repeats):
rep_vals = []
[[rep_vals.append(vals[i]) for _ in range(repeats[i])] for i in range(len(col_rules))]
return rep_vals
filter_sets = [_repeat_vals(set, mult) for set in filter_sets]
# evaluate every row to get a boolean mask of examples
rule_tmp = "(" + ") and (".join([col_rules[key] for key in col_rules]) + ")"
sel_mask = [eval(rule_tmp.format(*val_set)) for val_set in filter_sets]
# apply a boolean mask to take only entries that fit the selection rule
sel_sets = list(compress(sel_sets, sel_mask))
sel_vals = sel_sets
#sel_vals = [list(x) for x in zip(*sel_sets)]
return sel_vals
评论列表
文章目录
