In these examples you can see the consumption of different energy consumers as heatpumps (VPn), heatfloors (VGolv_xx), hot water (VVB), and others as well as the total electrical consumption
In short it works as follows:
A backup of the database is made each hour.
For each graph a python script is executed from the crontab. The script reads data from the backup database via sql and by use of the plotly offline function creates a html-page containing the graph.
The html-page with the graph is made accessible from the Domoticz Custom page
My python script for the hour graph looks as follows. I am not a python expert and this is my first python code.
Code: Select all
#!/usr/bin/python2.7
# -*- coding: utf-8 -*-
import sqlite3 as lite
import sys
from time import gmtime, strftime
import datetime
from plotly.offline import download_plotlyjs, plot
from plotly.graph_objs import *
layout = Layout(
barmode='stack',
title= 'Energiforbrukning per timme i kWh',
yaxis=dict(
title='kWh'
),
yaxis2=dict(
title='Grader',
# titlefont=dict(
# color='rgb(148, 103, 189)'),
overlaying='y',
side='right'
),
)
totConsumpt = [0] * 250
startTime = datetime.datetime.now()
con = lite.connect('/home/pi/domoticz/backups/backup-hour.db')
with con:
def getHoursE (idx):
cur = con.cursor()
timeSqlStart = startTime.strftime("%Y-%m-%d") + 'T' + startTime.strftime("%H:%M")
## text = "SELECT * FROM `Meter` WHERE `DeviceRowID` LIKE " + str(idx) + " AND `Date` < '" + timeSqlStart + "' AND `Date` > '" + timeEnd + "' AND `Date` LIKE '%00:__' ORDER BY `date` DESC LIMIT 0, 40;"
text = "SELECT * FROM `Meter` WHERE `DeviceRowID` LIKE " + str(idx) + " AND `Date` < '" + timeSqlStart + "' AND `Date` LIKE '%00:__' ORDER BY `date` DESC LIMIT 0, 40;"
# print (text)
cur.execute(text)
xrows = cur.fetchall()
return xrows
def getEnergyConsumpt (idx):
rows = getHoursE(idx)
noOfRows = len(rows)
# print (noOfRows)
xxx = [None] * noOfRows
yyy = [None] * noOfRows
i = 0
while (i < noOfRows-1):
xxx[i] = rows[i+1][2][:13] #retrieve only hours, not minutes and seconds
yyy[i] = (rows[i][1] - rows[i+1][1])/1000.0
totConsumpt[i] = totConsumpt[i] + yyy[i]
i += 1
return xxx, yyy
def getOtherEnergyConsumpt (idx):
rows = getHoursE(idx)
noOfRows = len(rows)
# print (noOfRows)
xxx = [None] * noOfRows
yyy = [None] * noOfRows
ttt = [None] * noOfRows
i = 0
while (i < noOfRows-1):
xxx[i] = rows[i+1][2][:13] #retrieve only hours, not minutes and seconds
y = (rows[i][1] - rows[i+1][1])/1000.0
yyy[i] = y - totConsumpt[i]
if (yyy[i] < 0 ): # no negative values
yyy[i] = 0
ttt[i] = 'E_tot: ' + str(y)
i += 1
return xxx, yyy, ttt
def getTemp (idx):
cur = con.cursor()
timeSqlStart = startTime.strftime("%Y-%m-%d") + 'T' + startTime.strftime("%H:%M")
# text = "SELECT * FROM `Meter` WHERE `DeviceRowID` LIKE " + str(idx) + " AND `Date` < '" + timeSqlStart + "' AND `Date` LIKE '%00:__' ORDER BY `date` DESC LIMIT 0, 40;"
text = "SELECT `DeviceRowID`, `Date`, `Temperature` FROM `Temperature` WHERE `DeviceRowID` LIKE " + str(idx) + " AND `Date` < '" + timeSqlStart + "' AND `Date` LIKE '%00:__' ORDER BY `Date` DESC LIMIT 0, 40";
# print (text)
cur.execute(text)
rows = cur.fetchall()
noOfRows = len(rows)
# print (noOfRows)
xxx = [None] * noOfRows
yyy = [None] * noOfRows
i = 0
while (i < noOfRows-1):
xxx[i] = rows[i+1][1][:13] #retrieve only hours, not minutes and seconds
yyy[i] = rows[i+1][2]
i += 1
# print (xxx)
# print (yyy)
return xxx, yyy
#VP1
xx, yy = getEnergyConsumpt(420)
trace1 = Bar(
x=xx,
y=yy,
name='VP1'
)
#VP2
xx, yy = getEnergyConsumpt(422)
trace2 = Bar(
x=xx,
y=yy,
name='Vp2'
)
#VVB
xx, yy = getEnergyConsumpt(479)
trace3 = Bar(
x=xx,
y=yy,
name='VVB'
)
#Varmegolv Uppe
xx, yy = getEnergyConsumpt(262)
trace4 = Bar(
x=xx,
y=yy,
name='VGolv_Uppe'
)
#Varmegolv Hall
xx, yy = getEnergyConsumpt(467)
trace5 = Bar(
x=xx,
y=yy,
name='VGolv_Hall'
)
#Varmegolv SPA
xx, yy = getEnergyConsumpt(469)
trace6 = Bar(
x=xx,
y=yy,
name='VGolv_SPA'
)
#Varmegolv UteRum
xx, yy = getEnergyConsumpt(468)
trace7 = Bar(
x=xx,
y=yy,
name='VGolv_UteRum'
)
#Other consumpt (subtracted from E_tot)
xx, yy, tt = getOtherEnergyConsumpt(461)
trace8 = Bar(
x=xx,
y=yy,
text= tt,
name='Hushallsel'
)
#Ute temp
xx, yy = getTemp(426)
trace9 = Scatter(
x=xx,
y=yy,
name='Ute_Temp',
yaxis = "y2"
)
data = [trace1, trace2, trace3, trace4, trace5, trace6, trace7, trace8, trace9]
fig = Figure(data=data, layout=layout)
plot(fig, filename='stacked-bar-hours.html')
# copy file to domoticz
import shutil
shutil.copy2('/home/pi/stacked-bar-hours.html', '/home/pi/domoticz/www/bargraph/stacked-bar-hours.html')