Peewee - Filters

It is possible to retrieve data from SQLite table by using where clause. Peewee supports following pst of logical operators.

Following code displays name with age>=20:

rows=User.select().where (User.age>=20)
for row in rows:
   print ("name: {} age: {}".format(row.name, row.age))

Following code displays only those name present in the names pst.

names=[ Anil ,  Amar ,  Kiran ,  Bala ]
rows=User.select().where (User.name << names)
for row in rows:
   print ("name: {} age: {}".format(row.name, row.age))

The SELECT query thus generated by Peewee will be −

( SELECT "t1"."id", "t1"."name", "t1"."age" FROM "User" AS "t1" WHERE 
   ("t1"."name" IN (?, ?, ?, ?)) , [ Anil ,  Amar ,  Kiran ,  Bala ])

Resultant output will be as follows −

name: Amar age: 20
name: Kiran age: 19

Filtering Methods

In addition to the above logical operators as defined in core Python, Peewee provides following methods for filtering −

As an example of above methods, look at the following code. It retrieves names starting with ‘R’ or ending with ‘r’.

rows=User.select().where (User.name.startswith( R ) | User.name.endswith( r ))

Equivalent SQL SELECT query is:

( SELECT "t1"."id", "t1"."name", "t1"."age" FROM "User" AS "t1" WHERE 
   (("t1"."name" LIKE ?) OR ("t1"."name" LIKE ?)) , [ R% ,  %r ])

Alternatives

Python’s built-in operators in, not in, and, or etc. will not work. Instead, use Peewee alternatives.

You can use −

.in_() and .not_in() methods instead of in and not in operators.

& instead of and.

| instead of or.

~ instead of not.

.is_null() instead of is.

None or == None.