mysql.connector do not give the last database state in Python

Question

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.

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

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

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)

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.

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

the Python script just SELECT data. The UPDATE statement was made outside Python, directly from the MySQL client for the test. — fauve
– fauve, Commented Nov 21, 2020 at 21:17
@KlausD. I also try to .close() the cursor and retry the process since cursor = maindb.cursor() but the behavior still the same. — fauve
– fauve, Commented Nov 21, 2020 at 21:36

james mukuya · Accepted Answer · 2020-11-22 00:03:42Z

1

You will need to use a socket or if the changes occur frequently have your code re-run every x minutes

answered Nov 22, 2020 at 0:03

james mukuya

314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fauve · Accepted Answer · 2020-11-22 06:54:56Z

1

I just need to .connect() maindb object and .close() it before each new need.

maindb.connect()

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

maindb.close()

answered Nov 22, 2020 at 6:54

fauve

3212 silver badges11 bronze badges

1 Comment

Benson Kiprono Over a year ago

I have been battling this issue for a whole day now and this answer really helped me out. I had to close my DB connection and initialize it again inside my endless while loop. Cheers mate!

snakecharmerb · Accepted Answer · 2020-11-22 09:49:22Z

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)

Collectives™ on Stack Overflow

mysql.connector do not give the last database state in Python

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related