【Python百日进阶-Web开发-Peewee】Day257 - Peewee过滤、排序、分页、计数、汇总记录

8.9 过滤记录

您可以使用普通的 python 运算符过滤特定记录。Peewee 支持多种查询运算符。

>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
...     print(tweet.user.username, '->', tweet.message)
...
Charlie -> hello world
Charlie -> this is fun

>>> for tweet in Tweet.select().where(Tweet.created_date < datetime.datetime(2011, 1, 1)):
...     print(tweet.message, tweet.created_date)
...
Really old tweet 2010-01-01 00:00:00

您还可以过滤连接：

>>> for tweet in Tweet.select().join(User).where(User.username == 'Charlie'):
...     print(tweet.message)
hello world
this is fun
look at this picture of my food

如果你想表达一个复杂的查询，请使用括号和 python 的按位 或和和运算符：

>>> Tweet.select().join(User).where(
...     (User.username == 'Charlie') |
...     (User.username == 'Peewee Herman'))

笔记

请注意，Peewee 使用按位运算符 ( &and |) 而不是逻辑运算符 ( andand or)。原因是 Python
将逻辑运算的返回值强制为布尔值。这也是为什么“IN”查询必须使用.in_()而不是in运算符来表示的原因。

查看查询操作表以了解可能的查询类型。

笔记

查询的 where 子句中可以包含很多有趣的东西，例如：

• 字段表达式，例如User.username == ‘Charlie’
• 函数表达式，例如fn.Lower(fn.Substr(User.username, 1, 1)) == ‘a’
• 一列与另一列的比较，例如Employee.salary < (Employee.tenure * 1000) + 40000

您还可以嵌套查询，例如用户名以“a”开头的用户的推文：

# get users whose username starts with "a"
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

# the ".in_()" method signifies an "IN" query
a_user_tweets = Tweet.select().where(Tweet.user.in_(a_users))

8.9.1 更多查询示例

笔记

有关范围广泛的示例查询，请参阅查询示例 文档，该文档展示了如何从PostgreSQL 练习 网站实现查询。

获取活跃用户：

User.select().where(User.active == True)

获取员工或超级用户的用户：

User.select().where(
    (User.is_staff == True) | (User.is_superuser == True))

获取名为“charlie”的用户的推文：

Tweet.select().join(User).where(User.username == 'charlie')

获取员工或超级用户的推文（假设 FK 关系）：

Tweet.select().join(User).where(
    (User.is_staff == True) | (User.is_superuser == True))

使用子查询获取员工或超级用户的推文：

staff_super = User.select(User.id).where(
    (User.is_staff == True) | (User.is_superuser == True))
Tweet.select().where(Tweet.user.in_(staff_super))

8.10 排序记录

要按顺序返回行，请使用以下order_by()方法：

>>> for t in Tweet.select().order_by(Tweet.created_date):
...     print(t.pub_date)
...
2010-01-01 00:00:00
2011-06-07 14:08:48
2011-06-07 14:12:57

>>> for t in Tweet.select().order_by(Tweet.created_date.desc()):
...     print(t.pub_date)
...
2011-06-07 14:12:57
2011-06-07 14:08:48
2010-01-01 00:00:00

您还可以使用+和-前缀运算符来指示排序：

# The following queries are equivalent:
Tweet.select().order_by(Tweet.created_date.desc())

Tweet.select().order_by(-Tweet.created_date)  # Note the "-" prefix.

# Similarly you can use "+" to indicate ascending order, though ascending
# is the default when no ordering is otherwise specified.
User.select().order_by(+User.username)

您还可以跨连接订购。假设您想按作者的用户名排序推文，然后按 created_date：

query = (Tweet
         .select()
         .join(User)
         .order_by(User.username, Tweet.created_date.desc()))

SELECT t1."id", t1."user_id", t1."message", t1."is_published", t1."created_date"
FROM "tweet" AS t1
INNER JOIN "user" AS t2
  ON t1."user_id" = t2."id"
ORDER BY t2."username", t1."created_date" DESC

在对计算值进行排序时，您可以包含必要的 SQL 表达式，或引用分配给该值的别名。以下是说明这些方法的两个示例：

# Let's start with our base query. We want to get all usernames and the number of
# tweets they've made. We wish to sort this list from users with most tweets to
# users with fewest tweets.
query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username))

您可以使用select子句中使用的相同 COUNT 表达式进行排序。在下面的示例中，我们按COUNT()推文 ID 降序排序：

query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(fn.COUNT(Tweet.id).desc()))

或者，您可以引用分配给select子句中计算值的别名。这种方法的好处是更容易阅读。请注意，我们不是直接引用命名别名，而是使用SQL帮助器包装它：

query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(SQL('num_tweets').desc()))

或者，以“peewee”的方式做事：

ntweets = fn.COUNT(Tweet.id)
query = (User
         .select(User.username, ntweets.alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(ntweets.desc())

8.11 获取随机记录

有时您可能想从数据库中提取随机记录。您可以通过random或rand函数（取决于您的数据库）排序来完成此操作：

Postgresql 和 Sqlite 使用Random函数：

# Pick 5 lucky winners:
LotteryNumber.select().order_by(fn.Random()).limit(5)

MySQL 使用Rand：

# Pick 5 lucky winners:
LotteryNumber.select().order_by(fn.Rand()).limit(5)

8.12 分页记录

该paginate()方法使抓取页面或记录变得容易。paginate()接受两个参数 page_number， 和items_per_page。

注意力

页码从 1 开始，因此结果的第一页将是第 1 页。

>>> for tweet in Tweet.select().order_by(Tweet.id).paginate(2, 10):
...     print(tweet.message)
...
tweet 10
tweet 11
tweet 12
tweet 13
tweet 14
tweet 15
tweet 16
tweet 17
tweet 18
tweet 19

如果您想要更精细的控制，您始终可以使用 limit()和offset()。

8.13 计数记录

您可以计算任何选择查询中的行数：

>>> Tweet.select().count()
100
>>> Tweet.select().where(Tweet.id > 50).count()
50

Peewee 会将您的查询包装在执行计数的外部查询中，这会产生如下 SQL：

SELECT COUNT(1) FROM ( ... your query ... );

8.14 汇总记录

假设您有一些用户，并且想要获取他们的列表以及每个用户的推文数量。

query = (User
         .select(User, fn.Count(Tweet.id).alias('count'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User))

生成的查询将返回User对象及其所有正常属性以及一个额外的属性计数，该属性计数将包含每个用户的推文计数。我们使用左外连接来包含没有推文的用户。

假设您有一个标记应用程序，并且想要查找具有一定数量相关对象的标记。对于此示例，我们将在多对多配置中使用一些不同的模型：

class Photo(Model):
    image = CharField()

class Tag(Model):
    name = CharField()

class PhotoTag(Model):
    photo = ForeignKeyField(Photo)
    tag = ForeignKeyField(Tag)

现在假设我们要查找至少有 5 张照片与之关联的标签：

query = (Tag
         .select()
         .join(PhotoTag)
         .join(Photo)
         .group_by(Tag)
         .having(fn.Count(Photo.id) > 5))

此查询等效于以下 SQL：

SELECT t1."id", t1."name"
FROM "tag" AS t1
INNER JOIN "phototag" AS t2 ON t1."id" = t2."tag_id"
INNER JOIN "photo" AS t3 ON t2."photo_id" = t3."id"
GROUP BY t1."id", t1."name"
HAVING Count(t3."id") > 5

假设我们要获取关联的计数并将其存储在标签上：

query = (Tag
         .select(Tag, fn.Count(Photo.id).alias('count'))
         .join(PhotoTag)
         .join(Photo)
         .group_by(Tag)
         .having(fn.Count(Photo.id) > 5))