How to do a SQL query using a string wildcard and LIKE?

Question

I am new to python and currently learning to use SQL with python. I have the following code:

word = input("Enter a word: ")
query = cursor.execute("SELECT * FROM Dictionary WHERE Expression LIKE '%s%' " % word)
results = cursor.fetchall()

The second line throws an error since I don't think I can use '%s%' like that? How would I change this so as to be able to make this work? I want to be able to return all related entries to the users input. So if the user inputs "rain", then I want the query to return all possible results e.g. "raining", "rainy" etc. Thank you.

Does this answer your question? [Python String Formats with SQL Wildcards and LIKE](https://stackoverflow.com/questions/3134691/python-string-formats-with-sql-wildcards-and-like) — Abhilash Chandran, Jun 12 '20 at 17:11

score 1 · Answer 1 · answered Jun 12 '20 at 15:48

You should use cursor.execute() parameter substitution rather than string formatting, to prevent SQL injection.

Then use CONCAT() to surround the search string with %.

query = cursor.execute("SELECT * FROM Dictionary WHERE Expression LIKE CONCAT('%', %s, '%' "), (word,))

score 1 · Accepted Answer · answered Jun 12 '20 at 15:51

1

You can try

query = cursor.execute(f"SELECT * FROM Dictionary WHERE Expression LIKE '%{word}%' ")

answered Jun 12 '20 at 15:51

EnergyBoy

547
1
5
18

How to do a SQL query using a string wildcard and LIKE?

2 Answers2