Python SQLite searching post title with multiple keywords

Question

I'm hitting some trouble writing this in python and I can't seems to solve this other than using string formatting (Which is strongly not recommended)

Basically I call the function input() and ask for the user to type in some keyword (space separated) and I need to find all the posts with title containing any of the keywords.

For example, if the user input was "python SQL mysql", I need to find all the post that matches at least one of the keywords. In this example, the query will be

SELECT posts.title FROM posts 
    WHERE lower(posts.title) LIKE "%python%" OR 
    lower(posts.title) LIKE "%sql%" OR 
    lower(posts.title) LIKE "%mysql%";

However, here is the problem. The number of keywords could vary. Therefore I cannot write a fixed SQL statement like

db.execute("SELECT posts.title FROM posts WHERE posts.title LIKE ? OR posts.title LIKE ?",("%"+keyword1+"%", "%"+keyword2+"%"))

Instead I have to resort to a for loop in Python, something like:

query = "posts.title LIKE '%" + keywords[0] + "%'"
keywords.remove(0)
for k in keywords:
    query += "OR posts.title LIKE '%" + k +"'%"

However this is obviously not recommended. While what I'm working on is a school project so it's not mission critical, plus we are not graded against SQL injection attacks, I do want to know what is the proper way to solve this question.

Flyrobot27 · Accepted Answer · 2020-10-29 01:26:44Z

1

I think I thought of a maybe sub optimal but safer methods First, create the query statement, but do not add userInput into them:

query = "posts.title Like ? "
for i in range(len(keywords) - 1):
    query += "OR posts.title Like ?"

query = "SELECT posts.title FROM posts WHERE " + query

And then I use the sqlite API do it it for me:

keywords = list()
for k in keywords:
    keywords.append("%" + k + "%")
keywords = tuple(keywords)
result = db.execute(query, keywords)

answered Oct 29, 2020 at 1:26

Flyrobot27

739 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python SQLite searching post title with multiple keywords

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related