python - 如何使用flask sqlalchemy使过滤查询更有效？

Question

关闭。这个问题需要更多focused .它目前不接受答案。












想改善这个问题吗？更新问题，使其仅关注一个问题 editing this post .

去年关闭。




Improve this question




我在 flask 应用程序中有一张下表

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)

以下参数被用作 get 的输入方法

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)

哪里 ns是我正在使用的命名空间

我目前正在对表启用过滤，我有以下代码:

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)

上面显示的代码工作得很好，但我想知道是否有更有效的方法来过滤数据。例如，如果有一种方法可以将最后的 2 elif 组合起来语句合二为一并使用 filter_by 进行过滤或 filter
样本数据

{
    "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"
        }]}

Answer 1

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_)

推理:您只需要两个参数(country_code 和 price_type)，并且您希望使用所有可用参数进行过滤，除非根本没有。因此，您清除所有冗余/不可用参数的过滤器，然后检查是否有 0 个或更多参数。