Trying to execute an 'advanced' query in SQLAlchemy but I'm stuck

The query I'm trying to execute is this one. I've pasted it below:

SELECT p.id, p.title, p.time_submitted, SUM(v.score) as num_votes 
FROM posts p, votes v
WHERE v.postid = p.id
GROUP BY p.id
ORDER BY 
   (SUM(v.score) - 1) / POW(TIMESTAMPDIFF(HOUR,p.time_submitted,NOW()) + INTERVAL 2 HOUR, 1.8) DESC
LIMIT 100

I was going to resort to using connection.execute to manually run the SQL without using the ORM but then realised it would fail in development mode (Pyramid) as sqlite doesn't support the functions used.

How would I go about executing this with the ORM?

DBSession().query(Posts).join(Posts.id, Votes.post_id).group_by(Posts.id).order_by(...)

I don't know how to take开发者_如何学C that further >.<

As you've already stated, there are functions in your query that are unsupported in sqlite (e.g., "pow"). Using the SA expression functions to construct your query will not help you in this case, as the resulting query will still contain the "pow" function that sqlite does not support.

Of course, besides the fact that sqlite is super-simple to use with a new project, there's nothing keeping you from using a different type of database while developing, and in this case you might need to.

If you want to dabble in the function stuff anyway, take a look at the your query would look something like this (Warning: untested code):

sum_result = func.sum(Vote.score) - 1
time_difference_result = func.pow(func.timestampdiff(text('HOUR'), Post.time_submitted, func.current_timestamp()) + func.interval(2, text('HOUR')), 1.8)
time_difference_result = func.timestampdiff(text('HOUR'), Post.time_submitted, func.now()) + func.interval(2, text('HOUR'))
vote_calculation = (sum_result / time_difference_result).label('vote_calculation')

session.query(Post.id, Post.title, vote_calculation)\
        .join(Vote)\
        .group_by(Post.id, Post.title)\
        .order_by('vote_calculation DESC')[:100]