Gerymake
Posts: 38
Joined: Sun Jan 06, 2019 11:38 pm

Storing multiple variables

Fri May 17, 2019 9:52 pm

Hello,

I can't seem to find a decent approach to my problem. I'm setting up an automade garden which is made up of several sensors collecting different types of data (soil moisture, light conditions, temperature...). I'd like to store all these variables in a MYSQL table under a same DATETIME, i.e., temperature, humidity, soil moisture... at 23:45 2019-05-17. This means that I should get the data simultaneously and insert it into the DB.

Whilst writing the script, I have defined several functions to read these values, but how should I store them (keep track of the previous sensor's data) so that I can upload them at the same time (under the same DATETIME parameter). I thought about using a dictionary, but in case a sensor fails and the process runs again, the sensor's previous value wouldn't be overwritten and should be uploaded again.

I wish you could understand. Should you require further information, please post a reply.

Thanks,
Gerard

Andyroo
Posts: 2823
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Storing multiple variables

Fri May 17, 2019 10:40 pm

Depends what you want to do when the sensor fails. If it takes an hour to recover then the temperature etc will be out.

You could have three entries for each reading:
Time
Reading value
Type of reading (eg sensor type)

It makes collating a bit of a chore but you can always just store the time so each sensor is reported at the same time.
Need Pi spray - these things are breeding in my house...

Gerymake
Posts: 38
Joined: Sun Jan 06, 2019 11:38 pm

Re: Storing multiple variables

Sat May 18, 2019 7:02 am

Andyroo wrote:
Fri May 17, 2019 10:40 pm
Depends what you want to do when the sensor fails. If it takes an hour to recover then the temperature etc will be out.

You could have three entries for each reading:
Time
Reading value
Type of reading (eg sensor type)

It makes collating a bit of a chore but you can always just store the time so each sensor is reported at the same time.
Then, what you put forward is just uploading every sensor value separately. Since there will be a negligible delay betweeen every upload, I could merge them together? I am not sure how to do that, I am a newbie at MYSQL.

ghp
Posts: 1317
Joined: Wed Jun 12, 2013 12:41 pm
Location: Stuttgart Germany
Contact: Website

Re: Storing multiple variables

Sat May 18, 2019 3:29 pm

Hello, some lines of code show can explain the concept. The assumption is that for each sensor a 'status' field is collected.

Code: Select all

import time
import random

class TestException(Exception):
    pass

def unexpected_source_of_trouble(func):
    """produce a simulation of random error"""
    def wrapper():
        if random.randrange(0,5) == 0:
            raise TestException("a really nasty and unexpected exception")
        return func()
    return wrapper


@unexpected_source_of_trouble
def sensor_1():
    return random.randrange(3456)
       
@unexpected_source_of_trouble
def sensor_2():
    return random.randrange(22)
       
def save_to_database( t, sensor_name, s, status):
    print("save to db: ", t, "sensor_name", sensor_name, s, status)
    

while True:
    time.sleep(1)  # quite fast
    current = time.time()
    s1 = 0    
    s1_status = "OK"
    
    s2 = 0
    s2_status = "OK"
    
    try:
        s1 = sensor_1()
    except TestException as e:
        s1_status = str(e)
    
    try:
        s2 = sensor_2()
    except TestException as e:
        s2_status = str(e)
        
    save_to_database( current, "S1", s1,  s1_status)
    save_to_database( current, "S2", s2,  s2_status)
        
With this concept, for each time stamp there is always a either good 'OK' record or a 'bad' record.

Gerymake
Posts: 38
Joined: Sun Jan 06, 2019 11:38 pm

Re: Storing multiple variables

Mon May 20, 2019 9:21 pm

Makes sense, thanks for the help! :D

Return to “Python”