0

Not sure how to get rid of type errors without jeopardizing functionality of LIKE operator - namely the % to indicate finding strings in the beginning, end, or throughout.

Trying to search for isbns of books that has the variable characters 380X.

Getting rid of % to fix type error returned an empty list.

isbn = request.form.get("search")
# Search the database for matching or part matching string, isbn, title, or author
book = db.execute("SELECT * FROM books WHERE isbn LIKE '%:isbn%'", {"isbn":str(isbn)}).fetchall()

expected: a list of books and their columns but i get this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "380" LINE 1: SELECT * FROM books WHERE isbn LIKE '%'380'%' ^

[SQL: SELECT * FROM books WHERE isbn LIKE '%%%(isbn)s%%']

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
soysushi
  • 71
  • 7
  • Placeholders don't operate exactly like string interpolation (which is why they're safer from SQL injection attacks). Your best bet is probably to pass in a string including the %. Something like `...LIKE :isbn", {"isbn": '%' + str(isbn) + '%'}).fetchall()` – jmelesky Jun 04 '19 at 02:13
  • Thank you, this one solved it. I didn't know you can pass % into it. – soysushi Jun 04 '19 at 16:53

1 Answers1

0

You can concatenate the % like so: db.execute("SELECT * FROM books WHERE isbn LIKE '%%' || %(isbn)s || '%%', {"isbn":str(isbn)})

Note that you use %% instead of % when using named parameters, at least in psycopg2.

Jeremy
  • 6,313
  • 17
  • 20