Introduction: Raspberry Datalogger With Mysql Highcharts

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

and

sudo apt-get upgrade

Step 1: Web Server

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 127.0.0.1:9000
    #
    #location ~ \.php$ {
    #    include snippets/fastcgi-php.conf;
    #
    #    # With php5-cgi alone:
    #    fastcgi_pass 127.0.0.1:9000;
    #    # 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

<?php
  phpinfo();
?>

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

Step 2: MySQL - MySQLdb

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://raspberry-ip/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

MySQLdb

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

#!/usr/bin/python
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
db.close()

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

Step 3: Phidgets

Phidgets

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-2.1.8.20150410/
./configure
make
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

Step 4: Start the Application

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
try:
    interfaceKit = InterfaceKit()
except RuntimeError as e:
    print("Runtime Exception: %s" % e.details)
    print("Exiting....")
    exit(1)
 
#Event Handler Callback Functions
def interfaceKitAttached(e):
    attached = e.device
 
def interfaceKitDetached(e):
    detached = e.device
 
def interfaceKitError(e):
    try:
        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.setRatiometric(False)
    interfaceKit.setSensorChangeTrigger(0, 3)
    for i in range(0,8):
        senValue=interfaceKit.getSensorRawValue(i)
        insert_to_db(i,senValue);
 
 
 
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)
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
    db.close()
    
#Main Program Code
try:
    interfaceKit.setOnAttachHandler(interfaceKitAttached)
    interfaceKit.setOnDetachHandler(interfaceKitDetached)
    interfaceKit.setOnErrorhandler(interfaceKitError)
    interfaceKit.setOnInputChangeHandler(interfaceKitInputChanged)
    interfaceKit.setOnOutputChangeHandler(interfaceKitOutputChanged)
    interfaceKit.setOnSensorChangeHandler(interfaceKitSensorChanged)
    
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
    print("Exiting....")
    exit(1)
 
 
try:
    interfaceKit.openPhidget()
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
    print("Exiting....")
    exit(1)
 
try:
    interfaceKit.waitForAttach(10000)
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
    try:
        interfaceKit.closePhidget()
    except PhidgetException as e:
        print("Phidget Exception %i: %s" % (e.code, e.details))
        print("Exiting....")
        exit(1)
    print("Exiting....")
    exit(1)
 
 
logSensor()      
 
 
try:
    interfaceKit.closePhidget()
except PhidgetException as e:
    print("Phidget Exception %i: %s" % (e.code, e.details))
    print("Exiting....")
    exit(1)

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

MAILTO=""
*/3 * * * * sudo python /home/pi/sensor_logger/ifkit.py

Highcharts

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

<html>
<head>
<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>
</head>
<body>
<div id="chart" style="height: 400px; margin: 0 auto"></div>
</body>
</html>

a file values.php

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

with this content

<?php
$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']. "/" ;
}
mysql_close($con);
?>

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]);
                x_values.push(ts);
                switch1 = false;
            }
            else
            {
                y_values.push(parseFloat(data[i]));
                switch1 = true;
            }
 
        }
        x_values.pop();
 
        $('#chart').highcharts({
            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://raspberry-ip/web you can see your chart

Emmeshop tutorial datalogger Mysql

Comments

author
JohnZ57 (author)2017-08-16

Has been a while since the article, but at this moment I am stuck at Step 3 with the database connection. As the script doesn't define the password yo connect to the database.
Traceback (most recent call last):

File "/home/pi/sensor_logger/ifkit.py", line 4, in <module>

db = MySQLdb.connect("localhost","root","raspberry","sensor_log" )

File "build/bdist.linux-armv6l/egg/MySQLdb/__init__.py", line 81, in Connect

File "build/bdist.linux-armv6l/egg/MySQLdb/connections.py", line 187, in __init__

_mysql_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

Have seen these issues before but not in this connection setup, So at this moment a bit clueless how to either provide the password or to connect without.

author
JbT5 (author)2017-02-10

Can you update the code in the file "values.php" with PDO extension please ?

author
turbochardged (author)2016-11-17

can you update the instructions for Jessie OS and Apache2 2.4? I messed up and now i'm lost.

author
dikainstruct (author)2016-10-30

..to be more precise:

I fetch via a python-script every 5 seconds the following Data from my Fritbox 7490:

-maxUpLoad, maxDownLoad, meanUpload, meanDownLoad.

I like to show them all in a chart, to monitor my DSL-Traffic and Line capability. Any help is highly appreciated, regards Dirk

author
dikainstruct (author)2016-10-30

Hi - and also from me many, many thanks for this great tutoial! As a bloddy beginner I have a (hopefully) simple question: I have more than one sensor - how must I adapt the files data.js and values.php to show all sensor values in the hightchart? For your kindly assistance in advance many thanks for this great tutorial!

Dirk

author
cfadden (author)2015-10-18

Hi - thanks for putting this Instructable together!

I think there might be some issues in the MySQLdb section. To get things working I had to do the following:

1. The "wget" command is showing up in your instructions with the HTML code for a link instead of just a URL. The command needs the <a href="..."> and </a> parts removed from your instructions.

2. I could not get the "python setup.py build" command to work because "mysql_config" was missing. In order to find "mysql_config" I first had to run the following:

sudo apt-get install libmysqlclient-dev
author
emmeshop (author)cfadden2015-10-19

I corrected the mistakes, thanks