Back
Featured image of post Dynamic charts with Highcharts, SQLite and Python

Dynamic charts with Highcharts, SQLite and Python

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.

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

Database

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()

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 (timeFinal resultstamp, measure) VALUES ({timestamp}, {measure})".format(timestamp=timestamp, measure=measure))
        conn.commit()
        timestamp += pitch
except Exception as e:
    conn.rollback()
    raise e
finally:
    conn.close()

Front-end

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'
)

Web page

In the root folder, create the following folders :

  • Templates : for your HTML templates
  • Static : for static content (images, js files…)

Hightchart folder organization
Hightchart folder organization

templates/graph.html

Then create a templates/graph.html with the following content :

<!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('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
                }
            }]
        });
    });
});
		</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>

Testing

Your graph should be available here : http://0.0.0.0:5000/graph :

Final result
Final result

Enjoy zooming, panning and automatic data grouping !

comments powered by Disqus
Built with Hugo
Theme Stack designed by Jimmy