Repeated MySQL queries from Python return the same data

Question

I need to repeatedly query a MySQL DB from Python, as the data is rapidly changing. Each time the data is read, it is transferred into a list.

I had assumed that simply putting the query in a loop would fetch the data from the database on each iteration. It seems not.

import mysql.connector
from mysql.connector import Error
from time import sleep

# Create empty list to store values from database.
listSize = 100
myList = []

for i in range(listSize):
    myList.append([[0,0,0]])

# Connect to MySQL Server
mydb = mysql.connector.connect(host='localhost',
                               database='db',
                               user='user',
                               password='pass')

# Main loop
while True:

    # SQL query
    sql = "SELECT * FROM table"

    # Read the database, store as a dictionary
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)

    # Store data in rows
    myresult = mycursor.fetchall()

    # Transfer data into list
    for row in myresult:
        myList[int(row["rowID"])] = (row["a"], row["b"], row["c"])

        print(myList[int(row["rowID"])])

    print("---")
    sleep (0.1)

I have tried using fetchall, fetchmany, and fetchone.

add a error managemnt https://stackoverflow.com/questions/30996401/error-handling-in-python-mysql and see what aches mysql — nbk, Apr 11 '20 at 15:22
I've just added some error management -- no errors are being returned. — wibbleface, Apr 11 '20 at 17:22
your code is too generic to find any solution, if there is no error your query doesn't return data or you have the wrong colmnames — nbk, Apr 11 '20 at 17:33
It's returning data, but only on the first iteration of the while loop. Subsequent iterations return the same data even though the database content has changed. The only way I can get it to query the database on each iteration is by moving the connection and close commands inside the While loop which isn't an ideal solution. — wibbleface, Apr 11 '20 at 17:44

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

You need to commit the connection after each query. This commits the current transaction and ensures that the next (implicit) transaction will pick up changes made while the previous transaction was active.

# Main loop
while True:

    # SQL query
    sql = "SELECT * FROM table"

    # Read the database, store as a dictionary
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)

    # Store data in rows
    myresult = mycursor.fetchall()

    # Transfer data into list
    for row in myresult:
        myList[int(row["rowID"])] = (row["a"], row["b"], row["c"])

        print(myList[int(row["rowID"])])

    # Commit !
    mydb.commit()
    print("---")
    sleep (0.1)

The concept here is isolation levels. From the docs (emphasis mine):

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read.

score 0 · Answer 2 · answered Apr 11 '20 at 15:20

I'd make a few changes. First, declare the cursor before the while loop. I would also do a buffered cursor. And finally, close the cursor and DB after the file is done. Hope this helps.

import mysql.connector
from mysql.connector import Error
from time import sleep

# Create empty list to store values from database.
listSize = 100
myList = []

for i in range(listSize):
    myList.append([[0,0,0]])

# Connect to MySQL Server
mydb = mysql.connector.connect(host='localhost',
                               database='db',
                               user='user',
                               password='pass')
mycursor = mydb.cursor(buffered=True, dictionary=True)

# Main loop
while True:

    # SQL query
    sql = "SELECT * FROM table"

    # Read the database, store as a dictionary
    mycursor.execute(sql)

    # Store data in rows
    myresult = mycursor.fetchall()

    # Transfer data into list
    for row in myresult:
        myList[int(row["rowID"])] = (row["a"], row["b"], row["c"])

        print(myList[int(row["rowID"])])

    print("---")
    sleep (0.1)
mycursor.close()
mydb.close()

These changes don't make any difference. – wibbleface Apr 11 '20 at 17:17 — wibbleface, Apr 11 '20 at 17:17

score 0 · Answer 3 · answered Oct 25 '21 at 13:20

0

For SqlAlchemy you need to close the session to get last changes:

try:
    results = session.query(TableName).all()

    return results

except Exception as e:
    print(e)
    return e

finally:
    session.close()  # optional, depends on use case

answered Oct 25 '21 at 13:20

Baher El Naggar

181
1
5

Repeated MySQL queries from Python return the same data

3 Answers3

Linked

Related