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:

If you like home automation and you want to build a complete home automation system, I recommend downloading my home automation course.

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 Rui Santos
# Complete project details: https://randomnerdtutorials.com
#

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)

View raw code

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>

View raw code

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):

/*****
 
 All the resources for this project:
 https://rntlab.com/
 
*****/

// 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");
  }
}

View raw code

Schematics

To complete this project you need these next components:

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

You can use the preceding links or go directly to MakerAdvisor.com/tools to find all the parts for your projects at the best price!

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.

Like home automation? Learn more about Node-RED, Raspberry Pi, ESP8266 and Arduino with my course: Build a Home Automation System for $100.

Do you have any questions? Leave a comment down below!

Thanks for reading. If you like this post probably you might like my next ones, so please support me by subscribing my blog.


Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »

Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »


Enjoyed this project? Stay updated by subscribing our weekly newsletter!

20 thoughts on “ESP8266 Publishing DHT22 Readings to SQLite Database”

  1. Just what i needed was trying to work out if i could do this and you give details thank you< you must be a mind reader 🙂

  2. Any chance you could do a similar tutorial using ESP8266 but using Node Red with MQTT and SQLite, to add to the ‘$100 Home Automation’ package?
    There is a node abailable for Node Red which should make things relatively simple ( flows.nodered.org/node/node-red-node-sqlite ) but, being very new to Node Red, MQTT and Raspberry Pi, I have no real Idea how to actually do anything useful with it.
    Thanks

  3. Hi ,Rui,

    great tutorial,
    works perfect. attention the app.py and the sensordata.db must be in the same directory!
    I had to remove the sensordata.db file from /home/pi to the web-server directory.

    rgds,
    frederik

  4. Hi, Rui,

    i had a problem with the time of the server, was not exact.
    Therefore i put in the app.py for the time ‘now’ also ‘localtime’ and everthing works fine again.
    time(‘now’ , ‘localtime’).

    regards, frederik

  5. Hi Rui,
    I want to update the HTML web-page without refreshing it,but I have no idea how to do that.
    any ideas to suggest,please?
    thanks for your help!!!

  6. Hy
    Thank you very much for the Tutorials on this website.
    It is very easy to realize projects with your instructions.
    Is there an easy way to continue using the data from the sqlite database?
    I can read the data in my python program, but have a problem with the time values. Can I simply convert these string tuples into a float list?
    kind regards
    Patrik

    • Hi Patrik,
      Thank you for your kind words and I’m glad you found the instructions useful and easy to follow.

      You can change any data stored in your database with UPDATE SQL queries.
      Python supports all those features that you’ve mentioned, you can use any data type to manipulate the data, but I only have this example with SQLite at the moment.

      Thanks for reading,
      Rui

  7. Hey Great tutorial!!!!
    It is all working, but I got two identical entries for each 10 seconds reading in the database table and therefore in the Html web server. Same line of data appears twice. I spend hours looking at the code files lines, but could not find what is wrong… If you could give me any hint why that would be that will be great! I have followed your lines strictly.
    Kind Regards,
    Nas

    • I’m having the same problem, I got two identical entries for each reading in the database. One think I may try is to have the readings being posted to diferent MQTT topics and then into two distinct tables.

  8. pi@raspberrypi:~/web-server $ sudo python app.py
    Connected with result code 0

    Traceback (most recent call last):
    File “app.py”, line 68, in
    app.run(host=’0.0.0.0′, port=8181, debug=True)
    File “/usr/lib/python2.7/dist-packages/flask/app.py”, line 841, in run
    run_simple(host, port, self, **options)
    File “/usr/lib/python2.7/dist-packages/werkzeug/serving.py”, line 691, in run_simple
    s.bind((hostname, port))
    File “/usr/lib/python2.7/socket.py”, line 228, in meth
    return getattr(self._sock,name)(*args)
    socket.error: [Errno 98] Address already in use

    This is the error I am facing, what to do??

  9. Hello,
    Thank you for the tutorial. I was wondering if its possible to connect more than one esp8266 modules to only one raspberry pi. Fetching data from all of them at once and displaying on a slightly modified HTML webpage. I haven’t finished implementing all of this yet but I will try to tweak around and find a solution by myself but I would really appreciate if any of ya’ll can share an example or two as it would make things easier for me.
    Thanks

    • Hi Demir.
      It is possible, but we don’t have any resources about what you’re looking for.
      Lots of success for your project.
      Regards,
      Sara 🙂

Leave a Comment

Download our Free eBooks and Resources

Get instant access to our FREE eBooks, Resources, and Exclusive Electronics Projects by entering your email address below.