#### 需求分析
需求是这样的,我现在有一个列表,列表中的数据是这样的
“`
[(a, b), (c, d), (e, f)…]
“`
数据库中有两个字段 `from_user_id` 和 `to_user_id`,我现在需要实现类似如下的查询
“`
where
(from_user_id = a and to_user_id = b)
or (from_user_id = c and to_user_id = d)
or (from_user_id = e and to_user_id = f)
…
“`
#### 解决方法
– #### 方法一
“`
from sqlalchemy import tuple_
from sqlalchemy import or_, and_
# model 为 UserProfile
# items 为上面的列表,limit 为列表长度
UserProfile.query.filter(tuple_(UserProfile.from_user_id, UserProfile.to_user_id).in_(items)).limit(limit)all()
“`
对于上面的方法,如果 items 是这样的 [(a,), (b,), (c,), (d,), (e,), (f,)],即只有一个字段作为筛选条件的话,也可以这么写
“`
# 列表里面一定得是元组
UserProfile.query.filter(tuple_(UserProfile.from_user_id).in_(items)).limit(limit).all()
“`
示例如下:
“`
In [5]: UserProfile.query.filter(tuple_(UserProfile.id).in_([(14,), (232,)])).all()
Out[5]: [<UserProfile 14>, <UserProfile 232>]
“`
– #### 方法二
“`
conditions = (and_(UserProfile.from_user_id==from_user_id, UserProfile.to_user_id==to_user_id) for (from_user_id, to_user_id) in items)
UserProfile.query.filter(or_(*conditions)).limit(limit).all()
“`
示例如下:
“`
In [43]: conditions = (and_(UserProfile.id==id, UserProfile.user_id==user_id) for (id, user_id) in [(14, ‘9ugy61mp3f’), (232, ‘9uh9u44uq1’)])
In [44]: UserProfile.query.filter(or_(*conditions)).all()
Out[44]: [<UserProfile 232>, <UserProfile 14>]
“`
如果只有一个字段作为筛选项的话,可以像下面这样写
“`
In [46]: conditions = (UserProfile.id==id for id in [14, 232])
In [47]: UserProfile.query.filter(or_(*conditions)).all()
Out[47]: [<UserProfile 232>, <UserProfile 14>]
“`
参考链接:
[How to get rows which match a list of 3-tuples conditions with SQLAlchemy
](https://stackoverflow.com/que…