Weather Station: ESP8266 Deepsleep, SQL and Graphing

Save Data to SQL and Display Data

I used sqlite3 to create a database for the web server. Install the sqlite3 in Rapberry Pi by:

sudo apt-get install sqlite3

Create a database and a table by typing into the terminal:

sqlite3 weatherstation.db
CREATE TABLE weatherdata (id INT PRIMARY KEY, thetime DATETIME, ldr INT, tls2561 INT, ds18b20 REAL, tsht21 REAL, hsht21 REAL);
.exit //to exit the sqlite command line and return to Linux terminal

To listen to a topic published by the weather station, I used a Paho library with Python. Install the libarry first:

Terminal
pip install paho-mqtt

and create a script to listen to the topic published by the ESP8266:

#! /usr/bin/python3
# adopted from: sqlwriter.py
# Binh Nguyen, August 04, 2018,
from time import localtime, strftime, sleep
import paho.mqtt.client as mqtt
import sqlite3, json
mqtt_topic = 'balcony/weatherstation'
mqtt_username = "johndoe"
mqtt_password = "password"
dbFile = "/path/to/databse/weatherstation.db"
mqtt_broker_ip = '192.168.1.50'
# The callback for when the client receives a CONNACK response from the server.
def on_connect(client, userdata, flags, rc):
    print("Connected with result code "+str(rc))
    client.subscribe(mqtt_topic)
    
# The callback for when a PUBLISH message is received from the server.
def on_message(client, userdata, msg):
    theTime = strftime("%Y-%m-%d %H:%M:%S", localtime())
    topic = msg.topic
    payload = json.loads(msg.payload.decode('utf-8'))
    sql_cmd = """INSERT INTO weatherdata VALUES ({0}, '{1}',\
        {2[ldr]}, {2[tsl2561]},{2[ds18b20]}, {2[tsht21]},{2[hsht21]})""".format(None, time_, payload)
    writeToDB(sql_cmd)
    print(sql_cmd)
    return None

def writeToDb(sql_cmd):
    conn = sqlite3.connect(dbFile)
    cur = conn.cursor()
    cur.execute(sql_command)
    conn.commit()

client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.username_pw_set(username=mqtt_username, password=mqtt_password)
client.connect(mqtt_broker_ip, 1883, 60)
sleep(1)
client.loop_forever()

To display data from use another SQL command to query data from the database such as:

sql_command = """ SELECT * from  weatherdata
                    ORDER BY thetime DESC
                    LIMIT 1000;"""

This SQL command is included in the app.py that uses Flask framework and Plotty to make a web server and plotting a graph.

The complete code is hosted on the GitHub.

The Flask - A Python Lightweight Web Application can be installed by:

pip install flask

If the ESP8266 cannot read the DS18B20, it assigned a value of -127 as the temperature which skews the relative range of other readable temperatures. I cleaned up those values by set a NULL value to those equals to -127:

sqlite3 weatherstation.db
sqlite3> UPDATE weatherdata SET ds18b20 = NULL WHERE ds18b20 = -127;

To set up an environment for this mini web server, I used the shared libraries on Raspberry Pi. A virtualenv is a better option if the web server is hosted on a powerful computer. Start the web server by:

python3 app.py

Press Control + C to stop the server.

The web server is set to auto-refreshed for every 60 seconds. You can change the interval in index.html file:

<meta http-equiv='refresh' content='60'>

Hopefully, the final version will look like this:

Screenshot
Screenshot of weather station.

This tutorial is pretty much to get the my curent weather station running on website in the main home page.

Battery performance:

I did not measure the current between the normal state or sleep state of ESP8266. Many others did so. The first google search turned to . The normal state of ESP8266 consumes about 100mA depends on the rate of transmitting and wifi activity. The deep-sleep state needs in the range of micro A, which a thousand times less.

For 5-minute interval between sleeping and waking up, one single Lithium 18650 (2000mAh) could fuel my weather station for 12 days. The same battery only enough for ESP 8266 ran less than a day with a normal working state. The one I took from the camera battery pack (did not know the capacity) was enough to run the weather station with deep sleep for 5-6 days.

Thank you for spending time with me to this end.

▣ ▣ ▣