Check if all dates in database (sqlalchemy) are between two distinct dates-CodePudding

If I have created a Dates column in flask sqlalchemy and also stored some dates in it, how can I check if each and every one of these dates are between to dates that I choose

CodePudding user response：

There are lots of ways to accomplish this. Here's one example.

The goal is to select all rows that have a date outside your desired range. If the result set is empty, all rows have a valid date. For good measure, we'll include any rows that don't have a date value in our "bad rows" query.

from sqlalchemy import select, or_

with Session.begin() as session:
    my_start_date = '2022-01-01'
    my_end_date = '2022-01-31'

    query = select(MyTable).where(
                                or_(MyTable.date < my_start_date, 
                                    MyTable.date > my_end_date,
                                    MyTable.date == Null)
                                  )
    results = session.execute(query).all()

Now you can take a look at the results and see what's up.

CodePudding user response：

you can use between in your orm or plain query:

with Session.begin() as session:
    my_start_date = '2022-01-01'
    my_end_date = '2022-01-31'
    q = session.query(table_name).filter(table_name.c.date.between(my_start_date,my_end_date))
    .....

select(table_name).where(table_name.c.date.between(my_start_date, my_end_date))