Go Down

Topic: Reading values from mySQL (located on a Pi) to arduino mega using python? (Read 2559 times) previous topic - next topic

Damo1984

Hi everyone

I have a problem which requires some guidance which im having problems finding the solution through google.

In a nutshell: An arduino MEGA 2560 is attached (via rx,tx) to a raspberry pi. The Pi contains LAMP and mySQL. The arduino sketch takes readings of some sensors (light, temp1, temp2) and stores these values on mySQL via a python script. I can read the mySQL values and display them on a webpage, I can alter different set of values (used to control relays) and succesfully store them in mySQL.

THE PROBLEM: I can't figure out how to read values from the mySQL database through python and ultimately into the arduino which will then control relays. There are two data types which need importing to arduino. The first set are time data types (e.g. HH:mm:ss) and the second are integer values. All these data values are stored in the same table (e.g. relay table) which is a different table to sensor readings.

My arduino sketch code so far:

Code: [Select]

/*-----( Import needed libraries )-----*/
#include <OneWire.h>
#include <DallasTemperature.h>
#include <Wire.h>
#include <RTClib.h>

/*-----( Declare Constants and 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 a oneWire instance to communicate with any OneWire devices
OneWire oneWire(temp_pin);
// Pass our oneWire reference to Dallas Temperature.
DallasTemperature sensors(&oneWire);
//Real Time Clock
RTC_DS1307 RTC;

/*-----( Declare Variables )-----*/
// Assign the addresses of your 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 to 10bit
  sensors.setResolution(probe2, 10); //set resolution to 10bit
  Wire.begin();        // Start the Wire (I2C communications)
  RTC.begin();         // Start the 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 as 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 CONSTANTLY ******/
void loop() {
  ReadSensors();
  //MakeDecisions();
  //Relays();
  delay(2000);
}

/****** Read Sensors ******/
void ReadSensors()
{
  DateTime now = RTC.now();  //Get time from 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" and "Relays" voids are empty now but where previously used to test whether the relays actually work, which they do.

Python script so far:

Code: [Select]
## This scripts updates the current values received from the arduino

#Import libraries
import serial
import string
import MySQLdb
import pprint

#Connectes to database
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="***", # your username
                     passwd="***", # your password
                     db="arduino") # name of the 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+" and value = "+value)
cur.execute('UPDATE currentpoints SET date="'+date+'", time="'+time+'", light="'+light+'", temperature1="'+temp1+'", temperature2="'+temp2+'" WHERE id=1')
db.commit()

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


I welcome any suggestions as to how I get this working. I have limited programming experience but have tought myself as I go along, just a warning if any of the code seems a bit dodgy, any feedback is valuable!!!


wildbill

I'd suggest breaking the problem down a bit and solving some subsets of it. Start with just sending data from Python using your ser instance to the Mega.

Probably better to use a different serial port on the Mega so you can use Serial to debug using the IDE terminal.

Initially, just echo what you get from Python, then build up to parsing some dummied up literal data, then pull that data from the database.

It'll be a lot harder to do the whole thing in one go, but stepwise refinement should get you there eventually, or at least far enough that you can ask for some specific help here.

PaulS

Quote
THE PROBLEM: I can't figure out how to read values from the mySQL database through python

Seeing as how Python doesn't run on the Arduino, I think you'd be better off asking how to do it on a Python forum.

Or, bite the bullet and learn to create an executable, using C/C++, to do the work on the Pi.

In any case, it's not an Arduino issue.

Damo1984


Quote

Or, bite the bullet and learn to create an executable, using C/C++, to do the work on the Pi.

In any case, it's not an Arduino issue.


Other than taking my "problem" question a bit out of context I do appreciate the feedback. I do feel however that receiving data from the python script into the arduino is actually partly aruino issue.


I'd suggest breaking the problem down a bit and solving some subsets of it. Start with just sending data from Python using your ser instance to the Mega.

Probably better to use a different serial port on the Mega so you can use Serial to debug using the IDE terminal.

Initially, just echo what you get from Python, then build up to parsing some dummied up literal data, then pull that data from the database.

It'll be a lot harder to do the whole thing in one go, but stepwise refinement should get you there eventually, or at least far enough that you can ask for some specific help here.


Thanks wildbill for the feedback. Yes I've been using that startegy from the very beginning, thats how I managed to get data from arduino to website. I've got this far without asking a single question but think I need to pull my hair out a bit more and come back with a more "specific question". I thought I was specific with what I needed, comparing other posts on these forums but appears Im wrong. 

wildbill

I have never used python, but a little googling shows the existence of the write command, so if I were working on this, I'd throw this in before the ser.close:
Code: [Select]

ser.write('<6,10:05:30,1,2,11:04:25,0>')


Being a send of a literal packet of commands to control the relays. Angle brackets are the packet delimiters, commands have three parameters - relay number, time, relay state, so the first three say turn relay 6 on at 10:05:30. Obviously you can devise whatever protocol you like.

Then write some string parsing on the arduino (many examples in the forums) and work the relays. Only once that's working would I worry about the specifics of having Python read the data from MySQL. I'd also separate out the transmission of data from the arduino from it getting times for the relay actions, but for your initial tests, it doesn't matter.

PaulS

Quote
Then write some string parsing on the arduino (many examples in the forums) and work the relays.

Here's some code to capture that string:
Code: [Select]

#define SOP '<'
#define EOP '>'

bool started = false;
bool ended = false;

char inData[80];
byte index;

void setup()
{
   Serial.begin(57600);
   // Other stuff...
}

void loop()
{
  // Read all serial data available, as fast as possible
  while(Serial.available() > 0)
  {
    char inChar = Serial.read();
    if(inChar == SOP)
    {
       index = 0;
       inData[index] = '\0';
       started = true;
       ended = false;
    }
    else if(inChar == EOP)
    {
       ended = true;
       break;
    }
    else
    {
      if(index < 79)
      {
        inData[index] = inChar;
        index++;
        inData[index] = '\0';
      }
    }
  }

  // We are here either because all pending serial
  // data has been read OR because an end of
  // packet marker arrived. Which is it?
  if(started && ended)
  {
    // The end of packet marker arrived. Process the packet

    // Reset for the next packet
    started = false;
    ended = false;
    index = 0;
    inData[index] = '\0';
  }
}


Where it says "Process the packet", strtok() would be useful. Both commas and colons are delimiters.

Damo1984

Thanks PaulS and wildbill

Yes thats exactly what I was after, a simple example of putting it all together. I did see posts with
Code: [Select]
ser.write() and
Code: [Select]
while(Serial.available() > 0) but had no clue how they meshed together. With that said ill be working on getting the rest to work.

I'm so frustrated with myself because for a long time I just wasn't seeing anything through the serial monitor. Turns out the serial monitor was reading from my debian laptop rather than the raspberry pi serial. As I don't know a way around this, I just copied over the scripts to my laptop for testing and works perfect!

Cheers,
Damo



SurferTim

And you are not using the usb port on the Mega? The tx and rx pins are also connected to the usb port.

Are you connected to a TTL serial port on the Pi and not a RS-232 port, or are you using a device like the MAX232 to convert the signal levels from TTL to RS232?

Damo1984


And you are not using the usb port on the Mega? The tx and rx pins are also connected to the usb port.

Are you connected to a TTL serial port on the Pi and not a RS-232 port, or are you using a device like the MAX232 to convert the signal levels from TTL to RS232?



I've taken this up as a hobby for a few months so don't take my word for it but I'm really new to all this.

The mega-pi matchup is solely connected to one another through RX and TX, I only use the mega USB when I'm uploading sketches. I do however use a logic converter between the two.

Does that answer your question?

SurferTim

Quote
Does that answer your question?

Some of them. So you are powering the Mega with an external power supply and you disconnect the usb when you run the sketch with the Pi connected?

I usually don't do that. I use the usb port for debugging and another hardware port for additional serial comm. I use D18 and D19 for TTL serial, and access that port with Serial1 rather than Serial.
Code: [Select]
void setup() {
  Serial.begin(9600); // usb
  Serial1.begin(9600); // Serial1

  Serial.println("This prints to the usb port");
  Serial.read(); // This reads from the usb port

  Serial1.println("This prints to D18");
  Serial1.read(); // This reads from D19
}


Damo1984

At this stage the Mega and Pi are powered separately but I intend to use a common power supply in the future. The USB is connected to my laptop for debugging I guess and communication between the Pi and Mega uses RX0 and TX0.
When the python script works on my laptop Ill copy it over to the Pi and change the port name. In the past I didn't notice the serial communication problem because I wasn't reading data into the mega. I was simply using Putty and saw data populating mySQL and then onto apache from the Mega. I find sending data the opposite direction way more involved.

Code: [Select]
void setup() {
 Serial.begin(9600); // usb
 Serial1.begin(9600); // Serial1

 Serial.println("This prints to the usb port");
 Serial.read(); // This reads from the usb port

 Serial1.println("This prints to D18");
 Serial1.read(); // This reads from D19
}


Good idea, wildbill also mentioned that. I really need to fix that before I forget!!!


Damo1984

Thanks for the feedback!

Code: [Select]
#include <Wire.h>
#define SOP '<'
#define EOP '>'

bool started, started1 = false;
bool ended, ended1 = false;

char inData[80];
int var1,var2;
byte index, index1;

void setup()
{
   Serial.begin(115200);
   Wire.begin();
   // Other stuff...
}

void loop()
{
  // Read all serial data available, as fast as possible
  while(Serial.available() > 0)
  {
    char inChar = Serial.read();
    if(inChar == SOP)
    {
       index = 0;
       inData[index] = '\0';
       started = true;
       ended = false;
    }
    else if(inChar == EOP)
    {
       ended = true;
       break;
    }
    else
    {
      if(index < 79)
      {
        inData[index] = inChar;
        index++;
        inData[index] = '\0';
      }
    }
  }

  // We are here either because all pending serial
  // data has been read OR because an end of
  // packet marker arrived. Which is it?

  if(started && ended)
  {
    // The end of packet marker arrived. Process the packet
   
    char *token = strtok(inData, ",");
   
   
    if (*token != NULL)
    {
      index1 = 0;
      var[index1] = token; // **I know this wont work**
      started1 = true;
      ended1 = false;       
    }
   
    else if(*token == NULL)
    {
       ended1 = true;
       break;
    }
   
    else
    {
      index= index++
    }
   
         
    // Reset for the next packet
    started = false;
    ended = false;
    index = 0;
    inData[index] = '\0';
  }
}


I manged to get the string of values read to the serial port. If I understand correctly the strtok() splits a string into tokens and then atoi() converts the tokens into integers? How could I take the first token and store as say var1, and then store the second token as var2, etc?

I know the above code won't work but its the concept that I'm trying to figure out. 

PaulS

Quote
if (*token != NULL)
    {
      index1 = 0;
      var[index1] = token; // **I know this wont work**

If you are trying to store the value in a variable named var0, you can't. If you create an array, called var, you can save the value in the 0th element of the array.

What possible use is there for started1 and ended1?

Code: [Select]
      index= index++
Since i++ is equivalent to i = i + 1, this code is equivalent to
index = index = index + 1;
Looks kind of stupid that way, doesn't it?

Go Up