· 6 years ago · Jun 10, 2019, 09:18 PM
1import pyodbc, sys, sqlanydb, time
2import psycopg2 as pg2
3import Connect as ct
4from DatabaseConnections import DatabaseConnect as dbct
5
6#Storing Variables
7GoogleList = [] #Empty list to store Google IDs
8GoogleDict = {}
9FacebookList = [] #Empty list to store Facebook IDs
10FacebookDict = {}
11ClinicList = [] #Empty list to store Clinic IDs
12ClinicDict = {} #Empty dictionary to store Clinic ID and host
13FacebookResults = [] #Empty list to store results
14
15#Argument Variables
16clincidictquery = 'SELECT "ClinicID","eaglesofthost" FROM "Clinic"."Clinic" WHERE "eaglesofthost" IS NOT NULL'
17fblistquery = 'SELECT "facebookid" FROM "Clinic"."Clinic" WHERE "facebookid" IS NOT NULL'
18gmblistquery = 'SELECT "ClinicID","googleid" FROM "Clinic"."Clinic" WHERE "googleid" IS NOT NULL'
19CheckTemp = """SELECT COUNT(*) FROM pg_tables WHERE ('"' || schemaname::text || '"."' || tablename::text || '"') = '{0}'"""
20CreateTemp = 'CREATE TABLE {0} AS (SELECT * FROM {1} WHERE 1 = 2)'
21DropTemp = 'DROP TABLE {0}'
22InsertTemp = 'INSERT INTO {0} VALUES {1}'
23CountTemp = 'SELECT COUNT(*) FROM {0}'
24
25#Populate variables for External and Internal use
26#Populate ClinicDict{}
27clinicdictresults = dbct.PGSelect(clincidictquery) #Pass query for ClinicDict
28for key, value in clinicdictresults:
29 ClinicDict[key] = value
30
31# facebookresults = dbct.PGSelect(fblistquery) #Pass query for ClinicDict
32# for key, value in facebookresults:
33# FacebookDict[key] = value
34facebooklistres = dbct.PGSelect(fblistquery)
35for value in facebooklistres:
36 FacebookList.extend(value)
37
38#Independent Functions
39#Grab data from Eaglesoft Server
40def ESGrab(host,query=None):
41 query = query or noquery #Use class query if no alternative
42 try:
43 esconn = sqlanydb.connect( #Establish Connection
44 UID=ct.SAUID,
45 PWD=ct.SAPWD,
46 HOST=host,
47 DBN=ct.SADatabase,
48 ENG=ct.SAServer)
49 escurs = esconn.cursor() #Create Cursor
50 try:
51 escurs.execute(query) #Execute query argument
52 esresults = escurs.fetchall() #Fetch results of query
53 escurs.close() #Close Cursor
54 esconn.close() #Close Connection
55 return esresults #Return stored results
56 except(sqlanydb.Error) as se:
57 print(se)
58 except(sqlanydb.Error) as ce:
59 print(ce)
60 print("Could not connect to HOST: %s" % host)
61 #If connect fails, return host IP
62
63#Execute query on PG Database
64def PGExecute(query):
65 pgconn = pg2.connect( #Establish Connection to DB
66 "dbname=%s user=%s host=%s password=%s" %
67 (ct.PGDatabase,ct.PGUID,ct.PGHost,ct.PGPWD))
68 pgcurs = pgconn.cursor() #Create Cursor
69 try: #Try INSERT Query
70 pgcurs.execute(query) #Execute query argument
71 pgconn.commit() #Commit INSERT
72 return True
73 except (pg2.Error,pg2.OperationalError,pg2.DataError,pg2.DatabaseError,pg2.ProgrammingError,pg2.IntegrityError,pg2.InterfaceError,pg2.NotSupportedError,pg2.InternalError) as e:
74 print (e.pgerror)
75 pgcurs.close() #Close Cursor
76 pgconn.close() #Close Connection
77
78#View data from PG Server
79def PGSelect(query=None):
80 query = query or noquery
81 pgconn = pg2.connect( #Establish Connection to DB
82 "dbname=%s user=%s host=%s password=%s" %
83 (ct.PGDatabase,ct.PGUID,ct.PGHost,ct.PGPWD))
84 pgcurs = pgconn.cursor() #Create Cursor
85 pgcurs.execute(query) #Execute query argument
86 pgresults = pgcurs.fetchall() #Fetch query results
87 pgcurs.close() #Close Cursor
88 pgconn.close() #Close Connection
89 return pgresults #Return stored results
90
91#Insert data into PG Server
92def PGInsert(query):
93 query = query.encode('utf-8') or noquery #Use class query if no alternative
94 pgconn = pg2.connect( #Establish Connection to DB
95 "dbname=%s user=%s host=%s password=%s" %
96 (ct.PGDatabase,ct.PGUID,ct.PGHost,ct.PGPWD))
97 pgcurs = pgconn.cursor() #Create Cursor
98 try: #Try INSERT Query
99 pgcurs.execute(query) #Execute query argument
100 pgconn.commit() #Commit INSERT
101 return True
102 except (pg2.Error,pg2.OperationalError,pg2.DataError,pg2.DatabaseError,pg2.ProgrammingError,pg2.IntegrityError,pg2.InterfaceError,pg2.NotSupportedError,pg2.InternalError) as e:
103 print (e.pgerror)
104 pgcurs.close() #Close Cursor
105 pgconn.close() #Close Connection
106
107#Insert many values itno PG (Not efficient)
108def PGInsertMany(query,list):
109 query = query or noquery #Use class query if no alternative
110 pgconn = pg2.connect( #Establish Connection to DB
111 "dbname=%s user=%s host=%s password=%s" %
112 (ct.PGDatabase,ct.PGUID,ct.PGHost,ct.PGPWD))
113 pgcurs = pgconn.cursor() #Create Cursor
114 try: #Try INSERT Query
115 pgcurs.executemany(query,) #Execute query argument
116 pgconn.commit() #Commit INSERT
117 #return print('INSERT Commited to Database')
118 except:
119 return print('Unable to execute INSERT query: %s' % query)
120 pgcurs.close() #Close Cursor
121 pgconn.close() #Close Connection
122
123#View data from SQL Server
124def MSSelect(query=None):
125 query = query or noquery #Use class query if no alternative
126 msconn = pyodbc.connect( #Establish Connection with MS Database
127 "Driver={%s};Server=%s;Database=%s;UID=%s;PWD=%s;" %
128 (ct.MSDriver,ct.MSServer,ct.MSDatabase,ct.MSUID,ct.MSPWD))
129 mscurs = msconn.cursor() #Create Cursor
130 mscurse.execute(query) #Execute Query
131 msresults = mscurs.fetchall() #Fetch query results
132 mscurs.close() #Close Cursor
133 msconn.close() #Close Connection
134
135#Insert Data into SQL Server
136def MSInsert(query=None):
137 query = query or noquery #Use class query if no alternative
138 msconn = pyodbc.connect( #Establish Connection with MS Database
139 "Driver={%s};Server=%s;Database=%s;UID=%s;PWD=%s;" %
140 (ct.MSDriver,ct.MSServer,ct.MSDatabase,ct.MSUID,ct.MSPWD))
141 mscurs = msconn.cursor() #Create Cursor
142 try:
143 mscurse.execute(query) #Execute INSERT Query
144 except:
145 print('Unable to execute INSERT query')
146 mscurs.close() #Close Cursor
147 msconn.close() #Close Connection
148
149def ListShaping(list,*args): #Add rows to list
150 list.extend(args)
151 return list
152
153def TupleList(list): #Change list into String
154 insertTuple = ''
155 for item in list:
156 insertTuple += str(tuple(item)) + ','
157 insertTuple = insertTuple[:-1] # Remove ending comma
158 insertTuple = insertTuple.replace("None", "NULL") # Replace Python 'None' with SQL NULL values in string
159 return insertTuple
160
161#Dynamic Insertion for PG for ES updates
162def BuildInsertPG(GrabQuery,InsertQuery,TestRange=None):
163 rangeLen = (TestRange or len(ClinicDict)+1)
164 starttime = time.time()
165 HostFail = []
166 ResultsList = []
167 for i in range(1,rangeLen): #Loop for n iterations of length of list
168 hostip = ClinicDict[i] #Set variable to key value
169 QueryResults = ESGrab(hostip,GrabQuery) #Set query results to variable
170 if not QueryResults: #If results are empty
171 HostFail.append(hostip)
172 pass
173 else:
174 for idx, row in enumerate(QueryResults): #For each row of data
175 ResultsList.append([i]) #Add list for each row with current clinic ID
176 for value in row: #For each individual value in the row
177 ResultsList[len(ResultsList)-1].append(value) #Add value to last list added
178 insertString = ''
179 for item in ResultsList:
180 insertString+=str(tuple(item))+',' #Add string tuple version of list to insert string
181 insertString = insertString[:-1] #Remove ending comma
182 insertString = insertString.replace("None","NULL") #Replace Python 'None' with SQL NULL values in string
183 injectionString = InsertQuery.format(insertString)
184 try:
185 ins = PGInsert(injectionString)
186 if ins == True:
187 print(HostFail or "Insert Success, No Failed Connections")
188 print('%s execution time' % (time.time()-starttime))
189 else:
190 raise
191 except:
192 return print("Failed to INSERT")
193
194#Dynamic Insertion for PG for ES updates, Staggered # of Clinics
195def BuildInsertPGStagger(GrabQuery,InsertQuery,StartRange=None,EndRange=None):
196 start = (StartRange or 1) #Starting Clinic
197 end = (EndRange+1 or len(ClinicDict)+1) #Ending Clinic
198 starttime = time.time()
199 HostFail = []
200 ResultsList = []
201 for i in range(start,end): #Loop for n iterations of length of list
202 hostip = ClinicDict[i] #Set variable to key value
203 QueryResults = ESGrab(hostip,GrabQuery) #Set query results to variable
204 if not QueryResults: #If results are empty
205 HostFail.append(hostip)
206 pass
207 else:
208 for idx, row in enumerate(QueryResults): #For each row of data
209 ResultsList.append([i]) #Add list for each row with current clinic ID
210 for value in row: #For each individual value in the row
211 ResultsList[len(ResultsList)-1].append(value) #Add value to last list added
212 insertString = ''
213 for item in ResultsList:
214 insertString+=str(tuple(item))+',' #Add string tuple version of list to insert string
215 insertString = insertString[:-1] #Remove ending comma
216 insertString = insertString.replace("None","NULL") #Replace Python 'None' with SQL NULL values in string
217 injectionString = InsertQuery.format(insertString)
218 try:
219 ins = PGInsert(injectionString)
220 if ins == True:
221 print(HostFail or "Insert Success, No Failed Connections")
222 print('%s execution time' % (time.time()-starttime))
223 else:
224 raise
225 except:
226 return print("Failed to INSERT")
227
228#Building and Inserting data via temporary tables
229def BuildInsertPGTemp(GrabQuery,TempTable,CentralTable,TempCompare,StartRange=None,EndRange=None):
230 start = (StartRange or 1) #Starting Clinic
231 end = (EndRange or len(ClinicDict))+1 #Ending Clinic
232 tempCheck = CheckTemp.format(TempTable)
233 tempDrop = DropTemp.format(TempTable) #Check if temporary Table exists
234 tempCreate = CreateTemp.format(TempTable,CentralTable)
235 tempCounter = CountTemp.format(TempTable)
236 checker = PGSelect(tempCheck)
237 if checker[0][0] == 1:
238 PGExecute(tempDrop)
239 starttime = time.time()
240 HostFail = []
241 ResultsList = []
242 for i in range(start,end): #Loop for n iterations of length of list
243 hostip = ClinicDict[i] #Set variable to key value
244 QueryResults = ESGrab(hostip,GrabQuery) #Set query results to variable
245 if not QueryResults: #If results are empty
246 HostFail.append(hostip)
247 pass
248 else:
249 for idx, row in enumerate(QueryResults): #For each row of data
250 ResultsList.append([i]) #Add list for each row with current clinic ID
251 for value in row: #For each individual value in the row
252 ResultsList[len(ResultsList)-1].append(value) #Add value to last list added
253 insertString = ''
254 for item in ResultsList:
255 insertString+=str(tuple(item))+',' #Add string tuple version of list to insert string
256 insertString = insertString[:-1] #Remove ending comma
257 insertString = insertString.replace("None","NULL") #Replace Python 'None' with SQL NULL values in string
258 injectionString = InsertTemp.format(TempTable,insertString) #Group INSERT Query + values
259 PGExecute(tempCreate) #Create Temporary Table using existing table
260 PGInsert(injectionString) #Insert ES data into Temporary Table
261 try:
262 ins = PGInsert(TempCompare)
263 if ins == True:
264 print("%s: Insert Success" % CentralTable)
265 print('%s execution time' % (time.time()-starttime))
266 else:
267 raise
268 except:
269 return print("Failed to INSERT")
270 #print(PGSelect(tempCounter))
271 PGExecute(tempDrop)
272
273#Test for failing Inserts
274def BuildInsertTest(GrabQuery,InsertQuery,StartRange=None,EndRange=None):
275 start = (StartRange or 1)
276 end = (EndRange or len(ClinicDict)+1)
277 starttime = time.time()
278 HostFail = []
279 ResultsList = []
280 for i in range(start,end): #Loop for n iterations of length of list
281 hostip = ClinicDict[i] #Set variable to key value
282 QueryResults = ESGrab(hostip,GrabQuery) #Set query results to variable
283 if not QueryResults: #If results are empty
284 HostFail.append(hostip)
285 pass
286 else:
287 for idx, row in enumerate(QueryResults): #For each row of data
288 ResultsList.append([i]) #Add list for each row with current clinic ID
289 for value in row: #For each individual value in the row
290 ResultsList[len(ResultsList)-1].append(value) #Add value to last list added
291 insertString = ''
292 for item in ResultsList:
293 insertString+=str(tuple(item))+',' #Add string tuple version of list to insert string
294 insertString = insertString[:-1] #Remove ending comma
295 insertString = insertString.replace("None","NULL") #Replace Python 'None' with SQL NULL values in string
296 injectionString = InsertQuery.format(insertString)
297 try:
298 ins = PGInsert(injectionString)
299 if ins == True:
300 print(HostFail or "Insert Success, No Failed Connections")
301 print('%s execution time' % (time.time()-starttime))
302 else:
303 raise
304 except:
305 return print("Testing Failed")
306 #print("Failed to INSERT")
307 #print(injectionString)
308
309def TempTableTest(GrabQuery,TempTable,CentralTable,StartRange=None,EndRange=None):
310 start = (StartRange or 1) #Starting Clinic
311 end = (EndRange or len(ClinicDict))+1 #Ending Clinic
312 tempCheck = CheckTemp.format(TempTable) #Check if temporary Table exists
313 tempDrop = DropTemp.format(TempTable) #Drop temp table
314 tempCreate = CreateTemp.format(TempTable,CentralTable) #Create temp table
315 tempCounter = CountTemp.format(TempTable) #Count # of rows on temp table
316 checker = PGSelect(tempCheck)
317 #print(checker)
318 if checker[0][0] == 1:
319 PGExecute(tempDrop)
320 starttime = time.time()
321 HostFail = []
322 ResultsList = []
323 for i in range(start,end): #Loop for n iterations of length of list
324 hostip = ClinicDict[i] #Set variable to key value
325 QueryResults = ESGrab(hostip,GrabQuery) #Set query results to variable
326 if not QueryResults: #If results are empty
327 HostFail.append(hostip)
328 pass
329 else:
330 for idx, row in enumerate(QueryResults): #For each row of data
331 ResultsList.append([i]) #Add list for each row with current clinic ID
332 for value in row: #For each individual value in the row
333 ResultsList[len(ResultsList)-1].append(value) #Add value to last list added
334 insertString = ''
335 for item in ResultsList:
336 insertString+=str(tuple(item))+',' #Add string tuple version of list to insert string
337 insertString = insertString[:-1] #Remove ending comma
338 insertString = insertString.replace("None","NULL") #Replace Python 'None' with SQL NULL values in string
339 injectionString = InsertTemp.format(TempTable,insertString) #Group INSERT Query + values
340 PGExecute(tempCreate) #Create Temporary Table using existing table
341 PGInsert(injectionString) #Insert ES data into Temporary Table
342 print('%s execution time' % (time.time()-starttime))
343
344#Create Tables from Report Views for POSTGRESQL Data Studio use
345def ReportCreate(ReportTable, ReportView):
346 CheckExist = CheckTemp.format(ReportTable) #Check if Report Table exists
347 DropReportTable = DropTemp.format(ReportTable) #Drop Table
348 CreateReportTable = 'CREATE TABLE {0} AS (SELECT * FROM {1})' #Create table from VIEW
349 checker = PGSelect(CheckExist)
350 if checker[0][0] == 1:
351 PGExecute(DropReportTable)
352 PGExecute(CreateReportTable.format(ReportTable,ReportView))
353
354#Internal Functions
355#Check to see if all ES Clinics can be connected
356def ESCheck():
357 StatusCheck = []
358 for i in range(1,43): #len(ClinicDict)+1
359 try:
360 esconn = sqlanydb.connect( #Establish Connection
361 UID=ct.SAUID,
362 PWD=ct.SAPWD,
363 HOST=ClinicDict[i],
364 DBN=ct.SADatabase,
365 ENG=ct.SAServer)
366 esconn.close() #Close Connection
367 StatusCheck.append("Clinic %s: Connection Success" % i)
368 except:
369 StatusCheck.append("Could not connect to Clinic %s at HOST: %s" % (i,ClinicDict[i]))
370 return StatusCheck