From Data to Graph: Building a Flask‑SQLite IoT Dashboard with Raspberry Pi

Capturing real data from a DHT22 sensor, storing it in a lightweight SQLite database, and visualizing the results on a web dashboard built with Flask.

1. Introduction

In our previous tutorial, Python WebServer With Flask and Raspberry Pi, we learned how to expose sensor data via a web front‑end. The next logical step is to persist those readings so they can be analysed later.

2. Bill of Materials

• Raspberry Pi 3 – $32.00
• DHT22 Temperature & Relative Humidity Sensor – $9.95
• 4.7 kΩ pull‑up resistor

3. Installing SQLite

SQLite is the ideal database engine for a single‑board computer: it is serverless, lightweight, and fully open‑source. Install it with:

sudo apt-get install sqlite3

Then create a working directory and a database file:

mkdir Sensors_Database
cd Sensors_Database
sqlite3 sensorsData.db

When the SQLite prompt appears, exit with .quit and you’ll be back at the shell.

4. Creating the DHT Table

The table DHT_data holds a timestamp, temperature and humidity.

sqlite3 sensorsData.db
BEGIN;
CREATE TABLE DHT_data (timestamp DATETIME, temp NUMERIC, hum NUMERIC);
COMMIT;

Alternatively, use Python:

import sqlite3
con = sqlite3.connect('sensorsData.db')
with con:
    cur = con.cursor()
    cur.execute('DROP TABLE IF EXISTS DHT_data')
    cur.execute('CREATE TABLE DHT_data(timestamp DATETIME, temp NUMERIC, hum NUMERIC)')

5. Inserting Sample Rows

Manual insertion via SQLite:

INSERT INTO DHT_data VALUES(datetime('now'), 20.5, 30);

Or bulk insert in Python:

with con:
    cur.execute('INSERT INTO DHT_data VALUES(datetime('now'), 20.5, 30)')
    cur.execute('INSERT INTO DHT_data VALUES(datetime('now'), 25.8, 40)')
    cur.execute('INSERT INTO DHT_data VALUES(datetime('now'), 30.3, 50)')

6. Wiring the DHT22

Connect VCC to 3.3 V, Data to GPIO 16, GND to ground, and place a 4.7 kΩ pull‑up between VCC and Data.

7. Installing the Adafruit DHT Library

Download from GitHub and install:

sudo python3 setup.py install

8. Reading and Logging Data

Below is a minimal script that reads the sensor, rounds the values, and writes them to the database:

import time
import sqlite3
import Adafruit_DHT

DB_NAME = 'sensorsData.db'
SAMPLE_FREQ = 120  # seconds

DHT_SENSOR = Adafruit_DHT.DHT22
DHT_PIN = 16


def read_and_log():
    humidity, temperature = Adafruit_DHT.read_retry(DHT_SENSOR, DHT_PIN)
    if humidity is not None and temperature is not None:
        humidity = round(humidity)
        temperature = round(temperature, 1)
        conn = sqlite3.connect(DB_NAME)
        cur = conn.cursor()
        cur.execute('INSERT INTO DHT_data VALUES(datetime('now'), ?, ?)', (temperature, humidity))
        conn.commit()
        conn.close()

for _ in range(3):
    read_and_log()
    time.sleep(SAMPLE_FREQ)

9. Continuous Logging

For a real‑time logger, run the following in a dedicated terminal. It captures a sample every minute:

import time, sqlite3, Adafruit_DHT

DB = 'sensorsData.db'
SAMPLE_FREQ = 60

while True:
    humidity, temperature = Adafruit_DHT.read_retry(Adafruit_DHT.DHT22, 16)
    if humidity is not None and temperature is not None:
        conn = sqlite3.connect(DB)
        cur = conn.cursor()
        cur.execute('INSERT INTO DHT_data VALUES(datetime('now'), ?, ?)', (round(temperature,1), round(humidity)))
        conn.commit()
        conn.close()
    time.sleep(SAMPLE_FREQ)

10. Querying the Database

Typical queries:

• All rows: SELECT * FROM DHT_data
• Last entry: SELECT * FROM DHT_data ORDER BY timestamp DESC LIMIT 1
• Temperature > 30 °C: SELECT * FROM DHT_data WHERE temp > 30
• Humidity = 29 %: SELECT * FROM DHT_data WHERE hum = 29

11. Flask Web Server

Install Flask: sudo apt-get install python3-flask. Create the project structure:

mkdir dhtWebServer
cd dhtWebServer
mkdir templates static

In appDhtWebServer.py:

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)


def fetch_latest():
    conn = sqlite3.connect('../sensorsData.db')
    cur = conn.cursor()
    cur.execute('SELECT * FROM DHT_data ORDER BY timestamp DESC LIMIT 1')
    row = cur.fetchone()
    conn.close()
    return row

@app.route('/')
def index():
    time, temp, hum = fetch_latest()
    return render_template('index.html', time=time, temp=temp, hum=hum)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=80, debug=False)

Template templates/index.html:

<!DOCTYPE html>
<html>
<head>
    <title>DHT Sensor Dashboard</title>
    <link rel='stylesheet' href='../static/style.css'>
</head>
<body>
    <h1>DHT Sensor Data</h1>
    <p>Temperature: {{ temp }} °C</p>
    <p>Humidity: {{ hum }} %</p>
    <p>Last reading: {{ time }}</p>
    <a href='/' class='button'>Refresh</a>
</body>
</html>

12. Enhancing the Front‑End with Gages

Download JustGage and place justgage.js and raphael-2.1.4.min.js in static. Update the template to render animated gauges:

<div id='g1'></div>
<div id='g2'></div>
<script src='../static/raphael-2.1.4.min.js'></script>
<script src='../static/justgage.js'></script>
<script>
    new JustGage({id:'g1', value:{{ temp }}, min:-10, max:50, title:'Temperature', label:'°C'});
    new JustGage({id:'g2', value:{{ hum }}, min:0, max:100, title:'Humidity', label:'%'});
</script>

13. Historical Graphs with Matplotlib

Install Matplotlib: sudo apt-get install python3-matplotlib. Create a new Flask app appDhtWebHist.py that serves PNG images generated on the fly.

from flask import Flask, render_template, make_response
from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
from matplotlib.figure import Figure
import sqlite3, io

app = Flask(__name__)
DB = '../sensorsData.db'


def get_latest():
    conn = sqlite3.connect(DB)
    cur = conn.cursor()
    cur.execute('SELECT * FROM DHT_data ORDER BY timestamp DESC LIMIT 1')
    row = cur.fetchone()
    conn.close()
    return row


def get_history(samples):
    conn = sqlite3.connect(DB)
    cur = conn.cursor()
    cur.execute('SELECT * FROM DHT_data ORDER BY timestamp DESC LIMIT ?', (samples,))
    rows = cur.fetchall()
    conn.close()
    rows.reverse()  # chronological order
    times, temps, hums = zip(*rows)
    return times, temps, hums

@app.route('/')
def index():
    time, temp, hum = get_latest()
    return render_template('index.html', time=time, temp=temp, hum=hum, samples=100)

@app.route('/plot/temp')
def plot_temp():
    times, temps, _ = get_history(100)
    fig = Figure()
    ax = fig.add_subplot(1,1,1)
    ax.plot(range(len(temps)), temps)
    ax.set_title('Temperature (°C)')
    canvas = FigureCanvas(fig)
    output = io.BytesIO()
    canvas.print_png(output)
    return make_response(output.getvalue(), 200, {'Content-Type':'image/png'})

@app.route('/plot/hum')
def plot_hum():
    _, _, hums = get_history(100)
    fig = Figure()
    ax = fig.add_subplot(1,1,1)
    ax.plot(range(len(hums)), hums)
    ax.set_title('Humidity (%)')
    canvas = FigureCanvas(fig)
    output = io.BytesIO()
    canvas.print_png(output)
    return make_response(output.getvalue(), 200, {'Content-Type':'image/png'})

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=80, debug=False)

Template templates/index.html includes two <img> tags that request the PNG routes.

14. Conclusion

This end‑to‑end project demonstrates how to turn a cheap sensor into a data‑logging and monitoring platform: capture, persist, query, and visualize everything through a local Flask web server. All source code is available on GitHub and further examples can be found on MJRoBot.org.