Peewee 高阶用法

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=%s
             UNION
             SELECT 
                common_user.user_id,
                common_user.user_name,
                common_user.superior_id 
             FROM common_user,virtual_user
             WHERE 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 exist
     LINE 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）那样连表查询了。

• 以上，还会持续更新💪