具有Flask，Connexion和SQLAlchemy的Python REST API –第3部分

In Part 2 of this series, you added the ability to save changes made through the REST API to a database using SQLAlchemy and learned how to serialize that data for the REST API using Marshmallow. Connecting the REST API to a database so that the application can make changes to existing data and create new data is great and makes the application much more useful and robust.

在本系列的第2部分中，您添加了使用SQLAlchemy将通过REST API进行的更改保存到数据库的功能，并学习了如何使用Marshmallow为REST API 序列化该数据。 将REST API连接到数据库，以便应用程序可以对现有数据进行更改并创建新数据，这非常有用，并使应用程序更加有用和强大。

That’s only part of the power a database offers, however. An even more powerful feature is the R part of RDBMS systems: relationships. In a database, a relationship is the ability to connect two or more tables together in a meaningful way. In this article, you’ll learn how to implement relationships and turn your Person database into a mini-blogging web application.

但是，这只是数据库提供的功能的一部分。 RDBMS系统的R部分是一个更强大的功能： 关系 。 在数据库中，关系是指以有意义的方式将两个或多个表连接在一起的能力。 在本文中，您将学习如何实现关系并将Person数据库变成微型博客Web应用程序。

In this article, you’ll learn:

在本文中，您将学习：

• Why more than one table in a database is useful and important
• How tables are related to each other
• How SQLAlchemy can help you manage relationships
• How relationships help you build a mini-blogging application

• 为什么数据库中有多个表是有用且重要的
• 表如何相互关联
• SQLAlchemy如何帮助您管理关系
• 关系如何帮助您构建微型博客应用程序

本文适用于谁 (Who This Article Is For)

Part 1 of this series guided you through building a REST API, and Part 2 showed you how to connect that REST API to a database.

本系列的第1部分将引导您构建REST API， 第2部分将向您展示如何将REST API连接到数据库。

This article expands your programming tool belt further. You’ll learn how to create hierarchical data structures represented as one-to-many relationships by SQLAlchemy. In addition, you’ll extend the REST API you’ve already built to provide CRUD (Create, Read, Update, and Delete) support for the elements in this hierarchical structure.

本文进一步扩展了您的编程工具范围。 您将学习如何创建由SQLAlchemy表示为一对多关系的分层数据结构。 此外，您将扩展已构建的REST API，以提供对此层次结构中的元素的CRUD（创建，读取，更新和删除）支持。

The web application presented in Part 2 will have its HTML and JavaScript files modified in major ways to create a more fully functional mini-blogging application. You can review the final version of the code from Part 2 in the GitHub repository for that article.

第2部分中介绍的Web应用程序将以主要方式修改其HTML和JavaScript文件，以创建功能更完善的微型博客应用程序。 您可以在该文章的GitHub存储库中查看第2部分中代码的最终版本。

Hang on as you get started creating relationships and your mini-blogging application!

继续创建关系和微型博客应用程序，请继续！

其他依赖 (Additional Dependencies)

There are no new Python dependencies beyond what was required for the Part 2 article. However, you will be using two new JavaScript modules in the web application to makes things easier and more consistent. The two modules are the following:

除了第2部分文章所要求的之外，没有新的Python依赖项。 但是，您将在Web应用程序中使用两个新JavaScript模块来使事情变得更加轻松和一致。 这两个模块如下：

1. Handlebars.js is a templating engine for JavaScript, much like Jinja2 for Flask.
2. Moment.js is a datetime parsing and formatting module that makes displaying UTC timestamps easier.

1. Handlebars.js是JavaScript的模板引擎，非常类似于Flask的Jinja2 。
2. Moment.js是一个日期时间解析和格式化模块，使显示UTC时间戳更加容易。

You don’t have to download either of these, as the web application will get them directly from the Cloudflare CDN (Content Delivery Network), as you’re already doing for the jQuery module.

您无需下载任何一个，因为Web应用程序将直接从Cloudflare CDN （ 内容交付网络 ）获取它们，就像您已经为jQuery模块所做的那样。

人员数据扩展用于博客 (People Data Extended for Blogging)

In Part 2, the People data existed as a dictionary in the build_database.py Python code. This is what you used to populate the database with some initial data. You’re going to modify the People data structure to give each person a list of notes associated with them. The new People data structure will look like this:

在第2部分中， People数据在build_database.py Python代码中以字典的build_database.py存在。 这就是您用一些初始数据填充数据库的方式。 您将修改People数据结构，以为每个人提供与他们相关的注释列表。 新的People数据结构将如下所示：

 # Data to initialize database with
# Data to initialize database with
PEOPLE PEOPLE = = [
    [
    {
        {
        "fname""fname" : : "Doug""Doug" ,
        ,
        "lname""lname" : : "Farrell""Farrell" ,
        ,
        "notes""notes" : : [
            [
            (( "Cool, a mini-blogging application!""Cool, a mini-blogging application!" , , "2019-01-06 22:17:54""2019-01-06 22:17:54" ),
            ),
            (( "This could be useful""This could be useful" , , "2019-01-08 22:17:54""2019-01-08 22:17:54" ),
            ),
            (( "Well, sort of useful""Well, sort of useful" , , "2019-03-06 22:17:54""2019-03-06 22:17:54" ),
        ),
        ],
    ],
    },
    },
    {
        {
        "fname""fname" : : "Kent""Kent" ,
        ,
        "lname""lname" : : "Brockman""Brockman" ,
        ,
        "notes""notes" : : [
            [
            (
                (
                "I'm going to make really profound observations""I'm going to make really profound observations" ,
                ,
                "2019-01-07 22:17:54""2019-01-07 22:17:54" ,
            ,
            ),
            ),
            (
                (
                "Maybe they'll be more obvious than I thought""Maybe they'll be more obvious than I thought" ,
                ,
                "2019-02-06 22:17:54""2019-02-06 22:17:54" ,
            ,
            ),
        ),
        ],
    ],
    },
    },
    {
        {
        "fname""fname" : : "Bunny""Bunny" ,
        ,
        "lname""lname" : : "Easter""Easter" ,
        ,
        "notes""notes" : : [
            [
            (( "Has anyone seen my Easter eggs?""Has anyone seen my Easter eggs?" , , "2019-01-07 22:47:54""2019-01-07 22:47:54" ),
            ),
            (( "I'm really late delivering these!""I'm really late delivering these!" , , "2019-04-06 22:17:54""2019-04-06 22:17:54" ),
        ),
        ],
    ],
    },
},
]
]

Each person in the People dictionary now includes a key called notes, which is associated with a list containing tuples of data. Each tuple in the notes list represents a single note containing the content and a timestamp. The timestamps are initialized (rather than dynamically created) to demonstrate ordering later on in the REST API.

现在， People词典中的每个人都包括一个称为notes的键，该键与包含数据元组的列表相关联。 notes列表中的每个元组代表一个包含内容和时间戳的单个注释 。 时间戳会被初始化（而不是动态创建），以演示稍后在REST API中的排序。

Each single person is associated with multiple notes, and each single note is associated with only one person. This hierarchy of data is known as a one-to-many relationship, where a single parent object is related to many child objects. You’ll see how this one-to-many relationship is managed in the database with SQLAlchemy.

每个单独的人都与多个注释相关联，并且每个单个的便笺仅与一个人相关联。 这种数据层次结构称为一对多关系，其中单个父对象与许多子对象相关。 您将看到如何使用SQLAlchemy在数据库中管理这种一对多关系。

蛮力法 (Brute Force Approach)

The database you built stored the data in a table, and a table is a two-dimensional array of rows and columns. Can the People dictionary above be represented in a single table of rows and columns? It can be, in the following way, in your person database table. Unfortunately to include all of the actual data in the example creates a scroll bar for the table, as you’ll see below:

您建立的数据库将数据存储在一个表中，而表是行和列的二维数组。 上面的People词典可以用行和列的单个表表示吗？ 它可以通过以下方式位于您的person数据库表中。 不幸的是，要在示例中包含所有实际数据，会为表格创建一个滚动条，如下所示：

person_idperson_id	lnamelname	fnamefname	timestamptimestamp	contentcontent	note_timestampnote_timestamp
1	1个	Farrell	法雷尔	Doug	道格	2018-08-08 21:16:01	2018-08-08 21:16:01	Cool, a mini-blogging application!	酷，一个迷你博客应用程序！	2019-01-06 22:17:54	2019-01-06 22:17:54
2	2	Farrell	法雷尔	Doug	道格	2018-08-08 21:16:01	2018-08-08 21:16:01	This could be useful	这可能很有用	2019-01-08 22:17:54	2019-01-08 22:17:54
3	3	Farrell	法雷尔	Doug	道格	2018-08-08 21:16:01	2018-08-08 21:16:01	Well, sort of useful	好吧，很有用	2019-03-06 22:17:54	2019-03-06 22:17:54
4	4	Brockman	布罗克曼	Kent	肯特郡	2018-08-08 21:16:01	2018-08-08 21:16:01	I’m going to make really profound observations	我将进行非常深刻的观察	2019-01-07 22:17:54	2019-01-07 22:17:54
5	5	Brockman	布罗克曼	Kent	肯特郡	2018-08-08 21:16:01	2018-08-08 21:16:01	Maybe they’ll be more obvious than I thought	也许它们会比我想象的更明显	2019-02-06 22:17:54	2019-02-06 22:17:54
6	6	Easter	复活节	Bunny	兔子	2018-08-08 21:16:01	2018-08-08 21:16:01	Has anyone seen my Easter eggs?	有人看过我的复活节彩蛋吗？	2019-01-07 22:47:54	2019-01-07 22:47:54
7	7	Easter	复活节	Bunny	兔子	2018-08-08 21:16:01	2018-08-08 21:16:01	I’m really late delivering these!	我真的迟到了！	2019-04-06 22:17:54	2019-04-06 22:17:54

The above table would actually work. All the data is represented, and a single person is associated with a collection of different notes.

上表将实际工作。 表示所有数据，并且一个人与不同笔记的集合相关联。

优点 (Advantages)

Conceptually, the above table structure has the advantage of being relatively simple to understand. You could even make the case that the data could be persisted to a flat file instead of a database.

从概念上讲，上述表结构具有易于理解的优点。 您甚至可以假设数据可以保存到平面文件而不是数据库中。

Because of the two-dimensional table structure, you could store and use this data in a spreadsheet. Spreadsheets have been pressed into service as data storage quite a bit.

由于是二维表结构，因此可以在电子表格中存储和使用此数据。 电子表格已被大量用作数据存储服务。

缺点 (Disadvantages)

While the above table structure would work, it has some real disadvantages.

尽管上面的表结构可以工作，但它有一些实际的缺点。

In order to represent the collection of notes, all the data for each person is repeated for every unique note, the person data is therefore redundant. This isn’t such a big deal for your person data as there aren’t that many columns. But imagine if a person had many more columns. Even with large disk drives, this can get to be a storage concern if you’re dealing with millions of rows of data.

为了表示笔记的集合，针对每个唯一笔记重复每个人的所有数据，因此该人数据是冗余的。 对于您的个人数据来说，这并不是什么大问题，因为没有那么多列。 但是想象一下，如果一个人有更多的专栏。 即使使用大型磁盘驱动器，如果要处理数百万行的数据，这也可能成为存储问题。

Having redundant data like this can lead to maintenance issues as time goes by. For example, what if the Easter Bunny decided a change of name was a good idea. In order to do this, every record containing the Easter Bunny’s name would have to be updated in order to keep the data consistent. This kind of work against the database can lead to data inconsistency, particularly if the work is done by a person running a SQL query by hand.

随着时间的流逝，拥有这样的冗余数据可能会导致维护问题。 例如，如果复活节兔子决定改名该怎么办？ 为此，必须更新包含复活节兔子名字的每条记录，以使数据保持一致。 针对数据库的这种工作可能导致数据不一致，尤其是如果该工作是由手动运行SQL查询的人员完成的。

Naming columns becomes awkward. In the table above, there is a timestamp column used to track the creation and update time of a person in the table. You also want to have similar functionality for the creation and update time for a note, but because timestamp is already used, a contrived name of note_timestamp is used.

命名列变得很尴尬。 上表中有一个timestamp列，用于跟踪表中人员的创建和更新时间。 您还希望为便笺的创建和更新时间具有类似的功能，但是由于已经使用了timestamp因此使用了人为的note_timestamp名称。

What if you wanted to add additional one-to-many relationships to the person table? For example, to include a person’s children or phone numbers. Each person could have multiple children and multiple phone numbers. This could be done relatively easily to the Python People dictionary above by adding children and phone_numbers keys with new lists containing the data.

如果要向person表添加其他一对多关系怎么办？ 例如，包括一个人的孩子或电话号码。 每个人可以有多个孩子和多个电话号码。 通过添加children和phone_numbers键以及包含数据的新列表，可以相对轻松地完成上述Python People词典。

However, representing those new one-to-many relationships in your person database table above becomes significantly more difficult. Every new one-to-many relationship increases the number of rows necessary to represent it for every single entry in the child data dramatically. In addition, the problems associated with data redundancy get bigger and more difficult to handle.

但是，在上面的person数据库表中表示这些新的一对多关系变得非常困难。 每个新的一对多关系都会显着增加子数据中每个单个条目表示它所必需的行数。 另外，与数据冗余相关的问题变得越来越大，也越来越难以处理。

Lastly, the data you’d get back from the above table structure wouldn’t be very Pythonic: it would be just a big list of lists. SQLAlchemy wouldn’t be able to help you very much because the relationship isn’t there.

最后，您将从上述表结构中获取的数据不是Python风格的：这只是列表的一大清单。 SQLAlchemy将无法为您提供很大帮助，因为这种关系不存在。

关系数据库方法 (Relational Database Approach)

Based on what you’ve seen above, it becomes clear that trying to represent even a moderately complex dataset in a single table becomes unmanageable pretty quickly. Given that, what alternative does a database offer? This is where the R part of RDBMS databases comes into play. Representing relationships removes the disadvantages outlined above.

根据上面所看到的，很明显，试图在单个表中甚至表示中等复杂的数据集也变得非常难以管理。 鉴于此，数据库提供什么选择？ 这是RDBMS数据库的R部分起作用的地方。 表示关系消除了上面概述的缺点。

Instead of trying to represent hierarchical data in a single table, the data is broken up into multiple tables, with a mechanism to relate them to one another. The tables are broken along collection lines, so for your People dictionary above, this means there will be a table representing people and another representing notes. This brings back your original person table, which looks like this:

与其尝试在单个表中表示层次结构数据，不如将数据分解为多个表，并采用一种机制将它们彼此关联。 这些表格是沿着收集线断开的，因此对于上面的People词典，这意味着将有一个代表人的表和另一个代表笔记的表。 这将带回您的原始person表，如下所示：

person_idperson_id	lnamelname	fnamefname	timestamptimestamp
1	1个	Farrell	法雷尔	Doug	道格	2018-08-08 21:16:01.888444	2018-08-08 21：16：01.888444
2	2	Brockman	布罗克曼	Kent	肯特郡	2018-08-08 21:16:01.889060	2018-08-08 21：16：01.889060
3	3	Easter	复活节	Bunny	兔子	2018-08-08 21:16:01.886834	2018-08-08 21：16：01.886834

To represent the new note information, you’ll create a new table called note. (Remember our singular table naming convention.) The table looks like this:

为了表示新的笔记信息，您将创建一个名为note的新表。 （记住我们的单表命名约定。）该表如下所示：

note_idnote_id	person_idperson_id	contentcontent	timestamptimestamp
1	1个	1	1个	Cool, a mini-blogging application!	酷，一个迷你博客应用程序！	2019-01-06 22:17:54	2019-01-06 22:17:54
2	2	1	1个	This could be useful	这可能很有用	2019-01-08 22:17:54	2019-01-08 22:17:54
3	3	1	1个	Well, sort of useful	好吧，很有用	2019-03-06 22:17:54	2019-03-06 22:17:54
4	4	2	2	I’m going to make really profound observations	我将进行非常深刻的观察	2019-01-07 22:17:54	2019-01-07 22:17:54
5	5	2	2	Maybe they’ll be more obvious than I thought	也许它们会比我想象的更明显	2019-02-06 22:17:54	2019-02-06 22:17:54
6	6	3	3	Has anyone seen my Easter eggs?	有人看过我的复活节彩蛋吗？	2019-01-07 22:47:54	2019-01-07 22:47:54
7	7	3	3	I’m really late delivering these!	我真的迟到了！	2019-04-06 22:17:54	2019-04-06 22:17:54

Notice that, like the person table, the note table has a unique identifier called note_id, which is the primary key for the note table. One thing that isn’t obvious is the inclusion of the person_id value in the table. What is that used for? This is what creates the relationship to the person table. Whereas note_id is the primary key for the table, person_id is what’s known as a foreign key.

注意，与person表一样， note表具有唯一的标识符，称为note_id ，该标识符是note表的主键。 不明显的一件事是在表中包含person_id值。 那是用来干什么的？ 这就是创建与person表的关系的原因。 而note_id是表的主键， person_id是外键 。

The foreign key gives each entry in the note table the primary key of the person record it’s associated with. Using this, SQLAlchemy can gather all the notes associated with each person by connecting the person.person_id primary key to the note.person_id foreign key, creating a relationship.

外键为note表中的每个条目提供与之关联的person记录的主键。 使用此方法，SQLAlchemy可以通过将person.person_id主键连接到note.person_id外键来收集与每个人关联的所有注释，从而创建关系。

优点 (Advantages)

By breaking the data set into two tables, and introducing the concept of a foreign key, you’ve made the data a little more complex to think about, you have resolved the disadvantages of a single table representation. SQLAlchemy will help you encode the increased complexity fairly easily.

通过将数据集分成两个表并引入外键的概念，您使数据的考虑变得更加复杂，解决了单个表表示的缺点。 SQLAlchemy将帮助您相当轻松地编码增加的复杂性。

The data is no longer redundant in the database. There is only one person entry for each person you want to store in the database. This solves the storage concern immediately and dramatically simplifies the maintenance concerns.

数据在数据库中不再冗余。 您要存储在数据库中的每个人只有一个人条目。 这立即解决了存储问题，并大大简化了维护问题。

If the Easter Bunny still wanted to change names, then you’d only have to change a single row in the person table, and anything else related to that row (like the note table) would immediately take advantage of the change.

如果Easter Bunny仍想更改名称，则只需更改person表中的一行，并且与该行相关的任何其他内容（如note表）都将立即利用该更改。

Column naming is more consistent and meaningful. Because person and note data exist in separate tables, the creation and update timestamp can be named consistently in both tables, as there is no conflict for names across tables.

列命名更加一致和有意义。 由于人员和注释数据存在于单独的表中，因此可以在两个表中一致地命名创建和更新时间戳，因为跨表的名称没有冲突。

In addition, you’d no longer have to create permutations of each row for new one-to-many relationships you might want to represent. Take our children and phone_numbers example from earlier. Implementing this would require child and phone_number tables. Each table would contain a foreign key of person_id relating it back to the person table.

此外，您不再需要为可能要表示的新的一对多关系创建每一行的排列。 以我们前面的children和phone_numbers为例。 要实现这一点，将需要child和phone_number表。 每个表将包含person_id的外键，该外键将person_id与person表相关联。

Using SQLAlchemy, the data you’d get back from the above tables would be more immediately useful, as what you’d get is an object for each person row. That object has named attributes equivalent to the columns in the table. One of those attributes is a Python list containing the related note objects.

使用SQLAlchemy，从上表中获取的数据将更加有用，因为您获取的是每个人行的对象。 该对象具有与表中列等效的命名属性。 这些属性之一是包含相关注释对象的Python列表。

缺点 (Disadvantages)

Where the brute force approach was simpler to understand, the concept of foreign keys and relationships make thinking about the data somewhat more abstract. This abstraction needs to be thought about for every relationship you establish between tables.

在蛮力方法更易于理解的地方，外键和关系的概念使对数据的思考更加抽象。 对于表之间建立的每个关系，都需要考虑这种抽象。

Making use of relationships means committing to using a database system. This is another tool to install, learn, and maintain above and beyond the application that actually uses the data.

利用关系意味着致力于使用数据库系统。 这是在实际使用数据的应用程序之外安装，学习和维护的另一种工具。

SQLAlchemy模型 (SQLAlchemy Models)

To use the two tables above, and the relationship between them, you’ll need to create SQLAlchemy models that are aware of both tables and the relationship between them. Here’s the SQLAlchemy Person model from Part 2, updated to include a relationship to a collection of notes:

要使用上面的两个表及其之间的关系，您需要创建一个知道两个表及其之间关系SQLAlchemy模型。 这是第2部分中SQLAlchemy Person模型，已更新为包括与notes集合的关系：

Lines 1 to 8 of the above Python class look exactly like what you created before in Part 2. Lines 9 to 16 create a new attribute in the Person class called notes. This new notes attributes is defined in the following lines of code:

上面的Python类的第1至8行与您在第2部分中之前创建的完全相同。第9至16 Person类中创建了一个称为notes的新属性。 在以下代码行中定义了这个新的notes属性：

• Line 9: Like the other attributes of the class, this line creates a new attribute called notes and sets it equal to an instance of an object called db.relationship. This object creates the relationship you’re adding to the Person class and is created with all of the parameters defined in the lines that follow.

• Line 10: The string parameter 'Note' defines the SQLAlchemy class that the Person class will be related to. The Note class isn’t defined yet, which is why it’s a string here. This is a forward reference and helps handle problems that the order of definitions could cause when something is needed that isn’t defined until later in the code. The 'Note' string allows the Person class to find the Note class at runtime, which is after both Person and Note have been defined.

• Line 11: The backref='person' parameter is trickier. It creates what’s known as a backwards reference in Note objects. Each instance of a Note object will contain an attribute called person. The person attribute references the parent object that a particular Note instance is associated with. Having a reference to the parent object (person in this case) in the child can be very useful if your code iterates over notes and has to include information about the parent. This happens surprisingly often in display rendering code.

• Line 12: The cascade='all, delete, delete-orphan' parameter determines how to treat note object instances when changes are made to the parent Person instance. For example, when a Person object is deleted, SQLAlchemy will create the SQL necessary to delete the Person from the database. Additionally, this parameter tells it to also delete all the Note instances associated with it. You can read more about these options in the SQLAlchemy documentation.

• Line 13: The single_parent=True parameter is required if delete-orphan is part of the previous cascade parameter. This tells SQLAlchemy not to allow orphaned Note instances (a Note without a parent Person object) to exist because each Note has a single parent.

• Line 14: The order_by='desc(Note.timestamp)' parameter tells SQLAlchemy how to sort the Note instances associated with a Person. When a Person object is retrieved, by default the notes attribute list will contain Note objects in an unknown order. The SQLAlchemy desc(...) function will sort the notes in descending order from newest to oldest. If this line was instead order_by='Note.timestamp', SQLAlchemy would default to using the asc(...) function, and sort the notes in ascending order, oldest to newest.

• 第9行：与该类的其他属性一样，此行创建一个称为notes的新属性，并将其设置为等于名为db.relationship的对象的实例。 该对象创建您要添加到Person类的关系，并使用Person行中定义的所有参数创建该关系。

• 第10行：字符串参数'Note'定义了与Person类相关SQLAlchemy类。 Note类尚未定义，这就是为什么它在这里是字符串的原因。 这是一个前瞻性参考，有助于解决需要在代码稍后才定义的内容时定义顺序可能导致的问题。 'Note'字符串允许Person类在定义了Person和Note之后，在运行时查找Note类。

• 第11行： backref='person'参数比较棘手。 它在Note对象中创建所谓的向后引用。 Note对象的每个实例将包含一个名为person的属性。 person属性引用与特定Note实例关联的父对象。 如果您的代码遍历笔记并必须包含有关父级的信息，则在子级中引用父级对象（在这种情况下person ）可能会非常有用。 这在显示渲染代码中经常出乎意料地发生。

• 第12行： cascade='all, delete, delete-orphan'参数确定在对父Person实例进行更改时如何处理笔记对象实例。 例如，当删除Person对象时，SQLAlchemy将创建从数据库中删除Person所必需SQL。 此外，此参数告诉它还删除与其关联的所有Note实例。 您可以在SQLAlchemy文档中阅读有关这些选项的更多信息。

• 第13行：如果delete-orphan是先前cascade参数的一部分，则需要single_parent=True参数。 这告诉SQLAlchemy不允许存在孤立的Note实例（没有父Person对象的Note ），因为每个Note都有一个父对象。

• 第14行： order_by='desc(Note.timestamp)'参数告诉SQLAlchemy如何对与Person相关的Note实例进行排序。 当一个Person对象被检索，默认情况下， notes的属性列表将包含Note未知顺序的对象。 SQLAlchemy desc(...)函数将按从新到旧的降序对笔记进行排序。 如果此行改为order_by='Note.timestamp' ，则SQLAlchemy将默认使用asc(...)函数，并按升序对笔记进行排序，从最早到最新。

Now that your Person model has the new notes attribute, and this represents the one-to-many relationship to Note objects, you’ll need to define a SQLAlchemy model for a Note:

现在，您的Person模型具有新的notes属性，并且它表示与Note对象的一对多关系，您需要为Note定义一个SQLAlchemy模型：

 class class NoteNote (( dbdb .. ModelModel ):
    ):
    __tablename__ __tablename__ = = 'note'
    'note'
    note_id note_id = = dbdb .. ColumnColumn (( dbdb .. IntegerInteger , , primary_keyprimary_key == TrueTrue )
    )
    person_id person_id = = dbdb .. ColumnColumn (( dbdb .. IntegerInteger , , dbdb .. ForeignKeyForeignKey (( 'person.person_id''person.person_id' ))
    ))
    content content = = dbdb .. ColumnColumn (( dbdb .. StringString , , nullablenullable == FalseFalse )
    )
    timestamp timestamp = = dbdb .. ColumnColumn (
        (
        dbdb .. DateTimeDateTime , , defaultdefault == datetimedatetime .. utcnowutcnow , , onupdateonupdate == datetimedatetime .. utcnow
    utcnow
    )
)

The Note class defines the attributes making up a note as seen in our sample note database table from above. The attributes are defined here:

Note类定义组成注释的属性，如上面的示例note数据库表所示。 属性在此处定义：

• Line 1 creates the Note class, inheriting from db.Model, exactly as you did before when creating the Person class.

• Line 2 tells the class what database table to use to store Note objects.

• Line 3 creates the note_id attribute, defining it as an integer value, and as the primary key for the Note object.

• Line 4 creates the person_id attribute, and defines it as the foreign key, relating the Note class to the Person class using the person.person_id primary key. This, and the Person.notes attribute, are how SQLAlchemy knows what to do when interacting with Person and Note objects.

• Line 5 creates the content attribute, which contains the actual text of the note. The nullable=False parameter indicates that it’s okay to create new notes that have no content.

• Line 6 creates the timestamp attribute, and exactly like the Person class, this contains the creation or update time for any particular Note instance.

• 第1行创建了从db.Model继承的Note类，与创建Person类时完全一样。

• 第2行告诉该类要使用哪个数据库表存储Note对象。

• 第3行创建了note_id属性，将其定义为整数值，并作为Note对象的主键。

• 第4行创建person_id属性，并将其定义为外键，并使用person.person_id主键将Note类与Person类相关联。 这以及Person.notes属性是SQLAlchemy如何与Person和Note对象进行交互时知道该怎么做的方式。

• 第5行创建content属性，其中包含注释的实际文本。 nullable=False参数指示可以创建不包含任何内容的新注释。

• 第6行创建了timestamp属性，与Person类完全一样，它包含任何特定Note实例的创建或更新时间。

初始化数据库 (Initialize the Database)

Now that you’ve updated the Person and created the Note models, you’ll use them to rebuild the testing database people.db. You’ll do this by updating the build_database.py code from Part 2. Here’s what the code will look like:

现在，您已经更新了Person并创建了Note模型，您将使用它们来重建测试数据库people.db 。 您将通过更新第2部分中的build_database.py代码来完成此操作。代码如下所示：

The code above came from Part 2, with a few changes to create the one-to-many relationship between Person and Note. Here are the updated or new lines added to the code:

上面的代码来自第2部分，进行了一些更改，以在Person和Note之间创建一对多关系。 这是添加到代码中的更新或新行：

• Line 4 has been updated to import the Note class defined previously.

• Lines 7 to 39 contain the updated PEOPLE dictionary containing our person data, along with the list of notes associated with each person. This data will be inserted into the database.

• Lines 49 to 61 iterate over the PEOPLE dictionary, getting each person in turn and using it to create a Person object.

• Line 53 iterates over the person.notes list, getting each note in turn.

• Line 54 unpacks the content and timestamp from each note tuple.

• Line 55 to 60 creates a Note object and appends it to the person notes collection using p.notes.append().

• Line 61 adds the Person object p to the database session.

• Line 63 commits all of the activity in the session to the database. It’s at this point that all of the data is written to the person and note tables in the people.db database file.

• 第4行已更新，可以导入先前定义的Note类。

• 第7至39行包含更新的PEOPLE词典，该词典包含我们的人员数据以及与每个人员相关的注释列表。 该数据将被插入数据库。

• 第49至61行在PEOPLE词典上进行迭代，依次轮流让每个person使用它来创建Person对象。

• 第53行遍历person.notes列表，依次获取每个note 。

• 第54行从每个note元组解压缩content和timestamp 。

• 第55至60行创建一个Note对象，并使用p.notes.append()将其附加到人员note集合。

• 第61行将Person对象p添加到数据库会话中。

• 第63行将会话中的所有活动提交到数据库。 至此，所有数据都被写入people.db数据库文件中的person和note表。

You can see that working with the notes collection in the Person object instance p is just like working with any other list in Python. SQLAlchemy takes care of the underlying one-to-many relationship information when the db.session.commit() call is made.

您可以看到在Person对象实例p使用notes集合就像在Python中使用其他列表一样。 进行db.session.commit()调用时，SQLAlchemy会处理基础的一对多关系信息。

For example, just like a Person instance has its primary key field person_id initialized by SQLAlchemy when it’s committed to the database, instances of Note will have their primary key fields initialized. In addition, the Note foreign key person_id will also be initialized with the primary key value of the Person instance it’s associated with.

例如，就像一个Person实例在提交给数据库时通过SQLAlchemy初始化其主键字段person_id ， Note实例也将对其主键字段进行初始化。 此外， Note外键person_id还将使用与其关联的Person实例的主键值进行初始化。

Here’s an example instance of a Person object before the db.session.commit() in a kind of pseudocode:

这是db.session.commit()之前的伪代码形式的Person对象的示例实例：

Person (
    person_id = None
    lname = 'Farrell'
    fname = 'Doug'
    timestamp = None
    notes = [
        Note (
            note_id = None
            person_id = None
            content = 'Cool, a mini-blogging application!'
            timestamp = '2019-01-06 22:17:54'
        ),
        Note (
            note_id = None
            person_id = None
            content = 'This could be useful'
            timestamp = '2019-01-08 22:17:54'
        ),
        Note (
            note_id = None
            person_id = None
            content = 'Well, sort of useful'
            timestamp = '2019-03-06 22:17:54'
        )
    ]
)
Person (
    person_id = None
    lname = 'Farrell'
    fname = 'Doug'
    timestamp = None
    notes = [
        Note (
            note_id = None
            person_id = None
            content = 'Cool, a mini-blogging application!'
            timestamp = '2019-01-06 22:17:54'
        ),
        Note (
            note_id = None
            person_id = None
            content = 'This could be useful'
            timestamp = '2019-01-08 22:17:54'
        ),
        Note (
            note_id = None
            person_id = None
            content = 'Well, sort of useful'
            timestamp = '2019-03-06 22:17:54'
        )
    ]
)

Here’s the example Person object after the db.session.commit():

这是db.session.commit()之后的示例Person对象：

The important difference between the two is that the primary key of the Person and Note objects has been initialized. The database engine took care of this as the objects were created because of the auto-incrementing feature of primary keys discussed in Part 2.

两者之间的重要区别是Person和Note对象的主键已经初始化。 由于第2部分中讨论的主键具有自动递增功能，因此数据库引擎在创建对象时会注意这一点。

Additionally, the person_id foreign key in all the Note instances has been initialized to reference its parent. This happens because of the order in which the Person and Note objects are created in the database.

此外，所有Note实例中的person_id外键均已初始化为引用其父代。 发生这种情况的原因是在数据库中创建Person和Note对象的顺序。

SQLAlchemy is aware of the relationship between Person and Note objects. When a Person object is committed to the person database table, SQLAlchemy gets the person_id primary key value. That value is used to initialize the foreign key value of person_id in a Note object before it’s committed to the database.

SQLAlchemy知道Person和Note对象之间的关系。 将Person对象提交到person数据库表后，SQLAlchemy将获得person_id主键值。 该值用于在Note对象person_id的外键值提交给数据库之前对其进行初始化。

SQLAlchemy takes care of this database housekeeping work because of the information you passed when the Person.notes attribute was initialized with the db.relationship(...) object.

由于使用db.relationship(...)对象初始化Person.notes属性时传递的信息，因此SQLAlchemy负责此数据库内务处理工作。

In addition, the Person.timestamp attribute has been initialized with the current timestamp.

另外， Person.timestamp属性已使用当前时间戳进行初始化。

Running the build_database.py program from the command line (in the virtual environment will re-create the database with the new additions, getting it ready for use with the web application. This command line will rebuild the database:

build_database.py运行build_database.py程序（在虚拟环境中，将使用新添加的内容重新创建数据库，使其可以与Web应用程序一起使用。此命令行将重建数据库：

 $ python build_database.py
$ python build_database.py

The build_database.py utility program doesn’t output any messages if it runs successfully. If it throws an exception, then an error will be printed on the screen.

如果成功运行build_database.py实用程序，则不会输出任何消息。 如果抛出异常，则屏幕上将打印错误。

更新REST API (Update REST API)

You’ve updated the SQLAlchemy models and used them to update the people.db database. Now it’s time to update the REST API to provide access to the new notes information. Here’s the REST API you built in Part 2:

您已经更新了SQLAlchemy模型，并使用它们来更新了people.db数据库。 现在是时候更新REST API，以提供对新注释信息的访问。 这是您在第2部分中构建的REST API：

Action	行动	HTTP Verb	HTTP动词	URL Path	URL路径	Description	描述
Create	创造	POSTPOST	/api/people/api/people	URL to create a new person	创建新人物的网址
Read	读	GETGET	/api/people/api/people	URL to read a collection of people	读取人的URL
Read	读	GETGET	/api/people/{person_id}/api/people/{person_id}	URL to read a single person by person_id	通过person_id阅读一个人的person_id
Update	更新资料	PUTPUT	/api/people/{person_id}/api/people/{person_id}	URL to update an existing person by person_id	通过person_id更新现有人员的person_id
Delete	删除	DELETEDELETE	/api/people/{person_id}/api/people/{person_id}	URL to delete an existing person by person_id	通过person_id删除现有人员的person_id

The REST API above provides HTTP URL paths to collections of things, and to the things themselves. You can get a list of people or interact with a single person from that list of people. This path style refines what’s returned in a left-to-right manner, getting more granular as you go.

上面的REST API提供了指向事物集合以及事物本身的HTTP URL路径。 您可以获取人员列表或与该人员列表中的单个人员进行交互。 此路径样式以从左到右的方式优化了返回的内容，随着您的前进，它变得越来越细。

You’ll continue this left-to-right pattern to get more granular and access the notes collections. Here’s the extended REST API you’ll create in order to provide notes to the mini-blog web application:

您将继续使用这种从左到右的模式，以获取更多详细信息并访问notes集合。 这是您将创建的扩展REST API，以向微型博客Web应用程序提供注释：

Action	行动	HTTP Verb	HTTP动词	URL Path	URL路径	Description	描述
Create	创造	POSTPOST	/api/people/{person_id}/notes/api/people/{person_id}/notes	URL to create a new note	创建新笔记的URL
Read	读	GETGET	/api/people/{person_id}/notes/{note_id}/api/people/{person_id}/notes/{note_id}	URL to read a single person’s single note	读取单人笔记的URL
Update	更新资料	PUTPUT	api/people/{person_id}/notes/{note_id}api/people/{person_id}/notes/{note_id}	URL to update a single person’s single note	用于更新单个人的单个笔记的URL
Delete	删除	DELETEDELETE	api/people/{person_id}/notes/{note_id}api/people/{person_id}/notes/{note_id}	URL to delete a single person’s single note	删除单个人的单个笔记的URL
Read	读	GETGET	/api/notes/api/notes	note.timestampnote.timestamp排序）

There are two variations in the notes part of the REST API compared to the convention used in the people section:

与people部分中使用的约定相比，REST API的notes部分有两种变化：

1. There is no URL defined to get all the notes associated with a person, only a URL to get a single note. This would have made the REST API complete in a way, but the web application you’ll create later doesn’t need this functionality. Therefore, it’s been left out.

2. There is the inclusion of the last URL /api/notes. This is a convenience method created for the web application. It will be used in the mini-blog on the home page to show all the notes in the system. There isn’t a way to get this information readily using the REST API pathing style as designed, so this shortcut has been added.

1. 没有定义URL来获取与一个人关联的所有notes ，只有URL来获取单个笔记。 这样可以使REST API完整一些，但是稍后将创建的Web应用程序不需要此功能。 因此，它被排除在外了。

2. 最后一个URL /api/notes包含在内。 这是为Web应用程序创建的便捷方法。 将在主页的迷你博客中使用它来显示系统中的所有注释。 无法使用设计的REST API路径样式轻松获取此信息，因此已添加了此快捷方式 。

As in Part 2, the REST API is configured in the swagger.yml file.

与第2部分相同，REST API在swagger.yml文件中进行配置。

Note:

注意：

The idea of designing a REST API with a path that gets more and more granular as you move from left to right is very useful. Thinking this way can help clarify the relationships between different parts of a database. Just be aware that there are realistic limits to how far down a hierarchical structure this kind of design should be taken.

设计一种REST API的想法非常有用，它的路径随着您从左向右移动而变得越来越细。 以这种方式思考可以帮助阐明数据库不同部分之间的关​​系。 请注意，这种设计应在多层次的结构中走多远，这是现实的限制。

For example, what if the Note object had a collection of its own, something like comments on the notes. Using the current design ideas, this would lead to a URL that went something like this: /api/people/{person_id}/notes/{note_id}/comments/{comment_id}

例如，如果“ Note对象具有自己的集合，例如注释上的注释，该怎么办。 使用当前的设计思想，这将导致URL变为以下内容： /api/people/{person_id}/notes/{note_id}/comments/{comment_id}

There is no practical limit to this kind of design, but there is one for usefulness. In actual use in real applications, a long, multilevel URL like that one is hardly ever needed. A more common pattern is to get a list of intervening objects (like notes) and then use a separate API entry point to get a single comment for an application use case.

这种设计没有实际的限制，但实用性是有限制的。 在实际应用中的实际使用中，几乎不需要像这样的长而多级的URL。 一种更常见的模式是获取介入对象的列表（如注释），然后使用单独的API入口点为应用程序用例获取单个注释。

实施API (Implement the API)

With the updated REST API defined in the swagger.yml file, you’ll need to update the implementation provided by the Python modules. This means updating existing module files, like models.py and people.py, and creating a new module file called notes.py to implement support for Notes in the extended REST API.

使用swagger.yml文件中定义的更新的REST API，您需要更新Python模块提供的实现。 这意味着更新现有的模块文件，例如models.py和people.py ，并创建一个名为notes.py的新模块文件，以在扩展的REST API中实现对Notes支持。

更新响应JSON (Update Response JSON)

The purpose of the REST API is to get useful JSON data out of the database. Now that you’ve updated the SQLAlchemy Person and created the Note models, you’ll need to update the Marshmallow schema models as well. As you may recall from Part 2, Marshmallow is the module that translates the SQLAlchemy objects into Python objects suitable for creating JSON strings.

REST API的目的是从数据库中获取有用的JSON数据。 现在，您已经更新了SQLAlchemy Person并创建了Note模型，您还需要更新Marshmallow模式模型。 您可能会在第2部分中回忆过，棉花糖是将SQLAlchemy对象转换为适合创建JSON字符串的Python对象的模块。

The updated and newly created Marshmallow schemas are in the models.py module, which are explained below, and look like this:

更新和新创建的棉花糖模式位于models.py模块中，下面对此进行说明，如下所示：

There are some interesting things going on in the above definitions. The PersonSchema class has one new entry: the notes attribute defined in line 5. This defines it as a nested relationship to the PersonNoteSchema. It will default to an empty list if nothing is present in the SQLAlchemy notes relationship. The many=True parameter indicates that this is a one-to-many relationship, so Marshmallow will serialize all the related notes.

上面的定义中发生了一些有趣的事情。 PersonSchema类具有一个新条目：在第5行中定义的notes属性。这将其定义为与PersonNoteSchema的嵌套关系。 如果SQLAlchemy notes关系中不存在任何内容，它将默认为空列表。 many=True参数指示这是一对多关系，因此棉花糖将序列化所有相关notes 。

The PersonNoteSchema class defines what a Note object looks like as Marshmallow serializes the notes list. The NoteSchema defines what a SQLAlchemy Note object looks like in terms of Marshmallow. Notice that it has a person attribute. This attribute comes from the SQLAlchemy db.relationship(...) definition parameter backref='person'. The person Marshmallow definition is nested, but because it doesn’t have the many=True parameter, there is only a single person connected.

PersonNoteSchema类定义当棉花糖序列化notes列表时， Note对象的外观。 NoteSchema根据NoteSchema定义SQLAlchemy Note对象的外观。 请注意，它具有一个person属性。 此属性来自SQLAlchemy db.relationship(...)定义参数backref='person' 。 person棉花糖定义是嵌套的，但由于它没有many=True参数，因此仅连接了一个person 。

The NotePersonSchema class defines what is nested in the NoteSchema.person attribute.

NotePersonSchema类定义在NoteSchema.person属性中嵌套的NoteSchema.person 。

Note:

注意：

You might be wondering why the PersonSchema class has its own unique PersonNoteSchema class to define the notes collection attribute. By the same token, the NoteSchema class has its own unique NotePersonSchema class to define the person attribute. You may be wondering whether the PersonSchema class could be defined this way:

您可能想知道为什么PersonSchema类具有自己的唯一PersonNoteSchema类来定义notes集合属性。 同样， NoteSchema类具有其自己的唯一NotePersonSchema类来定义person属性。 您可能想知道是否可以通过这种方式定义PersonSchema类：

 class class PersonSchemaPersonSchema (( mama .. ModelSchemaModelSchema ):
    ):
    class class MetaMeta :
        :
        model model = = Person
        Person
        sqla_session sqla_session = = dbdb .. session
    session
    notes notes = = fieldsfields .. NestedNested (( 'NoteSchema''NoteSchema' , , defaultdefault == [], [], manymany == TrueTrue )
)

Additionally, couldn’t the NoteSchema class be defined using the PersonSchema to define the person attribute? A class definition like this would each refer to the other, and this causes a recursion error in Marshmallow as it will cycle from PersonSchema to NoteSchema until it runs out of stack space. Using the unique schema references breaks the recursion and allows this kind of nesting to work.

另外，不能使用PersonSchema定义person属性来定义NoteSchema类吗？ 这样的类定义将互相引用，并且在棉花糖中引起递归错误，因为它将从PersonSchema循环到NoteSchema直到耗尽堆栈空间为止。 使用唯一的架构引用会破坏递归，并允许这种嵌套工作。

人 (People)

Now that you’ve got the schemas in place to work with the one-to-many relationship between Person and Note, you need to update the person.py and create the note.py modules in order to implement a working REST API.

现在，您已经有了可以使用Person和Note之间的一对多关系的架构，您需要更新person.py并创建note.py模块，以实现有效的REST API。

The people.py module needs two changes. The first is to import the Note class, along with the Person class at the top of the module. Then only read_one(person_id) needs to change in order to handle the relationship. That function will look like this:

people.py模块需要进行两项更改。 第一种是导入Note类以及模块顶部的Person类。 然后只需read_one(person_id)即可处理该关系。 该函数将如下所示：

The only difference is line 12: .outerjoin(Note). An outer join (left outer join in SQL terms) is necessary for the case where a user of the application has created a new person object, which has no notes related to it. The outer join ensures that the SQL query will return a person object, even if there are no note rows to join with.

唯一的区别是第12行： .outerjoin(Note) 。 对于应用程序的用户创建没有相关notes的新person对象的情况， 外部联接（用SQL术语表示为左外部联接）是必需的。 外部联接确保即使没有note行要联接，SQL查询也将返回person对象。

At the start of this article, you saw how person and note data could be represented in a single, flat table, and all of the disadvantages of that approach. You also saw the advantages of breaking that data up into two tables, person and note, with a relationship between them.

在本文开头，您了解了如何在单个平面表中表示人和笔记数据，以及该方法的所有缺点。 您还看到了将数据分成两个表（ person和note ）以及它们之间的关系的优点。

Until now, we’ve been working with the data as two distinct, but related, items in the database. But now that you’re actually going to use the data, what we essentially want is for the data to be joined back together. This is what a database join does. It combines data from two tables together using the primary key to foreign key relationship.

到目前为止，我们一直将数据作为数据库中两个独立但相关的项目来使用。 但是，既然您实际上将要使用数据，我们本质上想要的是将数据重新结合在一起。 这就是数据库联接的作用。 它使用主键到外键的关系将两个表中的数据组合在一起。

A join is kind of a boolean and operation because it only returns data if there is data in both tables to combine. If, for example, a person row exists but has no related note row, then there is nothing to join, so nothing is returned. This isn’t what you want for read_one(person_id).

联接是布尔and运算的一种，因为它仅在两个表中都存在要合并的数据时才返回数据。 例如，如果存在一个person行，但没有相关的note行，则没有任何要加入的内容，因此不会返回任何内容。 这不是您想要的read_one(person_id) 。

This is where the outer join comes in handy. It’s a kind of boolean or operation. It returns person data even if there is no associated note data to combine with. This is the behavior you want for read_one(person_id) to handle the case of a newly created Person object that has no notes yet.

这是外部联接派上用场的地方。 这是一种布尔or运算。 即使没有关联的note数据可以合并，它也会返回person数据。 这是您希望read_one(person_id)处理没有注释的新创建的Person对象的情况。

You can see the complete people.py in the article repository.

您可以在文章存储库中看到完整的people.py 。

笔记 (Notes)

You’ll create a notes.py module to implement all the Python code associated with the new note related REST API definitions. In many ways, it works like the people.py module, except it must handle both a person_id and a note_id as defined in the swagger.yml configuration file. As an example, here is read_one(person_id, note_id):

您将创建一个notes.py模块，以实现与与新便笺相关的REST API定义关联的所有Python代码。 在许多方面，它都必须像people.py模块那样工作，不同之处people.py ，它必须同时处理swagger.yml配置文件中定义的person_id和note_id 。 例如，这是read_one(person_id, note_id) ：

 def def read_oneread_one (( person_idperson_id , , note_idnote_id ):
    ):
    """
"""
    This function responds to a request for
    This function responds to a request for
    /api/people/{person_id}/notes/{note_id}
    /api/people/{person_id}/notes/{note_id}
    with one matching note for the associated person

    with one matching note for the associated person

    :param person_id:       Id of person the note is related to
    :param person_id:       Id of person the note is related to
    :param note_id:         Id of the note
    :param note_id:         Id of the note
    :return:                json string of note contents
    :return:                json string of note contents
    """
        """
    # Query the database for the note
# Query the database for the note
note note = = (
(
NoteNote .. queryquery .. joinjoin (( PersonPerson , , PersonPerson .. person_id person_id == == NoteNote .. person_idperson_id )
)
.. filterfilter (( PersonPerson .. person_id person_id == == person_idperson_id )
)
.. filterfilter (( NoteNote .. note_id note_id == == note_idnote_id )
)
.. one_or_noneone_or_none ()
()
)
)
# Was a note found?
    # Was a note found?
    if if note note is is not not NoneNone :
        :
        note_schema note_schema = = NoteSchemaNoteSchema ()
        ()
        data data = = note_schemanote_schema .. dumpdump (( notenote )) .. data
        data
        return return data

    data

    # Otherwise, nope, didn't find that note
    # Otherwise, nope, didn't find that note
    elseelse :
        :
        abortabort (( 404404 , , ff "Note not found for Id: "Note not found for Id:  {note_id}{note_id} "" )
)

The interesting parts of the above code are lines 12 to 17:

上面代码中有趣的部分是第12至17行：

• Line 13 begins a query against the Note SQLAlchemy objects and joins to the related Person SQLAlchemy object comparing person_id from both Person and Note.
• Line 14 filters the result down to the Note objects that has a Person.person_id equal to the passed in person_id parameter.
• Line 15 filters the result further to the Note object that has a Note.note_id equal to the passed in note_id parameter.
• Line 16 returns the Note object if found, or None if nothing matching the parameters is found.

• 第13行开始对Note SQLAlchemy对象的查询，并加入相关的Person SQLAlchemy对象，比较Person和Note person_id 。
• 第14 Person.person_id结果过滤到具有与传入的person_id参数相等的Person.person_id的Note对象。
• 第15 Note.note_id结果进一步过滤到Note.note_id等于传入的note_id参数的Note对象。
• 如果找到第16行，则返回Note对象；如果未找到与参数匹配的行，则返回None 。

You can check out the complete notes.py.

您可以检出完整的notes.py 。

更新了Swagger UI (Updated Swagger UI)

The Swagger UI has been updated by the action of updating the swagger.yml file and creating the URL endpoint implementations. Below is a screenshot of the updated UI showing the Notes section with the GET /api/people/{person_id}/notes/{note_id} expanded:

Swagger UI已通过更新swagger.yml文件并创建URL端点实现的操作进行了更新。 以下是更新后的用户界面的屏幕截图，其中显示了注释部分，其中GET /api/people/{person_id}/notes/{note_id}展开了：

迷你博客Web应用程序 (Mini-Blogging Web Application)

The web application has been substantially changed to show its new purpose as a mini-blogging application. It has three pages:

Web应用程序已进行了重大更改，以显示其作为微型博客应用程序的新目的。 它分为三页：

1. The home page (localhost:5000/), which shows all of the blog messages (notes) sorted from newest to oldest

2. The people page (localhost:5000/people), which shows all the people in the system, sorted by last name, and also allows the user to create a new person and update or delete an existing one

3. The notes page (localhost:5000/people/{person_id}/notes), which shows all the notes associated with a person, sorted from newest to oldest, and also allows the user to create a new note and update or delete an existing one

1. 主页（ localhost:5000/ ） ，显示从最新到最旧排序的所有博客消息（注释）

2. 人员页面（ localhost:5000/people ） ，该页面显示系统中的所有人员，按姓氏排序，还允许用户创建新人员并更新或删除现有人员

3. 笔记页面（ localhost:5000/people/{person_id}/notes ） ，其中显示了与一个人相关的所有笔记（从最新到最旧排序），还允许用户创建新笔记并更新或删除现有笔记

导航 (Navigation)

There are two buttons on every page of the application:

在应用程序的每个页面上都有两个按钮：

1. The Home button will navigate to the home screen.
2. The People button navigates to the /people screen, showing all people in the database.

1. 主页按钮将导航到主屏幕。
2. 人员按钮导航到/people屏幕，显示数据库中的所有人员。

These two buttons are present on every screen in the application as a way to get back to a starting point.

这两个按钮出现在应用程序的每个屏幕上，以此作为回到起点的一种方式。

主页 (Home Page)

Below is a screenshot of the home page showing the initialized database contents:

下面是主页的屏幕截图，显示了初始化的数据库内容：

The functionality of this page works like this:

该页面的功能如下：

• Double-clicking on a person’s name will take the user to the /people/{person_id} page, with the editor section filled in with the person’s first and last names and the update and reset buttons enabled.

• Double-clicking on a person’s note will take the user to the /people/{person_id}/notes/{note_id} page, with the editor section filled in with the note’s contents and the Update and Reset buttons enabled.

• 双击一个人的名字将把用户带到/people/{person_id}页面，其中的编辑器部分填写了该人的名字和姓氏，并且启用了更新和重置按钮。

• 双击某人的便笺将使用户进入/people/{person_id}/notes/{note_id}页面，其中编辑器部分填充了便笺的内容，并且启用了“更新”和“重置”按钮。

人物页面 (People Page)

Below is a screenshot of the people page showing the people in the initialized database:

以下是人员页面的屏幕快照，显示了初始化数据库中的人员：

The functionality of this page works like this:

该页面的功能如下：

• Single-clicking on a person’s name will populate the editor section of the page with the person’s first and last name, disabling the Create button, and enabling the Update and Delete buttons.

• Double clicking on a person’s name will navigate to the notes pages for that person.

• 单击某人的名字将使用该人的名字和姓氏填充页面的编辑器部分，并禁用“创建”按钮，并启用“更新”和“删除”按钮。

• 双击某人的姓名将导航至该人的注释页面。

The functionality of the editor works like this:

编辑器的功能如下：

• If the first and last name fields are empty, the Create and Reset buttons are enabled. Entering a new name in the fields and clicking Create will create a new person and update the database and re-render the table below the editor. Clicking Reset will clear the editor fields.

• If the first and last name fields have data, the user navigated here by double-clicking the person’s name from the home screen. In this case, the Update, Delete, and Reset buttons are enabled. Changing the first or last name and clicking Update will update the database and re-render the table below the editor. Clicking Delete will remove the person from the database and re-render the table.

• 如果名字和姓氏字段为空，则启用创建和重置按钮。 在字段中输入新名称并单击“创建”将创建一个新人员并更新数据库并重新呈现编辑器下方的表。 单击重置将清除编辑器字段。

• 如果名字和姓氏字段包含数据，则用户可以通过在主屏幕上双击此人的姓名来浏览此处。 在这种情况下，将启用“更新”，“删除”和“重置”按钮。 更改名字或姓氏并单击“更新”将更新数据库并重新呈现编辑器下方的表。 单击删除将从数据库中删除该人，然后重新呈现该表。

笔记页面 (Notes Page)

Below is a screenshot of the notes page showing the notes for a person in the initialized database:

以下是便笺页面的屏幕截图，显示了初始化数据库中某人的便笺：

The functionality of this page works like this:

该页面的功能如下：

• Single-clicking on a note will populate the editor section of the page with the notes content, disabling the Create button, and enabling the Update and Delete buttons.

• All other functionality of this page is in the editor section.

• 单击注释将用注释内容填充页面的编辑器部分，禁用“创建”按钮，并启用“更新”和“删除”按钮。

• 此页面的所有其他功能在编辑器部分中。

The functionality of the editor works like this:

编辑器的功能如下：

• If the note content field is empty, then the Create and Reset buttons are enabled. Entering a new note in the field and clicking Create will create a new note and update the database and re-render the table below the editor. Clicking Reset will clear the editor fields.

• If the note field has data, the user navigated here by double-clicking the person’s note from the home screen. In this case, the Update, Delete, and Reset buttons are enabled. Changing the note and clicking Update will update the database and re-render the table below the editor. Clicking Delete will remove the note from the database and re-render the table.

• 如果注释内容字段为空，则启用“创建”和“重置”按钮。 在该字段中输入新注释，然后单击“创建”将创建一个新注释并更新数据库并重新呈现编辑器下方的表。 单击重置将清除编辑器字段。

• 如果注释字段中包含数据，则用户可以通过从主屏幕双击该人的注释来导航到此处。 在这种情况下，将启用“更新”，“删除”和“重置”按钮。 更改注释并单击“更新”将更新数据库并重新呈现编辑器下方的表。 单击删除将从数据库中删除注释，然后重新呈现表。

Web应用程序 (Web Application)

This article is primarily focused on how to use SQLAlchemy to create relationships in the database, and how to extend the REST API to take advantage of those relationships. As such, the code for the web application didn’t get much attention. When you look at the web application code, keep an eye out for the following features:

本文主要关注如何使用SQLAlchemy在数据库中创建关系，以及如何扩展REST API以利用这些关系。 这样，Web应用程序的代码并没有引起太多关注。 在查看Web应用程序代码时，请注意以下功能：

• Each page of the application is a fully formed single page web application.

• Each page of the application is driven by JavaScript following an MVC (Model/View/Controller) style of responsibility delegation.

• The HTML that creates the pages takes advantage of the Jinja2 inheritance functionality.

• The hardcoded JavaScript table creation has been replaced by using the Handlebars.js templating engine.

• The timestamp formating in all of the tables is provided by Moment.js.

• 该应用程序的每个页面都是一个完整的单页Web应用程序 。

• 应用程序的每个页面均由JavaScript驱动，并遵循MVC （模型/视图/控制器）责任委托样式。

• 创建页面HTML利用Jinja2继承功能 。

• 使用Handlebars.js模板引擎已替换了硬编码JavaScript表创建。

• 所有表中的时间戳格式由Moment.js提供。

You can find the following code in the repository for this article:

您可以在本文的资源库中找到以下代码：

• The HTML for the web application
• The CSS for the web application
• The JavaScript for the web application

• Web应用程序的HTML
• Web应用程序的CSS
• Web应用程序的JavaScript

All of the example code for this article is available in the GitHub repository for this article. This contains all of the code related to this article, including all of the web application code.

本文的所有示例代码均可在本文的GitHub存储库中找到 。 它包含与本文相关的所有代码，包括所有Web应用程序代码。

结论 (Conclusion)

Congratulations are in order for what you’ve learned in this article! Knowing how to build and use database relationships gives you a powerful tool to solve many difficult problems. There are other relationship besides the one-to-many example from this article. Other common ones are one-to-one, many-to-many, and many-to-one. All of them have a place in your toolbelt, and SQLAlchemy can help you tackle them all!

恭喜您从本文中学到了什么！ 了解如何建立和使用数据库关系为您提供了解决许多难题的强大工具。 除了本文的一对多示例外，还有其他关系。 其他常见的是一对一 ， 多对多和多对一 。 它们全都在您的工具带中占有一席之地，SQLAlchemy可以帮助您解决所有问题！

For more information about databases, you can check out these tutorials. You can also set up Flask to use SQLAlchemy. You can check out Model-View-Controller (MVC) more information about the pattern used in the web application JavaScript code.

有关数据库的更多信息，您可以查看这些教程 。 您也可以将Flask设置为使用SQLAlchemy。 您可以签出Model-View-Controller（MVC）有关在Web应用程序JavaScript代码中使用的模式的更多信息。

In Part 4 of this series, you’ll focus on the HTML, CSS, and JavaScript files used to create the web application.

在本系列的第4部分中，您将重点介绍用于创建Web应用程序HTML，CSS和JavaScript文件。

翻译自: https://www.pybloggers.com/2019/04/python-rest-apis-with-flask-connexion-and-sqlalchemy-part-3/