使用PyParsing解析含聚合函数的SQL时触发语法错误，求修正

错误原因 #

你的解析规则存在以下核心问题：

• columnName仅定义为简单标识符（如table.column），完全不支持聚合函数表达式（如AVG(salaries.salary)），解析器遇到(时无法匹配任何规则，因此抛出"Expected FROM"的错误。
• WHERE子句仅支持单个比较条件，不处理AND/OR这类逻辑运算符。
• 未支持SQL中的函数调用（如NOW()）。
• 结果提取逻辑依赖固定位置索引（如result[1:]），结构脆弱，容易出错。

修正后的代码 #

from pyparsing import *

ParserElement.enablePackrat()

# Define keywords and symbols
SELECT = CaselessKeyword("SELECT")
FROM = CaselessKeyword("FROM")
JOIN = CaselessKeyword("JOIN")
ON = CaselessKeyword("ON")
WHERE = CaselessKeyword("WHERE")
GROUP_BY = CaselessKeyword("GROUP BY")
HAVING = CaselessKeyword("HAVING")
ORDER_BY = CaselessKeyword("ORDER BY")
AS = CaselessKeyword("AS")
AND = CaselessKeyword("AND")
OR = CaselessKeyword("OR")

COMMA = Suppress(",")
LPAREN = Literal("(")
RPAREN = Literal(")")
DOT, STAR = map(Literal, ".*")
COMPARE_OP = oneOf("> < = >= <= !=", caseless=True)

# Define SQL language elements
ParserElement.setDefaultWhitespaceChars(" \t")
identifier = Regex(r"[_a-zA-Z][_a-zA-Z0-9]*").setName("identifier")
columnName = identifier + ZeroOrMore(DOT + identifier)
tableName = identifier + ZeroOrMore(DOT + identifier)

# 扩展支持函数调用（包括聚合函数和普通函数）
function_call = identifier + LPAREN + Optional(delimitedList(columnName | identifier)) + RPAREN
# 支持列表达式：可以是普通列名或函数调用
column_expr = columnName | function_call
# 列定义：表达式 + 可选别名
column_def = column_expr + Optional(AS + identifier)
column_list = delimitedList(column_def)

# 支持WHERE子句的复合条件
condition = Forward()
simple_condition = column_expr + COMPARE_OP + (column_expr | identifier | Regex(r"\d+"))
condition << simple_condition + ZeroOrMore((AND | OR) + condition)

# 重构SELECT语句规则
selectStatement = SELECT + column_list + FROM + delimitedList(tableName) + \
                  ZeroOrMore(JOIN + tableName + ON + columnName + "=" + columnName) + \
                  Optional(WHERE + condition) + \
                  Optional(GROUP_BY + column_list + Optional(HAVING + simple_condition)) + \
                  Optional(ORDER_BY + column_list + Optional(CaselessKeyword("ASC") | CaselessKeyword("DESC")))


def extract_columns_and_tables(query):
    column_aliases = {}
    table_aliases = {}
    try:
        result = selectStatement.parseString(query, parseAll=True)
        
        # 提取列和别名：遍历SELECT后的列定义
        idx = result.index(SELECT) + 1
        while result[idx] != FROM:
            col_def = result[idx]
            if isinstance(col_def, ParseResults):
                # 处理带别名的情况
                if len(col_def) == 3 and col_def[1] == AS:
                    expr_str = "".join(map(str, col_def[0]))
                    alias_str = str(col_def[2])
                    column_aliases[expr_str] = alias_str
                else:
                    # 不带别名的列表达式
                    expr_str = "".join(map(str, col_def))
                    column_aliases[expr_str] = expr_str
            idx += 1
        
        # 提取表名：遍历FROM后的表
        idx = result.index(FROM) + 1
        while idx < len(result) and result[idx] not in (JOIN, WHERE, GROUP_BY, ORDER_BY):
            table_str = "".join(map(str, result[idx]))
            table_aliases[table_str] = table_str
            idx += 1
            # 跳过JOIN相关的内容
            while idx < len(result) and result[idx] == JOIN:
                idx += 4  # 跳过JOIN、table、ON、条件组合，直接到下一个表或关键字
                
    except ParseException as e:
        print("Error: ", e)
    
    return column_aliases, table_aliases


if __name__ == '__main__':
    query = "SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name, AVG(salaries.salary) AS average_salary FROM employees JOIN salaries ON employees.employee_id = salaries.employee_id JOIN dept_emp ON employees.employee_id = dept_emp.employee_id JOIN departments ON dept_emp.dept_no = departments.dept_no WHERE salaries.to_date > NOW() AND dept_emp.to_date > NOW() GROUP BY employees.employee_id, departments.department_name HAVING AVG(salaries.salary) > 50000 ORDER BY average_salary DESC"
    column_aliases, table_aliases = extract_columns_and_tables(query)

    print("Column aliases: ", column_aliases)
    print("Table aliases: ", table_aliases)

关键修正点 #

1. 支持聚合函数与函数调用：新增function_call规则匹配AVG(salaries.salary)、NOW()这类结构，将column_expr扩展为普通列名或函数调用。
2. 支持复合WHERE条件：通过condition递归规则处理AND/OR连接的多个条件。
3. 健壮的结果提取逻辑：不再依赖固定索引，而是通过关键字（FROM、JOIN等）定位列和表的位置，避免结构变化导致的错误。
4. 使用parseAll=True：确保解析整个SQL语句，避免遗漏未解析的部分。

内容的提问来源于stack exchange，提问作者Torque