Peewee - Filters

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)

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

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 −

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.

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.

Advertisements