Using the SQL LIKE operator with tuple insertion

user_10002708 0
Viewed 96

I am trying to write a SQL query where a user inputs a name, and with tuple insertion the query returns similar names in the database using the LIKE operator.

This is what I tried:

user_input = input('Enter name: ')
c.execute('SELECT FirstName, LastName FROM Person WHERE FirstName LIKE('%' + ? + '%')', user_input)

user_input = input('Enter name: ')
c.execute('SELECT FirstName, LastName FROM Person WHERE FirstName LIKE '%' + user_input + '%'')

user_input = input('Enter name: ')
c.execute('SELECT FirstName, LastName FROM Person WHERE FirstName LIKE ?', '%' + user_input + '%')

However, the query fails to run and I am getting these errors.

TypeError: not all arguments converted during string formatting
TypeError: must be str, not tuple

Any help is appreciated!

pycharm sqlite sql python
1 Answers

You can either use double quotes to quote the SQL query in order to allow single quotes to quote the % characters:

c.execute("SELECT FirstName, LastName FROM Person WHERE FirstName LIKE '%' || ? || '%'",
    (user_input,))

or pass to the placeholder in the SQL query a string with user_input already enclosed in % characters:

c.execute('SELECT FirstName, LastName FROM Person WHERE FirstName LIKE ?',
    (f'%{user_input}%',))
user_68909120
Related