SQLite, Python and lists

Question

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

indentation still not ok. Please use your editor to indent your code by 4 spaces and then paste into question. Makes life much easier ;) — flaschbier
– flaschbier, Commented Mar 16, 2016 at 20:52
try userOrder[j] as parameter for the select instead of prodItem. Similar for prodQty further down. Take userQty[j] instead. — flaschbier
– flaschbier, Commented Mar 16, 2016 at 20:55
userOrder[j] throws a ValueError: parameters are of unsupported type — user3717991
– user3717991, Commented Mar 16, 2016 at 20:58

flaschbier · Accepted Answer · 2016-03-16 21:34:46Z

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.

Collectives™ on Stack Overflow

SQLite, Python and lists

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related