Peewee – Query Builder
”; Previous Next
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
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 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()
Print Page
Previous
Next
Advertisements ”;