mysql.connector doesn't detect outside changes to data

Question

I have a script running which I want to process data when it's added to the database.

import mysql.connector
import time

wait_time = 2

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

while True:
    sql = "SELECT * FROM data WHERE processed = 0"
    mycursor.execute(sql)
    records = mycursor.fetchall()
    for i, r in enumerate(records):
        print(r)
    time.sleep(wait_time)

However, if insert a row via different connection, this connection doesn't show it.

I.e. if I connect to my database via a third party app, and insert a row to

However if I restart the above script, it appears.

Any ideas?

Try [looking at this](https://stackoverflow.com/a/21974489/1790644). Maybe your lib is caching results. — Matt Clark, Dec 05 '18 at 03:59
That's due to the default MySQL isolation level REPEATABLE READ. [This answer](https://stackoverflow.com/a/17589234/4134674) explains it briefly. Commit your connection in every iteration of your `while` loop to refresh the snapshot your query reads from. — shmee, Dec 05 '18 at 06:44

score 1 · Answer 1 · answered Dec 05 '18 at 03:41

Use a message queue (e.g. RabbitMQ). Get the third party App to use it. Message queue implementation has better APIs for processing information asynchronously. Even if you just use the message queue for storing the primary key of the database content.

Alternately enable binary logging and use a replication protocol library to process events.

score 0 · Answer 2 · answered Apr 07 '21 at 07:52

I just faced the same error. and the easiest way to solve it is... defining mydb and mycursor inside the loop.

import mysql.connector
import time

wait_time = 2



while True:
    mydb = mysql.connector.connect(
    host="localhost",
    user="xxx",
    passwd="yyy",
    database="my_database"
   )
    mycursor = mydb.cursor()
    sql = "SELECT * FROM data WHERE processed = 0"
    mycursor.execute(sql)
    records = mycursor.fetchall()
    for i, r in enumerate(records):
        print(r)
    time.sleep(wait_time)

mysql.connector doesn't detect outside changes to data

2 Answers2