PyMySQL selecting data in loop returns not all records

Question

I am experiencing problems with PyMySQL data selection in loop.

1. xxx table contains one record id = 1

2. I am starting script

   import pymysql
   import time
   connection = pymysql.connect(host="127.0.0.1",
                                port=3306,
                                user="yyy",
                                passwd="yyy",
                                db="yyy",
                                charset='utf8',
                                cursorclass=pymysql.cursors.DictCursor)
   
   while True:
       with connection.cursor() as cursor:
           sql = "select * from xxx"
           cursor.execute(sql)
           result = cursor.fetchall()
           print result
   
       time.sleep(5)
   
   connection.close()
   

3. While my script is running i open MySQLWorkbench and insert new record to xxx table, etc. id = 2

4. Problem: my script does not print new record it just prints id=1.

If i restart my script then it selects new inserted record id=2, but if then i insert another record from MySQLWorkbench it does not find it (unless i restart my script)

Can you explain how to retrieve all records from database in loop?

Update:

I solved problem by adding autocommit=True param to connection. But why I need to commit changes when I am doing select? Is it normal?

Answer 1

But why I need to commit changes when I am doing select? Is it normal?

Yes. It's normal. You must learn transaction isolation level.

• Transaction Isolation Level (Wikipedia)
• Transaction Isolation Level (MySQL document)