【Python百日进阶-Web开发-Peewee】Day292 - 查询示例（一）基本练习

十四 查询示例

http://docs.peewee-orm.com/en/latest/peewee/query_examples.html
这些查询示例取自站点PostgreSQL 练习。可以在入门页面上找到示例数据集。

以下是这些示例中使用的架构的直观表示：

14.1 模型定义 Model Definitions

要开始处理数据，我们将定义与图中的表对应的模型类。

笔记
在某些情况下，我们明确指定特定字段的列名。这是为了让我们的模型与用于 postgres 练习的数据库模式兼容。
In some cases we explicitly specify column names for a particular field. This is so our models are compatible with the database schema used for the postgres exercises.

from functools import partial
from peewee import *


db = PostgresqlDatabase('peewee_test')

class BaseModel(Model):
    class Meta:
        database = db

class Member(BaseModel):
    memid = AutoField()  # Auto-incrementing primary key.
    surname = CharField()
    firstname = CharField()
    address = CharField(max_length=300)
    zipcode = IntegerField()
    telephone = CharField()
    recommendedby = ForeignKeyField('self', backref='recommended',
                                    column_name='recommendedby', null=True)
    joindate = DateTimeField()

    class Meta:
        table_name = 'members'


# Conveniently declare decimal fields suitable for storing currency.
MoneyField = partial(DecimalField, decimal_places=2)


class Facility(BaseModel):
    facid = AutoField()
    name = CharField()
    membercost = MoneyField()
    guestcost = MoneyField()
    initialoutlay = MoneyField()
    monthlymaintenance = MoneyField()

    class Meta:
        table_name = 'facilities'


class Booking(BaseModel):
    bookid = AutoField()
    facility = ForeignKeyField(Facility, column_name='facid')
    member = ForeignKeyField(Member, column_name='memid')
    starttime = DateTimeField()
    slots = IntegerField()

    class Meta:
        table_name = 'bookings'

14.2 模式创建 Schema Creation

如果您从 PostgreSQL 练习站点下载了 SQL 文件，则可以使用以下命令将数据加载到 PostgreSQL 数据库中：
If you downloaded the SQL file from the PostgreSQL Exercises site, then you can load the data into a PostgreSQL database using the following commands:

createdb peewee_test
psql -U postgres -f clubdata.sql -d peewee_test -x -q

要使用 Peewee 创建模式，而不加载示例数据，您可以运行以下命令：

# Assumes you have created the database "peewee_test" already.
db.create_tables([Member, Facility, Booking])

14.3 基本练习 Basic Exercises

本类别涉及 SQL 的基础知识。它涵盖了 select 和 where 子句、case 表达式、联合和其他一些零碎的东西。
This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends.

14.3.1 检索一切 Retrieve everything

从设施表中检索所有信息。

SELECT * FROM facilities

# By default, when no fields are explicitly passed to select(), all fields
# will be selected.
query = Facility.select()

14.3.2 从表中检索特定列 Retrieve specific columns from a table

向成员检索设施名称和费用。

SELECT name, membercost FROM facilities;

query = Facility.select(Facility.name, Facility.membercost)

# To iterate:
for facility in query:
    print(facility.name)

14.3.3 控制检索哪些行 Control which rows are retrieved

检索对成员有费用的设施列表。

SELECT * FROM facilities WHERE membercost > 0

query = Facility.select().where(Facility.membercost > 0)

14.3.4 控制检索哪些行 - 第 2 部分 Control which rows are retrieved - part 2

检索对成员有费用的设施列表，该费用低于每月维护费用的 1/50。返回 ID、名称、成本和每月维护。
Retrieve list of facilities that have a cost to members, and that fee is less than 1/50th of the monthly maintenance cost. Return id, name, cost and monthly-maintenance.

SELECT facid, name, membercost, monthlymaintenance
FROM facilities
WHERE membercost > 0 AND membercost < (monthlymaintenance / 50)

query = (Facility
         .select(Facility.facid, Facility.name, Facility.membercost,
                 Facility.monthlymaintenance)
         .where(
             (Facility.membercost > 0) &
             (Facility.membercost < (Facility.monthlymaintenance / 50))))

14.3.5 基本字符串搜索 Basic string searches

您如何制作名称中带有“网球”一词的所有设施的列表？

SELECT * FROM facilities WHERE name ILIKE '%tennis%';

query = Facility.select().where(Facility.name.contains('tennis'))

# OR use the exponent operator. Note: you must include wildcards here:
query = Facility.select().where(Facility.name ** '%tennis%')

14.3.6 匹配多个可能的值 Matching against multiple possible values

如何检索 ID 为 1 和 5 的设施的详细信息？尝试在不使用 OR 运算符的情况下执行此操作。

SELECT * FROM facilities WHERE facid IN (1, 5);

query = Facility.select().where(Facility.facid.in_([1, 5]))

# OR:
query = Facility.select().where((Facility.facid == 1) |
                                (Facility.facid == 5))

14.3.7 将结果分类到桶中 Classify results into buckets

您如何制作一份设施清单，根据每月的维护成本是否超过 100 美元，将每个设施标记为“便宜”或“昂贵”？返回相关设施的名称和每月维护。
How can you produce a list of facilities, with each labelled as ‘cheap’ or ‘expensive’ depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.

SELECT name,
CASE WHEN monthlymaintenance > 100 THEN 'expensive' ELSE 'cheap' END
FROM facilities;

cost = Case(None, [(Facility.monthlymaintenance > 100, 'expensive')], 'cheap')
query = Facility.select(Facility.name, cost.alias('cost'))

笔记
有关更多示例，请参阅文档Case。

14.3.8 使用日期 Working with dates

您如何生成 2012 年 9 月开始之后加入的成员列表？返回相关成员的姓名、姓氏、名字和加入日期。
How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.

SELECT memid, surname, firstname, joindate FROM members
WHERE joindate >= '2012-09-01';

query = (Member
         .select(Member.memid, Member.surname, Member.firstname, Member.joindate)
         .where(Member.joindate >= datetime.date(2012, 9, 1)))

14.3.9 删除重复项和排序结果 Removing duplicates, and ordering results

如何生成成员表中前 10 个姓氏的有序列表？该列表不得包含重复项。
How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

SELECT DISTINCT surname FROM members ORDER BY surname LIMIT 10;

query = (Member
         .select(Member.surname)
         .order_by(Member.surname)
         .limit(10)
         .distinct())

14.3.10 组合来自多个查询的结果 Combining results from multiple queries

出于某种原因，您需要所有姓氏和所有设施名称的组合列表。
You, for some reason, want a combined list of all surnames and all facility names.

SELECT surname FROM members UNION SELECT name FROM facilities;

lhs = Member.select(Member.surname)
rhs = Facility.select(Facility.name)
query = lhs | rhs

可以使用以下运算符组成查询：

• |-UNION
• ±UNION ALL
• &-INTERSECT
• –EXCEPT

14.3.11 简单聚合 Simple aggregation

您想获得最后一个成员的注册日期。您如何检索这些信息？
You’d like to get the signup date of your last member. How can you retrieve this information?

SELECT MAX(join_date) FROM members;

query = Member.select(fn.MAX(Member.joindate))
# To conveniently obtain a single scalar value, use "scalar()":
# max_join_date = query.scalar()

14.3.12 更多聚合 More aggregation

您想获取最后注册成员的名字和姓氏 - 而不仅仅是日期。
You’d like to get the first and last name of the last member(s) who signed up - not just the date.

SELECT firstname, surname, joindate FROM members
WHERE joindate = (SELECT MAX(joindate) FROM members);

# Use "alias()" to reference the same table multiple times in a query.
MemberAlias = Member.alias()
subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
query = (Member
         .select(Member.firstname, Member.surname, Member.joindate)
         .where(Member.joindate == subq))