CS50:LIKE 运算符，带有 % 扩展的变量替换 [英] CS50: LIKE operator, variable substitution with % expansion

问题描述

我正在尝试对一个名为 results 的变量进行查询，在该变量中我查询数据库以查找标题类似于从 post 方法接收到的搜索栏输入的书籍.我正在运行的查询如下:

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 "%".

如果我删除 %，或者如果我手动给 LIKE 一个参数(例如:LIKE ('%the%'))，这将按预期工作，但是这不会真正返回任何结果，除非搜索与数据库中的书名之一完全相同，并且它违背了通过对参数进行硬编码来使用变量替换的目的.我还想知道在使用 SQLAlchemy 查询时是否可以使用 ILIKE 来区分大小写.

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.

我知道我可以使用对象关系映射，并使用不同的函数，例如过滤器函数等等，但对于这个分配，我们打算不使用 ORM 并使用简单的查询.有什么建议?

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?

推荐答案

将整个搜索字符串作为参数传递给 LIKE 运算符:

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

或者在数据库中连接:

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

这篇关于CS50:LIKE 运算符，带有 % 扩展的变量替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！