ESP8266 Publishing DHT22 Readings to SQLite Database

In this project you’ll create a standalone web server with a Raspberry Pi that displays temperature and humidity readings with a DHT22 sensor that are stored in an SQLite database.

In order to create the web server you will be using a Python microframework called Flask. Here’s the high level overview of the system:

Recommended resources:

Basic Raspberry Pi Setup

Before you continue reading this project, please make sure you have Raspbian Operating System installed in your Raspberry Pi.

You can read my Getting Started with the Raspberry Pi Guide to install Raspbian and complete the basic setup.

Run and install Mosquitto broker

The Raspberry Pi is going to interact with the ESP8266 with the MQTT protocol. Having Mosquitto broker installed, you need to have Mosquitto broker running on the background:

pi@raspberry:~ $ mosquitto -d

Installing Flask

We’re going to use a Python microframework called Flask to turn the Raspberry Pi into web server.

To install Flask, you’ll need to have pip installed. Run the following commands to update your Pi and install pip:

pi@raspberrypi ~ $ sudo apt-get update
pi@raspberrypi ~ $ sudo apt-get upgrade
pi@raspberrypi ~ $ sudo apt-get install python-pip python-flask git-core

Then, you use pip to install Flask:

pi@raspberrypi ~ $ sudo pip install flask

Creating the Python Script

This is the core script of our application. It sets up the web server, receives the temperature/humidity readings and saves those sensor readings in an SQLite database.

To keep everything organized, start by creating a new folder:

pi@raspberrypi ~ $ mkdir web-server
pi@raspberrypi ~ $ cd web-server
pi@raspberrypi:~/web-server $

Create a new file called app.py.

pi@raspberrypi:~/web-server $ nano app.py

Copy and paste the following script to your Raspberry Pi

#
# Created by Gnd_To_Vcc


import paho.mqtt.client as mqtt
from flask import Flask, render_template, request
import json
import sqlite3

app = Flask(__name__)

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

# 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))
    # Subscribing in on_connect() means that if we lose the connection and
    # reconnect then subscriptions will be renewed.
    client.subscribe("/esp8266/dhtreadings")

# The callback for when a PUBLISH message is received from the ESP8266.
def on_message(client, userdata, message):
    if message.topic == "/esp8266/dhtreadings":
        print("DHT readings update")
        #print(message.payload.json())
        #print(dhtreadings_json['temperature'])
        #print(dhtreadings_json['humidity'])

        dhtreadings_json = json.loads(message.payload)

        # connects to SQLite database. File is named "sensordata.db" without the quotes
        # WARNING: your database file should be in the same directory of the app.py file or have the correct path
        conn=sqlite3.connect('sensordata.db')
        c=conn.cursor()

        c.execute("""INSERT INTO dhtreadings (temperature,
            humidity, currentdate, currentime, device) VALUES((?), (?), date('now'),
            time('now'), (?))""", (dhtreadings_json['temperature'],
            dhtreadings_json['humidity'], 'esp8266') )

        conn.commit()
        conn.close()

mqttc=mqtt.Client()
mqttc.on_connect = on_connect
mqttc.on_message = on_message
mqttc.connect("localhost",1883,60)
mqttc.loop_start()

@app.route("/")
def main():
   # connects to SQLite database. File is named "sensordata.db" without the quotes
   # WARNING: your database file should be in the same directory of the app.py file or have the correct path
   conn=sqlite3.connect('sensordata.db')
   conn.row_factory = dict_factory
   c=conn.cursor()
   c.execute("SELECT * FROM dhtreadings ORDER BY id DESC LIMIT 20")
   readings = c.fetchall()
   #print(readings)
   return render_template('main.html', readings=readings)

if __name__ == "__main__":
   app.run(host='0.0.0.0', port=8181, debug=True)

Preparing Your SQLite File

Follow this next tutorial to learn how to Install SQLite database on a Raspberry Pi and prepare the database. Having an SQLite database file that has the following schema:

sqlite> .fullschema
CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT);

Your SQLite database file should be named “sensordata.db” without the quotes.

WARNING: your database file should be in the same directory of the app.py file or have the correct path in your Python app.py file created in a preceding section (section conn=sqlite3.connect(‘sensordata.db’)).

Creating the HTML File

Keeping HTML tags separated from your Python script is how you keep your project organized.Flask uses a template engine called Jinja2 that you can use to send dynamic data from your Python script to your HTML file.

Create a new folder called templates:

pi@raspberrypi:~/web-server $ mkdir templates
pi@raspberrypi:~/web-server $ cd templates
pi@raspberrypi:~/web-server/templates $

Create a new file called main.html.

pi@raspberrypi:~/web-server/templates $ nano main.html

Copy and paste the following template to your Pi:

<!DOCTYPE html>
<head>
   <title>RPi Web Server</title>
   <!-- Latest compiled and minified CSS -->
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
   <!-- Optional theme -->
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap-theme.min.css" integrity="sha384-fLW2N01lMqjakBkx3l/M9EahuwpSfeNvV63J5ezn3uZzapT0u7EYsXMjQV+0En5r" crossorigin="anonymous">
   <!-- Latest compiled and minified JavaScript -->
   <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" integrity="sha384-0mSbJDEHialfmuBBQP6A4Qrprq5OVfW37PRR3j5ELqxss1yVqOtnepnHVP9aJ7xS" crossorigin="anonymous"></script>
   <script src="https://code.jquery.com/jquery-3.1.1.min.js" integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8=" crossorigin="anonymous"></script>
   <meta name="viewport" content="width=device-width, initial-scale=1">
</head>

<body>
   <h1>RPi Web Server - ESP8266 SQLite Data</h1>
   <table class="table table-hover">
     <tr><th>ID</th>
     <th>Temperature</th>
     <th>Humidity</th>
     <th>Date</th>
     <th>Time</th>
     <th>Device</th></tr>
     {% for entry in readings %}
        <tr><td>{{ entry.id }}</td>
        <td>{{ entry.temperature }}</td>
        <td>{{ entry.humidity }}</td>
        <td>{{ entry.currentdate }}</td>
        <td>{{ entry.currentime }}</td>
        <td>{{ entry.device }}</td></tr>
     {% endfor %}
   </table>
</body>
</html>

Programming the ESP8266

For the ESP8266 to interact with the Raspberry Pi web server, you need to install PubSubClient library. This library provides a client for doing simple publish/subscribe messaging with a server that supports MQTT (basically allows your ESP8266 to talk with Python web server).

Installing the PubSubClient library

1) Click here to download the PubSubClient library. You should have a .zip folder in your Downloads folder

2) Unzip the .zip folder and you should get pubsubclient-master folder

3) Rename your folder from pubsubclient-master to pubsubclient

4) Move the pubsubclient folder to your Arduino IDE installation libraries folder

The library comes with a number of example sketches. See File > Examples > PubSubClient within the Arduino IDE software.

Installing the DHT sensor library

The DHT sensor library provides an easy way of using any DHT sensor to read temperature and humidity with your ESP8266 or Arduino boards.

1) Click here to download the DHT sensor library. You should have a .zip folder in your Downloads folder

2) Unzip the .zip folder and you should get DHT-sensor-library-master folder

3) Rename your folder from DHT-sensor-library-master to DHT

4) Move the DHT folder to your Arduino IDE installation libraries folder

5) Then, re-open your Arduino IDE

Uploading sketch

Finally, you can upload the full sketch to your ESP8266 (replace with your SSID, password and RPi IP address):

<pre class="wp-block-syntaxhighlighter-code">/*****
 
 All the resources for this project:
 <blockquote class="wp-embedded-content" data-secret="Vohe2hEZIm"><a href="https://rntlab.com/">(dup) Homepage</a></blockquote><iframe class="wp-embedded-content" sandbox="allow-scripts" security="restricted" style="position: absolute; clip: rect(1px, 1px, 1px, 1px);" title="&#8220;(dup) Homepage&#8221; &#8212; RNTLab.com" src="https://rntlab.com/embed/#?secret=Vohe2hEZIm" data-secret="Vohe2hEZIm" width="600" height="338" frameborder="0" marginwidth="0" marginheight="0" scrolling="no"></iframe>
 
*****/

// Loading the ESP8266WiFi library and the PubSubClient library
#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include "DHT.h"

// Uncomment one of the lines bellow for whatever DHT sensor type you're using!
//#define DHTTYPE DHT11   // DHT 11
//#define DHTTYPE DHT21   // DHT 21 (AM2301)
#define DHTTYPE DHT22   // DHT 22  (AM2302), AM2321

// Change the credentials below, so your ESP8266 connects to your router
const char* ssid = "YOUR_SSID";
const char* password = "YOUR_PASSWORD";

// Change the variable to your Raspberry Pi IP address, so it connects to your MQTT broker
const char* mqtt_server = "YOUR_RPi_IP_Address";

// Initializes the espClient
WiFiClient espClient;
PubSubClient client(espClient);

// DHT Sensor
const int DHTPin = 14;

// Initialize DHT sensor.
DHT dht(DHTPin, DHTTYPE);

// Timers auxiliar variables
long now = millis();
long lastMeasure = 0;

char data[80];

// Don't change the function below. This functions connects your ESP8266 to your router
void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.print("WiFi connected - ESP IP address: ");
  Serial.println(WiFi.localIP());
}

// This functions is executed when some device publishes a message to a topic that your ESP8266 is subscribed to
// Change the function below to add logic to your program, so when a device publishes a message to a topic that 
// your ESP8266 is subscribed you can actually do something
void callback(String topic, byte* message, unsigned int length) {
  Serial.print("Message arrived on topic: ");
  Serial.print(topic);
  Serial.print(". Message: ");
  String messageTemp;
  
  for (int i = 0; i < length; i++) {
    Serial.print((char)message[i]);
    messageTemp += (char)message[i];
  }
  Serial.println();
}

// This functions reconnects your ESP8266 to your MQTT broker
// Change the function below if you want to subscribe to more topics with your ESP8266 
void reconnect() {
  // Loop until we're reconnected
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Attempt to connect
     /*
     YOU  NEED TO CHANGE THIS NEXT LINE, IF YOU'RE HAVING PROBLEMS WITH MQTT MULTIPLE CONNECTIONS
     To change the ESP device ID, you will have to give a unique name to the ESP8266.
     Here's how it looks like now:
       if (client.connect("ESP8266Client")) {
     If you want more devices connected to the MQTT broker, you can do it like this:
       if (client.connect("ESPOffice")) {
     Then, for the other ESP:
       if (client.connect("ESPGarage")) {
      That should solve your MQTT multiple connections problem

     THE SECTION IN loop() function should match your device name
    */
    if (client.connect("ESP8266Client")) {
      Serial.println("connected");  
      // Subscribe or resubscribe to a topic
      // You can subscribe to more topics (to control more LEDs in this example)
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      // Wait 5 seconds before retrying
      delay(5000);
    }
  }
}

// The setup function sets your DHT sensor, starts the serial communication at a baud rate of 115200
// Sets your mqtt broker and sets the callback function
// The callback function is what receives messages and actually controls the LEDs
void setup() {
  dht.begin();
  Serial.begin(115200);
  setup_wifi();
  client.setServer(mqtt_server, 1883);
  client.setCallback(callback);
}

// For this project, you don't need to change anything in the loop function. 
// Basically it ensures that you ESP is connected to your broker
void loop() {
  if (!client.connected()) {
    reconnect();
  }
  if(!client.loop())
     /*
     YOU  NEED TO CHANGE THIS NEXT LINE, IF YOU'RE HAVING PROBLEMS WITH MQTT MULTIPLE CONNECTIONS
     To change the ESP device ID, you will have to give a unique name to the ESP8266.
     Here's how it looks like now:
       client.connect("ESP8266Client");
     If you want more devices connected to the MQTT broker, you can do it like this:
       client.connect("ESPOffice");
     Then, for the other ESP:
       client.connect("ESPGarage");
      That should solve your MQTT multiple connections problem

     THE SECTION IN recionnect() function should match your device name
    */
    client.connect("ESP8266Client");
    
  now = millis();
  // Publishes new temperature and humidity every 30 seconds
  if (now - lastMeasure > 10000) {
    lastMeasure = now;
    // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
    float h = dht.readHumidity();
    // Read temperature as Celsius (the default)
    float t = dht.readTemperature();
    // Read temperature as Fahrenheit (isFahrenheit = true)
    float f = dht.readTemperature(true);

    // Check if any reads failed and exit early (to try again).
    if (isnan(h) || isnan(t) || isnan(f)) {
      Serial.println("Failed to read from DHT sensor!");
      return;
    }

    // Computes temperature values in Celsius
    float hic = dht.computeHeatIndex(t, h, false);
    static char temperatureTemp[7];
    dtostrf(hic, 6, 2, temperatureTemp);
    
    // Uncomment to compute temperature values in Fahrenheit 
    // float hif = dht.computeHeatIndex(f, h);
    // static char temperatureTemp[7];
    // dtostrf(hic, 6, 2, temperatureTemp);
    
    static char humidityTemp[7];
    dtostrf(h, 6, 2, humidityTemp);
    
    String dhtReadings = "{ \"temperature\": \"" + String(temperatureTemp) + "\", \"humidity\" : \"" + String(humidityTemp) + "\"}";
    dhtReadings.toCharArray(data, (dhtReadings.length() + 1));
    
    // Publishes Temperature and Humidity values
    client.publish("/esp8266/dhtreadings", data);
    Serial.println(data);
    Serial.print("Humidity: ");
    Serial.print(h);
    Serial.print(" %\t Temperature: ");
    Serial.print(t);
    Serial.print(" *C ");
    Serial.print(f);
    Serial.print(" *F\t Heat index: ");
    Serial.print(hic);
    Serial.println(" *C ");
    // Serial.print(hif);
    // Serial.println(" *F");
  }
}
</pre>

Schematics

To complete this project you need these next components

  • 1x ESP8266 12E – read Best ESP8266 Wi-Fi Development Boards
  • 1x DHT22 Sensor 
  • 1x 4700 Ohm Resistor
  • Jumper wires
  • Breadboard

Note: other DHT sensor types will also work with a small change in the code.

Here’s the schematics:

Important: the DHT sensor requires 5V to operate properly, so make sure you use the Vin pin from your ESP8266 that outputs 5V.

Launching the Web Server

To launch your Raspberry Pi web server move to the folder that contains the file app.py:

pi@raspberrypi:~/web-server/templates $ cd ..

Then, run the following command:

pi@raspberrypi:~/web-server $ sudo python app.py

Your web server should start immediately on port :8181!

Demonstration

Open your Raspberry Pi address in your browser by entering its IP address, in my case: http://192.168.1.98:8181

Note: you must enter your IP address followed by :8181

Wrapping up

That’s it for now, I hope you can take these examples and build more features that fit your needs.

Thanks for reading.

Published by Gnd_To_Vcc

Here to spread my knowledge . Knowledge should always be spread not stored.

4 thoughts on “ESP8266 Publishing DHT22 Readings to SQLite Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website with WordPress.com
Get started
%d bloggers like this: