Dynamic Query Builder

Anvil Q&A
1

What I’m trying to do:
Programmatically build query objects and execute them.
Specifically, I want to create a function like this:

@anvil.server.callable
def assert_input_row_doesnt_exist_in_table(input_row, table_to_check_against):
  # input_row is kvp, key is string, which is column name, value is any value that can be in datatable
  # properties_that_matter is a list of string, which correspond to the column names in the table to check from input_row
  # table_to_check_against is the table to assert this statement against
  pass

What I’ve tried and what’s not working:
I’ve looked at this documentation, which is good for having hardcoded queries, but I couldn’t find anything about being able to dynamically build the query code itself.

Any ideas are appreciated :slight_smile:

1 Like
2

Maybe you can create a dictionary in which the keys are the parameter (column) names and values are query functions. Then unpack it like .search(**query_dict).

3 Likes
3

Like this?

 query_dict = {}
    query_dict['available_sms'] = 'q.not_()'
    query_dict['email_confirmation_key'] = 'q.not_()'
    tables.app_tables.users.search(**query_dict)

Sorry… I’m still a bit fuzzy on it

I was able to figure out the specific query assertion, but I’m not understanding how to use the ‘q’ query object to make more general dynamic queries.

@anvil.server.callable
def assert_customer_does_not_exist(first_name_param, last_name_param):
  query_dict = {}
  query_dict['first_name'] = first_name_param  
  query_dict['last_name'] = last_name_param  
  result = tables.app_tables.customers.search(**query_dict)
  if len(result) > 0:
    exception_message = 'user ' + first_name_param + ' ' + last_name_param + ' already exists'
    print(exception_message)
    raise Exception(exception_message)
1 Like
4

On second thought, for queries, I guess you’re actually passing the return value of the function, not the function itself (or the name of the function in a string, as you had).

So I think the first example here would be translated from:

app_tables.machines.search(
  last_seen=q.less_than(
    datetime(day=14, month=12, year=2017),
  )
)

to

query_dict = dict(
  last_seen=q.less_than(
    datetime(day=14, month=12, year=2017),
  )
)
app_tables.machines.search(**query_dict)

or, equivalently

query_dict = {}
query_dict['last_seen'] = q.less_than(datetime(day=14, month=12, year=2017))
app_tables.machines.search(**query_dict)

p.s. Just to be sure, all of the above code assumes you have already imported the query module as `q’ this way:

import anvil.tables.query as q
2 Likes
5

Gotcha! Thanks so much. This is going to save quite a bit of time.

1 Like