Best practice for multiple update

[hilmanski]

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

Example code multiple update data


def update_heroes():
    with Session(engine) as session:
         statement = select(Hero).where(Hero.name == "Spider-Boy")  # 
        results = session.exec(statement)  # 
        hero_1 = results.one()  # 
        print("Hero 1:", hero_1)  # 
        statement = select(Hero).where(Hero.name == "Captain North America")  # 
        results = session.exec(statement)  #         
        hero_2 = results.one()  # 
 .....

Description

Based on the documentation, when we want to update multiple data. We need to do N+1 query:
doc multiple update

Are there any other way to do it with only select needed data once?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9

Additional Context

No response

[aminalaee]

@hilmanski Do you mean you want to do a bulk update?

If that's the case I think an update method would be needed the same as select, something like this maybe:

statement = update(Hero).set(Hero.name == "Spider").where(Hero.name == "Spider-Boy")
number_of_modified = session.exec(statement)  # UPDATE hero SET name = 'Spider' WHERE name = 'Spider-Boy'

I think that's also the case for bulk delete, as of now I can see only single object-level deletes.
If @tiangolo confirms I can work on them.

[fredrikaverpil]

Any news on this?

It seems very inefficient to select for each update when you have a large number of entities to update.

I'm investigating whether to adopt SQLModel for use with FastAPI and wondering if there have been no efforts to solve this in 8 months?

Since SQLModel models are compatible with SQLAlchemy models, I was able to do the following based on the the SQLAlchemy docs:

from sqlmodel import Session, create_engine, update

# create engine, session etc here...

session.execute(  # note, I'm using SQLAlchemy's session.execute to avoid typing errors here
    update(User).
    where(User.name == "sandy").
    values(fullname="Sandy Squirrel Extraordinaire")
)

However, I'm concerned about "strangling" the database if you were to update several million records without any sort of batching/sleeping for every N records processed...

More from the SQLAlchemy docs:

• https://docs.sqlalchemy.org/en/14/orm/session_basics.html#orm-expression-update-delete
• https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#bulk-operations

[guilherme-miyake]

Hey @fredrikaverpil, had the same question and found this:

• looking at the sqlalchemy session stub I saw this method and it worked for me: https://docs.sqlalchemy.org/en/14/orm/session_api.html#sqlalchemy.orm.Session.add_all
• knowing about the method above, I found this stackoverflow thread: https://stackoverflow.com/questions/58517491/sqlalchemy-bulk-save-objects-vs-add-all-underlying-logic-difference
• For postgres there is also a upsert to handle both updates and inserts: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#insert-on-conflict-upsert
  So there are two APIs to actually doing that, but they will probably depend a lot on the engine you are using

[tiangolo]

@hilmanski Your code example doesn't include any update, so I'm not sure I understand the question. But changes are sent to the DB on session.commit(), not before.

Also, as all the work is really done by SQLAlchemy, you can apply here the same solutions you could find there.

[github-actions]

Assuming the original need was handled, this will be automatically closed now. But feel free to add more comments or create new issues or PRs.

[Yangeok]

Any update about multiple update feature?

[dev-bilal-raza]

Hey @tiangolo @Yangeok,

I have created a function that can update multiple fields, please check function below ⬇️.

from typing import Dict, Any
from app.db.db_utils import DB_SESSION
from app.models.user_models import User
from app.utils.api_messages import MESSAGES
from app.utils.api_statuses import STATUS


class UserInUpdate(BaseModel):
    username: Optional[str] = None
    email: Optional[str] = None
    password: Optional[str] = None
   

def update_user(user_id: int, user_details: UserInUpdate, db: DB_SESSION) -> Dict[str, Any]:
        # Fetch the user by id
        user = db.get(user, user_id)
        if not user:
            return {
                "status": STATUS["NOT_FOUND"],
                "message": MESSAGES["NOT_FOUND_MESSAGE"]
            }
        
        # update user fields with provided details
        user_to_update = user_details.model_dump(exclude_unset=True)
        for key, value in user_to_update.items():
            setattr(user, key, value)
        
        # commit changes to the database
        db.add(user)
        db.commit()
        db.refresh(user)
        
        return {
            "status": STATUS["SUCCESS"],
            "message": "User updated successfully",
            "updated_user": user
        }

Here's a step-by-step explanation:

• The function first attempts to fetch the user by their ID using db.get(User, user_id).
• If the user is not found, it returns a status of NOT_FOUND with an appropriate message.
• If the user is found, it updates the user's fields with the provided details from user_details. Only the fields that are set in user_details will be updated, thanks to the exclude_unset=True parameter.
• The updated user object is then added to the database session, and the changes are committed.
• The function refreshes the user object to reflect the latest state from the database.
• Finally, it returns a success status along with the updated user object.

[Nikola-Milovic]

Since session.execute is deprecated now, what is the current alternative to

 stmt = (
     update(sop_model.Model)
    .where(sop_model.Model.id == model.id)
     .values(...)
)
await session.execute(stmt)

I do not want to fetch the model then session.add it, I just want to update it