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.