Firmware Engineering Part 2: Data Logging Website
Last article, we wrote a simple MicroPython program for the ESP32. This device broadcasts raw temperature and humidity data to the cloud. Today, we will create a simplistic IOT (Internet of Things) website for the device, and then probably head back into firmware to improve the code. We will also log our data with some simple SQL and Python code.
I am going to use Flask to create a simple website. Open up a new project and create a python file named app.py and also create a folder in the same directory called templates. Inside the templates folder, create a file named index.html.
The idea is simple: A Flask site connects to our MQQT server and pulls data for a specific topic (solar1). This topic contains the data from our devices. We simply send the data to index.html and BINGO, a very simplistic IOT site. Now after we get the basic data displaying for each device, we are going to modify the website to save data and display it more interactively.
Here is the code for app.py so far.
from flask import Flask, render_template
import json
import paho.mqtt.client as mqtt
app = Flask(__name__)
mqtt_broker_address = "broker.hivemq.com"
mqtt_topic = "solar1"
iot_devices = {}
def on_connect(client, userdata, flags, rc):
client.subscribe(mqtt_topic)
def on_message(client, userdata, msg):
payload = json.loads(msg.payload.decode("utf-8"))
sensor_id = payload.get("sensor_id", "unknown")
iot_devices[sensor_id] = payload
print(f"Received message from {sensor_id}: {payload}")
mqtt_client = mqtt.Client()
mqtt_client.on_connect = on_connect
mqtt_client.on_message = on_message
mqtt_client.connect(mqtt_broker_address, 1883, 60)
mqtt_client.loop_start()
@app.route("/")
def index():
devices_list = list(iot_devices.values())
return render_template("index.html", iot_devices=devices_list)
if __name__ == "__main__":
app.run(debug=True)
This is a simplistic piece of code. the index() method routes the list of devices.values() which is the payload received by the server. The front end iterates through the list of values in index.html. For each device we list the data. Don't worry too much about this code, I am going to change it shortly.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Weather Station - Final Project by Henry Meier</title>
<style>
body {
background-color: black;
color: green;
font-family: Arial, sans-serif; /* Specify your desired font-family */
}
</style>
</head>
<body>
<h1>Weather Station</h1>
<ul>
{% for device in iot_devices %}
<li>
<strong>Sensor ID:</strong> {{ device['sensor_id'] }},
<strong>Temperature:</strong> {{ device['temp'] }}°C,
<strong>Humidity:</strong> {{ device['humidity'] }}%,
<strong>Timestamp:</strong> {{ device['timestamp_utc']}}
</li>
{% endfor %}
</ul>
</body>
</html>
We can run the app.py file and see if it works. The website will be available locally at https://127.0.0.1:5000 by default.
bot@botnet:~/PycharmProjects/weather-station$ python3 app.py
/home/bot/PycharmProjects/weather-station/app.py:15: DeprecationWarning: Callback API version 1 is deprecated, update to latest version
mqtt_client = mqtt.Client()
* Serving Flask app 'app'
* Debug mode: on
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
* Running on https://127.0.0.1:5000
Press CTRL+C to quit
* Restarting with stat
/home/bot/PycharmProjects/weather-station/app.py:15: DeprecationWarning: Callback API version 1 is deprecated, update to latest version
mqtt_client = mqtt.Client()
* Debugger is active!
* Debugger PIN: 123-845-918
It seems that the website initializes properly. Let's also head over to Wowki and get two instances running of our devices. This is achieved by running the code on two separate tabs of the browser. You should see console messages for app.py as the data comes in from the MQTT server. Notice that the data for each device is the same because of the simulated sterile world of Wokwi. We are able to discern between the Sensor ID's.
The website needs to be refreshed to show the new data. It would be nice to save data locally with an interactive system. Perhaps some nice graphs.
The website works, it definitely needs a little bit more security for the MQTT transmission. It's time to add more features here since what is presented is quite basic. I had promised to store our data for logging, let implement that.
First and foremost, we need to log our data. Lets import the sqlite3 library and create some new methods.
def init_db():
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS iot_data (
sensor_id TEXT,
temperature REAL,
humidity REAL,
timestamp TEXT
)
''')
conn.commit()
conn.close()
This method creates a new table for us, even if it does not exist. The table name is iot_data and this is located in file iot_data.db. When the method is executed we have a proper storage location for our IOT data.
Our second method will save the data for use, in order to create some interesting charts and applications, it would be nice to reference previous data.
领英推荐
def save_to_db(sensor_id, temperature, humidity, timestamp):
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
INSERT INTO iot_data (sensor_id, temperature, humidity, timestamp) VALUES (?, ?, ?, ?)
''', (sensor_id, temperature, humidity, timestamp))
conn.commit()
conn.close()
For each of the values, we insert the appropriate data. Here is an example of how our data table should look. We store the sensor_id, temperature, humidity, and timestamp in different columns. Notice that timestamp is apparently an integer. This just seems to be how Wokwi simulates a UTC timestamp, however if I am wrong about this, or am pulling the timestamp incorrectly, please tell me!
Here is the NEW and IMPROVED app.py file. This version has data logging and still translates the most recent MQTT data for each device to index.html. Notice that it utilizes the new functions.
from flask import Flask, render_template
import json
import paho.mqtt.client as mqtt
import sqlite3
app = Flask(__name__)
mqtt_broker_address = "broker.hivemq.com"
mqtt_topic = "solar1"
iot_devices = {}
def init_db():
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS iot_data (
sensor_id TEXT,
temperature REAL,
humidity REAL,
timestamp TEXT
)
''')
conn.commit()
conn.close()
def save_to_db(sensor_id, temperature, humidity, timestamp):
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
INSERT INTO iot_data (sensor_id, temperature, humidity, timestamp) VALUES (?, ?, ?, ?)
''', (sensor_id, temperature, humidity, timestamp))
conn.commit()
conn.close()
def on_connect(client, userdata, flags, rc):
client.subscribe(mqtt_topic)
def on_message(client, userdata, msg):
payload = json.loads(msg.payload.decode("utf-8"))
sensor_id = payload.get("sensor_id", "unknown")
temperature = payload.get("temp", 0.0)
humidity = payload.get("humidity", 0.0)
timestamp = payload.get("timestamp_utc", 0.0)
iot_devices[sensor_id] = {
"sensor_id": sensor_id,
"temperature": temperature,
"humidity": humidity,
"timestamp": timestamp
}
print(f"Received message from {sensor_id}: {payload}")
save_to_db(sensor_id, temperature, humidity, timestamp)
mqtt_client = mqtt.Client()
mqtt_client.on_connect = on_connect
mqtt_client.on_message = on_message
mqtt_client.connect(mqtt_broker_address, 1883, 60)
mqtt_client.loop_start()
@app.route("/")
def index():
devices_list = list(iot_devices.values())
return render_template("index.html", iot_devices=devices_list)
if __name__ == "__main__":
init_db()
app.run(debug=True)
Now that we are logging a lot of IOT data, lets change the route for index.html to help show the data as charts. I don't want to see this boring green text anymore. I want to see humidity and temperature charts for each sensor.
This should be easy. Modify index.html.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
</head>
<body>
{% for sensor_id, plots in device_plots.items() %}
<h2>Device: {{ sensor_id }}</h2>
<h3>Temperature Plot</h3>
<img src="data:image/png;base64,{{ plots.temperature_plot }}" alt="Temperature Plot for {{ sensor_id }}">
<h3>Humidity Plot</h3>
<img src="data:image/png;base64,{{ plots.humidity_plot }}" alt="Humidity Plot for {{ sensor_id }}">
{% endfor %}
</body>
</html>
For every sensor, we will pull an image from app.py of a humidity graph and temperature graph. These are base64 encoded images, I simply do not like saving images locally since it has messed up website functionality in the past for me.
Modify the route over at app.py to show our recorded data as matplotlib graphs.
@app.route("/")
def index():
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
SELECT sensor_id, temperature, humidity, timestamp
FROM iot_data
ORDER BY timestamp DESC
''')
data = c.fetchall()
conn.close()
devices_data = {}
for row in data:
sensor_id = row[0]
if sensor_id not in devices_data:
devices_data[sensor_id] = []
devices_data[sensor_id].append({
"sensor_id": row[0],
"temperature": row[1],
"humidity": row[2],
"timestamp": row[3]
})
device_plots = {}
# Create plots for each device
for sensor_id, device_data in devices_data.items():
timestamps = [d['timestamp'] for d in device_data]
temperatures = [d['temperature'] for d in device_data]
humidities = [d['humidity'] for d in device_data]
plt.figure(figsize=(10, 5))
plt.plot(timestamps, temperatures, label='Temperature', color='red')
plt.xlabel('Timestamp')
plt.ylabel('Temperature (°C)')
plt.title(f'Temperature over Time for Sensor {sensor_id}')
plt.xticks(rotation=45)
plt.tight_layout()
temp_img = BytesIO()
plt.savefig(temp_img, format='png')
plt.close()
temp_img.seek(0)
temp_plot_base64 = base64.b64encode(temp_img.getvalue()).decode('utf-8')
plt.figure(figsize=(10, 5))
plt.plot(timestamps, humidities, label='Humidity', color='blue')
plt.xlabel('Timestamp')
plt.ylabel('Humidity (%)')
plt.title(f'Humidity over Time for Sensor {sensor_id}')
plt.xticks(rotation=45)
plt.tight_layout()
hum_img = BytesIO()
plt.savefig(hum_img, format='png')
plt.close()
hum_img.seek(0)
hum_plot_base64 = base64.b64encode(hum_img.getvalue()).decode('utf-8')
device_plots[sensor_id] = {
"temperature_plot": temp_plot_base64,
"humidity_plot": hum_plot_base64
}
return render_template("index.html", device_plots=device_plot
The new code opens our database, iot_data.db, and creates plots for the data with matplotlib. It then sends the base64 encoded images over to index.html.
The results are quite nice. I found a way to change the temperature and humidity for the Wokwi simulation, you simply click your DHT22 sensor and change the temperature and humidity values. The results can be detected on our website, you can see where I changed the temperature and humidity.
Here is the complete new app.py code. Use it with the index.html file I shared earlier. This version imports the proper libraries for base64 encoding of matplotlib charts. Notice what values are returned to the index.html template.
from flask import Flask, render_template
import json
import paho.mqtt.client as mqtt
import sqlite3
import matplotlib.pyplot as plt
from io import BytesIO
import base64
app = Flask(__name__)
mqtt_broker_address = "broker.hivemq.com"
mqtt_topic = "solar1"
iot_devices = {}
def init_db():
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS iot_data (
sensor_id TEXT,
temperature REAL,
humidity REAL,
timestamp TEXT
)
''')
conn.commit()
conn.close()
def save_to_db(sensor_id, temperature, humidity, timestamp):
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
INSERT INTO iot_data (sensor_id, temperature, humidity, timestamp) VALUES (?, ?, ?, ?)
''', (sensor_id, temperature, humidity, timestamp))
conn.commit()
conn.close()
def on_connect(client, userdata, flags, rc):
client.subscribe(mqtt_topic)
def on_message(client, userdata, msg):
payload = json.loads(msg.payload.decode("utf-8"))
sensor_id = payload.get("sensor_id", "unknown")
temperature = payload.get("temp", 0.0)
humidity = payload.get("humidity", 0.0)
timestamp = payload.get("timestamp_utc", 0.0)
iot_devices[sensor_id] = {
"sensor_id": sensor_id,
"temperature": temperature,
"humidity": humidity,
"timestamp": timestamp
}
print(f"Received message from {sensor_id}: {payload}")
save_to_db(sensor_id, temperature, humidity, timestamp)
mqtt_client = mqtt.Client()
mqtt_client.on_connect = on_connect
mqtt_client.on_message = on_message
mqtt_client.connect(mqtt_broker_address, 1883, 60)
mqtt_client.loop_start()
@app.route("/")
def index():
conn = sqlite3.connect('iot_data.db')
c = conn.cursor()
c.execute('''
SELECT sensor_id, temperature, humidity, timestamp
FROM iot_data
ORDER BY timestamp DESC
''')
data = c.fetchall()
conn.close()
devices_data = {}
for row in data:
sensor_id = row[0]
if sensor_id not in devices_data:
devices_data[sensor_id] = []
devices_data[sensor_id].append({
"sensor_id": row[0],
"temperature": row[1],
"humidity": row[2],
"timestamp": row[3]
})
device_plots = {}
for sensor_id, device_data in devices_data.items():
timestamps = [d['timestamp'] for d in device_data]
temperatures = [d['temperature'] for d in device_data]
humidities = [d['humidity'] for d in device_data]
plt.figure(figsize=(10, 5))
plt.plot(timestamps, temperatures, label='Temperature', color='red')
plt.xlabel('Timestamp')
plt.ylabel('Temperature (°C)')
plt.title(f'Temperature over Time for Sensor {sensor_id}')
plt.xticks(rotation=45)
plt.tight_layout()
temp_img = BytesIO()
plt.savefig(temp_img, format='png')
plt.close()
temp_img.seek(0)
temp_plot_base64 = base64.b64encode(temp_img.getvalue()).decode('utf-8')
# Humidity plot
plt.figure(figsize=(10, 5))
plt.plot(timestamps, humidities, label='Humidity', color='blue')
plt.xlabel('Timestamp')
plt.ylabel('Humidity (%)')
plt.title(f'Humidity over Time for Sensor {sensor_id}')
plt.xticks(rotation=45)
plt.tight_layout()
hum_img = BytesIO()
plt.savefig(hum_img, format='png')
plt.close()
hum_img.seek(0)
hum_plot_base64 = base64.b64encode(hum_img.getvalue()).decode('utf-8')
device_plots[sensor_id] = {
"temperature_plot": temp_plot_base64,
"humidity_plot": hum_plot_base64
}
return render_template("index.html", device_plots=device_plots)
if __name__ == "__main__":
init_db()
app.run(debug=True)
This firmware engineering project is coming along well. Next episode, I want to try to actually modify the devices for low power mode and reduce the transmission time. These devices are going to be built! I have been thinking about purchasing a few ESP32's and soldering the DHT22 sensors to them. I think simulation can only go so far. It would also be nice to hook up some sort of battery or power supply to the ESP32's and perhaps some extra code for managing the power supply effectively?
So many new developments are on the way. If you have suggestions please comment. I noticed a lot of people immediately like the other firmware article so i rushed to improve this project. -Henry