Python Forum
Python animate live plotting fetching data from Mysql Table
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python animate live plotting fetching data from Mysql Table
#1
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()
Reply
#2
This way you are not filtering your query to get the record with the highest id. I'm not totally sure, but I think you select one row, because of the LIMIT, and then order it DESC.

Is there a date column with the date/time when the record was added which you can filter on in DATA_TEST? Because in that case you will be able to get te records from te last hour for example and then search for the record that was added last.
Reply
#3
Hi Jeff900,

Thank you for the response. The ID column in the table is Auto-incremented when a new row gets inserted. I want to select only the last row every time I try to fetch the data from the table ie., every 1 second interval.
Reply
#4
With the SQL statement you will first get data from a table. Since you used LIMIT 1 it will only get one record, without looking to any conditions. So problaly in your case it will select the first row it will find (probably the first added record) and then order that dataset DESC, which does not do anything since the dataset only contains one record.

Do you understand the problem I try to explain? If you want to filter the dataset with your SQL statement, you will need something more specific to filter on.
Reply
#5
ok, I get your point. I will remove the limit from sql query and see how it works.
Reply
#6
Yeah, that's why I was wondering if there is any column availabe like a column with the date/time the record was added. I can imagine that the table will grow exponantially after a while. Another thing you can try is using a MAX() function first like this.

SELECT MAX(id_column_name) from TEST_DATA
This will give you the highest id number, which is probably the last added record. Or, if your program is constantly running, you can maybe hold the id value that was used last and then do a select statement for every record that has a higher id. But for me this feels kind of like a trick. I'm not sure if this is a solid solution.

Like this by the way:

SELECT * FROM TEST_DATA WHERE <id_column> > <last_used_id>

Ok, one last addition. I tried the following query in PostgreSQL, but it should work in MySQL as well.

select * from TEST_DATA
where <id_column> = (select max(id_column) from TEST_DATA)
Reply
#7
Hi,

I was able to fix the issue by including the mysql db connection in the animate function.

import matplotlib.pyplot as plt
import matplotlib.animation as animation
from matplotlib import style
import mysql.connector
import pandas as pd
 
style.use('fivethirtyeight')
 
fig = plt.figure(figsize=(8,5))
ax = plt.subplot2grid((1,1), (0,0))
plt.ion()
cursor = mydb.cursor()
 
def animate(i):
    mydb = mysql.connector.connect(
       host="xxxxxxxxxx",
       user="xxxxx",
       passwd="xxxxxxx",
       database="sakila"
    )
 
    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()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Using pyodbc&pandas to load a Table data to df tester_V 3 748 Sep-09-2023, 08:55 PM
Last Post: tester_V
  Is it possible to capture live running waveforms in an oscilloscope using python? Girishbabu_27 4 923 Jul-29-2023, 06:05 AM
Last Post: Girishbabu_27
  Mysql Workbench table not updated CatBall 2 1,046 Feb-13-2023, 05:37 PM
Last Post: CatBall
  Create simple live plot of stock data dram 2 2,858 Jan-27-2023, 04:34 AM
Last Post: CucumberNox
Photo How to select NULL and blank values from MySQL table into csv python300 9 2,330 Dec-27-2022, 09:43 PM
Last Post: deanhystad
  fetching exit status hangs in paramiko saisankalpj 3 1,134 Dec-04-2022, 12:21 AM
Last Post: nilamo
  Animate the midpoints of a 3d “Rhombic dodecahedron” Beck_Johnson 0 839 Nov-05-2022, 04:18 PM
Last Post: Beck_Johnson
  Fetching the port number using asyncio gary 0 910 Nov-01-2022, 02:53 AM
Last Post: gary
  panda table data kucingkembar 0 1,074 Mar-01-2022, 10:38 PM
Last Post: kucingkembar

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020