def test_can_generate_select_all(self): queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertEqual(len(dat[0].keys()), 88)
def test_can_sum_from_joined_column_passes(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Join', query_func='join', query_kwargs= '{"table_name":"dac1_current","schema_name":"public", "join_on":{"year":"year"}\ ,"columns_x":["donor_name","usd_commitment"],"columns_y":["part_code","part_name"]}', source_id=2) OperationStep.objects.create( operation=self.op, step_id=3, name='Aggregate', query_func='aggregate', query_kwargs= '{"group_by":["part_name"],"agg_func_name":"Sum", "operational_column":"usd_commitment"}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue(dat[0]['usd_commitment_Sum'] > 150)
def test_can_generate_filter(self): OperationStep.objects.create( operation=self.op, step_id=2, name="Filter", query_func="filter", query_kwargs= '{"filters":[{"field":"year", "value":1973, "func":"ge"},{"field":"short_description", "value":"%sector%|%wheat%", "func":"text_search"}]}', source_id=1) queries = query.build_query(operation=self.op) _, dat = fetch_data(queries, database="default") self.assertEqual(len(dat), 41)
def test_can_perform_multi_transform_divide_by_zero(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Transform', query_func='multi_transform', query_kwargs= '{"trans_func_name":"divide", "operational_columns":["usd_disbursement","usd_disbursement_deflated"]}', source_id=2) queries = query.build_query(operation=self.op) _, dat = fetch_data(queries, database="default") self.assertTrue('usd_disbursement_divide' in dat[0].keys())
def test_can_generate_select_by_column(self): OperationStep.objects.create( operation=self.op, step_id=2, name="Select", query_func="select", query_kwargs="{ \"columns\": [ \"year\" ] }", source_id=1) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertEqual(len(dat[0].keys()), 1)
def test_can_catch_sql_err(self): OperationStep.objects.create( operation=self.op, step_id=2, name="Select", query_func="select", query_kwargs="{ \"columns\": [ \"iso10\" ] }", source_id=1) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue("error" in list(dat[0].keys()))
def test_can_catch_zip_err(self): OperationStep.objects.create( operation=self.op, step_id=2, name="Filter", query_func="filter", query_kwargs= '{"filters":[{"field":"year", "value":9999, "func":"ge"}]}', source_id=1) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertEqual(len(dat), 0)
def test_can_generate_join(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Join', query_func='join', query_kwargs= '{"table_name":"dac1_current","schema_name":"public", "join_on":{"year":"year"}}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertEqual(len(dat[0].keys()), 100)
def test_can_perform_scalar_transform(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Transform', query_func='scalar_transform', query_kwargs= '{"trans_func_name":"text_search", "operational_column":"short_description", "operational_value":"%wheat%"}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue(dat[0]['short_description_text_search'])
def test_can_generate_avg_aggregate(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Aggregate', query_func='aggregate', query_kwargs= '{"group_by":["year"],"agg_func_name":"Avg", "operational_column":"usd_commitment"}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue(dat[0]["usd_commitment_Avg"] > 9)
def test_can_perform_stddev(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Window StdDev', query_func='window', query_kwargs= '{"window_fn":"StdDev","term":"usd_commitment","over":["year"]}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue(dat[0]['stddev'] > 8)
def test_can_perform_last_value(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Window last val', query_func='window', query_kwargs= '{"window_fn":"LastValue","term":"usd_commitment","order_by":["year"]}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue(dat[0]['last_value'] > 14)
def test_can_generate_perform_dense_rank(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Window denserank', query_func='window', query_kwargs= '{"window_fn":"DenseRank","order_by":["usd_commitment"]}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertEqual(dat[0]["dense_rank"], 1)
def test_can_sum(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Aggregate', query_func='aggregate', query_kwargs= '{"group_by":["donor_code"],"agg_func_name":"Sum", "operational_column":"usd_commitment"}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertTrue('usd_commitment_Sum' in dat[0].keys())
def test_can_generate_join_for_specific_columns(self): OperationStep.objects.create( operation=self.op, step_id=2, name='Join', query_func='join', query_kwargs= '{"table_name":"dac1_current","schema_name":"public", "join_on":{"year":"year"},\ "columns_x":["donor_name","usd_commitment"],"columns_y":["part_code","part_name"]}', source_id=2) queries = query.build_query(operation=self.op, limit=1, offset=0, estimate_count=True) _, dat = fetch_data(queries, database="default") self.assertEqual(len(dat[0].keys()), 90)
def advanced_query_table(config, limit=None, offset=None, estimate_count=False): limit = 10000 if limit is None or int(limit) > 10000 else limit offset = offset or 0 queries = build_advanced_queries(config, limit, offset, estimate_count) return fetch_data(queries)
def query_table(operation=None, limit=None, offset=None, estimate_count=None, operation_steps=None, frozen_table_id=None): """Build a query then execute it to return the matching data""" limit = 10000 if limit is None or int(limit) > 10000 else limit offset = offset or 0 queries = build_query(operation, steps=operation_steps, limit=limit, offset=offset, estimate_count=estimate_count, frozen_table_id=frozen_table_id) return fetch_data(queries)