SQLAlchemy Updating multiple fields on multiple objects with a single database query

Question

I have a list of Category model objects that have their name, parent_id and archive fields changed and I need to update those objects in the database. Now I do it in a loop - I get an object from the database by id, set new field values and update the object. This is not optimal and I would like to know how can I do this with a single database query?

def update(id: int, instance: Category) -> None:
        instance.id = id
        instance = session.merge(instance)
        session.commit()
        return instance

def update_categories(categorues_to_update: list[Category]):
    for category_to_update in categories_to_update:
        category = session.execute(select(Category).where(Category.id==category_to_update.id))
        category.name = category_to_update.name
        category.parent_id = category_to_update.parent_id
        category.archive = category_to_update.archive
        update(category.id, category)

Answer 1

You can use a case statement with update like so, you still use a loop, but the query is made only once after the loop is completed (one query to db)

from sqlalchemy import update, case
case_1 = []
case_2 = []
case_3 = []
for category_to_update in categories_to_update:
    case_1.append((Category.id == category_to_update.id, category_to_update.name))
    case_2.append((Category.id == category_to_update.id, category_to_update.parent_id))
    case_3.append((Category.id == category_to_update.id, category_to_update.archive))
statement = update(Category).values(
    name=case(*case_1, else_=Category.name),
    parent_id=case(*case_2, else_=Category.parent_id),
    archive=case(*case_3, else_=Category.archive),
)
session.execute(statement)