def test_cume_dist(self):
query = Query().from_table(
table=Order,
fields=[
'*',
CumeDistField(over=QueryWindow().order_by('-margin'))
]).order_by('cume_dist')
query_str = query.get_sql()
expected_query = (
'SELECT tests_order.*, '
'CUME_DIST() OVER (ORDER BY margin DESC) AS cume_dist '
'FROM tests_order '
'ORDER BY cume_dist '
'ASC')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_select_fields_same_two_tables(self):
query = Query().from_table(
table=Account,
fields=['field_one', 'field_two'
]).from_table(table=Account,
fields=['field_three', 'field_four'])
query_str = query.get_sql()
expected_query = (
'SELECT querybuilder_tests_account.field_one, '
'querybuilder_tests_account.field_two, '
'T1.field_three, '
'T1.field_four '
'FROM querybuilder_tests_account, querybuilder_tests_account AS T1'
)
self.assertEqual(query_str, expected_query,
'\n{0}\n!=\n{1}'.format(query_str, expected_query))
def test_select_sql_args(self):
sql = 'SELECT id FROM querybuilder_tests_account WHERE id = %(my_id)s'
sql_args = {
'my_id': 2
}
rows = Query().select(sql=sql, sql_args=sql_args)
received = rows[0]['id']
expected = User.objects.all().filter(id=2)[0].id
self.assertEqual(
received,
expected,
'Expected {0} but received {1}'.format(
expected,
received
)
)
def test_minute_auto(self):
query = Query().from_table(table=Order,
fields=[Minute('time', auto=True)])
query_str = query.get_sql()
expected_query = (
'SELECT CAST(EXTRACT(year FROM querybuilder_tests_order.time) AS INT) AS "time__year", '
'CAST(EXTRACT(month FROM querybuilder_tests_order.time) AS INT) AS "time__month", '
'CAST(EXTRACT(day FROM querybuilder_tests_order.time) AS INT) AS "time__day", '
'CAST(EXTRACT(hour FROM querybuilder_tests_order.time) AS INT) AS "time__hour", '
'CAST(EXTRACT(minute FROM querybuilder_tests_order.time) AS INT) AS "time__minute", '
'CAST(EXTRACT(epoch FROM date_trunc(\'minute\', querybuilder_tests_order.time)) AS INT) AS "time__epoch" '
'FROM querybuilder_tests_order '
'GROUP BY time__year, time__month, time__day, time__hour, time__minute, time__epoch '
'ORDER BY time__epoch ASC')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def date_part_field_test(self):
"""
Tests the different options of DatePartField objects
"""
# test with no cast
query = Query().from_table(
table=Order, fields=[AllTime('time'),
SumField('margin')])
expected_query = ('SELECT CAST(0 AS INT) AS "time__epoch", '
'SUM(tests_order.margin) AS "margin_sum" '
'FROM tests_order')
self.assertEqual(expected_query, query.get_sql())
rows = query.select()
self.assertEqual(1, len(rows))
self.assertEqual(825.0, rows[0]['margin_sum'])
self.assertEqual(0, rows[0]['time__epoch'])
def test_clear_log_no_index(self):
"""
Makes sure that the query index doesn't change
"""
logger_one = Logger('one')
query = Query().from_table(Account)
query.select()
self.assertIsNone(logger_one.query_index)
# clear the log
logger_one.clear_log()
# make sure no query index
self.assertIsNone(logger_one.query_index)
def test_nth_value(self):
query = Query().from_table(
table=Order,
fields=[
'*',
NthValueField('margin',
n=2,
over=QueryWindow().order_by('-margin'))
])
query_str = query.get_sql()
expected_query = (
'SELECT querybuilder_tests_order.*, '
'NTH_VALUE(querybuilder_tests_order.margin, 2) OVER (ORDER BY margin DESC) AS "margin_nth_value" '
'FROM querybuilder_tests_order')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_row_number(self):
query = Query().from_table(
table=Order,
fields=[
'*',
RowNumberField(over=QueryWindow().order_by('-margin'))
]).order_by('row_number')
query_str = query.get_sql()
expected_query = (
'SELECT querybuilder_tests_order.*, '
'ROW_NUMBER() OVER (ORDER BY margin DESC) AS "row_number" '
'FROM querybuilder_tests_order '
'ORDER BY row_number '
'ASC')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_lag_default(self):
query = Query().from_table(
table=Order,
fields=[
'*',
LagField('margin',
default=0,
over=QueryWindow().order_by('-margin'))
])
query_str = query.get_sql()
expected_query = (
'SELECT querybuilder_tests_order.*, '
'LAG(querybuilder_tests_order.margin, 1, \'0\') OVER (ORDER BY margin DESC) AS "margin_lag" '
'FROM querybuilder_tests_order')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_ntile(self):
query = Query().from_table(
table=Order,
fields=[
'*',
NTileField(num_buckets=2,
over=QueryWindow().order_by('-margin'))
]).order_by('ntile')
query_str = query.get_sql()
expected_query = ('SELECT querybuilder_tests_order.*, '
'NTILE(2) OVER (ORDER BY margin DESC) AS "ntile" '
'FROM querybuilder_tests_order '
'ORDER BY ntile '
'ASC')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_rank_percent(self):
query = Query().from_table(
table=Order,
fields=[
'*',
PercentRankField(over=QueryWindow().order_by('-margin'))
]).order_by('percent_rank')
query_str = query.get_sql()
expected_query = (
'SELECT tests_order.*, '
'PERCENT_RANK() OVER (ORDER BY margin DESC) AS percent_rank '
'FROM tests_order '
'ORDER BY percent_rank '
'ASC')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_dense_rank(self):
query = Query().from_table(
table=Order,
fields=[
'*',
DenseRankField(over=QueryWindow().order_by('-margin'))
]).order_by('dense_rank')
query_str = query.get_sql()
expected_query = (
'SELECT tests_order.*, '
'DENSE_RANK() OVER (ORDER BY margin DESC) AS dense_rank '
'FROM tests_order '
'ORDER BY dense_rank '
'ASC')
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_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_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_second_auto(self):
query = Query().from_table(table=Order,
fields=[Second('time', auto=True)])
query_str = query.get_sql()
expected_query = (
'SELECT CAST(EXTRACT(year FROM tests_order.time) AS INT) AS time__year, '
'CAST(EXTRACT(month FROM tests_order.time) AS INT) AS time__month, '
'CAST(EXTRACT(day FROM tests_order.time) AS INT) AS time__day, '
'CAST(EXTRACT(hour FROM tests_order.time) AS INT) AS time__hour, '
'CAST(EXTRACT(minute FROM tests_order.time) AS INT) AS time__minute, '
'CAST(EXTRACT(second FROM tests_order.time) AS INT) AS time__second, '
'CAST(EXTRACT(epoch FROM date_trunc(\'second\', tests_order.time)) AS INT) AS time__epoch '
'FROM tests_order '
'GROUP BY time__year, time__month, time__day, time__hour, time__minute, time__second, time__epoch '
'ORDER BY time__epoch ASC')
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def bulk_update(manager, model_objs, fields_to_update):
"""
Bulk updates a list of model objects that are already saved.
Args:
model_objs: A list of model objects that have been updated.
fields_to_update: A list of fields to be updated. Only these fields will be updated
Sianals: Emits a post_bulk_operation signal when completed.
Examples:
# Create a couple test models
model_obj1 = TestModel.objects.create(int_field=1, float_field=2.0, char_field='Hi')
model_obj2 = TestModel.objects.create(int_field=3, float_field=4.0, char_field='Hello')
# Change their fields and do a bulk update
model_obj1.int_field = 10
model_obj1.float_field = 20.0
model_obj2.int_field = 30
model_obj2.float_field = 40.0
bulk_update(TestModel.objects, [model_obj1, model_obj2], ['int_field', 'float_field'])
# Reload the models and view their changes
model_obj1 = TestModel.objects.get(id=model_obj1.id)
print model_obj1.int_field, model_obj1.float_field
10, 20.0
model_obj2 = TestModel.objects.get(id=model_obj2.id)
print model_obj2.int_field, model_obj2.float_field
10, 20.0
"""
updated_rows = [
[model_obj.id] + [getattr(model_obj, field_name) for field_name in fields_to_update]
for model_obj in model_objs
]
if len(updated_rows) == 0 or len(fields_to_update) == 0:
return
# Execute the bulk update
Query().from_table(
table=manager.model,
fields=chain(['id'] + fields_to_update),
).update(updated_rows)
post_bulk_operation.send(sender=manager, model=manager.model)
def test_where_and_with_not_combined_or(self):
query = Query().from_table(
table='test_table'
).where(Q(
one='two'
)).where(~Q(
three='four'
) | Q(
five='six'
))
query_str = query.get_sql()
expected_query = (
'SELECT test_table.* '
'FROM test_table '
'WHERE (one = %(A0)s AND ((NOT(three = %(A1)s)) OR five = %(A2)s))'
)
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_update_null_numbers(self):
"""
Verifies that null values can be bulk updated
"""
order1 = G(Order, revenue=10)
order2 = G(Order, revenue=20)
order3 = G(Order, revenue=30)
query = Query().from_table(table=Order, fields=['id', 'revenue'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None], [order2.id, None], [order3.id, 3000]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertEqual(3000, orders[2].revenue)
def test_joined_model_foreign_reverse(self):
query = Query().from_table(Order).join(right_table=Account,
fields=['*'],
prefix_fields=True)
rows = query.select(True)
self.assertGreater(len(rows), 0, 'No records')
logger = Logger()
logger.start_logging()
for row in rows:
self.assertIsInstance(row, Order, 'Record is not model instance')
self.assertIs(hasattr(row, 'account'), True,
'Row does not have nested model')
self.assertIsInstance(row.account, Account,
'Nested record is not model instance')
self.assertEqual(logger.count(), 0,
'Queries were executed when none should')
def test_update_all_nulls(self):
"""
Verifies that the sql is modified when all values for a field are null. For whatever reason,
postgres doesn't handle this the same when all values are null
"""
order1 = G(Order, revenue=10, margin=5)
order2 = G(Order, revenue=20, margin=10)
order3 = G(Order, revenue=30, margin=15)
query = Query().from_table(table=Order, fields=['id', 'revenue', 'margin'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None, 50], [order2.id, None, 100], [order3.id, None, 150]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertIsNone(orders[2].revenue)
def test_find_field(self):
query = Query().from_table(
table=Account,
extract_fields=True,
).from_table(table={
'account2': Account
},
fields=[{
'name': 'first_name'
}]).join(Order)
table = query.tables[0]
field = table.find_field('id')
self.assertIsNotNone(field, 'Field not found')
result = field.get_identifier()
expected = 'tests_account.id'
self.assertEqual(result, expected,
get_comparison_str(result, expected))
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_upsert_json_field(self):
"""
Only runs for django 1.9 because the jsonfield project uses an incorrect db prep value
"""
if VERSION[0] != 1 or VERSION[1] < 9:
return
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7',
field8={'one': 'two'}),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5', 'field8'])
def test_stop_logging(self):
"""
Verifies that the logger stops caring about queries
"""
logger = Logger()
logger.start_logging()
query = Query().from_table(Account)
query.select()
query.select()
self.assertEqual(2, logger.count())
logger.stop_logging()
query.select()
query.select()
self.assertEqual(2, logger.count())
logger.start_logging()
query.select()
self.assertEqual(3, logger.count())
def test_upsert_pk(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
)
self.assertEqual(User.objects.count(), 3)
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_no_fields_and_fields(self):
query = Query().from_table(
table=Account,
fields=None
).join(
'other_table',
fields=[
'field_one',
'field_two'
],
prefix_fields=True,
condition='other_table.test_id = tests_account.id'
)
query_str = query.get_sql()
expected_query = (
'SELECT other_table.field_one AS other_table__field_one, '
'other_table.field_two AS other_table__field_two '
'FROM tests_account '
'JOIN other_table ON other_table.test_id = tests_account.id'
)
self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
def test_join_model_fields_all(self):
query = Query().from_table(
table=Account,
fields=[
'*',
]
).join(
Order,
fields=[
'*'
],
extract_fields=False
)
query_str = query.get_sql()
expected_query = (
'SELECT tests_account.*, '
'tests_order.* '
'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_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))