· 6 years ago · Dec 08, 2019, 03:48 AM
1This is MiscFunctions.py:
2
3path = r"/home/activ/utils/DataComparisonScript"
4
5
6if not os.path.exists("log"):
7 os.makedirs("log")
8 print("done!")
9
10def sqliteConnection(path):
11 conn = sqlite3.connect(path+"/TempLocalDatabase/DB_Comparisons.db")
12 c = conn.cursor()
13 c2 = conn.cursor()
14 return c, c2, conn
15
16def GetDate():
17 """return the date in YYY-MM-DD"""
18 return datetime.datetime.today().strftime('%Y-%m-%d')
19
20def GetTableName(host,table):
21 table_name = "{0}_Table{1}_{2}".format(host, table, GetDate())
22 return table_name
23
24
25def CleanupLogs(days): # runs at startup
26 """Cleans up logs that are a specified days old or older"""
27 folder_path = path+"\log"
28 print(folder_path)
29 file_ends_with = ".log"
30 how_many_days_old_logs_to_remove = days
31 now = time.time()
32 only_files = []
33 for file in os.listdir(folder_path):
34 file_full_path = os.path.join(folder_path, file)
35 if os.path.isfile(file_full_path) and file.endswith(file_ends_with:
36 # Delete files older than x days
37 if os.stat(file_full_path).st_mtime < now - how_many_days_old_logs_to_remove * 86400:
38 os.remove(file_full_path)
39
40
41def CleanupDatabase(): # runs at startup
42 """Deletes the LOCAL sqlite3 database on startup removing old data as these are stored temporarily due to disk space"""
43 try:
44 os.remove(path+"/TempLocalDatabase/DB_Comparisons.db")
45 except OSError:
46 pass
47
48
49
50
51
52
53this is
54
55import sqlite3
56from ComparisonConfig import username, password, path, InsertChunk
57from MiscFunctions import GetDate,CleanupDatabase
58import shlex
59import socket
60import os
61import subprocess
62from MiscFunctions import logger, sqliteConnection
63from datetime import datetime
64hostname = socket.gethostname()
65
66#CleanupDatabase()
67
68if not os.path.exists(path+"/TempLocalDatabase/"):
69 os.makedirs(path+"/TempLocalDatabase")
70
71c,c2,conn = sqliteConnection(path)
72
73# This file contains the function that runs AdminDataCapture and captures it's output into a sqlite3 database, one table per output. This is a local database stored in CWD/TempLocalDatabase/
74# See readme.txt for more information on the purpose of some functions if needed
75
76
77
78
79
80def TupleForInserts(values):
81 vtuple = tuple(['?' for i in values]) # values is line.split("|")[7::2] for these files e.g all the pipe separated values
82 vtuple = str(vtuple)
83 vtuple = vtuple.replace("'", "")
84 vtuple = vtuple.replace(" ?)", " ?, ?, ?, ?)")# add an extra ? for inserting Symbol later on in query
85 return vtuple
86
87
88
89
90def InitialiseTable(table_name,data):
91 global vtuple # make vutple variable global as it is needed outside this function later on for usage in inserts
92 DropTable = "DROP TABLE IF EXISTS '{}'".format(table_name)
93 c.execute(DropTable)
94 CreateTable = "CREATE TABLE IF NOT EXISTS '{}' (Symbol VARCHAR(255), ExchangeCode VARCHAR(255), PermID VARCHAR(255), PRIMARY KEY(Symbol))".format(table_name) # initialise table with the columns that are ALWAYS present
95 c.execute(CreateTable)
96
97 columns = data[6::2]
98 values = data[7::2]
99 vtuple = TupleForInserts(values) # Generate (?,?,...,?) tuple for inserts
100 for column in columns:
101 Query = "ALTER TABLE '{0}' ADD '{1}' VARCHAR(255)".format(table_name,column)
102 c.execute(Query)
103 conn.commit()
104
105
106def CaptureIntoSQL(host,table, InitialConfigDone=False):
107 """Starts AdminDataCapture and redirects output to a table in a local SQL database"""
108 DataListToInsert = []
109
110 logger.info(host+" capture starting")
111 command =path+"/AdminDataCapture_x86-64_rhel6_gcc48_cxx11-vstring_mds -u {0} -p {1} -h {2} -t {3} --static -c".format(username,password,host,table)
112 print(command)
113
114 table_name ="{0}_Table{1}_{2}".format(host,table,GetDate())
115 process = subprocess.Popen(shlex.split(command), stdout=subprocess.PIPE)
116 logger.info("process started for {}".format(table_name))
117 #logger.error("process did not start for {}".format(table_name))
118 capture_begin = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
119 logger.info("Capture begins {}".format(capture_begin))
120 while True:
121 cmdoutput = process.stdout.readline().decode("utf-8")
122 if cmdoutput == '' and process.poll() is not None:
123 InsertQuery = "INSERT INTO '{0}' VALUES{1}".format(table_name, vtuple)
124 c.executemany(InsertQuery,DataListToInsert)
125 conn.commit()
126 break
127 if cmdoutput:
128 data = cmdoutput.split("|")
129 try:
130 Symbol = data[5]
131 if Symbol != "<Symbol>" and InitialConfigDone == False: # Run InitialiseTable() function to create the sqlite3 table, if it hasn't been done
132 try:
133 InitialiseTable(table_name,data)
134 InitialConfigDone = True
135 logger.info("Table created for {0}".format(table_name))
136 except:
137 logger.info("InitialiseTable(table_name,data) failed:\n table_name: {0} \n data: {1}".format(table_name,data))
138
139
140 elif Symbol != "<Symbol>" and InitialConfigDone == True: # If we've already ran InitialiseTable() begin inserting data.
141 Permission = data[3]
142 exchangeCode = data[5].split(".")[-1]
143 if exchangeCode == "":
144 exchangeCode = "."
145 else:
146 pass
147 valuelist = data[7::2]
148 valuelist[0:0] = [Symbol,exchangeCode,Permission]
149 DataListToInsert.append(valuelist)
150 if len(DataListToInsert) == InsertChunk:
151 InsertQuery = "INSERT INTO '{0}' VALUES{1}".format(table_name, vtuple)
152 try:
153 c.executemany(InsertQuery,DataListToInsert)
154 conn.commit()
155
156 except:
157 logger.info("FAILED to insert")
158 logger.error("Failed to insert data \n Values: {0} \n table_name : {1} \n InsertQuery : {2}".format(valuelist,table,InsertQuery))
159 DataListToInsert = []
160 else:
161 pass
162
163 except:
164 pass
165 capture_end_time =datetime.now().strftime('%Y-%m-%d %H:%M:%S')
166 logger.info("capture end time is {}".format(capture_end_time))
167 conn.commit()