Python 使用 Peewee

Peewee教程展示了如何使用PythonPeeweeORM。

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

PythonPeewee

Peewee是一个简单的小型PythonORM工具。它支持SQLite、MySQL和PostgreSQL。

$ pipenv install peewee

我们安装peewee模块。

Peewee映射

Model映射到数据库表，Field映射到表列，instance映射到表行。

Peewee使用MySQLDatabase用于MySQL，PostgresqlDatabase用于PostgreSQL，SqliteDatabase用于SQLite。在本教程中，我们使用SQLite数据库。

Peewee字段类型

Peewee模型中的字段类型定义模型的存储类型。它们被转换为相应的数据库列类型。

此表列出了Peewee字段类型和对应的SQLite、PostgreSQL和MySQL列类型。

Peewee模型定义

在第一个例子中，我们创建了一个简单的数据库表。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'


Note.create_table()

note1 = Note.create(text='Went to the cinema')
note1.save()

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

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

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

该示例在SQLite中创建了一个notes数据库表。

db = peewee.SqliteDatabase('test.db')

我们启动一个test.dbSQLite数据库。这会在文件系统上创建一个test.db文件。

class Note(peewee.Model):
...

我们定义了一个名为Note的数据库模型。Peewee模型继承自peewee.Model。

text = peewee.CharField()
created = peewee.DateField(default=datetime.date.today)

我们指定模型字段。我们有一个CharField和一个DateField。CharField是一个用于存储字符串的字段类。DateField是一个用于存储日期的字段类。如果未指定，则采用默认值。

class Meta:
    database = db
    db_table = 'notes'

在Meta类中，我们定义了对数据库的引用和数据库表名。

Note.create_table()

该表是使用create_table从模型创建的。

note1 = Note.create(text='Went to the cinema')
note1.save()

我们创建并保存一个新实例。

sqlite> select * from notes;
1|Went to the cinema|2018-11-01
2|Exercised in the morning|2018-10-20
3|Worked in the garden|2018-10-22
4|Listened to music|2018-11-01

我们验证数据。

Peewee掉落表

使用drop_table模型方法删除表。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:
        database = db
        db_table = 'notes'


Note.drop_table()

该示例删除了notes表。

Peeweeinsert_many

insert_many方法允许批量创建。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'


Note.create_table()

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) }
]

with db.atomic():

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

代码示例在一次bulkcreate操作中重新创建notes表。

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) }
]

我们在字典列表中定义数据。

with db.atomic():

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

我们执行批量操作。atomic方法将批量操作放入事务中。

Peewee选择所有实例

select方法用于检索已定义模型的实例。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'


notes = Note.select()

for note in notes:
    print('{} on {}'.format(note.text, note.created))

该示例获取并显示所有Note实例。

notes = Note.select()

select方法创建一个SELECT查询。如果没有明确提供字段，查询将默认选择模型上定义的所有字段。

$ ./fetch_all.py
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

带where的Peewee过滤器

where方法可以根据给定的条件过滤数据。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'

notes = Note.select().where(Note.id > 3)

for note in notes:
    print('{} {} on {}'.format(note.id, note.text, note.created))

该示例检索ID大于三的所有行。

notes = Note.select().where(Note.id > 3)

where方法对查询应用过滤条件。

$ ./where_clause.py
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

Peewee多个where表达式

我们可以组合多个where表达式。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'

notes = Note.select().where((Note.id > 2) & (Note.id < 6))

for note in notes:
    print('{} {} on {}'.format(note.id, note.text, note.created))

该示例检索ID大于2且小于6的所有行。

notes = Note.select().where((Note.id > 2) & (Note.id < 6))

我们使用两个where表达式与&运算符组合。

$ ./multiple_where_expr.py
3 Went to cinema on 2018-10-05
4 Listened to music on 2018-10-28
5 Watched TV all day on 2018-10-14

Peewee检索单个实例

选择单个实例有两种方式；他们每个人都使用get方法。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'


note1 = Note.select().where(Note.text == 'Went to cinema').get()

print(note1.id)
print(note1.text)
print(note1.created)

note2 = Note.get(Note.text == 'Listened to music')

print(note2.id)
print(note2.text)
print(note2.created)

该示例展示了如何以两种方式检索单个实例。

note1 = Note.select().where(Note.text == 'Went to cinema').get()

我们可以使用Note.select().where().get方法链。

note2 = Note.get(Note.text == 'Listened to music')

还有一个Note.get快捷方法，也是一样的。

$ ./single_instance.py
3
Went to cinema
2018-10-05
4
Listened to music
2018-10-28

Peewee选择特定列

在select方法中，我们可以指定要包含在查询中的列的名称。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'


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

output = [e for e in notes.tuples()]
print(output)

该示例包括两列：text和created。Id被跳过。我们将查询限制为两个实例。

$ ./columns.py
[('Tai chi in the morning', datetime.date(2018, 10, 20)),
    ('Visited friend', datetime.date(2018, 10, 12))]

Peewee计数实例

要计算表中模型实例的数量，我们可以使用count方法。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'

n = Note.select().count()
print(n)

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

示例统计所有实例的个数和日期等于或晚于2018/10/20的实例的个数。

$ ./count_instances.py
7
4

Peewee展示SQL语句

生成的SQL语句可以用sql方法显示。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'

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

该示例显示了ORM查询转换成的SQL。

$ ./show_sql.py
('SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1"
    WHERE ("t1"."id" = ?)', [3])

Peewee偏移量，限制

使用offset和limit属性，我们可以定义初始跳过的实例和要包含在select中的实例数。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'notes'

notes = Note.select().offset(2).limit(3)

for note in notes:
    print(note.id, note.text, note.created)

该示例返回三个实例，从第二个实例开始。

$ ./offset_limit.py
3 Went to cinema 2018-10-05
4 Listened to music 2018-10-28
5 Watched TV all day 2018-10-14

Peewee订购

可以使用order_by对检索到的实例进行排序。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db 
        db_table = 'notes'


print('Ascending order')
print('*****************************')

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

for note in notes:
    print(note.text, note.created)

print()
print('Descending order')
print('*****************************')

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

for note in notes:
    print(note.text, note.created)

代码示例按创建日期对实例进行排序。

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

此行返回按创建日期升序排列的笔记实例。

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

要按升序检索注释，我们在字段上附加desc方法。

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

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

这是笔记实例的有序列表。

Peewee删除实例

delete_by_id方法删除由其Id标识的实例。它返回已删除实例的数量。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:
        database = db
        db_table = 'notes'

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

该示例删除了一个ID为1的Note实例。

Peewee删除多个实例

要删除更多实例，我们调用delete方法。它返回成功删除实例的数量。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:
        database = db
        db_table = 'notes'

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

print('{} instances deleted'.format(n))

在示例中，我们删除了所有Id大于3的实例。

$ ./delete_instances.py
4 instances deleted

在我们的例子中，我们删除了四个Note实例。

Peewee更新实例

update方法更新一个实例。它返回成功更新实例的数量。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Note(peewee.Model):

    text = peewee.CharField()
    created = peewee.DateField(default=datetime.date.today)

    class Meta:
        database = db
        db_table = 'notes'

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

print('# of rows updated: {}'.format(n))

例子修改了Id为1的笔记的创建日期。

Peewee一对多关系

在下面的示例中，我们将把模型映射到现有表。模型之间的关系是使用ForeignKeyField创建的。

BEGIN TRANSACTION;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS customers;

CREATE TABLE IF NOT EXISTS customers(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers(Name) VALUES('Paul Novak');
INSERT INTO customers(Name) VALUES('Terry Neils');
INSERT INTO customers(Name) VALUES('Jack Fonda');
INSERT INTO customers(Name) VALUES('Tom Willis');

CREATE TABLE IF NOT EXISTS reservations(id INTEGER PRIMARY KEY, 
    customer_id INTEGER, created DATE, 
    FOREIGN KEY(customer_id) REFERENCES customers(id));
INSERT INTO reservations(customer_id, created) VALUES(1, '2018-22-11');
INSERT INTO reservations(customer_id, created) VALUES(2, '2018-28-11');
INSERT INTO reservations(customer_id, created) VALUES(2, '2018-29-11');
INSERT INTO reservations(customer_id, created) VALUES(1, '2018-29-11');
INSERT INTO reservations(customer_id, created) VALUES(3, '2018-02-12');
COMMIT;

此SQL创建两个表：customers和reservations。两个表之间存在一对多关系：一个客户可以有多个预订。

sqlite> .read customers_reservations.sql 

我们将SQL文件读入数据库。

#!/usr/bin/python

import peewee
import datetime

db = peewee.SqliteDatabase('test.db')

class Customer(peewee.Model):

    name = peewee.TextField()

    class Meta:

        database = db
        db_table = 'customers'

class Reservation(peewee.Model):

    customer = peewee.ForeignKeyField(Customer, backref='reservations')
    created = peewee.DateField(default=datetime.date.today)

    class Meta:

        database = db
        db_table = 'reservations'

customer = Customer.select().where(Customer.name == 'Paul Novak').get()

for reservation in customer.reservations:

    print(reservation.id)
    print(reservation.created)

在示例中，我们定义了两个映射到表的模型。然后我们选择一位客户并显示他的预订。

customer = peewee.ForeignKeyField(Customer, backref='reservations')

Customer和Reservation模型之间的关系是使用ForeignKeyField创建的。backref属性设置我们如何引用客户的预订。

for reservation in customer.reservations:

客户实例有一个属性reservations，其中包含相应的预订。

$ ./one2many.py
1
2018-22-11
4
2018-29-11

在本教程中，我们介绍了PythonPeeweeORM。

访问Python教程或列出所有Python教程。