MySQL-python Connection Does Not See Changes To Database Made On Another Connection Even After Change Is Committed

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 co

Solution 1:

Try this

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)

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 :).

Solution 2:

This is an old question but in case someone have the same problem you need to enable autocommit in your connexion declaration:

import mysql.connector

conn = mysql.connector.connect(
  host='localhost',
  port='3306',
  user='root',
  passwd='rootpassword',
  auth_plugin='mysql_native_password',
  autocommit=True
)