SQLAlchemy-在Postgresql中执行批量更新（如果存在，更新或插入） [英] SQLAlchemy - performing a bulk upsert (if exists, update, else insert) in postgresql

问题描述

我正在尝试使用SQLAlchemy模块（而不是在SQL中！）在python中写一个批量upsert。

I am trying to write a bulk upsert in python using the SQLAlchemy module (not in SQL!).

我在SQLAlchemy add上遇到以下错误：

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 设置为<$ c $的新posts对象c> 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：

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屋！