Python-Modules-Peewee

Python的ORM模块Peewee模块

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

一. 简单介绍Peewee

peewee是一个简单而小型的Python ORM工具,它支持SQLiteMySQLPostgreSQL等数据库。

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
上一篇:vue中让input框自动聚焦


下一篇:ViewHolder views must not be attached when created. Ensure that you are not passing ‘true‘ to the at