peewee的使用与异步peewee-async在tornado中的使用总结
蒋飞捷
peewee是数据库ORM操作的第三方库。
建表
from datetime import datetime
from peewee import *
from peewee import Model
# import peewee_asnc
db = MySQLDatabase('my_db', host='139.196.161.70', port=3306, user="root", password="root")
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:
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年，源远而流长，龙的形象是一种符号、一种意绪、一种血肉相联的情感。"
}
]
保存方法
from chapter04.models.model import Supplier, Goods
from chapter04.data import supplier_list, goods_list
def save_model():
# 保存数据至表中
for data in supplier_list:
supplier = Supplier(**data)
supplier.save()
for data in goods_list:
good = Goods(**data)
good.save()
查询
def query_model():
# 查询id为1的数据的三种方式
good = Goods.get(Goods.id==1)
good = Goods.get_by_id(1)
good = Goods[1] # 次数1不是切片1而是id为1
# 获取所有数据,(相当于Django的all方法，返回的是查询集，并不会立即去查询数据库，
# 而是在使用到该查询集对象的时候才会去查询数据库)
# select * from goods
goods = Goods.select()
# select name, 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))
# 此时才会真正的区查询数据库
for good in goods:
print(good.name)
# select * from goods where name like "%飞天"
goods = Goods.select().where(Goods.name.contains("飞天"))
# select * from goods where id in "1,3"
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.desc())
goods = Goods.select().order_by(-Goods.price)
# 分页
goods = Goods.select().order_by(Goods.price).paginate(2,2)
for good in goods:
print(good.price)
更新和删除
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()
tornado使用异步ORM库peewee-async
tornado是一个异步框架需要使用异步ORM库才能发挥其性能。
-
连接数据库创建表：
和peewee差不多，只不过需要使用异步连接数据库。
from datetime import datetime from peewee import * from peewee import Model import peewee_async # db = MySQLDatabase('my_db', host='127.0.0.1', port=3306, user="root", password="root") # 使用异步ORM连接数据库 database = peewee_async.MySQLDatabase( 'message', host='127.0.0.1', port=3306, user="root", password="root" ) objects = peewee_async.Manager(database) database.set_allow_sync(False) class BaseModel(Model): add_time = DateTimeField(default=datetime.now, verbose_name="添加时间") class Meta: # database = db 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: 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(): database.create_tables([Goods, Supplier]) if __name__ == '__main__': init_table() -
表操作：
from chapter04.models.model import Goods, objects async def handler(): """ 使用协程异步操作表 :return: """ # 可以使用model中定义的objects对象来异步操作表 await objects.create(Goods, supplier_id=1, name="53度水井坊臻酿八號500ml", click_num=20, goods_num=1000, price=500, brief="州茅台酒厂（集团）保健酒业有限公司生产") # 也可以将ORM的sql语句当做参数执行 goods = await objects.execute(Goods.select()) for good in goods: print(good.name) # 需要使用协程事件循环驱动 loop = asyncio.get_event_loop() loop.run_until_complete(handler())
类似资料：