Python sql_select.SqlSelect類代碼示例

 def test_group_by_case_when(self):
     sql_select = SqlSelect.parse(
         "SELECT * FROM symbol GROUP BY CASE WHEN price > 10 THEN 'high' ELSE 'low' END AS hl")
     self.assertEqual(['hl'], sql_select.group_by.keys())
     self.assertEqual(stypes.Case, type(sql_select.group_by['hl']))
     sql_select = SqlSelect.parse(
         "SELECT * FROM symbol GROUP BY ( CASE WHEN price > 10 THEN 'high' ELSE 'low' END) AS hl")
     self.assertEqual(['hl'], sql_select.group_by.keys())
     self.assertEqual(stypes.Case, type(sql_select.group_by['hl']))

 def test_is(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol WHERE a IS NULL")
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual("IS", comparison.operator)
     self.assertEqual("a", str(comparison.left))
     self.assertEqual("NULL", str(comparison.right))

 def test_is_not(self):
     sql_select = SqlSelect.parse('SELECT * FROM symbol WHERE a IS  NOT NULL')
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual('IS  NOT', comparison.operator)
     self.assertEqual('a', str(comparison.left))
     self.assertEqual('NULL', str(comparison.right))

 def test_bigger_than(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol HAVING a > 0 ORDER BY name")
     comparison = sql_select.having[-2]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual(">", comparison.operator)
     self.assertEqual("a", str(comparison.left))
     self.assertEqual("0", str(comparison.right))

 def test_bigger_than_function_expression(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol WHERE a > now() - INTERVAL '5 DAYS'")
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual(">", comparison.operator)
     self.assertEqual("a", str(comparison.left))
     self.assertEqual("now() - eval_datetime('INTERVAL', '5 DAYS')", str(comparison.right))

 def test_support_now_and_interval(self):
     datetime_evaluator.NOW = datetime.datetime(2015, 1, 2)
     sql_select = SqlSelect.parse(
         "SELECT * FROM index('symbol-%Y-%m-%d', now()-interval('1 days'), timestamp('2015-01-03 00:00:00')) "
         "AS my_table GROUP BY name"
     )
     self.assertEqual("symbol-2015-01-01,symbol-2015-01-02,symbol-2015-01-03", sql_select.from_indices)

 def test_in(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol WHERE symbol IN ('AAPL', 'GOOG')")
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual("IN", comparison.operator)
     self.assertEqual("symbol", str(comparison.left))
     self.assertEqual("('AAPL', 'GOOG')", str(comparison.right))

 def test_not_in(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol WHERE a NOT IN (1,2,3)")
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual("NOT IN", comparison.operator)
     self.assertEqual("a", str(comparison.left))
     self.assertEqual("(1,2,3)", str(comparison.right))

 def test_not_like(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol WHERE a NOT LIKE 'abc%'")
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual("NOT LIKE", comparison.operator)
     self.assertEqual("a", str(comparison.left))
     self.assertEqual("'abc%'", str(comparison.right))

 def test_in(self):
     sql_select = SqlSelect.parse('SELECT * FROM symbol WHERE a IN (1,2,3)')
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual('IN', comparison.operator)
     self.assertEqual('a', str(comparison.left))
     self.assertEqual('(1,2,3)', str(comparison.right))

 def test_parameter(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol WHERE a = %(param)s")
     comparison = sql_select.where.tokens[-1]
     self.assertEqual(stypes.Comparison, type(comparison))
     self.assertEqual("=", comparison.operator)
     self.assertEqual("a", str(comparison.left))
     self.assertEqual("%(param)s", str(comparison.right))
     self.assertEqual(ttypes.Name.Placeholder, comparison.right.ttype)

    def test_where_should_not_be_part_of_join_condition(self):
        sql_select = SqlSelect.parse(
            """select  phone from cn_tag_data_info_es
join base_info on  cn_tag_data_info_es.phone = base_info.phone
where cn_tag_data_info_es.date_str='2016-07-07'
group by phone
            """)
        self.assertIsNotNone(sql_select.where)

 def test_projection_is_wildcard(self):
     sql_select = SqlSelect.parse("SELECT * FROM symbol")
     self.assertEqual(["*"], sql_select.projections.keys())
     self.assertEqual(ttypes.Wildcard, sql_select.projections["*"].ttype)
     self.assertIsNone(sql_select.where)
     self.assertEqual(dict(), sql_select.group_by)
     self.assertEqual([], sql_select.order_by)
     self.assertEqual([], sql_select.having)
     self.assertIsNone(sql_select.limit)

 def test_projection_is_expression_without_function(self):
     sql_select = SqlSelect.parse("SELECT a/2 AS abc FROM symbol")
     self.assertEqual(["abc"], sql_select.projections.keys())
     self.assertEqual(stypes.Expression, type(sql_select.projections["abc"]))
     self.assertEqual("a/2", str(sql_select.projections["abc"]))
     self.assertIsNone(sql_select.where)
     self.assertEqual(dict(), sql_select.group_by)
     self.assertEqual([], sql_select.order_by)
     self.assertEqual([], sql_select.having)
     self.assertIsNone(sql_select.limit)

    def test_group_by_and_having(self):
        sql_select = SqlSelect.parse(
            """SELECT phone, sum(cr_car_xiangqing_count) as total_count
  FROM cn_tag_data_info_es
      JOIN base_info ON cn_tag_data_info_es.phone = base_info.phone
 WHERE date_str >= "2016-05-01" and date_str <= "2016-05-03"
 GROUP BY phone
 HAVING total_count > 9""")
        self.assertIsNotNone(sql_select.where)
        self.assertIsNotNone(sql_select.having)