Most efficient way to get get multiple columns from data table?

Anvil Q&A
1

I quite often want to get multiple columns from the same row in a data table (I guess this is a common requirement). I’ve written this function to do it:

def getMultipleGameStateEntries(listOfColumns):
  gameStateRow = app_tables.game_state.get(uid=getUid())
  listOfValues = []
  for column in listOfColumns:
    try:
      listOfValues.append(gameStateRow[column])
    except:
      recordError("getMultipleGameStateEntries", f"Couldn't find column {column} in game state row: {gameStateRow}")
  return listOfValues

My question is: is this an efficient way to do it?

More specifically, once I’ve obtained a row item from a data table, is there any cost to accessing values from it? Each time I read a value from it, is that just like reading a value from a dict, or is it going back to the data table to check?

Thanks!

2

The game_state.get function reads from the database once and stores the values in the row object, so you are not working with the database every time you read a value from a column. The row object already has all of the values.

Well, almost.

The standard app_tables will read most values with the exception of simple object and linked columns (and maybe some other column types that I am forgetting now).

The new accelerated tables will read all the column values, unless you use fetch_only or other methods to decide what values must immediately loaded vs lazily loaded. The accelerated tables are still officially in beta, but I use them in all my production apps. They provide more control over what’s immediately cached, and they are waaay faster than the old ones, even if you don’t tweak the caching.

1 Like
3

This is really helpful, thanks.

Almost all of my columns are simple objects. I’m already using accelerated tables though, so it sounds like what I’ve written is efficient.

Still, I feel like I’m reinventing the wheel here. Is there a more standard syntax for obtaining several columns from a row?

4

The game_state.get does get several columns.

You can convert it to a dictionary with gameStateRow = app_tables.game_state.get(uid=getUid()), but I don’t know if this will trigger the loading of all columns, including the ones not included in fetch_only (if you do use fetch_only), or you can convert it to a list with list(gameStateRow.values()), but this would not include the call to recordError for missing columns.

I don’t see anything wrong with your little function:

  • get the row from the database, optionally using fetch_only to avoid reading large unused simple object values
  • cycle through the columns you need and either keep their value or log the error

Perhaps the only missing thing is a listOfValues.append(None) after the except, just to make sure the number of values in the list matches the number of expected values, but I don’t know if that is a requirement.

5

Again, this is really helpful, thanks.

I do have some really big objects in some of the columns, so it seems to me that using fetch_only would be helpful, but I am struggling with the syntax.

I tried this:

  gameStateRow = app_tables.game_state.get(q.fetch_only(listOfColumns), uid=getUid())

But it didn’t work (TypeError: columns must be strings), I think because listOfColumns is a list (of strings). Can you give me any pointers on how I can sort out the syntax? Many thanks!

6

Quick suggestion without trying: try to expand the list with q.fetch_only(*listOfColumns).

1 Like
7

That did it, thanks!

Here’s the improved function for anyone else trying to make something similar:

def getMultipleDataTableColumns(listOfColumns):
  dataTableRow = app_tables.my_data_table.get(q.fetch_only(*listOfColumns), uid=getUid())
  listOfValues = []
  for column in listOfColumns:
    try:
      listOfValues.append(dataTableRow[column])
    except Exception as e:
      recordError("getMultipleGameStateEntries", e)

  return listOfValues