Tetrikus
Posts: 28
Joined: Tue Apr 05, 2016 5:42 am

Database updates on runtime not detected

Sat Aug 11, 2018 5:47 am

Hi there
I'm building a software, which stores it's data into a mysql database on the pi. The database is also connected to a web interface where I can manage some settings or switch some devices on and off. Now, I noticed, that when I run the python program and change some values in the database, the python script does never notice that.

The python program runs new "SELECT...." statements every couple of minutes, but the results from these selects stay the same. It is working in the very first iteration of the loop, but after that, the values just stay the same. Is this normal behaviour with python or is this just related to errors in my code?

Heater
Posts: 10223
Joined: Tue Jul 17, 2012 3:02 pm

Re: Database updates on runtime not detected

Sat Aug 11, 2018 6:16 am

It's errors in your code.

Tetrikus
Posts: 28
Joined: Tue Apr 05, 2016 5:42 am

Re: Database updates on runtime not detected

Sat Aug 11, 2018 6:55 am

Heater wrote:
Sat Aug 11, 2018 6:16 am
It's errors in your code.
Whew, glad to hear that. First time I hoped my code is wrong. :) Thanks for clarifying.

DirkS
Posts: 9447
Joined: Tue Jun 19, 2012 9:46 pm
Location: Essex, UK

Re: Database updates on runtime not detected

Sat Aug 11, 2018 6:58 am

Tetrikus wrote:
Sat Aug 11, 2018 6:55 am
Heater wrote:
Sat Aug 11, 2018 6:16 am
It's errors in your code.
Whew, glad to hear that. First time I hoped my code is wrong. :) Thanks for clarifying.
What did you expect? You did not give any information at all...
Well apart from: I do something from a python script and I do something another way and something doesn't go quite right.

Heater
Posts: 10223
Joined: Tue Jul 17, 2012 3:02 pm

Re: Database updates on runtime not detected

Sat Aug 11, 2018 7:05 am

Glad I could be of help.

The good news is that it's going to be easier to find and fix errors in your code than if it were a bug in Python or the database.

Tetrikus
Posts: 28
Joined: Tue Apr 05, 2016 5:42 am

Re: Database updates on runtime not detected

Sat Aug 11, 2018 7:09 am

DirkS wrote:
Sat Aug 11, 2018 6:58 am
What did you expect?
Exactly that! Someone told me, that python is able to update database entries in runtime and it has to be an error in my code. That's enough for me to debug further.

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

Re: Database updates on runtime not detected

Sat Aug 11, 2018 2:18 pm

Make the updates atomic. Sprinkle some commit() calls in your code. Also close your cursor for a reader to release locks.

You should also switch from MYISAM to INNODB or ARIADB as those both have better support for ACID (which you need for a data reader). Consider making ever SQL statement run as a unique transaction.

If the updater needs to do a SELECT to establish position use a FOR UPDATE cursor.
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.

Return to “Python”