Calling function on column value before evaluating filter in Flask and SQLAlchemy-CodePudding

My problem:
Let's say I have a table with one DateTime column. I now want to query all rows which match a Date.

My idea:

Table.query.filter(Table.datetime.date() == date_to_be_matched)

Obviously this doesn't work because the column itself doesn't have a date() method. I also tried to just convert the date_to_be_matched into a datetime but I would need to access the time of the current row/value otherwise the == evaluation wouldn't work.

So my question is how do I call a function/access the current row and value before the filter expression gets evaluated.

I already looked at the SQLAlchemy Docs and a bunch of other questions on here but couldn't find an answer to my problem.

CodePudding user response：

In SQL, your query would be something like this:

SELECT * FROM mytable WHERE date(datetime_column) = '2022-02-03';

To do this in SQLAlchemy, use the same approach: apply the RDBMS's date function* to the column being filtered.

import sqlalchemy as sa

Table.query(Table).filter(sa.func.date(Table.datetime) == date_to_be_matched)

* The name of the function that extracts date parts from datetimes may vary by RDBMS, and not all RDBMSs may provide such a function. SQLAlchemy's func attributes are function names from the RDBMS, so if your RDBMS's equivalent function is get_date then you would do sa.func.get_date(...).