def test_distinct(self): query = Query().from_table(table=Account).distinct() query_str = query.get_sql() expected_query = "SELECT DISTINCT tests_account.* FROM tests_account" self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) query.distinct(use_distinct=False) query_str = query.get_sql() expected_query = "SELECT tests_account.* FROM tests_account" self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_distinct(self): query = Query().from_table(table=Account).distinct() query_str = query.get_sql() expected_query = 'SELECT DISTINCT tests_account.* FROM tests_account' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) query.distinct(use_distinct=False) query_str = query.get_sql() expected_query = 'SELECT tests_account.* FROM tests_account' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_inner(self): inner_query = Query().from_table(Account) query = Query().from_table(inner_query) query_str = query.get_sql() expected_query = "WITH T0 AS (SELECT tests_account.* FROM tests_account) SELECT T0.* FROM T0" self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) inner_query = Query().from_table(Account) query = Query().with_query(inner_query, "s3").from_table("s3") query_str = query.get_sql() expected_query = "WITH s3 AS (SELECT tests_account.* FROM tests_account) SELECT s3.* FROM s3" self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_inner_alias(self): inner_query = Query().from_table(Account) query = Query().from_table({"Q0": inner_query}) query_str = query.get_sql() expected_query = "WITH Q0 AS (SELECT tests_account.* FROM tests_account) SELECT Q0.* FROM Q0" self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_num_stddev(self): query = Query().from_table( table=Order, fields=[ '*', NumStdDevField( 'margin', over=QueryWindow() ) ] ).order_by( '-margin_num_stddev' ) query_str = query.get_sql() expected_query = ( 'SELECT tests_order.*, ' '(CASE WHEN (STDDEV(tests_order.margin) OVER ()) <> 0 ' 'THEN ((tests_order.margin - (' 'AVG(tests_order.margin) OVER ())) / (STDDEV(tests_order.margin) OVER ())) ' 'ELSE 0 ' 'END) ' 'AS "margin_num_stddev" ' 'FROM tests_order ' 'ORDER BY margin_num_stddev ' 'DESC' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_in_list(self): query = Query().from_table(table=Account).where(Q(id__in=[10, 11, 12])) query_str = query.get_sql() expected_query = 'SELECT tests_account.* FROM tests_account WHERE (id IN (%(A0)s,%(A1)s,%(A2)s))' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_join_model_fields(self): query = Query().from_table( table=Account, fields=[ 'one', 'two', ] ).join( Order, fields=[{ 'three': 'one' }, { 'four': 'two' }] ) query_str = query.get_sql() expected_query = ( 'SELECT tests_account.one, ' 'tests_account.two, ' 'tests_order.one AS three, ' 'tests_order.two AS four ' 'FROM tests_account ' 'JOIN tests_order ON tests_order.account_id = tests_account.id' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_query_window_partition_order_many(self): query_window = QueryWindow().partition_by('field_one').partition_by( 'field_two').order_by('field_one').order_by('-field_two') query_str = query_window.get_sql() expected_query = 'OVER (PARTITION BY field_one, field_two ORDER BY field_one ASC, field_two DESC)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_order_by_many_desc(self): query = Query().from_table( table='test_table').order_by('-field_one').order_by('-field_two') query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table ORDER BY field_one DESC, field_two DESC' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_join_model_fields_extract(self): query = Query().from_table( table=Account, fields=[ '*', ] ).join( Order, fields=[ '*' ] ) query_str = query.get_sql() expected_query = ( 'SELECT tests_account.*, ' 'tests_order.id, ' 'tests_order.account_id, ' 'tests_order.revenue, ' 'tests_order.margin, ' 'tests_order.margin_percent, ' 'tests_order.time ' 'FROM tests_account ' 'JOIN tests_order ON tests_order.account_id = tests_account.id' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_complex(self): query = Query().from_table( table='test_table' ).where(Q( one=1 )).where(Q( two__gt=2 )).where(~Q( three__gte=3 )).where(~Q( four__lt=4 ), OR).where(Q( five__lte=5 ), OR).where(Q( six__contains='six' )).where(~Q( seven__startswith='seven' )).where(Q( eight=8 ) & Q( nine=9 ) | Q( ten=10 ) | ~Q( eleven=11 )) query_str = query.get_sql() expected_query = ''.join([ 'SELECT test_table.* FROM test_table WHERE ', '(((one = %(A0)s AND two > %(A1)s AND (NOT(three >= %(A2)s))) OR (NOT(four < %(A3)s)) ', 'OR five <= %(A4)s) AND (six LIKE %(A5)s) AND (NOT(seven LIKE %(A6)s)) AND ', '((eight = %(A7)s AND nine = %(A8)s) OR ten = %(A9)s OR (NOT(eleven = %(A10)s))))' ]) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_select_fields_two_tables_alias(self): query = Query().from_table( table={ 'table_one': Account }, fields=[{ 'f1': 'field_one' }, { 'f2': 'field_two' }] ).from_table( table={ 'table_two': 'second_table' }, fields=[{ 'f3': 'field_three' }, { 'f4': 'field_four' }] ) query_str = query.get_sql() expected_query = ( 'SELECT table_one.field_one AS "f1", ' 'table_one.field_two AS "f2", ' 'table_two.field_three AS "f3", ' 'table_two.field_four AS "f4" ' 'FROM tests_account AS table_one, ' 'second_table AS table_two' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_limit(self): query = Query().from_table( table='test_table' ).limit(10) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table LIMIT 10' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_select_all_from_model(self): query = Query().from_table( table=Account ) query_str = query.get_sql() expected_query = 'SELECT tests_account.* FROM tests_account' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_over(self): query = Query().from_table(table=Order, fields=[RankField(over=QueryWindow())]) query_str = query.get_sql() expected_query = 'SELECT RANK() OVER () AS rank FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_count_id(self): query = Query().from_table(table='test_table', fields=[CountField('id')]) query_str = query.get_sql() expected_query = 'SELECT COUNT(test_table.id) AS "id_count" FROM test_table' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_select_all_from_string(self): query = Query().from_table( table='test_table' ) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank(self): query = Query().from_table( table=Order, fields=[ 'id', RankField( over=QueryWindow().partition_by( 'account_id' ).order_by( 'id' ) ) ] ).order_by( '-rank' ) query_str = query.get_sql() expected_query = ( 'SELECT tests_order.id, ' 'RANK() OVER (PARTITION BY account_id ORDER BY id ASC) AS "rank" ' 'FROM tests_order ' 'ORDER BY rank ' 'DESC' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_eq(self): query = Query().from_table(table='test_table').where(Q(one='two')) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE (one = %(A0)s)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_count_distinct(self): query = Query().from_table(table='test_table', fields=[CountField('name', distinct=True)]) query_str = query.get_sql() expected_query = 'SELECT COUNT(DISTINCT test_table.name) AS "name_count" FROM test_table' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_inner(self): inner_query = Query().from_table(Account) query = Query().from_table(inner_query) query_str = query.get_sql() expected_query = 'WITH T0 AS (SELECT tests_account.* FROM tests_account) SELECT T0.* FROM T0' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) inner_query = Query().from_table(Account) query = Query().with_query(inner_query, 's3').from_table('s3') query_str = query.get_sql() expected_query = 'WITH s3 AS (SELECT tests_account.* FROM tests_account) SELECT s3.* FROM s3' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_join_model_fields(self): query = Query().from_table( table=Account, fields=[ 'one', 'two', ] ).join( Order, fields=[{ 'three': 'one' }, { 'four': 'two' }] ) query_str = query.get_sql() expected_query = ( 'SELECT tests_account.one, ' 'tests_account.two, ' 'tests_order.one AS "three", ' 'tests_order.two AS "four" ' 'FROM tests_account ' 'JOIN tests_order ON tests_order.account_id = tests_account.id' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_distinct_on(self): query = Query().from_table(table=Account).distinct_on('field1') query_str = query.get_sql() expected_query = 'SELECT DISTINCT ON (field1) tests_account.* FROM tests_account' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_inner_outer_args_many(self): inner_query = Query().from_table( Account ).where( Q(id__gt=1) & Q(id__lt=10) ) inner_query2 = Query().from_table( Account ).where( Q(id__gt=1) & Q(id__lt=10) ) query = Query().from_table( inner_query ).from_table( inner_query2 ).where( ~Q(id=0) ) query_str = query.get_sql() expected_query = ( 'WITH T1 AS ' '(SELECT tests_account.* FROM tests_account WHERE (id > %(T1A0)s AND id < %(T1A1)s)), ' 'T0 AS (' 'SELECT tests_account.* ' 'FROM tests_account ' 'WHERE (id > %(T0A0)s AND id < %(T0A1)s)) ' 'SELECT T0.*, T1.* ' 'FROM T0, T1 ' 'WHERE ((NOT(id = %(A0)s)))' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_variance(self): query = Query().from_table(table=Order, fields=[VarianceField('margin')]) query_str = query.get_sql() expected_query = 'SELECT VARIANCE(tests_order.margin) AS "margin_variance" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_limit_with_offset(self): query = Query().from_table(table='test_table').limit(limit=5, offset=20) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table LIMIT 5 OFFSET 20' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_three_levels(self): inner_inner_query = Query().from_table(Account).where( Q(id__gt=1) & Q(id__lt=10)) inner_inner_query2 = Query().from_table(Account).where( Q(id__gt=1) & Q(id__lt=10)) inner_query = Query().from_table(Account).where( Q(id__gt=1) & Q(id__lt=10)) inner_query2 = Query().from_table(inner_inner_query).from_table( inner_inner_query2).where(Q(id__gt=1) & Q(id__lt=10)) query = Query().from_table(inner_query).from_table(inner_query2).where( ~Q(id=0)) query_str = query.get_sql() expected_query = ( 'WITH T1T1 AS (SELECT querybuilder_tests_account.* FROM querybuilder_tests_account ' 'WHERE (id > %(T1T1A0)s AND id < %(T1T1A1)s)), ' 'T1T0 AS (SELECT querybuilder_tests_account.* FROM querybuilder_tests_account ' 'WHERE (id > %(T1T0A0)s AND id < %(T1T0A1)s)), ' 'T1 AS (SELECT T1T0.*, T1T1.* FROM T1T0, T1T1 WHERE (id > %(T1A0)s AND id < %(T1A1)s)), ' 'T0 AS (SELECT querybuilder_tests_account.* FROM querybuilder_tests_account ' 'WHERE (id > %(T0A0)s AND id < %(T0A1)s)) ' 'SELECT T0.*, T1.* FROM T0, T1 WHERE ((NOT(id = %(A0)s)))') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_over_order(self): query = Query().from_table( table=Order, fields=['id', RankField(over=QueryWindow().order_by('id'))]) query_str = query.get_sql() expected_query = 'SELECT tests_order.id, RANK() OVER (ORDER BY id ASC) AS rank FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_combined_or(self): query = Query().from_table( table='test_table').where(Q(one='two') | Q(three='four')) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE ((one = %(A0)s OR three = %(A1)s))' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_year(self): query = Query().from_table(table=Order, fields=[Year('time')]) query_str = query.get_sql() expected_query = ( 'SELECT CAST(EXTRACT(year FROM querybuilder_tests_order.time) AS INT) AS "time__year" ' 'FROM querybuilder_tests_order') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_lt(self): query = Query().from_table(table='test_table').where( Q(field_name__lt=10)) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE (field_name < %(A0)s)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_distinct_on_many_fields(self): query = Query().from_table( table=Account ).distinct_on('field1', 'field2', 'field3') query_str = query.get_sql() expected_query = 'SELECT DISTINCT ON (field1, field2, field3) tests_account.* FROM tests_account' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_inner_alias(self): inner_query = Query().from_table(Account) query = Query().from_table({'Q0': inner_query}) query_str = query.get_sql() expected_query = 'WITH Q0 AS (SELECT tests_account.* FROM tests_account) SELECT Q0.* FROM Q0' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_not_gte(self): query = Query().from_table( table='test_table').where(~Q(field_name__gte=10)) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE ((NOT(field_name >= %(A0)s)))' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_query_window_partition_order(self): query_window = QueryWindow().partition_by( 'field_one' ).order_by( 'field_one' ) query_str = query_window.get_sql() expected_query = 'OVER (PARTITION BY field_one ORDER BY field_one ASC)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_max_field(self): """ Verifies that the MAX function is generated correctly in a query """ query = Query().from_table(table=Order, fields=[MaxField('margin')]) query_str = query.get_sql() expected_query = 'SELECT MAX(tests_order.margin) AS "margin_max" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_stddev(self): query = Query().from_table(table=Order, fields=[ StdDevField('margin'), ]) query_str = query.get_sql() expected_query = 'SELECT STDDEV(tests_order.margin) AS "margin_stddev" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_sum_field(self): """ Tests that the SumField generates correct sql """ query = Query().from_table(table=Order, fields=[SumField('margin')]) query_str = query.get_sql() expected_query = 'SELECT SUM(tests_order.margin) AS "margin_sum" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_group_by_id(self): query = Query().from_table(table='test_table', fields=[{ 'num': CountField('id') }]).group_by(field='id') query_str = query.get_sql() expected_query = 'SELECT COUNT(test_table.id) AS "num" FROM test_table GROUP BY id' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_order_by_single_asc(self): query = Query().from_table( table='test_table' ).order_by( 'field_one' ) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table ORDER BY field_one ASC' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_min_field(self): """ Verifies that the MinField generates correct MIN sql """ query = Query().from_table(table=Order, fields=[MinField('margin')]) query_str = query.get_sql() expected_query = 'SELECT MIN(tests_order.margin) AS "margin_min" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_group_by_table_id(self): query = ( Query() .from_table(table="test_table", fields=[{"num": CountField("id")}]) .group_by(field="id", table="test_table") ) query_str = query.get_sql() expected_query = "SELECT COUNT(test_table.id) AS num FROM test_table GROUP BY test_table.id" self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_select_all_from_string_alias(self): query = Query().from_table( table={ 'table_alias': 'test_table' } ) query_str = query.get_sql() expected_query = 'SELECT table_alias.* FROM test_table AS table_alias' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_variance(self): query = Query().from_table( table=Order, fields=[ VarianceField('margin') ] ) query_str = query.get_sql() expected_query = 'SELECT VARIANCE(tests_order.margin) AS margin_variance FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_join_model_one_to_one_reverse(self): query = Query().from_table(table=User).join(Account, ) query_str = query.get_sql() expected_query = ( 'SELECT tests_user.* ' 'FROM tests_user ' 'JOIN tests_account ON tests_account.user_id = tests_user.id') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_join_model_foreign_key_reverse(self): query = Query().from_table(table=Order).join(Account, ) query_str = query.get_sql() expected_query = ( 'SELECT tests_order.* ' 'FROM tests_order ' 'JOIN tests_account ON tests_account.id = tests_order.account_id') self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_avg(self): query = Query().from_table( table=Order, fields=[ AvgField('margin') ] ) query_str = query.get_sql() expected_query = 'SELECT AVG(tests_order.margin) AS margin_avg FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_count_id(self): query = Query().from_table( table='test_table', fields=[ CountField('id') ] ) query_str = query.get_sql() expected_query = 'SELECT COUNT(test_table.id) AS id_count FROM test_table' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_stddev(self): query = Query().from_table( table=Order, fields=[ StdDevField('margin'), ] ) query_str = query.get_sql() expected_query = 'SELECT STDDEV(tests_order.margin) AS margin_stddev FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_year(self): query = Query().from_table( table=Order, fields=[ Year('time') ] ) query_str = query.get_sql() expected_query = 'SELECT CAST(EXTRACT(year FROM tests_order.time) AS INT) AS "time__year" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_in_list(self): query = Query().from_table( table=Account ).where(Q( id__in=[10, 11, 12] )) query_str = query.get_sql() expected_query = 'SELECT tests_account.* FROM tests_account WHERE (id IN (%(A0)s,%(A1)s,%(A2)s))' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_eq(self): query = Query().from_table( table='test_table' ).where(Q( one='two' )) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE (one = %(A0)s)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_limit_with_offset(self): query = Query().from_table( table='test_table' ).limit( limit=5, offset=20 ) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table LIMIT 5 OFFSET 20' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_not_gt(self): query = Query().from_table( table='test_table' ).where(~Q( field_name__gt=10 )) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE ((NOT(field_name > %(A0)s)))' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_group_all(self): query = Query().from_table( table=Order, fields=[ AllTime('time') ] ) query_str = query.get_sql() expected_query = 'SELECT CAST(0 AS INT) AS "time__epoch" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_rank_no_over(self): query = Query().from_table( table=Order, fields=[ RankField() ] ) query_str = query.get_sql() expected_query = 'SELECT RANK() AS "rank" FROM tests_order' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_lte(self): query = Query().from_table( table='test_table' ).where(Q( field_name__lte=10 )) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE (field_name <= %(A0)s)' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_where_not_startswith(self): query = Query().from_table( table='test_table').where(~Q(field_name__startswith='some value')) query_str = query.get_sql() expected_query = 'SELECT test_table.* FROM test_table WHERE ((NOT(field_name LIKE %(A0)s)))' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) self.assertEqual(query._where.args['A0'], 'some value%', 'Value is not correct')