Skip to main content
 首页 » 编程设计

python之SQLAlchemy 混合属性表达式日期时间转换

2025年04月02日13del

我在 python 2.7 中使用 flask-sqlalchemy。

我正在尝试创建一个属性,我可以在查询中使用它来进行排序。我已经知道我应该为此使用一个 hybrid_property 和相应的表达式,但是我无法在表达式中获取一个 timedelta 对象。

我要执行的查询类似于 Article.query.filter(Article.language == language).order_by(Article.popularity.desc())

与

from math import exp 
from datetime import datetime, timedelta 
from sqlalchemy.ext.hybrid import hybrid_property 
 
class Article(db.Model): 
    __tablename__ = 'articles' 
    article_id = db.Column(db.Integer, primary_key=True) 
    # ... 
    language = db.Column(db.String(64), default='en') 
    date_time = db.Column(db.DateTime, default=datetime.utcnow) 
    readers_frac = db.Column(db.Float, default=0.) 
 
    @hybrid_property 
    def popularity(self): 
        # compute the popularity based on the readers_frac and the time in minutes since the article was published 
        min_since_published = (datetime.utcnow() - self.date_time).total_seconds()/60. 
        popularity_decay = exp(-(min_since_published**2 / (2.*1832.4**2))) 
        return self.readers_frac*popularity_decay 
 
    @popularity.expression 
    def popularity(cls): 
        # compute the popularity based on the readers_frac and the time in minutes since the article was published 
        min_since_published = (datetime.utcnow() - cls.date_time).seconds/60. 
        popularity_decay = exp(-(min_since_published**2 / (2.*1832.4**2))) 
        return cls.readers_frac*popularity_decay 

但是,在尝试计算 min_since_published 时,出现错误 AttributeError: 'BinaryExpression' 对象和 'Comparator' 对象都没有属性 'seconds'。有什么提示吗?谢谢!!

更新

我通过将所有内容更改为 sqlalchemy.func 表达式来修复它,即它可以(使用 mysql 后端)与

import sqlalchemy as sa 
@popularity.expression 
def popularity(cls): 
     # compute the popularity based on the readers_frac and the time in minutes since the article was published 
    min_since_published = sa.func.timestampdiff(sa.text('MINUTE'), cls.date_time, sa.func.utc_timestamp()) 
    popularity_decay = sa.func.exp(-(sa.func.pow(min_since_published, 2) / 6730009.)) 
    return cls.readers_frac*popularity_decay 

请您参考如下方法:

我了解到这实际上取决于您为此使用的数据库后端以及它提供的功能。例如,sqlite 将无法工作,因为数据库本身没有能力回答查询,但 mysql 可以。然后,它实际上只是查看函数作为原始 mysql 查询语句的样子,并将其转换为 sqlalchemy.func 表达式。这有效:

import sqlalchemy as sa 
@popularity.expression 
def popularity(cls): 
    # compute the popularity based on the readers_frac and the time in minutes since the article was published 
    min_since_published = sa.func.timestampdiff(sa.text('MINUTE'), cls.date_time, sa.func.utc_timestamp()) 
    popularity_decay = sa.func.exp(-(sa.func.pow(min_since_published, 2) / 6730009.)) 
    return cls.readers_frac*popularity_decay