Python SQLAlchemy - 在 postgresql 中执行批量更新插入（如果存在，更新，否则插入）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25955200/
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
SQLAlchemy - performing a bulk upsert (if exists, update, else insert) in postgresql
提问by mgoldwasser
I am trying to write a bulk upsert in python using the SQLAlchemy module (not in SQL!).
我正在尝试使用 SQLAlchemy 模块（而不是 SQL！）在 python 中编写批量更新插入。
I am getting the following error on a SQLAlchemy add:
我在 SQLAlchemy 添加上收到以下错误：
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "posts_pkey"
DETAIL: Key (id)=(TEST1234) already exists.
I have a table called postswith a primary key on the idcolumn.
我有一个表posts，id列上有一个主键。
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 idset to TEST1234, I get the error above. I was under the impression that if the primary key already exists, the record would get updated.
在这个例子中，我已经在数据库中有一行id=TEST1234. 当我尝试db.session.add()使用id设置为的新帖子对象时TEST1234，出现上述错误。我的印象是，如果主键已经存在，记录就会更新。
How can I upsert with Flask-SQLAlchemy based on primary key alone? Is there a simple solution?
如何仅基于主键使用 Flask-SQLAlchemy 进行更新插入？有简单的解决方案吗？
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.
如果没有，我总是可以检查并删除具有匹配 id 的任何记录，然后插入新记录，但这对于我的情况来说似乎很昂贵，我不希望有很多更新。
采纳答案by mgoldwasser
There is an upsert-esque operation in SQLAlchemy:
SQLAlchemy 中有一个 upsert-esque 操作：
db.session.merge()
db.session.merge()
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".
找到这个命令后，我就可以执行 upserts，但值得一提的是，这个操作对于批量“upsert”来说很慢。
The alternative is to get a list of the primary keys you would like to upsert, and query the database for any matching ids:
另一种方法是获取您要更新插入的主键列表，并查询数据库以查找任何匹配的 id：
# 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()
回答by danielcahall
An alternative approach using compilation extension (https://docs.sqlalchemy.org/en/13/core/compiler.html):
使用编译扩展的另一种方法（https://docs.sqlalchemy.org/en/13/core/compiler.html）：
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert
@compiles(Insert)
def compile_upsert(insert_stmt, compiler, **kwargs):
"""
converts every SQL insert to an upsert i.e;
INSERT INTO test (foo, bar) VALUES (1, 'a')
becomes:
INSERT INTO test (foo, bar) VALUES (1, 'a') ON CONFLICT(foo) DO UPDATE SET (bar = EXCLUDED.bar)
(assuming foo is a primary key)
:param insert_stmt: Original insert statement
:param compiler: SQL Compiler
:param kwargs: optional arguments
:return: upsert statement
"""
pk = insert_stmt.table.primary_key
insert = compiler.visit_insert(insert_stmt, **kwargs)
ondup = f'ON CONFLICT ({",".join(c.name for c in pk)}) DO UPDATE SET'
updates = ', '.join(f"{c.name}=EXCLUDED.{c.name}" for c in insert_stmt.table.columns)
upsert = ' '.join((insert, ondup, updates))
return upsert
This should ensure that all insert statements behave as upserts. This implementation is in Postgres dialect, but it should be fairly easy to modify for MySQL dialect.
这应该确保所有插入语句都表现为 upsert。这个实现是在 Postgres 方言中实现的，但是对于 MySQL 方言，它应该很容易修改。
