如何在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:关系数据库管理系统的比较 文章。

先决条件

在开始遵循本指南之前,您需要:

第 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;,它们会删除任何已经存在的名为 listsitems 的表,因此您不会看到令人困惑的行为。 请注意,无论何时使用这些 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 列。

由于一个列表可以有许多个项目,而一个项目只属于一个列表,listsitems表之间的关系是[ 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 将创建 listsitems 表。 接下来,使用 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 通过连接 itemslists 表(与表items 的别名 ilists 的别名 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 表示列表标题(即 HomeStudyWork),它们是使用您传递给 [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 模块的教程。