Publishing DHT11 Sensor Data to SQLite Database using ESP8266

pi@raspberry:~ $ sudo apt update

pi@raspberry:~ $ sudo apt install -y mosquitto mosquitto-clients

pi@raspberry:~ $ sudo systemctl enable mosquitto.service

Test the installation

pi@raspberry:~ $ mosquitto -v

Get the IP address of the Rpi

pi@raspberry:~ $ hostname -I

pi@raspberry:~ $ sudo apt-get install mosquitto-clients

pi@raspberry:~ $ mosquitto -d

pi@raspberry:~ $ mosquitto_sub -d -t testTopic

pi@raspberry:~ $ mosquitto_pub -d -t testTopic -m "Hello world!"

pi@raspberry:~ $ mosquitto_sub -d -t testTopic

pi@raspberry:~ $ mosquitto_pub -d -t testTopic -m "multiple test"

pi@raspberrypi ~ $ sudo apt-get update

pi@raspberrypi ~ $ sudo apt-get upgrade

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

pi@raspberrypi ~ $ sudo pip install flask

pi@raspberrypi ~ $ mkdir web-server

pi@raspberrypi ~ $ cd web-server

pi@raspberrypi:~/web-server $

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

Copy and paste the following code in the app.py file. Special thanks to Rui 

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)

pi@raspberry:~ $ sudo apt-get install sqlite3

pi@raspberry:~ $ sqlite3 sensordata.db

sqlite> BEGIN;

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

sqlite> COMMIT;

sqlite> .tables

dhtreadings

sqlite> .fullschema

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

sqlite> BEGIN;

sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.4, 48, date('now'), time('now'), "manual");

sqlite> COMMIT;

sqlite> SELECT * FROM dhtreadings;

1|22.4|48|2017-01-26|23:43:13|manual

sqlite> BEGIN;

sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.5, 48.7, date('now'), time('now'), "manual");

sqlite> COMMIT;

sqlite> .fullschema

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

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

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

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

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

<!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>

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

  }

}

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

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

sudo pip install paho-mqtt

or

sudo pip3 install paho-mqtt