摘要：本文主要講解如何生成不同需求的報(bào)表。項(xiàng)目描述第一個(gè)頁面，以和為關(guān)聯(lián)，查詢出本月職工的關(guān)聯(lián)客戶及客戶的資產(chǎn)余額。建立上個(gè)月的虛字段。，二是利用函數(shù)來對(duì)初始化數(shù)據(jù)進(jìn)行賦值的操作。提示注意的語法。權(quán)限為的用戶只顯示本人的相關(guān)信息。

曾經(jīng)，因?yàn)閷?duì) sqlalchemy 不熟悉，固執(zhí)的拋棄之，直接引入原生的 cx_Oracle 來生成報(bào)表，但是經(jīng)過一堆的“上下文等”錯(cuò)誤后，我幡然悔悟，還是回到了 sqlalchemy 的懷抱來。
認(rèn)真學(xué)習(xí) sqlalchemy，你會(huì)發(fā)現(xiàn)意想不到的驚喜。本文主要講解如何生成不同需求的報(bào)表。

第一個(gè)頁面，以 OusiStaff.phone 和 OusiGuest.staff_phone 為關(guān)聯(lián)，查詢出本月職工的關(guān)聯(lián)客戶及客戶的資產(chǎn)余額。

第二個(gè)頁面，以 OusiStaff.phone 和 OusiGuest.staff_phone 為關(guān)聯(lián)，查詢出每個(gè)職工所有關(guān)聯(lián)的客戶的本月資產(chǎn)、上月資產(chǎn)及本月管戶資產(chǎn)增額。

具有管理員權(quán)限的員工可以看到本部門下面所有員工的相關(guān)信息，非管理員權(quán)限的員工只能查看自己的相關(guān)信息。

查詢功能留給下一章詳細(xì)講解。

不在贅述。詳情請(qǐng)下載代碼查看。

第一個(gè)頁面使用sqlalchemy查詢比較簡(jiǎn)單，關(guān)鍵點(diǎn)是：

區(qū)分權(quán)限。

聯(lián)表查詢。

建立上個(gè)月的虛字段。

分頁顯示。

查詢嵌套。

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)

其實(shí)，以上內(nèi)容就是利用 sqlalchemy 的聯(lián)表查詢問題，將 OusiStaff 和 OusiGuest 以 phone 值為關(guān)聯(lián)進(jìn)行查詢，然后將結(jié)果顯示出來。

其中的難點(diǎn)一是利用 func.to_date 和 func.add_months 對(duì)上個(gè)月這個(gè)時(shí)間進(jìn)行關(guān)聯(lián)查詢（凡是數(shù)據(jù)庫支持的函數(shù)，在 sqlalchemy 中都可以使用 func 加函數(shù)名來操作。），二是利用 func.nvl 函數(shù)來對(duì)初始化數(shù)據(jù)進(jìn)行賦值 0 的操作。 具體的 sqlalchemy 語法，請(qǐng)參見代碼。

提示： 注意 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") ，這語句的主要是為了實(shí)現(xiàn)查詢上個(gè)月的余額，如果查詢不到，通過 oracle 的 nvl 函數(shù)設(shè)置為 0 。

權(quán)限為 admin 的用戶顯示本部門下所有員工的相關(guān)信息。

權(quán)限為 general 的用戶只顯示本人的相關(guān)信息。

第二頁使用 sqlalchemy 查詢稍微復(fù)雜點(diǎn)，關(guān)鍵點(diǎn)除了第一個(gè)頁面的要求外還有：

使用 func.sum 對(duì)相關(guān)字段進(jìn)行相加運(yù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 里分組查詢的字段。

權(quán)限為 admin 的用戶顯示本部門下所有員工的相關(guān)信息。

權(quán)限為 general 的用戶只顯示本人的相關(guān)信息。

源碼下載

下節(jié)更精彩，將實(shí)現(xiàn)搜索查詢功能，又是一堆 sqlalchemy 語句。