Headlines
Dynamic charts with Highcharts, SQLite and Python
Sources :
https://github.com/salhina/Flask-Database
Objective :
Plot data logs form Database.
Technologies :
– 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:
#File_name : create_db.py
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()
N.B.:
- Creates ‘db.sqlite’ file?!!
- Database structure created :
- Files generated
1.2 Populate database
A quick python script to fill up our SQLite database with random data (0 to 9):
#File_name : populate_db.py
import sqlite3 # for DB
import time # for timestamp
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()
N.B.:
- Timestamp:
| Timestamp_begin | 1388534400 | 01/01/14 00:00 |
| Pitch | 3600 | |
| timestamp_end | 1451520000 | timestamp_begin + 60*100 |
| Iteration left | timestamp_end- timestamp )/pitch | |
| n: total iterations | timestamp_end- timestamp_begin)/pitch |
- Timestamp summary:
| 36000 | timestamp_begin + 60*100 | ||||
| timestamp_begin | + Pitch | + Pitch | + Pitch | …. | timestamp_end |
| Sx0: | xn: x0 + n*pitch | ||||
| 1388534400 | timestamp= x0+1*pitch | timestamp= x0+2*pitch | timestamp= x0+2*pitch | timestamp= x0+n*pitch | 1451520000 |
- Graphical illustration :
- Files generated
2. Front-end
2.1 Data fetching (server side)
We’ll use only one python script to :
– Generate the json file from the SQLite
database
– Serve the webpage with the chart
#File_name : web.py
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', port=5000
)
2.2 Web page
In the root folder, create the following folders :
– Templates : for your HTML templates
– Static : for static content (images, js files…)
- Generated Files :
2.2.1 templates/graph.html
Then create a templates/graph.html with the following content:
File_name : templates/graph.html
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Highstock Example</title>
<script src="{{ url_for('static', filename='jquery-1.8.3.min.js') }}"></script>
<script type="text/javascript">
$(function () {
$.getJSON('/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
}
}]
});
});
});
</script>
</head>
<body>
<script src="{{ url_for('static', filename='highstock.js') }}"></script>
<script src="{{ url_for('static', filename='highcharts-more.js') }}"></script>
<script src="{{ url_for('static', filename='exporting.js') }}"></script>
<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
</body>
</html>
2.2.2 static content
On this repository youwill find the static folder with the files used in this example.
Go to GitHub Repository
Testing
Your graph should be available here : http://<Raspberry_IP>:5000/graph
Final Outcome :
Enjoy zooming, panning and automatic data grouping !
Code Source :
Synthesis
Data flow:
- Browser: <Raspberry_iP>:5000/graph
- à web.py à templates/graph.html à Ajax request(/data.json)
- à web.py à json from DB
- à Ajax return (templates/graph.html) à Hightcharts / Highstock
