CODEWITHSUNDEEP
pythonmysqlpython-2.7
Julian Espinosa
Julian Espinosa

Reputation: 722

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

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)

Upvotes: 1

Views: 209

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

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.

Upvotes: 1

Related Questions