python - SQLAlchemy 计算非空字符串的数量

Question

我正在开发一个用于电影评级服务的 API。数据库中有 3 个模型（电影、用户、评论）：

class Film(Base):
    __tablename__ = 'films'

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)
    year = Column(Integer, nullable=False)

    reviews = relationship('Review', back_populates='film', cascade='all, delete')

    @aggregated('reviews', Column(Float))
    def rating(self) -> float:
        return func.avg(Review.rating)

    @aggregated('reviews', Column(Integer, default=0))
    def num_reviews(self) -> int:
        return func.count(Review.rating).filter(
            Review.comment.isnot(None), Review.comment != ''
        )

    @aggregated('reviews', Column(Integer, default=0))
    def num_ratings(self) -> int:
        return func.count('*')

class Review(Base):
    __tablename__ = 'reviews'

    id = Column(Integer, primary_key=True, index=True)
    film_id = Column(Integer, ForeignKey('films.id'))
    user_id = Column(Integer, ForeignKey('users.id'))
    rating = Column(Integer, nullable=False)
    comment = Column(String, nullable=True)

    film = relationship('Film', back_populates='reviews')
    user = relationship('User', back_populates='reviews')

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    login = Column(String, unique=True, index=True)
    hashed_password = Column(String, nullable=False)

    reviews = relationship('Review', back_populates='user', cascade='all, delete')

我面临一个特定于 SQLite 的问题。虽然在本地我所有的测试都没有错误地通过，但在 gitlab CI 的测试阶段有一个异常：

context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x7f9f10fcddc0>
    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "AS": syntax error
E       [SQL: UPDATE films SET num_reviews=(SELECT count(reviews.rating) FILTER (WHERE reviews.comment IS NOT NULL AND reviews.comment != ?) AS anon_1 
E       FROM reviews 
E       WHERE films.id = reviews.film_id) WHERE films.id IN (?)]
E       [parameters: ('', 1)]
E       (Background on this error at: http://sqlalche.me/e/13/e3q8)
.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py:608: OperationalError

我怀疑模型中的num_reviews方法Film是否正确。我正在尝试计算每部电影的非空评论（带有一些文字）的数量。有没有办法重写这个表达式？

标签： pythonsqlitesqlalchemysqlalchemy-utils