def entries(self,
conditions="1=1",
order_by=None,
limit=None,
offset=0,
step=10000,
fields=None):
""" Generate a fully denormalized view of the entries on this
table. This view is nested so that each dimension will be a hash
of its attributes.
This is somewhat similar to the entries collection in the fully
denormalized schema before OpenSpending 0.11 (MongoDB).
"""
if not self.is_generated:
return
if fields is None:
fields = self.fields
joins = self.alias
for d in self.dimensions:
if d in fields:
joins = d.join(joins)
selects = [f.selectable for f in fields] + [self.alias.c.id]
# enforce stable sorting:
if order_by is None:
order_by = [self.alias.c.id.asc()]
for i in count():
qoffset = offset + (step * i)
qlimit = step
if limit is not None:
qlimit = min(limit - (step * i), step)
if qlimit <= 0:
break
query = db.select(selects,
conditions,
joins,
order_by=order_by,
use_labels=True,
limit=qlimit,
offset=qoffset)
rp = self.bind.execute(query)
first_row = True
while True:
row = rp.fetchone()
if row is None:
if first_row:
return
break
first_row = False
yield decode_row(row, self)
def entries(self, conditions="1=1", order_by=None, limit=None,
offset=0, step=10000, fields=None):
""" Generate a fully denormalized view of the entries on this
table. This view is nested so that each dimension will be a hash
of its attributes.
This is somewhat similar to the entries collection in the fully
denormalized schema before OpenSpending 0.11 (MongoDB).
"""
if not self.is_generated:
return
if fields is None:
fields = self.fields
joins = self.alias
for d in self.dimensions:
if d in fields:
joins = d.join(joins)
selects = [f.selectable for f in fields] + [self.alias.c.id]
# enforce stable sorting:
if order_by is None:
order_by = [self.alias.c.id.asc()]
for i in count():
qoffset = offset + (step * i)
qlimit = step
if limit is not None:
qlimit = min(limit - (step * i), step)
if qlimit <= 0:
break
query = select(selects, conditions, joins, order_by=order_by,
use_labels=True, limit=qlimit, offset=qoffset)
rp = self.bind.execute(query)
first_row = True
while True:
row = rp.fetchone()
if row is None:
if first_row:
return
break
first_row = False
yield decode_row(row, self)
def aggregate(self,
measures=['amount'],
drilldowns=[],
cuts=[],
page=1,
pagesize=10000,
order=[]):
""" Query the dataset for a subset of cells based on cuts and
drilldowns. It returns a structure with a list of drilldown items
and a summary about the slice cutted by the query.
``measures``
The numeric units to be aggregated over, defaults to
[``amount``]. (type: `list`)
``drilldowns``
Dimensions to drill down to. (type: `list`)
``cuts``
Specification what to cut from the cube. This is a
`list` of `two-tuples` where the first item is the dimension
and the second item is the value to cut from. It is turned into
a query where multible cuts for the same dimension are combined
to an *OR* query and then the queries for the different
dimensions are combined to an *AND* query.
``page``
Page the drilldown result and return page number *page*.
type: `int`
``pagesize``
Page the drilldown result into page of size *pagesize*.
type: `int`
``order``
Sort the result based on the dimension *sort_dimension*.
This may be `None` (*default*) or a `list` of two-`tuples`
where the first element is the *dimension* and the second
element is the order (`False` for ascending, `True` for
descending).
Type: `list` of two-`tuples`.
Raises:
:exc:`ValueError`
If a cube is not yet computed. Call :meth:`compute` to compute
the cube.
:exc:`KeyError`
If a drilldown, cut or order dimension is not part of this
cube or the order dimensions are not a subset of the drilldown
dimensions.
Returns: A `dict` containing the drilldown and the summary:
{"drilldown": [
{"num_entries": 5545,
"amount": 41087379002.0,
"cofog1": {"description": "",
"label": "Economic affairs"}},
... ]
"summary": {"amount": 7353306450299.0,
"num_entries": 133612}}
"""
# Get the joins (aka alias) and the dataset
joins = alias = self.alias
dataset = self
# Aggregation fields are all of the measures, so we create individual
# summary fields with the sum function of SQLAlchemy
fields = [db.func.sum(alias.c[m]).label(m) for m in measures]
# We append an aggregation field that counts the number of entries
fields.append(db.func.count(alias.c.id).label("entries"))
# Create a copy of the statistics fields (for later)
stats_fields = list(fields)
# Create label map for time columns (year and month) for lookup
# since they are found under the time attribute
labels = {
'year': dataset['time']['year'].column_alias.label('year'),
'month': dataset['time']['yearmonth'].column_alias.label('month'),
}
# Get the dimensions we're interested in. These would be the drilldowns
# and the cuts. For compound dimensions we are only interested in the
# most significant one (e.g. for from.name we're interested in from)
dimensions = drilldowns + [k for k, v in cuts]
dimensions = [d.split('.')[0] for d in dimensions]
# Loop over the dimensions as a set (to avoid multiple occurances)
for dimension in set(dimensions):
# If the dimension is year or month we're interested in 'time'
if dimension in labels:
dimension = 'time'
# If the dimension table isn't in the automatic joins we add it
if dimension not in [c.table.name for c in joins.columns]:
joins = dataset[dimension].join(joins)
# Drilldowns are performed using group_by SQL functions
group_by = []
for key in drilldowns:
# If drilldown is in labels we append its mapped column to fields
if key in labels:
column = labels[key]
group_by.append(column)
fields.append(column)
else:
# Get the column from the dataset
column = dataset.key(key)
# If the drilldown is a compound dimension or the columns table
# is in the joins we're already fetching the column so we just
# append it to fields and the group_by
if '.' in key or column.table == alias:
fields.append(column)
group_by.append(column)
else:
# If not we add the column table to the fields and add all
# of that tables columns to the group_by
fields.append(column.table)
for col in column.table.columns:
group_by.append(col)
# Cuts are managed using AND statements and we use a dict with set as
# the default value to create the filters (cut on various values)
conditions = db.and_()
filters = defaultdict(set)
for key, value in cuts:
# If the key is in labels (year or month) we get the mapped column
# else we get the column from the dataset
if key in labels:
column = labels[key]
else:
column = dataset.key(key)
# We add the value to the set for that particular column
filters[column].add(value)
# Loop over the columns in the filter and add that to the conditions
# For every value in the set we create and OR statement so we get e.g.
# year=2007 AND (from.who == 'me' OR from.who == 'you')
for attr, values in filters.items():
conditions.append(db.or_(*[attr == v for v in values]))
# Ordering can be set by a parameter or ordered by measures by default
order_by = []
# If no order is defined we default to order of the measures in the
# order they occur (furthest to the left is most significant)
if order is None or not len(order):
order = [(m, True) for m in measures]
# We loop through the order list to add the columns themselves
for key, direction in order:
# If it's a part of the measures we have to order by the
# aggregated values (the sum of the measure)
if key in measures:
column = db.func.sum(alias.c[key]).label(key)
# If it's in the labels we have to get the mapped column
elif key in labels:
column = labels[key]
# ...if not we just get the column from the dataset
else:
column = dataset.key(key)
# We append the column and set the direction (True == descending)
order_by.append(column.desc() if direction else column.asc())
# query 1: get overall sums.
# Here we use the stats_field we saved earlier
query = db.select(stats_fields, conditions, joins)
rp = dataset.bind.execute(query)
# Execute the query and turn them to a list so we can pop the
# entry count and then zip the measurements and the totals together
stats = list(rp.fetchone())
num_entries = stats.pop()
total = zip(measures, stats)
# query 2: get total count of drilldowns
if len(group_by):
# Select 1 for each group in the group_by and count them
query = db.select(['1'], conditions, joins, group_by=group_by)
query = db.select([db.func.count('1')], '1=1', query.alias('q'))
rp = dataset.bind.execute(query)
num_drilldowns, = rp.fetchone()
else:
# If there are no drilldowns we still have to do one
num_drilldowns = 1
# The drilldown result list
drilldown = []
# The offset in the db, based on the page and pagesize (we have to
# modify it since page counts starts from 1 but we count from 0
offset = int((page - 1) * pagesize)
# query 3: get the actual data
query = db.select(fields,
conditions,
joins,
order_by=order_by,
group_by=group_by,
use_labels=True,
limit=pagesize,
offset=offset)
rp = dataset.bind.execute(query)
while True:
# Get each row in the db result and append it, decoded, to the
# drilldown result. The decoded version is a json represenation
row = rp.fetchone()
if row is None:
break
result = decode_row(row, dataset)
drilldown.append(result)
# Create the summary based on the stats_fields and other things
# First we add a the total for each measurement in the root of the
# summary (watch out!) and then we add various other, self-explanatory
# statistics such as page, number of entries. The currency value is
# strange since it's redundant for multiple measures but is left as is
# for backwards compatibility
summary = {key: value for (key, value) in total}
summary.update({
'num_entries':
num_entries,
'currency': {m: dataset.currency
for m in measures},
'num_drilldowns':
num_drilldowns,
'page':
page,
'pages':
int(math.ceil(num_drilldowns / float(pagesize))),
'pagesize':
pagesize
})
return {'drilldown': drilldown, 'summary': summary}
def aggregate(self, measures=['amount'], drilldowns=[], cuts=[],
page=1, pagesize=10000, order=[]):
""" Query the dataset for a subset of cells based on cuts and
drilldowns. It returns a structure with a list of drilldown items
and a summary about the slice cutted by the query.
``measures``
The numeric units to be aggregated over, defaults to
[``amount``]. (type: `list`)
``drilldowns``
Dimensions to drill down to. (type: `list`)
``cuts``
Specification what to cut from the cube. This is a
`list` of `two-tuples` where the first item is the dimension
and the second item is the value to cut from. It is turned into
a query where multible cuts for the same dimension are combined
to an *OR* query and then the queries for the different
dimensions are combined to an *AND* query.
``page``
Page the drilldown result and return page number *page*.
type: `int`
``pagesize``
Page the drilldown result into page of size *pagesize*.
type: `int`
``order``
Sort the result based on the dimension *sort_dimension*.
This may be `None` (*default*) or a `list` of two-`tuples`
where the first element is the *dimension* and the second
element is the order (`False` for ascending, `True` for
descending).
Type: `list` of two-`tuples`.
Raises:
:exc:`ValueError`
If a cube is not yet computed. Call :meth:`compute` to compute
the cube.
:exc:`KeyError`
If a drilldown, cut or order dimension is not part of this
cube or the order dimensions are not a subset of the drilldown
dimensions.
Returns: A `dict` containing the drilldown and the summary:
{"drilldown": [
{"num_entries": 5545,
"amount": 41087379002.0,
"cofog1": {"description": "",
"label": "Economic affairs"}},
... ]
"summary": {"amount": 7353306450299.0,
"num_entries": 133612}}
"""
# Get the joins (aka alias) and the dataset
joins = alias = self.alias
dataset = self
# Aggregation fields are all of the measures, so we create individual
# summary fields with the sum function of SQLAlchemy
fields = [func.sum(alias.c[m]).label(m) for m in measures]
# We append an aggregation field that counts the number of entries
fields.append(func.count(alias.c.id).label("entries"))
# Create a copy of the statistics fields (for later)
stats_fields = list(fields)
# Create label map for time columns (year and month) for lookup
# since they are found under the time attribute
labels = {
'year': dataset['time']['year'].column_alias.label('year'),
'month': dataset['time']['yearmonth'].column_alias.label('month'),
}
# Get the dimensions we're interested in. These would be the drilldowns
# and the cuts. For compound dimensions we are only interested in the
# most significant one (e.g. for from.name we're interested in from)
dimensions = drilldowns + [k for k, v in cuts]
dimensions = [d.split('.')[0] for d in dimensions]
# Loop over the dimensions as a set (to avoid multiple occurances)
for dimension in set(dimensions):
# If the dimension is year or month we're interested in 'time'
if dimension in labels:
dimension = 'time'
# If the dimension table isn't in the automatic joins we add it
if dimension not in [c.table.name for c in joins.columns]:
joins = dataset[dimension].join(joins)
# Drilldowns are performed using group_by SQL functions
group_by = []
for key in drilldowns:
# If drilldown is in labels we append its mapped column to fields
if key in labels:
column = labels[key]
group_by.append(column)
fields.append(column)
else:
# Get the column from the dataset
column = dataset.key(key)
# If the drilldown is a compound dimension or the columns table
# is in the joins we're already fetching the column so we just
# append it to fields and the group_by
if '.' in key or column.table == alias:
fields.append(column)
group_by.append(column)
else:
# If not we add the column table to the fields and add all
# of that tables columns to the group_by
fields.append(column.table)
for col in column.table.columns:
group_by.append(col)
# Cuts are managed using AND statements and we use a dict with set as
# the default value to create the filters (cut on various values)
conditions = and_()
filters = defaultdict(set)
for key, value in cuts:
# If the key is in labels (year or month) we get the mapped column
# else we get the column from the dataset
if key in labels:
column = labels[key]
else:
column = dataset.key(key)
# We add the value to the set for that particular column
filters[column].add(value)
# Loop over the columns in the filter and add that to the conditions
# For every value in the set we create and OR statement so we get e.g.
# year=2007 AND (from.who == 'me' OR from.who == 'you')
for attr, values in filters.items():
conditions.append(or_(*[attr == v for v in values]))
# Ordering can be set by a parameter or ordered by measures by default
order_by = []
# If no order is defined we default to order of the measures in the
# order they occur (furthest to the left is most significant)
if order is None or not len(order):
order = [(m, True) for m in measures]
# We loop through the order list to add the columns themselves
for key, direction in order:
# If it's a part of the measures we have to order by the
# aggregated values (the sum of the measure)
if key in measures:
column = func.sum(alias.c[key]).label(key)
# If it's in the labels we have to get the mapped column
elif key in labels:
column = labels[key]
# ...if not we just get the column from the dataset
else:
column = dataset.key(key)
# We append the column and set the direction (True == descending)
order_by.append(column.desc() if direction else column.asc())
# query 1: get overall sums.
# Here we use the stats_field we saved earlier
query = select(stats_fields, conditions, joins)
rp = dataset.bind.execute(query)
# Execute the query and turn them to a list so we can pop the
# entry count and then zip the measurements and the totals together
stats = list(rp.fetchone())
num_entries = stats.pop()
total = zip(measures, stats)
# query 2: get total count of drilldowns
if len(group_by):
# Select 1 for each group in the group_by and count them
query = select(['1'], conditions, joins, group_by=group_by)
query = select([func.count('1')], '1=1', query.alias('q'))
rp = dataset.bind.execute(query)
num_drilldowns, = rp.fetchone()
else:
# If there are no drilldowns we still have to do one
num_drilldowns = 1
# The drilldown result list
drilldown = []
# The offset in the db, based on the page and pagesize (we have to
# modify it since page counts starts from 1 but we count from 0
offset = int((page - 1) * pagesize)
# query 3: get the actual data
query = select(fields, conditions, joins, order_by=order_by,
group_by=group_by, use_labels=True,
limit=pagesize, offset=offset)
rp = dataset.bind.execute(query)
while True:
# Get each row in the db result and append it, decoded, to the
# drilldown result. The decoded version is a json represenation
row = rp.fetchone()
if row is None:
break
result = decode_row(row, dataset)
drilldown.append(result)
# Create the summary based on the stats_fields and other things
# First we add a the total for each measurement in the root of the
# summary (watch out!) and then we add various other, self-explanatory
# statistics such as page, number of entries. The currency value is
# strange since it's redundant for multiple measures but is left as is
# for backwards compatibility
summary = {key: value for (key, value) in total}
summary.update({
'num_entries': num_entries,
'currency': {m: dataset.currency for m in measures},
'num_drilldowns': num_drilldowns,
'page': page,
'pages': int(math.ceil(num_drilldowns / float(pagesize))),
'pagesize': pagesize
})
return {'drilldown': drilldown, 'summary': summary}
def aggregate(self, measure='amount', drilldowns=None, cuts=None,
page=1, pagesize=10000, order=None):
""" Query the dataset for a subset of cells based on cuts and
drilldowns. It returns a structure with a list of drilldown items
and a summary about the slice cutted by the query.
``measure``
The numeric unit to be aggregated over, defaults to ``amount``.
``drilldowns``
Dimensions to drill down to. (type: `list`)
``cuts``
Specification what to cut from the cube. This is a
`list` of `two-tuples` where the first item is the dimension
and the second item is the value to cut from. It is turned into
a query where multible cuts for the same dimension are combined
to an *OR* query and then the queries for the different
dimensions are combined to an *AND* query.
``page``
Page the drilldown result and return page number *page*.
type: `int`
``pagesize``
Page the drilldown result into page of size *pagesize*.
type: `int`
``order``
Sort the result based on the dimension *sort_dimension*.
This may be `None` (*default*) or a `list` of two-`tuples`
where the first element is the *dimension* and the second
element is the order (`False` for ascending, `True` for
descending).
Type: `list` of two-`tuples`.
Raises:
:exc:`ValueError`
If a cube is not yet computed. Call :meth:`compute` to compute
the cube.
:exc:`KeyError`
If a drilldown, cut or order dimension is not part of this
cube or the order dimensions are not a subset of the drilldown
dimensions.
Returns: A `dict` containing the drilldown and the summary::
{"drilldown": [
{"num_entries": 5545,
"amount": 41087379002.0,
"cofog1": {"description": "",
"label": "Economic affairs"}},
... ]
"summary": {"amount": 7353306450299.0,
"num_entries": 133612}}
"""
cuts = cuts or []
drilldowns = drilldowns or []
order = order or []
joins = alias = self.alias
dataset = self
fields = [db.func.sum(alias.c[measure]).label(measure),
db.func.count(alias.c.id).label("entries")]
stats_fields = list(fields)
labels = {
'year': dataset['time']['year'].column_alias.label('year'),
'month': dataset['time']['yearmonth'].column_alias.label('month'),
}
dimensions = drilldowns + [k for k, v in cuts]
dimensions = [d.split('.')[0] for d in dimensions]
for dimension in set(dimensions):
if dimension in labels:
dimension = 'time'
if dimension not in [c.table.name for c in joins.columns]:
joins = dataset[dimension].join(joins)
group_by = []
for key in drilldowns:
if key in labels:
column = labels[key]
group_by.append(column)
fields.append(column)
else:
column = dataset.key(key)
if '.' in key or column.table == alias:
fields.append(column)
group_by.append(column)
else:
fields.append(column.table)
for col in column.table.columns:
group_by.append(col)
conditions = db.and_()
filters = defaultdict(set)
for key, value in cuts:
if key in labels:
column = labels[key]
else:
column = dataset.key(key)
filters[column].add(value)
for attr, values in filters.items():
conditions.append(db.or_(*[attr == v for v in values]))
order_by = []
if order is None or not len(order):
order = [(measure, True)]
for key, direction in order:
if key == measure:
column = db.func.sum(alias.c[measure]).label(measure)
elif key in labels:
column = labels[key]
else:
column = dataset.key(key)
order_by.append(column.desc() if direction else column.asc())
# query 1: get overall sums.
query = db.select(stats_fields, conditions, joins)
rp = dataset.bind.execute(query)
total, num_entries = rp.fetchone()
# query 2: get total count of drilldowns
if len(group_by):
query = db.select(['1'], conditions, joins, group_by=group_by)
query = db.select([db.func.count('1')], '1=1', query.alias('q'))
rp = dataset.bind.execute(query)
num_drilldowns, = rp.fetchone()
else:
num_drilldowns = 1
drilldown = []
offset = int((page - 1) * pagesize)
# query 3: get the actual data
query = db.select(fields, conditions, joins, order_by=order_by,
group_by=group_by, use_labels=True,
limit=pagesize, offset=offset)
rp = dataset.bind.execute(query)
while True:
row = rp.fetchone()
if row is None:
break
result = decode_row(row, dataset)
drilldown.append(result)
return {
'drilldown': drilldown,
'summary': {
measure: total,
'num_entries': num_entries,
'currency': {measure: dataset.currency},
'num_drilldowns': num_drilldowns,
'page': page,
'pages': int(math.ceil(num_drilldowns / float(pagesize))),
'pagesize': pagesize
}
}
def aggregate(self,
measure='amount',
drilldowns=None,
cuts=None,
page=1,
pagesize=10000,
order=None):
""" Query the dataset for a subset of cells based on cuts and
drilldowns. It returns a structure with a list of drilldown items
and a summary about the slice cutted by the query.
``measure``
The numeric unit to be aggregated over, defaults to ``amount``.
``drilldowns``
Dimensions to drill down to. (type: `list`)
``cuts``
Specification what to cut from the cube. This is a
`list` of `two-tuples` where the first item is the dimension
and the second item is the value to cut from. It is turned into
a query where multible cuts for the same dimension are combined
to an *OR* query and then the queries for the different
dimensions are combined to an *AND* query.
``page``
Page the drilldown result and return page number *page*.
type: `int`
``pagesize``
Page the drilldown result into page of size *pagesize*.
type: `int`
``order``
Sort the result based on the dimension *sort_dimension*.
This may be `None` (*default*) or a `list` of two-`tuples`
where the first element is the *dimension* and the second
element is the order (`False` for ascending, `True` for
descending).
Type: `list` of two-`tuples`.
Raises:
:exc:`ValueError`
If a cube is not yet computed. Call :meth:`compute` to compute
the cube.
:exc:`KeyError`
If a drilldown, cut or order dimension is not part of this
cube or the order dimensions are not a subset of the drilldown
dimensions.
Returns: A `dict` containing the drilldown and the summary::
{"drilldown": [
{"num_entries": 5545,
"amount": 41087379002.0,
"cofog1": {"description": "",
"label": "Economic affairs"}},
... ]
"summary": {"amount": 7353306450299.0,
"num_entries": 133612}}
"""
cuts = cuts or []
drilldowns = drilldowns or []
order = order or []
joins = alias = self.alias
dataset = self
fields = [
db.func.sum(alias.c[measure]).label(measure),
db.func.count(alias.c.id).label("entries")
]
stats_fields = list(fields)
labels = {
'year': dataset['time']['year'].column_alias.label('year'),
'month': dataset['time']['yearmonth'].column_alias.label('month'),
}
dimensions = drilldowns + [k for k, v in cuts]
dimensions = [d.split('.')[0] for d in dimensions]
for dimension in set(dimensions):
if dimension in labels:
dimension = 'time'
if dimension not in [c.table.name for c in joins.columns]:
joins = dataset[dimension].join(joins)
group_by = []
for key in drilldowns:
if key in labels:
column = labels[key]
group_by.append(column)
fields.append(column)
else:
column = dataset.key(key)
if '.' in key or column.table == alias:
fields.append(column)
group_by.append(column)
else:
fields.append(column.table)
for col in column.table.columns:
group_by.append(col)
conditions = db.and_()
filters = defaultdict(set)
for key, value in cuts:
if key in labels:
column = labels[key]
else:
column = dataset.key(key)
filters[column].add(value)
for attr, values in filters.items():
conditions.append(db.or_(*[attr == v for v in values]))
order_by = []
if order is None or not len(order):
order = [(measure, True)]
for key, direction in order:
if key == measure:
column = db.func.sum(alias.c[measure]).label(measure)
elif key in labels:
column = labels[key]
else:
column = dataset.key(key)
order_by.append(column.desc() if direction else column.asc())
# query 1: get overall sums.
query = db.select(stats_fields, conditions, joins)
rp = dataset.bind.execute(query)
total, num_entries = rp.fetchone()
# query 2: get total count of drilldowns
if len(group_by):
query = db.select(['1'], conditions, joins, group_by=group_by)
query = db.select([db.func.count('1')], '1=1', query.alias('q'))
rp = dataset.bind.execute(query)
num_drilldowns, = rp.fetchone()
else:
num_drilldowns = 1
drilldown = []
offset = int((page - 1) * pagesize)
# query 3: get the actual data
query = db.select(fields,
conditions,
joins,
order_by=order_by,
group_by=group_by,
use_labels=True,
limit=pagesize,
offset=offset)
rp = dataset.bind.execute(query)
while True:
row = rp.fetchone()
if row is None:
break
result = decode_row(row, dataset)
drilldown.append(result)
return {
'drilldown': drilldown,
'summary': {
measure: total,
'num_entries': num_entries,
'currency': {
measure: dataset.currency
},
'num_drilldowns': num_drilldowns,
'page': page,
'pages': int(math.ceil(num_drilldowns / float(pagesize))),
'pagesize': pagesize
}
}