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 build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »
Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »

Enjoyed this project? Stay updated by subscribing our newsletter!

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

  1. 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

    Reply
  2. 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

    Reply
  3. 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

    Reply
  4. 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!!!

    Reply
  5. 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

    Reply
    • 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

      Reply
  6. 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

    Reply
    • 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.

      Reply
  7. 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??

    Reply
    • once again restart your raspberry pi turn on and off. because that channel is already running in broker .so reboot your raspberry pi run program once again it will get work

      Reply
  8. 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

    Reply
    • 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 🙂

      Reply
  9. I am trying to follow the tutorial and i am getting the following error:
    /home/john/.arduino15/packages/esp8266/hardware/esp8266/2.5.2/cores/esp8266 -I/home/john/.arduino15/packages/esp8266/hardware/esp8266/2.5.2/variants/nodemcu -I/home/john/.arduino15/packages/esp8266/hardware/esp8266/2.5.2/libraries/ESP8266WiFi/src -I/home/john/Arduino/libraries/PubSubClient/src -I/home/john/Arduino/libraries/DHT /home/john/Arduino/libraries/DHT/DHT_U.cpp -o /dev/null
    In file included from /home/john/Arduino/libraries/DHT/DHT_U.cpp:15:0:
    /home/john/Arduino/libraries/DHT/DHT_U.h:36:29: fatal error: Adafruit_Sensor.h: No such file or directory
    #include

    ^
    compilation terminated.
    Using library ESP8266WiFi at version 1.0 in folder: /home/john/.arduino15/packages/esp8266/hardware/esp8266/2.5.2/libraries/ESP8266WiFi
    Using library PubSubClient at version 2.7 in folder: /home/john/Arduino/libraries/PubSubClient
    Using library DHT at version 1.3.5 in folder: /home/john/Arduino/libraries/DHT
    exit status 1
    Error compiling for board NodeMCU 1.0 (ESP-12E Module).

    Reply
  10. Hi, Rui
    Hi, Sara
    I like this sketch, but I imagin one “failure”:
    Connected with result code 0
    * Running on http://0.0.0.0:8181/ (Press CTRL+C to quit)
    * Restarting with stat
    * Debugger is active!
    Connected with result code 0
    * Debugger pin code: 191-356-481
    DHT readings update
    DHT readings update

    Last sentence is coming up always twice.

    Reply
  11. When running app.py, I get the message:

    pi@raspberrypi3:~/web-server $ sudo python app.py
    Traceback (most recent call last):
    File “app.py”, line 7, in
    import paho.mqtt.client as mqtt
    ImportError: No module named paho.mqtt.client

    Yet running ‘pip install paho-mqtt’ shows paho is already installed.

    Any ideas?

    Jim

    Reply
  12. Great work, thank you so much.
    Just one question, what if the DHT sensor is connected directly to the raspberry pi?
    or another example is if you have to connect a GPS modeule directly to the Raspberry pi?

    What will it look like? Does one have to go through all these processes or you will skip since you are not using the ESP8266?

    Thanks once again for your great efforts

    Reply
  13. Hi guys,
    I have two problems with arduino and mqtt, that is “Attempting MQTT connection… Failed. rc=-4…” and “code 400, message Bad HTTP/0.9 request type…”
    How can I fix it?

    Reply
  14. Having loaded Mosquitto and Node-Red and having nothing but compatibility problems with software, cannot install sqlite for node-red for some reason….
    please do two things.
    #1, list the version of the software you are using.
    #2, list how to find the version before installing.

    Reply
  15. I have followed your instructions. You have very detailed instructions. Thank you. But I got a small error, can you point it out to me. Thanks, again.
    “Attempting MQTT connection…failed, rc=5 try again in 5 seconds”

    Reply
  16. Excellent tutorial, thanks for your always great subjects. I did find a typo in the code where you translate the values to Fahrenheit, hic should be hif if I’m correct.
    // Uncomment to compute temperature values in Fahrenheit
    // float hif = dht.computeHeatIndex(f, h);
    // static char temperatureTemp[7];
    // dtostrf(hic, 6, 2, temperatureTemp);
    should be this
    // Uncomment to compute temperature values in Fahrenheit
    // float hif = dht.computeHeatIndex(f, h);
    // static char temperatureTemp[7];
    // dtostrf(hif, 6, 2, temperatureTemp);

    Reply
  17. Hello,
    i have also the problem:
    “Attempting MQTT connection…failed, rc=5 try again in 5 seconds”
    Mosquitto Broker is active, and the details is look like your tutorial, any ideas?
    Thank you, your tutorial was really helpful!!

    Reply
  18. I have two esp8266’s connected this way and date is flowing into the .db and onto the web page, how can I get the “device name” to identify each controller rather than just having a static device name of “esp8266” any help would be greatly received . thanks

    Reply

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.