How to write nested conjunctions (OR and AND clauses) in SQLAlchemy?

Question

Does anybody know how to produce nested conjunctions in SQLAlchemy?

I have some Python code similar to this:

import sqlalchemy as sa

a = sa.and_(mytable.col1 > 1, mytable.col2 < 3)
b = sa.and_(mytable.col1 < 1, mytable.col4 == None)
clause_args = [a, b] 
or_clauses = sa.or_(*clause_args)
session.query(mytable).filter(mytable.status.in_([1,2,3,4])).filter(or_clauses)

Note, this is just some pseudocode to demonstrate syntactical problem I am having. Don't analyze query logic too much. I just want to find a way how to add parenthesis around AND and OR blocks. SQLAlchemy produces SQL similar to this:

SELECT
     id, status, name, col1, col2, col3, col4
FROM mytable
WHERE
    status in (1,2,3,4)
AND (col1 > 1 
     AND col2 < 3
     OR col1 < 1
     AND col4 is NULL);

Note, AND condition has no parenthesis around logical AND blocks between ORs:

AND (col1 > 1 
     AND col2 < 3
     OR col1 < 1
     AND col4 is NULL);

I would like to force use of parenthesis in the filter with or_ and and_ conjunctions. I would like to parenthesize conditions so that SQL output looks like this:

SELECT
     id, status, name, col1, col2, col3, col4
FROM mytable
WHERE
    status in (1,2,3,4)
AND ((col1 > 1 
     AND col2 < 3)
     OR (col1 < 1
     AND col4 is NULL));

Can anybody suggest how to achieve this?

Thank you!

Answer 1

Use self_group(). Documentation Link: self_group

Solution for the example looks like

import sqlalchemy as sa

a = sa.and_(mytable.col1 > 1, mytable.col2 < 3).self_group()
b = sa.and_(mytable.col1 < 1, mytable.col4 == None).self_group()
clause_args = [a, b] 
or_clauses = sa.or_(*clause_args)
session.query(mytable).filter(mytable.status.in_([1,2,3,4])).filter(or_clauses)

Answer 2

IIUC, the parentheses you are looking for are not needed since AND takes precedence over OR.