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 :

1.1 Populate database

A quick python script to fill up our SQLite database with random data (0 to 9):

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

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 :

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 !

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

      Ronan

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

  3. Greetings Ronan!

    Great article, this helped immensely!

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

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

    Thanks

Leave a Reply

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