Community
OpenEnergyMonitor

Community

SBFSpot

smasolar
emonpi
emoncms
Tags: #<Tag:0x00007f16a7257938> #<Tag:0x00007f16a72577f8> #<Tag:0x00007f16a72576b8>

(Daniel Bates) #1

Exciting. I have SBFSpot running along-side emonCMS on a rPi 3B+.

This uploads solar PVdata to PVoutput.org.
SBFSpot is a very well maintained program with good documentation, and retrieves 5 minute resolution data from a wide range (possibly all) SMA solar inverters via bluetooth or ethernet.

SBFSpot works by downloading real-time and archival data via bluetooth or ethernet, storing it in a local database on the Pi for a user-defined timeframe (days, months, years…). SQLite is used to create and manage the database. An upload Daemon reads the database and sends the data to PVOutput.org.

Another highlight was the first run from SSH :slight_smile:

Next steps are to create a script to read the database and bring data into emonCMS. I plan to use Python’s pySQlite. I have no idea how to go about this yet and the hours I’ll need in front of a screen… well I’ll have to keep a close eye on the caffeine intake to not go mad :wink: Help would be much appreciated. I hope to create a module which fits with emonHub… again, not entirely sure how to go about that. I recognise there’s a bluetooth-sma emonHub module, but I saw it was having problems with recent updates of emoncms and I wanted to use an ethernet connection instead of bluetooth.

EDIT: Redis issue posted to separate thread.

EDIT: Also, the UploadDaemon which is added to /etc/rc.local with lines:

#Start SBFspotUploadDaemon
sudo /usr/local/bin/sbfspot.3/SBFspotUploadDaemon

Fails to start upon reboot. The issue, is I’m fairly sure, is that the system is read-only upon reboot, and the Daemon is therefore unable to write to the log file I’ve created according to the SBFSpot setup guide. I believe I can change the log location. Can the Pi be started in write mode? Or can I give the daemon permisson?

The SBFSpot data is going to /home/pi/smadata/
Would the best thing to do to put the SBFSpot data in /home/pi/data/smadata/ ?

I think I’d originally loaded emonSD-13Jun18.img
Edit: The permissions error was resolved by pointing SBF to the read/write ~/data/ folder, however future images of emonCMS will have the write lock function removed, the problem shouldn’t exist after the next release.


(Paul) #2

No need to change the logfile location, if you look closer at that rc.local file, there is already a “fix” (in the loosest possible terms) for several other softwares that fail to start in the manner they were intended, you can add the program to those already there. Be aware though, that any changes made to rc.local will block future updates via git (emonpi update) and will need to be stashed before and reapplied after running an update.

Redis-server is used by emoncms, I have never seen it consume as much resource as that, but you are running a “beta” image that hasn’t been widely tested yet.


(Daniel Bates) #3

I noticed the other lines in rc.local relating to WiFI. Which I’ve already commented out… I’ll have a closer look…
Regarding the beta-image, understood :slight_smile:
Any idea how I can debug the redis-server? Find out what it’s trying to do?


(Daniel Bates) #4

For reference, this is what the original rc.local looked like.


(Daniel Bates) #5

SBFspot.db
SBFspot.db.zip (8.2 KB)

Here’s the database, a few entries have been made.
@pb66 Any thoughts on how to read SQLite databases with python? I’ll get started with this tutorial: http://www.sqlitetutorial.net/sqlite-python/sqlite-python-select/
I’ve sent a message to the developer to see if he can give a pointer for reading the most recent power value from the database.


(Daniel Bates) #6

If you’re reading each post as quickly, I’m glad you’re not wasting your time replying :slight_smile:

Using DB Browser for SQLite I’ve found the table names.

Getting there:
#!/usr/bin/python

import sqlite3
from sqlite3 import Error

def select_all_tasks(conn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM SpotData")
 
    rows = cur.fetchall()
 
    for row in rows:
        print(row)

def main():
    database = "SBFspot.db"

    # create a database connection
    conn = create_connection(database)
    with conn:
      
        print(". Query all data within table: SpotData")
        select_all_tasks(conn)

 
if __name__ == '__main__':
    main()

prints:

. Query all data within table: SpotData    
(1534325149, 304991661, 1154, 1390, 5.033, 5.538, 229.46, 251.13, 828, 833, 831, 3.509, 3.508, 3.507, 236.01, 237.54, 237.17, 4437, 13844060, 49.99, 12077.6, 11648.3, 0.0, u'OK', u'Closed', 72.53)
(1534326202, 304991661, 927, 1104, 4.038, 4.443, 229.81, 248.57, 667, 660, 670, 2.824, 2.822, 2.818, 236.45, 233.8, 237.84, 5115, 13844737, 49.99, 12077.9, 11648.6, 0.0, u'OK', u'Closed', 72.93)

and so on! …

Very exciting.


(Trystan Lea) #7

Nice work @danbates! Great to hear about uploading to PVoutput! As Paul said redis is used by emoncms (an important component in increasing performance and reducing disk writes/extending SD card life).
Redis is using 1.3-3.6% on one of our Pi’s here, not sure what is causing your high activity, we are working here on a new Pi3B+ image that rebuilds from the ground up which seems to be giving stable WIFI access point performance which was the main issue with the one your using (If your using our beta image).


(Daniel Bates) #8

Sounds great. I’m up for reinstalling everything when the new image is ready. I’ve made a number of changes. Blacklisting the wifi drivers… countless packages have been installed… the next install I make with the new image I’ll be able to check the CPU usage after each package install.


(Daniel Bates) #9

@TrystanLea slight change of subject: in emonCMS I noticed there’s no clear way to disable the WiFi. Will there be a clear option to disable the WiFi in the new image? Or will an option be useless if the APscan has not been initiated? i.e. the wifi will, in effect, not transmit until called upon by the user?


(Paul) #10

Not sure if you’ve noticed but there is a special thanks to @stuart in that repo’s readme as it is apparently based on the code written by Stuart some years ago, more recently Stuart is also the author of the SMA bluetooth emonHub interfacer (among other things too).


(Daniel Bates) #11

There is! I did notice that and didn’t know what to do about it as I saw his work was more bluetooth focused.
So this goes full circle? This would be very cool :slight_smile:
I was thinking earlier about attributions and donation weblinks, they could be included in the eventual readme / script file / emonhub headers for sure. The question is, if a donation weblink is recommended, which one to include? For @stuart and/or SBFSpot project?
This has been an easy process so far thanks to the SBFspot install guide, and also SBF is built on @stuart 's work, therefore a suggested 50/50 donation to each?
What do you think @stuart ?


(Trystan Lea) #12

The access point only starts if the Pi is not connected to ethernet and has no WIFI client configuration. So in that sense the interface is ‘down’ unless configured to be ‘up’, but I dont think that’s the same as actually turning the WIFI off to save power…?


(Daniel Bates) #13

That’s what I don’t know. Sounds like it doesn’t make any difference when the Ethernet cable’s connected then.


(stuart) #14

I found the spot tool a few months ago and noticed my name in it, happy for the code to be used as needed, never thought it would be so popular!!


(stuart) #15

@danbates the emonpi image also has MySQL included which I see is supported by spot - perhaps you could use that instead of SQLite (avoid yet another install on the sd card!)

A lot of people use PVOutput - perhaps we could write a wrapper for emonCMS to accept the same URL based data as PVOutput and then it would be as easy as pointing the upload to a new URL.


(Daniel Bates) #16

Caught this a bit late, got most of the way through writing a python script for reading SQlite and posting to emonCMS.
MySQL should be easy enough to set up. I’ll take a fresh look tomorrow.
…What is a ‘wrapper’? I think I know, but always best to ask!


(Daniel Bates) #17

(Paul) #18

Minor point I know, but it’s actually MariaDB on the beta June image. I’m pretty sure the commands will be interchangeable and both MySQL and MariaDB are supported, but it might make a difference somewhere along the way.

A wrapper is generally a piece of code that transforms the way another piece of code works by wrapping it in custom code rather than rewriting it or making changes to it.


(Daniel Bates) #19

Seems like at least several thousand systems.

I’m keen to learn the tricks :slight_smile:

I’ve got momentum with this database reader so will just carry on.

def print_pairs():
    rowsindex = 0
    for i in names:
        print(str(i) + ":" + str(rows[0][rowsindex]))
        rowsindex = rowsindex + 1
    rowsindex = 0

14

I think I next need to use some kind of stringing tool, to create the json URL.


(stuart) #20

There is bound to be a library for creating JSON from code, use that instead of banging strings of text together