MySQL query results not updating in Python

Question

I created a simple Python script which queries every 0.5 seconds my Database and prints the results every time. I'm using Schedule to run the script every 0.5 seconds, here is my code:

import mysql.connector
import json, schedule, time

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="mydb"
)
mycursor = mydb.cursor()

def testing():
    mycursor.execute("SELECT x FROM mydb")
    myresult = mycursor.fetchall()
    print(myresult)

schedule.every(0.5).seconds.do(testing)

while True:
    schedule.run_pending()

I noticed that if i edit something on that DB table while the script is running, the script will keep printing data from my without that edit, to actually see my edits being reflected on the query, i will have to restart the script. Instead, if i edit something on the database while the script is running, i want to see the script printing the current state of the database, not the old one.Can anyone tell me what is happening here and how to fix it? Thanks in advance!

What happens if you move `mycursor = mydb.cursor()` to `testing()`? — Tomasito665, Apr 17 '20 at 11:34
Any chance you're editing the base with autocommit set to 0? https://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html — Błotosmętek, Apr 17 '20 at 11:35

score 0 · Accepted Answer · answered Apr 17 '20 at 12:12

0

Adding the line mydb.commit() inside the testing() function worked. Now the query is being updated successfully.

answered Apr 17 '20 at 12:12

Jack022

867
6
30
91

MySQL query results not updating in Python

1 Answers1