Update multiple rows in ORM model at once

Question

I got orm object like this:

class Fruit(ModelBase):
    __tablename__ = "fruits"
    id = Column(BigInteger, nullable=False)
    name = Column(Unicode)
    price = Column(Integer)

and my table looks like this:

+----+--------+-------+
| id |  name  | price |
+----+--------+-------+
|  1 | apple  |   100 |
|  2 | carrot |   200 |
|  3 | orange |   300 |
+----+--------+-------+

I want to update my orm object with data so my table would look like this:

+----+--------+-------+
| id |  name  | price |
+----+--------+-------+
|  1 | apple  |   500 |
|  2 | carrot |   200 |
|  3 | orange |   600 |
+----+--------+-------+

Fruits

updated_data = [{"id": 1, "name": "apple", "price": 500}, {"id": 3, "name": "orange", "price": 600}]

How can I update my orm object Fruits having data from updated_data list?

I tried with

update(Fruit).where(Fruit.id == updated_data.id).values(updated_data)

but it doesn't work.

score 1 · Answer 1 · answered Aug 24 '23 at 11:06

You could use an executemany approach, as stated in the SQLAlchemy documentation:

To support UPDATE in an “executemany” context, where many parameter sets will be invoked against the same statement, the bindparam() construct may be used to set up bound parameters; these replace the places that literal values would normally go:

I.e., for your specific case, this could be:

from sqlalchemy.orm import Session
from sqlalchemy.sql.expression import bindparam

query = (
    update(Fruit)
    .where(Fruit.id == bindparam("fruit_id"))
    .values(
        name=bindparam("fruit_name"),
        price=bindparam("fruit_price"),
    )
)

with Session(engine) as session:
    cursor_result = session.execute(
        statement=query,
        params=[
            {
                "fruit_id": 1, 
                "fruit_name": "apple", 
                "fruit_price": 500
            },
            {
                "fruit_id": 3, 
                "fruit_name": "orange", 
                "fruit_price": 600
            },
        ],
    )

Warnings:

The cursor_result will not have access to the updated rows. If you need to return the updated ORM objects, you will need to do a subsequent SELECT to fetch them. The reason for this is explained by the SQLAlchemy library author in this GitHub discussion with another developer.

You really need to name the dictionary items in the params argument in a different way than the Fruit ORM object attributes (columns), as the internals of this mapping functionality requires to be like that. Otherwise you will get an exception, like e.g.

sqlalchemy.exc.CompileError: bindparam() name 'price' is reserved for
  automatic usage in the VALUES or SET clause of this insert/update
  statement. Please use a name other than column name when using
  bindparam() with insert() or update() (for example, 'b_price')

score 0 · Answer 2 · answered Dec 22 '21 at 22:38

I think your code would work if you were to do one row at a time instead of passing the whole list to values(). Something like this should do it:

from sqlalchemy.orm import Session

with Session(engine) as session:
    for fruit in updated_data:
        session.execute(
            update(Fruit).
            where(Fruit.id == fruit["id"]).
            values(fruit)
        )
    session.commit()

If running one query for each row turns out to be slow, you could check out this answer here: SQLAlchemy update multiple rows in one transaction

Update multiple rows in ORM model at once

2 Answers2