07、peewee的功能介绍

项目GIthub源码地址：[https://github.com/vannesspeng/TornadoForum]

一、为什么选择peewee作为本项目的ORM

ORM带来的好处：
1、隔离数据之间的差异
2、便于维护
3、ORM会提供防止sql注入等功能，让我们只需要关注核心业务即可
4、变量传递时的调用更加方便
5、很多立志不用orm的项目，后期都会慢慢自己开发一套ORM

为什么我们要选择peewee
1、python主流的orm有三种：django orm， sqlalchemy，peewee
2、peewee简单，灵活声明方式和django的orm接近
3、github star数量高， 活跃度高
4、文档质量高，async-peewee活跃度高

二、peewee model的定义和数据表自动生成

老规矩直接上代码

#!/usr/bin/env python
#-*- coding:utf-8 -*-
# author:pyy
# datetime:2018/12/27 15:16
from datetime import datetime

from peewee import MySQLDatabase, Model, CharField, IntegerField, FloatField, TextField, DateTimeField, ForeignKeyField
# 生成mysql 数据库对象
db = MySQLDatabase("message", host="127.0.0.1", port=3306, user="root", password="root")

# 定义基础Model，供其他Model继承
class BaseModel(Model):
    add_time = DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        database = db

class Supplier(BaseModel):
    name = CharField(max_length=100, verbose_name="名称", index=True)
    address = CharField(max_length=100, verbose_name="联系地址")
    phone = CharField(max_length=11, verbose_name="联系方式")

    class Meta:
        # 设置表名，数据库对象已经在BaseModel中设置好了
        table_name = "supplier"

class Goods(BaseModel):
    # 设置商品的外键为供应商家，设置backref属性，提供反查功能（查询某个供应商供应的goods）
    supplier = ForeignKeyField(Supplier, verbose_name="供应商家", backref="goods")
    name = CharField(max_length=100, verbose_name="商品名称", index=True)
    click_num = IntegerField(default=0, verbose_name="点击数")
    goods_num = IntegerField(default=0, verbose_name="库存数")
    price = FloatField(default=0.0, verbose_name="价格")
    brief = TextField(verbose_name="商品简介")

    class Meta:
        table_name = "goods"

def init_table():
    # 创建数据库表
    db.create_tables([Goods, Supplier])

if __name__ == "__main__":
    init_table()

运行该程序后，mysql数据库中就会自动生成两张数据表：

数据表

三、model的数据保存

先准备好需要插入的数据data.py

#!/usr/bin/env python
#-*- coding:utf-8 -*-
# author:pyy
# datetime:2018/12/27 15:50
supplier_list = [
    {
        "name":"淘宝",
        "address":"杭州市",
        "phone":"18888888888"
    },
    {
        "name":"京东",
        "address":"上海市",
        "phone":"17777777777"
    },
    {
        "name":"天猫",
        "address":"北京市",
        "phone":"16666666666"
    }
]

goods_list = [
    {
        "supplier":6,
        "name": "52度茅台集团国隆双喜酒500mlx6",
        "click_num": 100,
        "goods_num": 666,
        "price": 128,
        "brief": "贵州茅台酒厂（集团）保健酒业有限公司生产，是以“龙”字打头的酒水。中国龙文化上下8000年，源远而流长，龙的形象是一种符号、一种意绪、一种血肉相联的情感。"
    },
    {
        "supplier":7,
        "name": "52度水井坊臻酿八號500ml",
        "click_num": 585,
        "goods_num": 288,
        "price": 36,
        "brief": "贵州茅台酒厂（集团）保健酒业有限公司生产，是以“龙”字打头的酒水。中国龙文化上下8000年，源远而流长，龙的形象是一种符号、一种意绪、一种血肉相联的情感。"
    },
    {
        "supplier":8,
        "name": "53度茅台仁酒500ml",
        "click_num": 553,
        "goods_num": 280,
        "price": 190,
        "brief": "贵州茅台酒厂（集团）保健酒业有限公司生产，是以“龙”字打头的酒水。中国龙文化上下8000年，源远而流长，龙的形象是一种符号、一种意绪、一种血肉相联的情感。"
    },
    {
        "supplier":6,
        "name": "茅台53度飞天茅台500ml",
        "click_num": 48,
        "goods_num": 20,
        "price": 22,
        "brief": "贵州茅台酒厂（集团）保健酒业有限公司生产，是以“龙”字打头的酒水。中国龙文化上下8000年，源远而流长，龙的形象是一种符号、一种意绪、一种血肉相联的情感。"
    },
    {
        "supplier":7,
        "name": "芝华士12年苏格兰威士忌700ml",
        "click_num": 31,
        "goods_num": 15,
        "price": 88,
        "brief": "贵州茅台酒厂（集团）保健酒业有限公司生产，是以“龙”字打头的酒水。中国龙文化上下8000年，源远而流长，龙的形象是一种符号、一种意绪、一种血肉相联的情感。"
    }
]

遍历上述数据后保存到数据库中

#!/usr/bin/env python
#-*- coding:utf-8 -*-
# author:pyy
# datetime:2018/12/27 15:47
from chapter_04.data import supplier_list, goods_list
from chapter_04.models.model import Supplier, Goods


def save_model():
    for data in supplier_list:
        supplier = Supplier()
        supplier.name = data['name']
        supplier.address = data['address']
        supplier.phone = data['phone']
        supplier.save()

    for data in goods_list:
        # 使用这种方式更加简介，可以直接将data数据映射到good对象中
        goods = Goods(**data)
        goods.save()


save_model()

执行以上代码后，数据中成功插入了数据：

image.png
image.png

四、peewee数据查询

def query_model():
    #获取某一条数据
    #good = Goods.get(Goods.id==1)
    good = Goods.get_by_id(1)
    good = Goods[1]

    #select 返回的是modelselect对象
    #获取所有数据
    # select price from goods
    goods = Goods.select(Goods.name, Goods.price)

    # select * from goods where price > 100
    goods = Goods.select().where(Goods.price>100)

    #select * from goods where price>100 and click_num>200
    goods = Goods.select().where((Goods.price>100)|(Goods.click_num>200))

    #select * from goods where name like "%飞天"
    goods = Goods.select().where(Goods.name.contains("飞天"))

    goods = Goods.select().where(Goods.id<<[1,3])
    goods = Goods.select().where((Goods.id==1)|(Goods.id==3))
    goods = Goods.select().where((Goods.id.in_([1,3])))


    #select * from goods where price>click_num
    goods = Goods.select().where(Goods.price>Goods.click_num)

    #排序 select * from goods order by price desc
    goods = Goods.select().order_by(Goods.price.asc())
    goods = Goods.select().order_by(Goods.price)

    #分页
    goods = Goods.select().order_by(Goods.price).paginate(2, 2)
    for good in goods:
        print(good.price)

五、peewee更新，删除数据

from peewee import ModelUpdate
def update_model():
    try:
        good = Goods.get_by_id(1)
        good.click_num += 1
        good.delete_instance()
    except Goods.DoesNotExist:
        pass
    #delete from goods where price>150
    Goods.delete().where(Goods.price>150).execute()

    #update click_num=100 where id =1
    # Goods.update(click_num=Goods.click_num+1).where(Goods.id==1).execute()

六、使用asyncio peewee将peewee集成到tornado框架中
修改model中的database为peewee

from datetime import datetime

from peewee import *
from peewee import Model
import peewee_async

db = MySQLDatabase('message', host="127.0.0.1", port=3306, user="root", password="root")

# 使用peewee数据库对象
database = peewee_async.MySQLDatabase(
    'message', host="127.0.0.1", port=3306, user="root", password="root"
)

# 获取peewee manager
objects = peewee_async.Manager(database)

# No need for sync anymore! 设置数据库操作为非同步
database.set_allow_sync(False)


class BaseModel(Model):
    add_time = DateTimeField(default=datetime.now, verbose_name="添加时间")
    class Meta:
        # 将基类Model Meta中的database属性设置为peewee database对象
        database = database

class Supplier(BaseModel):
    name = CharField(max_length=100, verbose_name="名称", index=True)
    address = CharField(max_length=100, verbose_name="联系地址")
    phone = CharField(max_length=11, verbose_name="联系方式")
    class Meta:
        database = db
        table_name = "supplier"

class Goods(BaseModel):
    supplier = ForeignKeyField(Supplier, verbose_name="商家", backref="goods")
    name = CharField(max_length=100, verbose_name="商品名称", index=True)
    click_num = IntegerField(default=0, verbose_name="点击数")
    goods_num = IntegerField(default=0, verbose_name="库存数")
    price = FloatField(default=0.0, verbose_name="价格")
    brief = TextField(verbose_name="商品简介")

    class Meta:
        table_name = "goods"


def init_table():
    db.create_tables([Goods, Supplier])
if __name__ == "__main__":
    init_table()

使用peewee操作数据库

import asyncio

from chapter_04.models.model import Goods
from chapter_04.models.model import objects


async def handler():
    # await objects.create(Goods, supplier_id=7, name="53度水井坊臻酿八號500ml",
    #                      click_num=20, goods_num=1000, price=500, brief="州茅台酒厂（集团）保健酒业有限公司生产")
    goods = await objects.execute(Goods.select())
    for good in goods:
        print(good.name)

loop = asyncio.get_event_loop()
loop.run_until_complete(handler())
# loop.close()