Pagination on the API?

Anvil Q&A
1

The API returns 100 results for a dataset which has much more than that. This leads me to believe that it is paginated but there isn’t anything in the documentation which would indicate that, nor a token to collect the next response. Is it a bug or did I miss something?

2

Which API are you referring to? The DataTables view in the IDE does that I think, but you can load more by clicking the “load more” at the bottom. Is this what you are referring to?

3

I’m referring to the anvil API: https://anvil.works/docs/http-apis/creating-http-endpoints

4

Okay so you are using Anvil’s HTTP requests library to call an external API? Or are making one of your Anvil server functions into an HTTP endpoint? I assume the former, but I just wanted to make sure I understood things correctly.

5

Well, that was needlessly painful… It turns out there’s a BYO-pagination expectation that isn’t documented. The only record of it is in thread buried from 2 years ago.

6

It’s definitely a hidden thing - you get 100 rows for free and have to iterate to get the rest…
Maybe there’s an updated way to do this. Since that post is a year old now…

Depending on how large your search iterator is you might find mileage in the following approach:


import pandas as pd
import anvil.media

@anvil.server.http_endpoint("/hello")
def hello(**kwargs):
  file = app_tables.my_table.search().to_csv()
  
  with anvil.media.TempFile(file) as f:
    df = pd.read_csv(f)
  return df.to_json(orient='records')

I’ve found this to be the quickest way to extract all data from a datatable. But it’s utility depends on the complexity/size of the table.

You might prefer a different type of orientation - you can find orient options here:
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html

7

Whoa! Yeah, that’s very much not intended behaviour. What you were seeing was bits of Anvil’s internal object representation, and should have produced an error instead. Anvil has now been updated and this code will now produce an HTTP 500 error. (I apologise for the sudden change – it was possible for apps to accidentally leak sensitive data this way, so we applied the fix immediately for security’s sake.)

Bottom line: Database rows are Python objects, not dictionaries, so you should always be doing some sort of translation if you want to present them as JSON. You can find an example of the recommended way in the Making HTTP APIs with Anvil tutorial. Here’s the code sample from the “Returning records from your database” example, which will return every record in the table:

@anvil.server.http_endpoint('/tasks')
def get_tasks(**q):
    return [{'title': task['title'], 'done': task['done']}
            for task in app_tables.tasks.search()]
1 Like
9

Thanks! This is, of course a much cleaner way to do it. I do have 2 questions on filtering & serializing.

Filtering

I was previously able to do this: which only queried rows within the past 24 hours. It now returns an error AttributeError: 'dict' object has no attribute 'greater_than'

def kit_log_export(**q):
  hours_ago_24 = datetime.datetime.now() - datetime.timedelta(hours=24)
  return [{'badge_id': kit['badge_id']
            , 'picklist_id': kit['picklist_id']
            , 'dateadded': str(kit['dateadded'])[:19]
            , 'uuid': kit['uuid']             
          }
          for kit in app_tables.kitting.search(dateadded = q.greater_than(hours_ago_24))]

I have also tried adding an if statement within the comprehension. Less efficient, but I’m not picky:
for kit in app_tables.kitting.search() if kit['dateadded'] > hours_ago_24 ]

Though it results in this error: TypeError: cannot compare naive and aware datetimes

What is the best way to do this?

Serializing
Secondly, my endpoint returns JSON, so the datetimes must be serialized. I could not find a reasonable way to do this & came up with a working, but ugly workaround:

, 'dateadded': str(kit['dateadded'])[:19]

Can you suggest a less ugly alternative?

10

This line defines a local q as a dictionary with the named parameters of the function.

You need to either change that q in something else, or import the q for the query with another name.

2 Likes