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.

– Store data : SQLite
– Serve content : Python with Flask
– Plot data : Hightcharts / Highstock

Highchart/highstocks with python and Sqlite

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

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

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

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

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__':

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.


Your graph should be available here : :

Enjoy zooming, panning and automatic data grouping !

18 thoughts on “Dynamic charts with Highcharts, SQLite and Python”

  1. 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 ?

    1. 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 !


  2. 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 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 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?

    1. 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__’:

      Keep in mind to update the GetJson parameter in the “emplates/graph.html” accordingly.


  3. Greetings Ronan!

    Great article, this helped immensely!

    Quick question – in cursor.execute, why are you multiplying the epoch with 1000?

    do cursor.execute("SELECT 1000*timestamp

    Thanks again!

    1. It’s been a while ago, if I’m not wrong, the timestamp is in seconds but Hightcharts requires milliseconds :).

  4. 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, so I guess i have to write this in my browser?

    When im trying this its showing blank page, and its added the following line in my SSH window: – – [12/Oct/2017 21:39:33] “GET /graph HTTP/1.1” 200 –

    Can anyone help me?

    1. Question: Should I change the IP when im looking for the graph on my local network? (My RPI is on IP, so I guess i have to write this in my browser?

      >> Yes, you’re right.

      When im trying this its showing blank page, and its added the following line in my SSH window: – – [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.

      1. 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.

  5. 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.

  6. 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?


  7. 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…

  8. 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!

  9. Great example Ronan!
    In case another Win10_x64 user stacks with a blank page, I had to replace ‘’ with ‘localhost’ in web_py, graph.html and browser’s url.

  10. For those looking for missing (invisible) data on a blog, try looking at the source. Thanks Ronan!!

    Highstock Example

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

Leave a Reply

Your email address will not be published. Required fields are marked *