Python+Sqlite插入行时批量更新整列场景编号问题

问题背景 #

在Flask页面中实现场景添加功能时，需要完成编号重排逻辑：当用户输入某个场景编号（例如"2"），若该编号已存在，需将现有编号为2的场景改为3、3改为4……以此类推，再插入新的场景行。目前初始插入逻辑正常，但重排后续行的逻辑存在缺陷，现有代码如下：

connection = sqlite3.connect('database')
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute("SELECT SCENE FROM main")
scenelist = cursor.fetchall()
for oldscene in scenelist:
   if oldscene['SCENE'] == int(scene):
      newscene = (oldscene['SCENE'] + 1)
      cursor.execute("UPDATE MAIN SET SCENE = ? WHERE SCENE = ?", (newscene, scene))
cursor.execute("INSERT into MAIN (TITLE, LOCATION, CHAPTER, SCENE, TAGS) \
     VALUES (?, ?, ?, ?, ?)", (title, location, chapter, scene, tags,))

核心问题 #

现有代码仅处理了与输入编号完全匹配的行，未对大于输入编号的所有场景执行递增加1操作，无法实现完整的编号重排。

修复方案 #

优化后的代码 #

connection = sqlite3.connect('database')
connection.row_factory = sqlite3.Row
cursor = connection.cursor()

try:
    # 开启事务，保证操作原子性
    connection.execute("BEGIN TRANSACTION")
    
    target_scene = int(scene)
    # 批量更新所有>=目标编号的场景，编号+1
    cursor.execute("UPDATE MAIN SET SCENE = SCENE + 1 WHERE SCENE >= ?", (target_scene,))
    
    # 插入新场景行
    cursor.execute("INSERT into MAIN (TITLE, LOCATION, CHAPTER, SCENE, TAGS) \
         VALUES (?, ?, ?, ?, ?)", (title, location, chapter, target_scene, tags,))
    
    # 提交事务
    connection.commit()
except Exception as e:
    # 出错回滚，避免数据混乱
    connection.rollback()
    raise e
finally:
    # 关闭数据库连接
    connection.close()

关键改进点 #

• 批量SQL更新：无需循环遍历所有场景，通过单条SQL语句直接处理所有符合条件的行，逻辑简洁且执行高效
• 事务保障：将更新与插入操作纳入事务，确保要么全部成功，要么全部回滚，避免出现编号混乱的中间状态
• 完整覆盖重排逻辑：WHERE SCENE >= ?条件会自动处理目标编号及所有后续编号的场景，完美实现"后续行依次递增加1"的需求

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