Python操作PostgreSQL：数据存在更新、不存在插入的正确实现

原代码存在的问题 #

• UPDATE语句逻辑错误：原代码中的UPDATE没有WHERE条件，执行后会更新表中所有行，而非目标行
• 并发风险：先查询再插入/更新的方式，在多线程/多进程场景下可能出现竞态条件，导致重复插入数据
• 未处理字典列表：函数设计为接收字典列表参数，但当前代码仅处理单个字典数据

正确实现方案 #

PostgreSQL原生支持INSERT ... ON CONFLICT ... DO UPDATE语法（即UPSERT），这是实现“存在更新、不存在插入”的最优方式，既高效又能避免并发问题。

前提条件

确保customer_new表的login_id字段有唯一约束（如果没有，先执行以下SQL创建）：

CREATE UNIQUE INDEX idx_customer_new_login_id ON customer_new(login_id);

完整Python函数代码

import psycopg2
from psycopg2 import sql

def upsert_customers(data_list):
    # 建立数据库连接
    conn = psycopg2.connect(
        database="db_database",
        user='user',
        password='password',
        host='127.0.0.1',
    )
    
    try:
        cursor = conn.cursor()
        
        # 定义UPSERT语句：当login_id冲突时，更新对应字段
        upsert_sql = sql.SQL("""
            INSERT INTO customer_new (
                login, telegram_id, orders, sum_orders, average_check, 
                balance, group_customer, zamena, reg_date, bot_or_site, login_id
            ) VALUES (
                %(login)s, %(telegram_id)s, %(orders)s, %(sum_orders)s, %(average_check)s, 
                %(balance)s, %(group)s, %(zamena)s, %(reg_date)s, %(bot_or_site)s, %(login id)s
            )
            ON CONFLICT (login_id) DO UPDATE SET
                login = EXCLUDED.login,
                telegram_id = EXCLUDED.telegram_id,
                orders = EXCLUDED.orders,
                sum_orders = EXCLUDED.sum_orders,
                average_check = EXCLUDED.average_check,
                balance = EXCLUDED.balance,
                group_customer = EXCLUDED.group,
                zamena = EXCLUDED.zamena,
                reg_date = EXCLUDED.reg_date,
                bot_or_site = EXCLUDED.bot_or_site
        """)
        
        # 批量处理传入的字典列表
        cursor.executemany(upsert_sql, data_list)
        conn.commit()
        
    except Exception as e:
        # 出错时回滚事务
        conn.rollback()
        raise e
    finally:
        # 确保关闭游标和连接，避免资源泄漏
        cursor.close()
        conn.close()

关键说明 #

• ON CONFLICT子句：指定用login_id作为冲突检测字段，当插入的login_id已存在时，执行后续的UPDATE操作
• EXCLUDED关键字：指代原本要插入的那条数据，用它来获取新值，更新现有行的对应字段
• executemany批量操作：一次性处理整个字典列表，比循环单条执行效率更高
• 事务控制：添加try-except-finally块，确保出错时回滚事务，避免数据不一致；同时保证连接资源被正确释放

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