Deleting list of items in SQLAlchemy Flask

Question

I'm trying to delete a list of items from using Flask and SQLAchlemy in an elegant way. In my case, I want to delete old items (items that are less than a certain time are considered old) from my database.

Here is my python model object:

class ItemModel(db.Model):
    __tablename__ = 'items'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    time = db.Column(db.Integer)

Now, I tried to delete the items using the following filter_by query:

def delete_from_db(self):
    db.session.query('items').filter_by(self.time <= epoch_time).delete()
    db.session.commit()

It just ends up giving me the error:

filter_by() takes 1 positional argument but 2 were given

If I try this using the filter function, instead of filter_by, I also receive an error:

   def delete_from_db(self):
        db.session.query('items').filter(self.time <= epoch_time).delete()
        db.session.commit()

sqlalchemy.exc.InvalidRequestError: This operation requires only one Table or entity be specified as the target.

Finally, I decide to go back to the basics and use sql code and this works but I don't think it is very elegant:

def delete_from_db(self):
    from sqlalchemy.sql import text
    cmd = 'delete from items where time <= :time'
    db.engine.execute(text(cmd), time = epoch_time)
    db.session.commit()

Is there a more elegant way to do this with Flask and SQLAchlemy?

Answer 1

This should work with a class method if you want to manage this from the model :

@classmethod
def delete_from_db(cls):
    cls.query.filter(cls.time <= epoch_time).delete()
    db.session.commit()

or the same, maybe cleaner, with a static method :

@staticmethod
def delete_from_db():
    ItemModel.query.filter(ItemModel.time <= epoch_time).delete()
    db.session.commit()