- Peewee - Discussion
- Peewee - Useful Resources
- Peewee - Quick Guide
- Peewee - Using CockroachDB
- Peewee - PostgreSQL & MySQL Extensions
- Peewee - SQLite Extensions
- Peewee - Integration with Web Frameworks
- Peewee - Query Builder
- Peewee - Database Errors
- Peewee - Atomic Transactions
- Peewee - User defined Operators
- Peewee - Retrieving Row Tuples/Dictionaries
- Peewee - SQL Functions
- Peewee - Counting & Aggregation
- Peewee - Sorting
- Peewee - Subqueries
- Peewee - Relationships & Joins
- Peewee - Connection Management
- Peewee - Defining Database Dynamically
- Peewee - Using PostgreSQL
- Peewee - Using MySQL
- Peewee - Constraints
- Peewee - Create Index
- Peewee - Delete Records
- Peewee - Update Existing Records
- Peewee - Primary & Composite Keys
- Peewee - Filters
- Peewee - Select Records
- Peewee - Insert a New Record
- Peewee - Field Class
- Peewee - Model
- Peewee - Database Class
- Peewee - Overview
- Peewee - Home
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
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 = SqpteDatabase( 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:5Advertisements