Peewee 教程 · ZetCode 中文系列教程

# 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/)。