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)

Answer 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.

• Julian Espinosa: Thank you Bill, that was it.