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 list 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 - Select Query using non-ORM API

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

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

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

Contacts=Contacts.bind(db)

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

db.close()

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.

Example - Tuples

The program is as follows −

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

Contacts=Table('Contacts', ('id', 'RollNo', 'Name', 'City'))
Contacts=Contacts.bind(db)

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

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')

Example - Named tuples

The program is stated below −

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

Contacts=Table('Contacts', ('id', 'RollNo', 'Name', 'City'))
Contacts=Contacts.bind(db)

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

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 list of records to be added is stored either as a list of dictionaries or as list 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()