Yep had to add sensor values directly to the db by using the sqlite3 commands in python.
Resulting code:
Code: Select all
#=========================================================================
# I have solar panels and a SMA solar inverter.
# The data from the SMA solar inverter is streamed to a website which I
# can access to view the power output and a company can access for
# monitoring and maintenance.
# I can tell the SMA inverter to stream to Domoticz INSTEAD of to the company
# but I want to keep them do maintenance and monitoring.
#
# This script scrapes the website through the consumer portal.
# It does this a few times a day to prevent overloading the website
# The data scraped contains a list of sensor values and their recording time
# We cannot use the normal json api (url) because this timestamps all sensor
# data to the current time, a custom timestamp is not accepted
# The data (with timestamps) can however be inserterd into the SQL database
############################### IMPORTS ##################################
import requests
from shutil import copy2
import datetime
import subprocess
import json
import sqlite3
############################### USER VARS ################################
#SEM WEBSITE CREDENTIALS AND URLs
username = "myusername" # put your own username here
password = "mypassword" # put your own password here
urlLogin="http://login page.php" # put the login page here
urlData="http://data.php?somepars" #put the page with data here
values = { 'frm_username': username, 'frm_password': password} #include ALL login form field names/values which are sent on logging in
#DOMOTICZ DB VARS
devId = 8; #the device ID in de domoticz database
domoticzPath='/home/pi/domoticz/'
#JSON KEY FOR VALUE is SMA ID
smaId = '6666666666' # In the json data this SMA ID is the key for the sensorvalues. NOTE: ENCLOSE IN QUOTES
############################### CODE #####################################
#-------------------------------------------------------------------------
#SCRAPE SENSOR VALUES OF TODAY
#-------------------------------------------------------------------------
#DOWNLOAD WEBPAGE WITH DATA
#with cookies
s=requests.session()
r=s.post(urlLogin,data=values)
c=r.cookies
r=s.get(urlData,cookies=c,verify=False)
raw=r.content.decode("utf-8")
#EXTRACT DATA FROM RAW HTML
#FORMAT: "dataProvider": [{"Time":"19:30",},{"Time":"19:35","6666666666":"0.76",},{"Time":"19...]
matchFrom='"dataProvider": ['
matchTo='],'
charFrom=raw.find(matchFrom)
charTo=raw.find(matchTo,charFrom)+1
data=raw[charFrom:charTo]
data=data.replace('\t','').replace('\n','').replace('\r','')
#remove some mall formed parts
data=data.replace(', }','}').replace(', }','}').replace(',}','}')
data=data.replace(',]',']')
data="{"+data
data=data+"}"
print ("FOUND SENSOR DATA: ",data)
jsonData = json.loads(data)
#-------------------------------------------------------------------------
#BACKUP DOMOTICZ DB
#-------------------------------------------------------------------------
now=datetime.datetime.now().strftime(".%Y-%m-%d(%H-%M-%S)")
strnow=str(now)
print ('BACKUP DB in: ',domoticzPath, 'with timestamp:',strnow)
dbFilePath=domoticzPath+"domoticz.db"
bpFilePath=domoticzPath+"backup/domoticz.db"+strnow
copy2(dbFilePath, bpFilePath)
#-------------------------------------------------------------------------
#STOP DOMOTICZ
#-------------------------------------------------------------------------
subprocess.call(["sudo","bash","{}domoticz.sh".format(domoticzPath),"stop"])
#-------------------------------------------------------------------------
#ADD NEW JSON DATA TO DB
#-------------------------------------------------------------------------
# OPEN DATABASE
dbConn = sqlite3.connect(dbFilePath)
dbCursor=dbConn.cursor()
# DEFINE SQL STATEMENTS AND SOME VARS
sqlFind = "SELECT `_rowid_`,* FROM `Meter` WHERE `DeviceRowID`='{}' AND `Date`='{} {}:00';"
sqlInsert = 'INSERT INTO `Meter`(`DeviceRowID`,`Value`,`Usage`,`Date`) VALUES ({},{},0,"{} {}:00");'
sDate = datetime.datetime.now().date().strftime("%Y-%m-%d") # current date
# FOR EACH JSON ROW CHECK IF EXISTS IN BD AND INSERT IN DB IF NOT
nrInsert=0
for jsonRow in jsonData["dataProvider"]:
sTime = jsonRow['Time']
if smaId in jsonRow:
sValue = jsonRow[smaId]
print("CHECK:|", jsonRow, "|", "<",sTime,sValue,">")
dbCursor.execute(sqlFind.format(devId, sDate, sTime))
nrRows = 0
for row in dbCursor:
print("FOUND:", row)
nrRows = nrRows + 1
if nrRows==0: # no records with same date and deviceID found
dbConn.execute(sqlInsert.format(devId, sValue, sDate, sTime))
nrInsert=nrInsert+1
print ("INSERT:",sqlInsert.format(devId, sValue, sDate, sTime))
print ("TOTAL SENSOR VALUES INSERTED:",nrInsert)
# COMMIT CHANGES AND CLOSE DATABASE
dbConn.commit()
dbConn.close()
#-------------------------------------------------------------------------
#START DOMOTICZ
#-------------------------------------------------------------------------
subprocess.call(["sudo","bash","{}domoticz.sh".format(domoticzPath),"start"])
And running it four times a day in crontab
Code: Select all
30 07 * * * sudo python3 /home/pi/domoticz/scripts/python/scrape-sem.py >/dev/null 2>&1
30 11 * * * sudo python3 /home/pi/domoticz/scripts/python/scrape-sem.py >/dev/null 2>&1
30 16 * * * sudo python3 /home/pi/domoticz/scripts/python/scrape-sem.py >/dev/null 2>&1
30 22 * * * sudo python3 /home/pi/domoticz/scripts/python/scrape-sem.py >/dev/null 2>&1
Hope this is helpfull to others out there. But I really think the option to backdate (specify custom timestamp for) sensor values should be added to the json api (yourip:yourport/json.htm?...) of domoticz.