在 SQLAlchemy 中动态构建过滤器 [英] Dynamically constructing filters in SQLAlchemy

问题描述

我正在寻找一种使用 SQLAlchemy 动态构建过滤器的方法.即给定列、运算符名称和比较值，构造相应的过滤器.

I am looking for a way to dynamically construct filters using SQLAlchemy. That is, given the column, the operator name and the comparing value, construct the corresponding filter.

我将尝试使用示例进行说明(这将用于构建 API).假设我们有以下模型:

I'll try to illustrate using an example (this would be used to build an API). Let's say we have the following model:

class Cat(Model):

  id = Column(Integer, primary_key=True)
  name = Column(String)
  age = Column(Integer)

我想将查询映射到过滤器.例如，

I would like to map queries to filters. For example,

• /cats?filter=age;eq;3 应该生成 Cat.query.filter(Cat.age == 3)

/cats?filter=age;in;5,6,7&filter=id;ge;10 应该生成 Cat.query.filter(Cat.age.in_([5, 6, 7])).filter(Cat.id >= 10)

我环顾四周，想看看它是如何完成的，但找不到一种不涉及将每个运算符名称手动映射到比较器或类似内容的方法.例如，Flask-Restless 保存所有支持操作的字典并存储相应的 lambda 函数(代码在这里).

I looked around to see how it has been done but couldn't find a way that didn't involve manually mapping each operator name to a comparator or something similar. For instance, Flask-Restless keeps a dictionary of all supported operations and stores the corresponding lambda functions (code here).

我在 SQLAlchemy 文档中搜索并找到了两个潜在的线索，但似乎都不令人满意:

I searched in the SQLAlchemy docs and found two potential leads but neither seemed satisfying:

• using Column.like, Column.in_...:这些操作符可以直接在列上使用，使用 getattr 会很简单 但仍有一些缺失(==、> 等).

• using Column.like, Column.in_...: these operators are available directly on the column which would make it simple using getattr but some are still missing (==, >, etc.).

使用Column.op:例如Cat.name.op('=')('Hobbes') 但这似乎不适用于所有运算符(即 in).

using Column.op: e.g. Cat.name.op('=')('Hobbes') but this doesn't seem to work for all operators (in namely).

如果没有 lambda 函数，有没有一种干净的方法可以做到这一点?

Is there a clean way to do this without lambda functions?

推荐答案

如果这对某人有用，我最终会这样做:

In case this is useful to someone, here is what I ended up doing:

from flask import request

class Parser(object):

  sep = ';'

  # ...

  def filter_query(self, query):
    model_class = self._get_model_class(query) # returns the query's Model
    raw_filters = request.args.getlist('filter')
    for raw in raw_filters:
      try:
        key, op, value = raw.split(self.sep, 3)
      except ValueError:
        raise APIError(400, 'Invalid filter: %s' % raw)
      column = getattr(model_class, key, None)
      if not column:
        raise APIError(400, 'Invalid filter column: %s' % key)
      if op == 'in':
        filt = column.in_(value.split(','))
      else:
        try:
          attr = filter(
            lambda e: hasattr(column, e % op),
            ['%s', '%s_', '__%s__']
          )[0] % op
        except IndexError:
          raise APIError(400, 'Invalid filter operator: %s' % op)
        if value == 'null':
          value = None
        filt = getattr(column, attr)(value)
      query = query.filter(filt)
    return query

这涵盖了所有 SQLAlchemy 列比较器:

This covers all SQLAlchemy column comparators:

• eq for ==
• lt 表示 <
• ge for >=
• in for in_
• like for like
• 等

• eq for ==
• lt for <
• ge for >=
• in for in_
• like for like
• etc.

可以找到带有相应名称的详尽列表此处.

The exhaustive list with their corresponding names can be found here.

这篇关于在 SQLAlchemy 中动态构建过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！