查询代码
# -*- 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()