Python：mysql-connector-python查询不到新增数据

查询代码

# -*- 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()


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
 

当执行以上代码时，在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.

  

 

  
1
  
2
 

经测试，以下两个方式都能解决查询新数据的问题

1. 配置参数autocommit=True
2. 执行后手动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()


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
 

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/111513316