def create_tables(self): """ @summary - creates tables for the wificounter database. @description - creates tables for the wificounter database, will get commands from the create_tables.txt files that holds the SQL commands to create the desired database. @author - mah60 @param - None @return - None """ # get command cmd = fr.read_txt_file("create_tables.txt", False).split(':') for c in cmd: # exectute all commands to create tables self.db_execute(c, False)
def import_txt_to_db(self, txt_file, index, bytes_send=250000): """ @summary - imports the text file data to the given database. @description - will import the data inside the text file to the database given in initilisation. You may be asked during the function to given start datetime or the filter (mac address and start/end time) if they have been recorded. @author - mah60 @param - txt_file - string - file location/path to the recordings text file. @param - index - int - config list index, for the text file @return - None """ if (self.db.is_tables()): # check if required tables exist #print("-------------------------------") # read data from txt file data = fr.read_txt_file(txt_file, True) seconds = [0.00, 0.00, 0.00] # total secounds , current value, reset value # string for cmds cmd = [ "INSERT INTO devices ( macAddress ) VALUES ", "INSERT INTO counters ( macAddress) VALUES ", "INSERT INTO recordings ( recordingDateTime, rssi, counterId ) VALUES ", "INSERT INTO devicerecords (deviceId, recordId) Values " ] # list for stored values mac_list = [] id_counts = [ self.get_id_count("devices"), self.get_id_count("counters"), self.get_id_count("recordings") ] is_counter_added = False counter_mac = "" datetime_set = False datetime_value = "" # make the start date time value constant # tell if variables have been reset self.var_reset = True for record in data: if (record != ''): r_parts = record.split(',') if (not is_counter_added): cmd[1] += "('" + r_parts[1] + "'), " counter_mac = r_parts[1] id_counts[1] = id_counts[1] + 1 is_counter_added = True if ('' not in r_parts): if (not datetime_set ): # check if start datetime value has been set # done to ensure the device is constant # check if connected to wifi if ('1970' in r_parts[2]): # if not ask for start datetime #print("Device did not connect to Wifi - what was the start date - time?") #output = self.ask_datetime() datetime_value = cf.start_date_time[index] datetime_set = True else: datetime_value = r_parts[2] datetime_set = True cmd[0], id_counts[ 0], mac_list, match = self.is_mac_exists( "devices", cmd[0], id_counts[0], r_parts[0], mac_list) # make datetime variable for recording time # if counter crashes timer will restart, then millis given will reset to seconds[1] = float(r_parts[3]) # set current seconds if (seconds[2] > seconds[1]): seconds[0] += seconds[1] seconds[2] = seconds[1] else: # if not add additional second time to total t = seconds[1] - seconds[2] seconds[0] += t seconds[2] = seconds[1] recordDateTime = datetime.strptime( datetime_value, '%Y-%m-%d-%H-%M-%S') recordDateTime = recordDateTime + timedelta( seconds=seconds[0]) #print(recordDateTime) cmd[2] += "('" + recordDateTime.strftime( '%Y-%m-%d-%H-%M-%S.%f') + "', " cmd[2] += r_parts[4] + ", " + str(id_counts[1]) + "), " id_counts[2] = id_counts[2] + 1 cmd[3] += "( " + str(match[0]) + ", " + str( id_counts[2]) + "), " # send data in insert many once string exceeds x bytes if ( len(cmd[2].encode('utf-8')) > bytes_send ): # max bytes that can be sent at a time is 4194304 self.send_records(cmd) self.var_reset = True # reset variables mac_list = [] # string for cmds cmd = [ "INSERT INTO devices ( macAddress ) VALUES ", "INSERT INTO counters ( macAddress ) VALUES ", "INSERT INTO recordings ( recordingDateTime, rssi, counterId ) VALUES ", "INSERT INTO devicerecords (deviceId, recordId) Values " ] # send final set or records self.send_records(cmd) # ask if there is a filter rssi_limit = self.get_filter(index) print("the rssi limit is : " + str(rssi_limit)) if (rssi_limit != -100): datetime_value = datetime.strptime(datetime_value, '%Y-%m-%d-%H-%M-%S') print("Start time : " + datetime_value.strftime('%Y-%m-%d %H:%M:%S')) self.set_filter(rssi_limit, counter_mac, id_counts[1])