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)
