mysql.connector 不给出 Python 中的最后一个数据库状态

2022-01-17 00:00:00 python sql-update mysql

我使用 Python 中的 mysql.connector 库向数据库发送查询.但是，当数据库在初始化后发生变化时，mysql.connector 的工具会回答就像数据库从未发生过变化一样.

I use mysql.connector library in Python to send query to database. But, when the database is changed after the initialization, the mysql.connector’s tools answer like if the database had never change.

例如，假设我有一个极简表 students，只有两列 id 和 name.

As example, let’s imagine I have a minimalist table students with just two columns id and name.

+----+------+
| id | name |
+----+------+
| 0  | foo  |
+----+------+

在下面的代码中，查询将询问 id 0 的用户.但是，在进程内部，一些事件会从 Python 脚本外部发生并改变数据库.

In the following code, the query will ask the user with id 0. But, inside the process, some events will happened from outside the Python script and alter the database.

import mysql.connector

maindb = mysql.connector.connect(
    host = "<host>",
    user = "<user>",
    password = "<password>",
    db = "<database name>"
)

cursor = maindb.cursor()

# Here, I will send outside the python script a MySQL query to modify the name of the student from "foo" to "bar" like this:
# `UPDATE `students` SET `name` = 'bar' WHERE `students`.`id` = 0;`

cursor.execute("SELECT `id`, `name` FROM `students` WHERE `id` = 0")
result = cursor.fetchall()
print(result)

然后我得到这个答案[(0, 'foo')].如您所见，自从调用 maindb.cursor() 以来，Python 并不知道数据库发生了变化.所以我得到 foo 作为名称字段而不是 bar 正如预期的那样.

Then I get this answer [(0, 'foo')]. As you see, Python is not aware the data base has change since maindb.cursor() was called. So I get foo as name field instead of bar as expected.

那么当我发送查询时，如何告诉 mysql.connector 的工具从数据库中获取最后的更新?

So how to tell mysql.connector’s tools to take the last updates from the database when I send a query?

推荐答案

数据库通过防止正在进行的事务看到其他事务所做的更改来维护数据完整性(请参阅 事务隔离级别).

The database maintains data integrity by preventing in-progress transactions from seeing changes made by other transactions (see transaction isolation levels).

您可以提交您的连接以允许它看到新的更改:

You can commit your connection to allow it to see new changes:


cursor = maindb.cursor()


# Here, I will send outside the python script a MySQL query to modify the name of the student from "foo" to "bar" like this:
# `UPDATE `students` SET `name` = 'bar' WHERE `students`.`id` = 0;`

# Doesn't show the update
cursor.execute("SELECT `id`, `name` FROM `students` WHERE `id` = 0")
result = cursor.fetchall()
print(result)  

# Shows the update because we have committed.
maindb.commit()
cursor.execute("SELECT `id`, `name` FROM `students` WHERE `id` = 0")
result = cursor.fetchall()
print(result)

相关文章