Peewee 简明教程

Peewee - Relationships and Joins

Peewee 支持实现不同类型的 SQL JOIN 查询。它的 Model 类有一个 join() 方法，它会返回一个 Join 实例。

Peewee supports implementing different type of SQL JOIN queries. Its Model class has a join() method that returns a Join instance.

M1.joint(m2, join_type, on)

join 表将 M1 模型映射到 m2 模型，并返回 Join 类实例。on 参数默认是 None，它是一个用作连接谓词的表达式。

The joins table mapped with M1 model to that of m2 model and returns Join class instance. The on parameter is None by default and is expression to use as join predicate.

Join Types

Peewee 支持以下 Join 类型（默认为 INNER）。

Peewee supports following Join types (Default is INNER).

  1. JOIN.INNER

  2. JOIN.LEFT_OUTER

  3. JOIN.RIGHT_OUTER

  4. JOIN.FULL

  5. JOIN.FULL_OUTER

  6. JOIN.CROSS

为了演示 join() 方法的用法，首先声明以下模型：

To show use of join() method, we first declare following models −

db = SqliteDatabase('mydatabase.db')

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

class Item(BaseModel):
   itemname = TextField()
      price = IntegerField()

class Brand(BaseModel):
   brandname = TextField()
      item = ForeignKeyField(Item, backref='brands')

class Bill(BaseModel):
   item = ForeignKeyField(Item, backref='bills')
   brand = ForeignKeyField(Brand, backref='bills')
   qty = DecimalField()

db.create_tables([Item, Brand, Bill])

Tables

接着，使用以下测试数据填充这些表：

Next, we populate these tables with following test data −

Item Table

item 表如下：

The item table is given below −

item table

Brand Table

brand 表如下：

Given below is the brand table −

brand table

Bill Table

bill 表如下：

The bill table is as follows −

bill table

若要在 Brand 和 Item 表之间执行一个简单的连接操作，请执行以下代码：

To perform a simple join operation between Brand and Item tables, execute the following code −

qs=Brand.select().join(Item)
for q in qs:
print ("Brand ID:{} Item Name: {} Price: {}".format(q.id, q.brandname, q.item.price))

最终输出如下：

The resultant output will be as follows −

Brand ID:1 Item Name: Dell Price: 25000
Brand ID:2 Item Name: Epson Price: 12000
Brand ID:3 Item Name: HP Price: 25000
Brand ID:4 Item Name: iBall Price: 4000
Brand ID:5 Item Name: Sharp Price: 12000

Joining Multiple Tables

我们有一个 Bill 模型，它与 item 和 brand 模型有两个外键关系。若要从所有三个表中获取数据，请使用以下代码：

We have a Bill model having two foreign key relationships with item and brand models. To fetch data from all three tables, use following code −

qs=Bill.select().join(Brand).join(Item)
for q in qs:
print ("BillNo:{} Brand:{} Item:{} price:{} Quantity:{}".format(q.id, \
q.brand.brandname, q.item.itemname, q.item.price, q.qty))

基于我们的测试数据，将显示以下输出：

Following output will be displayed, based on our test data −

BillNo:1 Brand:HP Item:Laptop price:25000 Quantity:5
BillNo:2 Brand:Epson Item:Printer price:12000 Quantity:2
BillNo:3 Brand:iBall Item:Router price:4000 Quantity:5