title: Why do I need to reconnect to the database to see changes in table data?
body: <p>I periodically query a MySQL table and check data in the same row.</p>
  
  <p>I use MySQLdb to do the job, querying the same table and row every 15 seconds. </p>
  
  <p>Actually, the row data changes every 3 seconds, but the cursor always return the same value.</p>
  
  <p>The strange thing is: after I close the MySQL connection and reconnect, using a new cursor to execute the same select command, the new value is returned.</p>
  
  <p>The code that I suspect to be wrong is begins after the comment:</p>
  
  <pre><code>config = SafeConfigParser()
  config.read("../test/settings_test.conf")
  
  settings = {}
  settings["mysql_host"] = config.get("mysql","mysql_host")
  settings["mysql_port"] = int(config.get("mysql","mysql_port"))
  settings["mysql_user"] = config.get("mysql","mysql_user")
  settings["mysql_password"] = config.get("mysql","mysql_password")
  settings["mysql_charset"] = config.get("mysql","mysql_charset")
  
  #suspected wrong code
  conn = mysql_from_settings(settings)
  cur = conn.cursor()
  cur.execute('use database_a;')
  cur.execute('select pages from database_a_monitor where id=1;')
  result = cur.fetchone()[0]
  print result
  #during 15 second, I manually update the row and commit from mysql workbench
  time.sleep(15)    
  
  cur.execute('select pages from database_a_monitor where id=1;')
  result = cur.fetchone()
  print result
  conn.close()
  </code></pre>
  
  <p>The output is:</p>
  
  <pre><code>94
  94
  </code></pre>
  
  <p>If I change the code so that it closes the connection and re-connects, it returns the latest value instead of repeating the same value:</p>
  
  <pre><code>conn = mysql_from_settings(settings)
  cur = conn.cursor()
  cur.execute('use database_a;')
  cur.execute('select pages from database_a_monitor where id=1;')
  result = cur.fetchone()[0]
  print result
  conn.close()
  
  time.sleep(15)
  #during that period, I manually update the row and commit from mysql workbench
  
  conn = mysql_from_settings(settings)
  cur = conn.cursor()
  cur.execute('use database_a;')
  cur.execute('select pages from database_a_monitor where id=1;')
  result = cur.fetchone()[0]
  print result
  conn.close() 
  </code></pre>
  
  <p>The output is:</p>
  
  <pre><code>94
  104
  </code></pre>
  
  <p>Why this difference in behavior?</p>
  
  <p>Here is the definition of <code>mysql_from_settings</code>:</p>
  
  <pre><code>def mysql_from_settings(settings):
      try:
          host = settings.get('mysql_host')
          port = settings.get('mysql_port')
          user = settings.get('mysql_user')
          password = settings.get('mysql_password')
          charset = settings.get('mysql_charset')
          conn=MySQLdb.connect(host=host,user=user,passwd=password,port=port,\
                 charset=charset)
  
          return conn
      except MySQLdb.Error,e:
          print "Mysql Error %d: %s" % (e.args[0], e.args[1])
  </code></pre>