本文介绍了如何使用Flask web框架和SQLite数据库创建一个待办事项应用，展示了如何在Python中建立和管理一对多数据库关系。通过创建数据库表、插入数据以及使用外键关联，实现了一个允许用户创建待办事项列表和项目的应用。文章详细讲解了Flask与SQLite的集成，以及如何在Web应用中展示相关数据。
flask sqlite
The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.
作者选择了COVID-19救济基金来接受捐赠，这是Write for DOnations计划的一部分。
介绍 (Introduction)
Flask is a framework for building web applications using the Python language, and SQLite is a database engine that can be used with Python to store application data. In this tutorial, you will use Flask with SQLite to create a to-do application where users can create lists of to-do items. You will learn how to use SQLite with Flask and how one-to-many database relationships work.
Flask是使用Python语言构建Web应用程序的框架，而SQLite是可与Python一起使用以存储应用程序数据的数据库引擎。 在本教程中，您将结合使用Flask和SQLite来创建待办事项应用程序，用户可以在其中创建待办事项列表。 您将学习如何将SQLite与Flask一起使用以及一对多数据库关系如何工作。
A one-to-many database relationship is a relationship between two database tables where a record in one table can reference several records in another table. For example, in a blogging application, a table for storing posts can have a one-to-many relationship with a table for storing comments. Each post can reference many comments, and each comment references a single post; therefore, one post has a relationship with many comments. The post table is a parent table, while the comments table is a child table—a record in the parent table can reference many records in the child table. This is important to be able to have access to related data in each table.
一对多数据库关系是两个数据库表之间的关系，其中一个表中的一条记录可以引用另一表中的几条记录。 例如，在博客应用程序中，用于存储帖子的表可以与用于存储评论的表具有一对多关系。 每个帖子可以引用很多评论，每个评论引用单个帖子； 因此， 一个帖子与很多评论有关系。 post表是一个父表 ，而comment表是一个子表 -父表中的一条记录可以引用该子表中的许多记录。 重要的是要能够访问每个表中的相关数据。
We’ll use SQLite because it is portable and does not need any additional set up to work with Python. It is also great for prototyping an application before moving to a larger database such as MySQL or Postgres. For more on how to choose the right database system read our SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems article.
我们将使用SQLite，因为它具有可移植性，并且不需要任何其他设置即可与Python一起使用。 在移至更大的数据库(如MySQL或Postgres)之前，对应用程序进行原型制作也非常有用。 有关如何选择合适的数据库系统的更多信息，请阅读我们的SQLite，MySQL和PostgreSQL：关系数据库管理系统比较文章。
先决条件 (Prerequisites)
Before you start following this guide, you will need:
在开始遵循本指南之前，您需要：
A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series for your local machine. In this tutorial we’ll call our project directory
flask_todo.在本地Python 3编程环境中，请按照如何为本地计算机上的Python 3系列安装和设置本地编程环境系列中的教程进行操作。 在本教程中，我们将项目目录
flask_todo。An understanding of basic Flask concepts such as creating routes, rendering HTML templates, and connecting to an SQLite database. You can follow How To Make a Web Application Using Flask in Python 3, if you are not familiar with these concepts, but it’s not necessary.
了解Flask的基本概念，例如创建路由，呈现HTML模板以及连接到SQLite数据库。 如果您对这些概念不熟悉，可以按照“ 如何使用Python 3中的Flask制作Web应用程序”进行操作 ，但这不是必需的。
第1步-创建数据库 (Step 1 — Creating the Database)
In this step, you will activate your programming environment, install Flask, create the SQLite database, and populate it with sample data. You’ll learn how to use foreign keys to create a one-to-many relationship between lists and items. A foreign key is a key used to associate a database table with another table, it is the link between the child table and its parent table.
在此步骤中，您将激活您的编程环境，安装Flask，创建SQLite数据库，并使用示例数据填充它。 您将学习如何使用外键在列表和项目之间创建一对多关系。 外键是用于将数据库表与另一个表关联的键，它是子表与其父表之间的链接。
If you haven’t already activated your programming environment, make sure you’re in your project directory (flask_todo) and use this command to activate it:
如果尚未激活编程环境，请确保您位于项目目录( flask_todo )中，然后使用以下命令将其激活：
source env/bin/activate
源ENV /斌/激活
Once your programming environment is activated, install Flask using the following command:
激活编程环境后，请使用以下命令安装Flask：
- pip install flask 点安装烧瓶
Once the installation is complete, you can now create the database schema file that contains SQL commands to create the tables you need to store your to-do data. You will need two tables: a table called lists to store to-do lists, and an items table to store the items of each list.
安装完成后，您现在可以创建包含SQL命令的数据库模式文件，以创建存储工作数据所需的表。 您将需要两个表：一个称为lists的表，用于存储待办事项列表，以及一个items表，用于存储每个列表的项目。
Open a file called schema.sql inside your flask_todo directory:
打开一个名为schema.sql的内部flask_todo目录：
- nano schema.sql 纳米schema.sql
Type the following SQL commands inside this file:
在此文件中键入以下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)
);Save and close the file.
保存并关闭文件。
The first two SQL command are DROP TABLE IF EXISTS lists; and DROP TABLE IF EXISTS items;, these delete any already existing tables named lists and items so you don’t see confusing behavior. Note that this will delete all of the content you have in the database whenever you use these SQL commands, so ensure you don’t write any important content in the web application until you finish this tutorial and experiment with the final result.
前两个SQL命令是DROP TABLE IF EXISTS lists; 和DROP TABLE IF EXISTS items; ，这些操作会删除任何已存在的名为lists和items表，因此您不会看到令人困惑的行为。 请注意，无论何时使用这些SQL命令，这都会删除数据库中所有的内容，因此请确保在完成本教程并尝试最终结果之前，不要在Web应用程序中编写任何重要的内容。
Next, you use CREATE TABLE lists to create the lists table that will store the to-do lists (such as a study list, work list, home list, and so on) with the following columns:
接下来，使用CREATE TABLE lists来创建lists表，该表表将使用以下几列存储待办事项列表(例如学习列表，工作列表，主列表等)：
id: An integer that represents a primary key, this will get assigned a unique value by the database for each entry (i.e. to-do list).id：代表主键的整数，数据库将为每个条目(例如，待办事项列表)分配一个唯一值。created: The time the to-do list was created at.NOT NULLsignifies that this column should not be empty and theDEFAULTvalue is theCURRENT_TIMESTAMPvalue, which is the time at which the list was added to the database. Just likeid, you don’t need to specify a value for this column, as it will be automatically filled in.created：created待办事项列表的时间。NOT NULL表示此列不应为空，并且DEFAULT值是CURRENT_TIMESTAMP值，这是将列表添加到数据库的时间。 就像id一样，您无需为此列指定值，因为它将自动填充。title: The list title.title：列表标题。
Then, you create a table called items to store to-do items. This table has an ID, a list_id integer column to identify which list an item belongs to, a creation date, and the item’s content. To link an item to a list in the database you use a foreign key constraint with the line FOREIGN KEY (list_id) REFERENCES lists (id). Here the lists table is a parent table, which is the table that is being referenced by the foreign key constraint, this indicates a list can have multiple items. The items table is a child table, which is the table the constraint applies to. This means items belong to a single list. The list_id column references the id column of the lists parent table.
然后，创建一个名为items的表以存储待办事项。 该表具有一个ID，一个list_id整数列(用于标识该项目属于哪个列表)，创建日期以及该项目的内容。 要将项目链接到数据库中的列表，请在外键行FOREIGN KEY (list_id) REFERENCES lists (id)使用外键约束 。 这里的lists表是一个父表 ，这是外键约束所引用的表，这表示一个列表可以有多个项目。 items表是一个子表 ，是约束所适用的表。 这意味着项目属于一个列表。 list_id列引用lists父表的id列。
Since a list can have many items, and an item belongs to only one list, the relationship between the lists and items tables is a one-to-many relationship.
由于一个列表可以包含许多项目，并且一个项目仅属于一个列表，因此lists与items表之间的关系是一对多关系。
Next, you will use the schema.sql file to create the database. Open a file named init_db.py inside the flask_todo directory:
接下来，您将使用schema.sql文件创建数据库。 在flask_todo目录中打开一个名为init_db.py的文件：
- nano init_db.py 纳米init_db.py
Then add the following code:
然后添加以下代码：
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()Save and close the file.
保存并关闭文件。
Here you connect to a file called database.db that will be created once you execute this program. You then open the schema.sql file and run it using the executescript() method that executes multiple SQL statements at once.
在这里，您连接到名为database.db的文件，该文件将在执行该程序后创建。 然后，您打开schema.sql文件并使用schema.sql executescript()方法运行一次可同时执行多个SQL语句的文件。
Running schema.sql will create the lists and items tables. Next, using a Cursor object, you execute a few INSERT SQL statements to create three lists and five to-do items.
运行schema.sql将创建lists和items表。 接下来，使用Cursor对象 ，执行一些INSERT SQL语句以创建三个列表和五个待办事项。
You use the list_id column to link each item to a list via the list’s id value. For example, the Work list was the first insertion into the database, so it will have the ID 1. This is how you can link the Morning meeting to-do item to Work—the same rule applies to the other lists and items.
您可以使用list_id列通过列表的id值将每个项目链接到列表。 例如，“ Work列表是第一次插入数据库，因此其ID为1 。 这是将Morning meeting待办事项链接到“ Work ”的方式-相同的规则适用于其他列表和项目。
Finally, you commit the changes and close the connection.
最后，提交更改并关闭连接。
Run the program:
运行程序：
- python init_db.py python init_db.py
After execution, a new file called database.db will appear in your flask_todo directory.
执行后，一个名为database.db的新文件将出现在flask_todo目录中。
You’ve activated your environment, installed Flask, and created the SQLite database. Next, you’ll retrieve the lists and items from the database and display them in the application’s homepage.
您已经激活了环境，安装了Flask，并创建了SQLite数据库。 接下来，您将从数据库中检索列表和项目，并将其显示在应用程序的主页中。
第2步-显示待办事项 (Step 2 — Displaying To-do Items)
In this step, you will connect the database you created in the previous step to a Flask application that displays the to-do lists and the items of each list. You will learn how to use SQLite joins to query data from two tables and how to group to-do items by their lists.
在此步骤中，您将把在上一步中创建的数据库连接到Flask应用程序，该应用程序显示任务列表和每个列表的项目。 您将学习如何使用SQLite联接从两个表中查询数据，以及如何按待办事项列表对其进行分组。
First, you will create the application file. Open a file named app.py inside the flask_todo directory:
首先，您将创建应用程序文件。 在flask_todo目录中打开一个名为app.py的文件：
- nano app.py 纳米应用
And then add the following code to the file:
然后将以下代码添加到文件中：
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)Save and close the file.
保存并关闭文件。
The get_db_connection() function opens a connection to the database.db database file and then sets the row_factory attribute to sqlite3.Row. In this way you can have name-based access to columns; this means that the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn connection object you’ll be using to access the database.
get_db_connection()函数打开与database.db数据库文件的连接，然后将row_factory属性设置为sqlite3.Row 。 这样，您可以对列进行基于名称的访问。 这意味着数据库连接将返回行为类似于常规Python字典的行。 最后，该函数返回将用于访问数据库的conn连接对象。
In the index() view function, you open a database connection and execute the following SQL query:
在index()视图函数中，打开数据库连接并执行以下SQL查询：
SELECT i.content, l.title FROM items i JOIN lists l ON i.list_id = l.id ORDER BY l.title;You then retrieve its results by using the fetchall() method and save the data in a variable called todos.
然后，您可以使用fetchall()方法检索其结果，并将数据保存在名为todos的变量中。
In this query, you use SELECT to get the content of the item and the title of the list it belongs to by joining both the items and lists tables (with the table aliases i for items and l for lists). With the join condition i.list_id = l.id after the ON keyword, you will get each row from the items table with every row from the lists table where the list_id column of the items table matches the id of the lists table. You then use ORDER BY to order the results by list titles.
在此查询中，您可以使用SELECT来通过连接items表和lists表(表别名i表示表items ， l表示lists )将项目的内容和它所属的列表的标题联系起来。 随着连接条件i.list_id = l.id后ON关键字，你会得到从每一行items表，从每一行lists表，其中list_id的列items表中的匹配id中的lists表。 然后，您可以使用ORDER BY按列表标题对结果进行排序。
To understand this query better, open the Python REPL in your flask_todo directory:
为了更好地理解此查询，请在flask_todo目录中打开Python REPL ：
- python Python
To understand the SQL query, examine the contents of the todos variable by running this small program:
要了解SQL查询，请通过运行以下小程序来检查todos变量的内容：
- from app import get_db_connection 从应用程序导入get_db_connection
- conn = get_db_connection() conn = get_db_connection()
- todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \ todos = conn.execute('SELECT i.content，l.title FROM items i JOIN list l \
- ON i.list_id = l.id ORDER BY l.title;').fetchall() ON i.list_id = l.id OR BY BY l.title;')。fetchall()
- for todo in todos: 在待办事项中待办事项：
- print(todo['title'], ':', todo['content']) 打印(todo ['title']，'：'，todo ['content'])
You first import the get_db_connection from the app.py file then open a connection and execute the query (note that this is the same SQL query you have in your app.py file). In the for loop you print the title of the list and the content of each to-do item.
您首先要从app.py文件导入get_db_connection ，然后打开一个连接并执行查询(请注意，这与app.py文件中SQL查询相同)。 在for循环中，您将打印列表标题和每个待办事项的内容。
The output will be as follows:
输出将如下所示：
Output
Home : Buy fruit
Home : Cook dinner
Study : Learn Flask
Study : Learn SQLite
Work : Morning meeting
Close the REPL using CTRL + D.
使用CTRL + D关闭REPL。
Now that you understand how SQL joins work and what the query achieves, let’s return back to the index() view function in your app.py file. After declaring the todos variable, you group the results using the following code:
现在您已经了解了SQL联接的工作原理以及查询所实现的功能，让我们回到app.py文件中的index()视图函数。 声明todos变量后，使用以下代码对结果进行分组：
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
lists[k] = list(g)You first declare an empty dictionary called lists, then use a for loop to go through a grouping of the results in the todos variable by the list’s title. You use the groupby() function you imported from the itertools standard library. This function will go through each item in the todos variable and generate a group of results for each key in the for loop.
首先，您声明一个名为lists的空字典，然后使用for循环按列表标题对todos变量中的结果进行分组。 您可以使用从itertools标准库导入的groupby()函数。 此函数将遍历todos变量中的每个项目，并为for循环中的每个键生成一组结果。
k represents list titles (that is, Home, Study, Work), which are extracted using the function you pass to the key parameter of the groupby() function. In this case the function is lambda t: t['title'] that takes a to-do item and returns the title of the list (as you have done before with todo['title'] in the previous for loop). g represents the group that contains the to-do items of each list title. For example, in the first iteration, k will be 'Home', while g is an iterable that will contain the items 'Buy fruit' and 'Cook dinner'.
k表示列表标题(即Home ， Study ， Work )，这些列表标题是使用传递给groupby()函数的key参数的函数提取的。 在这种情况下，该函数是lambda t: t['title'] ，它接受一个待办事项并返回列表的标题(就像您之前在上一个for循环中使用todo['title']所做的一样)。 g表示包含每个列表标题的待办事项的组。 例如，在第一个迭代中， k将为'Home' ，而g是一个可迭代项 ，其中将包含项'Buy fruit'和'Cook dinner' 。
This gives us a representation of the one-to-many relationship between lists and items, where each list title has several to-do items.
这使我们可以表示列表和项目之间的一对多关系，其中每个列表标题都有几个待办事项。
When running the app.py file, and after the for loop finishes execution, lists will be as follows:
运行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>]}
Each sqlite3.Row object will contain the data you retrieved from the items table using the SQL query in the index() function. To represent this data better, let’s make a program that goes through the lists dictionary and displays each list and its items.
每个sqlite3.Row对象将包含您使用index()函数中SQL查询从items表中检索到的数据。 为了更好地表示此数据，让我们制作一个程序，它遍历lists字典并显示每个列表及其项。
Open a file called list_example.py in your flask_todo directory:
在flask_todo目录中打开一个名为list_example.py的文件：
- nano list_example.py 纳米list_example.py
Then add the following code:
然后添加以下代码：
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'])Save and close the file.
保存并关闭文件。
This is very similar to the content in your index() view function. The last for loop here illustrates how the lists dictionary is structured. You first go through the dictionary’s items, print the list title (which is in the list_ variable), then go through each group of to-do items that belong to the list and print the content value of the item.
这与index()视图函数中的内容非常相似。 这里的最后一个for循环说明了lists字典的结构。 您首先浏览字典中的项目，打印列表标题(在list_变量中)，然后遍历属于该列表的每组待办事项，并打印项目的内容值。
Run the list_example.py program:
运行list_example.py程序：
- python list_example.py python list_example.py
Here is the output of list_example.py:
这是list_example.py的输出：
Output
Home
Buy fruit
Cook dinner
Study
Learn Flask
Learn SQLite
Work
Morning meeting
Now that you understand each part of the index() function, let’s create a base template and create the index.html file you rendered using the line return render_template('index.html', lists=lists).
现在您已经了解了index()函数的每个部分，让我们创建一个基本模板，并使用return render_template('index.html', lists=lists)行创建您渲染的index.html文件。
In your flask_todo directory, create a templates directory and open a file called base.html inside it:
在flask_todo目录中，创建一个templates目录，并在其中打开一个名为base.html的文件：
- mkdir templates mkdir模板
- nano templates/base.html 纳米模板/base.html
Add the following code inside base.html, note that you’re using Bootstrap here. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:
在base.html添加以下代码，请注意，您在此处使用的是Bootstrap 。 如果您不熟悉Flask中HTML模板，请参阅如何在Python 3中使用Flask制作Web应用程序的步骤3 ：
<!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>Save and close the file.
保存并关闭文件。
Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta> tags provide information for the web browser, the <link> tag links the Bootstrap CSS files, and the <script> tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.
前面块中的大多数代码是标准HTML和Bootstrap所需的代码。 <meta>标记为Web浏览器提供信息， <link>标记链接Bootstrap CSS文件，而<script>标记是指向JavaScript代码的链接，这些JavaScript代码允许一些其他Bootstrap功能。 查看Bootstrap文档以获取更多信息。
Next, create the index.html file that will extend this base.html file:
接下来，创建将扩展此base.html文件的index.html文件：
- nano templates/index.html 纳米模板/index.html
Add the following code to index.html:
将以下代码添加到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 %}Here you use a for loop to go through each item of the lists dictionary, you display the list title as a card header inside an <h3> tag, and then use a list group to display each to-do item that belongs to the list in an <li> tag. This follows the same rules explained in the list_example.py program.
在这里，您可以使用for循环遍历lists字典中的每个项目，将列表标题显示为<h3>标记内的卡片标题，然后使用列表组显示属于该列表的每个待办事项在<li>标记中。 这遵循list_example.py程序中解释的相同规则。
You will now set the environment variables Flask needs and run the application using the following commands:
现在，您将设置Flask需要的环境变量，并使用以下命令运行应用程序：
- export FLASK_APP=app 导出FLASK_APP = app
- export FLASK_ENV=development 出口FLASK_ENV =发展
- flask run 烧瓶运行
Once the development server is running, you can visit the URL http://127.0.0.1:5000/ in your browser. You will see a web page with the “Welcome to FlaskTodo” and your list items.
开发服务器运行后，您可以在浏览器中访问URL http://127.0.0.1:5000/ 。 您将看到一个网页，其中包含“ Welcome to FlaskTodo”和您的列表项。
You can now type CTRL + C to stop your development server.
现在，您可以键入CTRL + C来停止开发服务器。
You’ve created a Flask application that displays the to-do lists and the items of each list. In the next step, you will add a new page for creating new to-do items.
您已经创建了一个Flask应用程序，其中显示了待办事项列表以及每个列表中的项目。 在下一步中，您将添加一个新页面来创建新的待办事项。
第3步-添加新的待办事项 (Step 3 — Adding New To-do Items)
In this step, you will make a new route for creating to-do items, you will insert data into database tables, and associate items with the lists they belong to.
在此步骤中，您将为创建待办事项创建新路径，将数据插入数据库表中，并将项目与它们所属的列表相关联。
First, open the app.py file:
首先，打开app.py文件：
- nano app.py 纳米应用
Then, add a new /create route with a view function called create() at the end of the file:
然后，在文件末尾添加带有名为create()的视图函数的新/create路由：
...
@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)Save and close the file.
保存并关闭文件。
Because you will use this route to insert new data to the database via a web form, you allow both GET and POST requests using methods=('GET', 'POST') in the app.route() decorator. In the create() view function, you open a database connection then get all the list titles available in the database, close the connection, and render a create.html template passing it the list titles.
因为您将使用此路由通过Web表单将新数据插入数据库，所以您可以在app.route()装饰器中使用methods=('GET', 'POST')允许GET和POST请求。 在create()视图函数中，打开一个数据库连接，然后获取数据库中所有可用的列表标题，关闭该连接，然后渲染一个create.html模板，将列表标题传递给它。
Next, open a new template file called create.html:
接下来，打开一个名为create.html的新模板文件：
- nano templates/create.html 纳米模板/create.html
Add the following HTML code to create.html:
将以下HTML代码添加到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 %}Save and close the file.
保存并关闭文件。
You use request.form to access the form data that is stored in case something goes wrong with your form submission (for example, if no to-do content was provided). In the <select> element, you loop through the lists you retrieved from the database in the create() function. If the list title is equal to what is stored in request.form then the selected option is that list title, otherwise, you display the list title in a normal non-selected <option> tag.
如果表单提交出现问题(例如，如果未提供待办事项内容)，则可以使用request.form访问存储的表单数据。 在<select>元素中，您可以在create()函数中浏览从数据库中检索到的列表。 如果列表标题等于request.form存储的内容，则所选选项就是该列表标题，否则，您将在普通的未选择的<option>标记中显示列表标题。
Now, in the terminal, run your Flask application:
现在，在终端中，运行Flask应用程序：
- flask run 烧瓶运行
Then visit http://127.0.0.1:5000/create in your browser, you will see a form for creating a new to-do item, note that the form doesn’t work yet because you have no code to handle POST requests that get sent by the browser when submitting the form.
然后在浏览器中访问http://127.0.0.1:5000/create ，您将看到一个用于创建新待办事项的表单，请注意，该表单尚无法使用，因为您没有代码来处理POST请求提交表单时由浏览器发送。
Type CTRL + C to stop your development server.
键入CTRL + C停止您的开发服务器。
Next, let’s add the code for handling POST requests to the create() function and make the form function properly, open app.py:
接下来，让我们将用于处理POST请求的代码添加到create()函数，并使该表单函数正常运行，打开app.py ：
- nano app.py 纳米应用
Then edit the create() function to look like so:
然后编辑create()函数，如下所示：
...
@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)Save and close the file.
保存并关闭文件。
Inside the request.method == 'POST' condition you get the to-do item’s content and the list’s title from the form data. If no content was submitted, you send the user a message using the flash() function and redirect to the index page. If this condition was not triggered, then you execute a SELECT statement to get the list ID from the provided list title and save it in a variable called list_id. You then execute an INSERT INTO statement to insert the new to-do item into the items table. You use the list_id variable to link the item to the list it belongs to. Finally, you commit the transaction, close the connection, and redirect to the index page.
在request.method == 'POST'条件内，您可以从表单数据中获取待办事项的内容和列表的标题。 如果未提交任何内容，则使用flash()函数向用户发送一条消息，然后重定向到索引页面。 如果未触发此条件，则执行SELECT语句以从提供的列表标题中获取列表ID并将其保存在名为list_id的变量中。 然后，您执行INSERT INTO语句以将新的待办事项插入items表。 您可以使用list_id变量将项目链接到其所属的列表。 最后，提交事务，关闭连接，然后重定向到索引页面。
As a last step, you will add a link to /create in the navigation bar and display flashed messages below it, to do this, open base.html:
最后一步，您将在导航栏中添加到/create的链接，并在其下方显示闪烁的消息，为此，请打开base.html ：
- nano templates/base.html 纳米模板/base.html
Edit the file by adding a new <li> navigation item that links to the create() view function. Then display the flashed messages using a for loop above the content block. These are available in the get_flashed_messages() Flask function:
通过添加一个新的<li>导航项来编辑文件，该导航项链接到create()视图函数。 然后使用content块上方的for循环显示闪烁的消息。 这些在get_flashed_messages() Flask函数中可用：
<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>Save and close the file.
保存并关闭文件。
Now, in the terminal, run your Flask application:
现在，在终端中，运行Flask应用程序：
- flask run 烧瓶运行
A new link to /create will appear in the navigation bar. If you navigate to this page and try to add a new to-do item with no content, you’ll receive a flashed message saying Content is required!. If you fill in the content form, a new to-do item will appear on the index page.
指向/create新链接将出现在导航栏中。 如果您导航到此页面并尝试添加没有内容的新待办事项，则会收到一条闪烁的消息，提示需要内容！ 。 如果填写内容表单，索引页面上将出现一个新的待办事项。
In this step, you have added the ability to create new to-do items and save them to the database.
在此步骤中，您添加了创建新待办事项并将其保存到数据库的功能。
You can find the source code for this project in this repository.
您可以在此存储库中找到该项目的源代码。
结论 (Conclusion)
You now have an application to manage to-do lists and items. Each list has several to-do items and each to-do item belongs to a single list in a one-to-many relationship. You learned how to use Flask and SQLite to manage multiple related database tables, how to use foreign keys and how to retrieve and display related data from two tables in a web application using SQLite joins.
现在，您有了一个用于管理待办事项列表和项目的应用程序。 每个列表都有几个待办事项，每个待办事项以一对多关系属于一个列表。 您学习了如何使用Flask和SQLite管理多个相关的数据库表，如何使用外键，以及如何使用SQLite联接从Web应用程序的两个表中检索和显示相关数据。
Furthermore, you grouped results using the groupby() function, inserted new data to the database, and associated database table rows with the tables they are related to. You can learn more about foreign keys and database relationships from the SQLite documentation.
此外，您使用groupby()函数对结果进行了分组，将新数据插入到数据库中，并将数据库表行与它们所关联的表相关联。 您可以从SQLite文档中了解有关外键和数据库关系的更多信息。
You can also read more of our Python Framework content. If you want to check out the sqlite3 Python module, read our tutorial on How To Use the sqlite3 Module in Python 3.
您还可以阅读更多Python框架内容 。 如果要签出sqlite3 Python模块，请阅读我们的有关如何在Python 3中使用sqlite3模块的教程。
flask sqlite
扫一扫
2394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
