Trying to compare string to SQL column entry

Question

con = lite.connect(db)  # connects to the database
cur = con.cursor()
for item in self.button_array:
    if item.isChecked():
        productName = item.text()
custCommand = '''SELECT COUNT(*) FROM Customers'''  # counts the number of rows in the table Customers
cur.execute(custCommand)
newCustomerID = cur.fetchall()[0][0] + 1000 # gives the order an CustomerID that is +1
print(newCustomerID)
con.close()
print(productName)
con = lite.connect(db)
cur = con.cursor()
findProductID = '''SELECT ProductID FROM Products WHERE ProductName = ''' + productName #searches for ProductID from the database and compares it to the product name
cur.execute(findProductID)
resultProductID = cur.fetchall()
print(resultProductID)
con.close()

I'm trying to get it so it compares the column ProductName (just a column with flower names in it) in the table to the text of the labels for the flowers but it keeps coming up with this:

<class 'sqlite3.OperationalError'> no such column: Tulips <traceback object at 0x03FAA4E0>

(Tulips is just an example of a flower available)

So it seems to think that the text is meant to be a column name instead of an item in a column and I'm not too sure how to get it to work, I'm sorry for explaining it poorly but I don't really know what else to do since I'm not that experienced with this.

Edit: So I tried putting the statement in the execution and using 'LIKE ?' instead

con = lite.connect(db)
            cur = con.cursor()
            findProductID = '''SELECT ProductID FROM Products WHERE ProductName = LIKE ?''' #searches for ProductID from the database and compares it to the product name
            cur.execute('''SELECT ProductID FROM Products WHERE ProductName = LIKE ?''',(productName))
            resultProductID = cur.fetchall()
            print(resultProductID)
            con.close()

But that ended up coming up with this:

<class 'sqlite3.OperationalError'> near "?": syntax error <traceback object at 0x0356A2D8>

Does this answer your question? [sqlite-parameter-substitution-and-quotes](https://stackoverflow.com/questions/1005552) — stovfl, Mar 29 '20 at 19:40
I tried that but it just came back with this near "?": syntax error — ramblingLunatic, Mar 29 '20 at 19:59

score 0 · Answer 1 · answered Mar 31 '20 at 13:23

findProductID = '''SELECT ProductID FROM Products WHERE ProductName = "{}"'''.format(productName)
            cur.execute(findProductID)
            resultProductID = cur.fetchall()
            print(resultProductID)
            con.close()
            #the final part of inserting into the table
            con = lite.connect(db)
            cur = con.cursor()
            command = '''INSERT INTO Entries(entryID, ProductID, CustomerID) VALUES ("{}", "{}", "{}")'''.format(newOrderID,resultProductID,newCustomerID)

so I did this to the code and it seems to work, it does save like this "[('9003',)]" but that probably will not be too hard to fix

Trying to compare string to SQL column entry

1 Answers1