使用Jinja与SQL在HTML展示健身计划时触发IndexError问题

直接引发IndexError的问题 #

1. 参数键错误
   在Flask路由的routine_info查询中，参数错误地写成了(g.user['id',])，这里误用了带逗号的键'id,'，正确写法应为(g.user['id'],)，修正后才能正确获取当前用户ID。

2. 未获取查询结果集
   db.execute()返回的是数据库游标对象，而非实际查询结果。必须调用.fetchall()方法获取所有行数据，否则在Jinja模板中通过列名字符串访问时会触发索引错误。同时原代码中if routine_names is None:判断无效，游标对象或fetchall()返回的列表永远不会是None，空结果会返回空列表，应改为if not routine_names:。修正后的路由查询代码：

   @bp.route('/', methods=('GET', 'POST'))
   def index():
       db = get_db()
       # 获取用户的所有健身计划，调用fetchall()获取结果
       routine_names = db.execute(
           "SELECT routine_name, created FROM link_routines WHERE user_id = ?", 
           (g.user['id'],)
       ).fetchall()
       # 修正参数键，并调用fetchall()获取练习数据，明确指定user_id所属表
       routine_info = db.execute(
           "SELECT exercise_name, weight, sets, reps FROM exercises, routine_record WHERE exercises.id = routine_record.exercise_id AND routine_record.user_id = ?", 
           (g.user['id'],)
       ).fetchall()
       if not routine_names:
           return render_template("routine/index.html")
       return render_template("routine/index.html", routine_names=routine_names, routine_info=routine_info)
   

逻辑优化（解决每个计划显示所有练习的问题） #

当前模板中每个健身计划都会遍历所有练习，不符合“每个计划对应自己的练习”的需求，需要调整查询逻辑：

修正后的Flask路由代码 #

@bp.route('/', methods=('GET', 'POST'))
def index():
    db = get_db()
    # 获取包含计划ID的完整计划信息
    routines = db.execute(
        "SELECT id, routine_name, created FROM link_routines WHERE user_id = ?",
        (g.user['id'],)
    ).fetchall()
    
    # 构建计划ID到对应练习的映射
    routine_exercises = {}
    for routine in routines:
        exercises = db.execute(
            """
            SELECT exercise_name, weight, sets, reps 
            FROM exercises 
            JOIN routine_record ON exercises.id = routine_record.exercise_id 
            WHERE routine_record.routine_id = ? AND routine_record.user_id = ?
            """,
            (routine['id'], g.user['id'])
        ).fetchall()
        routine_exercises[routine['id']] = exercises
    
    return render_template("routine/index.html", routines=routines, routine_exercises=routine_exercises)

修正后的Jinja模板代码 #

{% for routine in routines %}
<article class="routines">
    <header>
        <h1>{{ routine['routine_name'] }} {{ routine['created'] }}</h1>
        {% for exercise in routine_exercises[routine['id']] %}
            <div class="preview">
                <p>{{ exercise['exercise_name'] }}, {{ exercise['weight'] }}kg, {{ exercise['sets'] }}x{{ exercise['reps'] }}</p>
            </div>
        {% endfor %}
    </header>
</article>
{% if not loop.last %}
<hr>
{% endif %}
{% endfor %}

额外注意事项 #

• SQL查询中明确指定user_id所属表为routine_record，避免多表查询时的字段歧义。
• 使用JOIN语法替代隐式连接，让SQL语句更清晰易读。

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