CODEWITHSUNDEEP
pythonsqlsqlite
ramrod
ramrod

Reputation: 3

Python/SQLite3 - Search function does not work for multiple combinations of results

Below is a snippet of code containing a search function I am writing as part of a back-end python script to a front end Tkinter interface. The interface has 4 fields: Title, Author, Year, and ISBN.

import sqlite3

def search(title='', author='', year='', isbn=''):
    conn = sqlite3.connect('books.db')
    cur = conn.cursor()
    cur.execute('SELECT * FROM book where title=? OR author=? OR year=? OR isbn=?', (title, author, year, isbn))
    rows = cur.fetchall()
    conn.close()
    return rows

The problem with this search function is that if more than 1 parameter if filled in, for example

"author=Barack Obama"

and

"year=2018"

the function will return ALL results matching Barack Obama books, and also ALL results including books that were published in 2018.

The user expectation is that only books written by Barack Obama in 2018 are returned.

What am I doing wrong?

Upvotes: 0

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

It seems that you want to AND together the criteria in the WHERE clause, but possibly also use a flexible statement which would discount one or more parameters which are not present. Something like this might work:

SELECT *
FROM book
WHERE
    (title = ? OR title IS NULL) AND
    (author = ? OR author IS NULL) AND
    (year = ? OR year IS NULL) AND
    (isbn = ? OR isbn IS NULL);

Upvotes: 1

Related Questions