def test_recipe_with_select_from(self):
from recipe import Dimension, Metric
shelf = Shelf(
{
"region": Dimension(StateFact.census_region_name),
"pop": Metric(func.sum(Census.pop2000)),
},
select_from=join(Census, StateFact,
Census.state == StateFact.name),
)
assert shelf.Meta.select_from is not None
r = (Recipe(shelf=shelf,
session=self.session).dimensions("region").metrics("pop"))
assert r._select_from is not None
assert (
r.to_sql() == """SELECT state_fact.census_region_name AS region,
sum(census.pop2000) AS pop
FROM census
JOIN state_fact ON census.state = state_fact.name
GROUP BY region""")
assert (r.dataset.tsv == """region\tpop\tregion_id\r
Northeast\t609480\tNortheast\r
South\t5685230\tSouth\r
""")
assert len(r.all()) == 2
def test_multirole_dimension_with_lookup(self):
"""Create a dimension with extra roles and lookup"""
d = Dimension(
MyTable.last,
id_expression=MyTable.first,
age_expression=MyTable.age,
id="d",
lookup={},
lookup_default="DEFAULT",
)
recipe = self.recipe().metrics("age").dimensions(d)
assert (recipe.to_sql() == """SELECT foo.first AS d_id,
foo.last AS d_raw,
foo.age AS d_age,
sum(foo.age) AS age
FROM foo
GROUP BY d_id,
d_raw,
d_age""")
assert recipe.all()[0].d_raw == "fred"
assert recipe.all()[0].d == "DEFAULT"
assert recipe.all()[0].d_id == "hi"
assert recipe.all()[0].d_age == 10
assert recipe.all()[0].age == 10
assert recipe.all()[1].d_raw == "there"
assert recipe.all()[1].d == "DEFAULT"
assert recipe.all()[1].d_id == "hi"
assert recipe.all()[1].d_age == 5
assert recipe.all()[1].age == 5
assert recipe.stats.rows == 2
def test_update_key_value(self):
""" Shelves can be built with key_values and updated """
new_shelf = Shelf(squee=Dimension(MyTable.first))
assert len(self.shelf) == 3
self.shelf.update(new_shelf)
assert len(self.shelf) == 4
assert isinstance(self.shelf.get("squee"), Dimension)
def test_find(self):
""" Find ingredients on the shelf """
ingredient = self.shelf.find("first", Dimension)
assert ingredient.id == "first"
# Raise if the wrong type
with pytest.raises(Exception):
ingredient = self.shelf.find("first", Metric)
# Raise if key not present in shelf
with pytest.raises(Exception):
ingredient = self.shelf.find("foo", Dimension)
# Raise if key is not an ingredient or string
with pytest.raises(Exception):
ingredient = self.shelf.find(2.0, Dimension)
with pytest.raises(Exception):
ingredient = self.shelf.find("foo", Dimension)
with pytest.raises(Exception):
ingredient = self.shelf.find(2.0, Dimension)
with pytest.raises(Exception):
ingredient = self.shelf.find("foo", Dimension)
with pytest.raises(Exception):
ingredient = self.shelf.find("foo", Dimension)
self.shelf["foo"] = Dimension(MyTable.last)
ingredient = self.shelf.find("last", Dimension)
assert ingredient.id == "last"
def test_add_to_shelf(self):
""" We can add an ingredient to a shelf """
with pytest.raises(BadRecipe):
ingredient = self.shelf.find("foo", Dimension)
self.shelf["foo"] = Dimension(MyTable.last)
ingredient = self.shelf.find("last", Dimension)
assert ingredient.id == "last"
def test_nested_recipe(self):
recipe = self.recipe().metrics("age").dimensions("last")
from recipe import Metric, Dimension
subq = recipe.subquery(name="anon")
nested_shelf = Shelf({
"age": Metric(func.sum(subq.c.age)),
"last": Dimension(subq.c.last)
})
r = (Recipe(shelf=nested_shelf,
session=self.session).dimensions("last").metrics("age"))
assert (r.to_sql() == """SELECT anon.last AS last,
sum(anon.age) AS age
FROM
(SELECT foo.last AS last,
sum(foo.age) AS age
FROM foo
GROUP BY last) AS anon
GROUP BY last""")
assert len(r.all()) == 2
fips_state = Column("fips_state", String())
assoc_press = Column("assoc_press", String())
standard_federal_region = Column("standard_federal_region", String())
census_region = Column("census_region", String())
census_region_name = Column("census_region_name", String())
census_division = Column("census_division", String())
census_division_name = Column("census_division_name", String())
circuit_court = Column("circuit_court", String())
__tablename__ = "state_fact"
__table_args__ = {"extend_existing": True}
mytable_shelf = Shelf({
"first":
Dimension(MyTable.first),
"last":
Dimension(MyTable.last),
"firstlast":
Dimension(MyTable.last, id_expression=MyTable.first),
"age":
Metric(func.sum(MyTable.age)),
})
mytable_extrarole_shelf = Shelf({
"first":
Dimension(MyTable.first),
"last":
Dimension(MyTable.last),
"firstlastage":
Dimension(MyTable.last,
def test_recipe_multi_tables(self):
dim = Dimension(Scores.username)
recipe = self.recipe().dimensions("last", dim).metrics("age")
with pytest.raises(BadRecipe):
recipe.all()
def test_order_bys(self):
recipe = self.recipe().metrics("age").dimensions("last").order_by(
"last")
assert (recipe.to_sql() == """SELECT foo.last AS last,
sum(foo.age) AS age
FROM foo
GROUP BY last
ORDER BY last""")
assert recipe.all()[0].last == "fred"
assert recipe.all()[0].age == 10
assert recipe.stats.rows == 2
recipe = self.recipe().metrics("age").dimensions("last").order_by(
"age")
assert (recipe.to_sql() == """SELECT foo.last AS last,
sum(foo.age) AS age
FROM foo
GROUP BY last
ORDER BY age""")
assert recipe.all()[0].last == "there"
assert recipe.all()[0].age == 5
assert recipe.stats.rows == 2
recipe = self.recipe().metrics("age").dimensions("last").order_by(
"-age")
assert (recipe.to_sql() == """SELECT foo.last AS last,
sum(foo.age) AS age
FROM foo
GROUP BY last
ORDER BY age DESC""")
assert recipe.all()[0].last == "fred"
assert recipe.all()[0].age == 10
assert recipe.stats.rows == 2
# Idvalue dimension
recipe = (self.recipe().metrics("age").dimensions(
"firstlast").order_by("firstlast"))
assert (recipe.to_sql() == """SELECT foo.first AS firstlast_id,
foo.last AS firstlast,
sum(foo.age) AS age
FROM foo
GROUP BY firstlast_id,
firstlast
ORDER BY firstlast,
firstlast_id""")
recipe = (self.recipe().metrics("age").dimensions(
"firstlast").order_by("-firstlast"))
assert (recipe.to_sql() == """SELECT foo.first AS firstlast_id,
foo.last AS firstlast,
sum(foo.age) AS age
FROM foo
GROUP BY firstlast_id,
firstlast
ORDER BY firstlast DESC,
firstlast_id DESC""")
# Dimensions can define their own ordering
d = Dimension(
MyTable.last,
id="d",
id_expression=MyTable.first,
order_by_expression=func.upper(MyTable.last),
)
recipe = self.recipe().metrics("age").dimensions(d).order_by(d)
assert (recipe.to_sql() == """SELECT foo.first AS d_id,
foo.last AS d,
upper(foo.last) AS d_order_by,
sum(foo.age) AS age
FROM foo
GROUP BY d_id,
d,
d_order_by
ORDER BY d_order_by,
d,
d_id""")
oven.engine.execute(TABLEDEF)
oven.engine.execute(
"insert into foo values ('hi', 'there', 5), ('hi', 'fred', 10)")
class MyTable(Base):
first = Column("first", String(), primary_key=True)
last = Column("last", String())
age = Column("age", Integer())
__tablename__ = "foo"
__table_args__ = {"extend_existing": True}
mytable_shelf = Shelf({
"first": Dimension(MyTable.first),
"last": Dimension(MyTable.last),
"age": Metric(func.sum(MyTable.age)),
})
class TestRecipeIngredients(object):
def setup(self):
# create a Session
self.session = oven.Session()
self.shelf = mytable_shelf
def recipe(self, **kwargs):
return Recipe(shelf=self.shelf,
session=self.session,
dynamic_extensions=["caching"],
def test_update_key_value_direct(self):
""" Shelves can be updated directly with key_value"""
assert len(self.shelf) == 3
self.shelf.update(squee=Dimension(MyTable.first))
assert len(self.shelf) == 4
assert isinstance(self.shelf.get("squee"), Dimension)
def test_update(self):
""" Shelves can be updated with other shelves """
new_shelf = Shelf({"squee": Dimension(MyTable.first)})
assert len(self.shelf) == 3
self.shelf.update(new_shelf)
assert len(self.shelf) == 4
