Flask-SqlAlchemy Filtering by time section of datetime

Question

I am looking to filter by the time section of a datetime column in Flask-Admin using Flask-SQlAlchemy.

My attempt so far is:

class BaseTimeBetweenFilter(filters.TimeBetweenFilter):
    def apply(self, query, value, alias=None):
        return query.filter(cast(Doctor.datetime, Time) >= value[0],
              cast(Doctor.datetime, Time) <= value[1]).all()

I've got the time selector showing and if I do

print (value[0])

or

print (value[1])

it prints out the inputted times as expected. However the query is not working.

admin-panel_1 | response = self.full_dispatch_request()

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1641, in full_dispatch_request

admin-panel_1 | rv = self.handle_user_exception(e)

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1544, in handle_user_exception

admin-panel_1 | reraise(exc_type, exc_value, tb)

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask/_compat.py", line 33, in reraise

admin-panel_1 | raise value

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1639, in full_dispatch_request

admin-panel_1 | rv = self.dispatch_request()

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1625, in dispatch_request

admin-panel_1 | return self.view_functionsrule.endpoint

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask_admin/base.py", line 69, in inner

admin-panel_1 | return self._run_view(f, *args, **kwargs)

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask_admin/base.py", line 368, in _run_view

admin-panel_1 | return fn(self, *args, **kwargs)

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask_admin/model/base.py", line 1818, in index_view

admin-panel_1 | view_args.search, view_args.filters)

admin-panel_1 | File "/usr/local/lib/python3.5/dist-packages/flask_admin/contrib/sqla/view.py", line 975, in get_list

admin-panel_1 | count = count_query.scalar() if count_query else None

admin-panel_1 | AttributeError: 'list' object has no attribute 'scalar'

Also, I import Time and Cast from sqlalchemy, is this OK or should I be getting it from flask-sqlalchemy?

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Time, cast

Sergey Shubin Sergey Shubin · Accepted Answer · 2017-03-14T07:44:42

Flask-Admin constructs a query by joining tables, applying filters and sorting. After all this procedures it calls the query itself and gets its results.

Your apply method should return sqlalchemy.orm.query.Query instance as the one it gets as query argument. When you add .all() method this query is called and you get query result as a list. Remove .all() call from result value and your filter should work:

class BaseTimeBetweenFilter(filters.TimeBetweenFilter):
    def apply(self, query, value, alias=None):
        return query.filter(
            cast(Doctor.datetime, Time) >= value[0],
            cast(Doctor.datetime, Time) <= value[1]
        )

The source of importing doesn't really matter. flask_sqlalchemy.SQLAlchemy instance contains the same objects as sqlalchemy:

>>> from flask_sqlalchemy import SQLAlchemy
>>> db = SQLAlchemy()
>>> db.Time
<class 'sqlalchemy.sql.sqltypes.Time'>
>>> db.cast
<function cast at 0x7f60a3e89c80>
>>> from sqlalchemy import Time, cast
>>> Time
<class 'sqlalchemy.sql.sqltypes.Time'>
>>> cast
<function cast at 0x7f60a3e89c80>
>>> db.cast == cast and db.Time == Time
True

Flask-SqlAlchemy Filtering by time section of datetime

1 Answers