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字段到各数据库字段的映射

Peewee字段类型	SQLite	PostgreSQL	MySQL
CharField	varchar	varchar	varchar
TextField	text	text	longtext
DateTimeField	datetime	timestamp	longtext
IntegerField	integer	integer	integer
BooleanField	smallint	bool	bool
FloatField	real	real	real
DoubleField	real	double	double
BigIntegerField	integer	bigint	bigint
DecimalField	decimal	numeric	numeric
PrimaryKeyField	integer	serial	integer
ForeignKeyField	integer	integer	integer
DateField	date	date	date
TimeField	time	time	time

二. 实际操作示例

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