Python用CONCAT函数替换| |运算符

Question

我试图解析一个SQL语句，如下所示。你知道吗

SELECT '-' || IPC.ITEM, IPC.first_name||' '||ISA.last_name AS DEA FROM IpcDetails IPC JOIN IsaDetails ISA ON IPC.ItemId = ISA.ItemId

我要求用CONCAT函数替换| |运算符，如下所示

SELECT CONCAT('-',IPC.ITEM), CONCAT(IPC.first_name,' ',ISA.last_name) AS DEA FROM IpcDetails IPC JOIN IsaDetails ISA ON IPC.ItemId = ISA.ItemId

我想用Regex把表达式拆分成| |并用函数替换操作符，有人能帮我实现吗？你知道吗

Answer 1

可能您应该为此使用一些SQL解析器。但是你可以尝试用正则表达式来解决这个问题，正则表达式可能不是解决这类问题的最佳工具。你知道吗

下面是一个示例（检查注释以查看代码的作用）：

import re

sql = "SELECT '-' || IPC.ITEM AS ITEM, IPC.first_name||' '||ISA.last_name, IPC.NAME, '0' || IPC.BAR AS DEA " \
      "FROM IpcDetails IPC JOIN IsaDetails ISA ON IPC.ItemId = ISA.ItemId;" \
      "SELECT '-' || IPC.ITEM, IPC.first_name||' '||ISA.last_name, IPC.NAME, '0' || IPC.BAR, IPC.FOO || '_BAR' || 'TEST' AS DEA " \
      "FROM IpcDetails IPC JOIN IsaDetails ISA ON IPC.ItemId = ISA.ItemId"

# Split all selects
selects = (re.findall(r'SELECT(.+?)FROM(.+?)(;|$)', sql, re.IGNORECASE))
for select in selects:
    origFields = select[0]
    fieldList = []
    # Split fields : not great to use split here as there might be commas inside of strings
    for field in origFields.split(","):
        # Split the field and the alias
        fieldAndAlias = re.search('(.+?)(\sAS.+|$)', field, re.IGNORECASE)
        if fieldAndAlias.group(1).find("||") > -1: # Check if we should do the transformation from || to CONCAT()
            concat = "CONCAT(%s)" % ",".join(fieldAndAlias.group(1).split("||"))
            concat += fieldAndAlias.group(2)
            fieldList.append(concat)
        else: # Field where no concat to be done
            fieldList.append(field)
    # Put everthing back again.
    newSQL = "SELECT %s FROM %s" % (",".join(fieldList), select[1])
    print(newSQL)

如果运行此脚本，您将在控制台上看到以下内容：

SELECT CONCAT( '-' , IPC.ITEM) AS ITEM,CONCAT( IPC.first_name,' ',ISA.last_name), IPC.NAME,CONCAT( '0' , IPC.BAR) AS DEA  FROM  IpcDetails IPC JOIN IsaDetails ISA ON IPC.ItemId = ISA.ItemId
SELECT CONCAT( '-' , IPC.ITEM),CONCAT( IPC.first_name,' ',ISA.last_name), IPC.NAME,CONCAT( '0' , IPC.BAR),CONCAT( IPC.FOO , '_BAR' , 'TEST') AS DEA  FROM  IpcDetails IPC JOIN IsaDetails ISA ON IPC.ItemId = ISA.ItemId

此解决方案不处理子选择，也不处理没有“AS”关键字的别名。所以是的：您需要一个合适的SQL解析器来完成真正专业的工作。正则表达式不足以胜任这项工作。你知道吗