- Peewee Tutorial
- Peewee - Home
- Peewee - Overview
- Peewee - Database Class
- Peewee - Model
- Peewee - Field Class
- Peewee - Insert a New Record
- Peewee - Select Records
- Peewee - Filters
- Peewee - Primary & Composite Keys
- Peewee - Update Existing Records
- Peewee - Delete Records
- Peewee - Create Index
- Peewee - Constraints
- Peewee - Using MySQL
- Peewee - Using PostgreSQL
- Peewee - Defining Database Dynamically
- Peewee - Connection Management
- Peewee - Relationships & Joins
- Peewee - Subqueries
- Peewee - Sorting
- Peewee - Counting & Aggregation
- Peewee - SQL Functions
- Peewee - Retrieving Row Tuples/Dictionaries
- Peewee - User defined Operators
- Peewee - Atomic Transactions
- Peewee - Database Errors
- Peewee - Query Builder
- Peewee - Integration with Web Frameworks
- Peewee - SQLite Extensions
- Peewee - PostgreSQL & MySQL Extensions
- Peewee - Using CockroachDB
- Peewee Useful Resources
- Peewee - Quick Guide
- Peewee - Useful Resources
- Peewee - Discussion
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
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()