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

当执行以上代码时,在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. 配置参数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()

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页