PowerShell.pub

Eine Grafische Auswertung von Daten ist nicht immer Einfach.

Ich habe leider nichts gefunden mit dem ich einfach aus einer mySQL-DB eine Grafik erstellen kann. Daher habe ich dann aus mehreren Seite im Internet eine Python-Script zusammenkopiert. Die erstellten Diagramme können Sie jedoch sehen lassen: Heizung

Der Link verweist auf eine Kopie der aktuellen Daten. Das Beagle-Board selbst hat keinen direkten Internetzugang.

Nachfolgend das Python-Script:

# Get DS18B20 Temperatur-Data from mySQL and use mathplot
# 4.4.2015

import sys
import os
import time
import math
import datetime
import MySQLdb as mdb
import numpy

import matplotlib
matplotlib.use('agg')

matplotlib.rcParams['figure.dpi'] = 200
matplotlib.rcParams['figure.figsize'] = [13.65, 10.24]
matplotlib.rcParams['lines.linewidth'] = 0.5
matplotlib.rcParams['axes.color_cycle'] = ['r', 'g', 'b', 'c', 'm', 'k']
matplotlib.rcParams['axes.labelsize'] = 'large'
matplotlib.rcParams['font.size'] = 8
matplotlib.rcParams['grid.linestyle']='-'

import matplotlib.pyplot as plt

# Check if ther is a commandline-parameter
if (len(sys.argv)) == 2 and int(sys.argv[1]) > 0:
	TimeValue = sys.argv[1]
else:
	TimeValue = "24"

#print TimeValue

DBconn = mdb.connect('localhost', 'root', 'XXXXXXXXXX', 'measure')
cursor = DBconn.cursor(mdb.cursors.DictCursor)
sql1 =  "select M.SensorID as id, S.name as name"
sql1 += " from measure M inner join sensor S on ( S.id = M.SensorID )"
sql1 += " where M.ts > (DATE_SUB(now(), INTERVAL " + TimeValue + " HOUR)) group by M.SensorID"

cursor.execute(sql1)
Sensors = cursor.fetchall()
Sens = len ( Sensors ) +1
CurSens = 1
cursor = DBconn.cursor()
sql1 =  "select unix_timestamp(M.ts) as ts, M.value / 1000 as value "
sql1 += "from measure M "
sql1 += "where M.ts > (DATE_SUB(now(), INTERVAL " + TimeValue + " HOUR)) and M.SensorID = "
tempmax = 0
tempmin = 0
Title = "Temperaturen unserer Heizung: " + TimeValue + " Stunden"

for Sensor in Sensors:
	#print Sensor['id'], Sensor['name'],
	#Title += Sensor['name'] + ", "
	sql2 = sql1 + str(Sensor['id']) + " order by TS desc"
	cursor.execute(sql2)
	Raw = cursor.fetchall()
	Raw = numpy.fromiter(Raw, count=-1, dtype=[('', numpy.float)]*2)
	Raw = Raw.view(numpy.float).reshape(-1, 2)
	for value in Raw:
		if tempmax < value[1]:
			tempmax = value[1]
		if tempmin > value[1]:
			tempmin = value[1]
	(samples,ports)=Raw.shape
	#print 'Samples: {}, DataPoints: {}'.format(samples,ports)
	plotme=numpy.zeros((samples,ports-1)) # make an array the same shape minus the epoch numbers
	for y in range(ports-1):
		for x in range(samples-1):
			seconds = Raw[x+1,0]-Raw[x,0]
			plotme[x,y] = Raw[x,y+1]
		plotme[samples-1,y] = None
	dts = map(datetime.datetime.fromtimestamp, Raw[:,0])
	lines = plt.plot(dts, plotme, label=Sensor['name']) 
	#plt.text(dts[0], plotme[0], Sensor['name'], fontsize=8)
	position = ((samples / ( Sens )) * ( CurSens ))
#	if the calculation of the position does not work??!!
	if position > samples:
		position = samples / 2
#	print samples, position
	plt.annotate(Sensor['name'], xy=(dts[position], plotme[position]),  xycoords='data',
                xytext=(15, 15), textcoords='offset points',
                arrowprops=dict(arrowstyle="->",
                                connectionstyle="angle,angleA=0,angleB=90,rad=10"),
                )
	CurSens += 1

DBconn.close()
#print plotme

# set up the plot details we want
plt.grid(True)
plt.ylabel('Temp $^\circ$C')

tempmax = int(( tempmax + 8 ) / 5 ) * 5
if tempmin < 0 :
        tempmin = int(( tempmin - 3 ) / 5) * 5

print "Max: " + str(tempmax)
print "Min: " + str(tempmin)

plt.axis(ymax=(tempmax) ,ymin=(tempmin) )

plt.xlabel(time.asctime())
plt.title(Title)
plt.hold(True)

# format Time and Date
plt.gca().xaxis.set_major_formatter(matplotlib.dates.DateFormatter('%d.%m. %H:%M'))
plt.gca().xaxis.set_major_locator(matplotlib.dates.AutoDateLocator())
#lines = plt.plot(dts,plotme)
plt.gcf().autofmt_xdate()

FileName = "/tmp/" + TimeValue + "hour.png"
plt.savefig(FileName)

Destination = '/var/www/'
os.system("cp " + FileName + " {}".format(Destination))