MySQL-python连接即使在提交更改后也不会看到对另一个连接对数据库所做的更改 [英] MySQL-python connection does not see changes to database made on another connection even after change is committed

问题描述

我使用MySQLdb模块（适用于Windows Python 2.7的v1.2.3预编译二进制文件）来读取和写入数据到MySQL数据库。一旦连接打开，我可以使用该连接来观察对同一连接上的数据库所做的更改，但不会看到使用另一个连接所做的更改，无论其他连接是使用Python还是使用MySQL命令行客户端。在使用Python进行更新的情况下，请注意我正在连接上运行commit（）命令。

I am using the MySQLdb module for Python (v1.2.3 precompiled binary for Windows Python 2.7) to read and write data to a MySQL database. Once a connection is open, I can use that connection to observe changes made to the database on that same connection, but do not see changes made using another connection, regardless of whether the other connection is made in Python or there is a change made using the MySQL command line client. In the case where I am making updates using Python, note that I am running the commit() command on the connection.

将新记录插入测试的程序示例具有一个VARCHAR列的表：

Example of program that inserts new records into a test table with one VARCHAR column:

import MySQLdb

conn = MySQLdb.connect("localhost", "test", "test", "test")
c = conn.cursor()
c.execute("INSERT INTO test VALUES(%s)", ("Test",))
conn.commit()
c.close()
conn.close()

$ b b

结束打印常数记录计数的程序示例（而不是打印最新的记录计数）。我只能通过杀死和重新运行脚本或每次运行 SELECT 语句时打开一个新连接来更新计数。

Example of program that ends up printing a constant record count (instead of printing the most up-to-date record count). I've only been able to update the count by killing and re-running the script or by having a new connection opened each time the SELECT statement is run.

import MySQLdb

conn = MySQLdb.connect("localhost", "test", "test", "test")

while True:
    input = raw_input("Enter anything: ")
    if input == "exit":
        break

    c = conn.cursor()
    c.execute("SELECT COUNT(*) FROM test")
    res = c.fetchone()[0]
    c.close()

    print("Number of records: %d" % res)

推荐答案

尝试此操作

import MySQLdb
import time

from MySQLdb.cursors import SSCursor
conn = MySQLdb.connect("localhost", "test", "test", "test")


while True:
    input = raw_input("Enter anything: ")
    if input == "exit":
        break
    c = conn.cursor()
    conn.begin()
    c.execute("SELECT COUNT(*) FROM test")
    res = c.fetchone()[0]
    #c.commit()
    c.close()

    print("Number of records: %d" % res)

光标的定义是它将存储数据，直到其更改。因此，您必须通过 begin ing或提交向光标指示您的连接。

The definition of the cursor is it will store the data until its change. So you have to give the indication to the cursor by begining or commiting your connection. This will inform cursor that you have to read new data from the database.

希望这会解决您的查询。

Hope this will solve your query.

我们也从你的问题学习新的东西：）。

We also learn new things from your question :).

这篇关于MySQL-python连接即使在提交更改后也不会看到对另一个连接对数据库所做的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！