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?

About the difference between filter and filter_by: https://stackoverflow.com/questions/2128505/whats-the-difference-between-filter-and-filter-by-in-sqlalchemy — Ilja Everilä, Feb 17 '18 at 22:25
The latter exception is a result of having 0 selectables in your query. The filter condition looks like it's plain old Python objects and evaluates to True or False in Python. `query('items')` creates a query with a single entity, the literal SQL `items`, which would look like `SELECT items` without the delete. The exception's message is a bit misleading in this case. — Ilja Everilä, Feb 17 '18 at 22:33
Hi, I want to do something like this but I want make the epoch_time as the beginning of a new month rather than a fixed deltatime. how can I achieve that? — Reactive_learner, Apr 24 '19 at 08:29

PRMoureu · Accepted Answer · 2018-02-17T09:32:49.450

14

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()

edited Feb 17 '18 at 09:32

answered Feb 17 '18 at 09:16

PRMoureu

12,817
6
38
48

2

Note that this method doesn't cascade delete, while using `db.session.delete(ItemModel.query.filter(ItemModel.time <= epoch_time).first())` cascades. [This link](https://github.com/inveniosoftware/invenio/issues/2419) saved me after lots of searching and trial and error. – Khaled Hamed Aug 15 '19 at 22:43
1

Thanks for this comment @KhaledHamed, worth noting indeed ! This answer is relevant too : https://stackoverflow.com/a/19245058/6655211 – PRMoureu Aug 16 '19 at 05:59

Deleting list of items in SQLAlchemy Flask

1 Answers1