Peewee 简明教程

Peewee - Quick Guide

Peewee - Overview

Peewee 是一个 Python 对象关系映射（ORM）库，由一位位于美国软件工程师 Charles Leifer 开发于 2010 年 10 月。其最新版本是 3.13.3. Peewee 支持 SQLite、MySQL、PostgreSQL 和 Cockroach 数据库。

Peewee is a Python Object Relational Mapping (ORM) library which was developed by a U.S. based software engineer Charles Leifer in October 2010. Its latest version is 3.13.3. Peewee supports SQLite, MySQL, PostgreSQL and Cockroach databases.

对象关系映射是一种编程技术，用于在面向对象编程语言中将数据在不兼容类型系统之间进行转换。

Object Relational Mapping is a programming technique for converting data between incompatible type systems in object-oriented programming languages.

在面向对象（OO）编程语言中（例如 Python），按定义的类被认为是非标量的。它不能表示为整数和字符串等基元类型。

Class as defined in an Object Oriented (OO) programming language such as Python, is considered as non-scalar. It cannot be expressed as primitive types such as integers and strings.

另一方面，Oracle、MySQL、SQLite 等数据库只能存储和处理表内组织的整数和字符串等标量值。

On the other hand, databases like Oracle, MySQL, SQLite and others can only store and manipulate scalar values such as integers and strings organised within tables.

程序员必须将对象值转换为标量数据类型的组，以便存储在数据库中，或者在检索后将它们转换回标量值，或仅在程序内使用简单的标量值。

The programmer must either convert the object values into groups of scalar data types for storage in the database or convert them back upon retrieval, or only use simple scalar values within the program.

在 ORM 系统中，每个类都映射到基础数据库中的一个表。你无需自己编写繁琐的数据库接口代码， ORM 会处理这些问题，而你可以专注于对系统逻辑进行编程。

In an ORM system, each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues, while you can focus on programming the logics of the system.

Environment setup

要安装 Peewee 的最新版本（由 PyPI（Python 包索引）托管），请使用 pip 安装程序。

To install latest version of Peewee as hosted on PyPI (Python Package Index), use pip installer.

pip3 install peewee

Peewee 工作没有任何其他依赖项。它不用安装任何其他包即可使用 SQLite，因为 sqlite3 模块与标准库捆绑在一起。

There are no other dependencies for Peewee to work. It works with SQLite without installing any other package as sqlite3 module is bundled with standard library.

但是，要使用 MySQL 和 PostgreSQL，你可能不得不分别安装 DB-API 兼容驱动器模块 pymysql 和 pyscopg2。Cockroach 数据库通过 playhouse 扩展来处理，该扩展将随 Peewee 一起默认安装。

However, to work with MySQL and PostgreSQL, you may have to install DB-API compatible driver modules pymysql and pyscopg2 respectively. Cockroach database is handled through playhouse extension that is installed by default along with Peewee.

Peewee 是托管在 https://github.com/coleifer/peewee 存储库上的开源项目。因此，它可以通过使用 Git 从这里安装。

Peewee is an open source project hosted on https://github.com/coleifer/peewee repository. Hence, it can be installed from here by using git.

git clone https://github.com/coleifer/peewee.git
cd peewee
python setup.py install

Peewee - Database Class

Peewee 包中的 Database 类的一个对象表示与数据库的连接。Peewee 通过 Database 类的相应子类，为 SQLite、PostgreSQL 和 MySQL 数据库提供了开箱即用的支持。

An object of Database class from Peewee package represents connection to a database. Peewee provides out-of-box support for SQLite, PostgreSQL and MySQL databases through corresponding subclasses of Database class.

Database 类的实例拥有打开与数据库引擎的连接所需的所有信息，并用于执行查询，管理事务和执行表的内省，列等。

Database class instance has all the information required to open connection with database engine, and is used to execute queries, manage transactions and perform introspection of tables, columns, etc.

Database 类拥有 SqliteDatabase 、 PostgresqlDatabase 和 MySQLDatabase 子类。尽管用于 SQLite 的 DB-API 驱动器，采用 sqlite3 模块的形式，包含在 Python 的标准库中，但 psycopg2 和 pymysql 模块必须先安装以便将 PostgreSql 和 MySQL 数据库与 Peewee 一起使用。

Database class has SqliteDatabase, PostgresqlDatabase and MySQLDatabase sub-classes. While DB-API driver for SQLite in the form of sqlite3 module is included in Python’s standard library, psycopg2 and pymysql modules will have to be installed first for using PostgreSql and MySQL databases with Peewee.

Using Sqlite Database

Python 在 sqlite3 模块的形式中拥有对 SQLite 数据库的内置支持。因此，它的连接非常容易。Peewee 中的 SqliteDatabase 类的对象表示连接对象。

Python has built-in support for SQLite database in the form of sqlite3 module. Hence, it is very easy to connect. Object of SqliteDatabase class in Peewee represents connection object.

con=SqliteDatabase(name, pragmas, timeout)

此处， pragma 是用于修改 SQLite 库操作的 SQLite 扩展。此参数可以是包含要每次打开连接时设置的 pragma 键值的一本词典或一个由 2-tuple 组成的列表。

Here, pragma is SQLite extension which is used to modify operation of SQLite library. This parameter is either a dictionary or a list of 2-tuples containing pragma key and value to set every time a connection is opened.

Timeout 参数以秒为单位指定，用以设置 SQLite 驱动器的繁忙超时。这两个参数都是可选的。

Timeout parameter is specified in seconds to set busy-timeout of SQLite driver. Both the parameters are optional.

下面的语句会创建一个新 SQLite 数据库的连接（如果它不存在）。

Following statement creates a connection with a new SQLite database (if it doesn’t exist already).

>>> db = peewee.SqliteDatabase('mydatabase.db')

Pragma 参数通常提供给新的数据库连接。pragmase 词典中提到的典型属性为 journal_mode 、 cache_size 、 locking_mode 、 foreign-keys 等。

Pragma parameters are generally given for a new database connection. Typical attributes mentioned in pragmase dictionary are journal_mode, cache_size, locking_mode, foreign-keys, etc.

>>> db = peewee.SqliteDatabase(
   'test.db', pragmas={'journal_mode': 'wal', 'cache_size': 10000,'foreign_keys': 1}
)

以下 pragma 设置是理想的且应指定 −

Following pragma settings are ideal to be specified −

Pragma attribute

Recommended value

Meaning

journal_mode

wal

allow readers and writers to co-exist

cache_size

-1 * data_size_kb

set page-cache size in KiB

foreign_keys

enforce foreign-key constraints

ignore_check_constraints

enforce CHECK constraints

Synchronous

let OS handle fsync

Peewee 还有一个 Another Python SQLite Wrapper (apsw) —— 一个高级 sqlite 驱动器。它提供虚拟表和文件系统以及共享连接等高级功能。APSW 比标准库 sqlite3 模块更快。

Peewee also has Another Python SQLite Wrapper (apsw), an advanced sqlite driver. It provides advanced features such as virtual tables and file systems, and shared connections. APSW is faster than the standard library sqlite3 module.

Peewee - Model

Peewee API 中 Model 子类的一个对象对应一个表，该表位于已建立其连接的数据库中。它允许执行数据库表操作，通过 Model 类中定义的方法来实现。

An object of Model sub class in Peewee API corresponds to a table in the database with which connection has been established. It allows performing database table operations with the help of methods defined in the Model class.

一个用户定义的 Model 有一个或更多类属性，它们中的每一个都是 Field 类的对象。Peewee 有多个子类用于保存不同类型的数据。示例有 TextField、DatetimeField 等。它们对应于数据库表中的字段或列。关联的数据库和表以及模型配置的引用在 Meta 类中提及。以下属性用于指定配置 −

A user defined Model has one or more class attributes, each of them is an object of Field class. Peewee has a number of subclasses for holding data of different types. Examples are TextField, DatetimeField, etc. They correspond to the fields or columns in the database table. Reference of associated database and table and model configuration is mentioned in Meta class. Following attributes are used to specify configuration −

Meta class Attributes

下面解释了元类属性：

The meta class attributes are explained below −

Sr.No	Attribute & Description
1	Database Database for model.
2	db_table Name of the table to store data. By default, it is name of model class.
3	Indexes A list of fields to index.
4	primary_key A composite key instance.
5	Constraints A list of table constraints.
6	Schema The database schema for the model.
7	Temporary Indicate temporary table.
8	depends_on Indicate this table depends on another for creation.
9	without_rowid Indicate that table should not have rowid (SQLite only).

以下代码为 mydatabase.db 中的 User 表定义了 Model 类 -

Following code defines Model class for User table in mydatabase.db −

from peewee import *
db = SqliteDatabase('mydatabase.db')
class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'
User.create_table()

create_table() 方法是 Model 类的一个类方法，它执行等效的 CREATE TABLE 查询。另一个实例方法 save() 添加了一个与对象对应行。

The create_table() method is a classmethod of Model class that performs equivalent CREATE TABLE query. Another instance method save() adds a row corresponding to object.

from peewee import *
db = SqliteDatabase('mydatabase.db')
class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'

User.create_table()
rec1=User(name="Rajesh", age=21)
rec1.save()

Methods in Model class

Model 类中的其他方法如下所示 −

Other methods in Model class are as follows −

Sr.No	Model Class & Description
1	Classmethod alias() Create an alias to the model-class. It allows the same Model to any referred multiple times in a query.
2	Classmethod select() Performs a SELECT query operation. If no fields are explicitly provided as argument, the query will by default SELECT * equivalent.
3	Classmethod update() Performs an UPDATE query function.
4	classmethod insert() Inserts a new row in the underlying table mapped to model.
5	classmethod delete() Executes delete query and is usually associated with a filter by where clause.
6	classmethod get() Retrieve a single row from mapped table matching the given filters.
7	get_id() Instance method returns primary key of a row.
8	save() Save the data of object as a new row. If primary-key value is already present, it will cause an UPDATE query to be executed.
9	classmethod bind() Bind the model to the given database.

Peewee - Field Class

Model 类包含一个或多个属性，它们是 Peewee 中 Field 类的对象。不会直接实例化 Base Field 类。Peewee 为等效 SQL 数据类型定义不同的子类。

Model class contains one or more attributes that are objects of Field class in Peewee. Base Field class is not directly instantiated. Peewee defines different sub classes for equivalent SQL data types.

Field 类的构造函数具有以下参数−

Constructor of Field class has following parameters−

Sr.No	Constructor & Description
1	column_name (str) Specify column name for field.
2	primary_key (bool) Field is the primary key.
3	constraints (list) List of constraints to apply to column
4	choices (list) An iterable of 2-tuples mapping column values to display labels.
5	null (bool) Field allows NULLs.
6	index (bool) Create an index on field.
7	unique (bool) Create an unique index on field.
8	Default Default value.
9	collation (str) Collation name for field.
10	help_text (str) Help-text for field, metadata purposes.
11	verbose_name (str) Verbose name for field, metadata purposes.

Field 类的子类映射到不同数据库（即 SQLite、PostgreSQL、MySQL 等）中对应的数据库类型。

Subclasses of Field class are mapped to corresponding data types in various databases, i.e. SQLite, PostgreSQL, MySQL, etc.

Numeric Field classes

Peewee 中的数字字段类如下 −

The numeric field classes in Peewee are given below −

Sr.No	Field classes & Description
1	IntegerField Field class for storing integers.
2	BigIntegerField Field class for storing big integers (maps to integer, bigint, and bigint type in SQLite, PostegreSQL and MySQL respectively).
3	SmallIntegerField Field class for storing small integers (if supported by database).
4	FloatField Field class for storing floating-point numbers corresponds to real data types.
5	DoubleField Field class for storing double-precision floating-point numbers maps to equivalent data types in corresponding SQL databases.
6	DecimalField Field class for storing decimal numbers. The parameters are mentioned below − max_digits (int) – Maximum digits to store. decimal_places (int) – Maximum precision. auto_round (bool) – Automatically round values.

Text fields

Peewee 中的文本字段如下 −

The text fields which are available in Peewee are as follows −

Sr.No

Fields & Description

CharField Field class for storing strings. Max 255 characters. Equivalent SQL data type is varchar.

FixedCharField Field class for storing fixed-length strings.

TextField Field class for storing text. Maps to TEXT data type in SQLite and PostgreSQL, and longtext in MySQL.

Binary fields

下面是对 Peewee 中二进制字段的说明−

The binary fields in Peewee are explained below −

Sr.No	Fields & Description
1	BlobField Field class for storing binary data.
2	BitField Field class for storing options in a 64-bit integer column.
3	BigBitField Field class for storing arbitrarily-large bitmaps in a Binary Large OBject (BLOB). The field will grow the underlying buffer as necessary.
4	UUIDField Field class for storing universally unique identifier (UUID) objects. Maps to UUID type in Postgres. SQLite and MySQL do not have a UUID type, it is stored as a VARCHAR.

Date and Time fields

Peewee 中的日期和时间字段如下所示 −

The date and time fields in Peewee are as follows −

Sr.No

Fields & Description

DateTimeField Field class for storing datetime.datetime objects. Accepts a special parameter string formats, with which the datetime can be encoded.

DateField Field class for storing datetime.date objects. Accepts a special parameter string formats to encode date.

TimeField Field class for storing datetime.time objectsAccepts a special parameter formats to show encoded time.

由于 SQLite 没有 DateTime 数据类型，所以此字段被映射为字符串。

Since SQLite doesn’t have DateTime data types, this field is mapped as string.

ForeignKeyField

此类用于在两个模型中建立外键关系，这样就得到了数据库中相应的表。此类使用以下参数进行实例化 −

This class is used to establish foreign key relationship in two models and hence, the respective tables in database. This class in instantiated with following parameters −

Sr.No	Fields & Description
1	model (Model) Model to reference. If set to ‘self’, it is a self-referential foreign key.
2	field (Field) Field to reference on model (default is primary key).
3	backref (str) Accessor name for back-reference. “+” disables the back-reference accessor.
4	on_delete (str) ON DELETE action.
5	on_update (str) ON UPDATE action.
6	lazy_load (bool) Fetch the related object, when the foreign-key field attribute is accessed. If FALSE, accessing the foreign-key field will return the value stored in the foreign-key column.

Example

以下是 ForeignKeyField 的示例。

Here is an example of ForeignKeyField.

from peewee import *

db = SqliteDatabase('mydatabase.db')
class Customer(Model):
   id=IntegerField(primary_key=True)
   name = TextField()
   address = TextField()
   phone = IntegerField()
   class Meta:
      database=db
      db_table='Customers'

class Invoice(Model):
   id=IntegerField(primary_key=True)
   invno=IntegerField()
   amount=IntegerField()
   custid=ForeignKeyField(Customer, backref='Invoices')
   class Meta:
      database=db
      db_table='Invoices'

db.create_tables([Customer, Invoice])

执行上述脚本时，将运行以下 SQL 查询 −

When above script is executed, following SQL queries are run −

CREATE TABLE Customers (
   id INTEGER NOT NULL
   PRIMARY KEY,
   name TEXT NOT NULL,
   address TEXT NOT NULL,
   phone INTEGER NOT NULL
);
CREATE TABLE Invoices (
   id INTEGER NOT NULL
   PRIMARY KEY,
   invno INTEGER NOT NULL,
   amount INTEGER NOT NULL,
   custid_id INTEGER NOT NULL,
   FOREIGN KEY (
      custid_id
   )
   REFERENCES Customers (id)
);

在 SQLiteStudio 图形用户界面工具中验证时，表结构如下所示 −

When verified in SQLiteStuidio GUI tool, the table structure appears as below −

Other Field Types

Peewee 中其他字段类型包括−

The other field types in Peewee include −

Sr.No	Fields & Description
1	IPField Field class for storing IPv4 addresses efficiently (as integers).
2	BooleanField Field class for storing boolean values.
3	AutoField Field class for storing auto-incrementing primary keys.
4	IdentityField Field class for storing auto-incrementing primary keys using the new Postgres 10 IDENTITYField class for storing auto-incrementing primary keys using the new Postgres 10 IDENTITY column type. column type.

Peewee - Insert a New Record

在 Peewee 中，有多个命令可以通过这些命令，可以在表中添加新的记录。我们已经使用了 Model 实例的 save() 方法。

In Peewee, there are more than one commands by which, it is possible to add a new record in the table. We have already used save() method of Model instance.

rec1=User(name="Rajesh", age=21)
rec1.save()

Peewee.Model 类还具有 a create() 方法，该方法创建一个新实例并在表中添加其数据。

The Peewee.Model class also has a create() method that creates a new instance and add its data in the table.

User.create(name="Kiran", age=19)

除此之外，Model 还具有 insert() 作为构造 SQL insert 查询对象的类方法。查询对象的 execute() 方法执行为基础表中的行添加操作。

In addition to this, Model also has insert() as class method that constructs SQL insert query object. The execute() method of Query object performs adding a row in underlying table.

q = User.insert(name='Lata', age=20)
q.execute()

查询对象相当于 INSERT 查询。q.sql() 返回查询字符串。

The query object is an equivalent INSERT query.q.sql() returns the query string.

print (q.sql())
('INSERT INTO "User" ("name", "age") VALUES (?, ?)', ['Lata', 20])

以下是演示使用上述插入记录方式的完整代码。

Here is the complete code that demonstrates the use of above ways of inserting record.

from peewee import *
db = SqliteDatabase('mydatabase.db')
class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'

db.create_tables([User])
rec1=User(name="Rajesh", age=21)
rec1.save()
a=User(name="Amar", age=20)
a.save()
User.create(name="Kiran", age=19)
q = User.insert(name='Lata', age=20)
q.execute()
db.close()

我们可以在 SQLiteStudio GUI 中验证结果。

We can verify the result in SQLiteStudio GUI.

Bulk Inserts

为了在表中一次使用多行，Peewee 提供两种方法： bulk_create 和 insert_many。

In order to use multiple rows at once in the table, Peewee provides two methods: bulk_create and insert_many.

insert_many()

insert_many() 方法生成等效 INSERT 查询，使用字典对象列表，每个列表都具有一个对象的一个字段值对。

The insert_many() method generates equivalent INSERT query, using list of dictionary objects, each having field value pairs of one object.

rows=[{"name":"Rajesh", "age":21}, {"name":"Amar", "age":20}]
q=User.insert_many(rows)
q.execute()

这里也是如此，q.sql() 返回 INSERT 查询字符串，如下获取 −

Here too, q.sql() returns the INSERT query string is obtained as below −

print (q.sql())
('INSERT INTO "User" ("name", "age") VALUES (?, ?), (?, ?)', ['Rajesh', 21, 'Amar', 20])

bulk_create()

此方法采用一个列表参数，其中包含映射到表的模型的一个或多个未保存实例。

This method takes a list argument that contains one or more unsaved instances of the model mapped to a table.

a=User(name="Kiran", age=19)
b=User(name='Lata', age=20)
User.bulk_create([a,b])

以下代码使用两种方法执行批量插入操作。

Following code uses both approaches to perform bulk insert operation.

from peewee import *
db = SqliteDatabase('mydatabase.db')
class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'

db.create_tables([User])
rows=[{"name":"Rajesh", "age":21}, {"name":"Amar", "age":20}]
q=User.insert_many(rows)
q.execute()
a=User(name="Kiran", age=19)
b=User(name='Lata', age=20)
User.bulk_create([a,b])
db.close()

Peewee - Select Records

从表中检索数据的最简单也是最明显的方法是调用相应模型的 select() 方法。在 select() 方法内部，我们可以指定一个或多个字段属性。但是，如果没有指定，则会选择所有列。

Simplest and the most obvious way to retrieve data from tables is to call select() method of corresponding model. Inside select() method, we can specify one or more field attributes. However, if none is specified, all columns are selected.

Model.select() 返回与行对应的模型实例列表。这类似于 SELECT 查询返回的结果集，可以通过 for 循环遍历该结果集。

Model.select() returns a list of model instances corresponding to rows. This is similar to the result set returned by SELECT query, which can be traversed by a for loop.

from peewee import *
db = SqliteDatabase('mydatabase.db')
class User (Model):
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'
rows=User.select()
print (rows.sql())
for row in rows:
   print ("name: {} age: {}".format(row.name, row.age))
db.close()

上述脚本显示以下输出 -

The above script displays the following output −

('SELECT "t1"."id", "t1"."name", "t1"."age" FROM "User" AS "t1"', [])
name: Rajesh age: 21
name: Amar age  : 20
name: Kiran age : 19
name: Lata age  : 20

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.

Peewee - Primary and Composite Keys

建议关系数据库中的表应当具有一个应用了主键约束的列。相应地，Peewee 模型类也可以指定具有 primary-key 设置为 True 的字段属性。但是，如果模型类没有任何主键，Peewee 自动创建一个，名称为 “id”。请注意，上面定义的 User 模型没有任何明确定义为主键的字段。因此，我们在数据库中映射的 User 表具有一个 id 字段。

It is recommended that the table in a relational database, should have one of the columns applied with primary key constraint. Accordingly, Peewee Model class can also specify field attribute with primary-key argument set to True. However, if model class doesn’t have any primary key, Peewee automatically creates one with the name “id”. Note that the User model defined above doesn’t have any field explicitly defined as primary key. Hence, the mapped User table in our database has an id field.

要定义一个自动递增整型主键，使用 AutoField 对象作为模型中的一个属性。

To define an auto-incrementing integer primary key, use AutoField object as one attribute in the model.

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

该内容将转换为以下 CREATE TABLE 查询 −

This will translate into following CREATE TABLE query −

CREATE TABLE User (
   user_id INTEGER NOT NULL
   PRIMARY KEY,
   name TEXT NOT NULL,
   age INTEGER NOT NULL
);

您还可以通过将 primary_key 参数设置为 True，来将任何非整型字段分配为一个主键。假设我们想将某个字母数字值作为 user_id 存储。

You can also assign any non-integer field as a primary key by setting primary_key parameter to True. Let us say we want to store certain alphanumeric value as user_id.

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

但是，当模型包含非整型字段作为主键时，模型实例的 save() 方法不会导致数据库驱动程序自动生成新的 ID，因此我们需要传递 force_insert=True 参数。但是，请注意 create() 方法隐式指定 force_insert 参数。

However, when model contains non-integer field as primary key, the save() method of model instance doesn’t cause database driver to generate new ID automatically, hence we need to pass force_insert=True parameter. However, note that the create() method implicitly specifies force_insert parameter.

User.create(user_id='A001',name="Rajesh", age=21)
b=User(user_id='A002',name="Amar", age=20)
b.save(force_insert=True)

save() 方法还会更新表中现有的行，在这种情况，强制插入主键是没有必要的，因为具有唯一主键的 ID 已存在。

The save() method also updates an existing row in the table, at which time, force_insert primary is not necessary, as ID with unique primary key is already existing.

Peewee 允许定义复合主键的功能。 CompositeKey 类的对象在 Meta 类中定义为主键。在以下示例中，由 User 模型的 name 和 city 字段组成的复合键已被分配为复合键。

Peewee allows feature of defining composite primary key. Object of CompositeKey class is defined as primary key in Meta class. In following example, a composite key consisting of name and city fields of User model has been assigned as composite key.

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

此模型转换为以下 CREATE TABLE 查询。

This model translates in the following CREATE TABLE query.

CREATE TABLE User (
   name TEXT NOT NULL,
   city TEXT NOT NULL,
   age INTEGER NOT NULL,
   PRIMARY KEY (
      name,
      city
   )
);

如果您希望该表没有主键，可在模型的 Meta 类中指定 primary_key=False。

If you wish, the table should not have a primary key, then specify primary_key=False in model’s Meta class.

Peewee - Update Existing Records

可以调用模型实例或 update() 类方法上的 save() 方法来修改现有数据。

Existing data can be modified by calling save() method on model instance as well as with update() class method.

以下示例在 get() 方法的帮助下从 User 表中提取一行，并通过更改 age 字段的值来更新它。

Following example fetches a row from User table with the help of get() method and updates it by changing the value of age field.

row=User.get(User.name=="Amar")
print ("name: {} age: {}".format(row.name, row.age))
row.age=25
row.save()

方法类的 update() 方法生成 UPDATE 查询。然后调用查询对象的 execute() 方法。

The update() method of Method class generates UPDATE query. The query object’s execute() method is then invoked.

以下示例使用 update() 方法来更改其中 age 列 >20 的行的 age 列。

Following example uses update() method to change the age column of rows in which it is >20.

qry=User.update({User.age:25}).where(User.age>20)
print (qry.sql())
qry.execute()

update() 方法呈现的 SQL 查询如下 −

The SQL query rendered by update() method is as follows −

('UPDATE "User" SET "age" = ? WHERE ("User"."age" > ?)', [25, 20])

Peewee 还有 bulk_update() 方法，以帮助在单个查询操作中更新多个模型实例。该方法需要更新的模型对象以及要更新的字段列表。

Peewee also has a bulk_update() method to help update multiple model instance in a single query operation. The method requires model objects to be updated and list of fields to be updated.

以下示例通过新值更新指定行的 age 字段。

Following example updates the age field of specified rows by new value.

rows=User.select()
rows[0].age=25
rows[2].age=23
User.bulk_update([rows[0], rows[2]], fields=[User.age])

Peewee - Delete Records

对模型实例运行 delete_instance() 方法，从映射的表中删除相应行。

Running delete_instance() method on a model instance delete corresponding row from the mapped table.

obj=User.get(User.name=="Amar")
obj.delete_instance()

另一方面，delete() 是模型类中定义的类方法，它生成 DELETE 查询。有效执行它从表中删除行。

On the other hand, delete() is a class method defined in model class, which generates DELETE query. Executing it effectively deletes rows from the table.

db.create_tables([User])
qry=User.delete().where (User.age==25)
qry.execute()

数据库中受影响的表显示了 DELETE 查询的效果，如下所示 −

Concerned table in database shows effect of DELETE query as follows −

('DELETE FROM "User" WHERE ("User"."age" = ?)', [25])

Peewee - Create Index

通过使用 Peewee ORM，可以定义一个模型，它将在单个列和多列上创建带有索引的表。

By using Peewee ORM, it is possible to define a model which will create a table with index on single column as well as multiple columns.

根据字段属性定义，将唯一约束设置为 True 将在映射的字段上创建索引。同样，将 index=True 参数传递给字段构造函数也会在指定字段上创建索引。

As per the Field attribute definition, setting unique constraint to True will create an index on the mapped field. Similarly, passing index=True parameter to field constructor also create index on the specified field.

在以下示例中，MyUser 模型中有两个字段，其中 username 字段具有设置为 True 的唯一参数，而 email 字段则有 index=True 。

In following example, we have two fields in MyUser model, with username field having unique parameter set to True and email field has index=True.

class MyUser(Model):
   username = CharField(unique=True)
   email = CharField(index=True)
   class Meta:
      database=db
      db_table='MyUser'

因此，SQLiteStudio 图形用户界面 (GUI) 会显示如下创建的索引 −

As a result, SQLiteStudio graphical user interface (GUI) shows indexes created as follows −

为了定义一个多列索引，我们需要在模型类的定义中，在 Meta 类中添加 indexes 属性。这组成的 2 项元组序列，一项元组定义一个索引。在每个 2 元素元组中，第一部分是字段名称组成的元组，第二部分设置为 True 以使其唯一，否则为 False。

In order to define a multi-column index, we need to add indexes attribute in Meta class inside definition of our model class. It is a tuple of 2-item tuples, one tuple for one index definition. Inside each 2-element tuple, the first part of which is a tuple of the names of the fields, the second part is set to True to make it unique, and otherwise is False.

我们按如下方式使用一个两列唯一索引定义 MyUser 模型 −

We define MyUser model with a two-column unique index as follows −

class MyUser (Model):
   name=TextField()
   city=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='MyUser'
      indexes=(
         (('name', 'city'), True),
      )

因此，SQLiteStudio 显示的索引定义如下面的图 −

Accordingly, SQLiteStudio shows index definition as in the following figure −

索引也可以在模型定义外构建。

Index can be built outside model definition as well.

您还可以通过手动提供 SQL 帮助器语句作为 add_index() 方法的参数来创建索引。

You can also create index by manually providing SQL helper statement as parameter to add_index() method.

MyUser.add_index(SQL('CREATE INDEX idx on MyUser(name);'))

当使用 SQLite 时，特别需要上面的方法。对于 MySQL 和 PostgreSQL，我们可以获取索引对象并将其用于 add_index() 方法。

Above method is particularly required when using SQLite. For MySQL and PostgreSQL, we can obtain Index object and use it with add_index() method.

ind=MyUser.index(MyUser.name)
MyUser.add_index(ind)

Peewee - Constraints

约束是对字段中可以放入的可能值施加的限制。其中一个约束是主键。当在字段定义中指定 primary_key=True 时，每一行只能存储唯一的值 - 字段的同一值不能在另一行中重复出现。

Constraints are restrictions imposed on the possible values that can be put in a field. One such constraint is primary key. When primary_key=True is specified in Field definition, each row can only store unique value – same value of the field cannot be repeated in another row.

如果某个字段不是主键，仍然可以对其施加约束，以在表中存储 unique 值。字段构造器还具有约束参数。

If a field is not a primary key, still it can be constrained to store unique values in table. Field constructor also has constraints parameter.

下面的示例在 age 字段上应用 CHECK 约束。

Following example applies CHECK constraint on age field.

class MyUser (Model):
   name=TextField()
   city=TextField()
   age=IntegerField(constraints=[Check('name<10')])
   class Meta:
      database=db
      db_table='MyUser'

这将生成以下数据定义语言 (DDL) 表达式 -

This will generate following Data Definition Language (DDL) expression −

CREATE TABLE MyUser (
   id INTEGER NOT NULL
   PRIMARY KEY,
   name TEXT NOT NULL,
   city TEXT NOT NULL,
   age INTEGER NOT NULL
   CHECK (name < 10)
);

因此，如果新行中 age<10 将导致错误。

As a result, if a new row with age<10 will result in error.

MyUser.create(name="Rajesh", city="Mumbai",age=9)
peewee.IntegrityError: CHECK constraint failed: MyUser

在字段定义中，我们还可以使用 DEFAULT 约束，如下面的 city 字段定义。

In the field definition, we can also use DEFAULT constraint as in following definition of city field.

city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])

因此，可以构建带有或不带有 city 明确值的对象模型。如果没有使用，city 字段将由默认值 - Mumbai 填充。

So, the model object can be constructed with or without explicit value of city. If not used, city field will be filled by default value – Mumbai.

Peewee - Using MySQL

如前所述，Peewee 通过 MySQLDatabase 类支持 MySQL 数据库。但是，与 SQLite 数据库不同，Peewee 无法创建 MySql 数据库。您需要使用诸如 pymysql 之类的 DB-API 兼容模块的功能手动创建它或使用此类模块。

As mentioned earlier, Peewee supports MySQL database through MySQLDatabase class. However, unlike SQLite database, Peewee can’t create a MySql database. You need to create it manually or using functionality of DB-API compliant module such as pymysql.

首先，您应该在机器中安装 MySQL 服务器。它可以是从 https://dev.mysql.com/downloads/installer/. 安装的独立 MySQL 服务器

First, you should have MySQL server installed in your machine. It can be a standalone MySQL server installed from https://dev.mysql.com/downloads/installer/.

您还可以使用捆绑了 MySQL 的 Apache（例如从 https://www.apachefriends.org/download.html 下载并安装的 XAMPP ）。

You can also work on Apache bundled with MySQL (such as XAMPP downloaded and installed from https://www.apachefriends.org/download.html ).

接下来，我们安装 pymysql 模块，它是与 DB-API 兼容的 Python 驱动程序。

Next, we install pymysql module, DB-API compatible Python driver.

pip install pymysql

创建一个名为 mydatabase 的新数据库。我们将使用 XAMPP 中提供的 phpmyadmin 接口。

The create a new database named mydatabase. We shall use phpmyadmin interface available in XAMPP.

如果您选择以编程方式创建数据库，请使用以下 Python 脚本 -

If you choose to create database programmatically, use following Python script −

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='')
conn.cursor().execute('CREATE DATABASE mydatabase')
conn.close()

在服务器上创建数据库后，我们现在可以声明模型，从而在其中创建映射表。

Once a database is created on the server, we can now declare a model and thereby, create a mapped table in it.

MySQLDatabase 对象需要服务器凭据，例如主机、端口、用户名和密码。

The MySQLDatabase object requires server credentials such as host, port, user name and password.

from peewee import *
db = MySQLDatabase('mydatabase', host='localhost', port=3306, user='root', password='')
class MyUser (Model):
   name=TextField()
   city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])
   age=IntegerField()
   class Meta:
      database=db
      db_table='MyUser'
db.connect()
db.create_tables([MyUser])

PhpMyAdmin Web 界面现在显示了已创建的 myuser 表。

The Phpmyadmin web interface now shows myuser table created.

Peewee - Using PostgreSQL

Peewee 也支持 PostgreSQL 数据库。它为该目的提供了 PostgresqlDatabase 类。在本章中，我们将看到如何借助 Peewee 模型连接到 Postgres 数据库并在其中创建一个表。

Peewee supports PostgreSQL database as well. It has PostgresqlDatabase class for that purpose. In this chapter, we shall see how we can connect to Postgres database and create a table in it, with the help of Peewee model.

就像 MySQL 的情况一样，使用 Peewee 的功能无法在 Postgres 服务器上创建数据库。必须使用 Postgres shell 或 PgAdmin 工具手动创建数据库。

As in case of MySQL, it is not possible to create database on Postgres server with Peewee’s functionality. The database has to be created manually using Postgres shell or PgAdmin tool.

首先，我们需要安装 Postgres 服务器。对于 Windows 操作系统，我们可以下载 https://get.enterprisedb.com/postgresql/postgresql-13.1-1-windows-x64.exe 并安装它。

First, we need to install Postgres server. For windows OS, we can download https://get.enterprisedb.com/postgresql/postgresql-13.1-1-windows-x64.exe and install.

接下来，使用 pip 安装程序安装 Postgres 的 Python 驱动程序，即 Psycopg2 软件包。

Next, install Python driver for Postgres – Psycopg2 package using pip installer.

pip install psycopg2

然后，从 PgAdmin 工具或 psql shell 启动服务器。我们现在可以创建数据库。运行以下 Python 脚本可在 Postgres 服务器上创建 mydatabase。

Then start the server, either from PgAdmin tool or psql shell. We are now in a position to create a database. Run following Python script to create mydatabase on Postgres server.

import psycopg2

conn = psycopg2.connect(host='localhost', user='postgres', password='postgres')
conn.cursor().execute('CREATE DATABASE mydatabase')
conn.close()

检查数据库是否已创建。在 psql shell 中，可以使用 \l 命令验证其是否已创建：

Check that the database is created. In psql shell, it can be verified with \l command −

要声明 MyUser 模型并在上述数据库中创建同名表，请运行以下 Python 代码：

To declare MyUser model and create a table of same name in above database, run following Python code −

from peewee import *

db = PostgresqlDatabase('mydatabase', host='localhost', port=5432, user='postgres', password='postgres')
class MyUser (Model):
   name=TextField()
   city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])
   age=IntegerField()
   class Meta:
      database=db
      db_table='MyUser'

db.connect()
db.create_tables([MyUser])

我们可以验证表是否已创建。在 shell 内，连接到 mydatabase 并获取其中的表列表。

We can verify that table is created. Inside the shell, connect to mydatabase and get list of tables in it.

要检查新创建的 MyUser 数据库的结构，请在 shell 中运行以下查询。

To check structure of newly created MyUser database, run following query in the shell.

Peewee - Defining Database Dynamically

如果你的数据库计划在运行时发生变化，请使用 DatabaseProxy 帮助程序更好地控制其初始化方式。DatabaseProxy 对象是一个占位符，数据库可以在运行时使用它进行选择。

If your database is scheduled to vary at run-time, use DatabaseProxy helper to have better control over how you initialise it. The DatabaseProxy object is a placeholder with the help of which database can be selected in run-time.

在以下示例中，将根据应用程序的配置设置选择合适的数据库。

In the following example, an appropriate database is selected depending on the application’s configuration setting.

from peewee import *
db_proxy = DatabaseProxy() # Create a proxy for our db.

class MyUser (Model):
   name=TextField()
   city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])
   age=IntegerField()
   class Meta:
      database=db_proxy
      db_table='MyUser'

# Based on configuration, use a different database.
if app.config['TESTING']:
   db = SqliteDatabase(':memory:')
elif app.config['DEBUG']:
   db = SqliteDatabase('mydatabase.db')
else:
   db = PostgresqlDatabase(
      'mydatabase', host='localhost', port=5432, user='postgres', password='postgres'
   )

# Configure our proxy to use the db we specified in config.
db_proxy.initialize(db)
db.connect()
db.create_tables([MyUser])

你还可以使用在数据库类和模型类中声明的 bind() 方法，在运行时将模型与任何数据库对象相关联。

You can also associate models to any database object during run-time using bind() method declared in both database class and model class.

以下示例在数据库类中使用 bind() 方法。

Following example uses bind() method in database class.

from peewee import *

class MyUser (Model):
   name=TextField()
   city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])
   age=IntegerField()

db = MySQLDatabase('mydatabase', host='localhost', port=3306, user='root', password='')
db.connect()
db.bind([MyUser])
db.create_tables([MyUser])

在 Model 类中也定义了 bind() 方法。

The same bind() method is also defined in Model class.

from peewee import *

class MyUser (Model):
   name=TextField()
   city=TextField(constraints=[SQL("DEFAULT 'Mumbai'")])
   age=IntegerField()

db = MySQLDatabase('mydatabase', host='localhost', port=3306, user='root', password='')
db.connect()
MyUser.bind(db)
db.create_tables([MyUser])

Peewee - Connection Management

默认情况下，数据库对象使用 autoconnect 参数设置为 True 创建。相反，为了以编程方式管理数据库连接，它最初被设置为 False。

Database object is created with autoconnect parameter set as True by default. Instead, to manage database connection programmatically, it is initially set to False.

db=SqliteDatabase("mydatabase", autoconnect=False)

数据库类具有 connect() 方法，用于建立与服务器上存在的数据库的连接。

The database class has connect() method that establishes connection with the database present on the server.

db.connect()

强烈建议在完成操作后关闭连接。

It is always recommended to close the connection at the end of operations performed.

db.close()

如果你试图打开一个已经打开的连接，Peewee 会引发 OperationError 。

If you try to open an already open connection, Peewee raises OperationError.

>>> db.connect()
True
>>> db.connect()
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File "c:\peewee\lib\site-packages\peewee.py", line 3031, in connect
      raise OperationalError('Connection already opened.')
peewee.OperationalError: Connection already opened.

要避免此错误，可以使用 reuse_if_open=True 作为 connect() 方法的参数。

To avoid this error, use reuse_if_open=True as argument to connect() method.

>>> db.connect(reuse_if_open=True)
False

在已经关闭的连接上调用 close() 不会导致错误。但是，你可以使用 is_closed() 方法检查连接是否已经关闭。

Calling close() on already closed connection won’t result error. You can however, check if the connection is already closed with is_closed() method.

>>> if db.is_closed()==True:
   db.connect()

True
>>>

不必在最后显示调用 db.close()，你也可以将数据库对象用作 context_manager 。

Instead of explicitly calling db.close() in the end, it is also possible to use database object as context_manager.

from peewee import *

db = SqliteDatabase('mydatabase.db', autoconnect=False)

class User (Model):
   user_id=TextField(primary_key=True)
   name=TextField()
   age=IntegerField()
   class Meta:
      database=db
      db_table='User'
with db:
   db.connect()
   db.create_tables([User])

Peewee - Relationships and Joins

Peewee 支持实现不同类型的 SQL JOIN 查询。它的 Model 类有一个 join() 方法，它会返回一个 Join 实例。

Peewee supports implementing different type of SQL JOIN queries. Its Model class has a join() method that returns a Join instance.

M1.joint(m2, join_type, on)

join 表将 M1 模型映射到 m2 模型，并返回 Join 类实例。on 参数默认是 None，它是一个用作连接谓词的表达式。

The joins table mapped with M1 model to that of m2 model and returns Join class instance. The on parameter is None by default and is expression to use as join predicate.

Join Types

Peewee 支持以下 Join 类型（默认为 INNER）。

Peewee supports following Join types (Default is INNER).

JOIN.INNER
JOIN.LEFT_OUTER
JOIN.RIGHT_OUTER
JOIN.FULL
JOIN.FULL_OUTER
JOIN.CROSS

为了演示 join() 方法的用法，首先声明以下模型：

To show use of join() method, we first declare following models −

db = SqliteDatabase('mydatabase.db')

class BaseModel(Model):
   class Meta:
      database = db

class Item(BaseModel):
   itemname = TextField()
      price = IntegerField()

class Brand(BaseModel):
   brandname = TextField()
      item = ForeignKeyField(Item, backref='brands')

class Bill(BaseModel):
   item = ForeignKeyField(Item, backref='bills')
   brand = ForeignKeyField(Brand, backref='bills')
   qty = DecimalField()

db.create_tables([Item, Brand, Bill])

Tables

接着，使用以下测试数据填充这些表：

Next, we populate these tables with following test data −

Item Table

item 表如下：

The item table is given below −

Brand Table

brand 表如下：

Given below is the brand table −

Bill Table

bill 表如下：

The bill table is as follows −

若要在 Brand 和 Item 表之间执行一个简单的连接操作，请执行以下代码：

To perform a simple join operation between Brand and Item tables, execute the following code −

qs=Brand.select().join(Item)
for q in qs:
print ("Brand ID:{} Item Name: {} Price: {}".format(q.id, q.brandname, q.item.price))

最终输出如下：

The resultant output will be as follows −

Brand ID:1 Item Name: Dell Price: 25000
Brand ID:2 Item Name: Epson Price: 12000
Brand ID:3 Item Name: HP Price: 25000
Brand ID:4 Item Name: iBall Price: 4000
Brand ID:5 Item Name: Sharp Price: 12000

Joining Multiple Tables

我们有一个 Bill 模型，它与 item 和 brand 模型有两个外键关系。若要从所有三个表中获取数据，请使用以下代码：

We have a Bill model having two foreign key relationships with item and brand models. To fetch data from all three tables, use following code −

qs=Bill.select().join(Brand).join(Item)
for q in qs:
print ("BillNo:{} Brand:{} Item:{} price:{} Quantity:{}".format(q.id, \
q.brand.brandname, q.item.itemname, q.item.price, q.qty))

基于我们的测试数据，将显示以下输出：

Following output will be displayed, based on our test data −

BillNo:1 Brand:HP Item:Laptop price:25000 Quantity:5
BillNo:2 Brand:Epson Item:Printer price:12000 Quantity:2
BillNo:3 Brand:iBall Item:Router price:4000 Quantity:5

Peewee - Subqueries

在 SQL 中，子查询是另一个查询的 WHERE 子句中的嵌入式查询。我们可以将子查询实现为外层模型的 select() 语句的 where 属性中的一个 model.select() 作为参数。

In SQL, a subquery is an embedded query in WHERE clause of another query. We can implement subquery as a model.select() as a parameter inside where attribute of outer model.select() statement.

为了演示 Peewee 中的子查询用法，让我们使用以下定义的模型：

To demonstrate use of subquery in Peewee, let us use defined following models −

from peewee import *
db = SqliteDatabase('mydatabase.db')

class BaseModel(Model):
   class Meta:
      database = db

class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()

class Branches(BaseModel):
   RollNo = IntegerField()
   Faculty = TextField()

db.create_tables([Contacts, Branches])

在创建表后，用以下示例数据填充它们：

After tables are created, they are populated with following sample data −

Contacts table

contacts 表如下：

The contacts table is given below −

为了仅显示 ETC 教师注册过的 RollNo 的联系人和城市，以下代码会生成一个 SELECT 查询，其中 WHERE 子句中包含另一个 SELECT 查询。

In order to display name and city from contact table only for RollNo registered for ETC faculty, following code generates a SELECT query with another SELECT query in its WHERE clause.

#this query is used as subquery
faculty=Branches.select(Branches.RollNo).where(Branches.Faculty=="ETC")
names=Contacts.select().where (Contacts.RollNo .in_(faculty))

print ("RollNo and City for Faculty='ETC'")
for name in names:
   print ("RollNo:{} City:{}".format(name.RollNo, name.City))

db.close()

以上代码将显示以下结果：

Above code will display the following result:

RollNo and City for Faculty='ETC'
RollNo:103 City:Indore
RollNo:104 City:Nasik
RollNo:108 City:Delhi
RollNo:110 City:Nasik

Peewee - Sorting

可以使用 order_by 子句及其模型的 select() 方法从表中选择记录。此外，通过将 desc() 附加到要对其执行排序的字段属性上，可以按降序收集记录。

It is possible to select records from a table using order_by clause along with model’s select() method. Additionally, by attaching desc() to the field attribute on which sorting is to be performed, records will be collected in descending order.

Example

以下代码按城市名称的升序显示联系表中的记录。

Following code display records from contact table in ascending order of City names.

rows=Contacts.select().order_by(Contacts.City)
print ("Contact list in order of city")
for row in rows:
   print ("RollNo:{} Name: {} City:{}".format(row.RollNo,row.Name, row.City))

Output

以下是有序列表，按城市名称的升序排列。

Here is the sorted list which is arranged according to ascending order of city name.

Contact list in order of city
RollNo:107 Name: Beena City:Chennai
RollNo:102 Name: Amar City:Delhi
RollNo:108 Name: John City:Delhi
RollNo:103 Name: Raam City:Indore
RollNo:101 Name: Anil City:Mumbai
RollNo:106 Name: Hema City:Nagpur
RollNo:104 Name: Leena City:Nasik
RollNo:109 Name: Jaya City:Nasik
RollNo:110 Name: Raja City:Nasik
RollNo:105 Name: Keshav City:Pune

Example

以下代码按姓名字段的降序显示列表。

Following code displays list in descending order of Name field.

rows=Contacts.select().order_by(Contacts.Name.desc())
print ("Contact list in descending order of Name")
for row in rows:
   print ("RollNo:{} Name: {} City:{}".format(row.RollNo,row.Name, row.City))

Output

输出如下 −

The output is as follows −

Contact list in descending order of Name
RollNo:110 Name: Raja City:Nasik
RollNo:103 Name: Raam City:Indore
RollNo:104 Name: Leena City:Nasik
RollNo:105 Name: Keshav City:Pune
RollNo:108 Name: John City:Delhi
RollNo:109 Name: Jaya City:Nasik
RollNo:106 Name: Hema City:Nagpur
RollNo:107 Name: Beena City:Chennai
RollNo:101 Name: Anil City:Mumbai
RollNo:102 Name: Amar City:Delhi

Peewee - Counting and Aggregation

我们可以通过附加 count() 方法来查找任何 SELECT 查询中报告的记录数。例如，以下语句返回 Contacts 表中 City=’Nasik’ 的行数。

We can find number of records reported in any SELECT query by attaching count() method. For example, following statement returns number of rows in Contacts table with City=’Nasik’.

qry=Contacts.select().where (Contacts.City=='Nasik').count()
print (qry)

Example

SQL在 SELECT 查询中具有 GROUP BY 子句。Peewee 以 group_by() 方法的形式支持它。以下代码返回 Contacts 表中按城市统计的名称。

SQL has GROUP BY clause in SELECT query. Peewee supports it in the form of group_by() method. Following code returns city wise count of names in Contacts table.

from peewee import *

db = SqliteDatabase('mydatabase.db')
class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()
   class Meta:
      database = db

db.create_tables([Contacts])

qry=Contacts.select(Contacts.City, fn.Count(Contacts.City).alias('count')).group_by(Contacts.City)
print (qry.sql())
for q in qry:
   print (q.City, q.count)

Peewee 发出的 SELECT 查询将如下所示：

The SELECT query emitted by Peewee will be as follows −

('SELECT "t1"."City", Count("t1"."City") AS "count" FROM "contacts" AS "t1" GROUP BY "t1"."City"', [])

Output

根据 Contacts 表中的样本数据，显示以下输出：

As per sample data in Contacts table, following output is displayed −

Chennai 1
Delhi   2
Indore  1
Mumbai  1
Nagpur  1
Nasik   3
Pune    1

Peewee - SQL Functions

美国国家标准协会 (ANSI) 结构化查询语言 (SQL) 标准定义了多种 SQL 函数。

American National Standards Institute (ANSI) Structured Query Language (SQL) standard defines many SQL functions.

以下之类的聚合函数在 Peewee 中很有用。

Aggregate functions like the following are useful in Peewee.

AVG() - Returns the average value.
COUNT() - Returns the number of rows.
FIRST() - Returns the first value.
LAST() - Returns the last value.
MAX() - Returns the largest value.
MIN() - Returns the smallest value.
SUM() - Returns the sum.

为了实现这些 SQL 函数，Peewee 有一个 SQL 辅助函数 fn()。上面示例中，我们用它来查找每个城市的记录数。

In order to implement these SQL functions, Peewee has a SQL helper function fn(). In above example, we used it to find count of records for each city.

以下示例构建了一个采用 SUM() 函数的 SELECT 查询。

Following example builds a SELECT query that employs SUM() function.

使用前面定义的模型中的 Bill 和 Item 表，我们将显示如 Bill 表中输入的每一项的数量总和。

Using Bill and Item tables from models defined earlier, we shall display sum of quantity of each item as entered in Bill table.

Item table

带有数据的 item 表如下所示 −

The item table with the data is given below −

Id	Item Name	Price
1	Laptop	25000
2	Printer	12000
3	Router	4000

Bill table

bill 表如下：

The bill table is as follows −

Id	Item_id	Brand_id	Quantity
1	1	3	5
2	2	2	2
3	3	4	5
4	2	2	6
5	3	4	3
6	1	3	1

Example

我们创建 Bill 和 Item 表之间的连接，从 Item 表中选择项目名称，从 Bill 表中选择数量总和。

We create a join between Bill and Item table, select item name from Item table and sum of quantity from Bill table.

from peewee import *
db = SqliteDatabase('mydatabase.db')

class BaseModel(Model):
   class Meta:
      database = db

class Item(BaseModel):
   itemname = TextField()
   price = IntegerField()

class Brand(BaseModel):
   brandname = TextField()
   item = ForeignKeyField(Item, backref='brands')

class Bill(BaseModel):
   item = ForeignKeyField(Item, backref='bills')
   brand = ForeignKeyField(Brand,      backref='bills')
   qty = DecimalField()

db.create_tables([Item, Brand, Bill])

qs=Bill.select(Item.itemname, fn.SUM(Bill.qty).alias('Sum'))
   .join(Item).group_by(Item.itemname)
print (qs)
for q in qs:
   print ("Item: {} sum: {}".format(q.item.itemname, q.Sum))

db.close()

以上的脚本执行了以下 SELECT 查询 −

Above script executes the following SELECT query −

SELECT "t1"."itemname", SUM("t2"."qty") AS "Sum" FROM "bill" AS "t2"
INNER JOIN "item" AS "t1" ON ("t2"."item_id" = "t1"."id") GROUP BY "t1"."itemname"

Output

相应地，输出如下 −

Accordingly, the output is as follows −

Item: Laptop sum: 6
Item: Printer sum: 8
Item: Router sum: 8

Peewee - Retrieving Row Tuples/Dictionaries

无需创建模型实例就可以遍历 resultset。这可以通过使用以下方法实现 −

It is possible to iterate over the resultset without creating model instances. This may be achieved by using the following −

tuples() method.
dicts() method.

Example

以元组形式返回 SELECT 查询中的数据，请使用 tuples() 方法。

To return data of fields in SELECT query as collection of tuples, use tuples() method.

qry=Contacts.select(Contacts.City, fn.Count(Contacts.City).alias('count'))
   .group_by(Contacts.City).tuples()
lst=[]
for q in qry:
   lst.append(q)
print (lst)

Output

输出如下 −

The output is given below −

[
   ('Chennai', 1),
   ('Delhi', 2),
   ('Indore', 1),
   ('Mumbai', 1),
   ('Nagpur', 1),
   ('Nasik', 3),
   ('Pune', 1)
]

Example

获取字典对象的集合 −

To obtain collection of dictionary objects −

qs=Brand.select().join(Item).dicts()
lst=[]
for q in qs:
   lst.append(q)
print (lst)

Output

输出如下 −

The output is stated below −

[
   {'id': 1, 'brandname': 'Dell', 'item': 1},
   {'id': 2, 'brandname': 'Epson', 'item': 2},
   {'id': 3, 'brandname': 'HP', 'item': 1},
   {'id': 4, 'brandname': 'iBall', 'item': 3},
   {'id': 5, 'brandname': 'Sharp', 'item': 2}
]

Peewee - User defined Operators

Peewee 拥有 Expression 类，借助该类，我们可以向 Peewee 的运算符列表中添加任何自定义运算符。Expression 的构造函数需要三个参数，左操作数、运算符和右操作数。

Peewee has Expression class with the help of which we can add any customized operator in Peewee’s list of operators. Constructor for Expression requires three arguments, left operand, operator and right operand.

op=Expression(left, operator, right)

使用 Expression 类，我们定义一个 mod() 函数，它接受 left 和 right 的参数，以及运算符 '%”。

Using Expression class, we define a mod() function that accepts arguments for left and right and ‘%’ as operator.

from peewee import Expression # the building block for expressions

def mod(lhs, rhs):
   return Expression(lhs, '%', rhs)

Example

我们可以在 SELECT 查询中使用它来获取 Contacts 表中 id 为偶数的记录列表。

We can use it in a SELECT query to obtain list of records in Contacts table with even id.

from peewee import *
db = SqliteDatabase('mydatabase.db')

class BaseModel(Model):
   class Meta:
      database = db

class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()

db.create_tables([Contacts])

from peewee import Expression # the building block for expressions

def mod(lhs, rhs):
   return Expression(lhs,'%', rhs)
qry=Contacts.select().where (mod(Contacts.id,2)==0)
print (qry.sql())
for q in qry:
   print (q.id, q.Name, q.City)

此代码将发出由以下字符串表示的 SQL 查询 −

This code will emit following SQL query represented by the string −

('SELECT "t1"."id", "t1"."RollNo", "t1"."Name", "t1"."City" FROM "contacts" AS "t1" WHERE (("t1"."id" % ?) = ?)', [2, 0])

Output

因此，输出如下 −

Therefore, the output is as follows −

2  Amar Delhi
4  Leena Nasik
6  Hema Nagpur
8  John Delhi
10 Raja Nasik

Peewee - Atomic Transactions

Peewee 的 database 类拥有 atomic() 方法，它创建了一个上下文管理器。它开启一个新事务。在上下文块内部，可以根据事务是否成功完成或遇到异常来提交或回滚事务。

Peewee’s database class has atomic() method that creates a context manager. It starts a new transaction. Inside the context block, it is possible to commit or rollback the transaction depending upon whether it has been successfully done or it encountered exception.

with db.atomic() as transaction:
   try:
      User.create(name='Amar', age=20)
      transaction.commit()
   except DatabaseError:
      transaction.rollback()

atomic() 也可作为装饰器使用。

The atomic() can also be used as decorator.

@db.atomic()
def create_user(nm,n):
   return User.create(name=nm, age=n)

create_user('Amar', 20)

多个原子事务块也可以嵌套。

More than one atomic transaction blocks can also be nested.

with db.atomic() as txn1:
   User.create('name'='Amar', age=20)

   with db.atomic() as txn2:
      User.get(name='Amar')

Peewee - Database Errors

Python 的 DB-API 标准（PEP 249 建议）指定了任何 DB-API 兼容模块（如 pymysql、pyscopg2 等）要定义的异常类类型。

Python’s DB-API standard (recommended by PEP 249) specifies the types of Exception classes to be defined by any DB-API compliant module (such as pymysql, pyscopg2, etc.).

Peewee API 为这些异常提供了易于使用的包装器。 PeeweeException 是 Peewee API 中定义了以下 Exception 类的基类 −

Peewee API provides easy-to-use wrappers for these exceptions. PeeweeException is the base classes from which following Exception classes has been defined in Peewee API −

DatabaseError
DataError
IntegrityError
InterfaceError
InternalError
NotSupportedError
OperationalError
ProgrammingError

我们可以从 Peewee 实现上述异常，而不是尝试 DB-API 特定的异常。

Instead of DB-API specific exceptions to be tried, we can implement above ones from Peewee.

Peewee - Query Builder

Peewee 还提供一个非 ORM API 来访问数据库。我们可以将数据库表和列绑定到 Peewee 中定义的 Table 和 Column 对象，而不是定义模型和字段，并使用它们来执行查询。

Peewee also provides a non-ORM API to access the databases. Instead of defining models and fields, we can bind the database tables and columns to Table and Column objects defined in Peewee and execute queries with their help.

首先，声明一个与数据库中的表对应的 Table 对象。你必须指定表名和列的列表。还可以选择提供主键。

To begin with, declare a Table object corresponding to the one in our database. You have to specify table name and list of columns. Optionally, a primary key can also be provided.

Contacts=Table('Contacts', ('id', 'RollNo', 'Name', 'City'))

这个表对象通过 bind() 方法与数据库绑定。

This table object is bound with the database with bind() method.

Contacts=Contacts.bind(db)

Example

现在，我们可以在这个表对象上使用 select() 方法设置一个 SELECT 查询，并迭代结果集，如下所示：

Now, we can set up a SELECT query on this table object with select() method and iterate over the resultset as follows −

names=Contacts.select()
for name in names:
   print (name)

Output

默认情况下，行以字典的形式返回。

The rows are by default returned as dictionaries.

{'id': 1,  'RollNo': 101, 'Name': 'Anil', 'City': 'Mumbai'}
{'id': 2,  'RollNo': 102, 'Name': 'Amar', 'City': 'Delhi'}
{'id': 3,  'RollNo': 103, 'Name': 'Raam', 'City': 'Indore'}
{'id': 4,  'RollNo': 104, 'Name': 'Leena', 'City': 'Nasik'}
{'id': 5,  'RollNo': 105, 'Name': 'Keshav', 'City': 'Pune'}
{'id': 6,  'RollNo': 106, 'Name': 'Hema', 'City': 'Nagpur'}
{'id': 7,  'RollNo': 107, 'Name': 'Beena', 'City': 'Chennai'}
{'id': 8,  'RollNo': 108, 'Name': 'John', 'City': 'Delhi'}
{'id': 9,  'RollNo': 109, 'Name': 'Jaya', 'City': 'Nasik'}
{'id': 10, 'RollNo': 110, 'Name': 'Raja', 'City': 'Nasik'}

如果需要的话，它们可以作为元组、命名元组或对象获取。

If needed, they can be obtained as tuples, namedtuples or objects.

Tuples

程序如下：

The program is as follows −

Example

names=Contacts.select().tuples()
for name in names:
   print (name)

Output

输出如下 −

The output is given below −

(1, 101, 'Anil', 'Mumbai')
(2, 102, 'Amar', 'Delhi')
(3, 103, 'Raam', 'Indore')
(4, 104, 'Leena', 'Nasik')
(5, 105, 'Keshav', 'Pune')
(6, 106, 'Hema', 'Nagpur')
(7, 107, 'Beena', 'Chennai')
(8, 108, 'John', 'Delhi')
(9, 109, 'Jaya', 'Nasik')
(10, 110, 'Raja', 'Nasik')

Namedtuples

程序如下：

The program is stated below −

Example

names=Contacts.select().namedtuples()
for name in names:
   print (name)

Output

输出如下 −

The output is given below −

Row(id=1, RollNo=101, Name='Anil', City='Mumbai')
Row(id=2, RollNo=102, Name='Amar', City='Delhi')
Row(id=3, RollNo=103, Name='Raam', City='Indore')
Row(id=4, RollNo=104, Name='Leena', City='Nasik')
Row(id=5, RollNo=105, Name='Keshav', City='Pune')
Row(id=6, RollNo=106, Name='Hema', City='Nagpur')
Row(id=7, RollNo=107, Name='Beena', City='Chennai')
Row(id=8, RollNo=108, Name='John', City='Delhi')
Row(id=9, RollNo=109, Name='Jaya', City='Nasik')
Row(id=10, RollNo=110, Name='Raja', City='Nasik')

要插入一条新记录，INSERT 查询的构建如下所示：

To insert a new record, INSERT query is constructed as follows −

id = Contacts.insert(RollNo=111, Name='Abdul', City='Surat').execute()

如果要添加的记录列表存储为字典列表或元组列表，则可以批量添加它们。

If a list of records to be added is stored either as a list of dictionaries or as list of tuples, they can be added in bulk.

Records=[{‘RollNo’:112, ‘Name’:’Ajay’, ‘City’:’Mysore’},
   {‘RollNo’:113, ‘Name’:’Majid’,’City’:’Delhi’}}

Or

Records=[(112, ‘Ajay’,’Mysore’), (113, ‘Majid’, ‘Delhi’)}

INSERT 查询的编写方式如下：

The INSERT query is written as follows −

Contacts.insert(Records).execute()

Peewee Table 对象有 update() 方法来实现 SQL UPDATE 查询。要将 Nasik 中所有记录的 City 更改为 Nagar，我们使用以下查询。

The Peewee Table object has update() method to implement SQL UPDATE query. To change City for all records from Nasik to Nagar, we use following query.

Contacts.update(City='Nagar').where((Contacts.City=='Nasik')).execute()

最后，Peewee 中的 Table 类还有 delete() 方法来实现 SQL 中的 DELETE 查询。

Finally, Table class in Peewee also has delete() method to implement DELETE query in SQL.

Contacts.delete().where(Contacts.Name=='Abdul').execute()

Peewee - Integration with Web Frameworks

Peewee 可以与大多数 Python Web 框架 API 无缝协作。每当 Web 服务器网关接口 (WSGI) 服务器从客户端接收连接请求时，就会建立与数据库的连接，然后在响应传递之后关闭连接。

Peewee can work seamlessly with most of the Python web framework APIs. Whenever the Web Server Gateway Interface (WSGI) server receives a connection request from client, the connection with database is established, and then the connection is closed upon delivering a response.

在基于 Flask 的 Web 应用程序中使用时，连接会对 @app.before_request 修饰符产生影响，并在 @app.teardown_request 上断开。

While using in a Flask based web application, connection has an effect on @app.before_request decorator and is disconnected on @app.teardown_request.

from flask import Flask
from peewee import *

db = SqliteDatabase('mydatabase.db')
app = Flask(__name__)

@app.before_request
def _db_connect():
   db.connect()

@app.teardown_request
def _db_close(exc):
   if not db.is_closed():
      db.close()

Peewee API 也可以在 Django. 中使用。为此，请在 Django 应用中添加一个中间件。

Peewee API can also be used in Django. To do so, add a middleware in Django app.

def PeeweeConnectionMiddleware(get_response):
   def middleware(request):
      db.connect()
      try:
         response = get_response(request)
      finally:
         if not db.is_closed():
            db.close()
      return response
   return middleware

中间件添加到 Django 的设置模块中。

Middleware is added in Django’s settings module.

# settings.py
MIDDLEWARE_CLASSES = (
   # Our custom middleware appears first in the list.
   'my_blog.middleware.PeeweeConnectionMiddleware',
   #followed by default middleware list.
   ..
)

Peewee 可以轻松地与 Bottle、Pyramid 和 Tornado 等其他框架一起使用。

Peewee can be comfortably used with other frameworks such as Bottle, Pyramid and Tornado, etc.

Peewee - SQLite Extensions

Peewee 带有一个 Playhouse 名称空间。它是一个由各种扩展模块组成的集合。其中之一是 playhouse.sqlite_ext 模块。它主要定义 SqliteExtDatabase 类，该类继承 SqliteDatabase 类，支持以下附加功能：

Peewee comes with a Playhouse namespace. It is a collection of various extension modules. One of them is a playhouse.sqlite_ext module. It mainly defines SqliteExtDatabase class which inherits SqliteDatabase class, supports following additional features −

Features of SQLite Extensions

Peewee 支持的 SQLite 扩展功能如下 −

The features of SQLite Extensions which are supported by Peewee are as follows −

Full-text search.
JavaScript Object Notation (JSON) extension integration.
Closure table extension support.
LSM1 extension support.
User-defined table functions.
Support for online backups using backup API: backup_to_file().
BLOB API support, for efficient binary data storage.

如果特别 JSONField 声明为某个字段属性，则可以存储 JSON 数据。

JSON data can be stored, if a special JSONField is declared as one of the field attributes.

class MyModel(Model):
   json_data = JSONField(json_dumps=my_json_dumps)

要激活全文搜索，模型可以使用 DocIdField 来定义主键。

To activate full-text search, the model can have DocIdField to define primary key.

class NoteIndex(FTSModel):
   docid = DocIDField()
   content = SearchField()

   class Meta:
      database = db

FTSModel 是 VirtualModel 的子类，可在 http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#VirtualModel 获得，与 FTS3 和 FTS4 全文搜索扩展一起使用。Sqlite 将所有列类型都视为 TEXT（尽管你可以存储其他数据类型，Sqlite 会将它们视为文本）。

FTSModel is a Subclass of VirtualModel which is available at http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#VirtualModel to be used with the FTS3 and FTS4 full-text search extensions. Sqlite will treat all column types as TEXT (although, you can store other data types, Sqlite will treat them as text).

SearchField 是一个 Field 类，用于模型中表示全文搜索虚拟表的列。

SearchField is a Field-class to be used for columns on models representing full-text search virtual tables.

SqliteDatabase 支持 AutoField 以增加主键。但是，SqliteExtDatabase 支持 AutoIncrementField 以确保主键始终呈单调递增，而不论是否删除行。

SqliteDatabase supports AutoField for increasing primary key. However, SqliteExtDatabase supports AutoIncrementField to ensure that primary always increases monotonically, irrespective of row deletions.

playhouse 命名空间 (playhouse.sqliteq) 中的 SqliteQ 模块定义了 SqliteExeDatabase 的一个子类，以处理对 SQLite 数据库的序列化并发写操作。

SqliteQ module in playhouse namespace (playhouse.sqliteq) defines subclass of SqliteExeDatabase to handle serialised concurrent writes to a SQlite database.

另一方面，playhouse.apsw 模块提供了对 apsw sqlite 驱动的支持。另一个 Python SQLite 封装器 (APSW) 速度快且可以处理嵌套事务，而这些事务是由你的代码显式管理的。

On the other hand, playhouse.apsw module carries support for apsw sqlite driver. Another Python SQLite Wrapper (APSW) is fast and can handle nested transactions, that are managed explicitly by you code.

from apsw_ext import *
db = APSWDatabase('testdb')

class BaseModel(Model):
   class Meta:
      database = db

class MyModel(BaseModel):
   field1 = CharField()
   field2 = DateTimeField()

Peewee - PostgreSQL and MySQL Extensions

playhouse.postgres_ext 中定义的帮助程序启用了额外的 PostgreSQL 功能。该模块定义了 PostgresqlExtDatabase 类，并提供了以下额外字段类型，这些类型专门用于声明要映射到 PostgreSQL 数据库表的模型。

Additional PostgreSQL functionality is enabled by helpers which are defined in playhouse.postgres_ext module. This module defines PostgresqlExtDatabase class and provides the following additional field types to be exclusively used for declaration of model to be mapped against PostgreSQL database table.

Features of PostgreSQL Extensions

Peewee 支持的 PostgreSQL 扩展功能如下 −

The features of PostgreSQL Extensions which are supported by Peewee are as follows −

ArrayField field type, for storing arrays.
HStoreField field type, for storing key/value pairs.
IntervalField field type, for storing timedelta objects.
JSONField field type, for storing JSON data.
BinaryJSONField field type for the jsonb JSON data type.
TSVectorField field type, for storing full-text search data.
DateTimeTZField field type, a timezone-aware datetime field.

此模块中的其他特定于 Postgres 的功能旨在提供。

Additional Postgres-specific features in this module are meant to provide.

hstore support.
server-side cursors.
full-text search.

Postgres hstore 是一个键值存储，可嵌入到表中作为 HStoreField 类型字段之一。要启用 hstore 支持，请创建具有 register_hstore=True 参数的数据库实例。

Postgres hstore is a key:value store that can be embedded in a table as one of the fields of type HStoreField. To enable hstore support, create database instance with register_hstore=True parameter.

db = PostgresqlExtDatabase('mydatabase', register_hstore=True)

使用一个 HStoreField 定义一个模型。

Define a model with one HStoreField.

class Vehicles(BaseExtModel):
   type = CharField()
   features = HStoreField()

按如下方式创建一个模型实例 −

Create a model instance as follows −

v=Vechicle.create(type='Car', specs:{'mfg':'Maruti', 'Fuel':'Petrol', 'model':'Alto'})

要访问 hstore 值 −

To access hstore values −

obj=Vehicle.get(Vehicle.id=v.id)
print (obj.features)

MySQL Extensions

由 playhouse.mysql_ext 模块中定义的 MySQLConnectorDatabase 提供 MysqlDatabase 类的备用实现。它使用 Python 的 DB-API 兼容官方 mysql/python connector 。

Alternate implementation of MysqlDatabase class is provided by MySQLConnectorDatabase defined in playhouse.mysql_ext module. It uses Python’s DB-API compatible official mysql/python connector.

from playhouse.mysql_ext import MySQLConnectorDatabase

db = MySQLConnectorDatabase('mydatabase', host='localhost', user='root', password='')

Peewee - Using CockroachDB

CockroachDB 或蟑螂数据库 (CRDB) 是由计算机软件公司 Cockroach Labs 开发的。它是一个可扩展的、持续复制的事务性数据存储，旨在将数据副本存储在多个位置，以提供快速访问。

CockroachDB or Cockroach Database (CRDB) is developed by computer software company Cockroach Labs. It is a scalable, consistently-replicated, transactional datastore which is designed to store copies of data in multiple locations in order to deliver speedy access.

Peewee 通过 playhouse.cockroachdb 扩展模块中定义的 CockroachDatabase 类提供对该数据库的支持。该模块包含 CockroachDatabase 的定义，它是核心模块中 PostgresqlDatabase 类的子类。

Peewee provides support to this database by way of CockroachDatabase class defined in playhouse.cockroachdb extension module. The module contains definition of CockroachDatabase as subclass of PostgresqlDatabase class from the core module.

此外，还有 run_transaction() 方法，它在事务内运行一个函数并提供自动客户端重试逻辑。

Moreover, there is run_transaction() method which runs a function inside a transaction and provides automatic client-side retry logic.

Field Classes

该扩展还具有某些特殊字段类，可用作 CRDB 兼容模型中的属性。

The extension also has certain special field classes that are used as attribute in CRDB compatible model.

UUIDKeyField - A primary-key field that uses CRDB’s UUID type with a default randomly-generated UUID.
RowIDField - A primary-key field that uses CRDB’s INT type with a default unique_rowid().
JSONField - Same as the Postgres BinaryJSONField.
ArrayField - Same as the Postgres extension, but does not support multi-dimensional arrays.