How do I bulk delete with conditions?

[nghianv19940]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

@dialplan.put("/dialplans")
async def put_dialplan(extension_in: ExtensionIn, offset: int = 0,
                       limit: int = Query(default=100, lte=100),
                       session: Session = Depends(get_session)):
    # delete multiple dialplan records with condtions
    session.delete()

Description

I can't find any instruction on https://sqlmodel.tiangolo.com/tutorial/ for this, please help!.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10

Additional Context

No response

[meirdev]

You can use delete from sqlalchemy:

from sqlalchemy import delete

stmt = delete(Item).where(Item.id.in_([1, 2, 3]))
session.exec(stmt)
session.commit()

[nghianv19940]

Thanks @meirdev,

I've managed to do delete this way:

My model:

from sqlmodel import SQLModel, Field as sqlmodel_field

class Extension(SQLModel, table=True):
    \_\_tablename\_\_ = 'extensions'
    id: Optional[int] = sqlmodel_field(default=None, primary_key=True)
    context: str
    exten: str
    priority: str
    app: str
    appdata: str = None

Delete code:

session.execute(
        delete(Extension).where(
            col(Extension.exten).like(
                f'{my_var}%')).execution_options(
            synchronize_session="fetch"))

[tiangolo]

Thanks for the help here @meirdev ! 👏 🙇

Thanks for reporting back and closing the issue @nghianv19940 👍

[mfornet]

Deleting this way doesn't pass type check. I have the following code

from sqlmodel import delete
session.exec(delete(Tags).where(Tags.registry_id == entry.id))

And pypy will fail with:

error: No overload variant of "exec" of "Session" matches argument type "Delete"  [call-overload]
hub/api/v1/registry.py:211: note: Possible overload variants:
hub/api/v1/registry.py:211: note:     def [_TSelectParam] exec(self, statement: Select[_TSelectParam], *, params: Mapping[str, Any] | Sequence[Mapping[str, Any]] | None = ..., execution_options: Mapping[str, Any] = ..., bind_arguments: dict[str, Any] | None = ..., _parent_execute_state: Any | None = ..., _add_event: Any | None = ...) -> TupleResult[_TSelectParam]
hub/api/v1/registry.py:211: note:     def [_TSelectParam] exec(self, statement: SelectOfScalar[_TSelectParam], *, params: Mapping[str, Any] | Sequence[Mapping[str, Any]] | None = ..., execution_options: Mapping[str, Any] = ..., bind_arguments: dict[str, Any] | None = ..., _parent_execute_state: Any | None = ..., _add_event: Any | None = ...) -> ScalarResult[_TSelectParam]

I see this is being discussed in #909