sqlalchemy查询,with_entities,label的使用

示例：

from sqlalchemy import func
from sqlalchemy.orm import aliased

def get_data(request_data):
    request_data = request_data if request_data else {}
    dep = request_data.get('DEP', '')
    arr = request_data.get('ARR', '')
    AT = request_data.get('TYPE', '')
    page_num = request_data.get('PAGE_NUM', None)
    page_index = request_data.get('PAGE_INDEX', None)

    m = aliased(Table1, name='a')
    f = aliased(Table2, name='b')
    a1 = aliased(Table3, name='a1')
    a2 = aliased(Table3, name='a2')
    m_query = db.session.query(f)
    query = m_query.join(m, m.ID == f.ID) \
        .join(a1, m.DEP == a1.ACODE)\
        .join(a2, m.ARR == a2.ACODE)\
        .add_entity(f)
    if dep:
        query = query.filter(m.DEP == dep)
    if arr:
        query = query.filter(m.ARR == arr)
    if AT:
        ATs = AT.split(',')
        if ATs:
            query = query.filter(f.AT.in_(ATs))

    # field_list = Table1.get_aliased_fields(m)
    # field_list.append(a1.CITY.label('DCITY'))
    # field_list.append(a2.CITY.label('ACITY'))
    # query = query.with_entities(
    #        *field_list
    #    ).order_by(m.DEP.asc(), m.ARR.asc(),
               m.NAME.asc(), f.AT.asc())

    query = query.with_entities(
        *Table1.__mapper__.attrs,
        func.concat(m.DEP, '-', m.ARR).label('AL'),
        (a1.CT + '-' + a2.CT).label('AL_NAME'),
        a1.CT.label('DEP_CT'),
        a2.CT.label('ARR_CT'),
        *Table2.__mapper__.attrs
    ).order_by(m.DEP.asc(), m.ARR.asc(),
               m.NAME.asc(), f.AT.asc())

    if page_num:
        if (not page_index) or (page_index <= 0):
            page_index = 1
        pagination = query.paginate(page_index, per_page=page_num, error_out=False)
        data = [dict(zip(item._fields, item)) for item in pagination.items]
        return dict(items=data, page=pagination.page,
                    pages=pagination.pages, total=pagination.total)
    else:
        result = query.all()
        data = [dict(zip(item._fields, item)) for item in result]
        return dict(items=data, page=1, pages=1, total=len(data))