CODEWITHSUNDEEP
pythonjsonpython-2.7sqlalchemy
ohpaulsen
ohpaulsen

Reputation: 23

grouping / order by in sqlalchemy query

This group by / order by wont work, the point is to count HTTP.statusline on minute and then return it. Now it return multiple time of minute. In the database the date is saved as String.

Example on output:

{u'date': u'2013-03-12 11:23:48', u'count': 21}

but something is returning more then one value like this:

{u'date': u'2013-03-12 11:36:21', u'count': 2}, {u'date': u'2013-03-12 11:36:21', u'count': 8}]

Code:

def statusline_date(status):
    session = load_session()
    now = datetime.datetime.now()
    DD = now - datetime.timedelta(minutes=60)
    DD = DD.strftime('%Y-%m-%d %H:%M:%S')
    #query = session.query(HTTP.date, extract('minute', HTTP.date).label('min'), HTTP.statusline, func.count(HTTP.statusline).
    #        label('count')).filter(HTTP.statusline.like('%'+status+'%'), HTTP.date>=(DD+'%')).group_by('min').all()
    query = session.query(HTTP.date, HTTP.statusline, func.count(HTTP.statusline).
            label('count')).filter(HTTP.statusline.like('%'+status+'%'), HTTP.date>=(DD)).group_by(HTTP.date, HTTP.statusline).order_by(asc(HTTP.date)).all()

    result = [{'date': t.date, 'count': t.count} for t in query]

    return result

Upvotes: 2

Views: 2266

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123350

You are grouping on both the date and the status line; most likely the status line differs between the two results.

Either include that status line in your output, or remove it from the group and output columns altogether.

Including the status line:

result = [{'date': t.date, 'count': t.count, 'status': t.statusline} for t in query]

would show what differs.

Removing the column from the group and columns list:

query = session.query(HTTP.date, func.count(HTTP.statusline).
        label('count')).filter(HTTP.statusline.like('%'+status+'%'), HTTP.date>=(DD)).group_by(HTTP.date).order_by(asc(HTTP.date)).all()

Instead of .like('%'+status+'%'), you could also use .contains(status); it is usually implemented as a LIKE but you don't have to do the % concatenation yourself.

Upvotes: 4

Related Questions