Peewee 高階用法

• 前言

  本文介紹的Peewee方法語法基于PostgreSQL

• 高階用法

  • 元組多條件查詢

    from peewee import Tuple
    e.g.: 
    1. model.select().where(Tuple(model.id, model.name) == (1, '胖胖'))
    2. model.select().where(Tuple(model.id, model.name).in_([(1, '胖胖1號'), (2, '胖胖2號')])
    

  • 簡單Case函數

    from peewee import Case Number = Table('numbers', ('val',))num_as_str = Case(Number.val, (  # case_ value(1, 'one'),  # （when_value1，結果1）(2, 'two'),  # （when_value2，結果2）(3, 'three')  # （when_value3，結果3）), 'a lot')  # else后的結果query = Number.select(Number.val, num_as_str.alias('num_str'))The above is equivalent to:
    SELECT "val",
    CASE "val"
    WHEN 1 THEN 'one'
    WHEN 2 THEN 'two'
    WHEN 3 THEN 'three'
    ELSE 'a lot' END AS "num_str"
    FROM "numbers"
    

  • Case搜索函數

    from peewee import Case Number = Table('numbers', ('val',))
    num_as_str = Case(None, (  # 用Case進行搜時，case_value為空，when中處理完整條件(Number.val == 1, 'one'),  # (case1，結果1)(Number.val == 2, 'two'),  # (case2，結果2)(Number.val == 3, 'three') # (case3，結果3)), 'a lot')  # else后的結果
    query = Number.select(Number.val, num_as_str.alias('num_str'))The above is equivalent to:
    SELECT "val",
    CASE
    WHEN "val" = 1 THEN 'one'
    WHEN "val" = 2 THEN 'two'
    WHEN "val" = 3 THEN 'three'
    ELSE 'a lot' END AS "num_str"
    FROM "numbers"
    

  • 子查詢
    官網實例

    rank = fn.rank().over(order_by=[fn.SUM(Booking.slots).desc()])  # rank() over(order_by(sum(booking.slots desc)))subq = (Booking.select(Booking.facility, fn.SUM(Booking.slots).alias('total'),rank.alias('rank')).group_by(Booking.facility))
    # Here we use a plain Select() to create our query.
    query = (Select(columns=[subq.c.facid, subq.c.total])  # columns: 查詢出指定的字段（對查詢結果集的字段進行操作時，需要在結果集后加上.c再.屬性）.from_(subq)  # 規定用法.where(subq.c.rank == 1)  # 查詢結果集.c.字段屬性.bind(db))  # We must bind() itto the database.# To iterate over the query results:
    for facid, total in query.tuples():  # tuples()可以按照select查詢順序，將每條查詢結果處理成元組，并將數據整合成一個可迭代類型數據print(facid, total)
    

  • 遞歸查詢（WITH RECURSIVE）
    官方文檔實例

    base = (CommonUser.select(CommonUser.user_id).where(CommonUser.user_id == user_id).cte("common_users", recursive=True, columns=("user_id", 'user_name', 'superior_id')))
    cu = CommonUser.alias("cu")
    recursive = (cu.select(cu.user_id).join(base, on=(base.c.user_id == cu.superior_id)))
    cte = base.union_all(recursive)
    query = (cte.select_from(cte.c.user_id).order_by(cte.c.user_id))
    # 這段orm實現了以下sql功能
    WITH RECURSIVE virtual_user (user_id,user_name,superior_id) AS (SELECT user_id,user_name,superior_id FROM common_user WHERE user_id=%sUNIONSELECT common_user.user_id,common_user.user_name,common_user.superior_id FROM common_user,virtual_userWHERE virtual_user.user_id=common_user.superior_id AND common_user.code_status=1)
    SELECT * FROM virtual_user WHERE user_name like %s ORDER BY convert_to(user_name,'GBK') ASC;
    

    那么在我們項目中的應用呢？

    按上面的例子，我們能得出來一些user_id。然后可以將這些user_ids作為查詢條件，比如: Book.select().where(Book.author_id.in_(user_ids))，這樣使用當然是可以的。但是在sql的查詢中，in作為條件的查詢效率是極其低效的，尤其當in中的元素很多時。
    這時就應該想到用join來解決問題，于是base = (CommonUser.select(CommonUser.user_id,CommonUser.user_name,CommonUser.superior_id).where(CommonUser.user_id == 26).cte("common_users", recursive=True, columns=("user_id", "user_name", "superior_id")))
    cu = CommonUser.alias("cu")
    recursive = (cu.select(cu.user_id,cu.user_name,cu.superior_id).join(base, on=(base.c.user_id == cu.superior_id)))
    cte = base.union_all(recursive)
    employees = (GcEmployee.select(GcEmployee.id, GcEmployee.name)# .with_cte(cte).join(cte, on=(GcEmployee.relation_user_id == cte.c.user_id)))
    運行一下，結果會是什么呢？見下👇
    

     psycopg2.errors.UndefinedTable: relation "common_users" does not existLINE 1: ..."t1"."name" FROM "gc_employee" AS "t1" INNER JOIN "common_us...^
    

    為什么會是這樣的結果呢。我理解的是，首先我們知道拋出來的錯誤中找不到的common_user它正是我們用with語句查詢出來的虛擬表，我們在代碼中脫離with直接用GcEmployee去連接虛擬的common_user當然會說找不到此表。解決方法是什么呢，細心的同學應該發現了注釋掉的代碼 .with_cte，正是這個方法，可以讓common_user存活在with里，所以我們就可以像常用的語法（舉的第一個例子中的sql）那樣連表查詢了。

• 以上，還會持續更新💪

總結

以上是生活随笔為你收集整理的Peewee 高阶用法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。