How to get last X rows from DB for Plotly

sales · November 9, 2022, 4:32pm

Whats the best way to pull the last X number of rows from the DB for a ploty bar graph?

I tried a few different ways I could think of, and I’m missing something.

I have an esp32 sending temp data from an oven to Anvil API that inserts into the sensor database. This happens every minute, and for my temp graph I just want the last hour, or roughly last 60 entries.

Code for bar graph:


  def build_TempHistory_graph(self):
        #self.plot_temp_history.data = go.Bar(y=[100,400,200,300,500])
        db_data = anvil.server.call('get_temp_history_plot')
        # Create a Bar plot with this data, and change the colour of the markers
        self.plot_temp_history.data = go.Bar(
         x = [x['timestamp'] for x in db_data],
         y = [x['Temp'] for x in db_data],
         marker=dict(color='#2196f3'),
         name = 'Dryer Temp History'
        )
        self.plot_temp_history.layout.title = "Dryer Temp Hisitory (Last Hour)"
        self.plot_temp_history.layout.plot_bgcolor = "#000000"
        #Plot.templates.default = "rally"
        self.plot_temp_history.layout.template = "material_light"

Server function:

@anvil.server.callable
def get_temp_history_plot():
  #latest_temp = app_tables.sensor.search(tables.order_by("timestamp", ascending=False))
  #last_hour = latest_temp[:60]
  return app_tables.sensor.search()
  #return last_hour

I tried this below and it didn’t work. Would like to make a single call and just tell it to get the last X rows if possible.

  latest_temp = app_tables.sensor.search(tables.order_by("timestamp", ascending=False))
  last_hour = latest_temp[:60]

jshaffstall · November 9, 2022, 4:39pm

When you say that something didn’t work, you should say how you know it didn’t work. Did you get an error message, etc.

This code works fine for me:

results = app_tables.table_1.search(tables.order_by('key', ascending=False))
results = results[:5]
self.repeating_panel_1.items = results

So if your version isn’t working, there’s something going on there that we need more detail about to be able to help.

stefano.menci · November 9, 2022, 4:46pm

As @jshaffstall says that code should work, but if you have many lines it will be very slow, because it will scan all the rows in the table.

It is better to change the order:

  latest_temp = app_tables.sensor.search(tables.order_by("timestamp", ascending=True))
  last_hour = reversed(latest_temp[0:60])

sales · November 9, 2022, 4:52pm

If I try to get the first 60 rows and return it like this.


@anvil.server.callable
def get_temp_history_plot():
  latest_temp = app_tables.sensor.search(tables.order_by("timestamp", ascending=False))
  last_hour = latest_temp[:60]
  #return app_tables.sensor.search()
  return last_hour

Then I get an error like this.

anvil.server.SerializationError: Cannot serialize return value from function. Cannot serialize <class 'anvil.tables.v2._search.PartialSearchIter'> object at msg['response']
at Graphs, line 19
called from Graphs, line 13
called from Dashboard, line 82

I think it’s something to do with how plotly expects the data?

sales · November 9, 2022, 4:54pm

Thanks! I want to make this as fast as possible so it loads quickly for the dashboard. I tried your suggestions, and it didn’t like it for some reason…

TypeError: 'PartialSearchIter' object is not reversible
at DashboardModule, line 44
called from Graphs, line 19
called from Graphs, line 13
called from Dashboard, line 82

sales · November 9, 2022, 4:58pm

Seems its something in how plotly is expecting the data?

  def build_TempHistory_graph(self):
        #self.plot_temp_history.data = go.Bar(y=[100,400,200,300,500])
        db_data = anvil.server.call('get_temp_history_plot')
        # Create a Bar plot with this data, and change the colour of the markers
        self.plot_temp_history.data = go.Bar(
         x = [x['timestamp'] for x in db_data],
         y = [x['Temp'] for x in db_data],
         marker=dict(color='#2196f3'),
         name = 'Dryer Temp History'
        )
        self.plot_temp_history.layout.title = "Dryer Temp Hisitory (Last Hour)"
        self.plot_temp_history.layout.plot_bgcolor = "#000000"
        #Plot.templates.default = "rally"
        self.plot_temp_history.layout.template = "material_light"

jshaffstall · November 9, 2022, 5:02pm

A serialization error doesn’t have anything to do with Plotly, it’s complaining that it doesn’t know how to send the partial search iterator from the server to the client. Just move the slicing to the client, e.g.:

@anvil.server.callable
def get_temp_history_plot():
  latest_temp = app_tables.sensor.search(tables.order_by("timestamp", ascending=False))
  return latest_temp

And on the client:

db_data = anvil.server.call('get_temp_history_plot')
db_data = db_data[:60]

That should avoid the serialization error.

sales · November 9, 2022, 5:16pm

Thanks man. I’m playing with that as an option now.

  def build_TempHistory_graph(self):
        #self.plot_temp_history.data = go.Bar(y=[100,400,200,300,500])
        db_data = anvil.server.call('get_temp_history_plot')
        db_data = db_data[0:60]
        # Create a Bar plot with this data, and change the colour of the markers
        self.plot_temp_history.data = go.Bar(
         x = [x['timestamp'] for x in db_data],
         y = [x['Temp'] for x in db_data],
         marker=dict(color='#2196f3'),
         name = 'Dryer Temp History'
        )
        self.plot_temp_history.layout.title = "Dryer Temp Hisitory (Last Hour)"
        self.plot_temp_history.layout.plot_bgcolor = "#000000"
        #Plot.templates.default = "rally"
        self.plot_temp_history.layout.template = "material_light"

Will have to play with it some more. It runs, but doesnt plot anything.

If I change it back to

        db_data = anvil.server.call('get_temp_history_plot')
        db_data = db_data

It pulls all the data successfully just takes a long time.

stefano.menci · November 9, 2022, 5:25pm

Try with reversed(list(latest_temp[0:60]))

jshaffstall · November 9, 2022, 5:30pm

This was in the interest of efficiency, right? Converting the entire search results to a list seems like the opposite. If there are a million rows in there, that’ll take some time. Better to convert to a list (if you’re trying to avoid extra round trips on the client) after slicing.

stefano.menci · November 9, 2022, 5:44pm

list(latest_temp)[0:60] would create a list with a million items and get the first 60.

list(latest_temp[0:60]) should iterate the first 60 items and create a list with them (untested).

jshaffstall · November 9, 2022, 6:08pm

Ah, darn parentheses! Got it.

sales · November 9, 2022, 7:15pm

Something about it doesnt like to be revered.

TypeError: 'PartialSearchIter' object is not reversible

sales · November 9, 2022, 7:31pm

I have the full timestamp for each row in the database. May try to make a call with time delta or something like that to see if I can just pull the data I need.

Or make another Db and have it with less data points, or just by the current day.

jshaffstall · November 9, 2022, 7:33pm

What you are trying to do should work. Can you share a clone link? That’ll let us play with it a bit to see where the disconnect is with the charting.

Keep in mind a clone link will share the contents of data tables, too, so if there’s anything sensitive in there you might want to create a smaller app that shows the problem.

stefano.menci · November 9, 2022, 7:36pm

I just tried and it works:

Please check that you are writing it the correct way.

sales · November 9, 2022, 8:02pm

Here is the clone link. I removed the SMS API keys, but everything else is not sensitive.

https://anvil.works/build#clone:DRDDNCY2QEGKKF7V=ZPVRRTT7B4W3L6JFJQ226KBS

I do have it hooked up and running, so its getting live data from the material dryer.

stefano.menci · November 9, 2022, 8:52pm

First of all I apologize for the nonsense in my previous posts. My eyes read latest_temp[:60], but my brain understood latest_temp[-60:].

Second, I cloned your app, clicked on Graphs and waited more than 10 seconds to see the graph. 95% of the time is spent in the go.Bar() function.

Then I checked the server function and did this change and, perhaps I got it to work the way you need it?

@anvil.server.callable
def get_temp_history_plot():
    latest_temp = app_tables.sensor.search(tables.order_by("timestamp", ascending=False))
    last_hour = list(latest_temp[:60])
    return last_hour

I made last_hour a list so it can be returned to the client.

sales · November 9, 2022, 10:18pm

NICE! Thank you. I just tested that and it worked great. Way faster than before.