使用peewee和Python聚合记录

I'm trying to learn Python and have problem to understand how to use the aggregate functions with peewee.

我正在尝试学习Python,并且有理解如何使用peewee的聚合函数。

In my code I first do imports like:

在我的代码中,我首先执行以下导入:

import sys
from datetime import datetime, timedelta
from peewee import *
from database import (db_init, MF_Djur, MF_Logg, MF_Senaste_Koll, MF_Foderspec)

To test if peewee and database connection works, I have used the following code successfully:

为了测试peewee和数据库连接是否有效,我成功使用了以下代码:

antalgivor24h = MF_Logg.select() \
        .where((MF_Logg.Logg_RFID_ID == tag_no) & (MF_Logg.Logg_Tid > timelastday)).count()
print(antalgivor24h)

To my problem: I would like to use sum function and this is where I get problems. I want to do this sql in peewee format:

对我的问题:我想使用sum函数,这是我遇到问题的地方。我想用peewee格式做这个sql:

SELECT SUM(`Logg_Giva_Foder1`) 
FROM mf_logg 
WHERE `Logg_RFID_ID`='752007904107005' AND `Logg_Tid` >= (CURDATE() - INTERVAL 24 HOUR)

In peewee docs (http://peewee.readthedocs.org/en/latest/peewee/querying.html#aggregating-records) I have seen the following example code:

在peewee docs(http://peewee.readthedocs.org/en/latest/peewee/querying.html#aggregating-records)中,我看到了以下示例代码:

query = (User
         .select()
         .annotate(
             Tweet,
             fn.Max(Tweet.created_date).alias('latest_tweet_date')))

Based on this I tried:

基于此我试过:

totalgiva124h = MF_Logg.select() \
        .where((MF_Logg.Logg_RFID_ID == tag_no) & (MF_Logg.Logg_Tid > timelastday)) \
        .annotate(MF_Logg, fn.Sum(MF_Logg.Logg_Giva_Foder1))

This code gives me the following error:

此代码给出了以下错误:

Traceback (most recent call last):
  File "testscript.py", line 32, in <module>
    .annotate(MF_Logg, fn.Sum(MF_Logg.Logg_Giva_Foder1))
NameError: name 'fn' is not defined

I have googled this error but for peewee aggregate records I couldn't get much help (on general Python nameerrors I found a lot but nothing that helped me). However, on one page I read that it could help importing peewee fn separately. I therefore tried adding

我用google搜索了这个错误但是对于peewee聚合记录我得不到多少帮助(对于一般的Python名称错误,我发现了很多,但没有任何帮助我)。但是,在一个页面上我读到它可以帮助分别导入peewee fn。因此,我尝试添加

from peewee import *, fn

but then I get the following error, so no luck:

但后来我得到以下错误,所以没有运气:

Traceback (most recent call last):
  File "testscript.py", line 32, in <module>
    .annotate(MF_Logg, fn.Sum(MF_Logg.Logg_Giva_Foder1))
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 1763, in annotate
    query = query.ensure_join(query._query_ctx, rel_model)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 1522, in ensure_join
    query = self.switch(lm).join(rm, on=on).switch(ctx)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 199, in inner
    func(clone, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 1505, in join
    self._query_ctx, model_class))
ValueError: No foreign key between <class 'database.MF_Logg'> and <class 'database.MF_Logg'>

I hope someone knows how to write the query in a correct way. Any help is appreciated.

我希望有人知道如何以正确的方式编写查询。任何帮助表示赞赏。

1 个解决方案

#1

Annotate is not what you want in this case. Instead try:

在这种情况下,Annotate不是您想要的。而是尝试:

MF_Logg.select(fn.SUM(MF_Logg.Logg_Giva_Foder1)) \
    .where((MF_Logg.Logg_RFID_ID == tag_no) & (MF_Logg.Logg_Tid > timelastday)) \
    .scalar()