使用Flask操作SQLite更新表时出现row value misused错误如何解决

报错根因 #

• 报错1sqlite3.OperationalError: row value misused：cursor.execute的第二个参数要求传入列表/元组类型，你直接传入字符串类型的latestNumber会被SQLite拆分为单个字符序列，和动态生成的占位符数量不匹配。同时对单值字符串调用len(latestNumber)生成占位符的逻辑本身不符合业务需求。
• 报错2sqlite3.OperationalError: table Stack has 1 columns but 3 values were supplied：你注释的建表SQL中StackLogs text后漏写逗号，实际执行时只会创建1个StackLogs字段，和INSERT时传入的多值数量不匹配。同时你动态生成占位符的逻辑完全不符合需求：Stack表固定2个字段，不需要根据栈长度动态生成占位符。

修复说明 #

1. 先删除本地已生成的错误stack.db文件，让代码重新创建结构正确的表
2. 建表逻辑补充缺失的逗号，新增表存在判断避免重复创建报错
3. 栈列表存入数据库前统一转为逗号拼接的字符串，不需要动态生成占位符，INSERT时固定传2个参数匹配表的2个字段
4. 修正cursor.execute传参格式，所有参数用元组包裹
5. 补充pop操作后latestNumber的更新逻辑，修复业务bug

修正后代码 #

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)

stackList = []
latestNumber = 0    #For pop/peek function

# 初始化数据库
def init_db():
    conn = sqlite3.connect('stack.db')
    cursor = conn.cursor()
    # 建表补充逗号，新增IF NOT EXISTS判断
    cursor.execute("""CREATE TABLE IF NOT EXISTS Stack (
                     StackLogs text,
                     Stacklist text
                  )""")
    conn.commit()
    conn.close()

init_db()

@app.route('/')
@app.route('/calc/')
@app.route('/calc/peek')
def calcPeek():
    return render_template('peek.html', latestNumber=latestNumber)

@app.route('/calc/push/<number>')
def pushNumber(number):
    stackList.append(number)
    global latestNumber
    latestNumber = number
    # 把栈列表转为逗号拼接字符串
    stack_str = ','.join(stackList)
    # 操作日志内容
    log_str = f"push {number}"

    conn = sqlite3.connect('stack.db')
    cursor = conn.cursor()
    # 固定2个占位符对应2个字段，参数传元组
    cursor.execute('INSERT INTO Stack VALUES (?, ?)', (log_str, stack_str))
    conn.commit()
    conn.close()

    return render_template('push.html', number=number, latestNumber=latestNumber)

@app.route('/calc/pop')
def calcPop():
    global latestNumber
    log_str = ""
    stack_str = ""
    if not stackList:
        print("Stack is empty")
        log_str = "pop failed: stack empty"
        stack_str = ''
    else: 
        popped_num = stackList.pop()
        log_str = f"pop {popped_num}"
        # 更新栈顶值
        latestNumber = stackList[-1] if stackList else 0
        stack_str = ','.join(stackList)

    conn = sqlite3.connect('stack.db')
    cursor = conn.cursor()
    cursor.execute('INSERT INTO Stack VALUES (?, ?)', (log_str, stack_str))
    conn.commit()
    conn.close()

    return render_template('pop.html', stackList=stackList, latestNumber=latestNumber)

if __name__ == '__main__':
    app.run(debug=True)

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