SQLAlchemy function query on SQLite-CodePudding

Executing the following query in SQLite Studio to get all rows having a date in 2021 or 2022 properly works and returns all rows correspondingly:

select * FROM daily_reports
WHERE strftime('%Y', daily_reports.reporting_date) in ('2021','2022');

Having the same query in a Python app returns now rows and the where clause created is '... where 1 = 0'

q = ...    
q = q.filter((func.strftime('%Y', DailyReportItem.reporting_date) in ['2021','2022']))

Any idea what is to be changed to make it work is appreciated!

CodePudding user response：

The problem is that the statement you passed into .filter() does not get converted into SQL, it is executed as false in Python right away and then passed down to .filter() as argument.

The way you do the IN operator in SQLAlchemy is via DailyReportItem.reporting_date.in_(['2021', '2022']).

So in your case it should be something like this: q.filter(func.strftime('%Y', DailyReportItem.reporting_date).in_(['2021', '2022']))

I would rather just check if date is bigger than the beginning of the year 2021 and lower than the end of 2022. This way you would also get a better use of indexes.