flask sqlite_如何通过Flask和SQLite使用一对多数据库关系

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 )中，然后使用以下命令将其激活：

Once your programming environment is activated, install Flask using the following command:

激活编程环境后，请使用以下命令安装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目录：

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 NULL signifies that this column should not be empty and the DEFAULT value is the CURRENT_TIMESTAMP value, which is the time at which the list was added to the database. Just like id, 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