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

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 !

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

Roland says:

2016-12-28 at 22:27

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 ?

Reply
1. Ronan says:
  
  2016-12-29 at 06:35
  
  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
  
  Reply
Laurens says:

2017-04-23 at 11:37

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?

Reply
1. Ronan says:
  
  2017-04-23 at 19:03
  
  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
  
  Reply
Ev Uraan says:

2017-07-27 at 04:42
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!
Reply
1. Ronan says:
  
  2017-07-29 at 10:05
  
  It’s been a while ago, if I’m not wrong, the timestamp is in seconds but Hightcharts requires milliseconds :).
  
  Reply
Johansen says:

2017-10-12 at 21:41

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?

Reply
1. Ronan says:
  
  2017-10-13 at 08:01
  
  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.
  
  Reply
  1. Richard says:
    
    2018-10-13 at 19:35
    
    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.
    
    Reply
Jason E. says:

2017-11-08 at 05:59

Great work. Thanks a lot.

Reply
Shashi says:

2018-01-15 at 06:10

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.

Reply
Romain says:

2018-09-29 at 20:49

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

Reply
Hasan Tariq says:

2018-12-09 at 23:12

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…

Reply
Georg B. says:

2019-07-26 at 13:29

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!

Reply
Dimitri says:

2019-11-07 at 13:17

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

Reply