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

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

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

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 :

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

<!DOCTYPE HTML>

<html>

<head>

<title>Highstock Example</title>

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

</body>

</html>

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 !

Facebook

Google+

Twitter

12 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 ?

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

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

1

do cursor.execute("SELECT 1000*timestamp

Thanks again!

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 :).
  
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?

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.
  
  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.
    
Jason E. says:

2017-11-08 at 05:59

Great work. Thanks a lot.

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.

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

1 Database

1.1 Create database

1.1 Populate database

2. Front-end

2.1 Data fetching

2.2 Web page

2.2.1 templates/graph.html

2.2.2 static content

Testing

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

Leave a Reply Cancel reply