【无标题】

最新推荐文章于 2024-10-08 20:27:53 发布

En_answer

最新推荐文章于 2024-10-08 20:27:53 发布

阅读量58

点赞数

文章标签： python

本文链接：https://blog.csdn.net/weixin_39782563/article/details/123390632

版权

SQL解析公共表表达式查询优化数据库操作 Python库

关键词由CSDN通过智能技术生成

# -*- coding: utf-8 -*-
# @Time    : 2022/3/9 20:44
# @Author  : wanged
# @File    : test.py
# @Desc    :

from sqlparser import parse_statement
from sqlparser.utils import print_tree
from antlr4 import ParserRuleContext
import re

sql1 = 'insert into db1.table0 select t1.name, t1.age, t2.gender, t2.address from db1.table1 t1 join db1.table2 t2 on t1.id=t2.id'
sql2 = """
with cte1 as (select id, name from db.table1 where name like 'abc%'), cte2 as (select id, age, gender from db.table2 where id > 20), cte3 as (select id, address from db.table3) insert into db.result select a.name, b.age, b.gender, c.address from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
"""


tb_list = []


def get_table_position(sql, node):
    if isinstance(node, ParserRuleContext):
        class_name = str(type(node))
        if str(class_name) == "<class 'sqlparser.generated.SqlBaseParser.SqlBaseParser.NamedQueryContext'>":
            tb_list.append(
                sql[int(re.split(r'[,:]', str(node.start))[1]):int(re.split(r'[:=]', str(node.start))[1]) + 1])

            return tb_list
    if hasattr(node, 'children') and getattr(node, 'children'):
        for child in getattr(node, 'children'):
            get_table_position(sql, child)
    return tb_list

tree = parse_statement(sql2)
get_table_position(sql2, tree)
print(tb_list)