Tables (with linked records) to JSON

Anvil Q&A
1

What I’m trying to do:
I have a table with linked records:
image

I’d like to get linked table rows as JSON. Best would be to resolve linked records as JSON array.
Because in tables to_csv exists, but not to_json

Here’s where I am, a tentative of workaround:
Code Sample:

  rescols = app_tables.noco1.list_columns()
  res = app_tables.noco1.search()
  for c in rescols:
    col = c['name']
    print (type(col))
    print(res[col])

that leads to:

<class 'str'>
AttributeError: __getitem__

    at /libanvil/anvil/_server.py:45
    called from /libanvil/anvil/_server.py:68
2

There is no need to cycle through the columns. You can just convert it to dict:

  res = app_tables.noco1.search()
  print(json.dumps(dict(res)))
3

Thanks @stefano.menci but it results in ValueError: sequence of pairs expected

4

Sorry, I was focusing on the JSON part, not on the search part and ended up with the wrong first line.

The problem is that app_tables.noco1.search() returns an iterator, while I assumed res was a row object.

So it should be either:

for row in app_tables.noco1.search():
  print(json.dumps(dict(row)))

or:

row = app_tables.noco1.get(some_field=some_value)
print(json.dumps(dict(row)))
5

Because of linked records I guess, I get:

TypeError: Object of type 'LiveObjectProxy' is not JSON serializable

    at /lib/lib-python/3/json/encoder.py:175
    called from /lib/lib-python/3/json/encoder.py:238
    called from /lib/lib-python/3/json/encoder.py:254
    called from /lib/lib-python/3/json/encoder.py:230
    called from /lib/lib-python/3/json/encoder.py:299
    called from /lib/lib-python/3/json/encoder.py:235
    called from /lib/lib-python/3/json/encoder.py:190
    called from /lib/lib-python/3/json/__init__.py:237
6

You guess right.
You could replace the list of row objects with a list of row objects converted to dict, something like:

for row in app_tables.noco1.search():
  row = dict(row)
  row['links'] = [dict(row_link) for row_link in row['links']]
  print(json.dumps(row))

This may not work on your case, but it should give you the idea.

1 Like
8

Thank you @stefano.menci !