Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
326 views
in Technique[技术] by (71.8m points)

python - PyMySQL returning old/snapshot values/not rerunning query?

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...