Python 使用 WHERE 在 SQLAlchemy Core 中进行批量更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25694234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk update in SQLAlchemy Core using WHERE
提问by jeanc
I have managed to work with the bulk insert in SQLAlchemy like:
我已经设法在 SQLAlchemy 中使用批量插入，例如：
conn.execute(addresses.insert(), [
{'user_id': 1, 'email_address' : '[email protected]'},
{'user_id': 1, 'email_address' : '[email protected]'},
{'user_id': 2, 'email_address' : '[email protected]'},
{'user_id': 2, 'email_address' : '[email protected]'},
])
What I need now is something equivalent for update. I have tried this:
我现在需要的是等价于更新的东西。我试过这个：
conn.execute(addresses.insert(), [
{'user_id': 1, 'email_address' : '[email protected]', 'id':12},
{'user_id': 1, 'email_address' : '[email protected]', 'id':13},
{'user_id': 2, 'email_address' : '[email protected]', 'id':14},
{'user_id': 2, 'email_address' : '[email protected]', 'id':15},
])
expecting that each row gets updated according to the 'id' field, but it doesn't work. I assume that it is because I have not specified a WHERE clause, but I don't know how to specify a WHERE clause using data that is included in the dictionary.
期望每一行都根据“id”字段进行更新，但它不起作用。我假设这是因为我没有指定 WHERE 子句，但我不知道如何使用字典中包含的数据指定 WHERE 子句。
Can somebody help me?
有人可以帮助我吗？
采纳答案by van
Read Inserts, Updates and Deletessection of the documentation. Following code should get you started:
阅读文档的插入、更新和删除部分。以下代码应该可以帮助您入门：
from sqlalchemy.sql.expression 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},
])
回答by Jongbin Park
The session has function called bulk_insert_mappingsand bulk_update_mappings: documentation.
会话有函数调用bulk_insert_mappings和bulk_update_mappings：文档。
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()
回答by Jayme Gordon
@Jongbin Park's solution DID work for me with a composite primary key.
@Jongbin Park 的解决方案 DID 使用复合主键对我有用。
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
其中 Name、StartDate 和 EndDate 都是复合 pk 的一部分。'Comment' 是要在数据库中更新的值
