Flask+MySQL出现ProgrammingError：参数未全部转换，求解决方案

Hey there, let's fix that ProgrammingError you're running into!

问题根源 #

The error pops up because you're using ? as parameter placeholders in your SQL query, but MySQLdb (the library Flask-MySQL relies on under the hood) uses %s for parameter substitution—? is the placeholder style for databases like SQLite or PostgreSQL's psycopg2, not MySQL. That mismatch is why your arguments aren't being converted correctly.

修改后的代码 #

Here's the corrected version of your upd function, with some extra robustness added:

@app.route('/upd',methods=['POST'])
def upd():
    # 先检查必填参数是否存在，避免KeyError
    if 'lat' not in request.form or 'lng' not in request.form:
        return jsonify({'error': 'Missing required parameters: lat and lng are mandatory'}), 400
    
    _lat = request.form['lat']
    _lng = request.form['lng']
    
    # 可选：转换参数类型匹配数据库字段（比如如果数据库是浮点型）
    try:
        _lat = float(_lat)
        _lng = float(_lng)
    except ValueError:
        return jsonify({'error': 'lat and lng must be numeric values'}), 400
    
    cur = mysql.connection.cursor()
    # 把?替换成MySQL支持的%s占位符
    cur.execute('insert into locations(lat,lng) values (%s,%s)', (_lat,_lng))
    cur.commit()
    # 关闭游标避免资源泄漏
    cur.close()
    return jsonify(request.form)

额外小提示 #

• 记得用完游标后关闭它，防止数据库资源浪费。
• 增加参数校验能让你的API更健壮，遇到非法输入时能友好返回错误，而不是直接崩溃。
• 确认你的locations表中lat和lng字段的数据类型（比如FLOAT或DECIMAL）和你插入的数值类型匹配。

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