0

I am trying to read the latest row from a table and plot the graph using animate and matplotlib in python. The table gets updated every 1 second with a new value. I need to simulate the graph by live plotting the values. However, when I use animate function with an interval of 1 second, I get the same value for every interval fetch.

I am adding the code for your reference. Please let me know what I am missing. The same code is working good when I use a flat file instead of MySql table.

import matplotlib.pyplot as plt
import matplotlib.animation as animation
from matplotlib import style
import mysql.connector
import pandas as pd

style.use('fivethirtyeight')

mydb = mysql.connector.connect(
  host="xxxxxxxxxx",
  user="xxxxx",
  passwd="xxxxxxx",
  database="sakila"
)


fig = plt.figure(figsize=(8,5))
ax = plt.subplot2grid((1,1), (0,0))
plt.ion()
cursor = mydb.cursor()

def animate(i):

    df = pd.read_sql("SELECT * FROM DATA_TEST ORDER BY ID DESC LIMIT 1", mydb)
    y = df["VALUE"]
    x = df["ID"]
    xs = []
    ys = []
    xs.append(x)
    ys.append(float(y)*100)
    ax.clear()
    ax.plot(xs,ys)

ani = animation.FuncAnimation(fig, animate, interval=1000)
plt.show()


## TESTING CURSOR IN FOR LOOP
import mysql.connector
import pandas as pd
import time


mydb = mysql.connector.connect(
  host="xxxxxxx",
  user="xxxx",
  passwd="xxxxxx",
  database="xxxxx"
)



for i in range(10):
    cursor = mydb.cursor()

    cursor.execute("SELECT * FROM DATA_TEST ORDER BY ID DESC LIMIT 1")
    result = cursor.fetchall()
    print("Total rows are:  ", len(result))
    for j in result:
        print(j[0])
        print(j[1])
    time.sleep(10)
    cursor.close()
Dhiraj
  • 21
  • 1
  • 6
  • I don't usually use MySQL, so I don't know if that's the answer, but I'm wondering if it's because the cursor hasn't been updated. So, isn't it necessary to set the cursor in the animation function and update the cursor after getting the record? – r-beginners Apr 24 '20 at 02:54
  • Though I mentioned the cursor statement in the code, I actually haven't used the cursor. But, to test the same, I did a simple test to execute a cursor in for loop. The issue is same. I get the same first iteration row value for every iteration too. – Dhiraj Apr 24 '20 at 07:03
  • Added the cursor test code in the CODE BLOCK under "## TESTING CURSOR IN FOR LOOP" for your reference – Dhiraj Apr 24 '20 at 07:07
  • [How to update a graph using matplotlib](https://stackoverflow.com/questions/31587170/how-to-update-a-graph-using-matplotlib) You may find this page helpful. If the cursor isn't the cause, then initialization might be the way to go. – r-beginners Apr 24 '20 at 07:24
  • I was able to fix the issue by including the mysql db connection inside the animate function. Thank you for your help @r-beginners – Dhiraj Apr 24 '20 at 17:12

0 Answers0