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. The computer must have a linux distribution, i used ubuntu 14.04.

Step 1: Setting up python

In order to communicate the sky mote with the mysql database a serial communication is needed. Later i will provide a python code that is in charge of getting the data from the sky mote and the save it into the mysql database. Python need 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.

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

1	`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 using 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 <stdio.h>

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

{

  return 10 * light_sensor.value(LIGHT_SENSOR_PHOTOSYNTHETIC) / 7;

}

/*---------------------------------------------------------------------------*/

static int

get_temp(void)

{

  return ((sht11_sensor.value(SHT11_SENSOR_TEMP) / 10) - 396) / 10;

}

/*---------------------------------------------------------------------------*/

int sequence = 0;

//Define the process code

PROCESS_THREAD(send_sensor_info_process, ev, data)

{

  static struct etimer et;

  PROCESS_BEGIN();

  while(1){

  etimer_set(&et, CLOCK_SECOND * 5);

  SENSORS_ACTIVATE(light_sensor);

  SENSORS_ACTIVATE(sht11_sensor);

  PROCESS_WAIT_EVENT_UNTIL(etimer_expired(&et));

  printf("Data\t", get_temp());

  printf("%d\t", sequence++);  

  printf("%d\t", get_temp());

  printf("%d\n", get_light()); 

  etimer_reset(&et);

  SENSORS_DEACTIVATE(light_sensor);

  SENSORS_DEACTIVATE(sht11_sensor);

  }

  PROCESS_END();

}

</stdio.h>

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

1	`/home/doctorado/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

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

Then, you must see in terminal the following

1 2	`Data 0 28 182` `Data 1 28 174`

Where the first column is the value of an integer 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 the sky mote sending 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, and the second command creates a table named weatherData.

CREATE DATABASE mote1;

USE mote1;

create table weatherData (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,secuencia 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 

  arduino = serial.Serial(device, 115200) 

except: 

  print "Failed to connect on",device    

while True:

 #open a cursor to the database

 cursor = dbConn.cursor() 

 try:  

   data = arduino.readline()  #read the data from the arduino

   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 (secuencia,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 Arduino!"

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

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

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

pccold

Saturday, 11 June 2016

Sending data from Sky motes to Mysql