Reading values from mySQL (located on a Pi) to arduino mega using python?

hi everyone

i have problem requires guidance im having problems finding solution through google.

in nutshell: arduino mega 2560 attached (via rx,tx) raspberry pi. pi contains lamp , mysql. arduino sketch takes readings of sensors (light, temp1, temp2) , stores these values on mysql via python script. can read mysql values , display them on webpage, can alter different set of values (used control relays) , succesfully store them in mysql.

the problem: can't figure out how read values mysql database through python , arduino control relays. there 2 data types need importing arduino. first set time data types (e.g. hh:mm:ss) , second integer values. these data values stored in same table (e.g. relay table) different table sensor readings.

my arduino sketch code far:

code: [select]


/*-----( import needed libraries )-----*/
#include <onewire.h>
#include <dallastemperature.h>
#include <wire.h>
#include <rtclib.h>

/*-----( declare constants , pin numbers )-----*/
#define photopin a0
const int temp_pin = a1;

#define relay_on 1
#define relay_off 0
#define relay_1  30  // arduino digital i/o pin number
#define relay_2  31
#define relay_3  32
#define relay_4  33
#define relay_5  34  // arduino digital i/o pin number
#define relay_6  35
#define relay_7  36
#define relay_8  37  // arduino digital i/o pin number

/*-----( declare objects )-----*/
// setup onewire instance communicate onewire devices
onewire onewire(temp_pin);
// pass our onewire reference dallas temperature.
dallastemperature sensors(&onewire);
//real time clock
rtc_ds1307 rtc;

/*-----( declare variables )-----*/
// assign addresses of 1-wire temp sensors.
deviceaddress probe1 = { 0x28, 0x64, 0x23, 0xbd, 0x03, 0x00, 0x00, 0x5f };
deviceaddress probe2 = { 0x28, 0xab, 0x10, 0xbd, 0x03, 0x00, 0x00, 0x2e };

float photolevel;
float dallas1;
float dallas2;
char photo1[10];
char temp1[10];
char temp2[10];

/*----( setup: runs once )----*/
void setup() {
  serial.begin(9600);
  sensors.begin();     //get ds18b20 temperatures
  sensors.setresolution(probe1, 10); //set resolution 10bit
  sensors.setresolution(probe2, 10); //set resolution 10bit
  wire.begin();        // start wire (i2c communications)
  rtc.begin();         // start rtc chip
 
  digitalwrite(relay_1, relay_off); //relays
  digitalwrite(relay_2, relay_off);
  digitalwrite(relay_3, relay_off);
  digitalwrite(relay_4, relay_off);   
  digitalwrite(relay_5, relay_off);
  digitalwrite(relay_6, relay_off);
  digitalwrite(relay_7, relay_off);
  digitalwrite(relay_8, relay_off);
 
  pinmode(relay_1, output); //set relays outputs
  pinmode(relay_2, output); 
  pinmode(relay_3, output); 
  pinmode(relay_4, output);   
  pinmode(relay_5, output);   
  pinmode(relay_6, output); 
  pinmode(relay_7, output); 
  pinmode(relay_8, output);
}
/*--(end setup )---*/

/****** loop: runs ******/
void loop() {
  readsensors();
  //makedecisions();
  //relays();
  delay(2000);
}

/****** read sensors ******/
void readsensors()
{
  datetime = rtc.now();  //get time rtc
  photolevel = analogread(photopin);  //read light level
 
  sensors.requesttemperatures();
  dallas1 = sensors.gettempc(probe1);
  dallas2 = sensors.gettempc(probe2);
 
  dtostrf(photolevel, 1, 0, photo1);
  dtostrf(dallas1, 1, 2, temp1);
  dtostrf(dallas2, 1, 2, temp2);
 
  string tempasstring1 = string(photo1);
  string tempasstring2 = string(temp1);
  string tempasstring3 = string(temp2);
   
  serial.print(now.unixtime());
  serial.print(" ");
  serial.print(now.year(), dec);
  serial.print('/');
  serial.print(now.month(), dec);
  serial.print('/');
  serial.print(now.day(), dec);
  serial.print(" ");
  serial.print(now.hour(), dec);
  serial.print(':');
  serial.print(now.minute(), dec);
  serial.print(" "); 
  serial.println(tempasstring1 + " " + tempasstring2 + " " + tempasstring3);
}

void makedecisions()
{
}

void relays()
{
}

the "makedecisions" , "relays" voids empty used test whether relays work, do.

python script far:

code: [select]

## scripts updates current values received arduino

#import libraries
import serial
import string
import mysqldb
import pprint

#connectes database
db = mysqldb.connect(host="localhost", # host, localhost
                     user="***", # username
                     passwd="***", # password
                     db="arduino") # name of data base
cur = db.cursor()
arduinoport= '/dev/ttyama0'
ser = serial.serial(baudrate=9600)
ser.setport(arduinoport)

ser.settimeout(2)

try:
ser.open()
except:
print('port error!')

else:

ardstring = ser.readline()
#print(ardstring)
valuematrix= ardstring.split(' ')
if len(valuematrix)> 1 :
pprint.pprint(valuematrix)
unix = valuematrix[0]
date = valuematrix[1]
time = valuematrix[2]
light = valuematrix[3]
temp1 = valuematrix[4]
temp2 = valuematrix[5]
#print("value received:"+string+ " interpreted as: project id = "+projectid+" , value = "+value)
cur.execute('update currentpoints set date="'+date+'", time="'+time+'", light="'+light+'", temperature1="'+temp1+'", temperature2="'+temp2+'" id=1')
db.commit()

ser.close()
print('connection closed')
db.close()
print('database closed')
print('end')


i welcome suggestions how working. have limited programming experience have tought myself go along, warning if of code seems bit dodgy, feedback valuable!!!

i'd suggest breaking problem down bit , solving subsets of it. start sending data python using ser instance mega.

probably better use different serial port on mega can use serial debug using ide terminal.

initially, echo python, build parsing dummied literal data, pull data database.

it'll lot harder whole thing in 1 go, stepwise refinement should there eventually, or @ least far enough can ask specific here.

Arduino Forum > Using Arduino > Networking, Protocols, and Devices (Moderator: fabioc84) > Reading values from mySQL (located on a Pi) to arduino mega using python?

arduino