Python/MySQL新手遇参数错误：无法提取指定ID食物记录求助

错误原因 #

• 执行第二个查询时，错误传入了Python内置函数id，而非用户输入的choose变量，导致MySQL无法处理函数对象作为参数。
• 即便传入正确变量，(choose)的写法不属于元组（括号仅作为运算符），MySQL要求参数必须是列表、元组或字典，单元素元组需写成(choose,)。

修正后的代码 #

import mysql.connector

def get_foods_detail(category):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='nutrition',
                                             user='root',
                                             password='631150#?ioB')

        cursor = connection.cursor()
        sql_select_query = """select * from foods where category = %s"""
        # set variable in query
        cursor.execute(sql_select_query, (category,))
        # fetch result
        record = cursor.fetchall()

        for row in record:
            print("Id  ", row[0], "CATEGORY:  ", category.rstrip(), "TYPE:  ", row[2], "BRAND: ", row[3], "SELLER: ", row[4], "Kcal: ", row[7])
                       
        choose = input("Input id of food consumed ")
        choose = int(choose)
        print(choose)
        sql_select_query = """ select * from foods where id = %s"""
        # 修正：使用choose变量，且用单元素元组格式
        cursor.execute(sql_select_query, (choose,))
        record = cursor.fetchone()
        print(record)
        user_input = input("Correct record? y/n")
    except mysql.connector.Error as error:
        print("Failed to get record from MySQL table: {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

category = input("Enter Food Category: ")
get_foods_detail(category)

关键修正点 #

1. 替换参数变量：将(id)改为(choose,)，choose是你获取的用户输入ID，而id是Python内置函数，并非目标参数。
2. 参数格式正确：单元素元组必须添加逗号(choose,)，确保MySQL能识别为序列类型；也可以用列表格式[choose]，效果一致。

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