forked from wirelessModem/NgToolset
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathngsqlquery.py
174 lines (149 loc) · 7.28 KB
/
ngsqlquery.py
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
#!/usr/bin/python3
# -*- encoding: utf-8 -*-
'''
File:
ngsqlquery.py
Description:
SQL query utilty using cx_Oracle library .
Change History:
2018-3-22 v0.1 created. github/zhenggao2
'''
from PyQt5.QtWidgets import QDialog
from PyQt5.QtWidgets import qApp
import cx_Oracle
import os
import re
from ngsqlsubui import NgSqlSubUi
class NgSqlQuery(object):
def __init__(self, ngwin, args):
self.ngwin = ngwin
self.args = args
self.subsMap = dict()
self.dbStat = False
self.queryStat = False
self.initDb()
def initDb(self):
confDir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'config')
try:
with open(os.path.join(confDir, self.args['dbConf']), 'r') as f:
self.ngwin.logEdit.append('<font color=blue>Parsing DB configuration: %s</font>' % f.name)
qApp.processEvents()
while True:
line = f.readline()
if not line:
break
if line.startswith('#') or line.strip() == '':
continue
tokens = line.split('=')
tokens = list(map(lambda x:x.strip(), tokens))
if len(tokens) == 2:
if tokens[0].upper() == 'HOST_NAME':
self.dbHost = tokens[1]
elif tokens[0].upper() == 'TCP_PORT':
self.dbPort = tokens[1]
elif tokens[0].upper() == 'DB_NAME':
self.dbService = tokens[1]
elif tokens[0].upper() == 'USER_NAME':
self.dbUserName = tokens[1]
elif tokens[0].upper() == 'USER_PASSCODE':
self.dbUserPwd = tokens[1]
self.dbStat = True
except Exception as e:
self.ngwin.logEdit.append('<font color=red>Exception: %s</font>' % str(e))
def exec_(self):
if not self.dbStat:
return
dsn = cx_Oracle.makedsn(self.dbHost, self.dbPort, service_name=self.dbService)
self.ngwin.logEdit.append('<font color=blue>Connecting to Oracle DB</font>')
self.ngwin.logEdit.append('-->DSN = %s' % dsn)
qApp.processEvents()
try:
db = cx_Oracle.connect(self.dbUserName, self.dbUserPwd, dsn)
except cx_Oracle.DatabaseError as e:
# cx_Oracle 5.0.4 raises a cx_Oracle.DatabaseError exception
# with the following attributes and values:
# code = 2091
# message = 'ORA-02091: transaction rolled back
# 'ORA-02291: integrity constraint (TEST_DJANGOTEST.SYS
# _C00102056) violated - parent key not found'
self.ngwin.logEdit.append('<font color=red>cx_Oracle.DatabaseError: %s!</font>' % e.args[0].message)
return
cursor = db.cursor()
sqlDir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'sql')
for sqlFn in self.args['sqlQuery']:
with open(os.path.join(sqlDir, sqlFn), 'r') as f:
self.ngwin.logEdit.append('<font color=blue>Executing query: %s</font>' % f.name)
qApp.processEvents()
self.names = []
self.answers = []
reSql = re.compile(r"^[a-zA-Z0-9\_\s\>\<\=\(]+\&([a-zA-Z\_]+)[\,\s\'a-zA-Z0-9\)]+$")
while True:
line = f.readline()
if not line:
break
#substitute names if necessary
m = reSql.match(line)
if m is not None:
self.names.extend(m.groups())
f.seek(0)
query = f.read()
if len(self.names) > 0:
#skip show NgSqlSubUi if self.names already exist in self.subsMap
if self.checkSubMap():
for name in self.names:
self.answers.append(self.subsMap[name])
else:
dlg = NgSqlSubUi(self.ngwin, self.names)
if dlg.exec_() == QDialog.Accepted:
self.answers = dlg.answers
valid = True
for an in self.answers:
if len(an) == 0:
valid = False
break
if not valid:
self.ngwin.logEdit.append('<font color=red>-->Query skipped!</font>')
qApp.processEvents()
continue
#save for later use if possible
if dlg.applyToAllChkBox.isChecked():
for name,answer in zip(self.names, self.answers):
self.subsMap[name] = answer
else:
self.ngwin.logEdit.append('<font color=red>-->Query skipped!</font>')
qApp.processEvents()
continue
for name, answer in zip(self.names, self.answers):
self.ngwin.logEdit.append('-->Subsitution: [%s=%s]' % (name, answer))
qApp.processEvents()
for index,name in enumerate(self.names):
query = query.replace('&'+name, "'"+self.answers[index]+"'")
try:
cursor.execute(query)
except cx_Oracle.DatabaseError as e:
self.ngwin.logEdit.append('<font color=red>cx_Oracle.DatabaseError: %s!</font>' % e.args[0].message)
return
fields = ','.join([a[0] for a in cursor.description])
#self.ngwin.logEdit.append('Fields: %s' % fields)
#record = cursor.fetchone()
#self.ngwin.logEdit.append(','.join([str(i) for i in record]))
records = cursor.fetchall()
outDir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'output')
outFn = sqlFn.replace('.sql', '.csv')
with open(os.path.join(outDir, outFn), 'w') as of:
self.ngwin.logEdit.append('-->Exporting query results to: %s' % of.name)
qApp.processEvents()
of.write(fields)
of.write('\n')
for r in records:
of.write(','.join([str(token) for token in r]))
of.write('\n')
self.queryStat = True
self.ngwin.logEdit.append('<font color=blue>Done!</font>')
def checkSubMap(self):
ret = True
for name in self.names:
if not name in self.subsMap:
ret = False
break
return ret