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

问题描述

我正在尝试对一个名为result的变量进行查询，在该查询中，我查询数据库以查找标题类似于从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 operator :

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屋！