I am making a vivarium with a Pi to control the lights and some small PC fans to help control humidity/mold. Ultimately I was hoping to have some python scripts running to control these elements on a particular schedule but also be able to alter the schedule depending on my whim (stop lights/fans coming on if I am on nights etc). I was hoping to be able to alter the times etc. either with another computer or even an android app.
Getting the lights to come on/off at particular times is quite easy. But working on making the timings editable is being more tricky.
While using a tutorial on android/pi communication I was playing with a MySQL database with a single row. Each column in that row would hold a variable and the column name would be the variable name (time on, time off etc). the database would be on the pi as a server so quite easy editable via different means.
I thought that using a cursor and "SELECT * FROM" would recheck the database each time, but have just found out that it doesn't actually reload the data from the row each cycle (unless the connection is closed or something like a 'commit' order is sent) . The only way I've found at the moment is to put the connect and close connection requests in the loop. This means every second it connects to the database and reads the data in case it has been modified in that last second.
My questions are...
Is it ok to constantly connect to a database like this? Would it be better to leave the connection open and get the cursor to 'refresh' each loop?
Am I making a huge mountain out of a molehill with this? Can I do this all within python its self?
I hope that is all clear. If you need any more info then let me know.