CODEWITHSUNDEEP
pythonsqlalchemybulkupdate
paul23
paul23

Reputation: 9435

How to bulk-update using sqlalchemy core? Different columns updated for each entry

While I can update multiple values if I know the columns before hand, I have trouble creating a bulk update if one doesn't wish to hard code the column values.

Consider the following:

stmt = task_table.update() \
    .where(task_table.c.id == bindparam('idx')) \
    .values(bindparam('values'))
vals = [{'idx': task.idx, 
         'values':{'queueStatus': task.queue_status, 'detail':str(task.idx)}}
        for task in actions]

connection.execute(stmt, vals)
connection.commit()

This gives the error AttributeError: Neither 'BindParameter' object nor 'Comparator' object has an attribute 'items'. In the values()

I could change it to something like: 

stmt = task_table.update() \
    .where(task_table.c.id == bindparam('idx')) \
    .values({queueStatus=bindparam('values'), detail=bindparam('detail')})
vals = [{'idx': task.idx, 
         'values':task.queue_status, 'detail':str(task.idx)}
        for task in actions]

connection.execute(stmt, vals)
connection.commit()

However this would require me to always give all columns - even if I don't want to update these columns.

Upvotes: 2

Views: 2298

Answers (1)

Yeray Álvarez Romero
Yeray Álvarez Romero

Reputation: 140

The only difference I can see from your example is that you are trying to unpack multiple values without indicating what is the specific column you want to update, also pay attention to the restricted names used for the bindparams, something like this should work:

stmt = (
    task_table.update()
        .where(task_table.c.id == bindparam('_idx'))
        .values(
        queueStatus=bindparam('_queue_status'),
        detail=bindparam('_detail')
    )
)
vals = [
    {
        '_idx': task.idx,
        '_queue_status': task.queue_status,
        '_detail': str(task.idx),
    }
    for task in actions
]

connection.execute(stmt, vals)
connection.commit()

This is documented in the the sqlalchemy docs:

Upvotes: 1

Related Questions