Deleting old items in SQLAlchemy

Looking to delete old entries from a table because they’ve expired? Want to do it in an elegant way?

I usually like to split this kind of functionality into two different parts: the method that does the deleting and a static method that can be invoked from cron, a celery scheduled task or a django command.

As an example, let’s say we want to delete all the log entries on a system that are over 181 days (6 months) old.

Assuming a model like:

import datetime

class LogEntry(db.Model):
    __tablename__ = 'log_entries'

    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(80))
    timestamp = db.Column(db.DateTime, default=datetime.datetime.utcnow)

First we add a method on the model that deletes expired log entries.

    @classmethod
    def delete_expired(cls):
        expiration_days = 181
        limit = datetime.datetime.now() - datetime.timedelta(days=expiration_days)
        cls.query.filter(cls.timestamp <= limit).delete()
        db.session.commit()

You’ll notice the use of @classmethod, that’s needed so we can invoke from the class and not from an object, as I’m doing in the next function (the one that can be called from a celery scheduled task, for instance):

def delete_expired_logs():
    LogEntry.delete_expired()

And with this you keep it elegant: all the relevant model relevant information in the model class, so if someone changes the timestamp field to another name, they will only have to change it in the delete_expired method, but you can easily call from somewhere else like a task or command.

Want to get python/AWS tips on your email? Just subscribe!

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax