当前位置:   article > 正文

sqlalchemy查询,with_entities,label的使用_sqlalchemy label

sqlalchemy label

示例:

  1. from sqlalchemy import func
  2. from sqlalchemy.orm import aliased
  3. def get_data(request_data):
  4. request_data = request_data if request_data else {}
  5. dep = request_data.get('DEP', '')
  6. arr = request_data.get('ARR', '')
  7. AT = request_data.get('TYPE', '')
  8. page_num = request_data.get('PAGE_NUM', None)
  9. page_index = request_data.get('PAGE_INDEX', None)
  10. m = aliased(Table1, name='a')
  11. f = aliased(Table2, name='b')
  12. a1 = aliased(Table3, name='a1')
  13. a2 = aliased(Table3, name='a2')
  14. m_query = db.session.query(f)
  15. query = m_query.join(m, m.ID == f.ID) \
  16. .join(a1, m.DEP == a1.ACODE)\
  17. .join(a2, m.ARR == a2.ACODE)\
  18. .add_entity(f)
  19. if dep:
  20. query = query.filter(m.DEP == dep)
  21. if arr:
  22. query = query.filter(m.ARR == arr)
  23. if AT:
  24. ATs = AT.split(',')
  25. if ATs:
  26. query = query.filter(f.AT.in_(ATs))
  27. # field_list = Table1.get_aliased_fields(m)
  28. # field_list.append(a1.CITY.label('DCITY'))
  29. # field_list.append(a2.CITY.label('ACITY'))
  30. # query = query.with_entities(
  31. # *field_list
  32. # ).order_by(m.DEP.asc(), m.ARR.asc(),
  33. m.NAME.asc(), f.AT.asc())
  34. query = query.with_entities(
  35. *Table1.__mapper__.attrs,
  36. func.concat(m.DEP, '-', m.ARR).label('AL'),
  37. (a1.CT + '-' + a2.CT).label('AL_NAME'),
  38. a1.CT.label('DEP_CT'),
  39. a2.CT.label('ARR_CT'),
  40. *Table2.__mapper__.attrs
  41. ).order_by(m.DEP.asc(), m.ARR.asc(),
  42. m.NAME.asc(), f.AT.asc())
  43. if page_num:
  44. if (not page_index) or (page_index <= 0):
  45. page_index = 1
  46. pagination = query.paginate(page_index, per_page=page_num, error_out=False)
  47. data = [dict(zip(item._fields, item)) for item in pagination.items]
  48. return dict(items=data, page=pagination.page,
  49. pages=pagination.pages, total=pagination.total)
  50. else:
  51. result = query.all()
  52. data = [dict(zip(item._fields, item)) for item in result]
  53. return dict(items=data, page=1, pages=1, total=len(data))

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小蓝xlanll/article/detail/510773
推荐阅读
相关标签
  

闽ICP备14008679号