如何使用Peewee实现基于另一张表数据的PostgreSQL UPSERT操作

首先假设你已经完成了两张表的Peewee模型定义，参考模型示例如下：

from peewee import *
from playhouse.postgres_ext import PostgresqlExtDatabase, EXCLUDED

# 初始化数据库连接
db = PostgresqlExtDatabase(
    '你的数据库名', 
    user='数据库账号', 
    password='数据库密码', 
    host='127.0.0.1', 
    port=5432
)

class Table1(Model):
    pk_t1 = IntegerField(primary_key=True)
    name = CharField()
    city = CharField()
    country = CharField()
    
    class Meta:
        database = db
        table_name = 'table1'

class Table2(Model):
    pk_t2 = IntegerField(primary_key=True)
    name = CharField()
    city = CharField()
    country = CharField()
    comments = TextField(null=True)
    
    class Meta:
        database = db
        table_name = 'table2'

核心UPSERT功能实现代码如下：

# 构造从table1取数的查询语句
select_query = Table1.select(
    Table1.pk_t1,
    Table1.name,
    Table1.city,
    Table1.country
)

# 构造带冲突处理的插入语句
insert_query = Table2.insert(
    # 指定插入字段顺序，需和select返回的字段顺序一一对应
    fields=[Table2.pk_t2, Table2.name, Table2.city, Table2.country],
    from_select=([Table2.pk_t2, Table2.name, Table2.city, Table2.country], select_query)
).on_conflict(
    conflict_target=[Table2.pk_t2], # 指定冲突判断的主键字段
    action='UPDATE',
    # 冲突时的更新规则，EXCLUDED对应原生SQL中的excluded虚拟表，指代本次尝试插入的值
    update={
        'name': EXCLUDED.name,
        'city': EXCLUDED.city,
        'country': EXCLUDED.country
    }
)

# 执行语句
insert_query.execute()

上述代码生成的原生SQL和你给出的目标逻辑完全一致，无额外性能损耗。

内容的提问来源于stack exchange，提问作者hashg