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!