Peewee Useful Resources

Peewee - Filters

Quiz

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

==	x equals y
<	x is less than y
<=	x is less than or equal to y
>	x is greater than y
>=	x is greater than or equal to y
!=	x is not equal to y
<<	x IN y, where y is a list or query
>>	x IS y, where y is None/NULL
%	x LIKE y where y may contain wildcards
**	x ILIKE y where y may contain wildcards
^	x XOR y
~	Unary negation (e.g., NOT x)

Example - Display name with Age >= 20

Following code displays name with age>=20:

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'
	  
rows=User.select().where (User.age>=20)
for row in rows:
   print ("name: {} age: {}".format(row.name, row.age))

Output

name: Rajesh age: 21
name: Amar age: 20
name: Lata age: 20

Example - Display selected names

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

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'

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

Output

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 −

Sr.No	Methods & Description
1	.in_(value) IN lookup (identical to <<).
2	.not_in(value) NOT IN lookup.
3	.is_null(is_null) IS NULL or IS NOT NULL. Accepts boolean param.
4	.contains(substr) Wild-card search for substring.
5	.startswith(prefix) Search for values beginning with prefix.
6	.endswith(suffix) Search for values ending with suffix.
7	.between(low, high) Search for values between low and high.
8	.regexp(exp) Regular expression match (case-sensitive).
9	.iregexp(exp) Regular expression match (case-insensitive).
10	.bin_and(value) Binary AND.
11	.bin_or(value) Binary OR.
12	.concat(other) Concatenate two strings or objects using \|\|.
13	.distinct() Mark column for DISTINCT selection.
14	.collate(collation) Specify column with the given collation.
15	.cast(type) Cast the value of the column to the given type.

Example - Retrieving Names starting with R or ending with r

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

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'
	  
rows=User.select().where (User.name.startswith('R') | User.name.endswith('r'))

for row in rows:
   print ("name: {} age: {}".format(row.name, row.age))

Output

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

name: Rajesh age: 21
name: Amar age: 20

Alternatives

Pythons 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.

Print Page