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:
- You need a Raspberry Pi board – read Best Raspberry Pi Starter Kits
- ESP8266 Publishing DHT22 Readings with MQTT to Raspberry Pi
- Raspberry Pi Publishing MQTT Messages to ESP8266
- Testing Mosquitto Broker and Client on Raspbbery Pi
- How to Install Mosquitto Broker on Raspberry Pi
- What is MQTT and How It Works
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.pyCopy 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)
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):
/*****
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");
}
}
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.
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.
Hi Rui,
Thank you so so much. I need that and you published. You are the best. Thank you again.
You’re welcome,
Thanks for reading!
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 🙂
happy to hear that I could help!
Regards,
Rui
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
Yes, I should be posting a tutorial on that exact subject in just a couple of days!
Thanks for the suggestion!
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
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
getting the right time now, thanks for the comment.
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!!!
Yes, you need to use AJAX to receive the values without refreshing the web page. I don’t have any tutorials with that exact example though…
Regards,
Rui
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
thank you for the tutorial. could you point me where the sketch and program template are?
What do you mean by template? The code used in this project is posted in the blog post.
Thanks for asking!
Rui
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.
Hi,
Basically you have to change the end of your python script where “debug=True” to False.
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??
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
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 🙂
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).
Hi John.
I think your DHT library is not properly installed or it is not installed.
Try to install the latest version of the DHT library.
Also, you also need the Adafruit_sensor driver library. Make sure you install it too: https://randomnerdtutorials.com/wp-content/uploads/2019/04/adafruit_unified_sensor_library.png
I hope this helps.
Regards,
Sara
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.
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
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
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?
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.
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”
Hi.
Double-check your MQTT broker details and make sure your MQTT broker is running.
Regards,
Sara
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);
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!!
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
Installing Flask in a virtual environment is different than these instructions.
But following the Flask install website worked.
Hi Rui,
That was great! But if i want to use Django as web server framework, where i write the code for MQTT?
Thanks