python - SQLAlchemy 条件过滤中的抽象

Question

我已经为我的数据库创建了模型:

class Album(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(128))
    year = db.Column(db.String(4))
    tracklist = db.relationship('Track', secondary=tracklist,
                                backref=db.backref('albums',
                                lazy='dynamic'), lazy='dynamic')

class Track(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(128))

class Artist(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    releases = db.relationship('Track', secondary=releases,
                               backref=db.backref('artists',         
                               lazy='dynamic'), lazy='dynamic')

它们是多对多相关的专辑<-->轨道<-->艺术家

接下来，我有这个表格:

class SearchForm(FlaskForm):
    search_by_album = StringField('Album', validators=[Optional()])
    search_by_artist = StringField('Artist', validators=[Optional()])
    search_track = StringField('Track', validators=[Optional()])
    year = StringField('Year', validators=[Optional(), Length(max=4)])

我的想法是让用户自由填写所需的表单组合(但至少需要一个)，所以我有这个函数，它接收 SearchForm().data (一个不可变的字典 'field_name': '数据'):

def construct_query(form):
    query = db.session.query(*[field.label.text for field in form if field.data and field.name != 'csrf_token'])
    if form.search_by_album.data:
        query = query.filter(Album.title == form.search_by_album.data)
    if form.search_by_artist.data:
        query = query.filter(Artist.name == form.search_by_artist.data)
    if form.search_track.data:
        query = query.filter(Track.title == form.search_track.data)
    if form.year.data:
        query = query.filter(Album.year == form.year.data)
    result = query.all()
    return result

我的问题是是否有更抽象的方法在上面的函数中添加过滤器？如果有一天我决定向表中添加更多列(甚至创建新表)，我将不得不向 constrcut_query() 添加更多可怕的 if，这最终会变得巨大。或者这样的抽象不是Python式的方式，因为“显式优于隐式”？

PS我了解模型中的表单，但我不认为它们是我的情况

Answer 1

一种方法是将过滤器属性与某个位置的字段关联起来，例如作为表单本身的类属性:

class SearchForm(FlaskForm):

    search_by_album = StringField('Album', validators=[Optional()])
    search_by_artist = StringField('Artist', validators=[Optional()])
    search_track = StringField('Track', validators=[Optional()])
    year = StringField('Year', validators=[Optional(), Length(max=4)])

    # map form fields to database fields/attributes
    field_to_attr = {search_by_album: Album.title,
                     search_by_artist: Artist.name,
                     search_track: Track.title,
                     year: Album.year}

构建查询时，您可以以一种非常舒适的方式构建 where 子句:

def construct_query(form):
    query = db.session.query(*[field.label.text for field in form if field.data and field.name != 'csrf_token'])

    for field in form:
        if field.data:
            query = query.filter(form.field_to_attr[field] == field.data)

    # or:
    # for field, attr in form.field_to_attr.items():
    #    if field.data:
    #        query = query.filter(attr == field.data)

    result = query.all()
    return result

添加要过滤的新字段和属性只会转换为创建字段及其到属性的映射。