How to make the filtering queries more efficient using flask sqlalchemy?

Question

I have a following table in a flask app

class Price(db.Model):
    __tablename__ = "prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(2), nullable=False)
    value = db.Column(db.Float(precision=2), nullable=False)
    start = db.Column(db.DateTime(timezone=True), nullable=False)
    end = db.Column(db.DateTime(timezone=True), nullable=False)
    price_type = db.Column(db.String(32), nullable=False)

The following parameters are being taken as input to the get method

from flask_restx import Resource, reqparse, inputs
parser = reqparse.RequestParser()
parser.add_argument("country_code", default=None, type=str,
                    choices=("DE", "NL"), help="Country code")
parser.add_argument("price_type", default=None,
                    type=str, help="Price type")
parser.add_argument("page", default=1, type=int,
                    help="Page Number")
parser.add_argument("limit", default=24, type=int,
                    help="Number of items to be displayed on one page")
parser.add_argument("value_from", type=float,
                    help="Starting value to filter values")
parser.add_argument("value_to", type=float,
                    help="Ending value to filter values")
parser.add_argument("sort", default="start", type=str,
                    choices=("id", "country_code", "value",
                             "start", "end", "price_type"),
                    help="Column to sort on")
parser.add_argument("dir", default="asc", type=str,
                    choices=("asc", "desc"),
                    help="Sort the column by ascending or descending")
parser.add_argument("start", type=inputs.date,
                    help="Start date (YYYY-MM-DD)")
parser.add_argument("end", type=inputs.date,
                    help="End date (YYYY-MM-DD)")
@ns.route("/")
class Prices(Resource)
    @ns.expect(parser, validate=True)
    def get(self):
        args = parser.parse_args()
        return DatabaseService.read_list(**args)

where ns is a namespace I am using

I am currently working on enabling filtering on the table and I have the following code:

class DatabaseService:

    @staticmethod
    def read_list(**filters):

        page = filters.pop('page')
        limit = filters.pop('limit')
        direction = filters.pop('dir')
        sort = filters.pop('sort')
        start_date = filters.pop('start')
        end_date = filters.pop('end')
        value_from = filters.pop('value_from')
        value_to = filters.pop('value_to')

        if all(filters[c] is not None for c in ('country_code', 'price_type')):
            print('Both are not none')
            items = Price.query.filter_by(**filters)

        elif all(filters[c] is None for c in ('country_code', 'price_type')):
            print('Both are none')
            items = Price.query

        elif filters['country_code'] is None:
            filters.pop('country_code')
            items = Price.query.filter_by(**filters)

        elif filters['price_type'] is None:
            filters.pop('price_type')
            items = Price.query.filter_by(**filters)

The code shown above works perfectly fine, but I was wondering if there is more efficient way of filtering the data. For example, if there is a way to combine the last 2 elif statements into one and do the filtering using filter_by or filter

Sample Data

{
    "items": [
        {
            "id": 1,
            "value": 21.4,
            "start": "2020-05-12T00:00:00+02:00",
            "end": "2020-05-12T01:00:00+02:00",
            "country_code": "DE",
            "price_type": "DAY_AHEAD"
        },
        {
            "id": 2,
            "value": 18.93,
            "start": "2020-05-12T01:00:00+02:00",
            "end": "2020-05-12T02:00:00+02:00",
            "country_code": "DE",
            "price_type": "DAY_AHEAD"
        },
        {
            "id": 3,
            "value": 18.06,
            "start": "2020-05-12T02:00:00+02:00",
            "end": "2020-05-12T03:00:00+02:00",
            "country_code": "LU",
            "price_type": "DAY_AHEAD"
        },
        {
            "id": 4,
            "value": 17.05,
            "start": "2020-05-12T03:00:00+02:00",
            "end": "2020-05-12T04:00:00+02:00",
            "country_code": "DE",
            "price_type": "TODAY"
        }]}

[Cross-posted on Code Review](https://codereview.stackexchange.com/q/243372/52915) — Mast, Jun 04 '20 at 21:10

score 3 · Accepted Answer · answered Jun 04 '20 at 15:55

filters_ = {k: v for k, v in filters.items() if k in ['country_code', 'price_type'] and v is not None}

if len(filters_) == 0:
    print('hi')
    items = Price.query
else:
    print()
    items = Price.query.filter_by(**filters_)

Reasoning: You only need two parameters (country_code and price_type) and you want to filter with all available parameters except in the case when there are none at all. So you clear your filter of all redundant/not available parameters and check then if there are 0 or more parameters.

How to make the filtering queries more efficient using flask sqlalchemy?

1 Answers1