高性能SQL解析库-fast-sqlparse

原本是我写的一个C 17跨平台SQL解析库后面用pybind11编译成了pyd和so文件然后二次开发而来他的速度有一定的损失但是我们解析SQL更简单、更快、更直观了。经过一年7个大版本的迭代开发、反复测试和不断完善今年我把它发布到github上希望有人能看到、使用、提出问题和意见。相比与纯python库sqlparse、sqlglot等它的速度通常会快出数十倍。另外它还非常适合解析超大、复杂查询、子查询深度嵌套、公共表达式混用的场景。GitHub仓库https://github.com/Nohaltsail/fast-pysqlparse目录安装快速开始核心类说明功能演示性能对比API参考安装pipinstallfast-pysqlparse快速开始fromfastsqlparseimportParsed,ParsedQuery# 解析SQLsqlSELECT * FROM users WHERE age 18parsedParsed(sql)# 获取解析结果queryparsed.parsedforest[0]print(query.sources)# 数据源print(query.columns)# 列信息print(query.format())# 格式化输出核心类说明1. Parsed - SQL解析器主类功能: 解析任意SQL语句SELECT、INSERT、UPDATE、DELETE、CREATE等参数:sql_statements(str): SQL语句字符串file(str, optional): SQL文件路径name(str, optional): 解析内容名称pure(bool, defaultFalse): 是否忽略注释主要属性和方法:parsedforest: 返回解析后的语句列表statements: 所有SQL语句tokens(): 获取词法单元AST(): 获取抽象语法树JSON格式format(indent): 格式化SQLcontent(): 获取原始SQL内容name: SQL语句名称示例:fromfastsqlparseimportParsed sqlSELECT u.id, u.name FROM users u WHERE u.age 18parsedParsed(sql)# 获取解析树itemsparsed.parsedforest# 格式化formattedparsed.format(indent )# 获取ASTast_jsonparsed.AST()# 获取Tokenstokensparsed.tokens()2. ParsedQuery - SELECT查询解析器功能: 专门解析SELECT查询语句提取查询子句和元数据参数:statement(str): SELECT语句name(str): 查询名称pure(bool, defaultFalse): 是否去除注释主要属性:sources: 数据源列表FROM/JOIN的表columns: 选择的列列表clause_select: SELECT子句内容clauses: 子句列表FROM子句内容WHERE子句内容GROUP BY/HAVING子句ORDER BY子句LIMIT子句parent: Parsed父对象cte: CTE映射字典unions: UNION查询列表subquery: 子查询信息level: 嵌套层级主要方法:format(indent, init_indent): 格式化查询ast(): 生成ASTtokens(): 获取Tokenstokenize(statement): 静态方法快速词法分析示例:fromfastsqlparseimportParsedQuery sql SELECT u.user_id, COUNT(o.order_id) as cnt FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE u.status active GROUP BY u.user_id HAVING cnt 5 ORDER BY cnt DESC LIMIT 10 queryParsedQuery(sql,user_orders)# 提取信息print(数据源:,query.sources)print(列:,query.columns)fori,clauseinenumerate(query.clauses):ifclause.partCLAUSE_FROM:print(fFROM子句:{clause.clause})elifclause.partCLAUSE_WHERE:print(fWHERE条件:{clause.clause})elifclause.partCLAUSE_AGGREGATION:print(fGROUP BY:{clause.clause})elifclause.partCLAUSE_SORT:print(fORDER BY:{clause.clause})elifclause.partCLAUSE_LIMIT:print(fLIMIT:{clause.clause})# 快速tokenizertokensParsedQuery.tokenize(sql)fortoken_type,token_value,posintokens[:5]:print(f{token_type}:{token_value})3. ParsedCTE - 公用表表达式解析器功能: 解析WITH子句CTE参数:statement(str): WITH语句pure(bool, defaultFalse): 是否去除注释name(str, optional): CTE名称主要属性:raw: 原始CTE语句cte_stmts: CTE语句列表name: CTE名称主要方法:format(indent, init_indent): 格式化CTEast(): 生成ASTtokenize(statement): 静态方法快速词法分析示例:fromfastsqlparseimportParsedCTE,ParsedQuery sql WITH RECURSIVE cte AS ( SELECT 1 as n UNION ALL SELECT n 1 FROM cte WHERE n 10 ) cteParsedCTE(sql)print(CTE语句:,cte.cte_stmts)print(格式化:\n,cte.format())sql WITH RECURSIVE cte AS ( SELECT 1 as n UNION ALL SELECT n 1 FROM cte WHERE n 10 ) SELECT * FROM cte ctesParsedQuery(sql,test).cteforcte_nameinctes:print(CTE名称:,cte_name)print(CTE语句:,ctes[cte_name].format())4. ParsedInsert - INSERT语句解析器功能: 解析INSERT语句支持VALUES和SELECT两种方式参数:statement(str): INSERT语句pure(bool, defaultFalse): 是否去除注释主要属性:name: 目标表名columns: 插入的列列表values: 插入的值query: 查询对象INSERT…SELECT时query_load: 是否有查询加载main_stmt: 主语句cte_stmt: CTE语句query_stmt: 查询语句主要方法:format(indent, init_indent): 格式化ast(): 生成ASTtokens(): 获取Tokenstokenize(statement): 静态方法快速词法分析示例:fromfastsqlparseimportParsedInsert sql1INSERT INTO users (id, name) VALUE (1, Alice)insert1ParsedInsert(sql1)print(表名:,insert1.name)print(列:,insert1.columns)print(值:,insert1.values)# SELECT方式带CTEsql2 INSERT INTO summary (product_id, total) WITH stats AS ( SELECT product_id, SUM(amount) as total FROM orders GROUP BY product_id ) SELECT product_id, total FROM stats sts insert2ParsedInsert(sql2)print(表名:,insert2.name)print(有查询:,insert2.query_load)ifinsert2.query:forsourceininsert2.query.sources:print(子句:,source.raw)print(表:,source.table)print(别名:,source.alias)5. 其他解析器类ParsedView - VIEW解析器fromfastsqlparseimportParsedView sqlCREATE VIEW active_users AS SELECT * FROM users WHERE statusactiveviewParsedView(sql)ParsedUpdate - UPDATE解析器fromfastsqlparseimportParsedUpdate sqlUPDATE users SET statusinactive WHERE last_login 2023-01-01updateParsedUpdate(sql)ParsedDelete - DELETE解析器fromfastsqlparseimportParsedDelete sqlDELETE FROM logs WHERE created_at 2023-01-01deleteParsedDelete(sql)ParsedCreate - CREATE TABLE解析器fromfastsqlparseimportParsedCreate sql CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(200) ) createParsedCreate(sql)功能演示场景1: 普通查询含子查询fromfastsqlparseimportParsed sql SELECT u.user_id, u.username, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.user_id) as order_count FROM users u WHERE u.age 18 ORDER BY u.username LIMIT 10 parsedParsed(sql)queryparsed.parsedforest[0]# 提取关键信息print(数据源:,query.sources)print(列:,query.columns)print(SELECT子句:,query.clause_select)forclauseinquery.clauses:ifclause.partCLAUSE_FROM:print(fFROM子句:{clause.clause})elifclause.partCLAUSE_WHERE:print(fWHERE子句:{clause.clause})elifclause.partCLAUSE_SORT:print(fORDER BY子句:{clause.clause})elifclause.partCLAUSE_LIMIT:print(fLIMIT子句:{clause.clause})输出:数据源: [DqlSourceExpr object] 列: [DqlColumnExpr object, ...] SELECT子句: [u.user_id, u.username, (SELECT COUNT(*) ...) as order_count] FROM子句: FROM users u WHERE子句: WHERE u.age 18 ORDER BY子句: ORDER BY u.username LIMIT子句: LIMIT 10场景2: 临时结果集聚合查询fromfastsqlparseimportParsedimportjson sql WITH sales_summary AS ( SELECT product_id, SUM(amount) as total_sales, AVG(amount) as avg_sales FROM sales WHERE sale_date 2024-01-01 GROUP BY product_id ) SELECT * FROM sales_summary WHERE total_sales 1000 parsedParsed(sql)# 获取Tokenstokensparsed.tokens()print(fToken数量:{len(tokens)})# 获取ASTast_strparsed.AST()ast_objjson.loads(ast_str)print(json.dumps(ast_obj,indent2,ensure_asciiFalse))场景3: UNION查询 TokenizerfromfastsqlparseimportParsedQuery sql WITH region_sales AS ( SELECT region, SUM(amount) as total FROM sales GROUP BY region ) SELECT * FROM region_sales UNION ALL SELECT TOTAL as region, SUM(total) FROM region_sales # 使用Tokenizer进行快速词法分析tokensParsedQuery.tokenize(sql)fortoken_type,token_value,positionintokens:print(fType:{token_type:15}| Value:{token_value[:30]:30}| Pos:{position})场景4: INSERT INTO … CTE SELECTfromfastsqlparseimportParsedInsert sql INSERT INTO summary_table (product_id, total_amount, avg_amount) WITH product_stats AS ( SELECT product_id, SUM(amount) as total_amount, AVG(amount) as avg_amount FROM orders GROUP BY product_id ) SELECT product_id, total_amount, avg_amount FROM product_stats insertParsedInsert(sql)print(目标表:,insert.name)print(插入列:,insert.columns)print(有查询:,insert.query_load)ifinsert.query:print(查询类型:,type(insert.query))print(查询来源:,insert.query.sources)print(查询列:,insert.query.columns)场景5: 处理注释和格式化fromfastsqlparseimportParsed,strip_note sql -- 这是主注释 SELECT u.user_id, -- 用户ID u.username -- 用户名 FROM users u /* 用户表 */ WHERE u.status active -- 只查活跃用户 # 保留注释并格式化parsed_with_commentsParsed(sql,pureFalse)print(保留注释:)print(parsed_with_comments.format())# 去除注释并格式化parsed_pureParsed(sql,pureTrue)print(\n去除注释:)print(parsed_pure.format())# 仅去除注释不格式化strippedstrip_note(sql)print(\n仅去注释:)print(stripped)性能对比测试环境SQL长度: 1359字符测试次数: 100次性能结果解析器总耗时(100次)平均每次相对速度fast-pysqlparse0.0170秒0.17ms1.0x(基准)sqlparse1.3040秒13.04ms76.75x更快sqlglot0.4283秒4.28ms25.21x更快大规模测试测试1: 5000次解析SQL长度: 639字符总耗时: 0.6084秒PPS (Parses Per Second): 8218.88平均每次: 0.1217ms测试2: 1000万字符SQLSQL长度: 10,500,998字符总耗时: 1.4085秒CPS (Characters Per Second): 7,455,540解析成功API参考工具函数strip_note(sql: str) - str去除SQL中的注释fromfastsqlparseimportstrip_note sqlSELECT * FROM users -- commentcleanstrip_note(sql)# 结果: SELECT * FROM usersformat(sql: str, indent: str ) - str格式化SQL语句fromfastsqlparseimportformatsqlSELECT * FROM users WHERE id1formattedformat(sql,query,indent )tokenize(sql: str) - List[Tuple[str, str, int]]词法分析tokenize_query(sql: str) - List[Tuple[str, str, int]]快速词法分析SELECT语句fromfastsqlparseimporttokenize_query tokenstokenize_query(SELECT * FROM users)tokenize_cte(sql: str) - List[Tuple[str, str, int]]快速词法分析WITH语句tokenize_insert(sql: str) - List[Tuple[str, str, int]]快速词法分析INSERT语句tokenize_update(sql: str) - List[Tuple[str, str, int]]快速词法分析UPDATE语句tokenize_delete(sql: str) - List[Tuple[str, str, int]]快速词法分析DELETE语句tokenize_view(sql: str) - List[Tuple[str, str, int]]快速词法分析VIEW语句Token结构每个Token包含以下属性:type: Token类型KEYWORD, IDENTIFIER, LITERAL, WHITESPACE等value: Token的值position: 在SQL中的位置tokensparsed.tokens()fortokenintokens:print(fType:{token.type}, Value:{token.value}, Pos:{token.at})AST结构AST以JSON格式返回包含:查询子句SELECT, FROM, WHERE等CTE定义列信息数据源信息联合查询信息importjson ast_json_listparsed.AST()ast_json_dicparsed_query.ast()ast_objjson.loads(ast_json_dic)最佳实践1. 选择合适的解析器通用SQL: 使用Parsed仅SELECT: 使用ParsedQuery更快仅INSERT: 使用ParsedInsert仅CTE: 使用ParsedCTE2. 性能优化如果只需要词法信息使用tokenize()静态方法设置pureTrue可以跳过注释处理提升速度避免重复解析相同SQL缓存解析结果3. 错误处理fromfastsqlparseimportParsedtry:parsedParsed(invalid_sql)exceptExceptionase:print(f解析失败:{e})常见问题Q1: 如何提取表名queryparsed.parsedforest[0]forsourceinquery.sources:print(source.table)# 或查看source的属性Q2: 如何处理多语句SQLparsedParsed(SELECT * FROM t1; SELECT * FROM t2;)forstmtinparsed.parsedforest:print(stmt)Q3: 如何获取子查询信息queryparsed.parsedforest[0]ifquery.subquery:forsubqinquery.subquery:print(subq)