Python sqlalchemy.sql 模块，extract() 实例源码

def show_stats():
    dow = extract('dow', Message.timestamp)
    stats = {
        i: session.query(Message).filter(dow == i).count() for i in range(7)
    }

    return render_template('stats.html')

def yearly_stats():
    day = datetime.datetime.utcnow().date()
    # FIXME: Use dateutil.relativedelta or something
    last_year = day + datetime.timedelta(-365)

    doy = extract('doy', Message.timestamp)
    messages = session.query(Message.timestamp, func.count(doy))\
                      .filter(last_year < Message.timestamp).group_by(doy)

    response = {date.strftime('%Y-%m-%d'): count for date, count in messages.all()}
    return jsonify(response)


# Helpers ####