Best practice for multiple update

[hilmanski]

First Check

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

Commit to Help

• [X] 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[bot]]

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.