sqlalchemy how to using AND in OR operation?

Question

i need to do this query:

SELECT *
FROM tbl_member
WHERE (member_type==1 AND member_status==1)
   OR (member_type==2 and member_status==2)

i've tried:

q=session.query(tbl_member) \
  .filter(or_(and_(tbl_member.member_type==1,tbl_member.member_status==1), \
              and_(tbl_member.member_type==2,tbl_member.member_status==2)))

and

q=session.query(tbl_member) \ 
  .filter(or_((and_(tbl_member.member_type==1,tbl_member.member_status==1)), \
              (and_(tbl_member.member_type==2,tbl_member.member_status==2))))

the query sql still like this:

SELECT *
FROM tbl_member
WHERE member_type==1 AND member_status==1 OR member_type==2 AND member_status==2

how should i do?

[Related thread](https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or), with [answer](https://stackoverflow.com/a/1241158/) providing a list of references for `AND` taking precedence over `OR`. — metatoaster, Jun 08 '22 at 02:56

score 10 · Accepted Answer · answered Mar 05 '13 at 08:05

10

The query is semantically the same because AND has precedence over OR, therefore
WHERE Cond1 AND Cond2 OR Cond3 AND Cond4 will produce the same result as
WHERE (Cond1 AND Cond2) OR (Cond3 AND Cond4).

Were you to try the other way around (switch or_ and and_ in your code), you would notice that sqlalchemy does generate parenthesis.

answered Mar 05 '13 at 08:05

van

74,297
13
168
171

1

i think that the "WHERE Cond1 AND Cond2 OR Cond3 AND Cond4" and "WHERE (Cond1 AND Cond2) OR (Cond3 AND Cond4)" should be not the same. – nick.xu Mar 06 '13 at 01:57
sorry, you are right, i tested in mysql database. is the same result. – nick.xu Mar 06 '13 at 03:01

sqlalchemy how to using AND in OR operation?

1 Answers1

Linked