This might not be a bug, but as my question at stackoverflow had not received any answers, it appears I'm not the only one vexed by this, so I'm putting it here in case it is a bug. Apols if it's some weird desired behaviour.

Set up a database with one table, one row.

mysql> CREATE TABLE state (counter INT(10) UNSIGNED NOT NULL) ENGINE=InnoDB;
mysql> INSERT INTO state VALUES (1);

Then run this:

#!/usr/bin/env python3
import pymysql.cursors
import time

conn = pymysql.connect(host='localhost',
     # credentials etc.
     cursorclass=pymysql.cursors.DictCursor)

while True:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM state limit 1;")
        vals = cursor.fetchone()
        print (vals)
        time.sleep(1)

This prints 1 every second. Now while that's running, fire up a MySQL client and update the state table

mysql> UPDATE state SET counter = counter + 1;

(On Debian/MariaDB 10 at least, this client autocommits, but you can issue a COMMIT from the client if you're unsure. And you can test by exiting the CLI client and starting a new one to inspect the table.)

I would now expect the running python script to read 2 but it keeps saying 1.

I have found that by passing autocommit=True, or issuing conn.commit() after the cursor.execute line, it works as expected.