示例:
搬运官网快速入门
#导入数据库驱动
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