Delete Rows in the Data Table Periodically

Anvil Q&A
1

I want to delete rows from a data table periodically based on a set timed period to fulfill some data retention policy.

I have a column for indicating if the record is still active, and another column indicating the date and time when the column was set to inactive.
I want to have a running job which checks for rows that are inactive and whether they are past a certain amount of time (i.e. 1 week old). If the row fulfills both conditions, they get deleted.

I am familiar that this can be done via SQL Jobs.
However, my question is, can this be done within Anvil?
If so, how should I go about it?

2

Absolutely. You should be able to write a function to do that and then schedule it with Scheduled Tasks

If you need a hand with how to write the function post what you have and we will be able to help

3 Likes
3

I have an app that generates PDF files and keeps them for a few days. The table has 3 columns: file_name, pdf and expiration. I scheduled this task to run nightly:

@anvil.server.background_task
def delete_expired_pdf():
    for row in app_tables.pdffiles.search(expiration=q.less_than(datetime.now())):
        print(f"Deleting expired PDF {row['file_name']}")
        row.delete()
2 Likes
4

Thank you so much for the answers!

I will come back to this thread should I get stuck

1 Like
5

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 instead of deleting rows one at a time. See Delete set of rows from data table more efficiently - #2 by jshaffstall.

(ScheduledTasks are, of course, still the way to go to automate performing this task periodically.)

4 Likes