def getLastNRowsBySensor(sensorId, rowCount):
try:
logging.info("Get last %s data points for sensor %s", rowCount,
sensorId)
mydb = sqlhelper.createConnection(sqlhelper.dbfilename)
cursor = mydb.cursor()
sql = '''select count(*) from datapoints'''
result = cursor.execute(sql).fetchone()
lastRow = result[0]
howManyGoBack = infohelper.DataInfo.dataInfo.numberOfSensors * rowCount
cursor.execute(
'''SELECT * FROM datapoints WHERE sensorid=? and id>=?''', (
sensorId,
lastRow - howManyGoBack,
))
rows = cursor.fetchall()
while (len(rows) > rowCount):
rows.pop(0)
mydb.close()
return rows
except sqlite3.Error as e:
logging.exception("Exception occurred")
logging.error("Unable to get data point %s", id)
mydb.close()
return None
def get(self, sensorid, numberrows):
db = sqlhelper.createConnection(sqlhelper.dbfilename)
changes = infohelper.getChange(db, numberrows)
timeBetweenReads = infohelper.timeBetweenSensorReads(db) * numberrows
change = changes[sensorid - 1]
rateOfChange = (60 * 60 * change) / timeBetweenReads
return jsonify({'change': change, 'rateOfChange': rateOfChange})
def summary():
page = "<!DOCTYPE html>"
page = page + "<html><h1>Web Thermometer Sensor Summary</h1><hr><br>"
db = sqlhelper.createConnection(sqlhelper.dbfilename)
rowCount = sqlhelper.countRows(db)
page = page + "Number of data points in table: " + str(rowCount) + '<p>'
row = sqlhelper.getRow(db, rowCount)
# display how many sensor are collecting data
sensorCount = infohelper.numberSensors(db)
page = page + "Number of sensors: " + str(sensorCount) + '<p>'
# display how many sensor are collecting data
rateOfChange = infohelper.getRateOfChange(db)
page = page + "Rate of change: " + str(rateOfChange) + '<p>'
# display time elapsed between sensor writes
timeBetweenReads = infohelper.timeBetweenSensorReads(db)
page = page + "Time between sensor reads: " + str(timeBetweenReads) + '<p>'
# display trends over past 10 mins
rows = sqlhelper.getRows(db, rowCount - 12, rowCount)
page = page + "Last 12 rows: <p>"
for r in rows:
page = page + str(r) + '<p>'
page = page + "Last data point: " + str(row) + '<p>'
db.close()
page = page + "</html>"
return page
def csv():
# return all data as CSV (comma seperated values)
page = ""
db = sqlhelper.createConnection(sqlhelper.dbfilename)
numrows = sqlhelper.countRows(db)
for id in range(0, numrows):
row = sqlhelper.getRow(db, id)
#print(row)
if row != None:
channel = row[1]
if (channel == 1):
datestamp = row[2]
timestamp = row[3]
page = page + "\n"
page = page + datestamp + ' ' + timestamp
page = page + ", " + str(row[5])
return page
# compute the change
timeBetween = DataInfo.dataInfo.timeBetweenSensorReads
rangeInbetween = int(timeInterleave / (timeBetween + 1))
rowCount = sqlhelper.countRows(db)
sensorCount = DataInfo.dataInfo.numberSensors
# get the last 6 rows
offset = sensorCount * rangeInbetween
rows = []
for rowIndex in range(0, numberRows):
offsetIndex = offset * rowIndex
rows.append(
sqlhelper.getRows(db, rowCount - sensorCount - offsetIndex + 1,
rowCount - offsetIndex))
# set up a return list that has change for each sensor
changeArray = []
for rowIndex in range(0, numberRows):
changeArray.append([None] * (sensorCount + 1))
changeArray[rowIndex][0] = rows[rowIndex][1][3]
for sensor in range(0, sensorCount):
sensorId = rows[rowIndex][sensor][1]
v = round(rows[rowIndex][sensorId - 1][5], 1)
changeArray[rowIndex][sensorId] = v
return changeArray
mydb = sqlhelper.createConnection(sqlhelper.dbfilename)
DataInfo.dataInfo = DataInfo(mydb)
def changes():
db = sqlhelper.createConnection(sqlhelper.dbfilename)
a = infohelper.getChanges(db, 10, 10 * 60)
return render_template('changes.html', data=a, lastupdated=a[0][0])