Take user input from Python to SQL query

Question

I am creating a store front page where the user will be able to search for items inside of an SQL data base. I am having issues with the python logic where I am trying to use the WHERE logic to find what the user hass entered. Here is my code:

username = input("Enter your username >>> ")
password = input("Enter your password >>> ")

try:
    cursor.execute('SELECT * FROM users ORDER BY email')
except:
    print("The database does not exist")
else:
    list_of_users = cursor.fetchall()


def login(email: str, pwd: str, list_of_users: [()]) -> bool:
    for db_email, db_pwd in list_of_users:
        if (email == db_email) and (pwd == db_pwd):
            return True
    return False

#----------Storefront----------#
while login(username, password, list_of_users) == True:
    search_bar = input("Enter what item you would like to look up >>> ")
    sql = "SELECT * FROM item_in_stock WHERE item_name = "
    cursor.execute(sql , search_bar)
    for row in iter(cursor.fetchone, None):
        print(row)

also if someone has a better way of testing to see if what I get out of the table is the correct value that I am looking for instead of using

for row in iter(cursor.fetchone, None):
    print(row)

then please do share as I do not understand what that for loop is doing.

When I run the program, this is what I get:

Enter your username >>> joe@gmail.com
Enter your password >>> qwerty
Enter what item you would like to look up >>> Jumper
Traceback (most recent call last):
  File "C:/Users/jerem/PycharmProjects/assignment_core/main.py", line 30, in <module>
    cursor.execute(sql , search_bar)
sqlite3.OperationalError: incomplete input

No clue how to fix this.

score 1 · Accepted Answer · answered Nov 15 '22 at 21:26

1

cursor.execute as you are using it accepts two parameters, sql and parameters. I believe, according to sqlite docs and sqlite parameter reference, that you should define your string sql with sql = "SELECT * FROM item_in_stock WHERE item_name = ?" and pass parameters into cursor.execute in a tuple.

All in all, you might want to try something along the lines of:

while login(username, password, list_of_users) == True:
    search_bar = input("Enter what item you would like to look up >>> ")
    sql = "SELECT * FROM item_in_stock WHERE item_name = ?"
    cursor.execute(sql, (search_bar))
    for row in iter(cursor.fetchone, None):
        print(row)

answered Nov 15 '22 at 21:26

Joshua Smith

176
4

It doesn't work. It gives this error message. I see what the question mark does, but I cannot give it an exact number of question marks as I need it to be able to search for a variety of items. sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied. What I entered was Jumper, which has 6 characters, and it is set to accept 1 character as there is only 1 question mark – Jeremy Panthier Nov 15 '22 at 22:37
Oops, I forgot that because you are passing a tuple with only one element, Python doesn't see it as a tuple. It is trying to pass each character of search bar into the query. To explicitly show that it is a tuple with one value, try `cursor.execute(sql, (search_bar,))` (note the comma after search bar). – Joshua Smith Nov 15 '22 at 22:53

Take user input from Python to SQL query

1 Answers1