How to delete multiple rows with SQLAlchemy ORM

by

I got pretty frustrated trying to figure this out earlier. The documentation was helpful to a point, but I couldn’t find any good examples online that really illustrated how to delete multiple rows in SQLAlchemy the correct way with ORM. What I mean by this is, not selecting the rows you want to delete and deleting them one by one, but rather something that produces something like the following SQL:

delete from SOME_TABLE where ID = 3;
SQL

How to delete everything from a table with SQLAlchemy ORM

First let’s look at how to delete everything from a table with SQLAlchemy ORM, i.e. delete from TABLE;:

import typing
import pathlib

import uvicorn

from fastapi import FastAPI, Depends

from sqlalchemy import Column, Integer, String, select, delete
from sqlalchemy.orm import declarative_base
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from pydantic import BaseModel

Base = declarative_base()

# This is our SQLAlchemy model used with the database
class Thing(Base):
    __tablename__ = "thing"
    id = Column(Integer, primary_key=True)
    text = Column(String, nullable=False)

pathlib.Path("/opt/data").mkdir(exist_ok=True)
engine = create_async_engine("sqlite+aiosqlite:////opt/data/db.sqlite3")
async_session_maker = async_sessionmaker(engine)

async def create_db_and_tables():
    async with engine.begin() as conn:
        # We drop all tables on startup just for demonstration purposes.
        # Don't do this in production!
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

async def get_async_session() -> typing.AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        yield session


app = FastAPI()

# This is our Pydantic model used with the API
class ThingModel(BaseModel):
    id: typing.Optional[int]
    text: str

# We use the "startup" event to create our database and tables
@app.on_event("startup")
async def on_startup(session: AsyncSession = Depends(get_async_session)):
    await create_db_and_tables()

# ... some stuff missing for brevity ...

@app.delete("/things")
async def delete_all_things(session: AsyncSession = Depends(get_async_session)):
    await session.execute(delete(Thing))
    await session.commit()

@app.post("/things")
async def post_thing(thing: ThingModel, session: AsyncSession = Depends(get_async_session)):
    session.add(Thing(text=thing.text))
    await session.commit()

# ... more stuff missing for brevity ...

if __name__ == "__main__":
    uvicorn.run("main:app", host="0.0.0.0", port=8080, reload=True)
Python

Note: the above code is taken from my FastAPI + SQLAlchemy tutorial – check it out if you want to learn more about those things.

Up there on line 8 you can see we need to import delete from the sqlalchemy package. Easy enough. Then on lines 52-55, all we need to do is call delete on the ORM object class (not an instance) and it’ll delete everything in the table. Don’t forget to session.commit()!

How to delete only some rows from a table in SQLAlchemy ORM with a where clause

Now let’s look at how to do the same thing but with a where clause. For this one, let’s take a list of Thing IDs from the client and delete all Things that they belong to. This is sort of like when you select multiple emails in your email client and delete them.

@app.delete("/things")
async def delete_all_things(thing_ids: list[int], session: AsyncSession = Depends(get_async_session)):
    await session.execute(delete(Thing).where(Thing.id.in_(thing_ids)))
    await session.commit()
Python

That’s all there is to it. Please note the potential gotcha up there, the in operator is actually in_ with an underscore after it. That’s presumably because in is a reserved word in Python. They could have actually just made the method in, but probably chose not to because some IDEs will still highlight it as if it were the reserved word, such as in for foo in bar.

Conclusion

To delete multiple rows from a table the correct way in SQLAlchemy ORM, you just need to import delete from the sqlalchemy package, then call it with the ORM class representing the table you want to delete from as the argument. Without any .where(...) clause method call chained after it, it’ll delete everything. Add a .where clause like you would normally, but chained right after the call to .delete(...) to do that instead.