Cs50: Like Operator, Variable Substitution with % Expansion

CS50: LIKE operator, variable substitution with % expansion

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();

How to db.execute in postgresql using the LIKE operator with variables within flask

Your library is naively substituting the value for :lookingFor into the middle of an SQL string, and the quoting is not correct for doing that. You could write the query such that the variable doesn't occur inside an SQL string:

isbn LIKE '%' || :lookingFor || '%'

Or, you could programatically add the '%' to the search string before passing it to the database. The latter options is likely best, because you should also be escaping any % or _ that happen to occur inside the :lookingFor already, so adding the unescaped % before and after would be a natural addition to that task.

Using db.execute in postgresql using the LIKE operator with variables within flask, no information passed

You're using an f-string in an attempt to use the variable searchBookVariableOnApplication_py, but not interpolating it within the f-string.

This:

{"lookingFor": f"\"%searchBookVariableOnApplication_py%\""}

Should be this:

{"lookingFor": f"\"%{searchBookVariableOnApplication_py}%\""}

Substring SQL Query Variable

you could try using concat for avoid quote and windchar issue

books = db.execute("SELECT * 
      FROM books 
      WHERE title LIKE concat('%',:form, '%')" , 
     ........

SqlAlchemy Programming Error when using raw SQL Like operator

You need string concatenation. Many SQL databases support concat() (and other have an equivalent function or operator, such as standard operator ||):

db.execute("SELECT * FROM books WHERE author LIKE CONCAT('%', :author, '%')", {"author":query})

Another option is to concatenate '%'s around your parameter in your application first, and then pass it to the query.

---

Related Topics