cod3licious cod3licious - 4 months ago 66
Python Question

SQLAlchemy hybrid property expression datetime conversion

I'm using flask-sqlalchemy with python 2.7.

I'm trying to create a property which I can use in a query to order by. I already learned I should use a

hybrid_property
for this with the according expression but I'm having trouble getting a
timedelta
object in the expression.

The query I want to execute is something like
Article.query.filter(Article.language == language).order_by(Article.popularity.desc())


with

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


However I'm getting the error
AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'seconds'
when trying to compute
min_since_published
. Any hints? Thanks!!

UPDATE

I fixed it by changing everything to
sqlalchemy.func
expressions, i.e. it works (with a mysql backend) with

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

Answer

I've learned it really depends on the database backend you're using for this and which functions it provides. For example sqlite would not work as the database itself just doesn't have the capabilities to answer the query, but mysql does. Then it's really just about looking how the function would look like as an original mysql query statement and translating this into sqlalchemy.func expressions. This works:

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