MySQL中cursor.lastrowid返回0引发查询报错问题求助

我是MySQL新手，尝试获取cursor.lastrowid时始终返回0。执行INSERT语句后，后续用该值执行UPDATE及循环查询操作，因行ID无效出现TypeError: 'NoneType' object is not iterable错误，已尝试截断数据库但问题依旧，请问哪里出错了？

相关代码 #

cursor.execute(
    "INSERT INTO ksgame (white, status, time, rated, white_rating) VALUES (%s,%s,%s,%s,%s)",
    (session["username"], "searching", time, rated, rating),
)
# Adding the link to the db
cursor.execute(
    "UPDATE ksgame SET site = %s WHERE game_id = %s",
    (
        "http://127.0.0.1:5000/play/" + str(cursor.lastrowid),
        cursor.lastrowid,
    ),
)
# Commiting changes
db.commit()
# Setting a boolean to keep track of black player's presence
black_player = False

# As long as black player is not here
times = 0

while not black_player:
    times += 1
    # Keep looking if any joined
    # -------------Here it returns 0 ---------------------------------------------
    print(cursor.lastrowid)
    cursor.execute(
        "SELECT black FROM ksgame WHERE game_id = %s", (cursor.lastrowid,)
    )
    black = cursor.fetchall()
    black = [dict(row) for row in black]
    if times == 1000:
        print(cursor.fetchall())
        times = 0
    cursor.reset()
    # If a black player has joined
    if black["black"] != None:
        # Set black_player boolean to true to stop the wait
        black_player = True

报错信息 #

Traceback (most recent call last):
  File "/home/KnightStable/.virtualenvs/myvirtualenv/lib/python3.10/site-packages/flask/app.py", line 2525, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/KnightStable/.virtualenvs/myvirtualenv/lib/python3.10/site-packages/flask/app.py", line 1822, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/KnightStable/.virtualenvs/myvirtualenv/lib/python3.10/site-packages/flask/app.py", line 1820, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/KnightStable/.virtualenvs/myvirtualenv/lib/python3.10/site-packages/flask/app.py", line 1796, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/KnightStable/mysite/knightstable/game/routes.py", line 280, in play
    black = [dict(row) for row in black]
TypeError: 'NoneType' object is not iterable

问题原因及解决办法 #

1. cursor.lastrowid返回0的核心原因 #

cursor.lastrowid仅保留最后一次执行SQL语句生成的自增ID。你在INSERT后执行了UPDATE，此时cursor.lastrowid会被更新为UPDATE的执行结果（UPDATE不生成自增ID，因此返回0）。

解决： 在INSERT后立刻将自增ID保存到变量，后续操作统一使用该变量：

# 执行INSERT后立即保存ID
cursor.execute(
    "INSERT INTO ksgame (white, status, time, rated, white_rating) VALUES (%s,%s,%s,%s,%s)",
    (session["username"], "searching", time, rated, rating),
)
game_id = cursor.lastrowid  # 保存到变量

# 后续操作都用这个变量
cursor.execute(
    "UPDATE ksgame SET site = %s WHERE game_id = %s",
    (
        f"http://127.0.0.1:5000/play/{game_id}",
        game_id,
    ),
)

2. TypeError的直接原因 #

当game_id为0时，查询语句找不到对应行，cursor.fetchall()可能返回None或空列表，导致遍历转换字典时报错；另外，转换后的字典列表不能直接用black["black"]索引，需取列表第一个元素。

解决： 增加空值判断，修正索引方式：

while not black_player:
    times += 1
    print(game_id)  # 用保存的变量，不是cursor.lastrowid
    cursor.execute(
        "SELECT black FROM ksgame WHERE game_id = %s", (game_id,)
    )
    black_rows = cursor.fetchall()
    # 先判断是否有查询结果
    if not black_rows:
        cursor.reset()
        continue
    # 转换为字典后取第一个元素
    black = [dict(row) for row in black_rows][0]
    if times == 1000:
        print(cursor.fetchall())
        times = 0
    cursor.reset()
    # 判断black字段是否不为空
    if black.get("black") is not None:
        black_player = True

3. 额外检查点 #

• 确认ksgame表的game_id字段是自增主键（设置AUTO_INCREMENT属性），这是cursor.lastrowid能获取有效ID的前提，非自增主键会导致lastrowid返回0。
• 若使用MySQLdb/PyMySQL，通常无需手动调用cursor.reset()，可根据实际情况移除该语句。

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