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

笔记首页 >> 教程系列 >> 如何在 Flask 和 SQLite 中使用一对多的数据库关系

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

作者选择了COVID-19 救济基金来接受捐赠，作为Write for DOnations计划的一部分。

介绍

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

一个一个一对多数据库关系是两个数据库表，其中一个表中的记录可以在另一个表中引用多个记录之间的关系。例如，在博客应用程序中，用于存储帖子的表可以与用于存储评论的表具有一对多关系。每个帖子可以引用多个评论，每个评论引用一个帖子；因此，一个帖子与许多评论有关系。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表，以及一个用于存储每个列表项目的表。

打开一个schema.sql在你的flask_todo目录中调用的文件：

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;and 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列引用父表的id列lists。

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

接下来，您将使用该schema.sql文件创建数据库。打开目录init_db.py内命名的flask_todo文件：

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文件并使用executescript()一次执行多个 SQL 语句的方法运行它。

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

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

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

运行程序：

python init_db.py

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

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

步骤 2 — 显示待办事项

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

首先，您将创建应用程序文件。打开目录app.py内命名的flask_todo文件：

nano app.py

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

flask_todo/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表（使用表别名iforitems和lfor lists）来获取项目的内容及其所属列表的标题。随着连接条件i.list_id = l.id后ON的关键字，你会得到从每一行items表从每一行lists所在的表list_id中的列items表匹配id的的lists表。然后使用ORDER BY按列表标题对结果进行排序。

要更好地理解此查询，请在您的目录中打开Python REPLflask_todo：

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

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

输出如下：

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

使用关闭 REPL CTRL + D。

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

lists = {}

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

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

k表示列表标题（即Home、Study、Work），这些标题是使用您传递给函数key参数的groupby()函数提取的。在这种情况下，该函数lambda t: t['title']接受一个待办事项并返回列表的标题（正如您todo['title']在前一个 for 循环中所做的那样）。g表示包含每个列表标题的待办事项的组。例如，在第一次迭代中，kwill 是'Home'，而g是一个包含项目和的可迭代对象。'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对象都将包含您items使用index()函数中的 SQL 查询从表中检索到的数据。为了更好地表示这些数据，让我们编写一个程序来遍历lists字典并显示每个列表及其项目。

打开list_example.py在您的flask_todo目录中调用的文件：

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：

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

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

在您的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>标签链接引导CSS文件和<script>标签链接的JavaScript代码，允许一些额外的引导功能。查看Bootstrap 文档以获取更多信息。

接下来，创建index.html将扩展此base.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

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

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

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

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

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

首先，打开app.py文件：

nano app.py

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

flask_todo/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 表单将新数据插入数据库，所以您允许methods=('GET', 'POST')在app.route()装饰器中使用 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以停止您的开发服务器。

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

nano app.py

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

flask_todo/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

通过添加<li>链接到create()视图函数的新导航项来编辑文件。然后使用块for上方的循环显示闪烁的消息content。这些在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 框架内容。如果您想查看sqlite3Python 模块，请阅读我们关于如何在 Python 3 中使用 sqlite3 模块的教程。

觉得文章有用？

点个广告表达一下你的爱意吧！