SQL query inside while loop always returns the same result

Python Help
1

I have a table in SQL database which is continuously populated with data. I want to check timestamp of the latest entry

#!/usr/bin/python3
import mysql.connector
import datetime
#Begin
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="xxx",
  database="test_db"
)
while True:
  mycursor = mydb.cursor()
  mycursor.execute("SELECT timestamp FROM `test_table` ORDER BY timestamp DESC LIMIT 5")
  myresult = list(mycursor.fetchall())
  print(myresult[0][0])
  if myresult[0][0] > some_value:
    print("Checkpoint 1")
    break
  mycursor.close()

But value of myresult[0][0] is always the same despite newer entries are written in the table.
Pls advise how to get the newest entry inside the loop.

2

Just out of curiosity, could you put in a short sleep? You’re hitting your server continuously. Maybe your server"s contents haven’t actually changed. Also, do you have verification from an outside source that new records have been added to the table? Maybe query from a second process which just asks for COUNT(*)?

3

New data are being written each second and I have run a script for a while. I have verified that new data has come, but variable is not changed

4

Are transactions involving the new insertions being properly ended? (Sorry, should have thought of this before. It’s been ages since I’ve done any real database work.) Can you post the code which performs the inserts?

5

I find kind of workaround using shell loop instead of python’s one. The code below works as expected.

#!/bin/bash
while true; do
python3 <<EOF
import mysql.connector
import sys
mydb = mysql.connector.connect(
host="localhost",
user="username",
password="xxx",
database="test_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT timestamp FROM \`test_table\` ORDER BY timestamp DESC LIMIT 5")
myresult = list(mycursor.fetchall())
print(myresult[0][0])
if str(myresult[0][0]) > some_value:
  sys.exit(1)
mycursor.close()
EOF
if [[ $? == 1 ]]; then
echo "Checkpoint 1"
exit 0
fi
done

Would appreciate pure python solution anyway

6

My advice, as already somebody else recommended, is TO SET A SLEEP in the loop. Probably for 1 second or very closed to 1 sec. When you run code in shell loop you launch Python interpreter every loop step which “give it some time to breeze” between loop steps… In pure Python code the query between loop steps is too fast for database to update (being a more lower process becausr involve write operations).

7

Setting session auto-commit to on for your writer or setting isolation levels to read committed in your connector class should resolve the issue.