Using keywords inside a list as query argument

Anvil Q&A
1

Sorry, I am very new to Anvil and not quite savvy enough…maybe even to ask a smart question here.

I am wondering, how can I use a list of keywords as an argument to search a table?

For example, if I have a table with multiple columns viz: name, age, employed, car_owner, annual_salary, professional. Now, I want to find row of names who fit the following search keys: age (no value), employed (True), car_owner (True), annual_salary (250000), professional (no value).

So, basically the list of keywords would consist of [blank, True, True, 250000, blank]. How would I put this keyword list say inside this code snippet below info = [ { } ] that can be searched in the table?

Your help is much appreciated.

Code Sample:

  {
    'name': r['name'],
    'employed': r['employed'],
  }
  for r in app_tables.people.search()
]
2

Here’s an example:

The key idea is dict unpacking.

3

Thank you sir for the feedback though I am still kept wondering.

If I use the dict-unpacking solution, I would have to indicate the details of the dicts inside the list argument [{…}]. That would mean looking at each user’s desired values to search and iterating over the table rows. My thought (amateurish?) is storing the user’s desired values in a list variable = [valid data items; exclude blanks] and passing the list variable (not specific dicts) as the argument to iterate over.

Is this a valid question? Possible?

Thank you,

4

Hi. Please disregard my follow up message. I don’t think that is possible and useful for what I want to do. However, if you can see how it can be done, I would greatly appreciate it.

Thank you.

5

I need to restructure my question. Since we are dealing with a 2 x 2 dimensional table, dict’s key:value pair is essential.

I guess my question now is this: is it possible to iterate over the values of a dict key to search for the list of keywords and then iterate to the next key, repeating the iteration over values of that specific key? In other words, it’s basically a nested loop.

Thank you.

6

I’m having trouble understanding what you’re trying to do, so let’s back up. Let me see if I understand correctly. The basic way to do what you’re saying in your example is this:

app_tables.people.search(employed=True, car_owner=True, annual_salary=250000)

Does that make sense?

I’m assuming that when you say “age (no value)” you mean you don’t want to restrict the search by age, as opposed to searching specifically for records that don’t specify an age.

If that’s all correct, I think what you’re wanting to do is let the user specify the values to search for. But I want to pause here to make sure we’re on the same page so far.

1 Like
7

Hello.

Your last paragraph is exactly what I want to do…let the user select the dict values to search for in the table.

I just went back to Python to rethink how I would be able to do it. If I can’t pass a list of items (not a dict) as an argument in querying the server table, maybe the dict sub-setting technique could work out. So, when iterating over a row in the table (which is a dict) I could test if another dict is a subset of the larger dict in the table being queried.

Do you think this will work?

Thank you,.

8

Perhaps I’m wrong, but considering the lack of understanding I thought we should make sure we are talking about the same thing.

Looking at your first snippet:

It looks like you are trying to build a list searching one item at a time.

I just want to make sure that you understand that you can get a list of people that match your search criteria in one shot. You only need one search.

This for example would give you the list of people named ‘joe’ with employed==True:

list_of_rows = app_tables.people.search(name='joe', employed=True)

Using simple argument values like name='joe', you will find all the rows with an exact match.

Using q in the arguments allows to use more complex criteria, like greater than a number, starting with a text, containing a text, etc.

Here is an example of some advanced queries.

9

(I think he copied that code from the docs here, not realizing it’s not the right subsection for what he’s wanting to do.)

Just to connect the dots another step, if you had the name and employed value chosen by the user in a list, like user_spec = ['joe', True], then you could do app_tables.people.search(name=user_spec[0], employed=user_spec[1]).

edit: In case it’s helpful, you could get the same result using a list comprehension akin to that example code by doing this following:

rows = [
  row for row in app_tables.people.search()
  if (row['name'] == user_spec[0] and row['employed'] == user_spec[1])
]

but that’s a less efficient way to do it.

10

Thank you for the lead and regrets for posting that unrelated code snippet. I only meant to focus how a dynamic variable could be inserted inside the [{ }] enclosure.

I am trying to test if sub-setting dict or whatever iterable types would work. Not so sure it will work but let me give it a try. Will post an update here.

11

I don’t understand what you mean…

12

I meant storing user-selected options to a variable (list, dictionary or set) that can be searched in the table but the option may change when another user makes a different set of selections to search.

Can that dynamic variable be used as argument in querying the table? For instance, if the variable is a list of dictionaries, can it be tested against each row of the table(also a list of dict) to see if there is a match and if there is, then store that row in an empty list. When iterating over the entire table is finished, then I would have a list of found items in the list of dicts that was filled up or still empty after query is done.

I hope I explained it better this time.

13

What does this mean?

If you are searching a table, then there is no need to iterate. You just tell the search function what you are looking for, and you get the list of matching rows.

If you want to iterate a list of dictionaries, then I don’t understand why you started with a snippet that includes app_tables.people.search().

Why don’t you tell us what you have and what you miss?

14

I tried sub-setting dictionaries to test if a search dict is a subset of any dict in the table. Please note: I am assuming here that each row of Anvil table is a list of dict. Is this correct?

Sub-setting dicts works but I am not sure if it is any better than what you suggested in your codes below (apology: I am not sure how to format it like real code) I could try replacing your conditional if statement to see which option I may want to use later. I am not yet an Anvil subscriber so I can’t test this yet.

Your suggested codes below using a list of user preferred search items follows:
user_spec = [‘joe’, True]
rows = [
row for row in app_tables.people.search()
if (row[‘name’] == user_spec[0] and row[‘employed’] == user_spec[1])
]

Thank you.

15

Just checked Anvil. Here’s what it say:

In Python, your table is represented by Row Objects. Row Objects behave like Python dictionaries. You can get, set and update values in Rows using square brackets.

16

Just checked Anvil. Here’s what it says:

In Python, your table is represented by Row Objects. Row Objects behave like Python dictionaries. You can get, set and update values in Rows using square brackets.

17

See here for how to format code: How to ask a good question (But, to be clear, I don’t understand why you decided to copy my code into your post.)

You don’t need to be an Anvil subscriber to test any of the code we’ve been talking about. And that’s probably what you should do next. I don’t feel like we’re making progress trying to help you here but rather going around in circles. My guess is that you either need to learn more basic Python or else spend some more time with the Anvil docs on Data Tables, trying out for yourself the examples provided there and gaining understanding that way.

Here’s a relevant tutorial: Searching and Filtering

1 Like
18

If you can’t test it, then there is no point for us to waste time answering your questiona. There is no way you can understand the answers if you don’t try first, then ask the question, then try all the things we answer.

In this case for example, both @hugetim and I have already answered. When you search a list, you need to iterate it, but when you search a table, you just use the search arguments. In this case it would be (noticed it’s rows, not row):

rows = app_tables.people.search(name='joe', employed='True')

Please sign up with Anvil, go through a few tutorials and come back here if you have questions.

19

That’s fine. I think I found out what I needed to know. Thanks, Mr Stefani for your help.

20

I understand and thank you for your patience.

Actually, I tested what I wanted to had in mind in PyCharm. It’s trying it with a pure Anvil table that I haven’t. I might just do that if I do not necessarily need to be a subscriber.

I apologize for throwing you guys off. It was unintended. Like I said, I opened up this topic by saying I may not even be smart enough to ask a smart question here.