Sending data from Sky motes to Mysql

Objective

The objective of this tutorial is to send temperature and light data from a sky mote connected to the USB port to a mysql database. This tutorial assumes that you already have mysql, python and contiki-3.0 running in your computer. This tutorial was tested in Ubuntu 14.04.

Step 1: Setting up python

In order to allow a communication between a sky mote and a mysql database a serial communication via the USB port is needed. Later in this tutorial, i will provide a python code that is in charge of getting the data from the sky mote and then save it into the mysql database. Python needs an extension called pySerial in order to be able to communicate with the serial port (USB). You can download the extension (pyserial-3.1.1.tar.gz) in the following link:

https://pypi.python.org/pypi/pyserial

Then, extract the archive, cd to the extracted folder and install the pySerial using the command.

python setup.py install

Now, you must install the MySQLdb library in order that python can talk with mysql. To that end, you must download the file MySQL-python-1.2.4b4.tar.gz from the following link:

http://sourceforge.net/projects/mysql-python/?source=dlp

Then, extract the archive, cd to the extracted folder and install the MySQLdb using the command.

python setup.py install

Step 2: Loading code to sky mote

The following code reads the temperature and light information from the sky mote, and then displays this information in the serial port (USB). This is a C code for Contiki-3.0

#include "contiki.h"
#include "/home/doctorado/contiki-3.0/dev/sht11/sht11-sensor.h"
#include "/home/doctorado/contiki-3.0/platform/sky/dev/light-sensor.h"
#include "/home/doctorado/contiki-3.0/core/dev/leds.h"
#include 

//Declare the process
PROCESS(send_sensor_info_process, "Print the Sensors Information");

//Make the process start when the module is loaded
AUTOSTART_PROCESSES(&send_sensor_info_process);

/*---------------------------------------------------------------------------*/
static int
get_light(void) //Get the light value from sensor
{
  return 10 * light_sensor.value(LIGHT_SENSOR_PHOTOSYNTHETIC) / 7;
}
/*---------------------------------------------------------------------------*/
static int
get_temp(void) //Get the temperature value from sensor
{
  return ((sht11_sensor.value(SHT11_SENSOR_TEMP) / 10) - 396) / 10;
}
/*---------------------------------------------------------------------------*/

int sequence = 0; // A sequence number that enumerates the data from 0 and increases in 1.

//Define the process code
PROCESS_THREAD(send_sensor_info_process, ev, data)
{


  static struct etimer et; // Struct used for the timer

  PROCESS_BEGIN(); 

 
  while(1){
 
  etimer_set(&et, CLOCK_SECOND * 5); // Configure timer to expire in 5 seconds

  SENSORS_ACTIVATE(light_sensor); // Activate light sensor
  SENSORS_ACTIVATE(sht11_sensor); // Activate temperature sensor

  PROCESS_WAIT_EVENT_UNTIL(etimer_expired(&et)); // Wait until timer expires 

  printf("Data\t", get_temp()); // Print the string "Data"
  printf("%d\t", sequence++);  // Print the sequence number
  printf("%d\t", get_temp()); // Print the temperature value
  printf("%d\n", get_light());  // Print the light value


  etimer_reset(&et); // Reset timer

  SENSORS_DEACTIVATE(light_sensor); // Deactivate light sensor
  SENSORS_DEACTIVATE(sht11_sensor);  // Deactivate temperature sensor

  }
  
  PROCESS_END();
}

Copy the code to an archive named TemperatureSky.c in the following folder of contiki-3.0

/home/YOUR_USER/contiki-3.0/examples/sky

In order to compile, to load the binary file to the sky mote and to visualize the temperature and light data you must write in terminal the following command

 
make TARGET=sky MOTES=/dev/ttyUSB0 TemperatureSky.upload && make login

Then, you must see in terminal the following

Data 0 28 182
Data 1 28 174

Where the first column is the sequence number which starts in 0 and increase always its value 1. I used this in order to enumerate the read data. The second column is the temperature value and the third column is the light value. Until now, you have configured the sky mote to send the temperature and light information via the serial port (USB). Then you must stop seeing the data pressing ctrl + c. This is important because you cann't see the data and run the python code at the same time.

Step 3: Creating a database in Mysql

To create a database and a table in Mysql which is appropiate for this application you must write the following commands in mysql. The first command creates a database called mote1, the second command uses this database, and the third command creates a table named weatherData. This table has 4 columns id, sequence, tempC and light.

CREATE DATABASE mote1;
USE mote1;
create table weatherData (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,sequence INT(11) NOT NULL,tempC INT(4) NOT NULL,light INT(4) NOT NULL);

Step 4: Creating a python code to read the temperature and light data and save it into mysql

The following python code reads the serial port (USB) and saves the temperature and light data into mysql, specifically python saves the information in the database named mote1 and in the table called weatherData.

#!/usr/bin/python
import serial 
import MySQLdb

#establish connection to MySQL. You'll have to change this for your database.
#dbConn = MySQLdb.connect("localhost","database_username","password","database_name") or die ("could not connect to database")
dbConn = MySQLdb.connect("localhost","root","1234","mote1") or die ("could not connect to database")
#open a cursor to the database
#cursor = dbConn.cursor()

#device = '/dev/tty.usbmodem1411' #this will have to be changed to the serial port you are using
device = '/dev/ttyUSB0' #this will have to be changed to the serial port you are using
try:
  print "Trying...",device 
  skyMote = serial.Serial(device, 115200) 
except: 
  print "Failed to connect on",device    

while True:
 #open a cursor to the database
 cursor = dbConn.cursor() 
 try:  
   data = skyMote.readline()  #read the data from the sky Mote
   pieces = data.split("\t")  #split the data by the tab
   print "pieces[0]=",pieces[0]
   print "pieces[1]=",pieces[1]
   print "pieces[2]=",pieces[2]
   print "pieces[2]=",pieces[3]
   #Here we are going to insert the data into the Database
   try:
     cursor.execute("INSERT INTO weatherData (sequence,tempC,light) VALUES (%s,%s,%s)", (pieces[1],pieces[2],pieces[3]))
     dbConn.commit() #commit the insert
     cursor.close()  #close the cursor
   except MySQLdb.IntegrityError:
     print "failed to insert data"
   finally:
     cursor.close()  #close just incase it failed
 except:
   print "Failed to get data from Sky mote!"

Copy the code in an archive named serial-mysql.py. Then run the code with the following command

python serial-mysql.py

Then, the you must see this in the terminal.

Dataes[0]= 
pieces[1]= 0
pieces[2]= 28
pieces[2]= 181

Dataes[0]= �
pieces[1]= 1
pieces[2]= 28
pieces[2]= 175

Finally, in order to see the table weatherData with the above values, type the following command in mysql

select * from weatherData;

You must see the following. That indicates that the values of temperature and light read by the sky mote are being saved in the mysql table.

+----+-----------+-------+-------+
| id | sequence  | tempC | light |
+----+-----------+-------+-------+
|  1 |         0 |    28 |   181 |
|  2 |         1 |    28 |   175 |