尝试使用 Flask API 和 MySQL 连接器从 MySQL 数据库检索数据时遇到问题

问：

当我尝试通过 API 端点 （） 访问基于特定类别的数据时，会发生这些错误。该问题似乎与SQL查询或数据库结构有关。/api/get_options_for_category

以下是我的设置摘要：

• 我正在使用带有 Flask 的 Python 来创建 API。
• 该 API 使用该库与 MySQL 数据库进行交互。mysql-connector
• 我正在尝试根据所选类别检索与“部门”、“医学”、“症状”列相关的数据。

下面是一个后端代码供参考：

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

app = Flask(__name__)

# Create database connection object
con = mysql.connector.connect(
    host="localhost", user="root", password="", database="data_visualization"
)

# Route to get options for a category
@app.route('/api/get_options_for_category', methods=['GET'])
def get_options_for_category():
    selected_category = request.args.get('category')

    options = fetch_data(selected_category)
    print(options)  # Print the retrieved options to the console

    return jsonify(options)

# Function to fetch data from the database based on the selected category
def fetch_data(selected_category):
    if selected_category == '1':  # Departments
        query = "SELECT DISTINCT Department FROM opd_data LIMIT 10000"
    elif selected_category == '2':  # Medicines
        query = "SELECT DISTINCT Medicine FROM opd_data LIMIT 10000"
    elif selected_category == '3':  # Symptoms
        query = "SELECT DISTINCT Symptom FROM opd_data LIMIT 10000"
    else:
        return []  # Return an empty list if the category is not recognized

    cursor = con.cursor()
    try:
        cursor.execute(query)
        options = [row[0] for row in cursor.fetchall()]
        cursor.close()
        return options
    except Exception as e:
        error_message = f"Error fetching data: {str(e)}"
        print(error_message)  # Print the error message to the console
        return []

如果名称 == 'main'： app.run（debug=True）

下面是一个前端代码供参考：

                            <!-- First dropdown -->
                        <select class="form-select" id="firstDropdown">
                          <option value="0">Select an option</option>
                          <option value="1">Department</option>
                          <option value="2">Medicine</option>
                          <option value="3">Symptom</option>
                        </select>

                        <!-- Second dynamic dropdown (will be populated based on the first selection) -->
                        <select class="form-select" id="secondDropdown" style="display: none;">
                          <option value="0">Select an item</option>
                          <!-- Options for the first selection will be populated here using JavaScript -->
                        </select>

    <script>
    function updateSecondDropdown(selectedCategory) {
        if (selectedCategory === "0") {
            document.getElementById("secondDropdown").style.display = "none";
        } else {
            fetch(`/api/get_options_for_category?category=${selectedCategory}`)
                .then(function (response) {
                    return response.json();
                })
                .then(function (data) {
                    var secondDropdown = document.getElementById("secondDropdown");
                    secondDropdown.innerHTML = '<option value="0">Select an item</option>';

                    data.forEach(function (option) {
                        var optionElement = document.createElement("option");
                        optionElement.value = option;
                        optionElement.innerText = option;
                        secondDropdown.appendChild(optionElement);
                    });

                    document.getElementById("secondDropdown").style.display = "block";
                })
                .catch(function (error) {
                    console.error('Error fetching data:', error);
                });
        }
    }

    // Event listener for first dropdown change
    document.getElementById("firstDropdown").addEventListener("change", function () {
        var selectedCategory = this.value;
        updateSecondDropdown(selectedCategory);
    });
</script>

我怀疑该错误可能与数据库架构或我正在执行的查询有关。我想在不遇到这些“未知列”错误的情况下检索所选类别的数据。

有人可以指导我如何纠正这个问题吗？我正在寻求有关对特定错误进行故障排除以及如何更正我的 SQL 查询或数据库架构以解决此问题的建议。任何见解或建议将不胜感激。谢谢。