python sqlite3 partial search

Question

I want to do a partial search with python sqlite3. My initial query is:

cur.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?", (title, author, year, isbn))

Then I tried using the LIKE keyword and string formatting to obtain partial search results for the title, like this:

cur.execute("SELECT * FROM book WHERE title LIKE ? OR author=? OR year=? OR isbn=?", ('%{}%'.format(title), author, year, isbn))

As in https://stackoverflow.com/a/20904256/13290801

This seems to do the trick for title, but now when searching on the other parameters, it's not working at all although there is no error on the terminal. What am I missing?

EDIT I tried the answer posted by @forpas, but it gives me the same results.

So my new code for my search function is:

def search(title="", author="", year="", isbn=""):
 conn = sqlite3.connect('books.db')
 cur = conn.cursor()
 cur.execute("SELECT * FROM book WHERE title LIKE '%' || ? || '%' OR author=? OR year=? OR isbn=?", (title, author, year, isbn))

It works for title. If I search for "amst", I get the Amsterdam title: Title partial search works

But if I search by year for 1970 I get all the results: Year search doesn't work

• Barbaros Özhan: What do you want to list whenever the first text value is amst, and the second is 1970 at the same time ? I mean, do you want to return two lines or none.
• MeL: That has to yield no lines

Answer 1

If you want to do partial search for the title then you must concatenate the '%' wildcard at the start and at the end of the title that you search.
Also you need an additional condition for the case that you pass an empty string for a column and the operator AND instead of OR:

sql = """
SELECT * FROM book 
WHERE (title LIKE '%' || ? || '%' OR LENGTH(?) = 0)
 AND (author = ? OR LENGTH(?) = 0) 
 AND (year = ? OR LENGTH(?) = 0) 
 AND (isbn = ? OR LENGTH(?) = 0)
"""
cur.execute(sql, (title, title, author, author, year, year, isbn, isbn))

• MeL: I get the same result as with my original code, i.e. the partial search for title works, but the (full) search for the others no longer work.
• forpas: @MeL can you post sample data where my code does not work?
• MeL: I've added the new code and some screenshots
• forpas: @MeL you forgot to mention that you may search with empty strings for some of the parameters.
• forpas: In my code there are 8 parameters passed in cur.execute. 2 for each column. Did you use the code as it is?

Answer 2

You can create a parameter list to append the non-null values for text boxes for title,author,year, and isbn(trimming the complete whitespace values), while adding counterparts of those parameters with use of AND rather than OR operator to the Select Statement such as

query = "SELECT * FROM book WHERE 1=1 "
prm=[]
if len(title.strip())>0:
 prm.append('%{}%'.format(title))
 query+=" AND title LIKE ?"
if len(author.strip())>0:
 prm.append(author)
 query+=" AND author=?"
if len(year.strip())>0:
 prm.append(year)
 query+=" AND year=?"
if len(isbn.strip())>0:
 prm.append(isbn)
 query+=" AND isbn=?"
 
cur.execute(query, (prm))

• forpas: When 2 or more Textboxes are not empty in the form, then OR will not work. For example if Author = 'xyz' and Year = 1970, the results should be the books of 'xyz' in 1970.
• forpas: By default, the SQLite LIKE operator is case-insensitive for ASCII chars.