曾經(jīng)，因為對 sqlalchemy 不熟悉，固執(zhí)的拋棄之，直接引入原生的 cx_Oracle 來生成報表，但是經(jīng)過一堆的“上下文等”錯誤后，我幡然悔悟，還是回到了 sqlalchemy 的懷抱來。
認(rèn)真學(xué)習(xí) sqlalchemy，你會發(fā)現(xiàn)意想不到的驚喜。本文主要講解如何生成不同需求的報表。
項目描述
- 第一個頁面，以 OusiStaff.phone 和 OusiGuest.staff_phone 為關(guān)聯(lián)，查詢出本月職工的關(guān)聯(lián)客戶及客戶的資產(chǎn)余額。
- 第二個頁面，以 OusiStaff.phone 和 OusiGuest.staff_phone 為關(guān)聯(lián)，查詢出每個職工所有關(guān)聯(lián)的客戶的本月資產(chǎn)、上月資產(chǎn)及本月管戶資產(chǎn)增額。
- 具有管理員權(quán)限的員工可以看到本部門下面所有員工的相關(guān)信息，非管理員權(quán)限的員工只能查看自己的相關(guān)信息。
- 查詢功能留給下一章詳細(xì)講解。
建立報表模板
不在贅述。詳情請下載代碼查看。
實現(xiàn)第一個頁面
建立第一個頁面的試圖函數(shù)
第一個頁面使用sqlalchemy查詢比較簡單，關(guān)鍵點是：
- 區(qū)分權(quán)限。
- 聯(lián)表查詢。
- 建立上個月的虛字段。
- 分頁顯示。
- 查詢嵌套。
views.py 內(nèi)容：
@show.route('/01', methods = ['GET', 'POST'])
@login_required
def _01():
g1 = aliased(OusiGuest) # 重復(fù)用到該數(shù)據(jù)庫，需要設(shè)置別名。
g2 = aliased(OusiGuest) # 重復(fù)用到該數(shù)據(jù)庫，需要設(shè)置別名。
page = request.args.get('page', 1, type=int) # 分頁
if current_user.role == 'admin': # 區(qū)分權(quán)限
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, OusiStaff.name.label('staff_name'), OusiStaff.phone,
OusiStaff.role,
g1.name.label('guest_name'), g1.month, g1.balance)
else:
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.phone==g1.staff_phone,
g1.month==date.today().strftime('%Y-%m')
).order_by(g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone,
OusiStaff.role,
g1.name.label('guest_name'), g1.month, 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)
其實，以上內(nèi)容就是利用 sqlalchemy 的聯(lián)表查詢問題，將 OusiStaff 和 OusiGuest 以 phone 值為關(guān)聯(lián)進(jìn)行查詢，然后將結(jié)果顯示出來。
其中的難點一是利用 func.to_date 和 func.add_months 對上個月這個時間進(jìn)行關(guān)聯(lián)查詢（凡是數(shù)據(jù)庫支持的函數(shù)，在 sqlalchemy 中都可以使用 func 加函數(shù)名來操作。），二是利用 func.nvl 函數(shù)來對初始化數(shù)據(jù)進(jìn)行賦值 0 的操作。 具體的 sqlalchemy 語法，請參見代碼。
提示：注意 sqlalchemy 的語法。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')，這語句的主要是為了實現(xiàn)查詢上個月的余額，如果查詢不到，通過 oracle 的 nvl 函數(shù)設(shè)置為 0 。
第一個頁面顯示結(jié)果
權(quán)限為 admin 的用戶顯示本部門下所有員工的相關(guān)信息。
012.png
權(quán)限為 general 的用戶只顯示本人的相關(guān)信息。
013.png
實現(xiàn)第二個頁面
建立第二個頁面的試圖函數(shù)
第二頁使用 sqlalchemy 查詢稍微復(fù)雜點，關(guān)鍵點除了第一個頁面的要求外還有：
- 使用 func.sum 對相關(guān)字段進(jìn)行相加運算。
- 用到了子查詢。
views.py 內(nèi)容：
@show.route('/02', methods = ['GET', 'POST'])
@login_required
def _02():
page = request.args.get('page', 1, type=int)
g1 = aliased(OusiGuest)
g2 = aliased(OusiGuest)
if current_user.role == 'admin':
sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,
func.nvl(db.session.query(g2.balance).filter(
g1.name==g2.name,
func.add_months(func.to_date(g1.month, 'yyyy-mm'), -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, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, 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.role,
sbq.c.staff_name, sbq.c.staff_phone,
sbq.c.month)
else:
sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,
func.nvl(db.session.query(g2.balance).filter(
g1.name == g2.name,
func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month,
'yyyy-mm')
), 0).label('last_balance')).filter(
OusiStaff.phone == g1.staff_phone,
OusiStaff.phone == current_user.phone
).group_by(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, 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.role,
sbq.c.staff_name, sbq.c.staff_phone,
sbq.c.month)
data = database.paginate(page, per_page=current_app.config['OUSI_POSTS_PER_PAGE'], error_out=False)
return render_template('show/02.html', data=data)
注意 query 的字段以及 group_by 里分組查詢的字段。
第二個頁面顯示結(jié)果
權(quán)限為 admin 的用戶顯示本部門下所有員工的相關(guān)信息。
014.png
權(quán)限為 general 的用戶只顯示本人的相關(guān)信息。
015.png
下節(jié)更精彩，將實現(xiàn)搜索查詢功能，又是一堆 sqlalchemy 語句。
