如何在Flask和SQLite中使用一对多的数据库关系

作者选择了 COVID-19 Relief Fund 作为 Write for DOnations 计划的一部分来接受捐赠。

介绍

Flask 是一个使用 Python 语言构建 Web 应用程序的框架，而 SQLite 是一个可以与 Python 一起使用来存储应用程序数据的数据库引擎。在本教程中，您将使用 Flask 和 SQLite 创建一个待办事项应用程序，用户可以在其中创建待办事项列表。您将学习如何将 SQLite 与 Flask 一起使用，以及一对多数据库关系如何工作。

一对多数据库关系 是两个数据库表之间的关系，其中一个表中的一条记录可以引用另一个表中的多条记录。例如，在博客应用程序中，用于存储帖子的表可以与用于存储评论的表具有一对多的关系。每个帖子可以引用多条评论，每条评论引用一个帖子；因此，one 帖子与 many 评论有关系。 post表是父表，comments表是子表——父表中的一条记录可以引用子表中的多条记录。这对于能够访问每个表中的相关数据非常重要。

我们将使用 SQLite，因为它是可移植的，并且不需要任何额外的设置来使用 Python。在迁移到更大的数据库（如 MySQL 或 Postgres）之前，它也非常适合对应用程序进行原型设计。有关如何选择正确的数据库系统的更多信息，请阅读我们的 SQLite vs MySQL vs PostgreSQL：关系数据库管理系统的比较文章。

先决条件

在开始遵循本指南之前，您需要：

本地 Python 3 编程环境，请按照如何为本地机器安装和设置 Python 3 系列的本地编程环境中的分发教程进行操作。在本教程中，我们将调用我们的项目目录 flask_todo。
了解基本的 Flask 概念，例如创建路由、呈现 HTML 模板和连接到 SQLite 数据库。如果您不熟悉这些概念，您可以关注 How To Make a Web Application Using Flask in Python 3，但不是必须的。

第 1 步 — 创建数据库

在这一步中，您将激活您的编程环境、安装 Flask、创建 SQLite 数据库并使用示例数据填充它。您将学习如何使用外键在列表和项目之间创建一对多的关系。外键是用于将数据库表与另一个表关联的键，它是子表与其父表之间的链接。

如果您还没有激活您的编程环境，请确保您位于项目目录 (flask_todo) 中并使用以下命令来激活它：

source env/bin/activate

激活编程环境后，使用以下命令安装 Flask：

pip install flask

安装完成后，您现在可以创建包含 SQL 命令的数据库模式文件，以创建存储待办事项数据所需的表。您将需要两个表：一个名为 lists 的表来存储待办事项列表，一个 items 表来存储每个列表的项目。

在 flask_todo 目录中打开一个名为 schema.sql 的文件：

nano schema.sql

在此文件中键入以下 SQL 命令：

flask_todo/schema.sql

DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;

CREATE TABLE lists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL
);

CREATE TABLE items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    list_id INTEGER NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    content TEXT NOT NULL,
    FOREIGN KEY (list_id) REFERENCES lists (id)
);

保存并关闭文件。

前两个 SQL 命令是 DROP TABLE IF EXISTS lists; 和 DROP TABLE IF EXISTS items;，它们会删除任何已经存在的名为 lists 和 items 的表，因此您不会看到令人困惑的行为。请注意，无论何时使用这些 SQL 命令，这都会删除数据库中的所有内容，因此请确保在完成本教程并试验最终结果之前不要在 Web 应用程序中编写任何重要内容。

接下来，您使用 CREATE TABLE lists 创建 lists 表，该表将存储具有以下列的待办事项列表（例如学习列表、工作列表、家庭列表等）：

id：表示主键的整数，数据库将为每个条目分配一个唯一值（即待办事项清单）。
created：待办事项列表的创建时间。 NOT NULL 表示此列不应该为空，DEFAULT 的值是 CURRENT_TIMESTAMP 的值，也就是将列表添加到数据库的时间。就像 id 一样，您不需要为此列指定值，因为它会自动填充。
title：列表标题。

然后，您创建一个名为 items 的表来存储待办事项。此表有一个 ID、一个 list_id 整数列，用于标识项目所属的列表、创建日期和项目的内容。要将项目链接到数据库中的列表，请使用 外键约束 和 FOREIGN KEY (list_id) REFERENCES lists (id) 行。这里的lists表是一个父表，也就是被外键约束引用的表，表示一个列表可以有多个项目。 items 表是子表，即约束适用的表。这意味着项目属于单个列表。 list_id 列引用 lists 父表的 id 列。

由于一个列表可以有许多个项目，而一个项目只属于一个列表，lists和items表之间的关系是[ X155X]一对多关系。

接下来，您将使用 schema.sql 文件来创建数据库。在 flask_todo 目录中打开一个名为 init_db.py 的文件：

nano init_db.py

然后添加以下代码：

flask_todo/init_db.py

import sqlite3

connection = sqlite3.connect('database.db')


with open('schema.sql') as f:
    connection.executescript(f.read())

cur = connection.cursor()

cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))

cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
            (1, 'Morning meeting')
            )

cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
            (2, 'Buy fruit')
            )

cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
            (2, 'Cook dinner')
            )

cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
            (3, 'Learn Flask')
            )

cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
            (3, 'Learn SQLite')
            )

connection.commit()
connection.close()

保存并关闭文件。

在这里，您连接到一个名为 database.db 的文件，该文件将在您执行此程序后创建。然后，您打开 schema.sql 文件并使用同时执行多个 SQL 语句的 executescript() 方法运行它。

运行 schema.sql 将创建 lists 和 items 表。接下来，使用 Cursor 对象，执行几个 INSERT SQL 语句来创建三个列表和五个待办事项。

您可以使用 list_id 列通过列表的 id 值将每个项目链接到列表。例如，Work 列表是第一个插入数据库的列表，因此它的 ID 为 1。这就是您可以将 Morning meeting 待办事项链接到 Work 的方式——同样的规则适用于其他列表和项目。

最后，您提交更改并关闭连接。

运行程序：

python init_db.py

执行后，一个名为 database.db 的新文件将出现在您的 flask_todo 目录中。

您已经激活了环境、安装了 Flask 并创建了 SQLite 数据库。接下来，您将从数据库中检索列表和项目并将它们显示在应用程序的主页中。

第 2 步 - 显示待办事项

在这一步中，您将在上一步中创建的数据库连接到一个 Flask 应用程序，该应用程序显示待办事项列表和每个列表的项目。您将学习如何使用 SQLite 联接从两个表中查询数据，以及如何按列表对待办事项进行分组。

首先，您将创建应用程序文件。在 flask_todo 目录中打开一个名为 app.py 的文件：

nano app.py

然后将以下代码添加到文件中：

烧瓶待办事项/app.py

from itertools import groupby
import sqlite3
from flask import Flask, render_template, request, flash, redirect, url_for


def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn


app = Flask(__name__)
app.config['SECRET_KEY'] = 'this should be a secret random string'


@app.route('/')
def index():
    conn = get_db_connection()
    todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \
                          ON i.list_id = l.id ORDER BY l.title;').fetchall()

    lists = {}

    for k, g in groupby(todos, key=lambda t: t['title']):
        lists[k] = list(g)

    conn.close()
    return render_template('index.html', lists=lists)

保存并关闭文件。

get_db_connection() 函数打开到 database.db 数据库文件的连接，然后将 row_factory 属性设置为 sqlite3.Row。通过这种方式，您可以对列进行基于名称的访问；这意味着数据库连接将返回行为类似于常规 Python 字典的行。最后，该函数返回您将用于访问数据库的 conn 连接对象。

在 index() 视图函数中，您打开一个数据库连接并执行以下 SQL 查询：

SELECT i.content, l.title FROM items i JOIN lists l ON i.list_id = l.id ORDER BY l.title;

然后，您可以使用 fetchall() 方法检索其结果，并将数据保存在名为 todos 的变量中。

在此查询中，您使用 SELECT 通过连接 items 和 lists 表（与表items 的别名 i 和 lists 的别名 l）。在 ON 关键字之后使用连接条件 i.list_id = l.id，您将从 items 表中获取每一行，其中 lists 表中的每一行 [X162X items 表的 ] 列与 lists 表的 id 匹配。然后使用 ORDER BY 按列表标题对结果进行排序。

为了更好地理解这个查询，在你的 flask_todo 目录中打开 Python REPL：

python

要了解 SQL 查询，请通过运行以下小程序检查 todos 变量的内容：

from app import get_db_connection
conn = get_db_connection()
todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
for todo in todos:
    print(todo['title'], ':', todo['content'])

您首先从 app.py 文件中导入 get_db_connection，然后打开一个连接并执行查询（请注意，这与您在 app.py 文件中的 SQL 查询相同）。在 for 循环中，您打印列表的标题和每个待办事项的内容。

输出如下：

OutputHome : Buy fruit
Home : Cook dinner
Study : Learn Flask
Study : Learn SQLite
Work : Morning meeting

使用 CTRL + D 关闭 REPL。

现在您已经了解了 SQL 连接的工作原理以及查询实现了什么，让我们回到 app.py 文件中的 index() 视图函数。声明 todos 变量后，使用以下代码对结果进行分组：

lists = {}

for k, g in groupby(todos, key=lambda t: t['title']):
    lists[k] = list(g)

您首先声明一个名为 lists 的空字典，然后使用 for 循环通过列表标题对 todos 变量中的结果进行分组。您使用从 itertools 标准库导入的 groupby() 函数。此函数将遍历 todos 变量中的每一项，并为 for 循环中的每个键生成一组结果。

k 表示列表标题（即 Home、Study、Work），它们是使用您传递给 [X136X 的函数提取的] groupby() 函数的参数。在这种情况下，函数是 lambda t: t['title'] ，它接受一个待办事项并返回列表的标题（就像您在前面的 for 循环中使用 todo['title'] 所做的那样）。 g 表示包含每个列表标题的待办事项的组。例如，在第一次迭代中，k 将是 'Home'，而 g 是一个 iterable 将包含项目 'Buy fruit'和 'Cook dinner'。

这为我们提供了列表和项目之间的一对多关系的表示，其中每个列表标题都有几个待办事项。

运行app.py文件时，for循环执行完毕后，lists如下：

Output{'Home': [<sqlite3.Row object at 0x7f9f58460950>,
          <sqlite3.Row object at 0x7f9f58460c30>],
 'Study': [<sqlite3.Row object at 0x7f9f58460b70>,
           <sqlite3.Row object at 0x7f9f58460b50>],
 'Work': [<sqlite3.Row object at 0x7f9f58460890>]}

每个 sqlite3.Row 对象将包含您使用 index() 函数中的 SQL 查询从 items 表中检索到的数据。为了更好地表示这些数据，让我们编写一个程序来遍历 lists 字典并显示每个列表及其项目。

在 flask_todo 目录中打开一个名为 list_example.py 的文件：

nano list_example.py

然后添加以下代码：

flask_todo/list_example.py

from itertools import groupby
from app import get_db_connection

conn = get_db_connection()
todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \
                        ON i.list_id = l.id ORDER BY l.title;').fetchall()

lists = {}

for k, g in groupby(todos, key=lambda t: t['title']):
    lists[k] = list(g)

for list_, items in lists.items():
    print(list_)
    for item in items:
        print('    ', item['content'])

保存并关闭文件。

这与您的 index() 视图函数中的内容非常相似。此处的最后一个 for 循环说明了 lists 字典的结构。您首先浏览字典的项目，打印列表标题（在 list_ 变量中），然后浏览属于列表的每组待办事项并打印项目的内容值。

运行list_example.py程序：

python list_example.py

这是 list_example.py 的输出：

OutputHome
     Buy fruit
     Cook dinner
Study
     Learn Flask
     Learn SQLite
Work
     Morning meeting

现在您了解了 index() 函数的每个部分，让我们创建一个基本模板并使用 return render_template('index.html', lists=lists) 行创建您渲染的 index.html 文件。

在您的 flask_todo 目录中，创建一个 templates 目录并在其中打开一个名为 base.html 的文件：

mkdir templates
nano templates/base.html

在 base.html 中添加以下代码，注意这里使用的是 Bootstrap。如果您不熟悉 Flask 中的 HTML 模板，请参阅如何在 Python 3 中使用 Flask 制作 Web 应用程序的第 3 步：

flask_todo/templates/base.html

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <title>{% block title %} {% endblock %}</title>
  </head>
  <body>
    <nav class="navbar navbar-expand-md navbar-light bg-light">
        <a class="navbar-brand" href="{{ url_for('index')}}">FlaskTodo</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
            <ul class="navbar-nav">
            <li class="nav-item active">
                <a class="nav-link" href="#">About</a>
            </li>
            </ul>
        </div>
    </nav>
    <div class="container">
        {% block content %} {% endblock %}
    </div>

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>

保存并关闭文件。

前面代码块中的大部分代码是标准 HTML 和 Bootstrap 所需的代码。 <meta> 标签为 Web 浏览器提供信息，<link> 标签链接 Bootstrap CSS 文件，而 <script> 标签是 JavaScript 代码的链接，允许一些额外的 Bootstrap 功能。查看 Bootstrap 文档了解更多信息。

接下来，创建将扩展此 base.html 文件的 index.html 文件：

nano templates/index.html

将以下代码添加到 index.html：

flask_todo/templates/index.html

{% extends 'base.html' %}

{% block content %}
    <h1>{% block title %} Welcome to FlaskTodo {% endblock %}</h1>
    {% for list, items in lists.items() %}
        <div class="card" style="width: 18rem; margin-bottom: 50px;">
            <div class="card-header">
                <h3>{{ list }}</h3>
            </div>
            <ul class="list-group list-group-flush">
                {% for item in items %}
                    <li class="list-group-item">{{ item['content'] }}</li>
                {% endfor %}
            </ul>
        </div>
    {% endfor %}
{% endblock %}

在这里，您使用 for 循环遍历 lists 字典的每个项目，将列表标题显示为 <h3> 标记内的卡片标题，然后使用列表组以在 <li> 标记中显示属于列表的每个待办事项。这遵循 list_example.py 程序中解释的相同规则。

您现在将设置 Flask 需要的环境变量并使用以下命令运行应用程序：

export FLASK_APP=app
export FLASK_ENV=development
flask run

开发服务器运行后，您可以在浏览器中访问 URL http://127.0.0.1:5000/。您将看到一个带有“欢迎使用 FlaskTodo”和您的列表项的网页。

您现在可以键入 CTRL + C 来停止您的开发服务器。

您已经创建了一个 Flask 应用程序，它显示待办事项列表和每个列表的项目。在下一步中，您将添加一个新页面来创建新的待办事项。

第 3 步 — 添加新的待办事项

在这一步中，您将为创建待办事项创建一条新路径，您将向数据库表中插入数据，并将项目与它们所属的列表相关联。

首先，打开app.py文件：

nano app.py

然后，在文件末尾添加一个带有名为 create() 的视图函数的新 /create 路由：

烧瓶待办事项/app.py

...
@app.route('/create/', methods=('GET', 'POST'))
def create():
    conn = get_db_connection()
    lists = conn.execute('SELECT title FROM lists;').fetchall()

    conn.close()
    return render_template('create.html', lists=lists)

保存并关闭文件。

因为您将使用此路由通过 Web 表单向数据库插入新数据，所以您在 app.route() 装饰器中允许使用 methods=('GET', 'POST') 的 GET 和 POST 请求。在 create() 视图函数中，您打开一个数据库连接，然后获取数据库中所有可用的列表标题，关闭连接，然后渲染一个 create.html 模板，将列表标题传递给它。

接下来，打开一个名为 create.html 的新模板文件：

nano templates/create.html

将以下 HTML 代码添加到 create.html：

flask_todo/templates/create.html

{% extends 'base.html' %}

{% block content %}
<h1>{% block title %} Create a New Item {% endblock %}</h1>

<form method="post">
    <div class="form-group">
        <label for="content">Content</label>
        <input type="text" name="content"
               placeholder="Todo content" class="form-control"
               value="{{ request.form['content'] }}"></input>
    </div>

    <div class="form-group">
        <label for="list">List</label>
        <select class="form-control" name="list">
            {% for list in lists %}
                {% if list['title'] == request.form['list'] %}
                    <option value="{{ request.form['list'] }}" selected>
                        {{ request.form['list'] }}
                    </option>
                {% else %}
                    <option value="{{ list['title'] }}">
                        {{ list['title'] }}
                    </option>
                {% endif %}
            {% endfor %}
        </select>
    </div>
    <div class="form-group">
        <button type="submit" class="btn btn-primary">Submit</button>
    </div>
</form>
{% endblock %}

保存并关闭文件。

您可以使用 request.form 访问存储的表单数据，以防您的表单提交出现问题（例如，如果未提供待办事项内容）。在 <select> 元素中，循环通过 create() 函数中从数据库中检索到的列表。如果列表标题等于存储在 request.form 中的内容，则选择的选项就是该列表标题，否则，您将在正常未选择的 <option> 标记中显示列表标题。

现在，在终端中，运行您的 Flask 应用程序：

flask run

然后在浏览器中访问 http://127.0.0.1:5000/create，您将看到一个用于创建新待办事项的表单，请注意该表单尚未工作，因为您没有代码来处理浏览器发送的 POST 请求提交表格时。

键入 CTRL + C 以停止您的开发服务器。

接下来，让我们在create()函数中添加处理POST请求的代码，并使表单功能正常，打开app.py：

nano app.py

然后编辑 create() 函数，如下所示：

烧瓶待办事项/app.py

...
@app.route('/create/', methods=('GET', 'POST'))
def create():
    conn = get_db_connection()

    if request.method == 'POST':
        content = request.form['content']
        list_title = request.form['list']

        if not content:
            flash('Content is required!')
            return redirect(url_for('index'))

        list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
                                 (list_title,)).fetchone()['id']
        conn.execute('INSERT INTO items (content, list_id) VALUES (?, ?)',
                     (content, list_id))
        conn.commit()
        conn.close()
        return redirect(url_for('index'))

    lists = conn.execute('SELECT title FROM lists;').fetchall()

    conn.close()
    return render_template('create.html', lists=lists)

保存并关闭文件。

在 request.method == 'POST' 条件中，您可以从表单数据中获取待办事项的内容和列表的标题。如果没有提交内容，则使用 flash() 函数向用户发送消息并重定向到索引页面。如果未触发此条件，则执行 SELECT 语句以从提供的列表标题中获取列表 ID，并将其保存在名为 list_id 的变量中。然后执行 INSERT INTO 语句将新的待办事项插入到 items 表中。您使用 list_id 变量将项目链接到它所属的列表。最后，您提交事务、关闭连接并重定向到索引页面。

作为最后一步，您将在导航栏中添加指向 /create 的链接并在其下方显示闪烁的消息，为此，请打开 base.html：

nano templates/base.html

通过添加链接到 create() 视图功能的新 <li> 导航项来编辑文件。然后使用 content 块上方的 for 循环显示闪烁的消息。这些在 get_flashed_messages() Flask 函数中可用：

flask_todo/templates/base.html

<nav class="navbar navbar-expand-md navbar-light bg-light">
    <a class="navbar-brand" href="{{ url_for('index')}}">FlaskTodo</a>
    <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse" id="navbarNav">
        <ul class="navbar-nav">
        <li class="nav-item active">
            <a class="nav-link" href="{{ url_for('create') }}">New</a>
        </li>

        <li class="nav-item active">
            <a class="nav-link" href="#">About</a>
        </li>
        </ul>
    </div>
</nav>
<div class="container">
    {% for message in get_flashed_messages() %}
        <div class="alert alert-danger">{{ message }}</div>
    {% endfor %}
    {% block content %} {% endblock %}
</div>

保存并关闭文件。

现在，在终端中，运行您的 Flask 应用程序：

flask run

导航栏中将出现一个指向 /create 的新链接。如果您导航到此页面并尝试添加没有内容的新待办事项，您将收到一条闪现的消息，指出 需要内容！。如果填写内容表单，索引页面上会出现一个新的待办事项。

在此步骤中，您添加了创建新待办事项并将其保存到数据库的功能。

你可以在这个仓库找到这个项目的源代码。

结论

您现在有一个应用程序来管理待办事项列表和项目。每个列表都有几个待办事项，每个待办事项属于一对多关系中的单个列表。您学习了如何使用 Flask 和 SQLite 管理多个相关的数据库表，如何使用外键以及如何使用 SQLite 连接从 Web 应用程序中的两个表中检索和显示相关数据。

此外，您使用 groupby() 函数对结果进行分组，将新数据插入数据库，并将数据库表行与它们相关的表关联起来。您可以从 SQLite 文档中了解有关外键和数据库关系的更多信息。

您还可以阅读我们的更多 Python 框架内容。如果您想查看 sqlite3 Python 模块，请阅读我们关于如何在 Python 3 中使用 sqlite3 模块的教程。