Bulk update in SQLAlchemy Core using WHERE
Read the Updating and Deleting Rows with Core section of the tutorial. The following code should get you started:
from sqlalchemy import bindparam
stmt = addresses.update().\
where(addresses.c.id == bindparam('_id')).\
values({
'user_id': bindparam('user_id'),
'email_address': bindparam('email_address'),
})
conn.execute(stmt, [
{'user_id': 1, 'email_address' : '[email protected]', '_id':1},
{'user_id': 1, 'email_address' : '[email protected]', '_id':2},
{'user_id': 2, 'email_address' : '[email protected]', '_id':3},
{'user_id': 2, 'email_address' : '[email protected]', '_id':4},
])
The session has function called bulk_insert_mappings
and bulk_update_mappings
: documentation.
Be aware that you have to provide primary key in mappings
# List of dictionary including primary key
user_mappings = [{
'user_id': 1, # This is pk?
'email_address': '[email protected]',
'_id': 1
}, ...]
session.bulk_update_mappings(User, user_mappings)
session.commit()
@Jongbin Park's solution DID work for me with a composite primary key. (Azure SQL Server).
update_vals = []
update_vals.append(dict(Name='name_a', StartDate='2020-05-26 20:17:32', EndDate='2020-05-26 20:46:03', Comment='TEST COMMENT 1'))
update_vals.append(dict(Name='name_b', StartDate='2020-05-26 21:31:16', EndDate='2020-05-26 21:38:37', Comment='TEST COMMENT 2'))
s.bulk_update_mappings(MyTable, update_vals)
s.commit()
where Name, StartDate, and EndDate are all part of the composite pk. 'Comment' is the value to update in the db