Quickly delete rows in table

Anvil Q&A
1

Hi,

At the moment, the below code is how I delete all rows in the table, and it is slow:

rows = app_tables.my_table.search(id=1)
  for row in rows:
    r.delete()

I tried the following code, but doesn’t work. Any help is greatly appreciated

rows = app_tables.my_table.search(id=1)
rows.delete()
1 Like
2

Good morning, Tony.

From Updating and Deleting Rows:

You can delete all rows in a table by calling the table’s delete_all_rows() method. All data is cleared from the database for this table!

# Delete all rows in the table
app_tables.people.delete_all_rows()
3 Likes
3

Hi @p.colbert,

Thanks for the information, it’s great. How about deleting all rows as the search results

rows = app_tables.my_table.search(id=1)
4

Good question. Maybe others know a better way, but in that case, I believe you need to loop through the row objects and delete them as you mentioned above, asfaik.

1 Like
5

With SQL databases, a series of deletions (and/or other changes) tends to go much faster when it is wrapped in a transaction. That might be true here, too.

1 Like
6

Thanks so much @p.colbert and @alcampopiano, I will try sql and let you know the result

1 Like
7

Actually, I meant these (Anvil’s) Transactions. Not everyone has SQL access to their Anvil tables.

1 Like
8

Thanks for the reminder. I use the open source server with external database. Hopefully, sql works:
https://anvil.works/docs/data-tables/indexing-and-sql

9

Could we pass a query as a parameter to delete_all_rows()?

1 Like
10

I tried and it doesn’t work, all rows are deleted regardless of the query. Thanks for the suggestion

11

Hi @Tony.Nguyen,

There’s currently no simple way to delete all rows returned by a search iterator. As @alcampopiano suggested, you need to loop through the rows to delete them.

Please feel free to create a Feature Request if it’s something you think would be helpful :slight_smile:

5 Likes
12

Updating this old thread so that future forum searchers aren’t misled…

It is now possible to delete all rows returned by a search iterator using the delete_all_rows() method.

See Delete set of rows from data table more efficiently - #2 by jshaffstall

4 Likes