peewee基本操作

 

示例：

搬运官网快速入门

#导入数据库驱动
import sqlite3
import os
import re
import enum
import peewee
from datetime import date
from peewee import *

#TODO 环境清理。删除上次创建的数据库
if os.path.exists("people.db"):  # 如果文件存在
    # 删除文件，可使用以下两种方法。
    os.remove("people.db")
    pass
else:
    print('no such file')  # 则返回文件不存在

#TODO 数据库名字
db = SqliteDatabase('people.db')

#TODO 创建两张表：Person和Pet
class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db # This model uses the "people.db" database.

class Pet(Model):
    owner = ForeignKeyField(Person, backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db # this model uses the "people.db" database

#TODO 连接数据库
db.connect()

#TODO 创建两张表Person和Pet
db.create_tables([Person, Pet])

#TODO 1- 添加
#往表中添加信息.注意添加完保存.save
#有两种接口：
#接口1：:Person()
#接口2：Person.create()
#每种接口，添加完使用xxx.save()才能保存在数据库中。
# 需要注意的是对于同一接口添加的条目，可以只保存一次。不同接口添加的条目必须至少用该条目返回的对象.save()才能保存在数据库中
uncle_bob = Person(name='Bob', birthday=date(2020, 1, 1))
uncle_bob.save() # bob is now stored in the database

grandma = Person.create(name='Grandma', birthday=date(1935, 3, 1))
herb = Person.create(name='Herb', birthday=date(1950, 5, 5))
herb.save() #用herb.save() 或 grandma.save()，如果都调用，则保存两次

#TODO 2- 查询
#全部打印
print("\nPerson Query:'):")
for person in Person.select():
    print(person.name)

#条件查询 Person.select().where(条件).get
uncle_bob = Person.select().where(Person.name == 'Bob').get()
print("\nPerson name=Bob Query:")
print(uncle_bob.name, uncle_bob.birthday)

#条件查询 Person.get(条件)
uncle_bob = Person.get(Person.name == 'Bob')
print("\nPerson name=Bob Query:")
print(uncle_bob.name, uncle_bob.birthday)

#TODO 3- 修改
grandma.name = 'Grandma L.'
grandma.save()
print("\nPerson Query after change grandma's name to Grandma L.:'):")
for person in Person.select():
    print(person.name)

#TODO 4- 往Pet表中添加内容
bob_kitty = Pet.create(owner=uncle_bob, name='Kitty', animal_type='cat')
herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
herb_mittens_jr = Pet.create(owner=herb, name='Mittens Jr', animal_type='cat')
print("\nPet Query:")
for pet in Pet.select():
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))

#TODO 5- 变更herb_fido的主人为uncle_bob
herb_fido.owner = uncle_bob
herb_fido.save()
print("\nPet Query after change herb_fido'owner to uncle_bob:")
for pet in Pet.select():
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))



#TODO 6- 删除。herb_mittens_jr is dies
herb_mittens.delete_instance()
print("\nPet Query after herb_mittens_jr is dies:")
for pet in Pet.select():
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))


#TODO 7- 高级查询join
#TODO 7.1- 查询养猫的主人
print("\nQuery owner cat :")
query = Pet.select().where(Pet.animal_type == 'cat')
for pet in query:
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))

#TODO 7.2- 多表查询join--方法1
query = (Pet
         .select(Pet, Person)
         .join(Person)
         .where(Pet.animal_type == 'cat'))
print("\nQuery owner join-1 in Pet and Person :")
for pet in query:
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))


#TODO 7.2- 多表查询join--方法2
#在Pet中查询Bob的宠物。先在Person中查询Bob的条目，然后在Pet中插叙该条目
print("\nQuery owner is Bob in Pet and Person :")
for pet in Pet.select().join(Person).where(Person.name == 'Bob'):
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))

#TODO 7.3- 查询结果按字母表顺序排序. 本例中针对宠物名排序
#升序where(xxx.order_by(yyy))
print("\nQuery owner is Bob in Pet and Person, order in alphabetically sort:")
for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))
#降速#升序where(xxx.order_by(yyy.desc()))
print("\nQuery owner is Bob in Pet and Person, order in alphabetically desc:")
for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name.desc()):
    print("pet.own:{0:10} pet.name:{1:10} pet.type:{2:10}".format(pet.owner.name, pet.name, pet.animal_type))


#TODO 7.4- 范围查找
#查找在1940前和1960后出生的人
d1940 = date(1940, 1, 1)
d1960 = date(1960, 1, 1)
query = (Person
         .select()
         .where((Person.birthday < d1940) | (Person.birthday > d1960)))
print("\nQuery bireth before 1940 or after 1960:")
for person in query:
    print("person.name:{0:10}  person.birthday:{1}".format(person.name, person.birthday))


#查找1940-1960出生的人
query = (Person
         .select()
         .where(Person.birthday.between(d1940, d1960)))
print("\nQuery bireth 1940-1960:")
for person in query:
    print("person.name:{0:10}  person.birthday:{1}".format(person.name, person.birthday))

#列出每个人宠物数
print("\nlist how many pets every own:")
for person in Person.select():
    print("{0} own {1} pets".format(person.name, person.pets.count()))

#TODO 7.5- 其他
#使用fn()
#Peewee provides a magical helper fn(), which can be used to call any SQL function.
# In the above example, fn.COUNT(Pet.id).alias('pet_count') would be translated into COUNT(pet.id) AS pet_count.
query = (Person
         .select(Person, fn.COUNT(Pet.id).alias('pet_count'))
         .join(Pet, JOIN.LEFT_OUTER)  # include people without pets.
         .group_by(Person)
         .order_by(Person.name))
print("\n")
for person in query:
    # "pet_count" becomes an attribute on the returned model instances.
    print(person.name, person.pet_count, 'pets')

#从人->宠物(了解)
print("\n")
query = (Person
         .select(Person,Pet)
         .join(Pet, JOIN.LEFT_OUTER)
         .order_by(Person.name, Pet.name))
for person in query:
    # We need to check if they have a pet instance attached, since not all
    # people have pets.
    if hasattr(person, 'pet'):
        print(person.name, person.pet.name)
    else:
        print(person.name, 'no pets')

print("\nList everybody's pets:")
query = Person.select().order_by(Person.name).prefetch(Pet)
for person in query:
    print(person.name)
    for pet in person.pets:
        print('  *', pet.name)

print("\nList people's name include 'g':")
expression = fn.Lower(fn.Substr(Person.name, 1, 1)) == 'g'
for person in Person.select().where(expression):
    print(person.name)

db.close()

 

 

参考：

官网快速入门：http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#quickstart

其他：https://www.jianshu.com/p/8d1bdd7f4ff5

https://www.cnblogs.com/yxi-liu/p/8514763.html