PEEWEE 的数据库设计与实践

前言

最近在测试 peewee，因此简单总结了下 peewee 在使用中的重点模型的实现方式、注意事项、以及一些应该会比较常用的技巧。

关于数据库设计，当然是需要讲到 one2one, one2many, many2many 的实践。另外，除了这几个典型的应用，还另外说些可能遇到的问题。

当提及ORM当然是会提及数据和业务分层的问题。peewee 应该是介乎 table gateway 和 active record 的模型。而具体它的情况要视项目实践而定。而在本文中（也在我的项目使用情况下），对 peewee 的使用应该是非纯粹的数据读写，而是会加上直接关系数据转换的弱的业务逻辑（如 type_id 对应的类型属性、分类等等）。关于非常强的业务逻辑（由于业务操作而导致的各种数据更新操作），是应该放在另外的层级上的。这里就不再对此问题详述了。

阅读本文前建议先阅读官方的 cookbook:

http://peewee.readthedocs.org/en/latest/peewee/cookbook.html

peewee 的入门

peewee 是一个非常精简的 Python ORM 库。它很容易上手，代码也很精简易读。快速上手 peewee 把 cookbook 都过一遍基本都没太大问题了。

peewee 的官方示例 app 基于 flask 。这或许是团队不熟悉的玩意。有兴趣的可以细看。

使用 ORM 很实际、但是也是最核心的问题在于：必须非常清楚你利用库进行的动作时，它为你输出的 SQL 是什么，否则非常容易写出极其低效率的代码。如果你发现自己的代码效率很低，可以观察下自己的代码语句所造成的数据库读写是否高效。

one to one

one to one 实例的太直接太简单了。这里顺路提一下。在这个范例中，一个 user 归属于一个 group（的确这样不太实际，绝大部分应用都要求一个用户归属了多个组，这里就做个范例吧）。因此，group_id 代表用户的分组信息，直接放在 Users 表中。

注意的是，在 select 的时候，尽可能指定需要的域（如下的 .select(Users.name, Groups.name) ，否则 peewee 将会 select 出所有在 Model 中定义好的 field 。

show you the code.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `fullname` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `users` VALUES (1,'otto',1),(2,'zjj',2);

CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
INSERT INTO `groups` VALUES (1,'oss'),(2,'dev');

peewee 中的 Model 定义：

class Groups(BaseModel):
    id = IntegerField()
    name = CharField(null=True)

    class Meta:
        db_table = 'groups'

class Users(BaseModel):
    id = IntegerField()
    name = CharField()
    group = ForeignKeyField(Groups, db_column='group_id')

    class Meta:
        db_table = 'users'

对于 Model的使用：

for user in Users.select(Users.name, Groups.name).join(Groups):
    print 'User (%s) in Group(%s)' % (user.name, user.group.name)
# output:
# User (otto) in Group(oss)
# User (zjj) in Group(dev)
这个很直接。没什么可说的了。 实际上这里执行了这一句 SQL:

SELECT t1.`name`, t2.`name`
FROM `users` AS t1
INNER JOIN `groups` AS t2
ON (t1.`group_id` = t2.`id`)

one to one (no joining)

对于 one to one，有个非常值得说的案例，如下：

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT '',
  `content` varchar(100) DEFAULT '',
  `type_id` int(11) default 0,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into tasks (`id`, `name`, `content`, `type_id`)
values
(1, 'task_1', 'content_1', 0),
(2, 'task_2', 'content_2', 1);

很多时候，对于一些数据的类型或者其他内容的数据，很多时候并不会去另外建表说明这个 type 类型的 id 与内容定义（0是未知、1是已完成、等等）；或者，维护旧系统的时候，会发现很多莫名其妙的域指定了 id，虽然这个 type 类信息并不庞大（可能就是 0 1 2 3 四种类型），但是显然非常不利于维护。

如：在某些维护得很不好的系统中到处有这样的代码：

select id, name, content from tasks where type = 0;

type 0 是啥？在哪里可以得知？项目代码里为了查验这个对应关系，在多个地方出现一个小 dict 来做翻译。代码可维护性就是这样降低的。即使良好一些的做法是在某个配置文件里写成一个 dict ，但是还是不尽如人意。

如：

class Tasks(BaseModel):
    id = IntegerField()
    name = CharField()
    content = CharField()
    type = IntegerField(db_column='type_id')

    class Meta:
        db_table = 'tasks'

for task in Tasks.select(Tasks.name, Tasks.type):
    print task.type
# output
# 0
# 1

默认取得这个 type 的时候，也只是一个 int 而已。获取到这个 int 的时候，需要进行翻译。

而在使用 peewee 的时候，我们可以拓展此域，在拓展的域中进行此类判断：

http://peewee.readthedocs.org/en/latest/peewee/models.html#creating-a-custom-field

拓展下 type 的这个整数型域，使得它拥有对特定逻辑的处理（这个时候，绝大部分场景下都是较弱的业务逻辑）。代码如下：

class TaskTypeField(Field):

    db_field = 'task_type'
    # type_id 对应的 类型名称
    type_dict = {
        0: 'other',
        1: 'finished',
        2: 'waiting',
    }
    type_value_dict = { v:k for k, v in type_dict.iteritems() }

    def db_value(self, value):
        return self.type_value_dict.get(value, 0)

    def python_value(self, value):
        return self.type_dict.get(int(value), 'other')

class Tasks(BaseModel):
    id = IntegerField()
    name = CharField()
    content = CharField()
    type = TaskTypeField(db_column='type_id')

    class Meta:
        db_table = 'tasks'

拓展而输出的TaskTypeField 类来处理关于 type_id 的逻辑，使得代码里不需要再重复对类型到 ID 多次进行。这样既容易维护，也更容易使用，读写都可以直接使用 type 的名称。

读：

for task in Tasks.select(Tasks.id, Tasks.name, Tasks.type): print task.type

直接写入 other 内容，在数据库中存入其对应的整形。

这样，显然会让代码更容易读，更容易维护，而且性能并不会被影响。当然，如果是有很多的 type、类型信息有其他读写的需求，还是需要单独做成一个表进行管理：但是这个也不太困难更新，更新 field 为 ForeignKey 即可。

one to many

one to many 的实现在于，把 one 的 id 放在每个 many 的个体中。

这个范例也非常简单。一个 user 会有多个 message。因此每条 message 会有一个 user_id。

one to many 关键只是在于自动生成的 set 变量（如下的 messages_set）。

class Users(BaseModel):
    id = IntegerField()
    name = CharField()

    class Meta:
        db_table = 'users'

class Messages(BaseModel):
    id = IntegerField()
    content = CharField()
    user = ForeignKeyField(Users, db_column='user_id')

    class Meta:
        db_table = 'messages'

# 1
for user in Users.select(Users.name, Messages.content).join(Messages):
    print user.name, user.messages.content

# 2
for user in Users.select(Users.id, Users.name):
    print user.name
    for


    
 message in user.messages_set:
       print '     ', message.content

显然我给的两个示例区别很明显：

第一个用 join 的出的结果以 message 为行单位，并不太直接方便使用，而且，这里给了一个inner join 的示例，而很多时候，使用 outer join 才是业务正确的。但此次检索只进行了一次数据库操作；第二个会更适合应用和输出，它和上面第一例的区别在于：它进行了多次 sql，一次获取所有 user，然后 nofusers 次获取各个 user 的 message。

one to many(no joining)

有些时候类似上面的 one to one (no joining) 的例子中讲到的。假设现在我们的 task 的类型是不唯一的，即某单可能会有多个类型。而且，此时也并不希望再多建个表。这种需求最常见的实现方式是：给这个 tasks 表加一个 bit 的域，利用二进制的与或操作而获取到此信息（也是相当于一个 one to many的形式）。但是显然这样的与或操作怎么去安排也是个问题。

这里我有个方案，还是拓展 field ，在 field 中对此 int 进行操作，从而使得对数据对象操作的时候，有更直接的接口，而且也更便于维护：

数据表如下：

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT '',
  `content` varchar(100) DEFAULT '',
  `types` tinyint(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


insert tasks(id, name, content, types)
values
(1, 'task1', 'content1', 1),
(2, 'task2', 'content2', 10),
(3, 'task3', 'content3', 7);

显然此时的 types 域用以做一个 bitmap 的操作。但是从数据库获取到的数据，是无法得知任何信息的（1、10、7 对应的是多个 type）。此时，拓展这个 types 域，加上相关的 domain 操作。

class TaskTypeField(Field):

    db_field = 'task_type'

    # 定义类型对应的位值
    type_mask = {
        'other' : 2**0,
        'useless' : 2**1,
        'waiting' : 2**2,
        'informed' : 2**3,
    }

    def db_value(self, value):
        types_bit = 0
        for type_name in value:
            type_bit = self.type_mask.get(type_name, 0)
            types_bit |= type_bit
        return types_bit

    def python_value(self, value):
        types = []
        for type_name, type_bit in self.type_mask.iteritems():
            if value & type_bit :
                types.append(type_name)
        return types

class Tasks(BaseModel):
    id = IntegerField()
    name = CharField()
    content = CharField()
    types = TaskTypeField(db_column='types')

    class Meta:
        db_table = 'tasks'

定义了 TaskTypeField 域，在 TaskTypeField 内对二进制数进行操作，从而获得到其数据。如下：

for task in Tasks.select(Tasks.id, Tasks.name, Tasks.types):
    print task.types
## output:
# ['other']
# ['useless', 'informed']
# ['useless', 'other', 'waiting']

tasks = Tasks.select(Tasks.id).where(Tasks.id==1)
for task in tasks:
    task.types = ['useless', 'waiting']
    task.save()

这样显然达到了我的目标了。接口表现完美。

one to many(no multiple joining on the saume table)

在下面这个表里面，如果希望在一条 SQL 中同时取出几个属性，是需要 join 同一个表多次的。如下这个设计：一个模块有四个负责人（运维负责人1号，运维负责人2号，开发负责人1号，开发负责人2号）。这四个负责人的 ID 都放在了 modules 里了。

CREATE TABLE `principals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `oss_principal1_id` int(11),
  `oss_principal2_id` int(11),
  `dev_principal1_id` int(11),
  `dev_principal2_id` int(11),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

这样的库设计就有其好处。但是显然弊大于利。这样的库表设计，以 peewee 作者的回复，是不对其进行支持的（按照正常声明的 Model，进行检索会报错）。除非进行 alias 。但是做了 alias 的检索会效率低很多（即，每一行的 module ，都需要 join 一次 principal 表以获取信息）。

错误的做法：

class Principals(BaseModel):
    id = IntegerField()
    name = CharField()

    class Meta:
        db_table = 'principals'

OssPrincipal1 = Principals
OssPrincipal2 = Principals
DevPrincipal1 = Principals
DevPrincipal2 = Principals

class Modules(BaseModel):
    id = IntegerField()
    name = CharField()
    oss_principal1 = ForeignKeyField(OssPrincipal1, db_column='oss_principal1_id')
    oss_principal2 = ForeignKeyField(OssPrincipal2, db_column='oss_principal2_id')
    dev_principal1 = ForeignKeyField(DevPrincipal1, db_column='dev_principal1_id')
    dev_principal2 = ForeignKeyField(DevPrincipal2, db_column='dev_principal2_id')

    class Meta:
        db_table = 'modules'

这样的 Model 声明，在进行检索的时候会提示 select 时重复声明的 principal 。

Modules.select(Modules, OssPrincipal1, OssPrincipal2) \
        .join(OssPrincipal1)
        .join(OssPrincipal2)
# ERROR:
_mysql_exceptions.OperationalError: (1066, "Not unique table/alias: 't2'")

这是因为 peewee 内部的实现，它获取 OssPrincipal1 和 OssPrincipal2 的 db_table 都为 modules，因此都 alias 为 t2 。

其实这是有方法实现这个需求的，利用 alias 把 Principal alias 成这四个类型的负责人。但是这样的检索效率很低，每次获取负责人信息的时候都会进行一次 SQL 操作。

因此我想到了下面的这个做法：还是拓展域以实现。

class Principals(BaseModel):
    id = IntegerField()
    name = CharField()

    class Meta:
        db_table = 'principals'

class PrincipalField(IntegerField):
    principal_dict = {}
    for principal in Principal.select().dicts():
        principal_dict[principal['id']] = principal

    def python_value(self, _v):
        principal_info = self.principal_dict.get(_v, {})
        


    
return principal_info

    # def db_value(self, _v):
    # not defined

class Modules(BaseModel):
    id = IntegerField()
    name = CharField()
    oss_principal1 = PrincipalField(db_column='oss_principal1_id')
    oss_principal2 = PrincipalField(db_column='oss_principal2_id')
    dev_principal1 = PrincipalField(db_column='dev_principal1_id')
    dev_principal2 = PrincipalField(db_column='dev_principal2_id')

    class Meta:
        db_table = 'modules'

四个负责人的域定义为 PrincipalField，它对负责人 ID 的数据进行操作，在 Model 里进行 ID -> 负责人信息的转化。这样就可以进行很直接的获取到模块负责人信息了。

Modules.oss_principal1
# return { id: xx, name: }

这样做除了不太规范以外，它效率应该是很不错的：principal信息都存放在内存中。它有个很小的问题，它会在初始化 PrincipalField 这个类的时候进行对负责人信息的载入（载入为字典 principal_dict 然后重复利用）。因此，如果 Principal 的信息有更新，可能需要重启应用。

many to many

这个没什么特别好说明的事情。直接看文档吧。还是需要注意 SQL 是否足够高效。

http://peewee.readthedocs.org/en/latest/peewee/models.html#implementing-many-to-many

树形结构树

树形结构的数据太常见了。如：

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT '',
  `level` tinyint(5) ,
  `parent_id` int(11),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

利用 parent_id 可以构建出树形的数据。这种时候我还会推荐利用一个 level 域来确定此数据的层级：这样会很大的方便到开发和维护，好处不少。

在 peewee 官方文档中说明了利用 alias 来 join 自身的表从而获取一个层级的数据：

http://peewee.readthedocs.org/en/latest/peewee/api.html#Model.alias

其本质是类似这样的：

select products.name, children.name
from products
left outer join products as children on products.id = children.parent_id

这种实现很正常，但是有个问题：我们的数据绝大部分时候并不是只有两级，而如果加一级就再 join 一次表显然就非常复杂。

我的做法是为 Model 新增 tree 方法，tree 内部进行一次没有 join 操作的检索，在 python 级别对数据进行处理形成希望的树状结构输出：

class Products(BaseModel):
    id = IntegerField()
    name = CharField()
    level = IntegerField()
    parent_id = IntegerField()

    class Meta:
        db_table = 'products'

    @classmethod
    def tree(class_, level=-1):
        ''' 获取最大级别到 level 的产品树信息 '''
        query = class_.select(class_)
        if level > 0:
            query = query.where(class_.level <= level)
        products = prefetch(query)
        # TODO
        # 在此处进行希望的数据结构的拼装
        for product in products:
            print product.name, product.id, product.level, product.parent_id

在这里，level的用途应该是非常明显了。根据业务需求，对输出的 tree 进行定制，从功能和业务角度而言都是比较优良的。

# 获取三级的产品信息树
product_tree = Products.tree(level=3)