曾经,因为对 sqlalchemy 不熟悉,固执的抛弃之,直接引入原生的 cx_Oracle 来生成报表,但是经过一堆的“上下文等”错误后,我幡然悔悟,还是回到了 sqlalchemy 的怀抱来。
认真学习 sqlalchemy,你会发现意想不到的惊喜。本文主要讲解如何生成不同需求的报表。
项目描述
- 第一个页面,以 OusiStaff.phone 和 OusiGuest.staff_phone 为关联,查询出本月职工的关联客户及客户的资产余额。
- 第二个页面,以 OusiStaff.phone 和 OusiGuest.staff_phone 为关联,查询出每个职工所有关联的客户的本月资产、上月资产及本月管户资产增额。
- 具有管理员权限的员工可以看到本部门下面所有员工的相关信息,非管理员权限的员工只能查看自己的相关信息。
- 查询功能留给下一章详细讲解。
建立报表模板
不在赘述。详情请下载代码查看。
实现第一个页面
建立第一个页面的试图函数
views.py
内容:
@show.route('/01',methods = ['GET','POST']) @login_required def _01(): g1 = aliased(OusiGuest) # 重复用到该数据库,需要设置别名。 g2 = aliased(OusiGuest) # 重复用到该数据库,需要设置别名。 page = request.args.get('page',1,type=int) # 分页 if current_user.role == 'admin': # 区分权限 database = db.session.query(OusiStaff.department,OusiStaff.name.label('staff_name'),OusiStaff.phone,OusiStaff.role,g1.name.label('guest_name'),g1.month,g1.balance,func.nvl(db.session.query(g2.balance).filter( g1.name == g2.name,func.to_date(g2.month,'yyyy-mm') == func.add_months( func.to_date(g1.month,'yyyy-mm'),-1) ),0).label('last_balance') ).filter( OusiStaff.phone == g1.staff_phone,current_user.department == OusiStaff.department,g1.month == date.today().strftime('%Y-%m') ).order_by(g1.name).group_by(OusiStaff.department,g1.balance) else: database = db.session.query(OusiStaff.department,func.nvl(db.session.query(g2.balance).filter( g1.name==g2.name,'yyyy-mm')==func.add_months( func.to_date(g1.month,0).label('last_balance') ).filter( OusiStaff.phone==g1.staff_phone,current_user.phone==g1.staff_phone,g1.month==date.today().strftime('%Y-%m') ).order_by(g1.name).group_by(OusiStaff.department,g1.balance) data = database.paginate(page,per_page=current_app.config['OUSI_POSTS_PER_PAGE'],error_out=False) return render_template('show/01.html',data=data)
其实,以上内容就是利用 sqlalchemy 的联表查询问题,将 OusiStaff 和 OusiGuest 以 phone 值为关联进行查询,然后将结果显示出来。
其中的难点一是利用 func.to_date
和 func.add_months
对上个月这个时间进行关联查询(凡是数据库支持的函数,在 sqlalchemy 中都可以使用 func 加函数名来操作。),二是利用 func.nvl
函数来对初始化数据进行赋值 0 的操作。 具体的 sqlalchemy 语法,请参见代码。
提示:
注意 sqlalchemy 的语法。func.nvl(db.session.query(g2.balance).filter(g1.name==g2.name,'yyyy-mm')==func.add_months(func.to_date(g1.month,-1)),0).label('last_balance')
,这语句的主要是为了实现查询上个月的余额,如果查询不到,通过 oracle 的 nvl 函数设置为 0 。
第一个页面显示结果
@H_301_71@
@H_301_71@
实现第二个页面
建立第二个页面的试图函数
第二页使用 sqlalchemy 查询稍微复杂点,关键点除了第一个页面的要求外还有:
- 使用 func.sum 对相关字段进行相加运算。
- 用到了子查询。
views.py
内容:
@show.route('/02','POST']) @login_required def _02(): page = request.args.get('page',type=int) g1 = aliased(OusiGuest) g2 = aliased(OusiGuest) if current_user.role == 'admin': sbq = db.session.query(OusiStaff.department,g1.staff_phone,func.nvl(db.session.query(g2.balance).filter( g1.name==g2.name,func.add_months(func.to_date(g1.month,-1) == func.to_date(g2.month,'yyyy-mm') ),0).label('last_balance')).filter( OusiStaff.phone==g1.staff_phone,OusiStaff.department==current_user.department ).group_by(OusiStaff.department,g1.balance).subquery() database = db.session.query(sbq.c.department,sbq.c.role,sbq.c.staff_name,sbq.c.staff_phone,sbq.c.month,func.count(sbq.c.guest_name).label('members'),func.sum(sbq.c.balance).label('balance'),func.sum(sbq.c.last_balance).label('last_balance')).\ filter(sbq.c.month==date.today().strftime('%Y-%m')).group_by(sbq.c.department,sbq.c.month) else: sbq = db.session.query(OusiStaff.department,func.nvl(db.session.query(g2.balance).filter( g1.name == g2.name,'yyyy-mm') ),0).label('last_balance')).filter( OusiStaff.phone == g1.staff_phone,OusiStaff.phone == current_user.phone ).group_by(OusiStaff.department,func.sum(sbq.c.last_balance).label('last_balance')). \ filter(sbq.c.month == date.today().strftime('%Y-%m')).group_by(sbq.c.department,sbq.c.month) data = database.paginate(page,error_out=False) return render_template('show/02.html',data=data)
注意 query 的字段以及 group_by 里分组查询的字段。
第二个页面显示结果
@H_301_71@
@H_301_71@