Peewee - Subqueries

In SQL, a subquery is an embedded query in WHERE clause of another query. We can implement subquery as a model.select() as a parameter inside where attribute of outer model.select() statement.

To demonstrate use of subquery in Peewee, let us use defined following models −

from peewee import *
db = SqpteDatabase( mydatabase.db )

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

class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()

class Branches(BaseModel):
   RollNo = IntegerField()
   Faculty = TextField()

db.create_tables([Contacts, Branches])

After tables are created, they are populated with following sample data −

Contacts table

The contacts table is given below −

In order to display name and city from contact table only for RollNo registered for ETC faculty, following code generates a SELECT query with another SELECT query in its WHERE clause.

#this query is used as subquery
faculty=Branches.select(Branches.RollNo).where(Branches.Faculty=="ETC")
names=Contacts.select().where (Contacts.RollNo .in_(faculty))

print ("RollNo and City for Faculty= ETC ")
for name in names:
   print ("RollNo:{} City:{}".format(name.RollNo, name.City))

db.close()

Above code will display the following result:

RollNo and City for Faculty= ETC 
RollNo:103 City:Indore
RollNo:104 City:Nasik
RollNo:108 City:Delhi
RollNo:110 City:Nasik