”;
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)
|
| 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.
”;