Python 列包含子字符串的 SQLAlchemy 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4926757/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLAlchemy query where a column contains a substring
提问by Dave
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?
我正在使用 SQLAlchemy 和 SQLite3 构建一个查询，我想在其中选择字符串列包含特定子字符串的行。实现这一目标的最佳方法是什么？
采纳答案by Paulo Scardine
Filter by db.table.column.like('%needle%'). There is also ilikefor a case insensitive search.
按 过滤db.table.column.like('%needle%')。还有ilike一个不区分大小写的搜索。
For a fancier interface you can allow for the known "dir" wildcards.
对于更高级的界面，您可以允许使用已知的“dir”通配符。
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))
Notes:
笔记：
- The
db.table.filteranddb.table.columnis forSQLSoup(SQLSoupis 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))
回答by Bluehorn
While table.c.column.like("%...%")should work, there is a more direct way to say what you want:
虽然table.c.column.like("%...%")应该有效，但有一种更直接的方式来表达您的需求：
table.c.column.contains("needle")
This will usually generate the same SQL query but it is better to read for the uninitiated. Note that contains does not seem to escape "_"and "%".
这通常会生成相同的 SQL 查询，但对于未入门的人来说最好阅读。请注意， contains 似乎没有转义"_"和"%"。
回答by kartheek
Try this
尝试这个
Model.query.filter(Model.columnName.contains('sub_string'))
回答by Sneha Ravichandran
@app.route('/<var>', methods=['GET'])
def getdb(var):
look_for = '%{0}%'.format(var)
log1 = table.query.filter(table.col.like(look_for))
I've used SQLAlchemy and Flask (app.route on top is a decorator). I used the get API to take in the variable that the user wishes to search for and I'm converting that variable to store it in another variable called look_for(since var cannot be used directly in the query) by using the format() and log1 stores the queried tuples.
我使用过 SQLAlchemy 和 Flask（顶部的 app.route 是一个装饰器）。我使用 get API 来接收用户希望搜索的变量，并通过使用 format() 和log1 存储查询的元组。
