Upload (previous) sensor values of last hour with JSON

[nardj]

Hi,

I have a SMA solar inverter which outputs its data to a website beyond my control. I don't have direct access to the SMA inverter, so can't link this to my RPi-Domoticz setup.

I made a Python script which scrapes the website only a few times a day (to avoid overloading the website). This produces a list of sensor values and the time they were recorded.

Code: Select all

14:10; 1.86
14:15; 1.11
14:25; 2.7
14:30; 0.69
14:35; 1.78

I want to upload this list to Domoticz using a JSON url call like

Code: Select all

http://localhost:8080/json.htm?type=command&param=udevice&idx=8&svalue=3000&nvalue=0

However I can't find any info on how to modify the url to include the time the sensor value was recorded. I already tried adding &date=2016-07-06 20:12, &date="2016-07-06 20:12" and &date=1467820145 (unix time). The status I receive is 'OK' however the values are added at the current time instead of the time I passed in the url.

Any ideas?

[trixwood]

I do not think you can back-post sensor data of the last hour, there is no option mentioned in https://www.domoticz.com/wiki/Domoticz_API/JSON_URL's
and the only date format mention there is MM-DD-YYYY, but that is related to other stuff.

I am also curious how to import data into domoticz db. you probably have to use sqlite in a script to talk to the db

[nardj]

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.

[Toulon7559]

@nardj

Direct interface is always better, because no delays and no processing by the 'intermediary' servers.

Which type of SMA-inverter do you have?
If you do not want to 'fiddle' with interfaces, you might think about using a PVLogger like this one:
http://fp4all.com/documents/SMAlogger-leaflet-NL.pdf
Such Logger has a local webpage and local USB-saving, provides an automatic upload to PVOutput each 5 minutes, and you can tap it's LAN-interface for XML-output.
Both PVOutput and XML are easily accessible from Domoticz:
- PVOutput-reading you can find under Dashboard/Hardware,
- for XML a readout-script is available in this Forum (see http://www.domoticz.com/forum/viewtopic ... 837#p84627 )

[nardj]

The delays are a bit of a nuisance indeed.

I have a SMA 5000T which has an UTP connection. However as I understand, you can only activate UTP OR Bluetooth on this inverter. The bluetooth is however needed by the company monitoring/servicing the inverter. If I understand incorrectly, or you know a workaround, I would very much like to know!

[Toulon7559]

If the UTP is not accessible, but only bluetooth, than perhaps this device can be used: http://fp4all.com/documents/SMA-bleutoo ... let-NL.pdf
Suggested to directly ask [email protected] what would be applicable for your case.