列包含子字符串的 SQLAlchemy 查询 [英] SQLAlchemy query where a column contains a substring

问题描述

我正在使用 SQLAlchemy 和 SQLite3 构建一个查询，我想在其中选择字符串列包含特定子字符串的行.实现这一目标的最佳方法是什么?

I'm constructing a query using SQLAlchemy and SQLite3 in which I'd like to select rows in which a String column contains a particular substring. What is the best way to accomplish this?

推荐答案

Filter by db.table.column.like('%needle%').还有 ilike 用于不区分大小写的搜索.

Filter by db.table.column.like('%needle%'). There is also ilike for a case insensitive search.

对于更高级的界面，您可以允许使用已知的dir"通配符.

For a fancier interface you can allow for the known "dir" wildcards.

if '*' in needle or '_' in needle: 
    looking_for = needle.replace('_', '__')\
                        .replace('*', '%')\
                        .replace('?', '_')
else:
    looking_for = '%{0}%'.format(needle)

result = db.table.filter(db.table.column.ilike(looking_for))

注意事项:

• db.table.filter 和 db.table.column 用于 SQLSoup (SQLSoup 如果数据库是由另一个应用程序创建的，则很有用)
• 对于 SQLAlchemy Core，它是 select(column_list).where(table.c.column.ilike(expr)).当您希望使用原始 SQL 的所有功能而无需使用字符串插值手动编写语句时，此接口是可行的方法(将它与 SQLSoup 一起使用以进行自省，因此您无需声明表)
• 对于 SQLAlchemy 声明式(Flask 中使用的)，它是 Model.query.filter(Model.field.ilike(expr))

• The db.table.filter and db.table.column is for SQLSoup (SQLSoup is useful if the database was made by another application)
• for SQLAlchemy Core it is select(column_list).where(table.c.column.ilike(expr)). This interface is the way to go when you want all the power from raw SQL without having to compose statements by hand using string interpolation (use it along SQLSoup for introspection, so you don't need to declare tables)
• for SQLAlchemy Declarative (the one used in Flask) it is Model.query.filter(Model.field.ilike(expr))

这篇关于列包含子字符串的 SQLAlchemy 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！