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))