检查了sqlAlchemy
文档,但看不到使用filter和FUNC进行多列查询的示例。
如何基于我的模型组合查询以返回如下结果:
SELECT
COUNT(amount)a_cnt,
SUM(amount)a_sum,
AVG(amount)a_avg
FROM public.transaction
WHERE acc_id = 1
AND "traDate" >= '2019-11-20'
AND "traDate" <= '2019-12-01'
******************
a_cnt || a_sum || a_avg
------------------------
3 || 12 || 4
请看下面我的模型和查询函数,一个带有类,另一个带有会话,仍然不确定在这种情况下我应该使用哪一个。两者都会导致打印查询语法
型号:
class Transaction(db.Model):
id = db.Column(db.Integer, primary_key=True)
traDate = db.Column(db.Date, nullable=False)
amount = db.Column(db.Float, nullable=False)
desc = db.Column(db.String, nullable=False)
card = db.Column(db.String(1), nullable=False)
tag_id = db.Column(db.Integer, db.ForeignKey('tag.id'), nullable=True)
acc_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False)
uplDate = db.Column(db.DateTime, nullable=False, default=datetime.now)
### this?
def sum_filtered(account_id, date_from, date_to):
return db.session.query(db.func.count(Transaction.amount).label('a_cnt'), db.func.sum(Transaction.amount).label('a_sum'), db.func.avg(Transaction.amount).label('a_avg')).filter_by(acc_id = account_id).filter(Transaction.traDate >= date_from, Transaction.traDate <= date_to)
### OR this?
def sum_filtered(account_id, date_from, date_to):
return Transaction.query.with_entities(func.sum(Transaction.amount).label('a_sum')).filter_by(acc_id = account_id).filter(Transaction.traDate >= date_from, Transaction.traDate <= date_to)
应用程序:
@app.route(...)
templateData = {
...
'total_amnt' : model.Transaction.sum_filtered(accountid, f_from, f_to),
...
}
return render_template('/list.html', **templateData)
html:
...
<span class="input-group-text">Total £{{ total_amnt }}</span><!-- shows the query syntax-->
<span class="input-group-text">Total £{{ total_amnt.a_sum }}</span><!-- shows nothing-->
...
我错过了什么
找到这个Docs。如果没有更好的答案,我会接受的
应用程序:
然后是html:
相关问题 更多 >
编程相关推荐