正则解析SQL表名和SQL类型

#该程序可以对SQL进行解析，对/**/hint注释，SQL类型、表名，SQL进行解析。
import re

def extract_sql_info(sql_query):
    # 正则表达式用于匹配操作类型和表名 
    # (?i)^\s*(select|insert|update|delete)\b(?:[\s\S]+?(?:from|into)\s+|(?:\s+))(?:[a-zA-Z_][a-zA-Z0-9_]*\.)?([a-zA-Z_][a-zA-Z0-9_]*)
    insert_pattern = r'^\s*insert\s+into\s+([a-zA-Z_][a-zA-Z0-9_]*)'
    update_pattern = r'^\s*update\s+([a-zA-Z_][a-zA-Z0-9_]*)'
    select_pattern = r'^\s*select\s+.*?\bfrom\s+([a-zA-Z_][a-zA-Z0-9_]*)\b'
    delete_pattern = r'^\s*delete\s+from\s+([a-zA-Z_][a-zA-Z0-9_]*)'
    trace_pattern = r'/\*(.*?)\*/'

    # 切割trace信息
    trace_match = re.search(trace_pattern, sql_query, re.DOTALL)
    trace_info = trace_match.group(1).strip() if trace_match else None
    sql_query = re.sub(trace_pattern, '', sql_query).strip() if trace_match else sql_query.strip()

    # 提取操作类型和表名
    if re.match(insert_pattern, sql_query, re.I):
        operation = 'insert'
        table_match = re.match(insert_pattern, sql_query, re.I)
    elif re.match(update_pattern, sql_query, re.I):
        operation = 'update'
        table_match = re.match(update_pattern, sql_query, re.I)
    elif re.match(select_pattern, sql_query, re.I):
        operation = 'select'
        table_match = re.match(select_pattern, sql_query, re.I)
    elif re.match(delete_pattern, sql_query, re.I):
        operation = 'delete'
        table_match = re.match(delete_pattern, sql_query, re.I)
    else:
        operation = None
        table_match = None

    table_name = table_match.group(1) if table_match else None


    return operation, table_name, sql_query, trace_info

# 测试
sql_query1 = "/*audit:zijie*/ SELECT * FROM employees WHERE department = 'HR';"
sql_query2 = "INSERT INTO products (name, price) VALUES ('Widget', 10.99);"
sql_query3 = "UPDATE customers SET email = 'new_email@example.com' WHERE customer_id = 1;"
sql_query4 = "DELETE FROM orders WHERE order_id = 100;"

queries = [sql_query1, sql_query2, sql_query3, sql_query4]

for query in queries:
    operation, table_name, sql_query, trace_info = extract_sql_info(query)
    print(f"操作类型: {operation}, 表名: {table_name}, SQL语句: {sql_query}, trace: {trace_info}")


#操作类型: select, 表名: employees, SQL语句: SELECT * FROM employees WHERE department = 'HR';, trace: audit:zijie
#操作类型: insert, 表名: products, SQL语句: INSERT INTO products (name, price) VALUES ('Widget', 10.99);, trace: None
#操作类型: update, 表名: customers, SQL语句: UPDATE customers SET email = 'new_email@example.com' WHERE customer_id = 1;, trace: None
#操作类型: delete, 表名: orders, SQL语句: DELETE FROM orders WHERE order_id = 100;, trace: None