SQLAlchemy - 在 postgresql 中执行批量更新插入(如果存在,更新,否则插入) [英] SQLAlchemy - performing a bulk upsert (if exists, update, else insert) in postgresql
问题描述
我正在尝试使用 SQLAlchemy 模块(而不是 SQL!)在 python 中编写批量更新插入.
I am trying to write a bulk upsert in python using the SQLAlchemy module (not in SQL!).
我在 SQLAlchemy 添加时收到以下错误:
I am getting the following error on a SQLAlchemy add:
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "posts_pkey"
DETAIL: Key (id)=(TEST1234) already exists.
我有一个名为 posts
的表,在 id
列上有一个主键.
I have a table called posts
with a primary key on the id
column.
在这个例子中,我已经在数据库中有一行 id=TEST1234
.当我尝试 db.session.add()
将 id
设置为 TEST1234
的新帖子对象时,出现上述错误.我的印象是,如果主键已经存在,记录就会更新.
In this example, I already have a row in the db with id=TEST1234
. When I attempt to db.session.add()
a new posts object with the id
set to TEST1234
, I get the error above. I was under the impression that if the primary key already exists, the record would get updated.
如何仅基于主键使用 Flask-SQLAlchemy 进行更新插入?有没有简单的解决方案?
如果没有,我总是可以检查并删除任何具有匹配 id 的记录,然后插入新记录,但这对于我的情况来说似乎很昂贵,我不希望有很多更新.
If there is not, I can always check for and delete any record with a matching id, and then insert the new record, but that seems expensive for my situation, where I do not expect many updates.
推荐答案
SQLAlchemy 中有一个 upsert-esque 操作:
There is an upsert-esque operation in SQLAlchemy:
db.session.merge()
找到这个命令后,我就可以执行upsert了,但值得一提的是,这个操作对于批量upsert"来说很慢.
After I found this command, I was able to perform upserts, but it is worth mentioning that this operation is slow for a bulk "upsert".
另一种方法是获取您要更新插入的主键的列表,并查询数据库以查找任何匹配的 ID:
The alternative is to get a list of the primary keys you would like to upsert, and query the database for any matching ids:
# Imagine that post1, post5, and post1000 are posts objects with ids 1, 5 and 1000 respectively
# The goal is to "upsert" these posts.
# we initialize a dict which maps id to the post object
my_new_posts = {1: post1, 5: post5, 1000: post1000}
for each in posts.query.filter(posts.id.in_(my_new_posts.keys())).all():
# Only merge those posts which already exist in the database
db.session.merge(my_new_posts.pop(each.id))
# Only add those posts which did not exist in the database
db.session.add_all(my_new_posts.values())
# Now we commit our modifications (merges) and inserts (adds) to the database!
db.session.commit()
这篇关于SQLAlchemy - 在 postgresql 中执行批量更新插入(如果存在,更新,否则插入)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!