session.execute or session.exec when updating data using the update() method

[OndrejIT]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

await self.session.execute(
    update(File).where(File.id.in_(data.files)).values(status=File.Status.DELETING),
)

await self.session.exec(
    update(File).where(File.id.in_(data.files)).values(status=File.Status.DELETING),
)

Description

How to use update()?
self.session.execute or self.session.exec?

I would like to use the sqlmodel version of exec everywhere in the code, however I am running into a problem in typehint where the update method is not allowed to be accepted, so IDE throws me an error in that case, even though the code seems functional and the sql code the same in both cases.

Thank you.

Operating System

Linux

Operating System Details

docker.io/python:3.12-alpine3.19

SQLModel Version

0.0.14

Python Version

python:3.12

Additional Context

No response

[nathanweir]

I'm interested in this as well. As far as I can tell, the SQLModel docs showcase performing updates with the following:

statement = select(Hero).where(Hero.name == "Spider-Boy")
results = session.exec(statement)
hero = results.one()
hero.age = 16
session.add(hero)
session.commit()

However, this causes two SQL statements to fires (a SELECT followed by an UPDATE), whereas the command @OndrejIT has shared only fires one UPDATE. Unfortunately, the type errors mean we're not benefiting much from SQLModel, here.

When I tested this myself I found my type errors were due to update() being a SQLAlchemy function which doesn't seem to have a SQLModel equivalent?

Avoiding the redundant SELECT is an important performance consideration so I'd love to learn if there's a well supported way of doing this!

[hhartzer]

Related to this, I'd like to be able to do things like UPDATE account SET value = value + 100

As far as I know if you do this like account.value += 100, the resulting SQL will not look like the above.

Edit take 2:

Here's my workaround that requires two type ignores to appease mypy (understandably!)

statement = (
    update(database.Account)
    .where(database.Account.id == account_id)
    .values(balance=database.Account.balance + amount)
)
self.session.exec(statement)

Maybe integers could expose a add/subtract method in sqlmodel? Not sure if that would be the cleanest option or not.