CODEWITHSUNDEEP
pythonsqlpython-3.xpostgresqlpsycopg2
oldnoob
oldnoob

Reputation: 23

Correct function definition for optional parameters of a SELECT query in Python using psycopg2 with POSTGRESQL

I want to SELECT from my PostgreSQL database using optional filter parameters in a function, but am struggling to get the syntax right.

def search(title: str = '', author: str = "", year: int, isbn: int):
    conn = psycopg2.connect(
        " dbname='mydb' user='postgres' password='pwd' host='localhost' port='5432' ")
    curs = conn.cursor()
    curs.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?",
                 (title, author, year, isbn))
    rows = curs.fetchall()
    conn.close()
    return rows

I then want to search by passing only one parameter (author name) to the function, and have the SELECT-statement be able to ignore/skip the fields that have not been passed. When I execute

print(search(author="John"))

The output is

Traceback (most recent call last):
  File "backend.py", line 48, in <module>
    print(search(author="John"))
  File "backend.py", line 39, in search
    (title, author, year, isbn))
psycopg2.errors.UndefinedFunction: operator does not exist: text =?
LINE 1: SELECT * FROM book WHERE title=? OR author=? OR year=? OR is...
                                      ^
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.

I understand this relates to a mismatch between the datatype of the SELECT-statement and the database column type, and that the error reflects that there is no = operator for comparing those two data types. But how do I define my function parameters and the SELECT statement to get my query to work?

Upvotes: 2

Views: 1934

Answers (2)

Parfait
Parfait

Reputation: 107707

Alternatively, consider passing None as default which translates to NULL and use SQL's COALESCE to select the first not null value. If params are None, then the columns in query will equal each other which is essentially applying no filter.

def search(title = None, author = None, year = None, isbn = None):
    conn = psycopg2.connect(
        " dbname='mydb' user='postgres' password='pwd' host='localhost' port='5432' ")

    sql = """SELECT * FROM book 
             WHERE COALESCE(title, 'N/A') = COALESCE(%s, title, 'N/A')
               AND COALESCE(author, 'N/A') = COALESCE(%s, author, 'N/A')
               AND COALESCE(year, -1) = COALESCE(%s, year, -1) 
               AND COALESCE(isbn, -1) = COALESCE(%s, isbn, -1)
          """

    curs = conn.cursor()
    curs.execute(sql, (title, author, year, isbn))
    rows = curs.fetchall()
    conn.close()

    return rows

Upvotes: 0

Jeremy
Jeremy

Reputation: 6723

psycopg2 uses %s as the placeholder for string substitution, not ?. Try changing the ? to %s and it should work.

Edit: @Parfait brought up a good point about your where logic. Changing ? to %s will fix your syntax error, but still won't fix the logic. One common way of handling this is to default all of your parameters to None and change your query to something like this:

curs.execute("""
    SELECT * FROM BOOK
    WHERE (title = %(title)s OR %(title)s IS NULL)
      AND (author = %(author)s OR %(author)s IS NULL)
      AND (year = %(year)s OR %(year)s IS NULL)
      AND (isbn = %(isbn)s OR %(isbn)s IS NULL)""",
    {"title": title, "author": author, "year": year, "isbn": isbn})

This is not the most performant method, but it's probably the simplest.

Upvotes: 3

Related Questions