I’m getting my head around the collecting and visualizing of power readings.  As in, it’s not hard to collect a lot of power readings.  But what should be displayed and how should it be displayed?  This post is about coming up with a self contained, easy to use data collecting and visualization software that moves my thoughts farther in the direction of what concerns I need to think about before I figure out the best way to build an awesome experience.  I find it is first best to try stuff out.  This way I gain knowledge on what’s the best way to build what I want to deploy to others.  In my previous attempt at building a prototype back end for collecting and visualizing energy data, I used AWS Services. 


Bumbling through the AWS services to do what I want felt like I was wading through something that is gooey, muddy, and slimy.  In fact, I bumbled around so successfully that apparently in just one day I had blown through my free level and started to get charged.  Amazon’s charging capability – seemingly down to the micro transaction – is a true testimonial in their ability to suck up every penny on the table.  Here’s part of a mail I got from AWS billing when I asked about charges: Although your account is eligible for the Free Tier, your usage charges were based on activity outside the specifications of the offer. Please note that the AWS Free Tier provides up to 750 hours per month of a single-AZ t2.micro.elasticsearch or t2.small.elasticsearch instance and 10GB per month of optional Amazon EBS storage (Magnetic or General Purpose), while you were exceeding the limitation of hours.  Hmmm…..I just want to collect energy data and draw a line chart here, a bar chart there….



So I changed direction on technologies to use for collecting and visualizing energy data:

In this prototype exploration, I  use a Raspberry Pi 3 to collect readings coming in from the energy monitor using mqtt.  The Raspberry Pi will also act as a web server to display visualizations of the energy data.  I’ll use Python as the programming language and hook together:

  • mosquitto – An mqtt Broker.
  • peewee – Makes database inserts/queries easy.
  • Dash – Puts together “the best of” Flask and plotly to make web pages with data visualization.

The Goal

The goal of this post is to build what I call a “sacrificial draft” of the Collect and Visualize component of the HappyDay Neighborhood project.  I will capture mqtt messages from a Monitor reading the current used by a lamp as I turn the lamp off and on.  This is a simple prototype that will give me an idea how all the “goo” fits together.

Step1:Install the Raspbian OS

Ooh – Raspberry Pi…I feel like I get to play with the cool kids.  I haven’t played around with one.I’ll use a Raspberry Pi as the “command device.”  It will receive mqtt messages from energy monitors and store them into a database.  My browser will access a web page on the Raspberry Pi to look at the power readings.  I got a Raspberry Pi in the mail.  Next I need to install an OS on it.  


I have a natural instinct at this point to start installs at the Adafruit Learning Center.  I wasn’t disappointed.  I found and followed along.  I decided to give Raspbian Stretch Lite a twirl.  I made an image…and then…hmmm…how do I work on a headless Raspberry Pi? I don’t want to use a separate monitor and keyboard…but still the Raspberry Pi wouldn’t boot.  The challenge was the original format of the SD card.  I bought the cheapest SD micro I could find on Amazon.  I didn’t format prior to using Etcher to burning the Raspbian image.  What worked for me was first formatting the card with SDFormatter (overwrite).

Step 2: Read/Write Files to the Raspberry Pi

I am not fluent with “Unixy” copying and saving files between a Raspberry Pi and a Mac.  I wanted to use my Mac editors on files stored on the Raspberry Pi.  The way to do this is by extending the Mac file system “goo” to include the Raspberry Pi’s drive.

To be able to drag/drop/open files on the Raspberry Pi from within my Mac’s Finder, I:

    • Installed SSHFS from here.
    • Created a directory named ‘mount’ within my home directory.  I.e.: in my case this would be /Users/margaret/mount .
    • Opened a Terminal window and ran the command:
sshfs pi@raspberrypi.home: /Users/margaret/mount

Where the IP address is the unique IP address of the Raspberry Pi within my internal network.

Note: The ssh connection will break when the connection is lost – say because the Raspberry Pi or the Mac was rebooted.  So the sshfs command will be run quite often.

Now the Raspberry Pi’s storage appears as a disk within the Mac’s Finder.

Pasted Image 10 9 17 3 32 AM

pi@raspberrypi:~ $ pwd


Looks like the directory on the Raspberry Pi associated with the mount folder on my mac is /home/pi.

Step 3: Install Mosquitto

Moving right along, I need an mqtt broker on the Raspberry Pi to grab the energy monitor mqtt messages.  For this I’ll use mosquitto.  Mosquitto seems to be THE mqtt software to use on the Raspberry Pi.

Run the following on the Raspberry Pi’s command line:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get dist-upgrade
sudo apt-get install mosquitto mosquitto-clients
pip install paho-mqtt 

Step 4: Test mqtt

OK.  Mosquitto is installed…but does it work?  I tried a few different scenarios to check this out.

Test 1 – command line

There are two command line utilities, mosquitto_pub and and mosquitto_sub.  As with all the other Linux commands I’ve used – get help on each by typing

~ $ mosquitto_pub –help

  • Open a terminal window, logon to the Raspberry PI and type the command:

 $ mosquitto_sub -t test/test1  -d

I.e.: subscribe to mqtt messages brokered on localhost sent to the test/test1 topic.  Debugging info (-d):

Client mosqsub/17589-raspberry sending CONNECT

Client mosqsub/17589-raspberry received CONNACK

Client mosqsub/17589-raspberry sending SUBSCRIBE (Mid: 1, Topic: test/test1, QoS: 0)

Client mosqsub/17589-raspberry received SUBACK

Subscribed (mid: 1): 0

  • Open a second terminal window, logon to the Raspberry Pi and type the command:

$ mosquitto_pub -t test/test1 -m “test message” -d

Client mosqpub/17627-raspberry sending CONNECT

Client mosqpub/17627-raspberry received CONNACK

Client mosqpub/17627-raspberry sending PUBLISH (d0, q0, r0, m1, ‘test/test1’, … (12 bytes))

Client mosqpub/17627-raspberry sending DISCONNECT

The terminal that was running mosquitto_sub received an mqtt message:

Client mosqsub/17589-raspberry received PUBLISH (d0, q0, r0, m0, ‘test/test1’, … (12 bytes))


test message

Test 2 – python


As an aside, I just checked /usr/bin on my Raspberry Pi and did ls -l python* :

lrwxrwxrwx 1 root root 9 Jan 24 2017 /usr/bin/python -> python2.7
lrwxrwxrwx 1 root root 9 Jan 24 2017 /usr/bin/python2 -> python2.7
-rwxr-xr-x 1 root root 3162224 Jan 19 2017 /usr/bin/python2.7
lrwxrwxrwx 1 root root 36 Jan 19 2017 /usr/bin/python2.7-config -> arm-linux-gnueabihf-python2.7-config
lrwxrwxrwx 1 root root 16 Jan 24 2017 /usr/bin/python2-config -> python2.7-config
lrwxrwxrwx 1 root root 9 Jan 20 2017 /usr/bin/python3 -> python3.5
-rwxr-xr-x 2 root root 3976264 Jan 19 2017 /usr/bin/python3.5
-rwxr-xr-x 2 root root 3976264 Jan 19 2017 /usr/bin/python3.5m
lrwxrwxrwx 1 root root 10 Jan 20 2017 /usr/bin/python3m -> python3.5m
lrwxrwxrwx 1 root root 16 Jan 24 2017 /usr/bin/python-config -> python2.7-config

Jeepers! I had no idea so many versions of Python were installed. For now, I defaulted to using python2.7


I tested out running mosquitto commands in python using the script on this page.  Here are some of the results I was getting:

$SYS/broker/connection/Usuario-PC.bridgeConnection/state 1

$SYS/broker/connection/Lucas-PC.bridgeConnection/state 1

$SYS/broker/connection/Servidor-PC.bridgeConnection/state 1

$SYS/broker/connection/caixa02.bridgeConnection/state 1

after awhile I got this error:

  File “mqtt_test.py”, line 25, in <module>




  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 1481, in loop_forever


    rc = self.loop(timeout, max_packets)


  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 1003, in loop


    rc = self.loop_read(max_packets)


  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 1284, in loop_read


    rc = self._packet_read()


  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 1849, in _packet_read


    rc = self._packet_handle()


  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 2305, in _packet_handle


    return self._handle_publish()


  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 2500, in _handle_publish




  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 2647, in _handle_on_message


    self.on_message(self, self._userdata, message)


  File “mqtt_test.py”, line 13, in on_message


    print(msg.topic+” “+str(msg.payload))


  File “/home/pi/.local/lib/python2.7/site-packages/paho/mqtt/client.py”, line 360, in topic


    return self._topic.decode(‘utf-8’)


  File “/usr/lib/python2.7/encodings/utf_8.py”, line 16, in decode


    return codecs.utf_8_decode(input, errors, True)


UnicodeDecodeError: ‘utf8’ codec can’t decode byte 0xe7 in position 31: invalid continuation byte

I’m thinking there are some characters in the topic name that can’t be parsed.  For now I’ll ignore and consider the test successful.

Test 3: Mac to Raspberry Pi Test

Can I publish an mqtt message on my Mac and have the Raspberry Pi get it? Easy-Peasy!

import paho.mqtt.publish as publish
# Based on this example: https://github.com/eclipse/paho.mqtt.python/blob/master/examples/publish_single.py
publish.single(“test/topic”, “Hello Pi”, hostname=”″)

Whoa.  That was too easy!

Step 5: Store Incoming Energy Messages

Store mqtt messages into a database on the Raspberry Pi.  FINALLY some fun, yah?  According to this post which I’ll take as fact until I learn better: SQLite is built into Python but is only built for access by a single connection at a time.  For now, I’ll use the built in SQLite.  When I scale to include other houses, I’ll have to change to a different database.  I should have a better feel with what data to store, how to store it, and what visualizations work the best.

The other piece of software I’ll  use for database access is the peewee ORM.  You might want to take a read at the material at this link –  if you are new to using the peewee ORM.  I chose to use the peewee ORM because of it’s simplicity.  

Install peewee

$ pip install peewee

Create the EnergyData database

For this step, I wrote EnergyReadingModel.py.  It contains the definition of the power and time variables and creates the EnergyReading.db file. I used Atom as my editor and saved the file to the EnergyDB directory I created (within the mounted directory).

~/EnergyDB $ python EnergyReadingModel.py

Created the EnergyReading.db file. This will be the database where the Energy readings will be stored.

Send Power Readings

The Feather Huzzah with the ATM90 FeatherWing is running the SendEnergyReadingsUsingMQTT.ino Arduino code.  For this part, I needed an mqtt library for the Arduino.  I am using what seems to be the most popular –  pubsubclient.

SendEnergyReadingsUsingMQTT.ino – 

  • Reads the power, current, power factor, and voltage.
  • Sends the energy Readings as an mqtt message formatted in JSON.  I ended up using a very simple name/value format.
    • {“sysStatus”:2,”voltage”:124.5,”current”:10.5,”power”:1278,”pf”:0.75}
    •  I used an online JSON parser to understand how JSON strings are parsed…

I use a 100 byte buffer to store the message.  This is lazy of me – what if the message is longer than 100 bytes?  I decided on 100 bytes after calculating the number of bytes in the above JSON string using this letter counting calculator:


Since this sample was 67 characters, I assume the JSON string will never be longer than 100 bytes.

Add Power Readings to the Energy DB 

I run the addPowerReadingToEnergyDB.py python app on the Raspberry Pi.  addPowerReadingToEnergyDB.py subscribes to the EnergyReading/# topic.  When it receives a message, it puts it into the energy db.

Note: while writing the python app, I learned about timestamps…which made sense to me to use as the time the energy reading was created.  I did it this way because the Raspberry Pi syncs its clock with ntp.  This means I can use the clock on the Raspberry Pi and not add an additional complexity of a RTC to the Energy monitor. 

>>> import time

>>> ts = time.time()

>>> print(ts)


>>> import datetime

>>> st = datetime.datetime.fromtimestamp(ts).strftime(‘%Y-%m-%d %H:%M:%S’)

Run As a Service

I want to capture readings on the Raspberry Pi 7 x 24.  In order to do this, I need to set up the Python script to run as a background service.  I found Matt’s post – How To Autorun A Python Script On Boot Using systemd to be very helpful.

Here’s the content of the systemd (addMessageToEnergyDB.service) file I’m using:


Description=Add Energy Record to EnergyDB.






ExecStart=/usr/bin/python /home/pi/EnergyDB/addMessageToEnergyDB.py




Executing the systemctl status command on the service gives me an indication readings are being received and entered into the EnergyDB:

 $ sudo systemctl status  addMessageToEnergyDB.service

addMessageToEnergyDB.service – Write Energy Record to the CSV file.

   Loaded: loaded (/lib/systemd/system/addMessageToEnergyDB.service; enabled; vendor preset: enabled)

   Active: active (running) since Sun 2017-11-12 09:20:01 PST; 9min ago

 Main PID: 634 (python)

   CGroup: /system.slice/addMessageToEnergyDB.service

           └─634 /usr/bin/python /home/pi/EnergyDB/addMessageToEnergyDB.py


Nov 12 09:26:15 raspberrypi python[634]: (‘INSERT INTO “reading” (“addressID”, “v1”, “i1”, “p1”, “pf1”, “v2”, “i2”, “p2”, “pf2”, “time”) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’, [1, 123.92, 2.508, 284

Nov 12 09:26:36 raspberrypi python[634]: (‘INSERT INTO “reading” (“addressID”, “v1”, “i1”, “p1”, “pf1”, “v2”, “i2”, “p2”, “pf2”, “time”) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’, [1, 123.89, 2.593, 294

Step 6: Make an HTML5 Web Page

I originally started down a path of Flask + plotly.

Then I figured out Dash was a better choice for visualizing energy readings on a web page.  I found this video gave me a good feel why I decided on Dash.  Dash builds on top of Flask and plotly so I get all the great features of these frameworks with the added benefit of doing everything in Python.  I  installed Dash using the directions in Getting Started.  Here is an image of the plot of energy readings I got after taking energy readings for our microwave over time:

I wrote and ran the visualizeEnergy.py python code.  Whoa.  Pretty darn simple.  At least getting started is not hard.  I’ll find out how easy it is to do more complex visualizations soon enough! 🙂
if __name__ == __main__:

Since I’m running this on the Raspberry Pi, I set the host to instead of the default (localhost). 

That’s it for now 

My-oh-my it took me a bit of bumbling to get to this point.  The learning part was thrilling.  I’m sure I’ll have many changes to my plans as I continue.  Next, I want to run the python script that adds mqtt messages to the Energy DB as a background service that starts up on boot.  Also, for some reason after “some amount” of time, the Feather starts getting 0 for all the energy readings…hmmm…. I never expect stuff to “just work.” 



Thanks for reading this far.  Please find many things to smile about.