python的orm-peewee详解
peewee的安装和入门
- 安装
pip install peewee
- peewee底层依赖pymysql，安装pymysql
pip install pymysql -i https://pypi.douban.com/simple
- 定义模型类，生成数据表结构
import datetime
from peewee import *
# 查看sql语句
import logging
logger = logging.getLogger("peewee")
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())
# 1. 定义并生成表
db = MySQLDatabase("peewee", host="mayanan.cn", port=3306, user="root", passwd="xxxxxx")
class User(Model):
# 如果没有设置主键，那么默认生成一个id的主键
username = CharField(primary_key=True, max_length=20)
class Meta:
database = db
class Tweet(Model):
user = ForeignKeyField(User, backref="tweets")
message = TextField()
created_date = DateTimeField(default=datetime.datetime.now)
is_published = BooleanField(default=True)
class Meta:
database = db
if __name__ == '__main__':
# 生成表结构
db.connect()
db.create_tables([User, Tweet])
- 添加、查询
# 添加方法1
# charlie = User(username="charlie")
# save()方法即可以完成新建，也可以完成更新的操作(取决于你的对象中是否有主键值设置, 如果有就是更新操作)
# 如果既有主键设置，又想完成更新操作，那么就需要设置force_insert=True
# charlie.save(force_insert=True)
# 添加方法2
# huey = User.create(username="huey")
# 查询
# 1. get方法 - 返回来的是直接的user对象, 如果查询不到会抛出异常
# try:
# # charlie = User.get(User.username == "charlie")
# charlie = User.get_by_id("charlie")
# except User.DoesNotExist as e:
# print("用户不存在")
# else:
# print(charlie, type(charlie), charlie.username) # charlie <Model: User> charlie
# 2. 查询所有
# users = User.select() # 没有看到sql查询语句，用于组装sql，对象是ModelSelect对象
# print(type(users)) # <class 'peewee.ModelSelect'>
# print(users.sql()) # ('SELECT `t1`.`username` FROM `user` AS `t1`', [])
# 对ModelSelect进行for循环或者切片的时候才会真正的发起sql请求
# for user in users:
# print(user)
# ModelSelect可以通过切片
# user = users[0]
# print(user, type(user))
# in查询 first过滤查到的第一条
users = User.select().where(User.username.in_(["charlie"]))
print(type(users), users.first(), type(users.first())) # <class 'peewee.ModelSelect'> charlie <Model: User>
- 数据的更新和删除
# 更新
# 方法1
# user = User.get(User.username == "charlie") # update User set xx = xx where username = "charlie"
# user.age = 28
# rows = user.save() # save()方法的返回值就是影响的行数
# print(rows)
# 方法2 - 使用update更新 (方法1需要先查询在更新，方法二直接更新，所以更新操作推荐使用方法2)
# rows = User.update(age=29).where(User.username == "charlie").execute()
# print(rows)
# 删除
# 方法1
user = User.get(User.username == "huey")
user.delete_instance()
# 方法2
rows = User.delete().where(User.username == "charlie").execute()
print(rows)
- 结论：get get_by_id方法会执行sql语句, select update delete方法只是用来拼凑sql语句的
- select加上for循环或者下标取一个元素才会真正的执行sql语句，update或delete加上execute才会真正的执行sql语句
- 多条插入、or查询
（1）更多功能
import datetime
from peewee import *
# 输出sql语句
import logging
logger = logging.getLogger("peewee")
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())
# db = MySQLDatabase("peewee", host="mayanan.cn", port=3306, user="root", password="123456")
class BaseModel(Model):
add_time = DateTimeField(default=datetime.datetime.now, verbose_name="添加时间")
class Meta:
# 这里是数据库链接，为了方便建立多个表，可以把这个部分提炼出来形成一个新的类
database = db
class Person(BaseModel):
name = CharField(verbose_name="姓名", max_length=10, null=False, index=True)
passwd = CharField(verbose_name="密码", max_length=20, null=False, default="123456")
email = CharField(verbose_name="邮件", max_length=50, null=True, unique=True)
gender = IntegerField(verbose_name="性别", null=False, default=1)
birthday = DateField(verbose_name="生日", null=True, default=None)
is_admin = BooleanField(verbose_name="是否是管理员", default=True)
class Meta:
table_name = "persons" # 自定义表名
if __name__ == '__main__':
db.connect()
db.create_tables([Person, Pet, Blog, Tag, BlogToTag])
（2）主键和约束
# 主键和约束
class Person(BaseModel):
first = CharField()
last = CharField()
class Meta:
primary_key = CompositeKey("first", "last")
class Pet(BaseModel):
owner_first = CharField()
owner_last = CharField()
pet_name = CharField()
class Meta:
constraints = [SQL('FOREIGN KEY(owner_first, owner_last) REFERENCES person(first, last)')]
class Blog(BaseModel):
pass
class Tag(BaseModel):
pass
# 复合主键
class BlogToTag(BaseModel):
blog = ForeignKeyField(Blog)
tag = ForeignKeyField(Tag)
class Meta:
primary_key = CompositeKey("blog", "tag")
（3）数据插入
# p_id = Person.insert({
# "first": "li",
# "last": "bobby",
# }).execute()
# print(p_id)
# p_id = Blog.insert({}).execute() # 此处插入时，默认值为空
# print(p_id)
# 我们的Model中设置的default是当我们让Model自动插入数据的时候
# blog = Blog()
# blog.save()
# print(blog.id)
# 批量插入
# blogs = [{"add_time": datetime.datetime.now()}, {"add_time": datetime.datetime.now()}]
# Blog.insert_many(blogs).execute()
（4）复合条件查询（and & or |）
# or
p1 = Person.select().where((Person.first == "li") | (Person.first == "li1"))
print(p1.sql())
print(p1[1]) # ('li1', 'bobby1')
# and
p2 = Person.select().where((Person.first == "li") & (Person.first == "li1"))
print(p2.sql())
p3 = Person.select().where(Person.first == "li", Person.first == "li1")
print(p3.sql())
- 模糊查询、字典展示、排序、limit和去重
（1）模糊查询 contains startswitch endswitch
# 模糊查询 like
# query = Person.select().where(Person.first.contains("li"))
query = Person.select().where(Person.first.startswith("li"))
print(type(query))
print(query)
（2）字典展示
persons = Person.select().dicts()
for person in persons:
print(type(person), person)
# <class 'dict'> {'add_time': datetime.datetime(2022, 2, 10, 13, 11, 52), 'first': 'li', 'last': 'bobby'}
（3）排序 - order_by
# 方法1
# users = User.select().order_by(User.age.desc())
# 方法2
users = User.select().order_by(-User.age)
for u in users:
print(u.username, u.age)
（4）distinct 去重
users = User.select(User.username).distinct()
for u in users:
print(u.username)
（5）统计个数
users = User.select(User.username).distinct().count()
print(users) # 10
- 聚合函数、执行原生的sql语句 -- 重要
（1）聚合查询
# 查询年龄最大值 fn.Max
# max_age = User.select(fn.Max(User.age)).scalar()
# print(max_age)
# 查询年龄最大值的姓名
# SELECT username FROM `new_user` where age = (select max(age) from new_user)
# users = User.select().where(User.age == max_age)
# for u in users:
# print(u.username)
# 子查询实现查询年龄最大值的姓名
sub_query = User.select(fn.Max(User.age))
users = User.select(User.username, User.age).where(User.age == sub_query)
for u in users:
print(u.username, u.age)
（2）原生sql
# 方法1
# users = User.raw("select * from new_user where age > %s", 50)
# for u in users:
# print(u.username, u.age)
# 方法2
users = User.select().where(SQL("username = '%s'" % "bobby5"))
for u in users:
print(u.username, u.age)
- 多表查询和反向查询 - 重要
点击查看代码
import datetime
from peewee import *
# 输出sql语句
import logging
logger = logging.getLogger("peewee")
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())
# db = MySQLDatabase("peewee", host="mayanan.cn", port=3306, user="root", password="123456")
class BaseModel(Model):
class Meta:
# 这里是数据库链接，为了方便建立多个表，可以把这个部分提炼出来形成一个新的类
database = db
class User(BaseModel):
username = TextField()
class Meta:
table_name = "user2"
class Tweet(BaseModel):
content = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)
user = ForeignKeyField(User, backref="tweets")
class Meta:
table_name = "tweet2"
class Favorite(BaseModel):
user = ForeignKeyField(User, backref="favorites")
tweet = ForeignKeyField(Tweet, backref="favorites")
def populate_test_data():
data = (
('huey', ('meow', 'hiss', 'purr')),
('mickey', ('woof', 'whine')),
('zaizee', ()))
for username, tweets in data:
user = User.create(username=username)
for tweet in tweets:
Tweet.create(user=user, content=tweet)
# Populate a few favorites for our users, such that:
favorite_data = (
('huey', ['whine']),
('mickey', ['purr']),
('zaizee', ['meow', 'purr']))
for username, favorites in favorite_data:
user = User.get(User.username == username)
for content in favorites:
tweet = Tweet.get(Tweet.content == content)
Favorite.create(user=user, tweet=tweet)
if __name__ == '__main__':
# 生成表结构
# db.connect()
# db.create_tables([User, Tweet, Favorite])
# 创建测试数据
# populate_test_data()
# 多表查询、反向查询
# 外键正向取
# for t in Tweet.select():
# print(t.content, t.user.username)
# 使用表连接查出用户信息和推文信息 (关联查询)
""" 原生sql
SELECT user2.username, tweet2.content FROM `user2` left join tweet2
on user2.id = tweet2.user_id
where user2.username = "mickey"
"""
# 方法1
# 注意不能使用下面这种方法,而是使用第2种或第3种方法
# query = Tweet.select().join(User).where(User.username == "mickey")
# query = Tweet.select(Tweet, User).join(User).where(User.username == "mickey")
# query = Tweet.select(Tweet.content, User.username).join(User).where(User.username == "mickey")
# for q in query:
# print(q.user.username, q.content)
# 方法2
# query = Tweet.select(Tweet.content, User.username).join(User, on=(Tweet.user == User.id)).where(User.username == "mickey")
# for q in query:
# print(q.user.username, q.content)
# 外键反向取
tweets = User.get(User.username == "mickey").tweets
for t in tweets: # 通过用户获取推文内容
print(t.content)
for f in t.favorites: # 通过推文内容获取收藏用户
print(f.user.username, f.tweet.content)
- 避免n加1查询问题
# for tweet in Tweet.select():
# print(tweet.content, tweet.user.username) # 查询数据库6次
# tweets = Tweet.select()
# for tweet in tweets:
# print(tweet.content, tweet.user.username) # 查询数据库6次
# 解决办法1 join
# tweets = Tweet.select(Tweet.content, User.username).join(User, on=(Tweet.user == User.id)) # 推荐使用几个字段写几个字段，减少网络传输
tweets = Tweet.select(Tweet, User).join(User, on=(Tweet.user == User.id)).where(User.username == "huey")
for tweet in tweets:
print(tweet.content, tweet.user.username) # 查询数据库1次
总结： 什么时候sql会发起请求，以及请求多少次，对开发人员来说很重要, 这也是很多sql高手不喜欢用orm的原因
