Peewee 简明教程

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)