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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_insert_subquery_wrapped_in_brackets(self):
purchase_order_item, part = Tables("purchase_order_item", "part")
q = Query.into(purchase_order_item) \
.columns(purchase_order_item.id_part, purchase_order_item.id_customer) \
.insert(Query.from_(part)
.select(part.part_id)
.where(part.part_number == "FOOBAR"), 12345)
self.assertEqual('INSERT INTO "purchase_order_item" '
'("id_part","id_customer") '
'VALUES '
'((SELECT "part_id" FROM "part" WHERE "part_number"=\'FOOBAR\'),12345)', str(q))
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_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 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__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_extract_datepart(self, date_part):
q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo))
self.assertEqual("SELECT EXTRACT(%s FROM \"foo\") FROM \"abc\"" % date_part.value, str(q))
def test_join_on_field_single(self):
query = Query.from_(self.table0).join(
self.table1).on_field("foo").select("*")
self.assertEqual(
'SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."foo"',
str(query))
def test_join_using_multiple_fields(self):
query = Query.from_(self.table0).join(self.table1).using(
"foo", "bar").select("*")
self.assertEqual('SELECT * FROM "abc" JOIN "efg" USING ("foo","bar")',
str(query))
def test_join_on_field_without_fields_raises_exception(self):
with self.assertRaises(JoinException):
Query.from_(self.table0).join(self.table1).on_field()
def test__cast__timestamp(self):
q1 = Q.from_(self.t).select(fn.Timestamp(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.TIMESTAMP))
self.assertEqual("SELECT TIMESTAMP(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS TIMESTAMP) FROM \"abc\"", str(q2))
def test__tochar__(self):
q = Q.from_(self.t).select(fn.ToChar(self.t.foo, "SomeFormat"))
self.assertEqual("SELECT TO_CHAR(\"foo\",'SomeFormat') FROM \"abc\"", str(q))
def test__subtraction__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_current_time(self):
query = Query.select(fn.CurTime())
self.assertEqual("SELECT CURRENT_TIME()", str(query))
def test__subtraction__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__multiplication__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__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_cross_join_on_table(self):
table_a, table_b = Tables("a", "b")
q = Query.from_(table_a).join(table_b).cross().select("*")
self.assertEqual('SELECT * FROM "a" CROSS JOIN "b"', str(q))
def test_use_different_table_objects_for_same_table(self):
table = Table("t")
q = Query.from_(table).select("*").where(Field("id", table=table) == 1)
self.assertEqual('SELECT * FROM "t" WHERE "id"=1', 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_current_date(self):
query = Query.select(fn.CurDate())
self.assertEqual("SELECT CURRENT_DATE()", str(query))
def test__cast__varchar(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR))
self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR) FROM \"abc\"", str(q))
def test_utc_timestamp(self):
query = Query.select(fn.UtcTimestamp())
self.assertEqual("SELECT UTC_TIMESTAMP()", str(query))
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__cast__unsigned(self):
q1 = Q.from_(self.t).select(fn.Unsigned(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) 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_join_using_with_quote_char(self):
query = Query.from_(self.table0).join(self.table1).using(
"foo", "bar").select("*")
self.assertEqual("SELECT * FROM abc JOIN efg USING (foo,bar)",
query.get_sql(quote_char=""))
def test__substring(self):
q = Q.from_(self.t).select(fn.Substring(self.t.foo, 2, 6))
self.assertEqual("SELECT SUBSTRING(\"foo\",2,6) FROM \"abc\"", str(q))
def test__length__str(self):
q = Q.select(fn.Length('ABC'))
self.assertEqual("SELECT LENGTH('ABC')", str(q))
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_join_using_string_field_name(self):
query = Query.from_(self.table0).join(
self.table1).using("id").select("*")
self.assertEqual('SELECT * FROM "abc" JOIN "efg" USING ("id")',
str(query))
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_now(self):
query = Query.select(fn.Now())
self.assertEqual("SELECT NOW()", str(query))
def test__lower__str(self):
q = Q.select(fn.Lower('ABC'))
self.assertEqual("SELECT LOWER('ABC')", str(q))
def test__multiplication__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))
