Python Sqlalchemy 按列表中的字段过滤但保持原始顺序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29326297/
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 filter by field in list but keep original order?
提问by user1835351
I have a Shoe model like this:
我有一个这样的鞋子模型：
class Shoe(db.Model):
id = db.Column(db.Integer, primary_key = True)
asin = db.Column(db.String(20), index = True)
I have a list of ids like ids = [2,1,3] and when I query on the Shoe model such that the results have ids in the 'ids' list, I want back: [{id:2, asin:"111"},{id:1, asin:"113"},{id:3, asin:"42"}] but the problem is that using the following query statement doesn't preserve the original order, the results will come back random. How do I keep the order of the list I filtered by?
我有一个像 ids = [2,1,3] 这样的 id 列表，当我查询 Shoe 模型以便结果在“ids”列表中有 id 时，我想返回：[{id:2, asin:” 111"},{id:1, asin:"113"},{id:3, asin:"42"}] 但是问题是使用下面的查询语句没有保留原来的顺序，结果会出来随机回来。如何保持我过滤的列表的顺序？
Incorrect one: Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()
错误一： Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()
采纳答案by Rob
If you have a reasonable small list of ids, you could just perform SQL queries on each id individually:
如果您有一个合理的小 id 列表，您可以单独对每个 id 执行 SQL 查询：
[Shoe.query.filter_by(id=id).one() for id in my_list_of_ids]
For a large number of ids, SQL queries will take a long time. Then you are better off with a single query and putting the values in the correct order in a second step (borrowed from how to select an object from a list of objects by its attribute in python):
对于大量的 id，SQL 查询会花费很长时间。然后，您最好使用单个查询并在第二步中按正确的顺序放置值（从如何通过 python 中的属性从对象列表中选择对象中借用）：
shoes = Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).all()
[next(s for s in shoes if s.id == id) for id in my_list_of_ids]
This is assuming the id's are unique (which they should be in your case). The first method will raise an exception if there are multiple elements with the same id.
这是假设 id 是唯一的（在您的情况下应该是唯一的）。如果有多个具有相同 id 的元素，第一种方法将引发异常。
回答by Alexander R.
What do you mean when you say "original order"? Database doesn't have such thing as "original order". If you need some order, you must add something like:
当您说“原始订单”时，您是什么意思？数据库没有“原始订单”这样的东西。如果您需要一些订单，您必须添加如下内容：
.order_by(Shoe.id.desc())
If you don't specify the order, it's still possible that you get ordered data from database. But in this case, database just uses order that doesn't needs any unnecessary data manipulation. It's just looks like an ordered data, but it isn't.
如果不指定顺序，仍然有可能从数据库中获取有序数据。但在这种情况下，数据库只使用不需要任何不必要的数据操作的顺序。它只是看起来像一个有序的数据，但事实并非如此。
回答by Earth Ponce Maniebo
I also have the same problem using a MySQL database. This is what I did:
我在使用 MySQL 数据库时也遇到了同样的问题。这就是我所做的：
my_list = [13,14,5,6,7]
# convert my_list to str
my_list_str = ','.join(map(str, my_list))
And this is how my query looks like:
这就是我的查询的样子：
checkpoints = (
db_session.query(Checkpoint)
.filter(Checkpoint.id.in_(my_list))
.order_by('FIELD(id, ' + my_list_str + ')')
.all()
)
FIELD() is a native function in MySQL.
FIELD() 是 MySQL 中的原生函数。
EDIT: So your query should look like this:
编辑：所以你的查询应该是这样的：
my_list_of_ids_str = ','.join(map(str, my_list_of_ids))
Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).order_by('FIELD(id, ' + my_list_of_ids_str + ')').all()
Cheers
干杯
回答by bjmc
One way I've solved this problem in the past is by using a SQL CASE expressionto tell the database in what order I'd like the rows returned. Using your example:
我过去解决这个问题的一种方法是使用SQL CASE 表达式告诉数据库我希望返回的行的顺序。使用您的示例：
from sqlalchemy.sql.expression import case
ordering = case(
{id: index for index, id in enumerate(my_list_of_ids)},
value=Shoe.id
)
Shoe.query.filter(Shoe.id.in_(my_list_of_ids)).order_by(ordering).all()
