如何为PostgreSQL数据库每行随机分配Team列值？

我有一个PostgreSQL数据库，已为players表新增team列，当前表数据如下：

date   |   forename   | surname   | team
-----------------------------------------
1/1/01 |   james      |  smith    | 
2/2/02 |   paul       |  jones    |
3/3/03 |   steven     |  bradley  |

我尝试用Python的random模块从球队列表为每行的team列随机分配值，编写的代码如下：

import psycopg2
import random

dbconfig = {
    "dbname": "mydb",
    "host": "127.0.0.1",
    "user": "user",
    "password": "password",
    "port": 5432,
}

connection = psycopg2.connect(**dbconfig)
connection.autocommit = True

teams = ["liverpool", "chelsea", "arsenal"]
cursor = connection.cursor()

cursor.execute("select * from players")
players = cursor.fetchall()

for player in players:
    team = random.choice(teams)
    query = f""" update trades set team = '{team}' """

但执行后所有行的team列被更新为同一值，结果如下：

date   |   forename   | surname   | team
-----------------------------------------
1/1/01 |   james      |  smith    | chelsea
2/2/02 |   paul       |  jones    | chelsea
3/3/03 |   steven     |  bradley  | chelsea

请问如何实现逐行或批量为每行分配随机的team值？

问题原因 #

原代码存在两个核心问题：

1. UPDATE语句没有WHERE条件，每次循环都会更新整个表的所有行，最后一次循环生成的球队值会覆盖所有记录
2. 表名写错，应该是players而非trades

解决方案 #

方法一：逐行更新（修正原逻辑） #

通过WHERE条件定位到单个玩家，确保每次只更新当前行。建议用主键（如id）作为定位条件，若没有主键可使用唯一组合字段（如date+forename+surname）。

带主键的示例代码

import psycopg2
import random

dbconfig = {
    "dbname": "mydb",
    "host": "127.0.0.1",
    "user": "user",
    "password": "password",
    "port": 5432,
}

connection = psycopg2.connect(**dbconfig)
connection.autocommit = True

teams = ["liverpool", "chelsea", "arsenal"]
cursor = connection.cursor()

# 查询时获取玩家主键id
cursor.execute("select id, date, forename, surname, team from players")
players = cursor.fetchall()

for player in players:
    player_id = player[0]
    team = random.choice(teams)
    # 使用参数化查询避免SQL注入，同时精准定位单条记录
    cursor.execute("UPDATE players SET team = %s WHERE id = %s", (team, player_id))

cursor.close()
connection.close()

无主键的示例代码

import psycopg2
import random

dbconfig = {
    "dbname": "mydb",
    "host": "127.0.0.1",
    "user": "user",
    "password": "password",
    "port": 5432,
}

connection = psycopg2.connect(**dbconfig)
connection.autocommit = True

teams = ["liverpool", "chelsea", "arsenal"]
cursor = connection.cursor()

cursor.execute("select date, forename, surname, team from players")
players = cursor.fetchall()

for player in players:
    player_date, forename, surname = player[0], player[1], player[2]
    team = random.choice(teams)
    cursor.execute(
        "UPDATE players SET team = %s WHERE date = %s AND forename = %s AND surname = %s",
        (team, player_date, forename, surname)
    )

cursor.close()
connection.close()

方法二：批量更新（高效方案） #

如果数据量较大，逐行更新会产生过多数据库交互。可以直接利用PostgreSQL内置的随机函数，在数据库层面完成批量随机分配，效率更高。

import psycopg2

dbconfig = {
    "dbname": "mydb",
    "host": "127.0.0.1",
    "user": "user",
    "password": "password",
    "port": 5432,
}

connection = psycopg2.connect(**dbconfig)
connection.autocommit = True
cursor = connection.cursor()

# 用PostgreSQL数组+随机函数，一次性为所有行分配随机球队
cursor.execute("""
    UPDATE players 
    SET team = (ARRAY['liverpool', 'chelsea', 'arsenal'])[floor(random() * 3) + 1]
""")

cursor.close()
connection.close()

逻辑说明：

• random()生成0_{1之间的随机数，乘以3后得到0}3的数值
• floor()取整得到0、1、2，加1后对应数组的索引1、2、3，从而随机选择数组中的球队

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