Pieman711
Posts: 2
Joined: Tue May 22, 2018 7:56 pm

Can I use MySQL as a way to store and edit python variables

Tue May 22, 2018 8:36 pm

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.

scotty101
Posts: 2987
Joined: Fri Jun 08, 2012 6:03 pm

Re: Can I use MySQL as a way to store and edit python variables

Wed May 23, 2018 8:59 am

Databases are intended to be written and read from. Don't worry about it.

Consider how often your program needs to read from the database though. If it has just updated a variable, it shouldn't need to read it right back again.
Keep an "in-memory" copy of all your variables that is loaded from the database either once at the start of the program or every 5 minutes (perhaps even less frequently)
When a variable is updated, write to the local variable and then store it to the database.
Then as program closes, save all the variables (or just those that have changed)

For simple program saved variables, I've recently been using text files containing JSON formatted data.
Electronic and Computer Engineer
Pi Interests: Home Automation, IOT, Python and Tkinter

User avatar
DougieLawson
Posts: 33348
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Can I use MySQL as a way to store and edit python variables

Wed May 23, 2018 6:48 pm

Pieman711 wrote:
Tue May 22, 2018 8:36 pm
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.
Stick a conn.commit() call in there and you will re-fetch the data on the next PREPARE/EXECUTE/FETCH cycle.

BTW, don't use SELECT * FROM ... use SELECT column1, column2, ... columnN FROM ... as it's a) more efficient (with the right indexes you may get index only access and b) doesn't leave a glaring bug waiting to hurt when someone alters the table structure to add or remove a column.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

asandford
Posts: 1999
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: Can I use MySQL as a way to store and edit python variables

Wed May 23, 2018 8:06 pm

Node Red and SQLite is pretty much all you need (NR can fire off your python scripts if there isn't a node that already implements the functionality). I/O functionality built-in includes serial/websocket/http/tcp/udp/email/gpio/MQTT/Arduino (firmata) and twitter, DB nodes are available for all common DBs. It also has a dashboard for status display.

Pieman711
Posts: 2
Joined: Tue May 22, 2018 7:56 pm

Re: Can I use MySQL as a way to store and edit python variables

Thu May 24, 2018 11:40 am

The answers all seem very reassuring that I haven't gone down the wrong route completely with this. Thanks!
DougieLawson wrote:
Wed May 23, 2018 6:48 pm
Pieman711 wrote:
Tue May 22, 2018 8:36 pm
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.
Stick a conn.commit() call in there and you will re-fetch the data on the next PREPARE/EXECUTE/FETCH cycle.

BTW, don't use SELECT * FROM ... use SELECT column1, column2, ... columnN FROM ... as it's a) more efficient (with the right indexes you may get index only access and b) doesn't leave a glaring bug waiting to hurt when someone alters the table structure to add or remove a column.
This sounds like what I should be doing to get the curser to 'refresh' as it were. I will look in to making sure the SELECT command is appropriately targeted too.
scotty101 wrote: Databases are intended to be written and read from. Don't worry about it.

Consider how often your program needs to read from the database though. If it has just updated a variable, it shouldn't need to read it right back again.
Keep an "in-memory" copy of all your variables that is loaded from the database either once at the start of the program or every 5 minutes (perhaps even less frequently)
When a variable is updated, write to the local variable and then store it to the database.
Then as program closes, save all the variables (or just those that have changed)

For simple program saved variables, I've recently been using text files containing JSON formatted data.
The issue is that other devices may update the database, not just this Pi, and I want the Pi to know fairly quickly when it is updated. Then only way I can think of this is to check the database every second to see if anything has changed.
asandford wrote: Node Red and SQLite is pretty much all you need (NR can fire off your python scripts if there isn't a node that already implements the functionality). I/O functionality built-in includes serial/websocket/http/tcp/udp/email/gpio/MQTT/Arduino (firmata) and twitter, DB nodes are available for all common DBs. It also has a dashboard for status display.
I haven't come across this at all before. I think my project is nearly in a stable place so I'll continue the last few tweaks to make it work. But once its running on its own I will look into node red as the next iteration. Thanks!

Return to “Python”

Who is online

Users browsing this forum: mariusmilatz and 15 guests