IOT ESP8266 Tutorial – Pi to ESP8266 via REST – Part 2
This is the second of a multi-part posting on building and analyzing a solar powered ESP8266. In the first posting we are showing how to connect up an ESP8266 to the SunAirPlus Solar Power Controller/Charger/Data Collection board and to a solar panel/battery and build a REST http interface on the ESP8266.
In part 2 we are over on the Raspberry Pi and are reading the data from the IOT SolarPowerESp8266 and putting the data in a MySQL database. Part 3 will be generating graphs and looking at the data coming into the Raspberry Pi from the ESP8266.
The purpose of this ESP8266 project is five fold:
- Demonstrate ESP8266 on Solar Power
- Measure EPS8266 power consumption dynamically
- Show how to use a REST interface to send to a database on a Raspberry Pi
- Display the data on the Raspberry Pi on graphs using MatPlotLib
- Adding a stepper motor to the solar panel to track the sun (next project) and measure the results versus non-tracking
Part 1 IOT ESP8266 Tutorial – Solar Power your ESP8266!
Part 2 IOT ESP8266 Tutorial – Pi to ESP8266 via REST
Part 3 IOT ESP8266 Tutorial – Displaying the data on the Raspberry Pi with MatPlotLib
We are using the Adafruit ESP8266 Huzzah breakout board for these postings.
The ESP8266
The ESP8266 is made by a privately held company in China called Espressif. They are a fabless semiconductor company that just came out of nowhere and shook up the whole industry. Now all the major players are working on inexpensive versions of an IOT chip with WiFi connectivity. And they are all struggling to make it as inexpensive as the ESP8266
The Adafruit ESP8266 Huzzah
The Adafruit ESP8266 Huzzah board is a great breakout for the ESP8266. It makes it much easier to use than the really cheap modules.
Most of the low cost modules are not breadboard friendly, don’t have an onboard 3.3V regulator or level shifting for signals. The Huzzah has all of those features. For more on the ESP8266 Huzzah board see this posting.
The ESP8266 Software
We are using the Arduino IDE for this project. See how to use the Arduino IDE with the ESP8266 in this posting.
The System Block Diagram
SolarPowerESP8266 is an IOT system built by SwitchDoc Labs for an upcoming article on building IOT devices. It consists of five major pieces:
- – ESP8266 Huzzah WiFi/CPU (programmed in Arduino IDE)
- – SunAirPlus – Solar Power Controller/Charger Data Collector
- – LiPo Battery
- – 6V 3.4W Solar Panel
- – Raspberry Pi as Data Logger / Analytics / Display
Installing MySQL / Apache / phpmyadmin
https://pimylifeup.com/raspberry-pi-mysql-phpmyadmin/
Setting up MySQL on your Raspberry PI
The first thing to do is to install all the software on your Raspberry Pi to support MySQL and the Python bindings for MySQL.
Follow this excellent tutorial on Pi My Life Up to set up MySQL and phpmyadmin.
Once you have MySQL up and running (and phpmyadmin – the MySQL admin tool of choice), do the following:
Start phpmyadminfrom a browser. To do this, go to the follow address in your browser. (Replace the IP with your IP. If you don’t have your IP number run the command hostname –I on your Raspberry Pi)
https://192.168.1.108/phpmyadmin
1. Add a database called “IOTSolarData”
2. Copy and paste the following SQL code to create the database columns for IOTSolarData into the SQL Tab and click on “Go”.
-- phpMyAdmin SQL Dump -- version 3.4.11.1deb2+deb7u2 -- https://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Dec 07, 2015 at 05:49 PM -- Server version: 5.5.46 -- PHP Version: 5.4.45-0+deb7u2 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `IOTSolarData` -- -- -------------------------------------------------------- -- -- Table structure for table `SolarPowerData` -- DROP TABLE IF EXISTS `SolarPowerData`; CREATE TABLE IF NOT EXISTS `SolarPowerData` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `type` varchar(20) NOT NULL, `deviceid` varchar(20) NOT NULL, `firmwareversion` varchar(20) NOT NULL, `heap_available` int(11) NOT NULL, `sample_timestamp` bigint(20) NOT NULL, `battery_load_voltage` float NOT NULL, `battery_current` float NOT NULL, `solarcell_load_voltage` float NOT NULL, `solarcell_current` float NOT NULL, `output_load_voltage` float NOT NULL, `output_current` float NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Now your database is ready to go.
Make sure you have noted the passwords you set up to access MySQL. You will need the password in the Python code.
Results
Below are the results from the IOTCollector.py Python code:
pi@LightSwarm ~/IOTCollector $ sudo python IOTColl*.py 2015-12-07 18:15:23: sampling SolarPowerESP8266 data 2 sample(s) read 2015-12-07 18:16:23: sampling SolarPowerESP8266 data 2 sample(s) read 2015-12-07 18:17:24: sampling SolarPowerESP8266 data 2 sample(s) read 2015-12-07 18:18:24: sampling SolarPowerESP8266 data 2 sample(s) read
Going to the phpmyadmin program on the Raspberry Pi, we can see the data building up inside of MySQL
Raspberry Pi Python Code for Building the Database
The Raspberry Pi Python code for building the on-going database of data from the SolarPowerESP8266 is pretty straight forward.
The latest software for this project is located on github at https://github.com/switchdoclabs/SDL_PI_IOTDataCollector.
There are two python files. The main Python file is “IOTCollector.py” and can be started by typing:
sudo python IOTCollector.py
IOTCollector.py is a simple program that periodically (currently set to every 60 seconds) reads the REST interface on the SolarPowerESP8266 and stores all of the data to the MySQL Database. Note that we have built this file to be able to read data from a number of different IOT devices that SwitchDoc Labs is in the process of building.
#
#
# IOT Data Collector
#
# SwitchDoc Labs
# December 2015
#
import httplib2 as http
import json
import time
import SolarPowerESP8266
try:
from urlparse import urlparse
except ImportError:
from urllib.parse import urlparse
# fetch the JSON data from the IOT device
def fetchJSONData(uri, path):
target = urlparse(uri+path)
method = 'GET'
body = ''
h = http.Http()
# If you need authentication some example:
#if auth:
# h.add_credentials(auth.user, auth.password)
response, content = h.request(
target.geturl(),
method,
body,
headers)
# assume that content is a json reply
# parse content with the json module
data = json.loads(content)
return data
headers = {
'Accept': 'application/json',
'Content-Type': 'application/json; charset=UTF-8'
}
# main program
uri = 'https://192.168.1.129'
path = '/'
while True:
data = fetchJSONData(uri, path)
if (data['name'] == "SolarPowerESP8266"):
SolarPowerESP8266.parseSolarPowerESP8266(data)
#print( data['id'], data['name'])
time.sleep(60.0)
The second file contains the parsing files for the JSON from the SolarPowerESP8266 REST interface. The formatting for the data is as follows:
The data string contains data, each packet of data delimited by a “|” character. The first value (38160) is the current value of the free heap (RAM available) on the ESP8266. It’s put there to help monitor memory leaks, which we were initially concerted about. However, the latest version of the Arduino IDE ESP8266 SDK is doing a lot better.
The next two packets are the data from SunAirPlus, formatted as follows:
<timestamp - milliseconds since ESP8266 boot >, <LIPO_Battery Bus Voltage(V)>, <LIPO_Battery Load Voltage(V)>, <LIPO_Battery Current(mA)>, <Solar Cell Bus Voltage(V)>, <Solar Cell Load Voltage(V)>, <Solar Cell Current(mA)>, <Output Bus Bus Voltage(V)>, <Output Bus Load Voltage(V)>, <Output Bus Current(mA)>
The important values are those that say “Load Voltage” and “Current”. Output Bus refers to the current going into the ESP8266.
The values in the string below are: 4.08V for the LiPo Battery, 3.53V for the Solar Cell and 4.98V for the Voltage supply to the ESP8266.
The REST respond below was taken with the Load Power Switch (SW1 – Turn off Voltage Booter) OFF. This makes the current values look funny as the ESP8266 is being powered through the connected FTDI cable.
{"RestDataString": "38160 | 4684697,4.08,4.08,0.00,3.53,3.53,0.00,4.98,4.98,-0.40 | 4685720,4.08,4.08,0.00,3.53,3.53,0.00,4.98,4.98,-0.40", "id": "1", "name": "SolarPowerESP8266", "connected": true}
The files are split up this way because additional parsing software supporting other IOT devices will be added later.
File: SolarPowerESP8266.py
#
# SolarPowerESP8266
#
# parsing and storage data
#
# SwitchDoc Labs
# December 2015
#
import time
import datetime
import MySQLdb as mdb
def parseSolarPowerESP8266(data):
type = data["name"]
deviceid = data["id"]
timestamp = data["variables"]["RestTimeStamp"]
dataString = data["variables"]["RestDataString"]
firmwareversion = data["variables"]["FirmwareVersion"]
ts = time.time()
receivedTimeStamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
print "%s: sampling SolarPowerESP8266 data" % receivedTimeStamp
#print "%s: type= %s deviceid=%s timestamp=%s dataString=%s" % (receivedTimeStamp, type, deviceid, timestamp, dataString)
con = mdb.connect('localhost', 'root', 'password', 'IOTSolarData');
# you must create a Cursor object. It will let
# you execute all the queries you need
cur = con.cursor()
# read all data out of JSON
samples = dataString.split("|")
if (len(samples) > 1):
# we have data
print "%i sample(s) read" % (len(samples) -1)
for i in range(len(samples)):
values = samples[i].split(',')
if (i == 0):
heap_available = int(samples[i])
else:
sample_timestamp = int(values[0])
battery_load_voltage = float(values[2])
battery_current = float(values[3])
solarcell_load_voltage = float(values[5])
solarcell_current = float(values[6])
output_load_voltage = float(values[8])
output_current = float(values[9])
query = 'INSERT INTO SolarPowerData(timestamp,type, deviceid, firmwareversion, heap_available, sample_timestamp, battery_load_voltage, battery_current, solarcell_load_voltage, solarcell_current, output_load_voltage, output_current) VALUES(UTC_TIMESTAMP(), "%s", "%s", "%s", %d, %d, %.3f, %.3f, %.3f, %.3f, %.3f, %.3f)' %( type, deviceid, firmwareversion, heap_available, sample_timestamp, battery_load_voltage, battery_current, solarcell_load_voltage, solarcell_current, output_load_voltage, output_current)
#print("query=%s" % query)
cur.execute(query)
con.commit()
Coming Next?
The next part of this tutorial will be on the Raspberry Pi. We will be showing how to build and display graphs on the Raspberry Pi using MatPlotLib.
The perfect way of displaying all this IOT data we have been gathering.
2 Trackbacks / Pingbacks
Comments are closed.