Search data tables and return a list

Anvil Q&A
1

Hi!

I have a (Hypothetical) table “Owners” that contains a column “Owner” and a column “Item” (Type string). An “Owner” can have multiple “Items”.

I’m trying to preform the following action:

  1. Search the table from a server module for all rows containing “Owner”=“John Doe”
  2. Return a list or table of all “Items” owned by that user.

I’ve been struggling with this for a while, and assume there is a simple solution.

Thanks!

2

Hi there and welcome,

What type of column is Owner? You mentioned that it can contain multiple items but it wasn’t clear if you meant multiple items in each cell.

If owner is just a string you can use standard data table methods to pull back rows equal to “John” for example.

The same logic applies to searching for rows of a given “type”, or even specific combinations of the two.

I would recommend reading the data tables documentation as there are many examples there.

https://anvil.works/docs/data-tables/data-tables-in-code

Something like,

app_tables.owners.search(Owner="john")

should get you started.

Feel free to clarify things if I’ve misunderstood the request.

3

Thanks for the quick reply!

To clarify, I’m able to get the SearchIterator, such as the item below:

[
  {"Name": "Dave", "Age": 34},
  {"Name": "James Smith", "Age": 12},
  {"Name": "Zaphod Beeblebrox", "Age": 42},
]

However I’m now not sure how to extract a column and get a list of ages. Is there a page that explains how to work with SearchIterators?

Thanks!

4

Oh I see, you could extract the values as follows (of course add your specific search filters and other details):

names = [r['name'] for r in app_tables.my_table.search()]

For simplicity, I think of the search iterator as a list of dictionaries (but there are important differences).

2 Likes
5

I am trying to solve a challenge I am facing and feel an answer to this would steer me in the right direction,further to what has been done here,
how would the code look if one was looking to return two columns instead of just one.
for example name and age.

6

How about a list of dicts:

result = [{key: r[key] for key in ["name", "age"]} for r in app_tables.my_table.search()]

If you don’t like the dict comprehension inside a list comprehension, you could do that long-hand:

result = [{"name": r["name"], "age": r["age"]} for r in app_tables.my_table.search()]

or you could use a separate function:

def serialise(row):
    return {key: row[key] for key in ["name", "age"]}

result = [serialise(r) for r in app_tables.my_table.search()]
1 Like
7

Thanks Owen
Thats a great help

8

For people new to python looking at this later; @owen.campbell 's list comprehension is a shortened form that is easier to write. If it were written without comprehensions it would probably look like:

result = []
for r in app_tables.my_table.search():
    result.append( { 'name' : r['name'], 'age' : r['age'] } )

This may do the same thing, but will not be executed by the python runtime compiler the exact same way. Also note that I had to choose to either write the keys twice or do another loop, either of which is… inelegant in comparison to the comprehension.

1 Like