No data from sbfspot to Domoticz

[Mithrandir]

A great!

Was already screenshotting my code

[bontwoody]

#!/usr/bin/python

from sqlite3 import connect
from datetime import datetime
from datetime import datetime

conn = connect('/home/pi/smadata/SBFspot.db')
curs = conn.cursor()
#Get last rows of day file for latest reading of solar output
curs.execute("select Timestamp, Power, TotalYield from vwDayData ORDER BY Timestamp DESC LIMIT 20")
rows1=curs.fetchall()
print(rows1)

curs.close
conn.close

conn = connect('/home/pi/domoticz/domoticz.db')
curs = conn.cursor()

#get last reading added to domoticz
curs.execute("select Date from Meter ORDER BY Date DESC LIMIT 1")
rows2=curs.fetchall()
#get last timestamp added to domoticz
if len(rows2)==0: #check there is some data in Meter table
now=datetime.now()
DT=now.strftime("%d/%m/%Y %H:%M:%S")
else:
#print(rows2)
DT=rows2[0][0]

a=0 # variable for flag to append date - 0=append, 1=dont append
for n in range(19):

if DT!=rows1[n][0] and a==0:#append data
#print("append", DT, rows1[n][0],a, rows1[n][1], rows1[n][2])
val=rows1[n][1]
tot=rows1[n][2]
params = (11,val*10,tot,rows1[n][0])
curs.execute("INSERT INTO Meter(DeviceRowID,Usage,Value,Date) VALUES (?,?,?,?)", params)
conn.commit()

elif DT==rows1[n][0] and a==0: #set 'dont append'flag
#print("set flag", DT, rows1[n][0],a)
a=1
else:
print("do nothing", DT, rows1[n][0],a)

curs.close()
conn.close()

code.jpg (193.76 KiB) Viewed 1003 times

[Mithrandir]

Great! It’s working now... thanks for all the support

[bontwoody]

Glad to have been of help

[Mithrandir]

Very interesting! It works for the day overview but Friday shows total of the eTotal and not the day total.

B6A616E9-04EA-43CD-9063-1793365D5B83.jpeg (202.45 KiB) Viewed 992 times

And very surprisingly; SBFspot is now also working a bit.. not the daily but weekly and monthly is showing the right information. Don’t understand that the python script is doing the exact opposite.

[Mithrandir]

Really not sure what’s happening.. the python script is now updating based on the current new numbers. It now also shows Saturday so I guess it was just the startup in that part of the script.

The ‘original’ is not showing current and still doesnt have the numbers from Friday. Tried to disable/enable but not updating anymore.

8B28DB3B-E7D4-4F44-907A-B258BB51B35D.jpeg (260.7 KiB) Viewed 990 times

[bontwoody]

Very Odd! The Python data should settle down as you get more data points I think. One way of seeing what going on might be to look directly into the Domoticz database. Could you download a copy from your zero and view it on a another computer?

[HvdW]

The solution is simple.

- Do the automated setup once more.
- Preserve your settings
- Choose advanced options
- Choose advances CSV settings
- Set export to CSV to ON

That's all

[Mithrandir]

I tried your solution with reinstalling SBFspot config but that's not working. It imports old data but not the new data. Or do you mean a different way with "automated setup"?

I used: curl -s https://raw.githubusercontent.com/sbfsp ... pot-config | sudo bash

thanks!!

[Doudy]

Virtualdevice.png

OK, so I havent been able to get the inbuilt import function to work properly but I have managed to use a virtual sensor and a python script to do what is required. There are a couple of glitches with the data in the image but Im very close. Im still fiddling with it to get the best results but Im happy to share how I did it with you if you want. Basically I an querying the SBFspot database directly and then inserting the values into the Domoticz database through the python code which is run every 5 minutes via crontab.

I just tested your script and it works
I used a copy of the Domoticz DB to do these tests !
I created one Device - Virtual Sensor(497) - Electric (Instant +counter ) => 243 General / 29 kWh Electric (Instant+Counter) voir ici : https://www.domoticz.com/wiki/Developin ... hon_plugin
The script used:

Code: Select all

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlite3 import connect
from datetime import datetime

## Obtenir les 20 dernières lignes du fichier du jour pour les dernières lecture de la production solaire.
## Obtenir plus de 1 en cas de mauvaise connexion Bluetooth

conn = connect('/home/pi/smadata/SBFspot.db')
curs = conn.cursor()

curs.execute("select Timestamp, Power, TotalYield from vwDayData ORDER BY Timestamp DESC LIMIT 20")
rows1=curs.fetchall()
print(rows1)

curs.close
conn.close

conn = connect('/home/pi/domoticz/domoticz.db')
curs = conn.cursor()

## Obtenir la dernière lecture ajoutée à domoticz
curs.execute("select Date from Meter ORDER BY Date DESC LIMIT 1")
rows2=curs.fetchall()
if len(rows2)==0: #check there is some data in Meter table
	now=datetime.now()
	DT=now.strftime("%d/%m/%Y %H:%M:%S")
else:
## Obtenir le dernier horodatage ajouté à domoticz
	print(rows2)
	DT =rows2[0][0]

	a=0 ## variable pour l'indicateur d'ajout de la date - 0=ajouter, 1=ne pas ajouter
for n in range (19) :

	if DT !=rows1[n][0] and a== 0: ## append data
		print("append", DT, rows1[n][0],a, rows1[n][1], rows1[n][2])
		val=rows1[n][1]
		tot =rows1[n][2]
		params = (497,val*10,tot,rows1[n][0])
		curs.execute("INSERT INTO Meter(DeviceRowID,Usage,Value,Date) VALUES (?,?, ?,?)", params)
		conn.commit()

	elif DT==row1[n][0] and a==0 : ## set 'dont append'flag
		print("set flag", DT, rows1[n][0],a)
		a=1
	else :
		print ("Ne rien faire", DT, row1[n][0],a)

curs.close()
conn.close()

The command:

Code: Select all

pi@raspberrypi-185:~/domoticz/scripts/python $ python SBFSpot_db_2.py

Here is the result :

Code: Select all

[(u'2022-08-12 17:00:00', 1464, 21718773), (u'2022-08-12 16:55:00', 1464, 21718651), (u'2022-08-12 16:50:00', 1500, 21718529), (u'2022-08-12 16:45:00', 1512, 21718404), (u'2022-08-12 16:40:00', 1524, 21718278), (u'2022-08-12 16:35:00', 1524, 21718151), (u'2022-08-12 16:30:00', 1548, 21718024), (u'2022-08-12 16:25:00', 1560, 21717895), (u'2022-08-12 16:20:00', 1572, 21717765), (u'2022-08-12 16:15:00', 1584, 21717634), (u'2022-08-12 16:10:00', 1596, 21717502), (u'2022-08-12 16:05:00', 1608, 21717369), (u'2022-08-12 16:00:00', 1596, 21717235), (u'2022-08-12 15:55:00', 1620, 21717102), (u'2022-08-12 15:50:00', 1596, 21716967), (u'2022-08-12 15:45:00', 1608, 21716834), (u'2022-08-12 15:40:00', 1488, 21716700), (u'2022-08-12 15:35:00', 1620, 21716576), (u'2022-08-12 15:30:00', 1632, 21716441), (u'2022-08-12 15:25:00', 1644, 21716305)]
[(u'2022-08-13 08:15:03',)]
('append', u'2022-08-13 08:15:03', u'2022-08-12 17:00:00', 0, 1464, 21718773)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:55:00', 0, 1464, 21718651)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:50:00', 0, 1500, 21718529)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:45:00', 0, 1512, 21718404)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:40:00', 0, 1524, 21718278)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:35:00', 0, 1524, 21718151)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:30:00', 0, 1548, 21718024)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:25:00', 0, 1560, 21717895)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:20:00', 0, 1572, 21717765)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:15:00', 0, 1584, 21717634)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:10:00', 0, 1596, 21717502)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:05:00', 0, 1608, 21717369)
('append', u'2022-08-13 08:15:03', u'2022-08-12 16:00:00', 0, 1596, 21717235)
('append', u'2022-08-13 08:15:03', u'2022-08-12 15:55:00', 0, 1620, 21717102)
('append', u'2022-08-13 08:15:03', u'2022-08-12 15:50:00', 0, 1596, 21716967)
('append', u'2022-08-13 08:15:03', u'2022-08-12 15:45:00', 0, 1608, 21716834)
('append', u'2022-08-13 08:15:03', u'2022-08-12 15:40:00', 0, 1488, 21716700)
('append', u'2022-08-13 08:15:03', u'2022-08-12 15:35:00', 0, 1620, 21716576)
('append', u'2022-08-13 08:15:03', u'2022-08-12 15:30:00', 0, 1632, 21716441)

But the result is not what I expected:


I have negative data!!
An idea ?

One question:
Could you describe the line for me? '2022-08-13 08:15:03', u'2022-08-12 15:30:00', 0, 1632, 21716441 mostly 1632, 21716441

[Doudy]

Hello,
I found a solution
I delete the data in tables Meter and Meter_Calendar

Code: Select all

sudo /etc/init.d/domoticz.sh stop
cd domoticz
sqlite3 domoticz.db
DELETE from Meter where DeviceRowId = 497;
DELETE from Meter_Calendar where DeviceRowId = 497;
.quit
sudo /etc/init.d/domoticz.sh start

Result:


Two more questions :
Could you describe the line for me? '2022-08-13 08:15:03', u'2022-08-12 15:30:00', 0, 1632, 21716441 mostly 1632, 21716441
I have data since 2009 how to integrate them?