Repeated MySQL queries from Python return the same data [duplicate]

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 stackoverflow.com/questions/30996401/… and see what aches mysql — nbk
– nbk, Commented Apr 11, 2020 at 15:22
I've just added some error management -- no errors are being returned. — wibbleface
– wibbleface, Commented Apr 11, 2020 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
– nbk, Commented Apr 11, 2020 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
– wibbleface, Commented Apr 11, 2020 at 17:44

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 11, 2020 at 17:53

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wibbleface Over a year ago

Perfect. Thank you!

R Lynch · Accepted Answer · 2020-04-11 15:20:00Z

0

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()

answered Apr 11, 2020 at 15:20

R Lynch

413 bronze badges

1 Comment

wibbleface Over a year ago

These changes don't make any difference.

Baher El Naggar · Accepted Answer · 2021-10-25 13:20:04Z

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, 2021 at 13:20

Baher El Naggar

3593 silver badges5 bronze badges

Collectives™ on Stack Overflow

Repeated MySQL queries from Python return the same data [duplicate]

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Related