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