def test_select__multiple_subqueries(self):
subquery0 = Query.from_(self.table_abc).select("foo")
subquery1 = Query.from_(self.table_efg).select("bar")
q = Query \
.from_(subquery0) \
.from_(subquery1) \
.select(subquery0.foo, subquery1.bar)
self.assertEqual('SELECT "sq0"."foo","sq1"."bar" '
'FROM (SELECT "foo" FROM "abc") "sq0",'
'(SELECT "bar" FROM "efg") "sq1"', str(q))
def test_select__subquery(self):
subquery = Query.from_(self.table_abc).select("*")
q = Query.from_(subquery) \
.select(subquery.foo, subquery.bar)
self.assertEqual('SELECT "sq0"."foo","sq0"."bar" '
'FROM (SELECT * FROM "abc") "sq0"', str(q))
def test_select_from_nested_query_with_join(self):
subquery1 = Query.from_(self.table_abc).select(
self.table_abc.foo,
fn.Sum(self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'),
).groupby(
self.table_abc.foo
)
subquery2 = Query.from_(self.table_efg).select(
self.table_efg.foo.as_('foo_two'),
self.table_efg.bar,
)
query = Query.from_(subquery1).select(
subquery1.foo, subquery1.fizzbuzz
).join(subquery2).on(subquery1.foo == subquery2.foo_two).select(
subquery2.foo_two, subquery2.bar
)
self.assertEqual('SELECT '
'"sq0"."foo","sq0"."fizzbuzz",'
'"sq1"."foo_two","sq1"."bar" '
'FROM ('
'SELECT '
'"foo",SUM("fizz"+"buzz") "fizzbuzz" '
'FROM "abc" '
'GROUP BY "foo"'
') "sq0" JOIN ('
'SELECT '
'"foo" "foo_two","bar" '
'FROM "efg"'
') "sq1" ON "sq0"."foo"="sq1"."foo_two"', str(query))
def test_where__in(self):
main, nested = Tables('abc', 'efg')
q = Query.from_(main).select('*').where(main.foo.isin(
Query.from_(nested).select(nested.foo).where(nested.bar == 0)
))
self.assertEqual('SELECT * FROM abc WHERE foo IN (SELECT foo FROM efg WHERE bar=0)', str(q))
def test_union_as_subquery(self):
abc, efg = Tables('abc', 'efg')
hij = Query.from_(abc).select(abc.t).union(Query.from_(efg).select(efg.t))
q = Query.from_(hij).select(fn.Avg(hij.t))
self.assertEqual('SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") UNION (SELECT "t" FROM "efg")) "sq0"',
str(q))
def test_inner_join(self):
query = Query.from_(self.table0).join(self.table1).on(
self.table0.foo == self.table1.bar).select('*')
query_explicit = Query.from_(self.table0).join(self.table1, how=JoinType.inner).on(
self.table0.foo == self.table1.bar).select('*')
self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query))
self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query_explicit))
def test_left_join(self):
query2 = Query.from_(self.table0).join(self.table1).on(
self.table0.foo == self.table1.bar).select('*')
q2 = Query.from_(self.table0).join(self.table1, how=JoinType.left).on(
self.table0.foo == self.table1.bar).select('*')
self.assertEqual('SELECT * FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(query2))
self.assertEqual('SELECT * FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(q2))
def test_immutable__tables(self):
query1 = Query.from_(self.table0).select(self.table0.foo)
query2 = Query.from_(self.table0).join(self.table1).on(self.table0.foo == self.table1.bar).select(
self.table0.foo,
self.table1.buz)
self.assertEqual('SELECT "t0"."foo","t1"."buz" FROM "abc" "t0" '
'JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(query2))
self.assertEqual('SELECT "foo" FROM "abc"', str(query1))
def test_where__equality(self):
subquery = Query.from_('efg').select('fiz').where(F('buz') == 0)
query = Query.from_(self.table_abc).select(
self.table_abc.foo,
self.table_abc.bar
).where(self.table_abc.bar == subquery)
self.assertEqual('SELECT "foo","bar" FROM "abc" '
'WHERE "bar"=(SELECT "fiz" FROM "efg" WHERE "buz"=0)', str(query))
def test_cross_join_on_subquery(self):
table_a, table_b = Tables('a', 'b')
q_a = Query.from_(table_a).select('*')
q_b = Query.from_(table_b).select('*').join(q_a).cross().select('*')
self.assertEqual(
'SELECT * FROM "b" CROSS JOIN (SELECT * FROM "a") "sq0"',
str(q_b)
)
def test_immutable__tables(self):
query1 = Query.from_(self.table_abc).select(self.table_abc.foo)
query2 = Query.from_(self.table_abc).join(self.table_efg).on(self.table_abc.foo == self.table_efg.bar).select(
self.table_abc.foo,
self.table_efg.buz)
self.assertEqual('SELECT "abc"."foo","efg"."buz" FROM "abc" '
'JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query2))
self.assertEqual('SELECT "foo" FROM "abc"', str(query1))
def test_join(self):
subquery = Query.from_('efg').select('fiz', 'buz').where(F('buz') == 0)
q = Query.from_(self.t).join(subquery).on(
self.t.bar == subquery.buz
).select(self.t.foo, subquery.fiz)
self.assertEqual('SELECT t0.foo,t1.fiz FROM abc t0 '
'JOIN (SELECT fiz,buz FROM efg WHERE buz=0) t1 '
'ON t0.bar=t1.buz', str(q))
def test_join(self):
subquery = Query.from_('efg').select('fiz', 'buz').where(F('buz') == 0)
q = Query.from_(self.table_abc).join(subquery).on(
self.table_abc.bar == subquery.buz
).select(self.table_abc.foo, subquery.fiz)
self.assertEqual('SELECT "abc"."foo","sq0"."fiz" FROM "abc" '
'JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=0) "sq0" '
'ON "abc"."bar"="sq0"."buz"', str(q))
def test_union_with_order_by_with_aliases(self):
query1 = Query.from_(self.table1).select(self.table1.foo.as_('a')).as_('a')
query2 = Query.from_(self.table2).select(self.table2.bar.as_('a')).as_('b')
union_query = (query1 + query2).orderby(query1.field('a'))
self.assertEqual('(SELECT "foo" "a" FROM "abc") "a" '
'UNION ALL '
'(SELECT "bar" "a" FROM "efg") "b" '
'ORDER BY "a"."a"', union_query)
def test_from_subquery_without_alias(self):
subquery = Query.from_(self.table_efg) \
.select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz)
test_query = Query.from_(subquery) \
.select(subquery.x, subquery.fizz, subquery.buzz)
self.assertEqual('SELECT "sq0"."x","sq0"."fizz","sq0"."buzz" '
'FROM ('
'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
') "sq0"', str(test_query))
def test_union_with_order_by_use_union_query_field(self):
query1 = Query.from_(self.table1).select(self.table1.foo.as_('a'))
query2 = Query.from_(self.table2).select(self.table2.bar.as_('a'))
union_query = (query1 + query2)
union_query = union_query.orderby(union_query.field('a'))
self.assertEqual('(SELECT "foo" "a" FROM "abc") '
'UNION ALL '
'(SELECT "bar" "a" FROM "efg") '
'ORDER BY "a"', union_query)
def test_no_rollup_after_rollup(self):
with self.assertRaises(AttributeError):
Query.from_(self.table).select(
self.table.foo,
self.table.fiz,
fn.Sum(self.table.bar)
).rollup(
self.table.foo,
vendor='mysql'
).rollup(
self.table.fiz,
vendor='mysql'
)
def test_join_query_without_alias(self):
subquery = Query.from_(self.table_efg) \
.select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz)
test_query = Query.from_(self.table_abc) \
.join(subquery) \
.on(subquery.x == self.table_abc.id) \
.select(self.table_abc.foo, subquery.fizz, subquery.buzz)
self.assertEqual('SELECT "abc"."foo","sq0"."fizz","sq0"."buzz" '
'FROM "abc" JOIN ('
'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
') "sq0" '
'ON "sq0"."x"="abc"."id"', str(test_query))
def test_select_from_nested_query(self):
subquery = Query.from_(self.table_abc).select(
self.table_abc.foo,
self.table_abc.bar,
(self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'),
)
query = Query.from_(subquery).select(subquery.foo, subquery.bar, subquery.fizzbuzz)
self.assertEqual('SELECT "sq0"."foo","sq0"."bar","sq0"."fizzbuzz" '
'FROM ('
'SELECT "foo","bar","fizz"+"buzz" "fizzbuzz" '
'FROM "abc"'
') "sq0"', str(query))
def test_select__nested_subquery(self):
subquery0 = Query.from_(self.table_abc).select("*")
subquery1 = Query.from_(subquery0) \
.select(subquery0.foo, subquery0.bar)
subquery2 = Query.from_(subquery1) \
.select(subquery1.foo)
q = Query.from_(subquery2) \
.select(subquery2.foo)
self.assertEqual('SELECT "sq2"."foo" '
'FROM (SELECT "sq1"."foo" '
'FROM (SELECT "sq0"."foo","sq0"."bar" '
'FROM (SELECT * FROM "abc") "sq0") "sq1") "sq2"', str(q))
def test_join_on_field_multi_with_extra_join(self):
query = Query.from_(self.table0)\
.join(self.hij).on_field("buzz")\
.join(self.table1).on_field("foo", "bar").select('*')
self.assertEqual('SELECT * FROM "abc" JOIN "hij" ON "abc"."buzz"="hij"."buzz" '
'JOIN "efg" ON "abc"."foo"="efg"."foo" AND "abc"."bar"="efg"."bar"', str(query))
def test_tuple_in_using_mixed_tuples(self):
q = Query.from_(self.table_abc) \
.select(self.table_abc.foo, self.table_abc.bar) \
.where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), Tuple(2, 2), (3, 3)]))
self.assertEqual('SELECT "foo","bar" FROM "abc" '
'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))
def test_select__multiple_tables(self):
q = Query.from_(self.table_abc) \
.select(self.table_abc.foo) \
.from_(self.table_efg) \
.select(self.table_efg.bar)
self.assertEqual('SELECT "abc"."foo","efg"."bar" FROM "abc","efg"', str(q))
def test_tuple_equality_tuple_on_left(self):
q = Query.from_(self.table_abc) \
.select(self.table_abc.foo, self.table_abc.bar) \
.where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))
self.assertEqual('SELECT "foo","bar" FROM "abc" '
'WHERE ("foo","bar")=(1,2)', str(q))
def test_join_same_table_with_prefixes(self):
table1 = Table('abc', alias='x')
table2 = Table('abc', alias='y')
q = Query.from_(table1).join(table2).on(table1.foo == table2.bar).select(table1.foo, table2.buz)
self.assertEqual('SELECT "x"."foo","y"."buz" FROM "abc" "x" '
'JOIN "abc" "y" ON "x"."foo"="y"."bar"', str(q))
def test_join_same_table(self):
table1 = Table('abc')
table2 = Table('abc')
q = Query.from_(table1).join(table2).on(table1.foo == table2.bar).select(table1.foo, table2.buz)
self.assertEqual('SELECT "abc"."foo","abc2"."buz" FROM "abc" '
'JOIN "abc" "abc2" ON "abc"."foo"="abc2"."bar"', str(q))
def test_groupby__alias(self):
bar = self.t.bar.as_('bar01')
q = Query.from_(self.t) \
.select(fn.Sum(self.t.foo), bar) \
.groupby(bar)
self.assertEqual('SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar01"', str(q))
def test_join_on_complex_criteria(self):
q = Query.from_(self.table0).join(self.table1, how=JoinType.right).on(
(self.table0.foo == self.table1.fiz) & (self.table0.bar == self.table1.buz)
).select('*')
self.assertEqual('SELECT * FROM "abc" '
'RIGHT JOIN "efg" ON "abc"."foo"="efg"."fiz" AND "abc"."bar"="efg"."buz"', str(q))
def test_select__fields_after_table_star(self):
q = Query.from_(self.table0).join(self.table1).on(self.table0.foo == self.table1.bar).select(self.table0.star,
self.table1.bar).select(
self.table0.foo)
self.assertEqual('SELECT "t0".*,"t1"."bar" FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"',
str(q))
def _build_query(self, table, joins, metrics, dimensions, mfilters, dfilters, references, rollup):
# Initialize query
query = Query.from_(table)
# Add join tables
for join_table, criterion, join_type in joins:
query = query.join(join_table, how=join_type).on(criterion)
# Select dimensions
dx = [dimension.as_(key)
for key, dimension in dimensions.items()
if key not in rollup]
rx = [dimension.as_(key)
for key, dimension in dimensions.items()
if key in rollup]
if dx:
query = query.select(*dx).groupby(*dx)
if rx:
query = query.select(*rx).rollup(*rx)
# Select metrics
mx = [metric.as_(key) for key, metric in metrics.items()]
query = query.select(*mx)
# Add comparisons
for reference_key, dimension_key in references.items():
criterion_f, field_f = self._get_reference_mappers(reference_key)
reference_query = copy.deepcopy(query)
cx = self._build_reference_join_criterion(reference_query, dimensions, criterion_f, dimension_key)
query = query.join(reference_query).on(cx)
# Select all of the comparison dimensions
# TODO maybe just select the dimensions that is different
if dimensions:
query = query.select(*[reference_query.field(key).as_('%s_%s' % (key, reference_key))
for key, dimension in dimensions.items()])
# Select all of the comparison metrics
query = query.select(*[field_f(metric, reference_query.field(key)).as_('%s_%s' % (key, reference_key))
for key, metric in metrics.items()])
# Add ordering
if dx:
query = query.orderby(*dx)
if rx:
query = query.orderby(*rx)
# Add filters
if dfilters:
for dfx in dfilters:
query = query.where(dfx)
if mfilters:
for mfx in mfilters:
query = query.having(mfx)
return query
def test__cast__as(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q))
def test__count__star(self):
q = Q.from_('abc').select(fn.Count('*'))
self.assertEqual('SELECT COUNT(*) FROM \"abc\"', str(q))
def test__avg(self):
q = Q.from_('abc').select(fn.Avg(F('foo')))
self.assertEqual('SELECT AVG(\"foo\") FROM \"abc\"', str(q))
def test__coalesce(self):
q = Q.from_('abc').select(fn.Coalesce(F('foo'), 0))
self.assertEqual('SELECT COALESCE(\"foo\",0) FROM \"abc\"', str(q))
def test__bin__field(self):
q = Q.from_(self.t).select(fn.Bin(self.t.foo))
self.assertEqual("SELECT BIN(\"foo\") FROM \"abc\"", str(q))
def test__addition__fields(self):
q1 = Q.from_('abc').select(F('a') + F('b'))
q2 = Q.from_(self.t).select(self.t.a + self.t.b)
self.assertEqual('SELECT \"a\"+\"b\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"+\"b\" FROM \"abc\"', str(q2))
def test__subtraction__fields(self):
q1 = Q.from_('abc').select(F('a') - F('b'))
q2 = Q.from_(self.t).select(self.t.a - self.t.b)
self.assertEqual('SELECT \"a\"-\"b\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"-\"b\" FROM \"abc\"', str(q2))
def test__cast__date(self):
q1 = Q.from_(self.t).select(fn.Date(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.DATE))
self.assertEqual("SELECT DATE(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS DATE) FROM \"abc\"", str(q2))
def test__convert__utf8(self):
q = Q.from_(self.t).select(fn.Convert(self.t.foo, SqlTypes.utf8))
self.assertEqual("SELECT CONVERT(\"foo\" USING utf8) FROM \"abc\"",
str(q))
def test__addition__right(self):
q1 = Q.from_('abc').select(1 + F('a'))
q2 = Q.from_(self.t).select(1 + self.t.a)
self.assertEqual('SELECT 1+\"a\" FROM \"abc\"', str(q1))
self.assertEqual('SELECT 1+\"a\" FROM \"abc\"', str(q2))
def test__case__raw(self):
q = Q.from_('abc').select(Case().when(F('foo') == 1, 'a').else_('b'))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END FROM \"abc\"",
str(q))
def test__ascii__field(self):
q = Q.from_(self.t).select(fn.Ascii(self.t.foo))
self.assertEqual("SELECT ASCII(\"foo\") FROM \"abc\"", str(q))
def test__stddev(self):
q = Q.from_('abc').select(fn.StdDev(F('foo')))
self.assertEqual('SELECT STDDEV(\"foo\") FROM \"abc\"', str(q))
def test__case__no_else(self):
with self.assertRaises(CaseException):
q = Q.from_('abc').select(Case().when(F('foo') > 0, F('fiz')))
str(q)
def test__max(self):
q = Q.from_('abc').select(fn.Max(F('foo')))
self.assertEqual('SELECT MAX(\"foo\") FROM \"abc\"', str(q))
def test__addition__decimal(self):
q1 = Q.from_('abc').select(F('a') + 1.0)
q2 = Q.from_(self.t).select(self.t.a + 1.0)
self.assertEqual('SELECT \"a\"+1.0 FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"+1.0 FROM \"abc\"', str(q2))
def test__sum(self):
q = Q.from_('abc').select(fn.Sum(F('foo')))
self.assertEqual('SELECT SUM(\"foo\") FROM \"abc\"', str(q))
def test__case__no_cases(self):
with self.assertRaises(CaseException):
q = Q.from_('abc').select(Case())
str(q)
def test__count(self):
q = Q.from_('abc').select(fn.Count(F('foo')))
self.assertEqual('SELECT COUNT(\"foo\") FROM \"abc\"', str(q))
def test__exponent__decimal(self):
q1 = Q.from_('abc').select(F('a')**0.5)
q2 = Q.from_(self.t).select(self.t.a**0.5)
self.assertEqual('SELECT POW(\"a\",0.5) FROM \"abc\"', str(q1))
self.assertEqual('SELECT POW(\"a\",0.5) FROM \"abc\"', str(q2))
def test__modulus__number(self):
q1 = Q.from_('abc').select(F('a') % 2)
q2 = Q.from_(self.t).select(self.t.a % 2)
self.assertEqual('SELECT MOD(\"a\",2) FROM \"abc\"', str(q1))
self.assertEqual('SELECT MOD(\"a\",2) FROM \"abc\"', str(q2))
def test__arithmetic_with_function(self):
q1 = Q.from_('abc').select(fn.Sum(F('foo')) + 1)
q2 = Q.from_(self.t).select(fn.Sum(self.t.foo) + 1)
self.assertEqual('SELECT SUM(\"foo\")+1 FROM \"abc\"', str(q1))
self.assertEqual('SELECT SUM(\"foo\")+1 FROM \"abc\"', str(q2))
def test__complex_op_div_parentheses(self):
q1 = Q.from_('abc').select((F('a') + 1) / (F('b') - 5))
q2 = Q.from_(self.t).select((self.t.a + 1) / (self.t.b - 5))
self.assertEqual('SELECT (\"a\"+1)/(\"b\"-5) FROM \"abc\"', str(q1))
self.assertEqual('SELECT (\"a\"+1)/(\"b\"-5) FROM \"abc\"', str(q2))
def test__length__field(self):
q = Q.from_(self.t).select(fn.Length(self.t.foo))
self.assertEqual("SELECT LENGTH(\"foo\") FROM \"abc\"", str(q))
def test__exponent__number(self):
q1 = Q.from_('abc').select(F('a')**2)
q2 = Q.from_(self.t).select(self.t.a**2)
self.assertEqual('SELECT POW(\"a\",2) FROM \"abc\"', str(q1))
self.assertEqual('SELECT POW(\"a\",2) FROM \"abc\"', str(q2))
def test__insert__field(self):
q = Q.from_(self.t).select(fn.Insert(self.t.foo, 3, 4, self.t.bar))
self.assertEqual("SELECT INSERT(\"foo\",3,4,\"bar\") FROM \"abc\"",
str(q))
def test__arithmetic_equality(self):
q1 = Q.from_('abc').select(F('a') / 2 == 2)
q2 = Q.from_(self.t).select(self.t.a / 2 == 2)
self.assertEqual('SELECT "a"/2=2 FROM \"abc\"', str(q1))
self.assertEqual('SELECT \"a\"/2=2 FROM \"abc\"', str(q2))
def test__lower__field(self):
q = Q.from_(self.t).select(fn.Lower(self.t.foo))
self.assertEqual("SELECT LOWER(\"foo\") FROM \"abc\"", str(q))
def test__complex_op(self):
q1 = Q.from_('abc').select(2 + 1 / F('a') - 5)
q2 = Q.from_(self.t).select(2 + 1 / self.t.a - 5)
self.assertEqual('SELECT 2+1/\"a\"-5 FROM \"abc\"', str(q1))
self.assertEqual('SELECT 2+1/\"a\"-5 FROM \"abc\"', str(q2))
def test__concat__field(self):
q = Q.from_(self.t).select(fn.Concat(self.t.foo, self.t.bar))
self.assertEqual("SELECT CONCAT(\"foo\",\"bar\") FROM \"abc\"", str(q))
