How to delete all rows matching a condition using SqlModel

Question

The documentation of SqlModel about DELETE action: https://sqlmodel.tiangolo.com/tutorial/delete/

shows how to delete a single line using the functions

• .one() - If only a single record is expected

or

• .first() - to get the 1st line from multiple lines found.

def delete_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Youngster")
        results = session.exec(statement)
        hero = results.one()
        print("Hero: ", hero)

        session.delete(hero)
        session.commit()

But how can I delete all the lines matching the condition? I tried using the .all() function

def delete_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Youngster")
        results = session.exec(statement)
        hero = results.all()
        print("Hero: ", hero)

        session.delete(hero)
        session.commit()

But all I get is an error: sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.list' is not mapped

---

My solution uses a loop to iterate over multiple lines, delete each line and commit at the end.

def delete_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Youngster")
        results = session.exec(statement)
        hero = results.all()
        print("Hero: ", hero)

        for result in results:
            session.delete(result)

        session.commit()

Is there a way to do this without the loop?

Answer 1

This is how I do it, by using directly sqlalchemy, on which sqlmodel is built:

from sqlalchemy import delete
with Session(engine) as session:
    statement = delete(Hero).where(<your conditon to get several rows>)
    session.exec(statement)
    session.commit()