Peewee - Query Builder

Peewee also provides a non-ORM API to access the databases. Instead of defining models and fields, we can bind the database tables and columns to Table and Column objects defined in Peewee and execute queries with their help.

To begin with, declare a Table object corresponding to the one in our database. You have to specify table name and pst of columns. Optionally, a primary key can also be provided.

Contacts=Table( Contacts , ( id ,  RollNo ,  Name ,  City ))

This table object is bound with the database with bind() method.

Contacts=Contacts.bind(db)

Example

Now, we can set up a SELECT query on this table object with select() method and iterate over the resultset as follows −

names=Contacts.select()
for name in names:
   print (name)

Output

The rows are by default returned as dictionaries.

{ id : 1,   RollNo : 101,  Name :  Anil ,  City :  Mumbai }
{ id : 2,   RollNo : 102,  Name :  Amar ,  City :  Delhi }
{ id : 3,   RollNo : 103,  Name :  Raam ,  City :  Indore }
{ id : 4,   RollNo : 104,  Name :  Leena ,  City :  Nasik }
{ id : 5,   RollNo : 105,  Name :  Keshav ,  City :  Pune }
{ id : 6,   RollNo : 106,  Name :  Hema ,  City :  Nagpur }
{ id : 7,   RollNo : 107,  Name :  Beena ,  City :  Chennai }
{ id : 8,   RollNo : 108,  Name :  John ,  City :  Delhi }
{ id : 9,   RollNo : 109,  Name :  Jaya ,  City :  Nasik }
{ id : 10,  RollNo : 110,  Name :  Raja ,  City :  Nasik }

If needed, they can be obtained as tuples, namedtuples or objects.

Tuples

The program is as follows −

Example

names=Contacts.select().tuples()
for name in names:
   print (name)

Output

The output is given below −

(1, 101,  Anil ,  Mumbai )
(2, 102,  Amar ,  Delhi )
(3, 103,  Raam ,  Indore )
(4, 104,  Leena ,  Nasik )
(5, 105,  Keshav ,  Pune )
(6, 106,  Hema ,  Nagpur )
(7, 107,  Beena ,  Chennai )
(8, 108,  John ,  Delhi )
(9, 109,  Jaya ,  Nasik )
(10, 110,  Raja ,  Nasik )

Namedtuples

The program is stated below −

Example

names=Contacts.select().namedtuples()
for name in names:
   print (name)

Output

The output is given below −

Row(id=1, RollNo=101, Name= Anil , City= Mumbai )
Row(id=2, RollNo=102, Name= Amar , City= Delhi )
Row(id=3, RollNo=103, Name= Raam , City= Indore )
Row(id=4, RollNo=104, Name= Leena , City= Nasik )
Row(id=5, RollNo=105, Name= Keshav , City= Pune )
Row(id=6, RollNo=106, Name= Hema , City= Nagpur )
Row(id=7, RollNo=107, Name= Beena , City= Chennai )
Row(id=8, RollNo=108, Name= John , City= Delhi )
Row(id=9, RollNo=109, Name= Jaya , City= Nasik )
Row(id=10, RollNo=110, Name= Raja , City= Nasik )

To insert a new record, INSERT query is constructed as follows −

id = Contacts.insert(RollNo=111, Name= Abdul , City= Surat ).execute()

If a pst of records to be added is stored either as a pst of dictionaries or as pst of tuples, they can be added in bulk.

Records=[{‘RollNo’:112, ‘Name’:’Ajay’, ‘City’:’Mysore’}, 
   {‘RollNo’:113, ‘Name’:’Majid’,’City’:’Delhi’}}

Or

Records=[(112, ‘Ajay’,’Mysore’), (113, ‘Majid’, ‘Delhi’)}

The INSERT query is written as follows −

Contacts.insert(Records).execute()

The Peewee Table object has update() method to implement SQL UPDATE query. To change City for all records from Nasik to Nagar, we use following query.

Contacts.update(City= Nagar ).where((Contacts.City== Nasik )).execute()

Finally, Table class in Peewee also has delete() method to implement DELETE query in SQL.

Contacts.delete().where(Contacts.Name== Abdul ).execute()