查询代码
# -*- coding: utf-8 -*- import time import mysql.connector config = { "database": "data", "username": "root", "password": "123456", "host": "127.0.0.1", "port": 3306 } connect = mysql.connector.Connect(**config) cursor = connect.cursor(dictionary=True) while True: cursor.execute("SELECT * FROM person") result = cursor.fetchall() # fetchall() 获取所有记录 time.sleep(2) print(result) # 关闭游标和连接 cursor.close() connect.close()
当执行以上代码时，在MySQL数据库修改数据（增、删、改）没法获取到修改后的数据
翻阅文档10.2.35 MySQLConnection.autocommit Property发现一句话
When the autocommit is turned off, you must commit transactions when using transactional storage engines such as InnoDB or NDBCluster.
经测试，以下两个方式都能解决查询新数据的问题
- 配置参数
autocommit=True - 执行后手动
commit
修改后的代码如下
# -*- coding: utf-8 -*- import time import mysql.connector config = { "database": "data", "username": "root", "password": "123456", "host": "127.0.0.1", "port": 3306, "autocommit": True } connect = mysql.connector.Connect(**config) cursor = connect.cursor(dictionary=True) while True: cursor.execute("SELECT * FROM person") result = cursor.fetchall() # 获取所有记录 time.sleep(2) print(result) print(connect.in_transaction) # 如果不commit，数据库新增、删除、修改的数据没法查询到 # connect.commit() # 关闭游标和连接 cursor.close() connect.close()
