04 | 在tornado中 使用peewee作为orm

安装

pip install peewee　　

github 地址

https://github.com/coleifer/peewee　

官方文档（文档写的很详细，建议读一遍）

http://docs.peewee-orm.com/en/latest/

　　

使用 peewee创建模型类

 实例化一个 数据库的实例 db ，要在Meta 中指定

 models/model.py

from datetime import datetime

from peewee import *
from peewee import Model

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


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

　

 运行该脚本，数据库中多了两张表

 

 要添加的数据data.py

supplier_list = [
    {
        "name":"淘宝",
        "address":"杭州市",
        "phone":"18888888888"
    },
    {
        "name":"京东",
        "address":"上海市",
        "phone":"17777777777"
    },
    {
        "name":"天猫",
        "address":"北京市",
        "phone":"16666666666"
    }
]

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

 

peewee_test.py  向数据库中插入数据 

from models.model import Supplier, Goods
from data import supplier_list, goods_list

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:
        good = Goods(**data)
        good.save()

if __name__ == "__main__":
    save_model()

 运行脚本，查看数据库

 

 

 peewee_test.py  查询数据

from models.model import Supplier, Goods
from data import supplier_list, goods_list
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)


if __name__ == "__main__":
    query_model()

 

 peewee_test.py  更新

在更新的时候要执行execute 懒加载，让它立即执行

from models.model import Supplier, Goods
from data import supplier_list, goods_list

from peewee import ModelUpdate
def update_model():
    try:
        # update click_num=100 where id =1
        Goods.update(click_num=Goods.click_num+1).where(Goods.id==1).execute()

        good = Goods.get_by_id(1)
        good.click_num += 1
        good.save()

    except Goods.DoesNotExist:
        pass


if __name__ == "__main__":
    update_model()

 

peewee_test.py  删除

在更新的时候要执行execute 懒加载，让它立即执行

from models.model import Supplier, Goods
from data import supplier_list, goods_list

from peewee import ModelUpdate
def update_model():
        # 删除
        good = Goods.get_by_id(1)
        good.delete_instance()
        # delete from goods where price>150
        Goods.delete().where(Goods.price > 150).execute()


if __name__ == "__main__":
    update_model()

 

通过peewee-async集成到tornado中

之所以集成peewee-async是因为peewee中存在大量的同步方法 execute （比如在更新和保存，删除），而我们需要异步的方法，peewee-async 中由asyncio提供的peewee ORM的异步接口，可以直接在tornado中使用 

安装 peewee-async　

pip install --pre peewee-async　

 gihub 地址

https://github.com/05bit/peewee-async

　　

根据官方文档改写models/model.py

from datetime import datetime

from peewee import *
from peewee import Model
import peewee_async

database = peewee_async.MySQLDatabase(
    'message', host="127.0.0.1", port=3306, user="root", password="mysql"
)

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:
        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 = database
        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_async_test.py

import asyncio

from chapter04.models.model import Goods
from chapter04.models.model import objects


async def handler():
    await objects.create(Goods, supplier_id=1, 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())

输出结果如下