Python Query.select方法代码示例

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

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import select [as 别名]
def issueTicket(token) :  #output [totalAmount, {'qty':theQty, 'drink':theDrink, 'priceU':theUprice, 'priceT":theTprice}, ...]
   if isValidToken(token) :
      query = Query().from_table('orders').where(token_id=token)
      # SELECT * FROM orders WHERE token_id=token
      orders = query.select()
      orderedDrinks = list();
      totalAmount = 0
      # For each order id we get each drink order in ordereddrink
      for order in orders :
         query = Query().from_table('ordereddrink').where(orderid_id=order['id'])
         # SELECT * FROM ordereddrink WHERE orderid_id=order['id']
         ordered = query.select()
         for drink in ordered :
            query = Query().from_table('drink').where(id=drink['drinkid_id'])
            # SELECT * FROM drink WHERE id=drink['drinkid_id']
            myDrink = query.select()
            
            qty = drink['qty']
            priceU = myDrink[0]['price']
            priceT = (qty * priceU)
            totalAmount = totalAmount + priceT
            name = myDrink[0]['name']
            
            orderedDrinks = makeTicket(qty, name, priceU, priceT, orderedDrinks)
            
      return [totalAmount] + orderedDrinks
   else :
      raise ValueError('The token is not a valid token to print ticket !')

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import select [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 select [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 select [as 别名]
    def test_start_logging(self):
        """
        Verifies that the query index gets updated
        """
        logger = Logger()

        query = Query().from_table(Account)
        query.select()
        query.select()
        logger.start_logging()
        self.assertEqual(2, logger.query_index)

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import select [as 别名]
    def test_count(self):
        """
        Verifies that the correct number of queries is returned
        """
        logger = Logger()
        logger.start_logging()

        query = Query().from_table(Account)
        query.select()
        query.select()
        self.assertEqual(2, logger.count())
        query.select()
        self.assertEqual(3, logger.count())

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import select [as 别名]
def freeTable(tableid) :
   # Get all client tokens associated to the table (id) and count them
   query = Query().from_table('client').where(tableid_id=tableid)
   # SELECT * FROM client WEHRE tableid_id = tableid
   client = query.select()
   clientCount = query.count()
   
   # Count when a payment has been done with the client token
   paymentCount = 0
   for token in client :
      query = Query().from_table('payment').where(token_id=token['id'])
      payment = query.select()
      if payment:
         paymentCount = paymentCount + 1
   return clientCount == paymentCount

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

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

# 需要导入模块: from querybuilder.query import Query [as 别名]
# 或者: from querybuilder.query.Query import select [as 别名]
 def test_cast(self):
     """
     Tests that the cast sql is generated and that the resulting value is of that type
     """
     query = Query().from_table(
         table=Order,
         fields=[SimpleField(field='revenue', cast='INT')]
     ).order_by('revenue').limit(1)
     expected_query = 'SELECT CAST(tests_order.revenue AS INT) FROM tests_order ORDER BY revenue ASC LIMIT 1'
     self.assertEqual(expected_query, query.get_sql())
     rows = query.select()
     self.assertEqual(1, len(rows))
     self.assertEqual(type(rows[0]['revenue']), int)

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

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

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

        # there should be one query
        self.assertEqual(logger_one.count(), 1)

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