Skip to content

ORM Bulk UPDATE by Primary Key - WHERE statement not supported #9595

@tamirg-cye

Description

@tamirg-cye

Describe the use case

As part of updating bulk data in the Sqlalchemy query, I would like to add a condition that updates bulk of students.
I would like to update all the students over the age of 20.

Something like(the example not working):

await db.execute(
    update(Student).where(age>20),
    [stud.dict() for stud in students]
)
await db.flush()

Databases / Backends / Drivers targeted

Postgres with asyncpg driver

Example Use

Model:

class Student(Base):
    __tablename__ = "students"
    id = Column(
        String,
        primary_key=True,
        default=lambda: uuid.uuid4().hex,
    )
    name = Column(String, nullable=False)
    age = Column(Integer, nullable=False)

Students:

[
    {"id": 1, "name": "Eugene H. Krabs"},
    {"id": 2, "name": "Spongebob Squarepants"},
    {"id": 3, "name": "Patrick Star"},
    {"id": 4, "name": "Eugene H. Krabs"}
]

Query:

await db.execute(
    update(Student),
    [stud.dict() for stud in students]
)
await db.flush()

Additional context

During running the query we get this message:
WHERE clause with bulk ORM UPDATE not supported right now. Statement may be invoked at the Core level using session.connection().execute(stmt, parameters)

Metadata

Metadata

Assignees

No one assigned

    Labels

    duplicateThis issue or pull request already existsexpected behaviorthat's how it's meant to work. consider the "documentation" label in additionormuse casenot really a feature or a bug; can be support for new DB features or user use cases not anticipated

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions