Python Query.get_sql方法代码示例

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    def test_one(self):
        if get_postgres_version() < (9, 4):
            raise unittest.SkipTest('Invalid Postgres version for test')

        metric_record = MetricRecord(data={
            'one': 1,
            'two': 'two',
        })
        metric_record.save()

        one_field = JsonField('data', key='one', alias='my_one_alias')
        two_field = JsonField('data', key='two', alias='my_two_alias')

        query = Query().from_table(MetricRecord, fields=[two_field]).where(**{
            two_field.get_where_key(): 'one'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'two\' AS "my_two_alias" FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'two\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [])

        query = Query().from_table(MetricRecord, fields=[two_field]).where(**{
            two_field.get_where_key(): 'two'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'two\' AS "my_two_alias" FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'two\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [{'my_two_alias': 'two'}])

        query = Query().from_table(MetricRecord, fields=[one_field]).where(**{
            one_field.get_where_key(): '1'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'one\' AS "my_one_alias" FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'one\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [{'my_one_alias': 1}])

        query = Query().from_table(MetricRecord, fields=[one_field]).where(**{
            one_field.get_where_key(): '2'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'one\' AS "my_one_alias" FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'one\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [])

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    def test_cannot_mix_distincts(self):
        query = Query().from_table(
            table=Account
        ).distinct_on('field1').distinct()

        with self.assertRaises(ValueError):
            query.get_sql()

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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())

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    def test_one(self):
        metric_record = MetricRecord(data={
            'one': 1,
            'two': 'two',
        })
        metric_record.save()

        one_field = JsonField('data', key='one', alias='my_one_alias')
        two_field = JsonField('data', key='two', alias='my_two_alias')

        query = Query().from_table(MetricRecord, fields=[two_field]).where(**{
            two_field.get_where_key(): 'one'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'two\' AS my_two_alias FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'two\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [])

        query = Query().from_table(MetricRecord, fields=[two_field]).where(**{
            two_field.get_where_key(): 'two'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'two\' AS my_two_alias FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'two\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [{'my_two_alias': 'two'}])

        query = Query().from_table(MetricRecord, fields=[one_field]).where(**{
            one_field.get_where_key(): '1'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'one\' AS my_one_alias FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'one\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [{'my_one_alias': 1}])

        query = Query().from_table(MetricRecord, fields=[one_field]).where(**{
            one_field.get_where_key(): '2'
        })
        self.assertEqual(
            query.get_sql(),
            (
                'SELECT tests_metricrecord.data->\'one\' AS my_one_alias FROM tests_metricrecord '
                'WHERE (tests_metricrecord.data->>\'one\' = %(A0)s)'
            )
        )
        self.assertEqual(query.select(), [])

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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
            )
        )

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
 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'])

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
 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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
 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))

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import get_sql [as 别名]
 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))