Flask+SQLAlchemy实现MySQL数据更新REST API报错求助

问题背景 #

使用Python、Flask、REST API更新MySQL表数据时，PATCH接口首次调用成功，后续请求报错。相关代码及场景如下：

1. Cafe模型定义（Flask-SQLAlchemy） #

class Cafe(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(250), unique=True, nullable=False)
    map_url = db.Column(db.String(500), nullable=False)
    img_url = db.Column(db.String(500), nullable=False)
    location = db.Column(db.String(250), nullable=False)
    seats = db.Column(db.String(250), nullable=False)
    has_toilet = db.Column(db.Boolean, nullable=False)
    has_wifi = db.Column(db.Boolean, nullable=False)
    has_sockets = db.Column(db.Boolean, nullable=False)
    can_take_calls = db.Column(db.Boolean, nullable=False)
    coffee_price = db.Column(db.String(250), nullable=True)

    def to_dict(self):
        dictionary = {}
        for column in self.__table__.columns:
            dictionary[column.name] = getattr(self, column.name)
        return dictionary

2. 正常工作的POST添加接口 #

@app.route("/add",methods=["POST"])
def add_cafe():
    body = request.form
    try:
        new_cafe = Cafe(
            name=request.form['name'],
            location=request.form['location'],
            seats=request.form['seats'],
            img_url=request.form['img_url'],
            map_url=request.form['map_url'],
            coffee_price=request.form['coffee_price'],
            has_wifi=bool(request.form['has_wifi']),
            has_toilet=bool(request.form['has_toilet']),
            has_sockets=bool(request.form['has_sockets']),
            can_take_calls=bool(request.form['can_take_calls']),
        )
    except KeyError:
        return jsonify(error={"Bad Request": "Some or all fields were incorrect or missing."})
    else:
        with app.app_context():
            db.session.add(new_cafe)
            db.session.commit()
        return jsonify(response={"success": f"Successfully added the new cafe."})

3. 存在问题的PATCH更新接口 #

@app.route("/update-price/<int:cafe_id>",methods=["PATCH"])
def patch_new_cafe():
    new_price = request.args.get("new_price")
    cafe = db.session.query(Cafe).get("cafe_id")
    cafe.coffee.price = new_price
    if cafe:
        with app.app_context():
            db.session.commit()
        return jsonify(response={"Success":"Successfully updated the price"}),200
    else:
        return jsonify(response={"Not Found":"Sorry a cafe with that id was not found in the database."}),404

问题分析与修复 #

原接口存在3个核心错误，导致后续请求报错：

• 查询参数错误：db.session.query(Cafe).get("cafe_id") 传入了字符串"cafe_id"，而非路由参数变量cafe_id，无法正确查询目标对象。
• 字段名错误：模型中价格字段为coffee_price，代码中写成cafe.coffee.price，触发AttributeError（Cafe对象无coffee属性）。
• 逻辑顺序错误：先修改字段再判断对象是否存在，若查询返回None会直接抛出异常，无法进入404分支。

修复后的PATCH接口代码 #

@app.route("/update-price/<int:cafe_id>", methods=["PATCH"])
def patch_new_cafe():
    new_price = request.args.get("new_price")
    # 使用路由参数查询目标咖啡馆
    cafe = db.session.query(Cafe).get(cafe_id)
    
    # 先判断对象是否存在
    if not cafe:
        return jsonify(response={"Not Found":"Sorry a cafe with that id was not found in the database."}),404
    
    # 检查new_price是否为空（可选但推荐）
    if not new_price:
        return jsonify(error={"Bad Request": "new_price parameter is missing or empty."}),400
    
    # 修改正确的字段名
    cafe.coffee_price = new_price
    # 请求上下文已包含应用上下文，无需额外嵌套
    db.session.commit()
    
    return jsonify(response={"Success":"Successfully updated the price"}),200

额外优化建议 #

• 若采用JSON格式传递参数，可改用request.get_json().get("new_price")，更符合REST API规范。
• 布尔字段转换优化：bool(request.form.get('has_wifi', False))，避免空值转换报错。

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