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:
/*-----( 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:
## 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!!!