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