3

I am trying to get a query into a variable called results, in which I query the database to find the books with a title like the input from the search bar received from a post method. The query I am running is as follows:

results = db.execute("SELECT * FROM books WHERE title LIKE (%:search%)", {"search": search}).fetchall();

With the above query, I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "%".

This works as expected if I remove the %, or if I manually give the LIKE a parameter (eg: LIKE ('%the%')), but this does not really give back any results unless the search is exactly as one of the book titles in the database, and it defeats the purpose of using variable substitution by hard coding the parameters. I am also wondering if it's possible to use ILIKE for case insensitive when querying with SQLAlchemy.

I am aware that I could use Object Relational Mapping, and use different functions such as the filter function and whatnot, but for this assignment we are meant to not use ORM and use simple queries. Any suggestions?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
DGs
  • 89
  • 10

1 Answers1

6

Pass the entire search string as the parameter to the LIKE operator:

results = db.execute(text("SELECT * FROM books WHERE title LIKE :search"),
                     {"search": f"%{search}%"}).fetchall();

or alternatively concatenate in the database:

results = db.execute(
    text("SELECT * FROM books WHERE title LIKE ('%' || :search || '%')"),
    {"search": search}).fetchall();
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • why encapsulate the query string in text()? and where do this function comes from? – fccoelho Feb 06 '19 at 11:45
  • Core `execute()` does not by default handle named [paramstyle](https://www.python.org/dev/peps/pep-0249/#paramstyle) (the `:search`), but passes the query string as is to the underlying DB-API method – which usually does not support named style either. [`text()`](https://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.text) constructs on the other hand translate named style to what ever the DB-API is using. – Ilja Everilä Feb 06 '19 at 12:04