- Peewee Tutorial
- Peewee - Home
- Peewee - Overview
- Peewee - Database Class
- Peewee - Model
- Peewee - Field Class
- Peewee - Insert a New Record
- Peewee - Select Records
- Peewee - Filters
- Peewee - Primary & Composite Keys
- Peewee - Update Existing Records
- Peewee - Delete Records
- Peewee - Create Index
- Peewee - Constraints
- Peewee - Using MySQL
- Peewee - Using PostgreSQL
- Peewee - Defining Database Dynamically
- Peewee - Connection Management
- Peewee - Relationships & Joins
- Peewee - Subqueries
- Peewee - Sorting
- Peewee - Counting & Aggregation
- Peewee - SQL Functions
- Peewee - Retrieving Row Tuples/Dictionaries
- Peewee - User defined Operators
- Peewee - Atomic Transactions
- Peewee - Database Errors
- Peewee - Query Builder
- Peewee - Integration with Web Frameworks
- Peewee - SQLite Extensions
- Peewee - PostgreSQL & MySQL Extensions
- Peewee - Using CockroachDB
- Peewee Useful Resources
- Peewee - Quick Guide
- Peewee - Useful Resources
- Peewee - Discussion
Peewee - Relationships and Joins
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)
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 supports following Join types (Default is INNER).
JOIN.INNER
JOIN.LEFT_OUTER
JOIN.RIGHT_OUTER
JOIN.FULL
JOIN.FULL_OUTER
JOIN.CROSS
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
The item table is given below −
Brand Table
Given below is the brand table −
Bill Table
The bill table is as follows −
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
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
To Continue Learning Please Login
Login with Google