Python 2.7 - MySQL: Query value won't change while running on infinite loop

Question

The idea is to display a message box once the value changes. The COUNT(*) of the query variable is currently 0. While the program is running in an infinite loop to check if there are any changes, a new request is created, and the count should now be 1. However, this does not happen. The console keeps printing No new Tickets.

Why is the query (row) variable not being updated when there is a change in the background?

If I kill the program and re-run it it will get the new value.

while True:

#if this count increases by 1 send a notification in python that there is a new ticket     
query = """
        SELECT  COUNT(*)#q.id, q.Name, q.Description, t.Created, t.Subject
        FROM
        Queues as q
        LEFT JOIN Tickets as t
        on q.id = t.Queue
        LEFT JOIN Users as u
        on u.id = t.Owner
        WHERE
        q.id = 1 AND u.id = 10 AND t.Status = 'New'
        AND
        STR_TO_DATE(t.Created, "%Y-%m-%d") = CURDATE();
"""

cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
#Debug for printing the current count
print row[0]
#if the current count of unowned and status ="new" tickets is greater than 0 send a notification.
if(row[0] > 0):
    ctypes.windll.user32.MessageBoxW(0, u"New Ticket Available", u"NEW TICKET", 1)
else:
    print "No new Tickets"
time.sleep(5)

Bill Karwin · Accepted Answer · 2019-02-28 20:05:53Z

1

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-commit.html

...by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

Furthermore, if your transaction isolation level is the default (REPEATABLE-READ), then subsequent queries on the same transaction will always get the same result, even if new data has been changing in the meantime.

You have two choices:

Call conn.commit() to get a new snapshot of the database at the start of your loop.
set tx_isolation='REPEATABLE-READ'; before you begin your transaction, so your long-running transaction can see the result of concurrent changes automatically.

answered Feb 28, 2019 at 20:05

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Julian Espinosa Over a year ago

Thank you Bill, that was it.

Collectives™ on Stack Overflow

Python 2.7 - MySQL: Query value won't change while running on infinite loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related