Data table referencing a deleted record

Anvil Q&A
1

I’ve got some data stored across multiple tables and I’ve used the linked records feature to relate them.

Anyway, I’ve deleted some data from one of the master tables - and it didn’t cascade down to the detail table. Which is fine.

But there’s too much data to delete to do it by hand - and if I try and use

app_tables.things.search(parent=None)

I get an empty set back. I assume this is because the parent_id is still set - it’s just referencing a deleted row.

Is there an easy way to pull back the rows with invalid references so I can get rid of them?

Cheers

Baz

2

If you attempt to access a column in a non-existent row, you get anvil.tables.TableError.

So to perform a cascading delete, you can iterate over the linking table and handle that exception:

@anvil.server.callable
def cascade_the_delete():
  for table2_row in app_tables.table2.search():
    try: 
      # Try to access the linked row
      table2_row['table1_row']['some_column']
    except anvil.tables.TableError:
      # The linked row doesn't exist, so delete the linking row
      table2_row.delete()
      # Alternatively, could set table2_row['table1_row'] = None to 
      # remove the dangling link rather than deleting the row

As I’ve said in that final comment, if you wanted to tidy up the dangling links rather than deleting the entire row, you could replace table2_row.delete() with table2_row['table1_row1'] = None.

It goes without saying, but … I recommend testing anything that deletes data in a dummy app before implementing in the production app!

Does this solution work for you?

1 Like
3

Thanks Shaun.

It almost works - I’m triggering my clean up code as an HTTP API call and it’s timing out - but I’m getting there. Thanks for your help

B