-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlparse
131 lines (98 loc) · 3.58 KB
/
sqlparse
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
import re
from collections import OrderedDict
def tables_in_query(sql_str):
# remove the /* */ comments
q = re.sub(r"/\*[^*]*\*+(?:[^*/][^*]*\*+)*/", "", sql_str)
# remove whole line -- and # comments
lines = [line for line in q.splitlines() if not re.match("^\s*(--|#)", line)]
# remove trailing -- and # comments
q = " ".join([re.split("--|#", line)[0] for line in lines])
# split on blanks, parens and semicolons
tokens = re.split(r"[\s)(;]+", q)
# scan the tokens. if we see a FROM or JOIN, we set the get_next
# flag, and grab the next one (unless it's SELECT).
result = set()
get_next = False
for tok in tokens:
if get_next:
if tok.lower() not in ["", "select"]:
result.add(tok)
get_next = False
get_next = tok.lower() in ["from", "join"]
return result
def cols_in_query(sql_str):
# remove the /* */ comments
q = re.sub(r"/\*[^*]*\*+(?:[^*/][^*]*\*+)*/", "", sql_str)
# remove whole line -- and # comments
lines = [line for line in q.splitlines() if not re.match("^\s*(--|#)", line)]
# remove trailing -- and # comments
q = " ".join([re.split("--|#", line)[0] for line in lines])
from_token = re.split("From", q)[0]
tokens = OrderedDict()
tokens = re.split(",", from_token)
print(tokens)
# scan the tokens. if we see a FROM or JOIN, we set the get_next
# flag, and grab the next one (unless it's SELECT).
result = OrderedDict()
get_next = False
asd = []
stackable = 0
for tok in tokens:
if get_next:
if stackable>0:
stackable = upd_stackable(tok, stackable=stackable)
asd.append(tok.strip())
else:
if len(asd) >0:
d= join_stackbaleString(asd) #', '.join(asd)
result[d] = d
asd = []
if "(" in tok:
asd.append(tok)
stackable = upd_stackable(tok, stackable=stackable)
else:
result[tok] = tok.strip()
else:
get_next = "select" in tok.lower()
#result.add(tok.lower().replace("select", ""))
d = tok.lower().replace("select", "").strip()
result[d] = d
if len(asd) >0:
d = join_stackbaleString(asd)
result[d] = d
return result
def upd_stackable( token :str, stackable:int ):
bracket_val = {"(": 1, ")": -1}
if ")" in token:
token = token.replace(")", ") ")
stackable = stackable - token.count(")")
if "(" in token:
token = token.replace("(", "( ")
stackable = stackable + token.count("(")
return stackable
def join_stackbaleString(asd):
return ', '.join(asd).replace("(", "( ").replace(")", " )")
sql = """
Select
trim(ct.TBCREATOR)|| '.'|| ct.tbname as sch_tab_name
, colNO
, fk_tab_name
, replace(sch_tab_name, '.', '_')||':'||COLNO ||'->'||fk_tab_creator||'_'||fk_tab_name||':3;' as gggg
, replace((
(replace(asd), '+', '_'), "", "" )) as testtest2
, Count(*)over(partition by IX.TBCREATOR, IX.tbname, KEY.ixname ) as indx_key_count
From
lib://asd/child_tabs.qvd ct --join sysibm.syscolumns upc on ct.TBNAME = upc.tbname and ct.TBCREATOR = upc.TBCREATOR
where
trim(ct.TBCREATOR)|| '.'|| ct.tbname in ( 'asd.asd', 'asd.ass', 'asd.asd', 'asd.asd', 'asd')
"""
a = tables_in_query(sql)
print(a)
a= cols_in_query(sql)
print("dfdsfsfs")
for i in a:
print(i, )
from sql_metadata import Parser
a = Parser(sql)
print(a.columns)
print(a.columns_aliases)