python - How do I create an SQLite record for each item in a list

Question

I've written this code to create a list of user inputs which represent products in an SQLite DB table.

The list is referred to as basket, and now for each item in basket I want to create a record in another table which contains the orderID from the record I just created and the product ID which will come from the basket.

My current solution is very messy and looks like this:

shopping = True
                while shopping:

                    itemToAdd = input("Please enter the ID of the item to add to the basket: ")
                    basket.append(itemToAdd)
                    print(basket)
                    continueShop = input("Continue shopping?(y/n): ")
                    if continueShop == "n":
                        conn.execute("INSERT INTO Orders (UserID) VALUES (?)", (results[0][0],))
                        conn.commit()
                        counter = 0
                        for items in basket:
                            createOrderItems = "INSERT INTO OrderItems (OrderID, ProductID) VALUES (?,?)"
                            currentOrder = "SELECT * FROM Orders WHERE UserID = (?)"
                            conn.execute(currentOrder, (results[0][0],))
                            conn.execute(createOrderItems, (currentOrder, basket(counter)))
                            counter = +1

Currently this gives the error

line 108, in <module>
    conn.execute(createOrderItems, (currentOrder, basket(counter)))
TypeError: 'list' object is not callable

googling the problem was giving me the exact opposite of what I was trying to find so sorry if this is a poor question, I'm just really lost with this now having tried all I could think of.

UPDATE:

shopping = True
                while shopping:

                    itemToAdd = input("Please enter the ID of the item to add to the basket: ")
                    basket.append(itemToAdd)
                    print(basket)
                    continueShop = input("Continue shopping?(y/n): ")
                    if continueShop == "n":
                        conn.execute("INSERT INTO Orders (UserID) VALUES (?)", (results[0][0],))
                        conn.commit()
                        counter = 0
                        for items in basket:
                            createOrderItems = "INSERT INTO OrderItems (OrderID, ProductID) VALUES (?,?)"
                            currentOrder = ("SELECT * FROM Orders WHERE UserID = (?)", (results[0][0]))
                            conn.execute(createOrderItems, (currentOrder, basket[counter]))
                            counter = +1
                            conn.commit()

Updated the code to include basket[counter] instead of basket(counter) and that got rid of the error, however the raw sql statement itself was being added to the table instead of the ID the statement is meant to locate so I have attempted to fix that aswell by changing currentOrder, however it now gives this error:

line 107, in <module>
    conn.execute(createOrderItems, (currentOrder, basket[counter]))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Answer 1

Change this:

basket(counter)

To this:

basket[counter]

Also, currentOrder in same line must be an id, you currently pass the entire sql statement. Try to pass the

currentOrder[0][x]

where x is the column position (1st, 2nd, 5fth, etc) of the column that represents the OrderId in your Orders table. So, this line should become:

conn.execute(createOrderItems, (currentOrder[0][x], basket[counter]))