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}
