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?

Vertebrate

Related thread, with answer providing a list of references for AND taking precedence over OR. – Shaff

Answer 1

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.

Fright

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. – Vertebrate

sorry, you are right, i tested in mysql database. is the same result. – Vertebrate