def test_cannot_mix_distincts(self): query = Query().from_table( table=Account ).distinct_on('field1').distinct() with self.assertRaises(ValueError): query.get_sql()
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_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_insert_multiple_rows(self): query = Query().from_table( table=Account, fields=[ 'user_id', 'first_name', 'last_name' ] ) rows = [ [1, 'Test', 'User'], [2, 'Test2', 'User2'], ] sql, sql_params = query.get_insert_sql(rows) self.assertEqual( sql, 'INSERT INTO tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s), (%s, %s, %s)' ) self.assertEqual(sql_params[0], 1) self.assertEqual(sql_params[1], 'Test') self.assertEqual(sql_params[2], 'User') self.assertEqual(sql_params[3], 2) self.assertEqual(sql_params[4], 'Test2') self.assertEqual(sql_params[5], 'User2') query.insert(rows) sql = self.logger.get_log()[0]['sql'] self.assertEqual( sql, ("INSERT INTO tests_account (user_id, first_name, last_name) " "VALUES (1, 'Test', 'User'), (2, 'Test2', 'User2')") )
def test_get_alias(self): """ Tests the different cases of getting the alias of a field """ field = SimpleField(field="revenue") query = Query().from_table(table=Order, fields=[field]) expected_query = "SELECT tests_order.revenue FROM tests_order" self.assertEqual(expected_query, query.get_sql()) field.auto_alias = "my_auto_alias" query = Query().from_table(table=Order, fields=[field]) expected_query = 'SELECT tests_order.revenue AS "my_auto_alias" FROM tests_order' self.assertEqual(expected_query, query.get_sql()) field.alias = "my_alias" query = Query().from_table(table=Order, fields=[field]) expected_query = 'SELECT tests_order.revenue AS "my_alias" FROM tests_order' self.assertEqual(expected_query, query.get_sql()) query = Query().from_table(table=Order, fields=[field], prefix_fields=True, field_prefix="my_field_prefix") expected_query = 'SELECT tests_order.revenue AS "my_field_prefix__my_alias" FROM tests_order' self.assertEqual(expected_query, query.get_sql()) field.alias = None field.auto_alias = None query = Query().from_table(table=Order, fields=[field], prefix_fields=True, field_prefix="my_field_prefix") expected_query = 'SELECT tests_order.revenue AS "my_field_prefix__revenue" FROM tests_order' self.assertEqual(expected_query, query.get_sql())
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_prefix(self): query = Query().from_table( table=Account, fields=[ '*', ] ).join( Order, fields=[ 'id', 'margin', ], prefix_fields=True ) query_str = query.get_sql() expected_query = ( 'SELECT tests_account.*, ' 'tests_order.id AS order__id, ' 'tests_order.margin AS order__margin ' 'FROM tests_account ' 'JOIN tests_order ON tests_order.account_id = tests_account.id' ) self.assertEqual(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_cast(self): query = Query().from_table( table=Account, fields=[ CountField( 'id', alias='count', cast='float' ) ] ) query_str = query.get_sql() expected_query = 'SELECT CAST(COUNT(tests_account.id) AS FLOAT) AS count FROM tests_account' self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) received = query.select()[0]['count'] expected = float(len(User.objects.all())) self.assertEqual( received, expected, 'Expected {0} but received {1}'.format( expected, received ) )
def test_week_grouping(self): """ Verifies that the week grouping query and result is correct """ query = Query().from_table( table=Order, fields=[ Week('time', auto=True), SumField('margin') ] ) expected_query = ( "SELECT CAST(EXTRACT(year FROM tests_order.time) AS INT) AS time__year, " "CAST(EXTRACT(week FROM tests_order.time) AS INT) AS time__week, " "CAST(EXTRACT(epoch FROM date_trunc('week', tests_order.time)) AS INT) AS time__epoch, " "SUM(tests_order.margin) AS margin_sum " "FROM tests_order " "GROUP BY time__year, time__week, time__epoch " "ORDER BY time__epoch ASC" ) self.assertEqual(expected_query, query.get_sql()) rows = query.select() self.assertEqual(1, len(rows)) week_dt = datetime.datetime(2012, 10, 19) week_unix_time = unix_time(floor(week_dt, week=1)) self.assertEqual(825.0, rows[0]['margin_sum']) self.assertEqual(week_unix_time, rows[0]['time__epoch']) self.assertEqual(2012, rows[0]['time__year']) self.assertEqual(42, rows[0]['time__week'])
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_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_cast(self): query = Query().from_table( table=Account, fields=[ CountField( 'id', alias='count', cast='float' ) ] ) query_str = query.get_sql() expected_query = ( 'SELECT CAST(COUNT(querybuilder_tests_account.id) AS FLOAT) AS "count" FROM querybuilder_tests_account' ) self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query)) received = query.select()[0]['count'] expected = float(len(User.objects.all())) self.assertEqual( received, expected, 'Expected {0} but received {1}'.format( expected, received ) )
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_count(self): query = Query().from_table(User) received = query.count() expected = len(User.objects.all()) self.assertEqual( received, expected, 'Expected {0} but received {1}'.format(expected, received))
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_update_single_row(self): query = Query().from_table( table=Account, fields=['id', 'user_id', 'first_name', 'last_name']) rows = [[1, 1, 'Test\'s', '"User"']] sql, sql_params = query.get_update_sql(rows) self.assertEqual(sql, ('UPDATE tests_account ' 'SET user_id = new_values.user_id, ' 'first_name = new_values.first_name, ' 'last_name = new_values.last_name ' 'FROM (VALUES (%s, %s, %s, %s)) ' 'AS new_values (id, user_id, first_name, last_name) ' 'WHERE tests_account.id = new_values.id')) self.assertEqual(sql_params[0], 1) self.assertEqual(sql_params[1], 1) self.assertEqual(sql_params[2], 'Test\'s') self.assertEqual(sql_params[3], '"User"') query.update(rows) sql = self.logger.get_log()[0]['sql'] self.assertEqual(sql, ("UPDATE tests_account " "SET user_id = new_values.user_id, " "first_name = new_values.first_name, " "last_name = new_values.last_name " "FROM (VALUES (1, 1, 'Test''s', '\"User\"')) " "AS new_values (id, user_id, first_name, last_name) " "WHERE tests_account.id = new_values.id"))
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_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_upsert_custom_db_column(self): """ Makes sure upserting a model containing a field with a custom db_column name works. """ model = Uniques(field1='1', custom_field_name='test') Query().from_table(Uniques).upsert([model], unique_fields=['field1'], update_fields=[]) saved_model = Uniques.objects.get() self.assertEqual(saved_model.custom_field_name, 'test') saved_model.custom_field_name = 'edited' Query().from_table(Uniques).upsert([saved_model], unique_fields=['field1'], update_fields=['custom_field_name']) updated_model = Uniques.objects.get() self.assertEqual(updated_model.custom_field_name, 'edited') rows = Query().from_table(Uniques).select() self.assertEqual(rows[0]['actual_db_column_name'], 'edited')
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_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_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_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_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_insert_single_row(self): G(User, id=1) query = Query().from_table( table=Account, fields=[ 'user_id', 'first_name', 'last_name' ] ) rows = [ [1, 'Test', 'User'] ] sql, sql_params = query.get_insert_sql(rows) self.assertEqual( sql, ( 'INSERT INTO tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s)' ) ) self.assertEqual(sql_params[0], 1) self.assertEqual(sql_params[1], 'Test') self.assertEqual(sql_params[2], 'User') query.insert(rows) sql = self.logger.get_log()[2]['sql'] self.assertEqual(sql, "INSERT INTO tests_account (user_id, first_name, last_name) VALUES (1, 'Test', 'User')")
def test_join_model_fields_prefix(self): query = Query().from_table( table=Account, fields=[ '*', ] ).join( Order, fields=[ 'id', 'margin', ], prefix_fields=True ) query_str = query.get_sql() expected_query = ( 'SELECT tests_account.*, ' 'tests_order.id AS "order__id", ' 'tests_order.margin AS "order__margin" ' 'FROM tests_account ' 'JOIN tests_order ON tests_order.account_id = tests_account.id' ) self.assertEqual(query_str, expected_query)
def test_clear_log(self): """ Makes sure queries are cleared """ logger_one = Logger('one') logger_one.start_logging() query = Query().from_table(Account) # run a query and update the logger's query list query.select() logger_one.update_log() # the index should be at 1 self.assertEqual(1, logger_one.query_index) # increment the connection query count query.select() # clear the log logger_one.clear_log() # make sure no queries self.assertEqual(0, len(logger_one.queries)) # query index should match that of the connection log self.assertEqual(2, logger_one.query_index)
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(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_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_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_insert_multiple_rows(self): G(User, id=1) G(User, id=2) query = Query().from_table( table=Account, fields=['user_id', 'first_name', 'last_name']) rows = [ [1, 'Test', 'User'], [2, 'Test2', 'User2'], ] sql, sql_params = query.get_insert_sql(rows) self.assertEqual( sql, 'INSERT INTO querybuilder_tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s), (%s, %s, %s)' ) self.assertEqual(sql_params[0], 1) self.assertEqual(sql_params[1], 'Test') self.assertEqual(sql_params[2], 'User') self.assertEqual(sql_params[3], 2) self.assertEqual(sql_params[4], 'Test2') self.assertEqual(sql_params[5], 'User2') query.insert(rows) sql = self.logger.get_log()[4]['sql'] self.assertEqual(sql, ( "INSERT INTO querybuilder_tests_account (user_id, first_name, last_name) " "VALUES (1, 'Test', 'User'), (2, 'Test2', 'User2')"))
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_single_model(self): query = Query().from_table(Account) rows = query.select(True) self.assertGreater(len(rows), 0, 'No records') for row in rows: self.assertIsInstance(row, Account, 'Row is not model instance')
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_get_from_name(self): """ Verifies that the correct name is generated """ inner_query = Query().from_table(Account) query = Query().from_table(inner_query) self.assertEqual('(SELECT tests_account.* FROM tests_account)', query.tables[0].get_from_name())
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_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_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_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_single_model(self): query = Query().from_table(Account) rows = query.select(True) self.assertGreater(len(rows), 0, "No records") for row in rows: self.assertIsInstance(row, Account, "Row is not model instance")
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_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_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_select_all_from_model_alias(self): query = Query().from_table( table={ 'table_alias': Account } ) query_str = query.get_sql() expected_query = 'SELECT table_alias.* FROM tests_account AS table_alias' self.assertEqual(query_str, expected_query, '{0}\n!=\n{1}'.format(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_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_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_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_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_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))