从Python重复执行MySQL查询 [英] Repeated MySQL queries from Python

问题描述

由于数据快速变化，我需要从Python重复查询MySQL数据库.每次读取数据时，都会将其传输到列表中.

I need to repeatedly query a MySQL DB from Python, as the data is rapidly changing. Each time the data is read, it is transferred into a list.

我曾经假设简单地将查询放入循环中将在每次迭代中从数据库中获取数据.好像没有.

I had assumed that simply putting the query in a loop would fetch the data from the database on each iteration. It seems not.

import mysql.connector
from mysql.connector import Error
from time import sleep

# Create empty list to store values from database.
listSize = 100
myList = []

for i in range(listSize):
    myList.append([[0,0,0]])

# Connect to MySQL Server
mydb = mysql.connector.connect(host='localhost',
                               database='db',
                               user='user',
                               password='pass')

# Main loop
while True:

    # SQL query
    sql = "SELECT * FROM table"

    # Read the database, store as a dictionary
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)

    # Store data in rows
    myresult = mycursor.fetchall()

    # Transfer data into list
    for row in myresult:
        myList[int(row["rowID"])] = (row["a"], row["b"], row["c"])

        print(myList[int(row["rowID"])])

    print("---")
    sleep (0.1)

我尝试使用fetchall，fetchmany和fetchone.

I have tried using fetchall, fetchmany, and fetchone.

推荐答案

您需要在每次查询后提交连接.这将提交当前事务，并确保下一个(隐式)事务将接管在前一个事务处于活动状态时所做的更改.

You need to commit the connection after each query. This commits the current transaction and ensures that the next (implicit) transaction will pick up changes made while the previous transaction was active.

# Main loop
while True:

    # SQL query
    sql = "SELECT * FROM table"

    # Read the database, store as a dictionary
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)

    # Store data in rows
    myresult = mycursor.fetchall()

    # Transfer data into list
    for row in myresult:
        myList[int(row["rowID"])] = (row["a"], row["b"], row["c"])

        print(myList[int(row["rowID"])])

    # Commit !
    mydb.commit()
    print("---")
    sleep (0.1)

这里的概念是隔离级别.从 docs (重点是我的):

The concept here is isolation levels. From the docs (emphasis mine):

可重复读取

这是InnoDB的默认隔离级别.同一事务中的一致读取读取由第一次读取建立的快照.

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read.

这篇关于从Python重复执行MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！