def getAllRecords(numHours, location): currentTime = datetime.datetime.now() searchTime = currentTime - datetime.timedelta(hours = numHours) record = reading() with database.connect('envDatabase.db') as conn: db = conn.cursor() tempArray = [] rhArray = [] dateArray = [] searchPhrase = DATE_SEARCH + searchTime.strftime('%Y%m%d%H%M%S') + AREA_SEARCH + "'" + location + "'" + SEARCH_ORDER try: for row in db.execute(searchPhrase): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '') splitResult = result.split(',') splitResult[0] = str(splitResult[0]).replace('L', '') dateArray.append(datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S')) tempArray.append(float(splitResult[2])) rhArray.append(float(splitResult[3])) except: pass if len(tempArray) > 0: record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) return (tempArray, rhArray, dateArray, record)
def getFullRecordsByRange(startDate, endDate, location): record = reading() records = [] with database.connect('envDatabase.db') as conn: db = conn.cursor() searchPhrase = (DATE_SEARCH + startDate.strftime('%Y%m%d%H%M%S') + END_DATE_SEARCH + endDate.strftime('%Y%m%d%H%M%S') + AREA_SEARCH + "'" + location + "'" + SEARCH_ORDER) #print searchPhrase try: for row in db.execute(searchPhrase): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '') splitResult = result.split(',') splitResult[0] = str(splitResult[0]).replace('L', '') record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(splitResult[4]) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) records.append(deepcopy(record)) except: pass return records
def getAllRecords(numHours, location): currentTime = datetime.datetime.now() searchTime = currentTime - datetime.timedelta(hours = numHours) record = reading() networkResult = True tempArray = [] rhArray = [] dateArray = [] searchString = """ select * from readings where datetime > {0} having location = {1} order by datetime""" searchPhrase = searchString.format(searchTime.strftime('%Y%m%d%H%M%S'), "'" + location + "'") print searchPhrase try: conn = mysql.Connect(SERVER_A, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) db = conn.cursor() except mysql.OperationalError: try: conn = mysql.Connect(SERVER_B, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) db = conn.cursor() except mysql.OperationalError: networkResult = False if networkResult: db.execute(searchPhrase) for row in db.fetchall(): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '') splitResult = result.split(',') splitResult.pop(0) splitResult[0] = str(splitResult[0]).replace('L', '') dateArray.append(datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S')) tempArray.append(float(splitResult[2])) rhArray.append(float(splitResult[3])) conn.close() if len(tempArray) > 0: record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) else: return localDb.getAllRecords(numHours, location) else: return localDb.getAllRecords(numHours, location) return (tempArray, rhArray, dateArray, record)
def getCurrentRecords(numHours, location): currentTime = datetime.datetime.now() searchTime = currentTime - datetime.timedelta(hours = numHours) record = reading() with database.connect('envDatabase.db') as conn: db = conn.cursor() tempArray = [] rhArray = [] dateArray = [] searchPhrase = DATE_SEARCH + searchTime.strftime('%Y%m%d%H%M%S') + AREA_SEARCH + "'" + location + "'" + SEARCH_ORDER try: for row in db.execute(searchPhrase): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '') splitResult = result.split(',') if int(splitResult[9]) == 0: tempArray[:] = [] rhArray[:] = [] dateArray[:] = [] splitResult[0] = str(splitResult[0]).replace('L', '') dateArray.append(datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S')) tempArray.append(float(splitResult[2])) rhArray.append(float(splitResult[3])) except database.OperationalError: createTable() print 'Table Created!' if len(tempArray) > 0: record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) if (datetime.datetime.now() - record.dateTime).seconds > 900: #if more than 15 minutes have elapsed since the last record. tempArray[:] = [] rhArray[:] = [] dateArray[:] = [] return (tempArray, rhArray, dateArray, record)
def getFullRecordsByRange(startDate, endDate, location): record = reading() records = [] networkResult = True searchString = """select * from readings where datetime > {0} AND datetime < {1} having location = '{2}' order by datetime""" searchPhrase = searchString.format(startDate.strftime('%Y%m%d%H%M%S'), endDate.strftime('%Y%m%d%H%M%S'), location) print searchPhrase try: conn = mysql.Connect(SERVER_A, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) db = conn.cursor() except mysql.OperationalError: print 'Server A connection failure. Trying server B...' try: conn = mysql.Connect(SERVER_B, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) print 'Server B connection successful.' db = conn.cursor() except mysql.OperationalError: networkResult = False if networkResult: db.execute(searchPhrase) for row in db.fetchall(): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '') splitResult = result.split(',') splitResult.pop(0) splitResult[0] = str(splitResult[0]).replace('L', '') record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) records.append(deepcopy(record)) conn.close() if len(records) == 0: return localDb.getFullRecordsByRange(startDate, endDate, location) return records
def getLastRecord(): record = reading() networkResult = True searchPhrase = ('SELECT * FROM readings WHERE location = ' + "'" + location + "'" + 'ORDER BY dateTime DESC LIMIT 1') try: conn = mysql.Connect(SERVER_A, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) db = conn.cursor() except mysql.OperationalError: print 'Server A connection failure. Trying server B...' try: conn = mysql.Connect(SERVER_B, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) print 'Server B connection successful.' db = conn.cursor() except mysql.OperationalError: networkResult = False print 'Network SQL connection failure! Using local storage only!' conn = databse.connect('envDatabase.db') db = conn.cursor() if networkResult: db.execute(searchPhrase) for row in db.fetchall(): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '') splitResult = result.split(',') splitResult.pop(0) splitResult[0] = str(splitResult[0]).replace('L', '') conn.close() record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) return record
def getLastRecord(): record = reading() with database.connect('envDatabase.db') as conn: db = conn.cursor() for row in db.execute('SELECT * FROM readings ORDER BY dateTime DESC LIMIT 1'): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","") splitResult = result.split(',') splitResult[0] = str(splitResult[0]).replace('L', '') record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) return record
def getCurrentRecords(numHours, location): currentTime = datetime.datetime.now() searchTime = currentTime - datetime.timedelta(hours = numHours) record = reading() tempArray = [] rhArray = [] dateArray = [] searchString = """select * from readings where datetime > {0} having location = '{1}' order by datetime""" searchPhrase = searchString.format(searchTime.strftime('%Y%m%d%H%M%S'), location) print searchPhrase networkResult = True try: conn = mysql.Connect(SERVER_A, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) db = conn.cursor() except mysql.OperationalError: print 'Server A connection failure. Trying server B...' try: conn = mysql.Connect(SERVER_B, 'pi', 'LEM1620', 'envDatabase', connect_timeout = 30) print 'Server B connection successful.' db = conn.cursor() except mysql.OperationalError: networkResult = False if networkResult: db.execute(searchPhrase) for row in db.fetchall(): result = str(row).replace('(','').replace(')','').replace('u\'','').replace("'","").replace(' ', '').replace(' ', '') splitResult = result.split(',') splitResult.pop(0) if int(splitResult[9]) == 0: tempArray[:] = [] rhArray[:] = [] dateArray[:] = [] splitResult[0] = str(splitResult[0]).replace('L', '') dateArray.append(datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S')) tempArray.append(float(splitResult[2])) rhArray.append(float(splitResult[3])) conn.close() if len(tempArray) > 0: record.dateTime = datetime.datetime.strptime(splitResult[0],'%Y%m%d%H%M%S') record.location = splitResult[1] record.tempF = float(splitResult[2]) record.tempC = convertFtoC(record.tempF) record.RH = float(splitResult[3]) record.alarm = bool(int(splitResult[4])) record.alarmTempHigh = bool(int(splitResult[5])) record.alarmTempLow = bool(int(splitResult[6])) record.alarmRHHigh = bool(int(splitResult[7])) record.alarmRHLow = bool(int(splitResult[8])) record.continuous = bool(int(splitResult[9])) if (datetime.datetime.now() - record.dateTime).seconds > 900: #if more than 15 minutes have elapsed since the last record. tempArray[:] = [] rhArray[:] = [] dateArray[:] = [] else: return localDb.getCurrentRecords(numHours, location) else: return localDb.getCurrentRecords(numHours, location) return (tempArray, rhArray, dateArray, record)