I used to use a Raspberry Pi as a data logger for many sensors, here is how I create a nice front-end for data visualization.
– Store data : SQLite
– Serve content : Python with Flask
– Plot data : Hightcharts / Highstock
1 Database
1.1 Create database
A quick python script to create our SQLite database :
import sqlite3 sqlite_file = 'db.sqlite' conn = sqlite3.connect(sqlite_file) c = conn.cursor() conn.execute("CREATE TABLE measures (timestamp DATETIME, measure INTEGER)") conn.commit() conn.close()
1.1 Populate database
A quick python script to fill up our SQLite database with random data (0 to 9):
import sqlite3 import time from random import randint sqlite_file = 'db.sqlite' timestamp_begin = 1388534400 # 01/01/14 00:00 #timestamp_end = timestamp_begin + 60*100 timestamp_end = 1451520000 pitch = 3600 try: conn = sqlite3.connect(sqlite_file) c = conn.cursor() timestamp = timestamp_begin while timestamp <= timestamp_end: print("Iterations left :", (timestamp_end-timestamp)/pitch) measure = randint(0, 9) conn.execute("INSERT INTO measures (timestamp, measure) VALUES ({timestamp}, {measure})".format(timestamp=timestamp, measure=measure)) conn.commit() timestamp += pitch except Exception as e: conn.rollback() raise e finally: conn.close()
2. Front-end
2.1 Data fetching
We'll use only one python script to :
- Generate the json file from the SQLite database
- Serve the webpage with the chart
from flask import Flask, render_template, request import sqlite3 import json app = Flask(__name__) @app.route("/data.json") def data(): connection = sqlite3.connect("db.sqlite") cursor = connection.cursor() cursor.execute("SELECT 1000*timestamp, measure from measures") results = cursor.fetchall() print results return json.dumps(results) @app.route("/graph") def graph(): return render_template('graph.html') if __name__ == '__main__': app.run( debug=True, threaded=True, host='0.0.0.0' )
2.2 Web page
In the root folder, create the following folders :
- Templates : for your HTML templates
- Static : for static content (images, js files...)
2.2.1 templates/graph.html
Then create a templates/graph.html with the following content :
Highstock Example
2.2.2 static content
Here is a zip of my static folder with the files used in this example.
Testing
Your graph should be available here : http://0.0.0.0:5000/graph :
Enjoy zooming, panning and automatic data grouping !
Hello,
Thanks for sharing your experience.
I am quite new with sqlite and flask,and wanted to try your example.
Nevertheless, it looks that the template graph.html you call in flask is missing. Or did I something wrong ?
Hi Roland,
You’re right. I’ve tried to fixed it quickly, you should find all the information that you need now.
Let me know if it’s clear and if it works !
Ronan
Hi,
I’ve followed your examples, but got stuck. I’m running it on a remote server (a Synology NAS) and I’m not sure about the IP addresses. If I run things with 0.0.0.0 I get an “Address already in use” error. If I then add a port (e.g. 8080) it runs fine.
However I cannot open the charts yet on my laptop. It throws an “GET http://0.0.0.0:5000/data.json net::ERR_CONNECTION_REFUSED” error. Worth noting is that the Synology GUI also runs on port 5000.
Do you have any idea what the problem is and how to fix this?
Hi,
Flask default port is 5000, and you’ll have a conflict with the Synology DSM webserver.
Did you try to ask flask to use another port using something like :
if __name__ == ‘__main__’:
app.run(
debug=True,
threaded=True,
host=’0.0.0.0′,
port=4000
)
Keep in mind to update the GetJson parameter in the “emplates/graph.html” accordingly.
Ronan
Greetings Ronan!
Great article, this helped immensely!
Quick question – in cursor.execute, why are you multiplying the epoch with 1000?
Thanks again!
It’s been a while ago, if I’m not wrong, the timestamp is in seconds but Hightcharts requires milliseconds :).
I think im almost there. But im still only seeing blank page 🙁
Question: Should I change the IP when im looking for the graph on my local network? (My RPI is on IP 192.168.175.100, so I guess i have to write this in my browser? http://192.168.175.100:5000/graph
When im trying this its showing blank page, and its added the following line in my SSH window:
192.168.175.164 – – [12/Oct/2017 21:39:33] “GET /graph HTTP/1.1” 200 –
Can anyone help me?
Question: Should I change the IP when im looking for the graph on my local network? (My RPI is on IP 192.168.175.100, so I guess i have to write this in my browser? http://192.168.175.100:5000/graph
>> Yes, you’re right.
When im trying this its showing blank page, and its added the following line in my SSH window:
192.168.175.164 – – [12/Oct/2017 21:39:33] “GET /graph HTTP/1.1” 200 –
>> The HTTP 200 OK success status response code indicates that the request has succeeded. So the issue is somewhere else.
I had the exact same problem, to fix it you need to ensure the graph.html also uses your pi’s address in the $.getJSON command.
Great work. Thanks a lot.
Well written article, I was able to modify it to use for my project to collect and show the temperature data on my web portal with this blog.
But I have to refresh my portal screen every time to get updated data, I was hoping to see the page auto-refresh and get new data. That’s what I thought when I saw the Dynamic charting.
Hi,
I’ll try with my db my dt is like 2018-09-15 17:46:03 and when I check json output I see something like that (u’2018-09-15 17:46:03′, 12.675982) so chart doesn’t work. Do you have an idea what’s going on?
Thanks
Good Example Ronan…
I am getting blank page…
1. Request = 200 OK..
2. Page Title Shows “Highstock Example…
Everything seems fine… but page is blank!
Please assist…
Really an excellent work! I need some time to find out that I have to start with “x.x.x.x:5000/graph”. Thank you very much!
Great example Ronan!
In case another Win10_x64 user stacks with a blank page, I had to replace ‘0.0.0.0’ with ‘localhost’ in web_py, graph.html and browser’s url.
Thanks
Hi, I can’t see your graph.html, can you put it up?
Same here
For those looking for missing (invisible) data on a blog, try looking at the source. Thanks Ronan!!
Highstock Example
$(function () {
$.getJSON(‘http://0.0.0.0:5000/data.json’, function (data) {
// Create the chart
$(‘#container’).highcharts(‘StockChart’, {
rangeSelector : {
selected : 1
},
title : {
text : ‘My Sensor’
},
series : [{
name : ‘Value’,
data : data,
tooltip: {
valueDecimals: 2
}
}]
});
});
});