def _upsert(self, bind, data, unique_columns): key = db.and_(*[self.table.c[c]==data.get(c) for c in unique_columns]) q = self.table.update(key, data) if bind.execute(q).rowcount == 0: q = self.table.insert(data) rs = bind.execute(q) return rs.inserted_primary_key[0] else: q = self.table.select(key) row = bind.execute(q).fetchone() return row['id']
def aggregate(self, metric='amount', drilldowns=None, cuts=None, page=1, pagesize=10000, order=None): cuts = cuts or [] drilldowns = drilldowns or [] joins = self.alias for dimension in set(drilldowns + [k for k,v in cuts]): joins = self[dimension.split('.')[0]].join(joins) group_by = [] fields = [db.func.sum(self.alias.c.amount).label(metric), db.func.count(self.alias.c.id).label("entries")] for key in drilldowns: column = self.key(key) if '.' in key or column.table == self.alias: fields.append(column) else: fields.append(column.table) group_by.append(column) conditions = db.and_() filters = defaultdict(set) for key, value in cuts: column = self.key(key) filters[column].add(value) for attr, values in filters.items(): conditions.append(db.or_(*[attr==v for v in values])) order_by = [] for key, direction in order or []: # TODO: handle case in which order criterion is not joined. column = self.key(key) order_by.append(column.desc() if direction else column.asc()) query = db.select(fields, conditions, joins, order_by=order_by or [metric + ' desc'], group_by=group_by, use_labels=True) #print query summary = {metric: 0.0, 'num_entries': 0} drilldown = [] rp = self.bind.execute(query) while True: row = rp.fetchone() if row is None: break result = {} for key, value in row.items(): if key == metric: summary[metric] += value if key == 'entries': summary['num_entries'] += value if '_' in key: dimension, attribute = key.split('_', 1) if dimension == 'entry': result[attribute] = value else: if not dimension in result: result[dimension] = {} result[dimension][attribute] = value else: if key == 'entries': key = 'num_entries' result[key] = value drilldown.append(result) offset = ((page-1)*pagesize) return {'drilldown': drilldown[offset:offset+pagesize], 'summary': summary}