Python SQL解析器函数报错求助：语法错误排查及输出实现

问题背景 #

尝试用Python编写简单SQL解析器，解析以下SELECT语句，但运行时持续出现语法错误，需要排查问题并实现指定格式的输出：

sql_statement = 'SELECT EMPLOYEE.EMPNO, POSITION FROM EMPLOYEE E, JOBHISTORY J WHERE E.EMPNO = J.EMPNO AND STARTDATE <= ENDDATE'

报错信息 #

Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token COMMA
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token COMMA
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token EQUAL
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token IDENTIFIER
Syntax error at token LESS_EQUAL
Syntax error at token IDENTIFIER
Syntax error at EOF
None

原解析器代码 #

import ply.lex as lex
import ply.yacc as yacc

#define the lexer
tokens = (
    'SELECT',
    'FROM',
    'WHERE',
    'IDENTIFIER',
    'LITERAL',
    'COMMA',
    'EQUAL',
    'AND',
    'LESS_EQUAL',
 )

t_SELECT = r'SELECT'
t_FROM = r'FROM'
t_WHERE = r'WHERE'
t_IDENTIFIER = r'[a-zA-Z_][a-zA-Z0-9_.]*'
t_LITERAL = r'\d+'
t_COMMA = r',' 
t_EQUAL = r'='
t_AND = r'AND'
t_LESS_EQUAL = r'<='
t_ignore = ' '

def p_statement_select(p):
    'statement : SELECT select_list FROM from_clause where_clause'
    # print('SELECT list detected')
    print('SELECT statement detected')
    
def p_select_list(p):
    '''select_list : IDENTIFIER COMMA select_list
                    | IDENTIFIER'''
    print('SELECT list detected')
    
def p_from_clause(p):
    'from_clause : IDENTIFIER COMMA IDENTIFIER'
    print('FROM clause detected')
    
def p_where_clause(p):
    'where_clause : condition'
    print('WHERE clause detected')
              
                    
def p_condition(p):
    '''condition : IDENTIFIER
                | LITERAL
                | condition AND condition
                | condition EQUAL condition
                | condition LESS_EQUAL condition'''
    print('Condition detected')
    
def t_error(t):
    print("Illegal character '%s' at index %d" % (t.value[0], t.lexpos))
    t.lexer.skip(1)
    
    
def p_error(p):
    if p:
        print("Syntax error at token", p.type)
        # just discard the token and tell the parser its okay
        parser.errok()
    else: 
        print("Syntax error at EOF")
        
#Define the lexer
lexer = lex.lex()

#Define the parser
parser = yacc.yacc()

错误排查 #

• 词法匹配优先级错误：关键字（SELECT、FROM等）的正则定义在IDENTIFIER之后，而IDENTIFIER的规则会先匹配到这些关键字，导致它们被识别为IDENTIFIER而非对应关键字token，这是核心语法错误来源。
• FROM子句规则不兼容：原规则只支持两个无别名的表，但实际SQL中表可以带别名（如EMPLOYEE E），规则需要适配带别名的表引用。
• WHERE子句强制必填：原语法规则要求SELECT语句必须带WHERE子句，但实际SQL中WHERE是可选的，导致无WHERE的语句会报错。
• 条件表达式逻辑混乱：原condition规则允许单个标识符作为条件，不符合SQL条件的二元表达式结构，且递归规则存在歧义。
• 未构建目标输出结构：原代码仅打印提示信息，没有收集解析结果并组装成指定格式的元组。

修正后的代码 #

import ply.lex as lex
import ply.yacc as yacc

# 定义token
tokens = (
    'SELECT',
    'FROM',
    'WHERE',
    'IDENTIFIER',
    'LITERAL',
    'COMMA',
    'EQUAL',
    'AND',
    'LESS_EQUAL',
)

# 关键字用函数定义，确保优先匹配
def t_SELECT(t):
    r'SELECT'
    return t

def t_FROM(t):
    r'FROM'
    return t

def t_WHERE(t):
    r'WHERE'
    return t

def t_AND(t):
    r'AND'
    return t

# 其他token定义
t_IDENTIFIER = r'[a-zA-Z_][a-zA-Z0-9_.]*'
t_LITERAL = r'\d+'
t_COMMA = r',' 
t_EQUAL = r'='
t_LESS_EQUAL = r'<='
t_ignore = ' '

# 语法规则与结果构建
def p_statement_select(p):
    '''statement : SELECT select_list FROM from_clause where_clause
                 | SELECT select_list FROM from_clause'''
    select_cols = p[2]
    tables = p[4]
    conditions = p[5] if len(p) == 6 else []
    # 组装指定格式输出
    result = ('SELECT', select_cols, tables, conditions)
    print(result)
    p[0] = result

def p_select_list(p):
    '''select_list : IDENTIFIER COMMA select_list
                   | IDENTIFIER'''
    if len(p) == 4:
        p[0] = [('COLUMN', p[1])] + p[3]
    else:
        p[0] = [('COLUMN', p[1])]

def p_from_clause(p):
    '''from_clause : table_ref COMMA from_clause
                   | table_ref'''
    if len(p) == 4:
        p[0] = [p[1]] + p[3]
    else:
        p[0] = [p[1]]

def p_table_ref(p):
    '''table_ref : IDENTIFIER IDENTIFIER
                 | IDENTIFIER'''
    # 提取表名，忽略别名
    p[0] = ('TABLE', p[1])

def p_where_clause(p):
    'where_clause : WHERE condition_list'
    p[0] = p[2]

def p_condition_list(p):
    '''condition_list : condition AND condition_list
                      | condition'''
    if len(p) == 4:
        p[0] = [p[1]] + p[3]
    else:
        p[0] = [p[1]]

def p_condition(p):
    '''condition : IDENTIFIER EQUAL IDENTIFIER
                 | IDENTIFIER LESS_EQUAL IDENTIFIER
                 | IDENTIFIER EQUAL LITERAL
                 | IDENTIFIER LESS_EQUAL LITERAL'''
    # 构建条件元组
    p[0] = ('CONDITION', p[1], p[2], p[3])

def t_error(t):
    print(f"Illegal character '{t.value[0]}' at index {t.lexpos}")
    t.lexer.skip(1)

def p_error(p):
    if p:
        print(f"Syntax error at token {p.type} (value: {p.value})")
        parser.errok()
    else:
        print("Syntax error at EOF")

# 初始化分析器
lexer = lex.lex()
parser = yacc.yacc()

# 测试解析
sql_statement = 'SELECT EMPLOYEE.EMPNO, POSITION FROM EMPLOYEE E, JOBHISTORY J WHERE E.EMPNO = J.EMPNO AND STARTDATE <= ENDDATE'
parser.parse(sql_statement)

运行结果 #

('SELECT', [('COLUMN', 'EMPLOYEE.EMPNO'), ('COLUMN', 'POSITION')], [('TABLE', 'EMPLOYEE'), ('TABLE', 'JOBHISTORY')], [('CONDITION', 'E.EMPNO', '=', 'J.EMPNO'), ('CONDITION', 'STARTDATE', '<=', 'ENDDATE')])

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