python database request not updating

Question

I am pulling data from a database in a python script which works on start up but does not update when I change the value within the database.

The script initially connects to the database then selects all the contents where id is equal to 1 (this has a number of columns which I intend to use)

It then updates the global variable garageHeating with the contents from the garage column, lets say this is equal to 0 at start up.

The while loop them prints out this variable which on start will be 0

If I them change the column garage within the database to 10 the python script stays at 0. It does not update the global variable which I thought it should do

my script is below

import mysql.connector as mariadb

mariadb_connection = mariadb.connect(
  host="localhost",
  user="garage",
  passwd="*******",
  database="mydb"
  )
cursor = mariadb_connection.cursor()

garageHeating = 0 

def readDbHeating():
   global garageHeating
   result = []
   try:
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
         garageHeating = result[0][8]
   except () as e:
      print (e)

while 1:
   readDbHeating()
   print garageHeating

I have edited the code above shown below to show the global variable outside the function and also to print out the result within the try command. I am still getting the same issue after the first pull of data from the database the script keeps displaying the first set data but does not update to the new data if i change the database records.

import mysql.connector as mariadb

mariadb_connection = mariadb.connect(
  host="localhost",
  user="garage",
  passwd="14Odiham",
  database="mydb"
  )
cursor = mariadb_connection.cursor()

global garageHeating

def readDbHeating():
   result = []
   try:
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
         garageHeating = result[0][8]
         print garageHeating
         print result
   except () as e:
      print (e)

while 1:
    readDbHeating()
    #print garageHeating

how would i do that, i did use a previous connection method with .commit but this maria version did not allow me to use it — , May 13 '19 at 19:44
declare global variable outsidethe function, after each calling it got reset again nd again — sahasrara62, May 13 '19 at 19:50
You're updating the `global garageHeating` with the first item in the list `result[0]`. It should be `garageHeating = result[-1][8]`. The index `-1` fetches the last appended item. — Nizam Mohamed, May 13 '19 at 19:52
tried -1 this did not work. I tried making the global variable outside the function and removing the one within the function but I still get the same problem — , May 13 '19 at 19:56
which column of which row you change? How many row has `id = 1`? simply do `print(result)` from the function `readDbHeating`. — Nizam Mohamed, May 13 '19 at 20:02
print result returns the following [(1, 0, 0, 1, 0, 1, 0, 1, 15)] the last number is the garage one I am reading. If I change this whilst the program is running it continues to print the 15 — , May 13 '19 at 20:09
actually if I change any of the database contents whilst the program is running nothing us updated until I stop the program and restart it — , May 13 '19 at 20:11

score 1 · Accepted Answer · answered May 14 '19 at 16:59

You must either call MySQLdb.connections.Connection.commit before executing a query or set autocommit on the connection object.

Commiting before query

def readDbHeating():
   global garageHeating
   result = []
   try:
      # commit
      mariadb_connection.commit()
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
         garageHeating = result[0][8]
   except () as e:
      print (e)

Autocommit when creating a connection

mariadb_connection = mariadb.connect(
  host="localhost",
  user="garage",
  passwd="14Odiham",
  database="mydb",
  # Auto commit
  autocommit=True
  )

Autocommit after connection creation

mariadb_connection.autocommit(True)

Thank you Nizam I was trying commit() after the cursor.execute which is what i was doing with mySQLdb but it was not working with Maria. Anyway i used autocommit=True within the connection and it works great so thanks — , May 15 '19 at 05:21

score 0 · Answer 2 · answered May 14 '19 at 15:32

I seem to have it working. I have to open the database connection and then close the connection within the function as shown below. This is different to what I have done in the past with mysql but at the moment at least it now works

import mysql.connector as mariadb
import time

garageHeating = 0

def readDbHeating():
   mariadb_connection = mariadb.connect(
     host="localhost",
     user="garage",
     passwd="14Odiham",
     database="mydb"
     )
   cursor = mariadb_connection.cursor()
   result = []
   try:
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
   except () as e:
      print (e)
   finally:
    #closing database connection.
    if(mariadb_connection.is_connected()):
        mariadb_connection.close()
        print("connection is closed")
   return (result)


while 1:
   test = readDbHeating()
   print test
   time.sleep(1)

python database request not updating

2 Answers2