Flask联动下拉框Bug求助：二级下拉混入一级选项问题排查

问题根源 #

当前AJAX请求触发后，后端返回的是完整的HTML页面，前端直接将整个页面的HTML赋值给第二个下拉框的innerHTML，导致下拉框中混入了页面的其他内容（包括一级下拉框的选项）。

修复方案 #

1. 后端新增专用API接口返回子主题数据 #

修改Flask代码，新增一个路由专门处理子主题的AJAX请求，返回纯JSON格式的子主题列表，而非完整页面：

from flask import jsonify, request
from flask import Flask, render_template  
import mysql.connector

app = Flask(__name__)

# Configure MySQL connection
cnx = mysql.connector.connect(
  host="xxxxxxx",
  user="xxxxxxxxx",
  password="xxxxxxxxx",
  database="xxxxxxxxxx")

# 新增：专门返回子主题的API
@app.route('/get_sub_topics', methods=['POST'])
def get_sub_topics():
    try:
        selected_topic = request.form.get('selected_topic')
        if not selected_topic:
            return jsonify([])
        
        cursor = cnx.cursor()
        query = "SELECT sub_topic FROM text_table WHERE topic = %s"
        cursor.execute(query, (selected_topic,))
        sub_topics = [row[0] for row in cursor.fetchall()]
        cursor.close()
        return jsonify(sub_topics)
    except Exception as e:
        return jsonify(error=str(e)), 500

@app.route('/', methods=['GET', 'POST'])
def index():
    try:
        cursor = cnx.cursor()
        query = "SELECT topic FROM text_table"
        cursor.execute(query)
        data = [row[0] for row in cursor.fetchall()]
        cursor.close()

        selected_topic = None
        sub_topics = []
        selected_sub_topic = None
        result = None

        if request.method == 'POST':
            selected_topic = request.form.get('selected_topic')
            selected_sub_topic = request.form.get('selected_sub_topic')
            
            if selected_topic:
                cursor = cnx.cursor()
                query = "SELECT sub_topic FROM text_table WHERE topic = %s"
                cursor.execute(query, (selected_topic,))
                sub_topics = [row[0] for row in cursor.fetchall()]
                cursor.close()

                if selected_sub_topic:
                    cursor = cnx.cursor()
                    query = "SELECT text FROM text_table WHERE topic = %s AND sub_topic = %s"
                    cursor.execute(query, (selected_topic, selected_sub_topic))
                    result = cursor.fetchone()[0]
                    cursor.close()

        return render_template('index.html', 
                               topics=data, 
                               selected_topic=selected_topic,
                               sub_topics=sub_topics,
                               selected_sub_topic=selected_sub_topic,
                               result=result)

    except Exception as e:
        return jsonify(error=str(e)), 500

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

2. 前端修改AJAX逻辑，动态生成子主题选项 #

修改HTML中的JavaScript代码，请求新的API接口，接收JSON数据后手动构建下拉框选项，而非直接替换整个页面内容：

<!DOCTYPE html>
<html>
  <head>
    <title>Drop Down Filter</title>
    <script>
        function updateSubTopics() {
            var selectTopic = document.getElementById("selected_topic");
            var selectSubTopic = document.getElementById("selected_sub_topic");
            var selectedTopicValue = selectTopic.value;

            var xhr = new XMLHttpRequest();
            xhr.open('POST', '/get_sub_topics');
            xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
            xhr.onload = function() {
                if (xhr.status === 200) {
                    // 清空原有选项
                    selectSubTopic.innerHTML = '';
                    // 解析JSON数据
                    var subTopics = JSON.parse(xhr.responseText);
                    // 动态生成新选项
                    subTopics.forEach(function(subTopic) {
                        var option = document.createElement('option');
                        option.value = subTopic;
                        option.textContent = subTopic;
                        selectSubTopic.appendChild(option);
                    });
                    // 重置选中状态
                    selectSubTopic.value = "";
                }
                else {
                    console.log('Request failed. Returned status of ' + xhr.status);
                }
            };
            xhr.send('selected_topic=' + selectedTopicValue);
        }
    </script>
  </head>
  <body>
    <form method="POST">
      <select name="selected_topic" id="selected_topic" onchange="updateSubTopics()">
        {% for topic in topics %}
          <option value="{{ topic }}"
            {% if selected_topic == topic %}selected{% endif %}>
            {{ topic }}
          </option>
        {% endfor %}
      </select>
      <select name="selected_sub_topic" id="selected_sub_topic">
        {% for sub_topic in sub_topics %}
          <option value="{{ sub_topic }}"
            {% if selected_sub_topic == sub_topic %}selected{% endif %}>
            {{ sub_topic }}
          </option>
        {% endfor %}
      </select>
      <input type="submit" value="Filter">
    </form>
    {% if result %}
      <h1>{{ result }}</h1>
    {% endif %}
  </body>
</html>

关键修改点 #

• 后端拆分了页面渲染和API数据请求的逻辑，避免AJAX请求返回完整页面
• 前端改为接收JSON数据，动态构建下拉框选项，确保下拉框仅显示对应子主题
• 移除了不必要的cursor.nextset()调用（查询单表结果时不需要）

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