[FIXED] Flask SQLAlchemy bulk deleting records

Issue

I'm new to SQLAlchemy and I cannot work out how to delete multiple records at once. The documentation states that it is the same as .add() but using .delete() instead. It works if I append .first(), however I can't use .all() otherwise I get the following error:

sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.list' is not mapped

Here is the code:

delete = Skilljoin.query.filter_by(staffid=30).all()
db.session.delete(delete)
db.session.commit()

Thanks in advance.

Solution

Skilljoin.query.filter_by(staffid=30).all() returns a list of the result of the query.

To delete either use:

[Edited to add sqlalchemy 2.0 style]

After sqlalchemy 1.4 (2.0 style)

skilljoins = db.session.execute(
    db.query(Skilljoin).filter_by(staffid=30)
    ).scalars()
for skilljoin in skilljoins :
    db.session.delete(skilljoin)
db.session.commit()

or

db.session.execute(
    db.delete(Skilljoin).filter_by(staffid=30)
    )
db.session.commit()

Prior sqlalchemy 1.4 (1.0 style)

skilljoins = Skilljoin.query.filter_by(staffid=30).all()
for skilljoin in skilljoins :
    db.session.delete(skilljoin)
db.session.commit()

or

Skilljoin.query.filter_by(staffid=30).delete() 

(https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.delete)

Answered By - JBLaf