SQLite、Python和列表

Question

我是Python和SQLite的新手，但我有一个程序，希望用户输入他们希望从数据库表中订购多少项。它们输入产品和数量，select查询将返回匹配的项目。它只打印最后一个用户条目，而不是所有用户条目。我想问题出在select查询行附近，但我想不出来。我尝试了许多变体，但它要么崩溃，要么只返回最后一个用户条目。你知道吗

enter code here
for i in range(orderQty):
    prodItem = int(input("Enter your Item to the List: "))
    userOrder.append(prodItem)
    prodQty = int(input("Enter the item quantity: "))
    userQty.append(prodQty)


for j in range(len(userOrder)):
    cursor = connection.execute("SELECT GTINnum, Item, CurrentStock,Cost from orderFile WHERE GTINnum= ?", (prodItem,))


    for row in cursor:
        print ("GTINnum =", row[0], "Item = ", row[1], "Cost = ", row[2], "Qty Ordered=",(prodQty), "\n")
        prodCost = prodQty * row[2]
        print ("Total product cost is: ", (prodCost))

Answer 1

在街区里

for j in range(len(userOrder)):
    cursor = connection.execute("SELECT GTINnum, Item, CurrentStock,Cost from orderFile WHERE GTINnum= ?", (prodItem,))
    for row in cursor:
        print ("GTINnum =", row[0], "Item = ", row[1], "Cost = ", row[2], "Qty Ordered=",(prodQty), "\n")
        prodCost = prodQty * row[2]
        print ("Total product cost is: ", (prodCost))

您正在反复使用上一个用户输入prodItem和prodQty。替换为遍历您以前构建的列表的内容：

for j in range(len(userOrder)):
    cursor = connection.execute("SELECT GTINnum, Item, CurrentStock,Cost from orderFile WHERE GTINnum= ?", (userOrder[j],))
    for row in cursor:
        print ("GTINnum =", row[0], "Item = ", row[1], "Cost = ", row[2], "Qty Ordered=",userQty[j], "\n")
        prodCost = userQty[j] * row[2]
        print ("Total product cost is: ", (prodCost))

也许我错过了一些事情，但应该可以清楚地看到你被困在哪里。。。你知道吗

更新：当您只想做一个选择时，您必须生成一个?序列，例如

q = "(" + ",".join(["?" for x in userOrder]) + ")"

并将其附加到以in结尾的查询。您还必须将listuserOrder转换为元组。但是，与prodQty中条目的顺序相比，摆弄结果的顺序变得很复杂。简单地将数组作为参数传递并期望它将=扩展到in将不起作用。如果你不想完全改变你的计划，我不会尝试这样做。你知道吗