6 Oct 2015

Datalogger Mysql Highcharts

Submitted by Max

In this tutorial we achieve a data logger for several sensors connected to Raspberry. The sensors data will be stored in a MySQL database and displayed by a chart with Highcharts

Emmeshop tutorial datalogger mysql

You need a Raspberry Pi with latest Raspbian preinstalled ( we use the new Jessie version ), some sensors connected to Phidgets interfaceKit

Emmeshop tutorial datalogger Mysql

First of all we update the packages

sudo apt-get update


sudo apt-get upgrade

Web Server

Install nginx webserver

sudo apt-get install nginx

and php

sudo apt-get install php5-fpm php-apc

edit the config file of nginx as shown below

sudo nano /etc/nginx/sites-available/default

# You should look at the following URL's in order to grasp a solid understanding
# of Nginx configuration files in order to fully unleash the power of Nginx.
# http://wiki.nginx.org/Pitfalls
# http://wiki.nginx.org/QuickStart
# http://wiki.nginx.org/Configuration
# Generally, you will want to move this file somewhere, and start with a clean
# file but keep this around for reference. Or just disable in sites-enabled.
# Please see /usr/share/doc/nginx-doc/examples/ for more detailed examples.
# Default server configuration
server {
    #listen 80 default_server;
    #listen [::]:80 default_server;
    listen 80;
        server_name $domain_name;
        root /var/www;
        index index.html index.htm index.php;
        access_log /var/log/nginx/access.log;
        error_log /var/log/nginx/error.log;
    location ~\.php$ {
                fastcgi_pass unix:/var/run/php5-fpm.sock;
                fastcgi_split_path_info ^(.+\.php)(/.*)$;
                fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_param HTTPS off;
                try_files $uri =404;
                include fastcgi_params;

    # SSL configuration
    # listen 443 ssl default_server;
    # listen [::]:443 ssl default_server;
    # Self signed certs generated by the ssl-cert package
    # Don't use them in a production server!
    # include snippets/snakeoil.conf;
    #root /var/www/html;
    # Add index.php to the list if you are using PHP
    index index.html index.htm index.nginx-debian.html;
    #server_name _;
    location / {
        # First attempt to serve request as file, then
        # as directory, then fall back to displaying a 404.
        try_files $uri $uri/ =404;
    # pass the PHP scripts to FastCGI server listening on
    #location ~ \.php$ {
    #    include snippets/fastcgi-php.conf;
    #    # With php5-cgi alone:
    #    fastcgi_pass;
    #    # With php5-fpm:
    #    fastcgi_pass unix:/var/run/php5-fpm.sock;
    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #location ~ /\.ht {
    #    deny all;
# Virtual Host configuration for example.com
# You can move that to a different file under sites-available/ and symlink that
# to sites-enabled/ to enable it.
#server {
#    listen 80;
#    listen [::]:80;
#    server_name example.com;
#    root /var/www/example.com;
#    index index.html;
#    location / {
#        try_files $uri $uri/ =404;
#    }

Create a file to test Php

sudo nano /var/www/info.php

with this content


Restart the server

sudo service nginx restart

open your browser to Raspberry ip address and test, if all went well you should see a page like this

Emmeshop tutorial datalogger Mysql


Ok, now install MySQL, phpMyAdmin, and php5-mysql.

sudo apt-get install mysql-server mysql-client php5-mysql phpmyadmin

During MySQL server installation process, you will be asked to configure the password for root user of MySQL.

You will also be asked to choose the web server installed in the system (Apache2 or Lighttpd). In this case, you can leave the web server selection field empty.

During phpMyAdmin installation, you will be asked to configure database for phpMyAdmin. Answer "yes". When asked to enter the password of the administrative user, provide the password.

After make a link of phpMyAdmin from /usr/share/phpmyadmin to /var/www/phpmyadmin.

sudo ln -s /usr/share/phpmyadmin /var/www/phpmyadmin

Open phpMyAdmin from your browser  http://<raspberrypi-ip-address>/phpmyadmin/index.php, and log in as "root" using the administrative password that you have set earlier.

Emmeshop tutorial datalogger Mysql

Create a new database with name sensor_log and a new table table_sensor_log with 4 fields as shown below

Emmeshop tutorial datalogger Mysql


To read the sensors and write values in the database we'll use a python script. We make the connection to the database through MySQLdb.

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API

Download and install MySQLdb

wget http://sourceforge.net/projects/mysql-python/files/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz

gunzip MySQL-python-1.2.3.tar.gz

tar -xvf MySQL-python-1.2.3.tar

cd MySQL-python-1.2.3

sudo apt-get install python-dev libmysqlclient-dev

python setup.py build

python setup.py install

Create a directory in which to save your files and give write permissions

sudo mkdir /home/pi/sensor_logger

sudo chmod -R 0777 /home/pi/sensor_logger

Create a file ifkit.py to test the database connection

sudo nano /home/pi/sensor_logger/ifkit.py

with this content

import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","root","raspberry","sensor_log" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print "Database version : %s " % data
# disconnect from server

Save and running this script,

sudo python /home/pi/sensor_logger/ifkit.py

If the connection is ok it produces the following result

pi@raspberrypi ~ $ sudo python /home/pi/sensor_logger/ifkit.py
Database version : 5.5.44-0+deb8u1



Now that the database is ready we install the driver and library Phidgets

sudo apt-get install libusb-1.0-0-dev

wget http://www.phidgets.com/downloads/libraries/libphidget.tar.gz

tar zxvf libphidget.tar.gz

cd libphidget-



sudo make install

wget http://www.phidgets.com/downloads/libraries/PhidgetsPython.zip

unzip PhidgetsPython.zip

cd PhidgetsPython/

sudo python setup.py install

After connect the 1018_2 - PhidgetInterfaceKit 8/8/8 to the usb port of Raspberry and some sensors to the analog input of 1018

Emmeshop tutorial datalogger Mysql


Replace the content of file ifkit.py with this

#!/usr/bin/env python
#Basic imports
from __future__ import division
from ctypes import *
import sys
import math
import random
import os
import time
import datetime
#Phidget specific imports
from Phidgets.PhidgetException import *
from Phidgets.Events.Events import *
from Phidgets.Devices.InterfaceKit import *
#Mysql imports
import MySQLdb
#Create an interfacekit object
    interfaceKit = InterfaceKit()
except RuntimeError as e:
    print("Runtime Exception: %s" % e.details)
#Event Handler Callback Functions
def interfaceKitAttached(e):
    attached = e.device
def interfaceKitDetached(e):
    detached = e.device
def interfaceKitError(e):
        source = e.device
        print("InterfaceKit %i: Phidget Error %i: %s" % (source.getSerialNum(), e.eCode, e.description))
    except PhidgetException as e:
        print("Phidget Exception %i: %s" % (e.code, e.details))
def interfaceKitInputChanged(e):
    source = e.device
def interfaceKitSensorChanged(e):
    source = e.device
def interfaceKitOutputChanged(e):
    source = e.device
def logSensor():
    interfaceKit.setSensorChangeTrigger(0, 3)
    for i in range(0,8):
def insert_to_db(id_sensor, value_sensor):
    db = MySQLdb.connect("localhost","root","raspberry","sensor_log" )
    cursor = db.cursor()
    time_sensor = time.time()
    # Prepare SQL query to INSERT a record into the database.
    sql = "INSERT INTO table_sensor_log(timestamp, sensor_id, sensor_value) VALUES ('%d', '%d', '%d' )" % (time_sensor,id_sensor ,value_sensor)
#Main Program Code
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
    except PhidgetException as e:
        print("Phidget Exception %i: %s" % (e.code, e.details))
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))

When we run the script the value of the sensors will be read and through the function insert_to_db the values will be saved in the database.

sudo python /home/pi/sensor_logger/ifkit.py

Emmeshop tutorial datalogger Mysql

We can automate the script via crontab, for example to make it start every 3 minutes

crontab -e

and add this lines to the end of file

*/3 * * * * sudo python /home/pi/sensor_logger/ifkit.py



Now that the data are stored in the database there are many ways to view them, here we use Highcharts to display them graphically

Create a new folder with right permissions

sudo mkdir /home/pi/sensor_logger/web

create a file index.html

sudo nano /home/pi/sensor_logger/web/index.html

with this content

<title>Emmeshop tutorial</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js" type="text/javascript"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
<script type="text/javascript" src="data.js" ></script>
<div id="chart" style="height: 400px; margin: 0 auto"></div>

a file values.php

sudo nano /home/pi/sensor_logger/web/values.php

with this content

$con = mysql_connect("localhost","root","raspberry");
if (!$con) {
die('Could not connect: ' . mysql_error());
mysql_select_db("sensor_log", $con);
$result = mysql_query("SELECT * FROM `table_sensor_log` WHERE sensor_id=1 ") or die ("Connection error");
while($row = mysql_fetch_array($result)) {
echo $row['timestamp'] . "/" . $row['sensor_value']. "/" ;

and a file data.js

sudo nano /home/pi/sensor_logger/web/data.js

with this content

$(function() {
    var x_values = [];
    var y_values = [];
    var switch1 = true;
    $.get('values.php', function(data) {
        data = data.split('/');
        for (var i in data)
            if (switch1 == true)
                var ts = timeConverter(data[i]);
                switch1 = false;
                switch1 = true;
            chart : {
                type : 'spline'
            title : {
                text : 'Datalogger Highcharts Mysql'
            subtitle : {
                text : 'Source: www.emmeshop.eu'
            xAxis : {
                title : {
                    text : 'Time'
                categories : x_values
            yAxis : {
                title : {
                    text : 'Sensor value'
                labels : {
                    formatter : function() {
                        return this.value + ' UM'
            tooltip : {
                crosshairs : true,
                shared : true,
                valueSuffix : ''
            plotOptions : {
                spline : {
                    marker : {
                        radius : 4,
                        lineColor : '#666666',
                        lineWidth : 1
            series : [{
                name : 'Sensor Value',
                data : y_values
function timeConverter(UNIX_timestamp){
  var a = new Date(UNIX_timestamp * 1000);
  var months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
  var year = a.getFullYear();
  var month = months[a.getMonth()];
  var date = a.getDate();
  var hour = a.getHours();
  var min = a.getMinutes() < 10 ? '0' + a.getMinutes() : a.getMinutes();
  var sec = a.getSeconds() < 10 ? '0' + a.getSeconds() : a.getSeconds();
  var time = date + ' ' + month + ' ' + year + ' ' + hour + ':' + min + ':' + sec ;
  return time;

create a link to the webserver folder

sudo ln -s /home/pi/sensor_logger/web /var/www/web

Ok, now we are ready to read sensor values.

In the index.html file there are the links to jquery and Highcharts.

The value.php file connects to the database and reads the values (in this example, only those sensor with id 1).

The data.js file formats and display them graphically 

Open your browser on http://<raspberrypi-ip-address>/web you can see your chart

Emmeshop tutorial datalogger Mysql