CS50期末项目：Flask向SQL插入咖啡评分时遇Unsupported Value错误

问题描述 #

正在开发CS50期末项目（精品咖啡信息平台），已实现咖啡信息添加功能，但在实现用户评分功能时遇到错误：执行INSERT操作到coffee_ratings表时触发unsupported value {id:1}错误，其中1是从coffees表查询到的有效coffee_id，但SELECT查询能正常返回结果。

相关代码与结构 #

Flask后端代码（原错误版本） #

@app.route("/score", methods=["GET", "POST"])
def score():
        if request.method =="POST":

            name = request.form.get("name")
            rating = request.form.get("rating")
            #aroma = request.form.get("aroma")
           # body = request.form.get("body")

            coffee_id = db.execute("SELECT id FROM coffees WHERE name LIKE ?", name)
              # Check if the coffee exists in the database
            if coffee_id is not None:
                coffee_id = coffee_id[0]  # Extracting the ID from the dictionary
            #aroma_id = db.execute("SELECT id FROM aromas WHERE aroma_name = ?", aroma)
           # body_id = db.execute("SELECT id FROM bodies WHERE body_name = ?", body)
           # db.execute("INSERT INTO coffee_ratings (coffee_id, aroma_id, body_id, rating) VALUES (?, ?, ?, ?)", coffee_id, aroma_id, body_id, rating)
                db.execute("INSERT INTO coffee_ratings (coffee_id, rating) VALUES (?, ?)", coffee_id, rating)
                return render_template("score.html")
            else:
                return f"Coffee '{name}' not found in the database."

        else:
            row = db.execute("SELECT * FROM coffee_ratings;")
            return render_template("score.html", score = row)

前端表单代码 #

<form action="/score" method="post">
    <input id="name" autocomplete="off" autofocus name="name" placeholder="Name" type="text">
    <input id="rating" autocomplete="off" autofocus name="rating" placeholder="rating" type="number" min="1" max="10">
    <button type="submit">score</button>
</form>

数据库表结构 #

CREATE TABLE coffee_ratings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
coffee_id INTEGER,
aroma_id INTEGER,
taste_id INTEGER,
body_id INTEGER,
bitterness_id INTEGER,
sourness_id INTEGER,
rating INTEGER NOT NULL,
votes INTEGER NOT NULL,
FOREIGN KEY (coffee_id) REFERENCES coffees(id),
FOREIGN KEY (aroma_id) REFERENCES aromas(id),
FOREIGN KEY (taste_id) REFERENCES tastes(id),
FOREIGN KEY (body_id) REFERENCES bodies(id),
FOREIGN KEY (bitterness_id) REFERENCES bitterness(id)
);

CREATE TABLE coffees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
roaster TEXT,
country TEXT,
variety TEXT,
farm TEXT
);

错误原因分析 #

1. coffee_id提取错误：db.execute返回的是字典列表（如[{"id": 1}]），原代码中coffee_id = coffee_id[0]得到的是完整字典{"id":1}，而非整数1，直接插入数据库会触发类型不支持错误。
2. 空结果判断错误：当查询不到咖啡时，db.execute返回空列表[]，而非None，原代码if coffee_id is not None会误判空列表为有效结果。
3. rating类型不匹配：request.form.get返回字符串类型，但数据库rating字段是INTEGER类型，直接插入会导致类型错误。
4. votes字段未赋值：coffee_ratings表中votes字段为NOT NULL，原INSERT语句未提供该值，会触发约束错误。

修复后的代码 #

@app.route("/score", methods=["GET", "POST"])
def score():
    if request.method == "POST":
        name = request.form.get("name")
        rating_str = request.form.get("rating")
        
        # 校验rating是否为有效数字
        if not rating_str or not rating_str.isdigit():
            return "请输入1-10之间的有效评分"
        rating = int(rating_str)
        if not 1 <= rating <=10:
            return "评分必须在1到10之间"

        # 使用LIKE模糊查询时添加通配符，避免精确匹配问题
        coffee_rows = db.execute("SELECT id FROM coffees WHERE name LIKE ?", f"%{name}%")
        
        # 判断是否查询到咖啡
        if coffee_rows:
            coffee_id = coffee_rows[0]["id"]  # 正确提取整数id
            # 插入时必须给votes字段赋值（NOT NULL约束）
            db.execute("INSERT INTO coffee_ratings (coffee_id, rating, votes) VALUES (?, ?, ?)", 
                      coffee_id, rating, 1)
            return render_template("score.html")
        else:
            return f"Coffee '{name}' not found in the database."

    else:
        rows = db.execute("SELECT * FROM coffee_ratings;")
        return render_template("score.html", score=rows)

额外优化建议 #

• 模糊查询优化：原查询使用LIKE ?但未加通配符，会变成精确匹配，改为LIKE ?并传入f"%{name}%"可实现模糊搜索，提升用户体验。
• 后端校验增强：前端表单的min/max限制可被绕过，后端必须校验评分的合法性，防止非法数据存入。
• 事务处理：如果后续扩展评分功能涉及多表操作，建议使用事务保证数据一致性。

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