ThinkChat2.0新版上线,更智能更精彩,支持会话、画图、阅读、搜索等,送10W Token,即刻开启你的AI之旅 广告
# Peewee 教程 > 原文: [http://zetcode.com/python/peewee/](http://zetcode.com/python/peewee/) Peewee 教程展示了如何使用 Python Peewee ORM。 对象关系映射(ORM)是一种从面向对象的语言访问关系数据库的技术。 它是 Python 数据库 API 的抽象。 ## Python Peewee Peewee 是一个简单而小型的 Python ORM 工具。 它支持 SQLite,MySQL 和 PostgreSQL。 ```py $ pipenv install peewee ``` 我们安装`peewee`模块。 ## Peewee 映射 `Model`映射到数据库表,`Field`映射到表列,`instance`映射到表行。 Peewee 对于 MySQL 使用`MySQLDatabase`,对于 PostgreSQL 使用`PostgresqlDatabase`,对于 SQLite 使用`SqliteDatabase`。 在本教程中,我们将使用 SQLite 数据库。 ## 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` | 下表列出了 Peewee 字段类型以及相应的 SQLite,PostgreSQL 和 MySQL 列类型。 ## Peewee 模型定义 在第一个示例中,我们创建一个简单的数据库表。 `model_definition.py` ```py #!/usr/bin/env python3 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`数据库表。 ```py db = peewee.SqliteDatabase('test.db') ``` 我们启动`test.db` SQLite 数据库。 这将在文件系统上创建一个`test.db`文件。 ```py class Note(peewee.Model): ... ``` 我们定义了一个名为`Note`的数据库模型。 Peewee 模型继承自`peewee.Model`。 ```py text = peewee.CharField() created = peewee.DateField(default=datetime.date.today) ``` 我们指定模型字段。 我们有一个`CharField`和一个`DateField`。 `CharField`是用于存储字符串的字段类。 `DateField`是用于存储日期的字段类。 如果未指定,则采用默认值。 ```py class Meta: database = db db_table = 'notes' ``` 在`Meta`类中,我们定义对数据库的引用和数据库表名称。 ```py Note.create_table() ``` 该表是使用`create_table()`从模型创建的。 ```py note1 = Note.create(text='Went to the cinema') note1.save() ``` 我们创建并保存一个新实例。 ```py 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()`模型方法删除该表。 `drop_table.py` ```py #!/usr/bin/env python3 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`表。 ## Peewee `insert_many` `insert_many()`方法允许进行批量创建。 `insert_many.py` ```py #!/usr/bin/env python3 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() ``` 该代码示例通过一次批量创建操作来重新创建`notes`表。 ```py 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) } ] ``` 我们在字典列表中定义数据。 ```py with db.atomic(): query = Note.insert_many(data) query.execute() ``` 我们执行批量操作。 `atomic()`方法将批量操作置于事务中。 ## Peewee 选择所有实例 `select()`方法用于检索定义的模型的实例。 `fetch_all.py` ```py #!/usr/bin/env python3 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`实例。 ```py notes = Note.select() ``` `select()`方法创建一个`SELECT`查询。 如果未明确提供任何字段,则查询将默认选择模型上定义的所有字段。 ```py $ ./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 ``` 这是输出。 ## Peewee `where`过滤器 `where()`方法可以根据给定条件过滤数据。 `where_clause.py` ```py #!/usr/bin/env python3 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 大于三的所有行。 ```py notes = Note.select().where(Note.id > 3) ``` `where()`方法对查询应用过滤条件。 ```py $ ./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`表达式。 `multiple_where_expr.py` ```py #!/usr/bin/env python3 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 的所有行。 ```py notes = Note.select().where((Note.id > 2) & (Note.id < 6)) ``` 我们将两个`where`表达式与`&`运算符结合使用。 ```py $ ./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()`方法。 `single_instance.py` ```py #!/usr/bin/env python3 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) ``` 该示例显示了如何以两种方式检索单个实例。 ```py note1 = Note.select().where(Note.text == 'Went to cinema').get() ``` 我们可以使用`Note.select().where().get()`方法链。 ```py note2 = Note.get(Note.text == 'Listened to music') ``` 还有一个`Note.get()`快捷方式,其功能相同。 ```py $ ./single_instance.py 3 Went to cinema 2018-10-05 4 Listened to music 2018-10-28 ``` 这是输出。 ## Peewee 选择特定的列 在`select()`方法内部,我们可以指定要包含在查询中的列的名称。 `columns.py` ```py #!/usr/bin/env python3 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 被跳过。 我们将查询限制为两个实例。 ```py $ ./columns.py [('Tai chi in the morning', datetime.date(2018, 10, 20)), ('Visited friend', datetime.date(2018, 10, 12))] ``` 这是输出。 ## Peewee 计数实例 要计算表中的模型实例数,我们可以使用`count()`方法。 `count_instances.py` ```py #!/usr/bin/env python3 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`的实例的数量。 ```py $ ./count_instances.py 7 4 ``` 这是输出。 ## Peewee 显示 SQL 语句 可以使用`sql()`方法显示生成的 SQL 语句。 `show_sql.py` ```py #!/usr/bin/env python3 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。 ```py $ ./show_sql.py ('SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1" WHERE ("t1"."id" = ?)', [3]) ``` 这是输出。 ## Peewee `offset`和`limit` 通过`offset`和`limit`属性,我们可以定义实例的初始跳过和要包含在`select()`中的实例数。 `offset_limit.py` ```py #!/usr/bin/env python3 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) ``` 该示例从第二个实例开始返回三个实例。 ```py $ ./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()`对检索到的实例进行排序。 `order_by.py` ```py #!/usr/bin/env python3 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) ``` 该代码示例按创建日期对实例进行排序。 ```py notes = Note.select(Note.text, Note.created).order_by(Note.created) ``` 该行返回按创建日期升序排列的笔记实例。 ```py notes = Note.select(Note.text, Note.created).order_by(Note.created.desc()) ``` 要按升序检索笔记,我们在字段上附加`desc()`方法。 ```py 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 标识的实例。 它返回已删除实例的数量。 `delete_by_id.py` ```py #!/usr/bin/env python3 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()`方法。 它返回成功删除的实例数。 `delete_instances.py` ```py #!/usr/bin/env python3 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 的所有实例。 ```py $ ./delete_instances.py 4 instances deleted ``` 在我们的例子中,我们删除了四个`Note`实例。 ## Peewee 更新实例 `update()`方法更新一个实例。 它返回成功更新的实例数。 `update_instance.py` ```py #!/usr/bin/env python3 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`创建模型之间的关系。 `customers_reservations.sql` ```py 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`。 两个表之间存在一对多的关系:一个客户可以进行很多预订。 ```py sqlite> .read customers_reservations.sql ``` 我们将 SQL 文件读入数据库。 `one2many.py` ```py #!/usr/bin/env python3 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) ``` 在示例中,我们定义了两个映射到表的模型。 然后,我们选择一个客户并显示其预订。 ```py customer = peewee.ForeignKeyField(Customer, backref='reservations') ``` `Customer`和`Reservation`模型之间的关系是通过`ForeignKeyField`创建的。 `backref`属性设置了我们如何引用客户的预订。 ```py for reservation in customer.reservations: ``` 客户实例具有`reservations`属性,其中包含相应的预留。 ```py $ ./one2many.py 1 2018-22-11 4 2018-29-11 ``` 这是输出。 在本教程中,我们介绍了 Python Peewee ORM。 您可能也对以下相关教程感兴趣: [Python 教程](/lang/python/), [pyDAL 教程](/python/pydal/), [PyMongo 教程](/python/pymongo/)和 [PyMySQL 教程](/python/pymysql/)。