Syntax For And And Or Statements In Sqlalchemy Core?

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 ex

Solution 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.