在函数名称上拆分SQL语句，但在Python中保留定界符

debugcn 发表于 Dev

东京

假设我具有string包含从SELECT子句中提取的SQL语句的以下内容（实际上，这是一个巨大的SQL语句，其中包含数百个此类语句）；

  SUM(case when(A.money-B.money>1000
                and A.unixtime-B.unixtime<=890769
                and B.col10 = "A"
                and B.col11 = "12"
                and B.col12 = "V") then 10
      end) as finalCond0,
  MAX(case when(A.money-B.money<0
                and A.unixtime-B.unixtime<=6786000
                and B.cond1 = "A"
                and B.cond2 = "4321"
                and B.cond3 in ("E", "F", "G")) then A.col10
        end) as finalCond1,
  SUM(case when(A.money-B.money>0
                and A.unixtime-B.unixtime<=6786000
                and B.cond1 = "A"
                and B.cond2 = "1234"
                and B.cond3 in ("A", "B", "C")) then 2
      end) as finalCond2

我怎么能在功能拆分此查询（即SUM，MAX，MIN，MEAN等），这样我可以提取一次查询，但不删除分隔符（在这种情况下SUM）？

因此，所需的输出将是类似于以下内容的字符串：

  SUM(case when(A.money-B.money>0
                and A.unixtime-B.unixtime<=6786000
                and B.cond1 = "A"
                and B.cond2 = "1234"
                and B.cond3 in ("A", "B", "C")) then 2
      end) as finalCond2

PS：出于演示目的，我提供了某种缩进，但实际上，这些语句之间用逗号分隔，这意味着原始格式中不会出现空格或新行。

马丁·彼得斯（Martijn Pieters）

您不能在此处使用正则表达式，因为SQL语法无法形成正则模式，因此可以与Pythonre引擎匹配。您实际上必须将字符串解析为令牌流或语法树。SUM(...)毕竟，您可以包含多种语法，包括子选择。

该sqlparse库可以做到这一点，即使它的文档不足，并且对外部使用不太友好。

重用walk_tokens我在链接到的另一篇文章中定义的功能：

from collections import deque
from sqlparse.sql import TokenList

def walk_tokens(token):
    queue = deque([token])
    while queue:
        token = queue.popleft()
        if isinstance(token, TokenList):
            queue.extend(token)
        yield token

SELECT然后从标识符列表中提取最后一个元素是：

import sqlparse
from sqlparse.sql import IdentifierList

tokens = sqlparse.parse(sql)[0]
for tok in walk_tokens(tokens):
    if isinstance(tok, IdentifierList):
        # iterate to leave the last assigned to `identifier`
        for identifier in tok.get_identifiers():
            pass
        break

print(identifier)

演示：

>>> sql = '''\
...   SUM(case when(A.money-B.money>1000
...                 and A.unixtime-B.unixtime<=890769
...                 and B.col10 = "A"
...                 and B.col11 = "12"
...                 and B.col12 = "V") then 10
...       end) as finalCond0,
...   MAX(case when(A.money-B.money<0
...                 and A.unixtime-B.unixtime<=6786000
...                 and B.cond1 = "A"
...                 and B.cond2 = "4321"
...                 and B.cond3 in ("E", "F", "G")) then A.col10
...         end) as finalCond1,
...   SUM(case when(A.money-B.money>0
...                 and A.unixtime-B.unixtime<=6786000
...                 and B.cond1 = "A"
...                 and B.cond2 = "1234"
...                 and B.cond3 in ("A", "B", "C")) then 2
...       end) as finalCond2
... '''
>>> tokens = sqlparse.parse(sql)[0]
>>> for tok in walk_tokens(tokens):
...     if isinstance(tok, IdentifierList):
...         # iterate to leave the last assigned to `identifier`
...         for identifier in tok.get_identifiers():
...             pass
...         break
...
>>> print(identifier)
SUM(case when(A.money-B.money>0
                and A.unixtime-B.unixtime<=6786000
                and B.cond1 = "A"
                and B.cond2 = "1234"
                and B.cond3 in ("A", "B", "C")) then 2
      end) as finalCond2

identifier是sqlparse.sql.Identifier实例，但是再次将其转换为字符串（可以print()，或者也可以使用str()）再次为该部分提供输入SQL字符串。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。