Domoticz

Code: Select all

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

Code: Select all

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

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"])

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