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


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

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

    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))
        timestamp += pitch
except Exception as e:
    raise e


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

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)

def graph():
    return render_template('graph.html')

if __name__ == '__main__':

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


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

		<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('', function (data) {
        // Create the chart
        $('#container').highcharts('StockChart', {
            rangeSelector : {
                selected : 1
            title : {
                text : 'My Sensor'
            series : [{
                name : 'Value',
                data : data,
                tooltip: {
                    valueDecimals: 2
<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>



Your graph should be available here : :

Final result
Final result

Enjoy zooming, panning and automatic data grouping !

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