18

I'm using pymysql.cursors and a simplified code example that loads a row from a table and prints it every second is:

#!/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)

state is a table with a single row in a MariaDb database.

Now while this is running if I fire up a MySQL client and change the table's contents, this script merrily keeps pumping out the original value; i.e. it's apparently not consulting the database(!).

I'm newish to Python and I'm definitely new to PyMySQL, so apols if this is a daft question but I have RTM a bit and it just looks odd.

artfulrobot
  • 20,637
  • 11
  • 55
  • 81

1 Answers1

23

I do not understand why this is necessary, but you can fix it by either

  1. Adding autocommit=True into the connect() parameters.

  2. Calling conn.commit() after the cursor.execute() command.

Seems it starts a transaction at a snapshot or something by default. I (nervously!) submitted an issue on the pymysql repo, as I'd not heard anything back here. This was closed immediately with the explanation

It's repeatable read

If anyone knows something better than using autocommit let me know.

artfulrobot
  • 20,637
  • 11
  • 55
  • 81