Arduino Yun: Storing Data in Mysql installed on Linino

Hi,

i want to store data from a temperature sensor connected in A0 pin.

In linino side with mysql-server, i created a sql database called 'ARDUINO' which contains a table, called 'temperatura' with 3 columns: 'id', 'data' and 'tempdegree'.

How can Arduino fill this table with the sensor Data Output?

Anybody can help me?

Best Regards,

nlourenco

  1. Install mySQL server:

http://wiki.openwrt.org/doc/howto/lamp

  1. Setup python-mysql

http://forum.arduino.cc/index.php?topic=206746.msg1524868#msg1524868

mysql> CREATE DATABASE ARDUINO;
mysql> USE ARDUINO;
mysql> CREATE TABLE temperatura (id INT, data VARCHAR(20), tempdegree VARCHAR(20));
mysql> SHOW TABLES;
opkg update
opkg install python-mysql
nano /mnt/sda1/mysql.py
#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys, string, os
#print sys.argv


try:
      con = _mysql.connect('127.0.0.1', 'root', 'new-password', 'ARDUINO')
      #con.query("SELECT VERSION()")
      #result = con.use_result()
      #print "MySQL version: %s" %  result.fetch_row()[0]
    sqlstr="INSERT INTO temperatura (id,data ,tempdegree) VALUES( " + sys.argv[1] +", '" + sys.argv[2] + "', '" + sys.argv[3] + "')"
    #print  sqlstr
    con.query(sqlstr)
    
except _mysql.Error, e:
  
      print "Error %d: %s" % (e.args[0], e.args[1])
      sys.exit(1)

finally:
    
      if con:
                con.close()
chmod 755  /mnt/sda1/mysql.py  
/mnt/sda1/mysql.py  2  'test1' 'test2'

confirm data insert into table.

  1. ATmega32u4 side.

http://arduino.cc/en/Tutorial/Process

void insertdb() {
  Process p;            
  p.begin("/mnt/sda1/mysql.py");      
  p.addParameter("1"); 
  p.addParameter("32"); 
  p.addParameter("64"); 
  p.run();

Thanks :D

I will try

Revision 2:

Setup timezone and NTP ( RTC) at LUCI

Create table with AUTO_INCREMENT and TIMESTAMP.

CREATE TABLE temperatura_new (
id INT NOT NULL AUTO_INCREMENT,
tempdegree VARCHAR(20),
insert_date TIMESTAMP,
PRIMARY KEY (id)
);

Insert test data.

INSERT INTO temperatura_new (tempdegree) VALUES ( '32');
INSERT INTO temperatura_new (tempdegree) VALUES ( '34');
select * from temperatura_new;
+----+------------+---------------------+
| id | tempdegree | insert_date         |
+----+------------+---------------------+
|  1 | 32         | 2014-02-03 10:08:51 |
|  2 | 34         | 2014-02-03 10:08:57 |
+----+------------+---------------------+

The python script now only need pass one parameter only.

Thanks :D So the pyhton code, comes like this: sqlstr="INSERT INTO temperatura (tempdegree) VALUES( " + sys.argv[1] +")" ???

I have some doubts in the arduino code, to get the data from sensor A0:

int sensorValue = analogRead(A0);
void insertdb() {
  Process p;            
  p.begin("/mnt/sda1/mysql.py");      
  p.addParameter("sensorValue"); 

  p.run();

it returns me an error.

Best Regards

sqlstr="INSERT INTO temperatura (tempdegree) VALUES( '" + sys.argv[1] +"')"

Run

/mnt/sda1/mysql.py    '32'

at console to confirm data insert into table .

Please post error message.

Hi,

I’ve tried to do what you have said.

What i do:

  • I created the file mysql.py, and i copy to the mini sd root, in the path /mnt/sda1/mysql.py
  • My file mysql.py, looks like this:#!/usr/bin/python

-- coding: utf-8 --

import _mysql
import sys, string, os
#print sys.argv

try:
con = _mysql.connect(‘192.168.0.104’, ‘root’, ‘mypassword’, ‘homeautomation’)
#con.query(“SELECT VERSION()”)
#result = con.use_result()
#print “MySQL version: %s” % result.fetch_row()[0]
sqlstr=“INSERT INTO NossoTeste (valor ,tensao) VALUES( " + sys.argv[1] +”, ‘" + sys.argv[2] + "’)"
#print sqlstr
con.query(sqlstr)

except _mysql.Error, e:

print “Error %d: %s” % (e.args[0], e.args[1])
sys.exit(1)

finally:

if con:
con.close()

  • When i try to run it, in the linino console, with the command: /usr/bin/python /mnt/sda1/mysql.py , it returns me an error :" Error 2002: Can’t connect to local MySQL server through socket ‘/tmp’ (146)
    Traceback (most recent call last):
    File “/mnt/sda1/mysql.py”, line 25, in
    if con:
    NameError: name ‘con’ is not defined

Can you help me please?

My bind adress, in the mysql server is defined by arduino IP in my lan, in this case 192.168.0.104.
I have to do that, to link the toad software installed on my PC, to the database installed on linino side.

Thanks,

use "127.0.0.1"

You mean, use 127.0.0.1 in bind-adress??

I can't do that.

If i put 127.0.0.1 in bind-adress, i will no longer available, to connect my db software installed on my PC, to the db stored in Arduino.

I think, if i put 0.0.0.0, MySQL will listen all available interfaces.

Tks

Python code:

try:
      con = _mysql.connect('127.0.0.1', 'root', 'new-password', 'ARDUINO')

At my.conf if bind-address = 0.0.0.0 is not working,

# bind-address = 127.0.0.1  # comment this line out

mysqld will be listening on 0.0.0.0:3306.

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'new-password';

grant right to root@anyipaddress.

Tks, i put 0.0.0.0 in bind adress, and now i can write values on my db table, stored in Linino through an IDE installed on my PC.

So I pick your code anda i made some changes:

try:
      con = _mysql.connect('192.168.0.100', 'NSL', 'teste', 'homeautomation')
      #con.query("SELECT VERSION()")
      #result = con.use_result()
      #print "MySQL version: %s" %  result.fetch_row()[0]
    
    sqlstr="INSERT INTO NossoTeste (valor,tensao) VALUES(1,7)"
    #print  sqlstr
    con.query(sqlstr)

The Values 1 and 7 were inserted on my 'Nosso Teste' Table.

Then i've tried to do something different:

valio= 5
tension= 4.88

try:
      con = _mysql.connect('192.168.0.100', 'NSL', 'teste', 'homeautomation')
      #con.query("SELECT VERSION()")
      #result = con.use_result()
      #print "MySQL version: %s" %  result.fetch_row()[0]
    
    sqlstr=("INSERT INTO NossoTeste (valor,tensao) VALUES(%s,%s)",(valio,tension))
    #print  sqlstr
    con.query(sqlstr)

And it returns me an error: TypeError: must be string or read-only buffer, not tuple

Do you know how to solve it? Sorry i'm a newbie at python!

Tks

mysql -uroot -p

get mysql console:

use homeautomation;
show create table NossoTeste;

post the result here.

at python code:

print  sqlstr
#con.query(sqlstr)

post the result here.

mysql result:

mysql> show create table NossoTeste;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                           |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NossoTeste | CREATE TABLE `NossoTeste` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `valor` int(11) DEFAULT NULL,
  `tensao` decimal(3,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Python Code:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys, string, os
#print sys.argv
valio= 5
tension= 4.88

try:
      con = _mysql.connect('192.168.0.100', 'NSL', 'teste', 'homeautomation')
      #con.query("SELECT VERSION()")
      #result = con.use_result()
      #print "MySQL version: %s" %  result.fetch_row()[0]
    
    sqlstr=("INSERT INTO NossoTeste (valor,tensao) VALUES(%s,%s)",(valio,tension))
    print  sqlstr
#   con.query(sqlstr)
    
except _mysql.Error, e:
  
      print "Error %d: %s" % (e.args[0], e.args[1])
      sys.exit(1)

finally:
    
      if con:
                con.close()

Python Result:

Python 2.7.6 (default, Nov 10 2013, 19:24:18) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.

Imported NumPy 1.8.0, SciPy 0.13.3, Matplotlib 1.3.1
+ guidata 1.6.1, guiqwt 2.3.1
Type "scientific" for more details.
>>> runfile('C:/Users/Nuno/.spyder2/mysql.py', wdir=r'C:/Users/Nuno/.spyder2')
('INSERT INTO NossoTeste (valor,tensao) VALUES(%s,%s)', (5, 4.88))
>>>

I do this:

root@Arduino:~# /usr/bin/python /mnt/sda1/mysql.py 1000 2

:slight_smile: and it was inserted in my db, the values 1000 and 2, id nº 17:

mysql> SELECT * from NossoTeste;
+----+-------+--------+
| id | valor | tensao |
+----+-------+--------+
|  1 |     1 |   NULL |
|  2 |     0 |   0.00 |
|  3 |     0 |   0.00 |
|  4 |     0 |   0.00 |
|  5 |     2 |   4.00 |
|  6 |  NULL |   NULL |
|  7 |  NULL |   NULL |
|  8 |     6 |   4.00 |
|  9 |     0 |   0.00 |
| 10 |     0 |   0.00 |
| 11 |     0 |   0.00 |
| 12 |     0 |   0.00 |
| 13 |     5 |   6.00 |
| 14 |     1 |   4.00 |
| 15 |     4 |   8.00 |
| 16 |     1 |   7.00 |
| 17 |  1000 |   2.00 |
| 18 |  1024 |   4.88 |
+----+-------+--------+
18 rows in set (0.00 sec)

Then In arduino code, i put this:

#include <Process.h>
void setup() {
  // put your setup code here, to run once:
Bridge.begin();
}

void loop() {
  // put your main code here, to run repeatedly:

}
void insertdb() {
  Process p;            
  p.begin("/usr/bin/python /mnt/sda1/mysql.py");      
  p.addParameter("1");  
  p.addParameter("64"); 
  p.run();
  }

But it didn’t work, values 1 and 64 weren’t inserted in my db.

What i’m doing wrong?

I found the solution ehehe

#include <Process.h>
void setup() {
  // put your setup code here, to run once:
Bridge.begin();
insertdb();
}

void loop() {
  // put your main code here, to run repeatedly:

}
void insertdb() {
  Process p;            
  p.begin("/usr/bin/python");      
  p.addParameter("/mnt/sda1/mysql.py");
  p.addParameter("10");  
  p.addParameter("2.27"); 
  p.run();
  }

Now it works :smiley:

You are missing "#!/usr/bin/python" at beginning of python.

That's strange... because i've already that line in my code:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys, string, os
#print sys.argv


try:
      con = _mysql.connect('127.0.0.1', 'root', '123456789', 'homeautomation')
      #con.query("SELECT VERSION()")
      #result = con.use_result()
      #print "MySQL version: %s" %  result.fetch_row()[0]
    sqlstr="INSERT INTO NossoTeste (valor ,tensao) VALUES( " + sys.argv[1] +", '" + sys.argv[2] + "')"
    #print  sqlstr
    con.query(sqlstr)
    
except _mysql.Error, e:
  
      print "Error %d: %s" % (e.args[0], e.args[1])
      sys.exit(1)

finally:
    
      if con:
                con.close()

If i put, in the command line just: /mnt/sda1/mysql.py -ash: /mnt/sda1/mysql.py: not found

One more problem:

if i put values in addparameter, this work perfectly.
Ex:

#include <Process.h>
void setup() {
  // put your setup code here, to run once:
Bridge.begin();
insertdb();
}

void loop() {
  // put your main code here, to run repeatedly:

}
void insertdb() {
  Process p;            
  p.begin("/usr/bin/python");      
  p.addParameter("/mnt/sda1/mysql.py");
  p.addParameter("10");  
  p.addParameter("2.27"); 
  p.run();
  }

But if i put values from a variable, it returns an error.
Ex:

#include <Process.h>
int sensorPin = 0;
int teste1 = 1005;
int teste2= 4.23;
void setup() {
  // put your setup code here, to run once:
Serial.begin(9600);
Serial.println("Bit \t Tensao (V)");
Bridge.begin();
 insertdb(teste1, teste2);
}

void loop() {
  // put your main code here, to run repeatedly:
int reading = analogRead(sensorPin);
float tensao = reading * 4.88;
 tensao /= 1024.0;

Serial.print(reading);
 Serial.print("\t");
 Serial.print("\t");
 
 Serial.print(tensao);
 Serial.print("\t");
 Serial.print("\n");
  delay(1000);
}
void insertdb(int x, int y) {
  Process p;            
  p.begin("/usr/bin/python");      
  p.addParameter("/mnt/sda1/mysql.py");
  p.addParameter(x);  
  p.addParameter(y); 
  p.run();
  }

the error is:
Arduino: 1.5.6-r2 (Windows 8), Placa:“Arduino Yún”

ler_tensoes.ino: In function ‘void insertdb(int, int)’:
ler_tensoes:32: error: conversion from ‘int’ to ‘const String’ is ambiguous
C:\Program Files (x86)\Arduino\hardware\arduino\avr\cores\arduino/WString.h:61: note: candidates are: String::String(const __FlashStringHelper*)
C:\Program Files (x86)\Arduino\hardware\arduino\avr\cores\arduino/WString.h:59: note: String::String(const char*)
ler_tensoes:33: error: conversion from ‘int’ to ‘const String’ is ambiguous
C:\Program Files (x86)\Arduino\hardware\arduino\avr\cores\arduino/WString.h:61: note: candidates are: String::String(const __FlashStringHelper*)
C:\Program Files (x86)\Arduino\hardware\arduino\avr\cores\arduino/WString.h:59: note: String::String(const char*)

Do you know how to solve it?

Best Regards

String stringOne =  String(analogRead(0), DEC);     // using an int and a base
String stringOne =  String(13);                     // using a constant integer
...
void insertdb(String x, String y) {
...

http://arduino.cc/en/Tutorial/StringConstructors#.UxwMAvldVtU

Thanks :D it worked