CODEWITHSUNDEEP
pythonlistsqliteiterator
user3717991
user3717991

Reputation: 11

SQLite, Python and lists

I am a newbie to Python and SQLite but I have a program where I want the user to enter how many items they want to order from a database table. They enter the products and quantities and the select query should return the items that match. It works except that it only prints out the last user entry and not all the user entries. I think the problem is around the select query line but I cannot figure it out. I have tried numerous variations but it either crashes or returns just the last user entry. 

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

Upvotes: 1

Views: 110

Answers (1)

flaschbier
flaschbier

Reputation: 4177

In the block

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

you are using the last user input prodItemand prodQty over and over. Replace by something that is iterating thru the lists you have built before:

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

Maybe I have missed some occurrence, but it should become clear where you are stuck...

Update: When you want to do only one select, you have to generate a sequence of ?, e.g. by

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

and append this to a query ending with in. You will also have to transform the list userOrder into a tuple. But then it becomes complicated to fiddle with the order of the result compared with the order of the entries in prodQty. Simply passing the array as a parameter and expect it to "expand" the = to an in will not work. If you do not want to completely change your program I would not try to go that way.

Upvotes: 1

Related Questions