Syntax for AND and OR statements in SQLalchemy core?

Question

How do I write the following SQLite expression in SQLalchemy core?

SELECT * FROM table
WHERE id = 1
AND (last_date IS NULL OR
    last_date < date('now', '-30 day') );

I saw examples in SQLalchemy orm using the _and and _or but I wasn't sure if those applied to SQLalchemy core.

https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators

Answer 1

yes, you can use sqlalchemy.and_ and sqlalchemy.or_ on column expressions (note the underscore at the end, not beginning, leading underscores signify non-supported, private api).

You can also use & and |, the bitwise operators, and they will be compiled in the same fashion as and_/or_. Be sure to parenthesize them properly, & and | have much higher operator precedence than and and or.

Your code would look a bit like:

t.select().where((t.c.id == 1) 
                 & ((t.c.last_date == None)
                    | (t.c.last_date < sa.func.date('now', '-30 day'))))

Another option is to treat conjunctive clauses as progressively filtered relations, since the identity:

σ_{a ∧ b}R ↔ σ_b σ_aR

you can write that in sqlalchemy as

t.select().where(t.c.id == 1) \
          .where((t.c.last_date == None)
                 | (t.c.last_date < sa.func.date('now', '-30 day')))

And it produces the same SQL as above.