Preserve columns order when searching (querying) a table

Anvil Q&A
1

Hi
The query doesn’t return the same order of columns.
These are the columns:

print(app_tables.TABLE.list_columns())
[{'name': 'author', 'type': 'string'}, {'name': 'subject', 'type': 'string'}, {'name': 'message', 'type': 'string'}, {'name': 'date', 'type': 'string'}]

These are the columns order from search method:

rows = app_tables.TABLE.search()
for row in rows:
	for e in row:
		print(e)
['message', "..."]
['author', '...']
['subject', '...']
['date', '...']

Is there a way to keep the order without getting the values with the columns list?
Thanks

2

Welcome to the forum!

A row is like a dictionary, there’s no actual ordering of columns. If you want to process them in a specific order, then you’d need to use something else to control the order (such as the list_columns if that’s somehow the order you want to process them in).

3 Likes
3

Specifically, Python’s notion of a dictionary: an object of Python’s dict type.

Later versions of Python did specify an order to the columns – the order in which they were added – but Anvil’s database infrastructure is built on other technology, so it does not preserve order.

3 Likes
4

Hello @sebaro

Building on the previous responses: if printing out fieldnames (with data) in the order produced by list_columns has value for you, then the following server function should serve.

@anvil.server.callable
def print_data_in_listcolumns_order(table_name):
  columns = eval(f"app_tables.{table_name}.list_columns()")
  column_names = [d['name'] for d in columns]
  rows = eval(f"app_tables.{table_name}.search()")
  for row in rows:
      for col in column_names:
          print([col, row[col]])
1 Like
5

With accelerated tables on, that can instead be:

rows = app_tables[table_name].search()

Without accelerated tables:

my_table = getattr(app_tables, table_name)
rows = my_table.search()

No need for eval.

4 Likes
6

Thank you all for replying.

For getting table as json I did this:

res = []
cols = [c['name'] for c in app_tables.contact.list_columns()]
rows = app_tables.contact.search()
for row in rows:
	entry = {}
	for col in cols:
		entry[col] = row[col]
	res.append(entry)
print(json.dumps(res))

Does method “list_columns” list the columns as I see them on the database/table page?

7

It should list all of the columns.

Will it always list them in the displayed order? I don’t know. Probably not.

If you need to know the order in which the IDE displays the columns, there is a reliable place to look: your local Git repository’s anvil.yaml file. An excerpt from one of mine:

allow_embedding: false
correct_dependency_ids: {dep_gmbim1gr96hup: J4EL2HK23CGRRZOC}
db_schema:
  admin_users:
    client: none
    columns:
    - admin_ui: {order: 0, width: 200}
      name: email
      type: string
    - admin_ui: {order: 1, width: 131}
      name: enabled
      type: bool
    - admin_ui: {order: 2, width: 200}
      name: last_login
      type: datetime
    - admin_ui: {order: 3, width: 200}
      name: password_hash
      type: string
...

The admin_ui entries tell you what order the columns are in, and the visual width (in pixels) of the column.

I get a local copy of my apps’ anvil.yaml file via Git:

1 Like
9

I’ll just use a tuple with the names of the columns in the order I need:

#cols = [c['name'] for c in app_tables.contact.list_columns()]
cols = ("author", "subject", "message", "date")
2 Likes
10

That’s doing more work than needed. The following will have the same effect.

for row in rows:
	res.append(dict(row))

And then when you pull items out on the client you can use your tuple of column names to control the order in which they get processed.

You’d only want your original code if you want to pass a subset of the columns back to the client, where you’re not processing all the columns, just some of them.

11

This is for backup to JSON with the same structure as previous backups.

12

You may also want to take a look at the Data Tables import/export tool that was posted on the forum a couple of years back.

1 Like