0

I am currently trying to build a query which give me for a one-to-many sqlalchemy query in flask both my result filters grouped and then says how many individual entries there are for it

Following is my database model to illustrate the question:

class cellphone(db.Model):
    __tablename__ = 'cellphone'
    id = db.Column(db.Integer, primary_key = True)
    number = db.Column(db.String(30), unique=True)
    sms = db.relationship('sms_accounting',  backref="cellphone", lazy='dynamic')
    

class sms_accounting(db.Model):
    __tablename__ = 'sms_accounting'
    id = db.Column(db.Integer, primary_key = True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    cellphone_id = db.Column(db.Integer, db.ForeignKey('cellphone.id'))

What I want to do now is find out how many SMS were sent within X days per number.

Filtering and grouping I managed to do, but to calculate the sum per device correctly is not possible.

def sms_count():
    search_peroid='90' #time to fetch events in days
    period_start = datetime.utcnow() - timedelta(hours=int(search_peroid))
    phone_count = sms_accounting.query.filter(sms_accounting.timestamp.between(period_start, \
        datetime.utcnow() )).group_by(sms_accounting.cellphone_id).all()

I found some examples for func.count, but unfortunately none of them works. This already starts with the usage,

AttributeError: BaseQuery object has no attribute 'func'

even though it was imported especially.

from sqlalchemy.sql.functions import func

homecrow
  • 1
  • 4

1 Answers1

0

Forgive me if I am wrong.

As an option, you could try executing an SQL Query through Flask.

db.session.execute('select number, count(sms_accounting.id) from cellphone join sms_accounting on sms_accounting.cellphone_id = cellphone.id');

You can easily add the time based filter using where.

Regarding the AttributeError, are you sure you are using the 'func' method properly? The correct usage can be found on this unrelated answer at https://stackoverflow.com/a/4086229/4854064. It might be that you accidentally called func as a method of the query object.

Nandakishore
  • 11
  • 2