python的orm-peewee详解

peewee的安装和入门

  1. 安装
    pip install peewee
  • peewee底层依赖pymysql，安装pymysql
    pip install pymysql -i https://pypi.douban.com/simple

github源码
peewee官方文档

  1. 定义模型类，生成数据表结构
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. 添加、查询
    # 添加方法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. 数据的更新和删除
    # 更新
    # 方法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语句
  1. 多条插入、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())
  1. 模糊查询、字典展示、排序、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
  1. 聚合函数、执行原生的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)
  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):
    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)
  1. 避免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的原因

posted @   专职  阅读(630)  评论(0)  编辑  收藏  举报
相关博文：
· 高并发，高性能，高可用MySQL 实战-orm框架原理，怎个给数据上保险
· rabbitmq基础教程
· Peewee：Python 简洁强大的 ORM 框架
· peewee操作
· 使用peewee模块操作数据库
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示