Python module mysql-connector does not see new changes

0
votes

I use the mysql.connector module to fetch rows in a python script but when I update a table using the terminal, my script doesen't see any changes.

My code is this:

import mysql.connector

database = mysql.connector.connect(host='localhost', user='root', passwd='password', database='my_db')
cursor = database.cursor()

cursor.execute('SELECT * FROM my_table')
print(cursor.fetchall())

cursor.execute('SELECT * FROM my_table')
print(cursor.fetchall())

The first time it always reads the correct values but at the second time it does not see changes even when I have update my database.

I tried this solutions but it still did not work:

  • I tried updating the database using the mysql.connector module
  • I tried installing some older versions
  • I tried using the root user
1
pythonmysqlmariadbmysql-connector

1 Answers

1
votes

When use performs DML like update, delete, etc You have to commit cursor after performing the operation otherwise your operation not save. There are use case of commit cursor some time

  • due to the electricity issue
  • atomicity transaction will rollback or commit latter

like

import mysql.connector

database = mysql.connector.connect(host='localhost', user='root', passwd='password', database='my_db')
cursor = database.cursor()

try:  
    cursor.execute("update Employee set name = 'alex' where id = 110")  
    cursor.commit()  
except:  
    cursor.rollback()  
  
cursor.close()

commit if the update will succeed otherwise rollback if got any error at the database level

or you can pass autocommit=True when you connect with database it will work too it's global configuration it will commit of some interval of time like


database = mysql.connector.connect(host='localhost', user='root', passwd='password', database='my_db', autocommit=True)
cursor = database.cursor()