使用SQL数据表索引时遇IndexError及价格计算功能需求

• 开发了一个季节性果蔬清单数据库，希望根据用户输入查询数据库，将结果存入groceryList，对应价格存入priceList，但运行时触发IndexError: list index is out of range错误
• 还需要实现：让用户输入所选商品的购买重量，与对应价格相乘计算总价的功能

import sqlite3
connection = sqlite3.connect('seasonalProduce.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS productList (
        productID INTEGER PRIMARY KEY,
        productName text NOT NULL,
        productPrice real
);""")


# add rows (records) to the productList table

productRecords = [(1, "Apples", 4.59),
                (2, "Grapes", 5.99),
                (3, "Pumpkin", 3.00),
                (4, "Strawberries", 2.50),
                (5, "Leafy Greens", 4.00)]
        
cursor.executemany("INSERT OR IGNORE INTO productList (productID, productName, productPrice) VALUES(?,?,?);", productRecords)

connection.commit()

cursor.execute("SELECT * FROM productList;")

produce = cursor.fetchall()

for rows in produce:
    print(rows)

connection.close()
# create list to store productID("ID"), price("price")
# with empty list as their values
# data structure to hold user items as they make their order
groceryList = []
priceList = []

total = 0
productType = 0 

# create tuples list with products
productsList = [(1, "Apples", 4.59),
                (2, "Grapes", 5.99),
                (3, "Pumpkin", 3.00),
                (4, "Strawberries", 2.50),
                (5, "Leafy Greens", 4.00)]

# This loop terminates when user select 2.EXIT option when asked
# in try it will ask user for an option as an integer (1 or 2) 
# if correct then proceed with an if/else statement asking for user input

# It should continue and keep adding the total price * weight - BUT THIS FUNCTION DOESN'T WORK

while True: 
    try:
        choice = int(input("1.SELECT\n2.EXIT\nPlease select 1 or 2 (from the menu) here : ")) # use new line formatting
    except ValueError:
        print("Invalid response. Please try again.")
    #
    if choice == 1:
        # have user enter productID -unique identification for each product
        #
        # straight into inner-loop with validation
        while True: # create inner loop to search database
            connection = sqlite3.connect('seasonalProduce.db')  # sQ search database
            cursor = connection.cursor()
            itemID = int(input("Please enter the product ID here : "))
            #
            # To display the updates animals to screen:
            cursor.execute("SELECT * FROM productList WHERE productID = ?;", (itemID,))
            #
            results = cursor.fetchall() # if it's there returns single row - list with 1 tuple
            #
            if results == []:
                print("Product ID not found.")
            else:
                # if it does exist return rows
                print(results) 
                # append to groceryList[]
                groceryList.append((results))
                # access price with indexing [2]
                priceList.append((results[2]))
                #
                # print(priceList)
                # Close connection to database
                connection.close()
                break
    else:
        break

1. 解决IndexError错误 #

cursor.fetchall()返回的是列表类型，哪怕只查到一条数据，也是[(productID, productName, productPrice)]这种嵌套结构。原代码直接用results[2]会报错，因为列表只有一个元素（索引0），正确写法是先取results[0]拿到商品元组，再通过索引2获取价格，即results[0][2]。

同时原代码groceryList.append(results)会把整个嵌套列表加进去，改成groceryList.append(results[0])直接存储商品元组，后续处理更方便。

2. 实现重量输入与总价计算 #

在用户确认商品存在后，添加重量输入的校验循环，计算商品小计并累加到总价，退出时展示订单汇总。

import sqlite3
connection = sqlite3.connect('seasonalProduce.db')
cursor = connection.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS productList (
        productID INTEGER PRIMARY KEY,
        productName text NOT NULL,
        productPrice real
);""")


# add rows (records) to the productList table
productRecords = [(1, "Apples", 4.59),
                (2, "Grapes", 5.99),
                (3, "Pumpkin", 3.00),
                (4, "Strawberries", 2.50),
                (5, "Leafy Greens", 4.00)]
        
cursor.executemany("INSERT OR IGNORE INTO productList (productID, productName, productPrice) VALUES(?,?,?);", productRecords)
connection.commit()

# 初始化展示所有商品
cursor.execute("SELECT * FROM productList;")
produce = cursor.fetchall()
print("=== 季节性果蔬清单 ===")
for row in produce:
    print(f"ID: {row[0]} | 名称: {row[1]} | 单价: {row[2]}元/kg")
connection.close()

# 订单相关变量
groceryList = []
priceList = []
total = 0

while True: 
    try:
        choice = int(input("\n1.选择商品\n2.退出结算\n请输入1或2: "))
    except ValueError:
        print("输入无效，请输入数字1或2")
        continue

    if choice == 1:
        while True:
            connection = sqlite3.connect('seasonalProduce.db')
            cursor = connection.cursor()
            try:
                itemID = int(input("请输入商品ID: "))
            except ValueError:
                print("请输入有效的数字ID")
                connection.close()
                continue

            cursor.execute("SELECT * FROM productList WHERE productID = ?;", (itemID,))
            results = cursor.fetchall()

            if not results:
                print("商品ID不存在，请重新输入")
                connection.close()
            else:
                product = results[0]
                print(f"已找到商品: {product[1]}，单价: {product[2]}元/kg")
                groceryList.append(product)
                product_price = product[2]
                priceList.append(product_price)

                # 获取购买重量并计算小计
                while True:
                    try:
                        weight = float(input(f"请输入{product[1]}的购买重量(kg): "))
                        if weight <= 0:
                            print("重量必须大于0，请重新输入")
                            continue
                        break
                    except ValueError:
                        print("请输入有效的数字重量")
                
                subtotal = product_price * weight
                total += subtotal
                print(f"已添加{weight}kg {product[1]}，小计: {subtotal:.2f}元")
                connection.close()
                break
    elif choice == 2:
        # 展示订单汇总
        print("\n=== 订单结算 ===")
        if not groceryList:
            print("您未选择任何商品")
        else:
            for idx, item in enumerate(groceryList):
                print(f"{idx+1}. {item[1]} | 单价: {item[2]}元/kg")
            print(f"总价: {total:.2f}元")
        break
    else:
        print("请输入1或2")

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