SQLAlchemy Bulk Update with Jagged Columns

Viewed 26

I have a python code that performs a bulk update of rows in a database:

db.execute(
            update(MyObject.__table__).where(row_id == bindparam("row_id")),
            update_list,
        )

where update_list contains a list of rows to be updated and each row does not contain the exact same columns (hence the term "jagged").

If I have:

update_list = [
{"row1_colA": "content1A", "row1_colB": "content1B"},
{"row2_colA": "content2A", "row2_colB": "content2B", "row2_colC": "content2C"}
]

the behavior I observe is that only the database columns contained in the first list entry get updated for each row.

However, if I have:

update_list = [
{"row1_colA": "content1A", "row1_colB": "content1B", "row1_colC": "content1C"},
{"row2_colA": "content2A", "row2_colB": "content2B"}
]

I see an error like this:

sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'row1_colC', in parameter group 1

I believe this is an implementation limitation of SQLAlchemy. I'm wondering if anyone has a clever method to get around this? Perhaps via column defaults or onupdate usage?

I really don't want to have to loop over my rows and group based on which columns are being updated as it seems inefficient.

0 Answers
Related