def user_removes_columns(sheet, schema, request):
"""Deletes user defined columns
Gather the columns the user wants to delete via the request and
create a table which will "house" those columns.
We can then delete the entry in sheets_schema and then have
sqlalchemy-migrate drop the column via our "housing" table.
Afterwards, we iterate over all the columns and adjust the sequence numbers
before we actually delete the column in our schema table.
"""
col_to_delete_id = request.form.getlist("to_delete")[0]
col_to_delete = session.query(
models.Sheets_Schema).filter_by(id=col_to_delete_id).one()
# drop the given column from the given table
sqlalchemy.Column("col_{}".format(col_to_delete_id)).drop(
sqlalchemy.Table("table_{}".format(sheet.id), metadata))
for col in session.query(
models.Sheets_Schema).filter_by(sheet_id=sheet.id).all():
if col.sequence_number > col_to_delete.sequence_number:
col.sequence_number -= 1
col_to_delete = session.query(
models.Sheets_Schema).filter_by(id=col_to_delete_id).delete()
session.commit()
def user_deletes_table(sheets, form):
"""Deletes a user generated tabled
Based on the id of the sheet, remove the corresponding generated table,
and the entry in 'Sheets' as well. Deletes the column definitions automatically.
"""
sheet_to_drop_id = form.delete_table_id.data
generate_table = sqlalchemy.Table("table_{}".format(sheet_to_drop_id),
metadata).drop()
session.query(models.Sheets).filter_by(id=sheet_to_drop_id).delete()
session.commit()
def get(table, response_col, target_col=None, target=None, start=None,
end=None, sort=False):
t = tables[table]
ts = getattr(t, table_cols[table]['timestamp'])
col = getattr(t, response_col)
q = session.query(ts, col)
if start:
q = q.filter(ts > start)
if end:
q = q.filter(ts < end)
if target_col and target:
tc = getattr(t, target_col)
q = q.filter(tc == target)
if sort:
q = q.order_by(ts)
res = q.all()
return res
def user_alters_column(edit_form, sheet, request):
"""Alters user picked columns
Currently just queries the database and alters the name
of the column. More work will be needed to properly adjust
column types (ensure data correction, leave as-is, delete, etc)
"""
col_to_alter_id = request.form.getlist("col_to_alter")[0]
col = session.query(
models.Sheets_Schema).filter_by(id=col_to_alter_id).one()
col.column_name = edit_form.column_name.data
col.column_type = edit_form.types.data
session.commit()
def get_comparison_ts(table, target_col, groups, resp, sort=False):
t = tables[table]
ts = getattr(t, table_cols[table]['timestamp'])
group_col = getattr(t, target_col)
resp_col = getattr(t, resp)
q = session.query(ts, func.avg(resp_col)).filter(group_col.in_(groups))\
.group_by(ts)
if sort:
q = q.order_by(ts)
return q.all()
def groupdo(table, yn, *aggs, **kwargs):
'''
Returns result of an aggregation function applied to the entire population.
Parameters
----------
table : str
Tempus table name.
yn : str
Response variable to send to the aggregate function.
aggs : list of strings
Aggregation function names, from list of valid MySQL aggregation
functions.
tstart : datetime.datetime, optional
Only include entries after `tstart` (the default is to include
everything)
tend : datetime.datetime, optional
Only include entries before `tend` (the default is to include
everything)
Returns
-------
list
List of responses to the SQL query. Since most aggregation functions
only return one value, this is often a list of a single tuple, the
first entry of which is the result.
See Also
--------
groupby: Performs aggregation function within groups.
Notes
-----
Valid agg function strings:
AVG -- Return the average value of the argument
BIT_AND -- Return bitwise and
BIT_OR -- Return bitwise or
BIT_XOR -- Return bitwise xor
COUNT(DISTINCT)Return the count of a number of different values
COUNT -- Return a count of the number of rows returned
GROUP_CONCAT -- Return a concatenated string
MAX -- Return the maximum value
MIN -- Return the minimum value
STD -- Return the population standard deviation
STDDEV_POP -- Return the population standard deviation
STDDEV_SAMP -- Return the sample standard deviation
STDDEV -- Return the population standard deviation
SUM -- Return the sum
VAR_POP -- Return the population standard variance
VAR_SAMP -- Return the sample variance
VARIANCE -- Return the population standard variance
'''
logger.debug('Executing groupdo: kwargs={}'.format(kwargs))
t = tables[table]
fs = [getattr(func, agg) for agg in aggs]
y = getattr(t, yn)
tstart = kwargs.get('tstart', None)
tend = kwargs.get('tend', None)
conds = []
if tstart and tend:
ts = getattr(t, table_cols[table]['timestamp'])
conds = [(ts > tstart), (ts < tend)]
q = session.query(*[f(y) for f in fs])
for cond in conds:
q = q.filter(cond)
aggs = q.all()
return aggs
def get_groups(table, group_col):
t = tables[table]
col = getattr(t, group_col)
q = session.query(func.distinct(col)).filter(col != None)
return q.all()
def groupby(table, xs, ys, agg, **kwargs):
'''
Returns result of an aggregation function applied to groups.
Parameters
----------
table : str
Tempus table name.
ss : str or iterable of str
Variable(s) to group by.
ys : str or iterable of str
Response variable(s) to send to the aggregate function.
agg : str
Aggregation function name, from list of valid MySQL aggregation
functions.
tstart : datetime.datetime, optional
Only include entries after `tstart` (the default is to include
everything)
tend : datetime.datetime, optional
Only include entries before `tend` (the default is to include
everything)
Returns
-------
dict
Dictionary mapping group identifiers (column names) to a dictionary of
aggregated response variables.
See Also
--------
groupdo: Performs aggregation function across entire table.
Notes
-----
Valid agg function strings:
AVG -- Return the average value of the argument
BIT_AND -- Return bitwise and
BIT_OR -- Return bitwise or
BIT_XOR -- Return bitwise xor
COUNT(DISTINCT)Return the count of a number of different values
COUNT -- Return a count of the number of rows returned
GROUP_CONCAT -- Return a concatenated string
MAX -- Return the maximum value
MIN -- Return the minimum value
STD -- Return the population standard deviation
STDDEV_POP -- Return the population standard deviation
STDDEV_SAMP -- Return the sample standard deviation
STDDEV -- Return the population standard deviation
SUM -- Return the sum
VAR_POP -- Return the population standard variance
VAR_SAMP -- Return the sample variance
VARIANCE -- Return the population standard variance
'''
logger.debug('Executing groupby: kwargs={}'.format(kwargs))
if isinstance(xs, basestring):
xs = [xs]
if isinstance(ys, basestring):
ys = [ys]
t = tables[table]
columns = [getattr(t, x) for x in xs]
yvars = [getattr(t, y) for y in ys]
f = getattr(func, agg)
tstart = kwargs.get('tstart', None)
tend = kwargs.get('tend', None)
conds = []
if tstart and tend:
ts = getattr(t, table_cols[table]['timestamp'])
conds = [(ts > tstart), (ts < tend)]
funcs = [f(y) for y in yvars]
q = session.query(*(funcs + columns))
for col in columns:
q = q.group_by(col)
for cond in conds:
q = q.filter(cond)
counts = q.all()
countdict = {}
for row in counts:
yval = row[:len(ys)]
xs = tuple(row[len(ys):])
countdict[xs] = dict(zip(ys, yval))
return countdict
