Peewee 简明教程

Peewee - Filters

可以使用 where 子句从 SQLite 表中检索数据。Peewee 支持以下逻辑运算符列表。

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)

以下代码显示 age>=20: 的名称

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

Peewee 因此生成的 SELECT 查询将为 −

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

除了核心 Python 中定义的上述逻辑运算符之外，Peewee 还提供以下方法进行筛选 −

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.

作为以上方法的一个例子，查看以下代码。它获取以“R”开头或以“r”结尾的名称。

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

等价的 SQL SELECT 查询为：

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 内置的运算符 in、not in、and、or 等不起作用。相反，使用 Peewee 替代。

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.