Python MySQL查询与数据库表不匹配导致wishlist显示异常求助

测试代码时调用show_wishlist方法打印心愿单书籍信息，使用以下代码片段：

for book in wishlist:
        print("\tBook Name: {}\n\tAuthor: {}\n".format(book[1], book[3]))

预期book[1]对应书籍名称，book[3]对应作者信息，但实际输出结果为：

Book Name: George
Author: 8

该结果与预期不符，以下是完整代码及错误排查：

完整代码 #

import sys
import mysql.connector
from mysql.connector import errorcode

config = {
    "user": "whatabook_user",
    "password": "MySQL8IsGreat!",
    "host": "localhost",
    "database": "whatabook",
    "raise_on_warnings": True
}

def show_menu():
    """ Print the main menu """
    print("\n -- MAIN MENU -- \n\t1. Books\n\t2. Store Locations\n\t3. My Account\n\t4. Exit Program\n")
    try:
        option = int(input('Select a menu <Example 1 for book listing>: '))
        return option
    except ValueError:
        print("\n Invalid number entered, program terminated...\n")
        sys.exit(0)
    
def show_books(_cursor):
    _cursor.execute("SELECT book_id, book_name, author, details FROM book")
    books = _cursor.fetchall()
    print ("\n -- DISPLAYING BOOK LISTING --")
    for book in books:
        print("\n\tBook Name: {}\n\t Author: {}\n\t Details: {}\n".format(book[1], book[2], book[3]))
    
def show_locations(_cursor):
    _cursor.execute("SELECT store_id, locale FROM store")
    locations = _cursor.fetchall()
    print("\n -- DISPLAYING STORE LOCATIONS --")
    for location in locations:
        print("\n\tLocale: {}\n".format(location[1]))

def validate_user():
    try:
        user_id = int(input("\n\tEnter a customer ID <Example 1 for user_id 1>: "))
        if user_id < 0 or user_id > 3:
            print("\n Invalid customer ID, program terminated...\n")
            sys.exit(0)
        return user_id
    except ValueError:
        print("\n Invalid number, program terminated...\n")
        sys.exit(0)
    
def show_account_menu():
    try:
        print("\n -- Customer Menu -- \n\t1. Wishlist\n\t2. Add Book\n\t3. Main Menu")
        account_option = int(input("\n\tSelect an account menu <Example 1 for wishlist>: "))
        return account_option
    except ValueError:
        print("\n Invalid number, program terminated...\n")
        sys.exit(0)
    
def show_wishlist(_cursor, _user_id):
    _cursor.execute("SELECT user.user_id, user.first_name, user.last_name, book.book_id, book.book_id, book.book_name, book.author FROM wishlist INNER JOIN user ON wishlist.user_id = user.user_id INNER JOIN book ON wishlist.book_id = book.book_id WHERE user.user_id = {}".format(_user_id))
    wishlist = _cursor.fetchall()
    print("\n -- DISPLAYING WISHLIST ITEMS --")
    for book in wishlist:
        print("\tBook Name: {}\n\tAuthor: {}\n".format(book[1], book[3]))
    
def show_books_to_add(_cursor, _user_id):
    query_result = ("SELECT book_id, book_name, author, details FROM book WHERE book_id NOT IN (SELECT book_id FROM wishlist WHERE user_id = {})".format(_user_id))
    print(query_result)
    _cursor.execute(query_result)
    books_to_add = _cursor.fetchall()
    print("\n -- DISPLAYING AVAILABLE BOOKS --")
    for book in books_to_add:
        print("\tBook ID: {}\n\tBook Name: {}\n".format(book[0], book[1]))
    
def add_book_to_wishlist(_cursor, _user_id, _book_id):
    _cursor.execute("INSERT INTO wishlist(user_id, book_id) VALUES({}, {})".format(_user_id, _book_id))
    
try:
    db = mysql.connector.connect(**config) # connect to database
    cursor = db.cursor()
    print("\n -- WhatABook Application --")
    user_option = show_menu()
    while user_option != 4:
        if user_option == 1:
            show_books(cursor)
        elif user_option == 2:
            show_locations(cursor)
        elif user_option == 3:
            my_user_id = validate_user()
            account_option = show_account_menu()
            while account_option != 3:
                if account_option == 1:
                    show_wishlist(cursor, my_user_id)
                elif account_option == 2:
                    show_books_to_add(cursor, my_user_id)
                    book_id = int(input("\n\t Enter the book ID for the book you would like to add: "))
                    add_book_to_wishlist(cursor, my_user_id, book_id)
                    db.commit()
                    print("\n\tBook ID {} was added to your wishlist.".format(book_id))
                elif account_option < 0 or account_option > 3:
                    print("\n\tInvalid option, please retry...")
                account_option = show_account_menu()
        elif user_option < 0 or user_option > 4:
            print("\n\tInvalid option, please retry...")
        user_option = show_menu()
    print("\n\n\t Program terminated...")

except mysql.connector.Error as err:
    """ handle any errors that may come up """
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print(" The supplied username or password are invalid")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print(" The specified database does not exist")
    else:
        print(err)
        
finally:
    """ Close connection to MySQL (end of program) """
    db.close()

错误原因分析 #

问题出在show_wishlist函数的SQL查询语句和后续索引引用不匹配：

1. SQL查询语句中，字段顺序为：

   • 索引0：user.user_id
   • 索引1：user.first_name → 输出的"George"是用户名字，不是书籍名称
   • 索引2：user.last_name
   • 索引3：book.book_id → 输出的"8"是书籍ID，不是作者
   • 索引4：book.book_id（重复查询了该字段，完全冗余）
   • 索引5：book.book_name → 这才是正确的书籍名称字段
   • 索引6：book.author → 这才是正确的作者字段

2. 代码中错误地用book[1]和book[3]去匹配书名和作者，导致输出内容完全错误。

修复方案 #

方案一：调整索引引用（快速修复） #

将打印代码改为正确的索引：

print("\tBook Name: {}\n\tAuthor: {}\n".format(book[5], book[6]))

方案二：精简SQL查询（推荐） #

修改SQL语句，只查询需要的字段，避免冗余和索引混乱：

def show_wishlist(_cursor, _user_id):
    # 只查询书籍名称和作者，字段更清晰
    _cursor.execute("SELECT book.book_name, book.author FROM wishlist INNER JOIN book ON wishlist.book_id = book.book_id WHERE wishlist.user_id = {}".format(_user_id))
    wishlist = _cursor.fetchall()
    print("\n -- DISPLAYING WISHLIST ITEMS --")
    for book in wishlist:
        print("\tBook Name: {}\n\tAuthor: {}\n".format(book[0], book[1]))

该方案不仅解决了索引问题，还减少了不必要的数据查询，提升代码可读性。

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