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