#### Demand analysis
The requirements look like this. I have a list now, and the data in the list looks like this
` ` `
[(a, b), (c, d), (e, f)…]
` ` `
There are two fields’ from_user_id ‘and’ to_user_id ‘in the database. I now need to implement a query similar to the following
` ` `
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)
.
` ` `
#### Solution
– #### Method 1
` ` `
from sqlalchemy import tuple_
from sqlalchemy import or_, and_
UserProfile # model
# items is the list above, and limit is the length of the list
UserProfile.query.filter(tuple_(UserProfile.from_user_id, UserProfile.to_user_id).in_(items)).limit(limit)all()
` ` `
For the above methods, if the items is such [(, a), (b), (c), (d), (e), (f,)], that is only a field as a condition of filter, can write so
` ` `
# The list must be tuples
UserProfile.query.filter(tuple_(UserProfile.from_user_id).in_(items)).limit(limit).all()
` ` `
Here is an example:
` ` `
In [5]: UserProfile.query.filter(tuple_(UserProfile.id).in_([(14,), (232,)])).all()
Out[5]: [<UserProfile 14>, <UserProfile 232>]
` ` `
– #### Method two
` ` `
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()
` ` `
Here is an example:
` ` `
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>]
` ` `
If you have only one field as a filter, you can write as follows
` ` `
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>]
` ` `
Reference link:
[How to get rows which match a list of 3-tuples conditions with SQLAlchemy
](https://stackoverflow.com/que…