如何用Python2.7 psycopg2更新PostgreSQL中SELECT查询获取的行？

首先得指出你原来的UPDATE语句存在的问题：那个WHERE子句里的子查询会返回所有license_type_code='20'的记录，执行后会把这类记录的score_percentile全设成同一个值，这显然和你想要的“每行对应自己的百分位”不符。

作为SQL新手，我给你两种解决方案，优先推荐纯SQL的高效写法，再给你修正后的Python循环写法。

方案一：纯SQL批量更新（推荐） #

现在主流数据库（MySQL 8+、PostgreSQL、SQL Server等）都支持窗口函数，用它可以一次性计算所有行的百分位并完成更新，避免来回数据库的开销，性能提升非常明显。

假设你的表有唯一主键id（用来精准定位每一行，没有的话得找其他唯一标识列），可以这么写：

-- 用CTE先计算每个符合条件的行的百分位
WITH ranked_restaurants AS (
    SELECT 
        id,
        -- 完全按照你给出的公式计算：100*(排名-0.5)/总数量
        100 * (ROW_NUMBER() OVER (ORDER BY general_score DESC) - 0.5) / COUNT(*) OVER () AS calculated_percentile
    FROM restaurants
    WHERE license_type_code = '20'
)
-- 关联原表更新对应行的百分位列
UPDATE restaurants r
SET score_percentile = rr.calculated_percentile
FROM ranked_restaurants rr
WHERE r.id = rr.id;

如果是旧版MySQL（不支持CTE或者UPDATE...FROM语法），可以改用JOIN的写法：

UPDATE restaurants r
JOIN (
    SELECT 
        id,
        100 * (ROW_NUMBER() OVER (ORDER BY general_score DESC) - 0.5) / (SELECT COUNT(*) FROM restaurants WHERE license_type_code='20') AS calculated_percentile
    FROM restaurants
    WHERE license_type_code='20'
) rr ON r.id = rr.id
SET r.score_percentile = rr.calculated_percentile;

关键说明： #

• ROW_NUMBER() OVER (ORDER BY general_score DESC)：给符合条件的行按general_score降序排名，得到每行的位置
• COUNT(*) OVER ()：获取符合条件的总行数（也就是你的group_size）
• 用主键关联确保每行只更新自己的百分位，不会出错

方案二：修正后的Python循环写法 #

如果你还是想用Python来处理，得修正原来的UPDATE语句，确保每次只更新当前循环的那一行：

# 先查询出需要处理的记录的主键
cursor.execute("SELECT id FROM restaurants WHERE license_type_code='20' ORDER BY general_score DESC;")
group_size = cursor.rowcount

# 用enumerate获取从1开始的排名，比cursor.rownumber更可靠
for index, record in enumerate(cursor, start=1):
    restaurant_id = record[0]
    percentile = 100 * (index - 0.5) / group_size
    # 用参数化查询（%s），避免SQL注入，同时精准定位当前行
    cursor.execute("UPDATE restaurants SET score_percentile=%s WHERE id=%s", (percentile, restaurant_id))

# 一定要提交事务，不然更新不会生效！
connection.commit()

关键修正点： #

• 用enumerate获取排名，避免依赖cursor.rownumber（不同数据库驱动的行为可能不一致）
• 用主键id作为WHERE条件，确保只更新当前循环的行
• 使用参数化查询（不要直接拼接字符串），既安全又能避免格式错误

新手建议 #

• 优先用纯SQL方案：数据量大的时候，循环更新会产生大量数据库请求，性能远不如批量SQL操作
• 操作前先测试：可以先把计算逻辑写成SELECT语句，确认百分位计算正确后再执行UPDATE
• 备份数据：执行UPDATE前最好备份相关数据，避免误操作导致数据丢失
• 避免SQL注入：永远用参数化查询代替字符串拼接，这是SQL安全的基本要求

内容的提问来源于stack exchange，提问作者D. Miranda