如何从复杂的sql查询语句中提取所有来源表名
本文使用Python实现
一、背景
前段时间开发了一个小工具用来检测一些sql脚本，其中有一个步骤需要将查询语句的各个来源表都提取出来。本来借助数据库是可以实现的，但该方法有局限性。所以我一直在想，能不能通过纯文本分析的方法来解决这个问题。
网上虽然能找到类似的解决方法，但基本都不是通用的脚本，只能解决某些句式，考虑的场景太少了，想要找到通用的脚本，只能靠自己写了。
最后经过一番费力的分析拆解，终于写出来一个相对来说比较通用的脚本。
二、题外话
先说一下借助数据库可以怎么实现。
在oracle中，有个数据字典dba_dependences，可以查出某个对象（包、视图、表）所依赖的表和视图。假如我们想要借助数据库来实现这个功能，那么很简单，只要用sql查询语句先创建一个固定名字的视图，然后查询该视图的依赖的所有表，即可获得该查询语句的所有来源表。
但这种方式有2个弊端：
1、查询语句的来源表，必须是已经在数据库里建好的；
2、如果需要批量处理，频繁地连接数据库建视图然后查询依赖表，这个过程资源开销较大，中间需要等待的时间较长；
三、正题
那么如何通过文本分析的方式来解决这个问题呢？
1、先分析sql句式
分析日常我们编写的sql查询语句，大概有哪些句式，通过归纳总结，针对这些句式去分析如何按步骤拆解，简化，统一。
通过分析，可以把日常的sql查询语句归纳为以下的多种句式
一.单表查询语句
--1、单表查询，没有过滤条件，没有分组条件
SELECT [列名]
FROM [表名]
--2、单表查询，有过滤条件，没有分组条件
SELECT [列名]
FROM [表名]
WHERE [过滤条件]
--3、单表查询，没有过滤条件，有分组条件
SELECT [列名]
FROM [表名]
GROUP BY [分组条件]
--4、单表查询，带子查询
SELECT [列名]
FROM ([子查询语句])
GROUP BY [分组条件]
二.多表关联查询
--1、Oracle特有写法
SELECT [列名]
FROM [表名1],
[表名2],
[表名3]
WHERE [关联和过滤条件]
--2.1、sql标准写法
SELECT [列名]
FROM [表名1]
JOIN [表名2]
ON 表1.XX = 表2.XX
--2.2、sql标准写法2
SELECT [列名]
FROM [表名1]
INNER JOIN [表名2]
ON 表1.XX = 表2.XX
--2.3、sql标准写法3
SELECT [列名]
FROM [表名1]
LEFT JOIN [表名2]
ON 表1.XX = 表2.XX
--2.4、sql标准写法4
SELECT [列名]
FROM [表名1]
RIGHT JOIN [表名2]
ON 表1.XX = 表2.XX
三.多表关联查询嵌套子查询(上述所有情况把表名替换成子查询即可)
四.WITH临时表写法
--1.1、WITH单临时表，单表查询，无过滤条件，无分组条件
WITH TMP AS (
子查询
)
SELECT [列名] FROM TMP;
--1.2、WITH单临时表，单表查询，有过滤条件，无分组条件
WITH TMP AS (
子查询
)
SELECT [列名] FROM TMP WHERE [过滤条件];
--1.3、WITH单临时表，单表查询，无过滤条件，有分组条件
WITH TMP AS (
子查询
)
SELECT [列名] FROM TMP GROUP BY [分组条件];
--2、WITH多临时表， 单表查询...
WITH TMP1 AS (
子查询1
),
TMP2 AS (
子查询2
)
SELECT [列名] FROM TMP1;
--3、WITH多临时表， 多表关联查询, ORACLE写法
WITH TMP1 AS (
子查询1
),
TMP2 AS (
子查询2
)
SELECT [列名]
FROM TMP1,
TMP2
WHERE TMP1.XX = TMP2.XX(+) --（+）可有可无
--4、WITH多临时表， 多表关联查询, 标准sql写法
WITH TMP1 AS (
子查询1
),
TMP2 AS (
子查询2
)
SELECT [列名]
FROM TMP1
JOIN TMP2 --JOIN 可能替换为LEFT JOIN , INNER JOIN , RIGHT JOIN
ON TMP1.XX = TMP2.XX(+)
2、拟定处理流程，绘制流程图
有了上面的句式，我们就可以思考如何按步骤来识别判断。
经过思考，上述的多种句式可以通过以下流程逐步拆解处理
3、开始编写代码
按照上面的流程来编写，细节处理自己多考虑一下，最终写出的代码如下
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# date: 2021/6/6
# filename: get_tables_from_sql
# author: kplin
import re
def get_query_sql(file_path):
'''读取文本中的sql'''
with open(file_path, 'r', encoding='utf-8') as f:
# 文本预处理：
# 1、打开文件，获取文本内容，转换成大写，去除前后空格，去除换行符
content = f.read().upper().strip(' ').split('\n')
content_list = []
for i in content:
if not i.startswith('--') and i != '':
# 2、去除单行注释
j = i if '--' not in i else i.replace(i, i.split('--')[0])
# 3、去除tab符
content_list.append(j.replace('\t', ' '))
# 4、双空格转为单空格
content = ' '.join(content_list).replace(' ', ' ').replace(' ', ' ')
# 5、去除多行注释
comments = re.findall('/\*.*?\*/', content)
for i in comments:
content = content.replace(i, '')
return content
def with_query(query_sql):
'''传进一个with语句，返回临时表名，子查询和主查询'''
# 先把（+） 替换成###++###, 避免干扰判断, 后面再替换回来，把双空格转换成单空格
query_sql = query_sql.replace('(+)', '###++###').replace(' ', ' ').replace(' ', ' ')
# 把AS (), 或者AS () , 或者AS ()SELECT 或者 AS () SELECT中间部分提取出来(这里分多个情况处理)
tmp_querys = []
tmp_querys1 = re.findall(r'AS \(.*?\),', query_sql)
if len(tmp_querys1) != 0:
for i in tmp_querys1:
query_sql = query_sql.replace(i, '')
tmp_querys2 = re.findall(r'AS \(.*?\) ,', query_sql)
if len(tmp_querys2) != 0:
for i in tmp_querys2:
query_sql = query_sql.replace(i, '')
tmp_querys3 = re.findall(r'AS \(.*?\) SELECT', query_sql)
if len(tmp_querys3) != 0:
for i in tmp_querys3:
i = i[:-7]
query_sql = query_sql.replace(i, '')
tmp_querys4 = re.findall(r'AS \(.*?\)SELECT', query_sql)
if len(tmp_querys4) != 0:
for i in tmp_querys4:
i = i[:-6]
query_sql = query_sql.replace(i, '')
tmp_querys5 = re.findall(r'AS\(.*?\),', query_sql)
if len(tmp_querys5) != 0:
for i in tmp_querys5:
query_sql = query_sql.replace(i, '')
tmp_querys6 = re.findall(r'AS\(.*?\) ,', query_sql)
if len(tmp_querys6) != 0:
for i in tmp_querys6:
query_sql = query_sql.replace(i, '')
tmp_querys7 = re.findall(r'AS\(.*?\) SELECT', query_sql)
if len(tmp_querys7) != 0:
for i in tmp_querys7:
i = i[:-7]
query_sql = query_sql.replace(i, '')
tmp_querys8 = re.findall(r'AS\(.*?\)SELECT', query_sql)
if len(tmp_querys8) != 0:
for i in tmp_querys8:
i = i[:-6]
query_sql = query_sql.replace(i, '')
for i in [tmp_querys1, tmp_querys2, tmp_querys3, tmp_querys4, tmp_querys5, tmp_querys6, tmp_querys7, tmp_querys8]:
if len(i) != 0:
tmp_querys.extend(i)
# 把AS ()中间的子查询提取，获得临时表的查询语句
sub_querys = []
for i in tmp_querys:
for j in re.findall(r'AS \((.*?)\),', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS \((.*?)\) ,', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS \((.*?)\) SELECT', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS \((.*?)\)SELECT', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS\((.*?)\),', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS\((.*?)\) ,', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS\((.*?)\) SELECT', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
for j in re.findall(r'AS\((.*?)\)SELECT', i):
sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' '))
# 截取with和第一个select之间的部分，获取临时表名
tmp_names = query_sql[query_sql.index('WITH') + 4 : query_sql.index('SELECT')].strip(' ').split(' ')
tmp_names = [i for i in tmp_names if i != '']
tmp_names = [i.strip(' ') for i in tmp_names]
# 把剩下的select 到结尾部分截取出来，获得主查询语句
main_query = query_sql[query_sql.index('SELECT'):].strip(' ').replace(' ', ' ').replace(' ', ' ').replace('###++###', '(+)')
sub_querys.append(main_query)
return (tmp_names, sub_querys)
def get_sub_query(query_sql):
'''传进一条查询语句，返回主查询和所有子查询'''
# 1、先去除(+)的干扰，去除前后空格，双空格替换为单空格
query_sql = query_sql.replace('(+)', '###++###').strip(' ').replace(' ', ' ').replace(' ', ' ')
# 2、匹配()内的子查询
sub_querys = re.findall('\((.*?)\)', query_sql)
sub_query_list = []
if len(sub_querys) != 0:
for i in sub_querys:
if ' FROM ' in i: # 括号内有FROM关键字才是子查询，不然函数可能也用括号包围
query_sql = query_sql.replace(i, '')
i = i.replace('###++###', '(+)')
sub_query_list.append(i)
main_query = query_sql.replace('###++###', '(+)')
sub_query_list.append(main_query)
return sub_query_list
def split_join(ret_list):
join_list = [' LEFT JOIN ', ' RIGHT JOIN ', ' OUTER JOIN ', ' JOIN ']
for i in ret_list:
for j in join_list:
if j in i:
ret_list.remove(i)
ret_list.extend(i.split(j))
return split_join(ret_list)
return ret_list
def get_tables_from_sql(content):
# 开始SQL解析流程
with_query_dict = {'tmp_names': [], 'sub_querys': []}
# 1、含有with关键字,则提取临时表查询语句和主查询语句
querys = []
if content.startswith('WITH'):
# 处理with语句，取出临时表名，临时表查询语句，主查询语句(放在sub_querys的最后一个)
tmp_names, sub_querys = with_query(content)
# 把返回的结果记录在with_query_dict，方便后面取出判断是不是临时表名
with_query_dict['tmp_names'] = tmp_names
with_query_dict['sub_querys'] = sub_querys
# 把前面获取到的临时表查询语句和主查询语句传进去，进一步拆分出其中嵌套的子查询语句, 把拆分出来的sql先暂存到querys里面
for i in sub_querys:
sub_query_list = get_sub_query(i)
for j in sub_query_list:
querys.append(j)
# 2、如果不是with写法，可以直接拿content继续处理，否则从query_dict中获取临时表别名和临时表查询语句，
else:
sub_query_list = get_sub_query(content)
for j in sub_query_list:
querys.append(j)
# 3、到这里就全都是单个查询语句，不嵌套子查询了.
# 3.1、先判断有没有join， left join， right join, outer join这些关键字，有的代表是标准Sql的多表关联
sources_tables = []
for i in querys:
for j in [' LEFT JOIN ', ' RIGHT JOIN ', ' OUTER JOIN ', ' JOIN ']:
if j in i:
# 3.1.1、代表该表是标准sql的多表关联写法, 有可能会关联多次，各种组合都有可能，查到一个切割一次，对结果继续遍历
# ，切割，直到最后没有了
ret = split_join([i])
# 获取到的ret第一个必然是有From的，并且From后是表名
if len(ret) > 0:
for x in range(len(ret)):
# 第一个肯定是带From的，取出from后的部分
if x == 0:
s = ret[x].strip(' ').replace(' ', ' ').replace('()', '').replace('( )', '')
table = s[s.index(' FROM ')+5:].strip(' ')
# 可能带有别名，尝试按空格切，切得到就取前面部分，切不到就是没有别名
try:
tmp_list = table.split(' ')
sources_tables.append(tmp_list[0].strip(' '))
except:
sources_tables.append(table)
# 剩下的都是ON的，取出ON前面部分即可
else:
s = ret[x].strip(' ').replace(' ', ' ').replace('()', '').replace('( )', '')
table = s[:s.index(' ON ')].strip(' ')
# 可能带有别名，尝试按空格切，切得到就取前面部分，切不到就是没有别名
try:
tmp_list = table.split(' ')
sources_tables.append(tmp_list[0].strip(' '))
except:
sources_tables.append(table)
# i 已经提取出源表名，直接从querys中移除
querys.remove(i)
# 注意检测到一个就交给split_join函数做循环切割，这里不再判断
break
# 3.1.2、代表该语句不是join写法，要么是oracle关联写法，要么是单表查询语句
if ' WHERE ' in i:
# 3.1.2、获取FROM到WHERE之间的部分，有逗号代表是oracle多表关联
table_str = i[i.index(' FROM ') + 5: i.index(' WHERE ')+1].strip(' ').replace(' ', ' ').\
replace('()', '').replace('( )', '')
if ',' in table_str:
table_list = table_str.split(',')
for t in table_list:
t = t.strip(' ')
try:
tables = t.split(' ')
# 切割成功代表它是有别名的，取第一个作为真实表名
sources_tables.append(tables[0])
except:
# 切割不成功，代表它没有别名，直接添加
sources_tables.append(t)
else: # 没有多表关联
try:
tables = table_str.split(' ')
# 切割成功代表它是有别名的，取第一个作为真实表名
sources_tables.append(tables[0])
except:
# 切割不成功，代表它没有别名，直接添加
sources_tables.append(table_str)
if ' GROUP BY ' in i and ' WHERE ' not in i:
# 3.1.2、获取FROM到WHERE之间的部分，有逗号代表是oracle多表关联
table_str = i[i.index(' FROM ') + 5: i.index(' GROUP BY ') + 1].strip(' ').replace(' ', ' ').\
replace('()', '').replace('( )', '')
if ',' in table_str:
table_list = table_str.split(',')
for t in table_list:
t = t.strip(' ')
try:
tables = t.split(' ')
# 切割成功代表它是有别名的，取第一个作为真实表名
sources_tables.append(tables[0])
except:
# 切割不成功，代表它没有别名，直接添加
sources_tables.append(t)
else: # 没有多表关联
try:
tables = table_str.split(' ')
# 切割成功代表它是有别名的，取第一个作为真实表名
sources_tables.append(tables[0])
except:
# 切割不成功，代表它没有别名，直接添加
sources_tables.append(table_str)
else: # 代表直接就是一句单查询语句，没有WHERE 和 Group by，直接截取FROM后面部分
table = i[i.index(' FROM ') + 6:].strip(' ')
try:
tables = table.split(' ')
# 切割成功代表它是有别名的，取第一个作为真实表名
sources_tables.append(tables[0])
except:
# 切割不成功，代表它没有别名，直接添加
sources_tables.append(table)
# 对结果加工处理（对表名去重，去除分号，过滤掉表名长度在4 以下的, 可能是误提取的表名， 过滤掉with临时表写法的表名）
sources_tables = [i.replace(';', '') for i in sources_tables]
sources_tables = list(set([i for i in sources_tables if len(i)>4 and i not in with_query_dict['tmp_names']]))
return sources_tables
if __name__ == '__main__':
file_path = r'test_case\join_query.sql'
query_sql = get_query_sql(file_path)
sources_tables = get_tables_from_sql(query_sql)
print(sources_tables)
4、上述脚本如何使用呢？
只要将这里的文件路径替换为你的sql文件路径即可
例如这样的sql文本：
注意，sql文本中只允许有一句select 语句，可以包含子查询，表关联，但只能有一句。
跑出来的结果：
5、测试
该文本目前已通过以下的测试用例（逐个查询语句作为一个sql文件，都能准确提取出来源表名）
一.单表查询语句
--1、单表查询，没有过滤条件，没有分组条件
SELECT
A.HW_CONTRACT_NUM,
A.LAST_UPDATE_DATE,
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A;
SELECT
HW_CONTRACT_NUM,
LAST_UPDATE_DATE,
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP;
--2、单表查询，有过滤条件，没有分组条件
SELECT
A.HW_CONTRACT_NUM,
A.LAST_UPDATE_DATE
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
WHERE A.HW_CONTRACT_NUM = 'AAAAABBBBB'
--3、单表查询，没有过滤条件，有分组条件
SELECT COUNT(1)
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
GROUP BY A.HW_CONTRACT_NUM;
--4、单表查询，带子查询
SELECT SUM(AMOUNT)
FROM (SELECT
A.HW_CONTRACT_NUM,
A.LAST_UPDATE_DATE
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
WHERE A.HW_CONTRACT_NUM = 'AAAAABBBBB') B
GROUP BY B.HW_CONTRACT_NUM;
二.多表关联查询
--1、Oracle特有写法
SELECT *
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A,
DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B,
DWAPP.DWL_EBG_CONTRACT_INFO_TMP3 C
WHERE 1=1
AND A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM(+)
AND A.HW_CONTRACT_NUM = C.HW_CONTRACT_NUM(+);
--2.1、sql标准写法
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM;
--2.2、sql标准写法2
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
INNER JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM;
--2.3、sql标准写法3
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
LEFT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM;
--2.4、sql标准写法4
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM;
三.多表关联查询嵌套子查询(上述所有情况把表名替换成子查询即可)
四.WITH临时表写法
--1.1、WITH单临时表，单表查询，无过滤条件，无分组条件
WITH TMP AS (
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM
)
SELECT HW_CONTRACT_NUM FROM TMP;
--1.2、WITH单临时表，单表查询，有过滤条件，无分组条件
WITH TMP AS (
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM
)
SELECT HW_CONTRACT_NUM FROM TMP WHERE TO_CHAR(LAST_UPDATE_DATE)='2021/06/07';
--1.3、WITH单临时表，单表查询，无过滤条件，有分组条件
WITH TMP AS (
SELECT A.*, B.*
FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B
ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM
)
SELECT SUM(AMOUNT) FROM TMP GROUP BY HW_CONTRACT_NUM;
--2、WITH多临时表， 单表查询...
WITH TMP1 AS (
SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
),
TMP2 AS (
SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP1 B
)
SELECT HW_CONTRACT_NUM FROM TMP1;
--3、WITH多临时表， 多表关联查询, ORACLE写法
WITH TMP1 AS (
SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
),
TMP2 AS (
SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP1 B
)
SELECT C.HW_CONTRACT_NUM, D.CUST_NUM
FROM TMP1 C,
TMP2 D
WHERE C.XX = D.XX(+); --（+）可有可无
--4、WITH多临时表， 多表关联查询, 标准sql写法
WITH TMP1 AS (
SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A
),
TMP2 AS (
SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP1 B
)
SELECT C.HW_CONTRACT_NUM, D.CUST_NUM
FROM TMP1 C
JOIN TMP2 D --JOIN 可能替换为LEFT JOIN , INNER JOIN , RIGHT JOIN
ON C.XX = D.XX(+);
6、缺陷
说一下这个脚本的缺陷：
1、如果表别名的长度在4个字符以上（不含4），将有可能被误识别为真实的来源表名；
2、如果来源表名的长度小于或等于4个字符，会被当做表别名过滤掉；
最后，感谢一下曾文佑同学，百忙之中帮忙测试并发现了其中的bug。
684
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
