def stats(): conn = get_conn() c = conn.cursor() c.execute("SELECT COUNT(*),MIN(date),MAX(date) FROM samples") stats = c.fetchone() c.close() return dict(count=stats[0], earliest=stats[1], latest=stats[2])
def generate_badge(): conn = get_conn(DB) c = conn.cursor() c.execute("SELECT * FROM samples ORDER BY DATE DESC LIMIT 1") row = c.fetchone() c.close() date = dateparser.parse(row[0]) co2 = str(int(row[1])) timestamp = date.strftime("%I:%M%p") datestamp = date.strftime("%m/%d") if (datetime.now() - date).days > 0 else "" print "Generating badge: %s - %s %s" % (co2, timestamp, datestamp) image = Image.open("template.png") draw = ImageDraw.ImageDraw(image) font = ImageFont.truetype("arial.ttf", 10) draw.setfont(font) dw, dh = font.getsize(datestamp) draw.text((30 - dw / 2, 78 - dh), datestamp) tw, th = font.getsize(timestamp) draw.text((30 - tw / 2, 77 - th - dh), timestamp) font = ImageFont.truetype("arial.ttf", 26) draw.setfont(font) cw, ch = font.getsize(co2) draw.text((30 - cw / 2, 8), co2) image.save("webroot/badge.png")
def fetch(date): conn = get_conn() c = conn.cursor() c.execute("SELECT * FROM samples WHERE date>? ORDER BY date LIMIT 1000", (date,)) rows = c.fetchall() c.close() return dumps(rows)
def synchronize(): conn = get_conn(DB) c = conn.cursor() c.execute("SELECT MAX(date) FROM samples") row = c.fetchone() date = row[0] or "0" print "Synchronizing starting with date %s" % date f = urllib.urlopen(FETCH_URL + date) result = f.read() rows = loads(result) for row in rows: c.execute("INSERT INTO samples (date, co2, co2abs, cellpres, celltemp, ivolt) VALUES(?,?,?,?,?,?)", row) conn.commit() c.close() print "Synchronized %s rows" % len(rows)
def synchronize(): conn = get_conn(DB) c = conn.cursor() c.execute("SELECT MAX(date) FROM samples") row = c.fetchone() date = row[0] or "0" print "Synchronizing starting with date %s" % date f = urllib.urlopen(FETCH_URL + date) result = f.read() rows = loads(result) for row in rows: c.execute( "INSERT INTO samples (date, co2, co2abs, cellpres, celltemp, ivolt) VALUES(?,?,?,?,?,?)", row) conn.commit() c.close() print "Synchronized %s rows" % len(rows)
def average_rows(sql, params, freq=timedelta(0, 60)): conn = get_conn() c = conn.cursor() c.execute(sql, params) rows = c.fetchall() values = [float(rows[0][1])] time = dateparser.parse(rows[0][0]) data = [] for row in rows[1:]: if dateparser.parse(row[0]) - time > freq: avg = sum(values) / len(values) data.append((time, avg)) time = dateparser.parse(row[0]) values = [float(row[1])] else: values.append(float(row[1])) c.close() return data
def run(self): conn = get_conn() while True: try: if not self.simulate: print "Reading sample" ser = Serial(2, 9600) ser.readline() # first line may be incomplete, read and discard line = ser.readline() # second line should be good ser.close() else: # simluation mode, make up some data print "Simulating sample" line = "<s><t><co2>%s</co2><co2abs>-1</co2abs><cellpres>-1</cellpres><celltemp>-1</celltemp><ivolt>-1</ivolt></t></s>" % random.randint(200, 500) except SerialException: # port is not available right now, try again later line = None try: if line: x = parseString(line) self.lastsample = dict((n.nodeName, float(n.firstChild.data)) for n in x.firstChild.firstChild.childNodes if n.nodeName != 'raw') self.lastsampletime = datetime.now() c = conn.cursor() c.execute("INSERT INTO samples (date, co2, co2abs, cellpres, celltemp, ivolt) VALUES(?,?,?,?,?,?)", (self.lastsampletime.isoformat(), self.lastsample['co2'], self.lastsample['co2abs'], self.lastsample['cellpres'], self.lastsample['celltemp'], self.lastsample['ivolt']) ) try: conn.commit() except OperationalError: print "Could not commit sample - database locked?" pass # Ignore locked database error c.close() except ExpatError, err: # line was invalid, try again later print err time.sleep(10)
def recent(): conn = get_conn() c = conn.cursor() c.execute("SELECT date,co2 FROM samples WHERE date>? ORDER BY date", ((datetime.now() - timedelta(1)).isoformat(),)) rows = c.fetchall() values = [float(rows[0][1])] time = dateparser.parse(rows[0][0]) data = [] for row in rows[1:]: if dateparser.parse(row[0]) - time > timedelta(0, 60): avg = sum(values) / len(values) data.append((time, avg)) time = dateparser.parse(row[0]) values = [float(row[1])] else: values.append(float(row[1])) c.close() datatable = gviz_api.DataTable([("time", "datetime"), ("CO2", "number")], data=data) callback = request.GET.get('responseHandler', 'google.visualization.Query.setResponse') return datatable.ToJSonResponse(response_handler=callback)