MySQL/Python -- committed changes not appearing in loop
<p>Using MySQL Connector/Python I have a loop that keeps checking a value for a change every 2 seconds.  Without all the meat, here is the loop (the print is there for testing purposes:</p>

<pre><code>try:
   while True:
       request = database.get_row(table="states", wherecol="state", whereval="request_from_interface")[0]
       print(request.value)
       time.sleep(2)
except KeyboardInterrupt:
   pass
# back to normal operation
</code></pre>

<p>get_row is a simple select query and it returns the namedtuple fetchall (hence the [0] at the end, it will only ever be one row).</p>

<p>The problem is that once it gets the initial value, it keeps returning that value even if I change it.  For example, if it is "0" to start, it keeps printing "0" even if I go to adminer and change it and even if I open a new terminal and change it and commit it.  I've tried setting the query_cache_size to 0, still no luck.  I thought it was a commit problem, but that wasn't it, either.  If I change it and reconnect, I see the change, but I'm not sure why it won't change while in the program.  Any thoughts?</p>

<p>EDIT: in case it comes up, yes I'm closing the cursor and grabbing a new cursor with every call.</p>

<p>The default <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html" rel="nofollow">isolation level</a> of InnoDB is <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read" rel="nofollow">REPEATABLE READ</a>. This means that subsequent consistent <code>SELECT</code> statements will read the same snapshot established by the first within a transaction. Though you're opening and closing cursors, you're probably doing that inside a transaction and so you're reading the established snapshot over and over again. Either <code>ROLLBACK</code> or <code>COMMIT</code> to end the current transaction so that you can read values committed after the snapshot was taken.</p>

python mysql python-3.x mysql-connector-python