SQLAlchemy中等价于SQL“LIKE”语句的方法

Question

一个标签列具有类似于“苹果 香蕉 橘子”和“草莓 香蕉 柠檬”的值。我希望找到与以下语句等效的SQLAlchemy语句：

SELECT * FROM table WHERE tags LIKE "%banana%";

我应该向 Class.query.filter() 传递什么来实现这个目的？

- Gary Oldfaber

Answer 1

每个列都有like()方法，可以在query.filter()中使用。给定搜索字符串，在两侧添加%字符以在两个方向上搜索子字符串。

tag = request.form["tag"]
search = "%{}%".format(tag)
posts = Post.query.filter(Post.tags.like(search)).all()

- Daniel Kluev

---

你知道除了在苹果前面加一个空格之外，还有没有更好的方法来区分苹果和菠萝？ - Gary Oldfaber

最好的方式是将数据库进行归一化，并添加两个单独的表用于标签和标签与任务的关系，然后使用JOIN代替LIKE。否则，是的，似乎你需要在每个标签字符串周围添加某种分隔符。前导空格不够，因为还有钢笔和铅笔，带有％pen％。如果你使用“|apple|pineapple|pen|pencil|”之类的东西并匹配“％|pen |％”，它不应该冲突。 - Daniel Kluev

通过规范化，我不太确定如何将多个标签与给定任务相关联，或者反过来使用标签映射。 "Toxi" 的解决方案似乎将标签集合分组为单个项目，而不是分别存储每个标签？而在此（http://elixir.ematia.de/trac/wiki/Recipes/TagCloud）食谱中使用的方法似乎仅允许每个项目一个标签。哪些资源最适合阐明这个话题？ 我也阅读了这篇文章（http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html），但无法想象如何管理多个标签。 - Gary Oldfaber

就像我说的，你需要两个表。基本上，这只是典型的多对多关系，所以你可以按照SQLAlchemy的指南进行操作：http://www.sqlalchemy.org/docs/ormtutorial.html#building-a-many-to-many-relationship 你将拥有一个tags表，其中存储标签名称和其他标签信息，还将拥有一个task_tags表，每个添加到任务中的标签都将有一条记录。因此，具有2个标签的任务将在task_tags表中仅有2条记录。 - Daniel Kluev

这个程序是否安全，能够防止 SQL 注入攻击？我想将其指定为用户输入。 - Peter Mølgaard Pallesen

Answer 2

在上面的答案基础上，如果有人在寻找解决方案，你也可以尝试使用'match'操作符代替'like'。不想有偏见，但它在Postgresql中完美地为我工作。

添加到上面的答案，谁寻找解决方案，您可以尝试使用“match”操作符而不是“like”。不想有偏见，但它在Postgresql中对我完美地起作用。

Note.query.filter(Note.message.match("%somestr%")).all()

它继承了像CONTAINS和MATCH这样的数据库函数。然而，在SQLite中不可用。

有关详细信息，请访问常见过滤器运算符

- igsm

---

这两个操作符有什么区别？ - buhtz

@buhtz 取决于您的数据库后端，请查看 SQL-Alchemy 文档: https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.match 在 Postgres 中，您可以使用 to_tsquery 来添加文本操作符，例如 OR 和 AND https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES - Nick

顺便说一句：我发现match不支持部分字符串匹配（例如，abc％匹配abcd），而like则支持。 - Nick

Answer 3

如果您需要不区分大小写的模糊匹配实现：

session.query(TableName).filter(TableName.colName.ilike(f'%{search_text}%')).all()

- Prason Ghimire

Answer 4

试一下这段代码：

output = dbsession.query(<model_class>).filter(
    <model_class>.email.ilike("%" + <email> + "%")
)

- waruna k

Answer 5

在SQLAlchemy 1.4/2.0中：

q = session.query(User).filter(User.name.like('e%'))

- collinsmarra

Answer 6

虽然接受的答案运行良好，但“ORM查询对象作为SQLAlchemy 2.0的遗留结构”（参考：Legacy Query API - SQLAlchemy 2.0文档）。

在Python 3.10代码片段中，与SQL LIKE语句对应的SQLAlchemy v2.0等价于使用select构造的方式如下所示：

from typing import List

from sqlalchemy import select
from sqlalchemy.orm import Session

...

def get_multi_like_tag_substring_bidirectional(
    db: Session,
    *,
    tags_search_substring: str,
    skip: int = 0,
    limit: int = 10,
) -> List[Post]:
    return db.scalars(
        select(Post)
        .where(Post.tags.like(f"%{tags_search_substring}%"))
        .offset(skip)
        .limit(limit)
    ).all()

...

banana_tagged_posts = get_multi_like_tag_substring_bidirectional(
    db=db_session,
    tags_search_substring = "banana"
)
    

- sidd23

Answer 7

如果您使用本地SQL，可以参考我的代码，否则请忽略我的回答。

SELECT * FROM table WHERE tags LIKE "%banana%";

from sqlalchemy import text

bar_tags = "banana"

# '%' attention to spaces
query_sql = """SELECT * FROM table WHERE tags LIKE '%' :bar_tags '%'"""

# db is sqlalchemy session object
tags_res_list = db.execute(text(query_sql), {"bar_tags": bar_tags}).fetchall()

- Toby

---

非常感谢。您能解释一下 LIKE '%' :bar_tags '%' 语法吗？这很奇怪。 - Cédric ZUGER

在SQLA 1.4中，使用sqlite或postgresql + psycopg2至少不起作用; 引用的％字符会导致语法错误。 - snakecharmerb

同样适用于sqla 1.2，不起作用。 - Adam Parkin

我认为一些标点符号可能被 SO 吃掉了。在“%”和“:bar_tags”之间使用两个竖杠作为字符串连接。 - Peter Tölgyesi

Answer 8

如下所示，我在tags列中添加前导和尾随空格，以便可以匹配所需的标签，确保只匹配完整的单词，防止'pineapple'匹配到 'apple' 或 'sqlachemy'匹配到 'sql'。

tag = 'banana'
search = f"% {tag} %"  # !spaces around the tag
query = select(MyTable).filter((' ' + MyTable.tags + ' ').like(search))

- van

Answer 9

除了显而易见的之外

select(User).where(User.name.like(f'%{some_term}%'))

SQLAlchemy提供了一些其他生成LIKE子句的方法。

• startswith：搜索词后跟%通配符

select(User.name).where(User.name.startswith('A'))

SELECT users.name 
FROM users 
WHERE (users.name LIKE :name_1 || '%')

• endswith：以%通配符开头的搜索词

select(User.name).where(User.name.endswith('e'))

SELECT users.name 
FROM users 
WHERE (users.name LIKE '%' || :name_1)

• contains：在%通配符内搜索的搜索词

select(User.name).where(User.name.contains('i'))

SELECT users.name 
FROM users 
WHERE (users.name LIKE '%' || :name_1 || '%')

与like和ilike方法一样，每个方法都有一个不区分大小写的对应方法：istartswith、iendswith和icontains。

---

所有这些方法也适用于核心表列，并且还可以与传统的session.query语法一起使用。

- snakecharmerb

Answer 10

使用PostgreSQL的like（请参考上面接受的答案）在我这里不起作用，尽管情况匹配，但是ilike（大小写不敏感的like）可以。

- koks der drache

---

"ILIKE"是不区分大小写的"LIKE"版本，因此您的输入仅在大小写上有所不同。 - Martijn Pieters