Python的ORM模块Peewee模块

对象关系映射（ORM）是一种面向对象语言访问关系数据库的技术。

一. 简单介绍Peewee

peewee是一个简单而小型的Python ORM工具，它支持SQLite， MySQL及PostgreSQL等数据库。

1.1 安装

         peewee依赖pymysql
pip3 install pymysql
pip3 install peewee
 也可以使用pipenv来安装
pipenv intall peewee

    

1.2 映射

1.2.1 类到数据库元素的映射

• database类映射到数据库；

• Model类映射到数据表；

• Field映射到表列；

• instance映射到表行；

1.2.2 Peewee连接各类数据的类

• 对于 MySQL 使用MySQLDatabase；

• 对于 PostgreSQL 使用PostgresqlDatabase；

• 对于 SQLite 使用SqliteDatabase；

1.2.3 Peewee字段到各数据库字段的映射

二. 实际操作示例

2.1 映射关系展示

        import peewee
import datetime

 使用peewee.MySQLDatabase创建一个db对象
 注：如果test.db并不存在，不会自动创建
 create database testdb character set UTF8 collate utf8_bin;
db = peewee.MySQLDatabase("testdb", host="127.0.0.1", user="root", passwd="123.com")

 定义一个Model类的对象Note，映射数据库中的表
class Note(peewee.Model):
    
     使用peewee的Fields映射数据库中的列(字段)
    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today())

     Meta类中定义对db对象的引用及数据表的名称
    class Meta:
        database = db
        db_table = ‘notes‘

 使用Model类的create_table()方法在数据库中实际创建notes这张表
Note.create_table()
 相对应的删除Model类所对应的表，使用drop_table()方法
Note.drop_table()

 创建一个新instance
note1 = Note.create(text=‘Went to the cinema‘)
 将instance写入数据库
note1.save()

note2 = Note.create(text="Exercised in the morning", created=datetime.date(2018, 10, 20))
note2.save()

note3 = Note.create(text="Wored in the garden", created=datetime.date(2018, 10, 22))
note3.save()

note4 = Note.create(text="Listened to music")
note4.save()


    

         注意在peewee中，会自动为表创建自增主键id
mysql> select * from notes;
+----+--------------------------+------------+
| id | text                     | created    |
+----+--------------------------+------------+
|  1 | Went to the cinema       | 2020-08-25 |
|  2 | Exercised in the morning | 2018-10-20 |
|  3 | Wored in the garden      | 2018-10-22 |
|  4 | Listened to music        | 2020-08-25 |
+----+--------------------------+------------+
4 rows in set (0.00 sec)

    

2.2 批量插入

         需要上面的映射关系
 将需要写入的数据防止在data列表中
data = [
    { ‘text‘: ‘Tai chi in the morning‘, ‘created‘: datetime.date(2018, 10, 20) },
    { ‘text‘: ‘Visited friend‘, ‘created‘: datetime.date(2018, 10, 12) },
    { ‘text‘: ‘Went to cinema‘, ‘created‘: datetime.date(2018, 10, 5) },
    { ‘text‘: ‘Listened to music‘, ‘created‘: datetime.date(2018, 10, 28) },
    { ‘text‘: ‘Watched TV all day‘, ‘created‘: datetime.date(2018, 10, 14) },
    { ‘text‘: ‘Worked in the garden‘, ‘created‘: datetime.date(2018, 10, 22) },
    { ‘text‘: ‘Walked for a hour‘, ‘created‘: datetime.date(2018, 10, 28) }
]

 通过db.atomic新建事务执行
with db.atomic():

    query = Note.insert_many(data)
    query.execute()

    

        mysql> select * from notes;
+----+------------------------+------------+
| id | text                   | created    |
+----+------------------------+------------+
|  1 | Tai chi in the morning | 2018-10-20 |
|  2 | Visited friend         | 2018-10-12 |
|  3 | Went to cinema         | 2018-10-05 |
|  4 | Listened to music      | 2018-10-28 |
|  5 | Watched TV all day     | 2018-10-14 |
|  6 | Worked in the garden   | 2018-10-22 |
|  7 | Walked for a hour      | 2018-10-28 |
+----+------------------------+------------+
7 rows in set (0.00 sec)

    

2.3 查询

2.3.1 批量查询select方法

2.3.1.1 查询所有记录

         未指定条件查询所有
notes = Note.select()
 取出所有instance的两个属性
for note in notes:
    print(‘{} on {}‘.format(note.text, note.created))

    

        Tai chi in the morning on 2018-10-20
Visited friend on 2018-10-12
Went to cinema on 2018-10-05
Listened to music on 2018-10-28
Watched TV all day on 2018-10-14
Worked in the garden on 2018-10-22
Walked for a hour on 2018-10-28

    

2.3.1.2 使用where设置单个条件

        notes = Note.select().where(Note.id > 3)
for note in notes:
    print(‘{}>>{} on {}‘.format(note.id, note.text, note.created))

    

        4>>Listened to music on 2018-10-28
5>>Watched TV all day on 2018-10-14
6>>Worked in the garden on 2018-10-22
7>>Walked for a hour on 2018-10-28

    

2.3.1.3 使用where设置多个条件

         多条件使用运算符
notes = Note.select().where((Note.id < 3) | (Note.id > 5))
 多条件使用where自带关系<where为and，orwhere为or>
notes = Note.select().where((Note.id < 3), (Note.id > 5))
for note in notes:
    print(‘{}>>{} on {}‘.format(note.id, note.text, note.created))

    

        4>>Listened to music on 2018-10-28

    

2.3.2 单个数据查询

         查询方法1-使用复杂的组合查询
note1 = Note.select().where(Note.text=="Listened to music").get()
print(note1.id, note1.text, note1.created)

 直接使用简便查询，等同于上面的
note2 = Note.get(Note.text=="Watched TV all day")
print(note2.id, note2.text, note2.created)

    

        4 Listened to music 2018-10-28
5 Watched TV all day 2018-10-14

    

2.3.3 相关限制

2.3.3.1 限制特定列

        notes = Note.select(Note.text, Note.created)

    

2.3.3.2 limit

        notes = Note.select(Note.text, Note.created).limit(2)

    

2.3.3.3 offset

        notes = Note.select(Note.text, Note.created).offset(2).limit(2)

    

2.3.3.4 排序

        #升序
notes = Note.select().order_by(Note.created)
#降序
notes = Note.select().order_by(Note.created.desc())

    

2.3.4 聚合

2.3.4.1 count

        num = Note.select().where(Note.created >= datetime.date(2018, 10, 20)).count()

    

2.3.5 其他功能

2.3.5.1 显示sql语句

        note3 = Note.select().where(Note.id == 3)
print(note3.sql())

    

2.4 删除

2.4.1 通过id删除

        n2 = Note.delete_by_id(1)
print(n2)

    

2.4.2 通过delete批量删除

        query = Note.delete().where(Note.id > 3)
n = query.execute()

    

2.5 更新

2.5.1 通过update更新

        query = Note.update(created=datetime.date(2018, 10, 27)).where(Note.id == 1)
n = query.execute()

    

2.5.2 通过实例属性修改

        note2 = Note.get(Note.text=="Watched TV all day")
 此更改仅在程序中生效
note2.created = datetime.date.today()
 将实例真正写入数据库中
note2.save()

    

三. 关系

3.1 一对多关系

        import peewee
import datetime
import playhouse

db = peewee.MySQLDatabase(‘testdb‘, host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123.com‘)


class BaseModule(peewee.Model):
    class Meta:
        database = db


class Person(BaseModule):

    name = peewee.CharField(32, null=False)
    birth = peewee.DateField(null=True, default=datetime.date.today())

    class Meta:
        db_table = ‘person‘


class Messages(BaseModule):
    person_id = peewee.ForeignKeyField(Person, field=Person.id, backref="messages")
    msg = peewee.TextField(null=False, default="He or she is lazy, said nothing...")

    class Meta:
        db_table = ‘messages‘


Person.create_table()
Messages.create_table()

bruce = Person.create(name="Bruce")
alvin = Person.create(name="Alvin")
msg1 = Messages.create(person_id=alvin.id, msg="Hi, I am alvin")
msg2 = Messages.create(person_id=bruce.id, msg="Hi, I am bruce")

print(msg1.person_id.name)
print(msg2.person_id.name)
for message in bruce.messages:
    print(message.msg)

    

        Alvin
Bruce
Hi, I am bruce

    

原文：https://www.cnblogs.com/FcBlog11/p/14588336.html