· 5 years ago · May 14, 2020, 12:56 PM
1import mysql.connector
2import json
3
4USERNAME = 'root'
5PASSWORD = 'sushant'
6
7dataBase = None
8
9
10def authenticate(username, password):
11 try:
12 dataBase = mysql.connector.connect(host='localhost', user=username, password=password)
13 return dataBase
14 except:
15 raise Exception('wrong user / password')
16 return None
17
18
19def makeDataBase(databaseName):
20 cursor = dataBase.cursor()
21 cursor.execute('DROP DATABASE %s' %databaseName)
22 cursor.execute('CREATE DATABASE IF NOT EXISTS %s' % databaseName)
23
24
25def makeJobDataTable(databaseName):
26 if dataBase is None:
27 raise Exception('authentication not done')
28 return
29 cursor = dataBase.cursor()
30 cursor.execute('USE %s' % databaseName)
31 cursor.execute('CREATE TABLE IF NOT EXISTS jobdata (jobID INT PRIMARY KEY, jobWeight INT, scriptName VARCHAR(255))')
32 print(cursor)
33
34
35def makeJobExecutionTable(databaseName):
36 if dataBase is None:
37 raise Exception('authentication not done')
38 return
39 cursor = dataBase.cursor()
40 cursor.execute('USE %s' % databaseName)
41 cursor.execute('CREATE TABLE IF NOT EXISTS jobexecution (jobID INT PRIMARY KEY, executationStatus VARCHAR(255))')
42 print(cursor)
43
44
45def makeJobHierarchyTable(databaseName):
46 if dataBase is None:
47 raise Exception('authentication not done')
48 return
49 cursor = dataBase.cursor()
50 cursor.execute('USE %s' % databaseName)
51 cursor.execute(
52 'CREATE TABLE IF NOT EXISTS jobhierarchy (jobID INT, parentJobID INT, PRIMARY KEY( jobID, parentJobID ) )')
53 print(cursor)
54
55
56def insertJobDataTable(databaseName):
57 if dataBase is None:
58 raise Exception('authentication not done')
59 return
60 cursor = dataBase.cursor()
61 cursor.execute('USE %s' % databaseName)
62 jobId = input('Enter JobID: ')
63 weight = input('Enter Job weight: ')
64 script = input('Enter script name: ')
65 cursor.execute('INSERT INTO jobdata(jobID , jobWeight , scriptName) VALUES (%s,%s,%s)', (jobId, weight, script))
66
67
68def printJobDataTable(databaseName):
69 if dataBase is None:
70 raise Exception('authentication not done')
71 return
72 cursor = dataBase.cursor()
73 cursor.execute('USE %s' % databaseName)
74 cursor.execute('SELECT * FROM jobdata')
75 table = cursor.fetchall()
76 for data in table:
77 print(data)
78
79
80def insertJobExecutionTable(databaseName):
81 if dataBase is None:
82 raise Exception('authentication not done')
83 return
84 cursor = dataBase.cursor()
85 cursor.execute('USE %s' % databaseName)
86 jobId = input('Enter JobID: ')
87 status = input('Enter execution status: ')
88 cursor.execute('INSERT INTO jobexecution(jobID , executationStatus) VALUES (%s,%s)', (jobId, status))
89
90
91def printJobExecutionTable(databaseName):
92 if dataBase is None:
93 raise Exception('authentication not done')
94 return
95 cursor = dataBase.cursor()
96 cursor.execute('USE %s' % databaseName)
97 cursor.execute('SELECT * FROM jobexecution')
98 table = cursor.fetchall()
99 for data in table:
100 print(data)
101
102
103def insertJobHierarchyTable(databaseName):
104 if dataBase is None:
105 raise Exception('authentication not done')
106 return
107 cursor = dataBase.cursor()
108 cursor.execute('USE %s' % databaseName)
109 jobId = input('Enter JobID: ')
110 parentId = input('Enter parentId: ')
111 cursor.execute('INSERT INTO jobhierarchy(jobID , parentJobID) VALUES (%s,%s)', (jobId, parentId))
112
113
114def printJobHierarchyTable(databaseName):
115 if dataBase is None:
116 raise Exception('authentication not done')
117 return
118 cursor = dataBase.cursor()
119 cursor.execute('USE %s' % databaseName)
120 cursor.execute('SELECT * FROM jobhierarchy')
121 table = cursor.fetchall()
122 for data in table:
123 print(data)
124
125
126def getJobDataJSON(databaseName):
127 if dataBase is None:
128 raise Exception('authentication not done')
129 return
130 cursor = dataBase.cursor()
131 cursor.execute('USE %s' % databaseName)
132 cursor.execute('SELECT * FROM jobdata')
133 table = cursor.fetchall()
134 row = {}
135 with open('./jobdata.json', 'w+') as fp:
136 for data in table:
137 row[data[0]] = {'jobWeight': data[1], 'scriptName': data[2]}
138 s = json.dumps(row)
139 print(s)
140 fp.write(s)
141
142
143def getJobExecutionJSON(databaseName):
144 if dataBase is None:
145 raise Exception('authentication not done')
146 return
147 cursor = dataBase.cursor()
148 cursor.execute('USE %s' % databaseName)
149 cursor.execute('SELECT * FROM jobexecution')
150 table = cursor.fetchall()
151 row = {}
152 with open('./jobexecution.json', 'w+') as fp:
153 for data in table:
154 row[data[0]] = {'executionStatus': data[1]}
155 s = json.dumps(row)
156 print(s)
157 fp.write(s)
158
159
160def getJobHierarchyJSON(databaseName):
161 if dataBase is None:
162 raise Exception('authentication not done')
163 return
164 cursor = dataBase.cursor()
165 cursor.execute('USE %s' % databaseName)
166 cursor.execute('SELECT * FROM jobhierarchy')
167 table = cursor.fetchall()
168 row = {}
169 with open('./jobhierarchy.json', 'w+') as fp:
170 for data in table:
171 if data[0] in row:
172 row[data[0]].append(data[1])
173 else:
174 row[data[0]] = [data[1]]
175 # row[data[0]] = {'parentJobID': data[1]}
176 s = json.dumps(row)
177 print(s)
178 fp.write(s)
179
180
181dataBase = authenticate(USERNAME, PASSWORD)
182
183makeDataBase('paytm')
184makeJobExecutionTable('paytm')
185makeJobHierarchyTable('paytm')
186makeJobDataTable('paytm')
187
188insertJobDataTable('paytm')
189insertJobExecutionTable('paytm')
190insertJobHierarchyTable('paytm')
191insertJobHierarchyTable('paytm')
192insertJobHierarchyTable('paytm')
193
194printJobDataTable('paytm')
195printJobExecutionTable('paytm')
196printJobHierarchyTable('paytm')
197
198getJobDataJSON('paytm')
199getJobExecutionJSON('paytm')
200getJobHierarchyJSON('paytm')
201
202dataBase.commit()