Go Down

Topic: Arduino Yun: Storing Data in Mysql installed on Linino (Read 14304 times) previous topic - next topic

nlourenco

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


sonnyyu

1. Install mySQL server:

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

2. Setup  python-mysql

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

Code: [Select]
mysql> CREATE DATABASE ARDUINO;
mysql> USE ARDUINO;
mysql> CREATE TABLE temperatura (id INT, data VARCHAR(20), tempdegree VARCHAR(20));
mysql> SHOW TABLES;


Code: [Select]
opkg update
opkg install python-mysql


Code: [Select]
nano /mnt/sda1/mysql.py


Code: [Select]
#!/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()


Code: [Select]
chmod 755  /mnt/sda1/mysql.py 
/mnt/sda1/mysql.py  2  'test1' 'test2'


confirm data insert into table.

3. ATmega32u4  side.

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

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


sonnyyu

Revision 2:

Setup  timezone and NTP ( RTC) at LUCI



Create table with AUTO_INCREMENT and TIMESTAMP.

Code: [Select]
CREATE TABLE temperatura_new (
id INT NOT NULL AUTO_INCREMENT,
tempdegree VARCHAR(20),
insert_date TIMESTAMP,
PRIMARY KEY (id)
);


Insert test data.

Code: [Select]
INSERT INTO temperatura_new (tempdegree) VALUES ( '32');
INSERT INTO temperatura_new (tempdegree) VALUES ( '34');


Code: [Select]
select * from temperatura_new;

Code: [Select]
+----+------------+---------------------+
| 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.

nlourenco

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:

Code: [Select]

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

sonnyyu

Code: [Select]
sqlstr="INSERT INTO temperatura (tempdegree) VALUES( '" + sys.argv[1] +"')"
Run
Code: [Select]
/mnt/sda1/mysql.py    '32'
at console to confirm data insert into table .

Please post error message.

nlourenco

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 <module>
        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,

sonnyyu


nlourenco

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

sonnyyu

#9
Mar 07, 2014, 12:42 am Last Edit: Mar 07, 2014, 12:55 am by sonnyyu Reason: 1
Python code:

Code: [Select]
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,

Code: [Select]
# bind-address = 127.0.0.1  # comment this line out

mysqld will be listening on 0.0.0.0:3306.

Code: [Select]
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'new-password';

grant right to root@anyipaddress.

nlourenco

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:
Code: [Select]
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:

Code: [Select]

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

sonnyyu

#11
Mar 07, 2014, 03:41 am Last Edit: Mar 07, 2014, 03:45 am by sonnyyu Reason: 1
Code: [Select]
mysql -uroot -p

get mysql console:

Code: [Select]
use homeautomation;
show create table NossoTeste;


post the result here.

at python code:

Code: [Select]
print  sqlstr
#con.query(sqlstr)


post the result here.

nlourenco

mysql result:
Code: [Select]

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:
Code: [Select]
#!/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:

Code: [Select]
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))
>>>

nlourenco

I do this:
Code: [Select]
root@Arduino:~# /usr/bin/python /mnt/sda1/mysql.py 1000 2

:) and it was inserted in my db, the values 1000 and 2, id nº 17:
Code: [Select]
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:

Code: [Select]
#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?

nlourenco

I found the solution ehehe

Code: [Select]

#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 :D


Go Up