MySQL-python connection does not see changes to database made on another connection even after change is committed

Question

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.

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()

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)

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

Answer 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
)