项目上遇到一个需求,在很多sql种提取出所有表名。代码如下:
# 这个脚本有一个前提,就是from接表名之后必定有一个where。通常情况下也都是如此
strSql = "select * from mm a, bb b where a > 0; select * from cc a, dd, qq, aa b where a > 0;"
def check_ch(ch):
if ch >= '0' and ch <= '9':
return False
if ch >= 'A' and ch <= 'Z':
return False
if ch >= 'a' and ch <= 'z':
return False
return True
def get_string(strTmp):
strRes = ""
for i in range(len(strTmp)):
if not check_ch(strTmp[i]):
strRes += strTmp[i]
continue
if len(strRes) > 0:
break
return strRes
name_list = []
def find_table_name(strSql):
strSql = strSql.upper()
strFrom = "FROM"
while True:
pos = strSql.find(strFrom)
if pos < 0:
break
pos_front = pos - 1
pos_tail = pos + len(strFrom)
if pos_tail < len(strSql) and not check_ch(strSql[pos_tail]):
strSql = strSql[pos_tail:]
continue
if pos_front >= 0 and not check_ch(strSql[pos_front]):
strSql = strSql[pos_tail:]
continue
strSql = strSql[pos_tail:]
strTable = strSql[:strSql.find("WHERE")]
while True:
name = get_string(strTable)
if len(name) == 0:
break
if name not in name_list:
name_list.append(name)
if strTable.find(",") > 0:
strTable = strTable[strTable.find(",") + 1:]
else:
break
find_table_name(strSql)
def compaire_list(list1, list2):
if len(list1) != len(list2):
return False
for v in list2:
if v not in list1:
return False
for v in list1:
if v not in list2:
return False
return True
def print_talbe_name(name_list):
tmp = ""
name_list.sort()
for v in name_list:
tmp += v + ";"
print(tmp)
print(name_list)
print_talbe_name(name_list)
print(compaire_list(name_list, name_list))