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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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())
输出结果如下