python - SQLite，Python和列表

我是Python和SQLite的新手，但是我有一个程序，希望用户输入要从数据库表中订购的项目数量。他们输入产品和数量，并且选择查询应返回匹配的项目。除了只打印最后一个用户条目而不打印所有用户条目之外，它都起作用。我认为问题出在选择查询行周围，但我无法弄清楚。我尝试了许多变体，但是它要么崩溃，要么仅返回最后一个用户条目。

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))

最佳答案

在街区

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结尾的查询中。您还必须将list userOrder转换为元组。但是，与prodQty中的条目顺序相比，弄乱结果的顺序变得很复杂。简单地将数组作为参数传递，并期望其将=“扩展”为in将不起作用。如果您不想完全更改程序，我不会尝试那样做。