FastAPI 教程翻译 - 用户指南 29 - SQL（关系）数据库

最新推荐文章于 2024-06-03 17:13:30 发布

北望天狼

最新推荐文章于 2024-06-03 17:13:30 发布

阅读量2.4k

点赞数 2

分类专栏： FastAPI 教程翻译文章标签： python

原文链接：https://fastapi.tiangolo.com/tutorial/sql-databases/

版权

FastAPI 教程翻译专栏收录该内容

38 篇文章 17 订阅

订阅专栏

FastAPI 教程翻译 - 用户指南 29 - SQL（关系）数据库

FastAPI Tutorial - User Guide - SQL (Relational) Databases

FastAPI doesn’t require you to use a SQL (relational) database.

FastAPI 不需要您使用 SQL（关系）数据库。

But you can use any relational database that you want.

但是您可以使用所需的任何关系数据库。

Here we’ll see an example using SQLAlchemy.

在这里，我们将看到一个使用 SQLAlchemy 的示例。

You can easily adapt it to any database supported by SQLAlchemy, like:

您可以轻松地使其适应 SQLAlchemy 支持的任何数据库，例如：

PostgreSQL
MySQL
SQLite
Oracle
Microsoft SQL Server, etc.

In this example, we’ll use SQLite, because it uses a single file and Python has integrated support. So, you can copy this example and run it as is.

在此示例中，我们将使用 **SQLite **，因为它使用单个文件并且 Python 集成了支持。因此，您可以复制此示例并按原样运行它。

Later, for your production application, you might want to use a database server like PostgreSQL.

稍后，对于您的生产应用程序，您可能想要使用 PostgreSQL 之类的数据库服务器。

Tip

提示

There is an official project generator with FastAPI and PostgreSQL, all based on Docker, including a frontend and more tools: https://github.com/tiangolo/full-stack-fastapi-postgresql

有一个官方的项目生成器，带有 FastAPI 和 PostgreSQL，它们都基于 Docker，包括前端和更多工具：https://github.com/tiangolo/full-stack-fastapi-postgresql

Note

注意

Notice that most of the code is the standard SQLAlchemy code you would use with any framework.

请注意，大多数代码是您将在任何框架上使用的标准 SQLAlchemy 代码。

The FastAPI specific code is as small as always.

FastAPI 专用代码与以往一样小。

ORMs

FastAPI works with any database and any style of library to talk to the database.

FastAPI 可与任何数据库和任何样式的库一起使用来与数据库进行通信。

A common pattern is to use an “ORM”: an “object-relational mapping” library.

一种常见的模式是使用『ORM』：『对象关系映射』库。

An ORM has tools to convert (“map”) between objects in code and database tables (“relations”).

ORM 具有在代码表和数据库表（『关系』）中的对象之间进行转换（『map』）的工具。

With an ORM, you normally create a class that represents a table in a SQL database, each attribute of the class represents a column, with a name and a type.

使用 ORM，通常可以创建一个表示 SQL 数据库中的表的类，该类的每个属性表示一个具有名称和类型的列。

For example a class Pet could represent a SQL table pets.

例如，类 Pet 可以表示 SQL 表 pets。

And each instance object of that class represents a row in the database.

并且该类的每个实例对象都代表数据库中的一行。

For example an object orion_cat (an instance of Pet) could have an attribute orion_cat.type, for the column type. And the value of that attribute could be, e.g. "cat".

例如，对象 orion_cat（Pet的实例）可以为 type 列具有属性 orion_cat.type。该属性的值可以是 『cat』。

These ORMs also have tools to make the connections or relations between tables or entities.

这些 ORM 还具有在表或实体之间建立连接或关系的工具。

This way, you could also have an attribute orion_cat.owner and the owner would contain the data for this pet’s owner, taken from the table owners.

这样，您还可以拥有一个属性 orion_cat.owner，所有者将包含该宠物所有者的数据，该数据取自表 owners。

So, orion_cat.owner.name could be the name (from the name column in the owners table) of this pet’s owner.

因此，orion_cat.owner.name 可能是该宠物所有者的名字（来自 owners 表中的 name 列）。

It could have a value like "Arquilian".

它的值可能类似于 『Arquilian』。

And the ORM will do all the work to get the information from the corresponding table owners when you try to access it from your pet object.

当您尝试从宠物对象访问 ORM 时，ORM 将完成所有工作以从相应的表 owners 中获取信息。

Common ORMs are for example: Django-ORM (part of the Django framework), SQLAlchemy ORM (part of SQLAlchemy, independent of framework) and Peewee (independent of framework), among others.

常见的 ORM 例如：Django-ORM（Django 框架的一部分），SQLAlchemy ORM（SQLAlchemy 的一部分，独立于框架）和 Peewee（独立于框架），等等。

Here we will see how to work with SQLAlchemy ORM.

在这里，我们将了解如何使用 SQLAlchemy ORM。

In a similar way you could use any other ORM.

以类似的方式，您可以使用任何其他 ORM。

Tip

提示

There’s an equivalent article using Peewee here in the docs.

在文档中有一篇使用 Peewee 的等效文章。

File structure

文件结构

For these examples, let’s say you have a directory named my_super_project that contains a sub-directory called sql_app with a structure like this:

对于这些示例，假设您有一个名为 my_super_project 的目录，其中包含一个名为 sql_app 的子目录，其结构如下：

.
└── sql_app
    ├── __init__.py
    ├── crud.py
    ├── database.py
    ├── main.py
    ├── models.py
    └── schemas.py

The file __init__.py is just an empty file, but it tells Python that sql_app with all its modules (Python files) is a package.

文件 __init__.py 只是一个空文件，但它告诉 Python 带有所有模块的sql_app（Python 文件）是一个软件包。

Now let’s see what each file/module does.

现在，让我们看看每个文件 / 模块的功能。

Create the SQLAlchemy parts

创建 SQLAlchemy 部分

Let’s refer to the file sql_app/database.py.

让我们参考文件 sql_app/database.py。

Import the SQLAlchemy parts

导入 SQLAlchemy 部分

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Create a database URL for SQLAlchemy

为 SQLAlchemy 创建数据库 URL

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

In this example, we are “connecting” to a SQLite database (opening a file with the SQLite database).

在此示例中，我们正在『连接』到 SQLite 数据库（使用 SQLite 数据库打开文件）。

The file will be located at the same directory in the file test.db.

该文件将位于文件 test.db 中的同一目录中。

That’s why the last part is ./test.db.

这就是为什么最后一部分是 ./test.db 的原因。

If you were using a PostgreSQL database instead, you would just have to uncomment the line:

如果您使用的是 PostgreSQL 数据库，则只需取消注释以下行：

SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

…and adapt it with your database data and credentials (equivalently for MySQL, MariaDB or any other).

…… 并使其适应您的数据库数据和凭据（等效于 MySQL、MariaDB 或其他任何数据库）。

Tip

提示

This is the main line that you would have to modify if you wanted to use a different database.

如果要使用其他数据库，这是必须修改的主行。

Create the SQLAlchemy `engine`

创建 SQLAlchemy `engine`

The first step is to create a SQLAlchemy “engine”.

第一步是创建一个 SQLAlchemy『引擎』。

We will later use this engine in other places.

稍后我们将在其他地方使用此 engine 。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Note

注意

The argument:

参数：

connect_args={"check_same_thread": False}

…is needed only for SQLite. It’s not needed for other databases.

…… 仅对于 SQLite 才需要。其他数据库不需要它。

Technical Details

技术细节

By default SQLite will only allow one thread to communicate with it, assuming that each thread would handle an independent request.

默认情况下，假定每个线程将处理一个独立的请求，SQLite 仅允许一个线程与其通信。

This is to prevent accidentally sharing the same connection for different things (for different requests).

这是为了防止为不同的事物（针对不同的请求）意外共享同一连接。

But in FastAPI, using normal functions (def) more than one thread could interact with the database for the same request, so we need to make SQLite know that it should allow that with connect_args={"check_same_thread": False}.

但是在 FastAPI 中，使用正常功能（def），一个以上的线程可以与同一请求的数据库交互，因此我们需要使 SQLite 知道它应该允许使用 connect_args = {“ check_same_thread”：False}。

Also, we will make sure each request gets its own database connection session in a dependency, so there’s no need for that default mechanism.

此外，我们将确保每个请求在依赖关系中都有自己的数据库连接会话，因此不需要该默认机制。

Create a `SessionLocal` class

创建一个 `SessionLocal` 类

Each instance of the SessionLocal class will be a database session. The class itself is not a database session yet.

SessionLocal 类的每个实例将是一个数据库会话。该类本身还不是数据库会话。

But once we create an instance of the SessionLocal class, this instance will be the actual database session.

但是，一旦我们创建了 SessionLocal 类的实例，该实例将成为实际的数据库会话。

We name it SessionLocal to distinguish it from the Session we are importing from SQLAlchemy.

我们将其命名为 SessionLocal，以区别于我们从 SQLAlchemy 导入的 Session 。

We will use Session (the one imported from SQLAlchemy) later.

稍后我们将使用 Session（从 SQLAlchemy 导入的会话）。

To create the SessionLocal class, use the function sessionmaker:

要创建 SessionLocal 类，请使用函数 sessionmaker：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Create a `Base` class

创建一个 `Base` 类

Now we will use the function declarative_base() that returns a class.

现在，我们将使用函数 declarative_base（） 返回一个类。

Later we will inherit from this class to create each of the database models or classes (the ORM models):

稍后，我们将从该类继承以创建每个数据库模型或类（ORM 模型）：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Create the database models

创建数据库模型

Let’s now see the file sql_app/models.py.

现在让我们看一下文件 sql_app/models.py。

Create SQLAlchemy models from the `Base` class

从 `Base` 类创建 SQLAlchemy 模型

We will use this Base class we created before to create the SQLAlchemy models.

我们将使用之前创建的 Base 类来创建 SQLAlchemy 模型。

Tip

提示

SQLAlchemy uses the term “model” to refer to these classes and instances that interact with the database.

SQLAlchemy 使用术语『模型』来指代与数据库交互的这些类和实例。

But Pydantic also uses the term “model” to refer to something different, the data validation, conversion, and documentation classes and instances.

但 Pydantic 也使用术语『模型』来指代不同的东西，即数据验证，转换以及文档类和实例。

Import Base from database (the file database.py from above).

从 database 中导入Base（从上方导入文件 database.py）。

Create classes that inherit from it.

创建从其继承的类。

These classes are the SQLAlchemy models.

这些类是 SQLAlchemy 模型。

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    items = relationship("Item", back_populates="owner")


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="items")

The __tablename__ attribute tells SQLAlchemy the name of the table to use in the database for each of these models.

__tablename__ 属性告诉 SQLAlchemy 这些模型中的每一个在数据库中使用的表的名称。

Create model attributes/columns

创建模型属性 / 列

Now create all the model (class) attributes.

现在创建所有模型（类）属性。

Each of these attributes represents a column in its corresponding database table.

这些属性中的每一个都代表其相应数据库表中的一列。

We use Column from SQLAlchemy as the default value.

我们使用 SQLAlchemy 的 Column 作为默认值。

And we pass a SQLAlchemy class “type”, as Integer, String, and Boolean, that defines the type in the database, as an argument.

然后，我们将一个 SQLAlchemy 类『类型』作为 Integer、String 和 Boolean 传递，该类将数据库中的类型定义为参数。

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    items = relationship("Item", back_populates="owner")


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="items")

Create the relationships

创建关系

Now create the relationships.

现在创建关系。

For this, we use relationship provided by SQLAlchemy ORM.

为此，我们使用 SQLAlchemy ORM 提供的 relationship。

This will become, more or less, a “magic” attribute that will contain the values from other tables related to this one.

这或多或少将成为『魔术』属性，其中将包含与该表相关的其他表中的值。

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    items = relationship("Item", back_populates="owner")


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="items")

When accessing the attribute items in a User, as in my_user.items, it will have a list of Item SQLAlchemy models (from the items table) that have a foreign key pointing to this record in the users table.

当访问 User 中的 items 属性时，如 my_user.items 中一样，它将有一个 Item SQLAlchemy 模型列表（来自 Item 表），该模型具有指向此记录的外键用户表。

When you access my_user.items, SQLAlchemy will actually go and fetch the items from the database in the items table and populate them here.

当您访问 my_user.items 时，SQLAlchemy 实际上会去从 items 表中的数据库中获取项目，并在此处填充它们。

And when accessing the attribute owner in an Item, it will contain a User SQLAlchemy model from the users table. It will use the owner_id attribute/column with its foreign key to know which record to get from the users table.

当访问 Item 中的属性 owner 时，它将包含来自 User 表中的 users SQLAlchemy 模型。它将使用 owner_id 属性 / 列及其外键来知道要从 users 表中获取哪条记录。

Create the Pydantic models

创建 Pydantic 模型

Now let’s check the file sql_app/schemas.py.

现在让我们检查文件 sql_app/schemas.py。

Tip

提示

To avoid confusion between the SQLAlchemy models and the Pydantic models, we will have the file models.py with the SQLAlchemy models, and the file schemas.py with the Pydantic models.

为了避免 SQLAlchemy 模型和 Pydantic 模型之间的混淆，我们将在 SQLAlchemy 模型中使用文件 models.py，在 Pydantic 模型中使用文件 schemas.py。

These Pydantic models define more or less a “schema” (a valid data shape).

这些 Pydantic 模型或多或少定义了『模式』（有效数据形状）。

So this will help us avoiding confusion while using both.

因此，这将有助于我们避免在同时使用两者时产生混淆。

Create initial Pydantic models / schemas

创建初始 Pydantic 模型 / 模式

Create an ItemBase and UserBase Pydantic models (or let’s say “schemas”) to have common attributes while creating or reading data.

创建一个 ItemBase 和 UserBase Pydantic 模型（或称『模式』）以在创建或读取数据时具有共同的属性。

And create an ItemCreate and UserCreate that inherit from them (so they will have the same attributes), plus any additional data (attributes) needed for creation.

并创建一个从它们继承的 ItemCreate 和 UserCreate（这样它们将具有相同的属性）以及创建所需的任何其他数据（属性）。

So, the user will also have a password when creating it.

因此，用户在创建密码时也会有一个密码。

But for security, the password won’t be in other Pydantic models, for example, it won’t be sent from the API when reading a user.

但是为了安全起见，例如，password 不会在其他 Pydantic 模型中使用，在读取用户时不会从 API 发送。

from typing import List

from pydantic import BaseModel


class ItemBase(BaseModel):
    title: str
    description: str = None


class ItemCreate(ItemBase):
    pass


class Item(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True


class UserBase(BaseModel):
    email: str


class UserCreate(UserBase):
    password: str


class User(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []

    class Config:
        orm_mode = True

SQLAlchemy style and Pydantic style

SQLAlchemy 样式和 Pydantic 样式

Notice that SQLAlchemy models define attributes using =, and pass the type as a parameter to Column, like in:

注意，SQLAlchemy 模型使用 = 定义属性，并将类型作为参数传递给 Column，例如：

name = Column(String)

while Pydantic models declare the types using :, the new type annotation syntax/type hints:

Pydantic 模型使用 : 声明类型时，新的类型注释语法 / 类型提示：

name: str

Have it in mind, so you don’t get confused when using = and : with them.

注意这一点，因此在将它们使用 = 和 : 时不会感到困惑。

Create Pydantic models / schemas for reading / returning

创建 Pydantic 模型 / 模式以读取 / 返回

Now create Pydantic models (schemas) that will be used when reading data, when returning it from the API.

现在创建 Pydantic 模型（方案），该方案在读取数据以及从 API 返回数据时将使用。

For example, before creating an item, we don’t know what will be the ID assigned to it, but when reading it (when returning it from the API) we will already know its ID.

例如，在创建项目之前，我们不知道分配给它的 ID 是什么，但是在读取它（从 API 返回）时，我们已经知道它的 ID。

The same way, when reading a user, we can now declare that items will contain the items that belong to this user.

同样，当读取用户时，我们现在可以声明 items 将包含属于该用户的项目。

Not only the IDs of those items, but all the data that we defined in the Pydantic model for reading items: Item.

不仅是这些项目的 ID，还有我们在 Pydantic 模型中定义的用于读取项目的所有数据：Item。

from typing import List

from pydantic import BaseModel


class ItemBase(BaseModel):
    title: str
    description: str = None


class ItemCreate(ItemBase):
    pass


class Item(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True


class UserBase(BaseModel):
    email: str


class UserCreate(UserBase):
    password: str


class User(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []

    class Config:
        orm_mode = True

Tip

提示

Notice that the User, the Pydantic model that will be used when reading a user (returning it from the API) doesn’t include the password.

请注意，在读取用户（从 API 返回）时将使用的 User，Pydantic 模型不包含 password。

Use Pydantic’s `orm_mode`

使用 Pydantic 的 `orm_mode`

Now, in the Pydantic models for reading, Item and User, add an internal Config class.

现在，在 Pydantic 的模型文件中，读取 Item 和 User，添加一个内部 Config 类。

This Config class is used to provide configurations to Pydantic.

这个 Config 类用于为 Pydantic 提供配置。

In the Config class, set the attribute orm_mode = True.

在 Config 类中，设置属性 orm_mode = True。

from typing import List

from pydantic import BaseModel


class ItemBase(BaseModel):
    title: str
    description: str = None


class ItemCreate(ItemBase):
    pass


class Item(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True


class UserBase(BaseModel):
    email: str


class UserCreate(UserBase):
    password: str


class User(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []

    class Config:
        orm_mode = True

Tip

提示

Notice it’s assigning a value with =, like:

注意，它使用 = 来赋值，例如：
orm_mode = True
It doesn’t use : as for the type declarations before.

之前的类型声明不使用 :。

This is setting a config value, not declaring a type.

这是在设置配置值，而不是声明类型。

Pydantic’s orm_mode will tell the Pydantic model to read the data even if it is not a dict, but an ORM model (or any other arbitrary object with attributes).

Pydantic 的 orm_mode 会告诉 Pydantic 模型读取数据，即使它不是 dict 而是 ORM 模型（或任何其他具有属性的任意对象）。

This way, instead of only trying to get the id value from a dict, as in:

这样，而不是仅尝试从 dict 中获取 id 值，如下所示：

id = data["id"]

it will also try to get it from an attribute, as in:

它还将尝试从属性获取它，如：

id = data.id

And with this, the Pydantic model is compatible with ORMs, and you can just declare it in the response_model argument in your path operations.

有了这个，Pydantic 模型就可以与 ORM 兼容，您可以在路径操作中的 response_model 参数中声明它。

You will be able to return a database model and it will read the data from it.

您将能够返回数据库模型，并且它将从中读取数据。

Technical Details about ORM mode

有关 ORM 模式的技术详细信息

SQLAlchemy and many others are by default “lazy loading”.

SQLAlchemy 和许多其他默认情况下是『延迟加载』。

That means, for example, that they don’t fetch the data for relationships from the database unless you try to access the attribute that would contain that data.

例如，这意味着除非您尝试访问将包含该数据的属性，否则它们不会从数据库中获取关系数据。

For example, accessing the attribute items:

例如，访问属性 items：

current_user.items

would make SQLAlchemy go to the items table and get the items for this user, but not before.

可以使 SQLAlchemy 转到 items 表并获取此用户的项目，但不能早于此。

Without orm_mode, if you returned a SQLAlchemy model from your path operation, it wouldn’t include the relationship data.

没有 orm_mode，如果您从路径操作返回了一个 SQLAlchemy 模型，它将不包含关系数据。

Even if you declared those relationships in your Pydantic models.

即使您在 Pydantic 模型中声明了这些关系。

But with ORM mode, as Pydantic itself will try to access the data it needs from attributes (instead of assuming a dict), you can declare the specific data you want to return and it will be able to go and get it, even from ORMs.

但是在 ORM 模式下，由于 Pydantic 本身会尝试从属性访问其所需的数据（而不是假设是个 dict），因此您可以声明要返回的特定数据，甚至可以继续获取它来自 ORM。

CRUD utils

CRUD 实用程序

Now let’s see the file sql_app/crud.py.

现在让我们看一下文件 sql_app/crud.py。

In this file we will have reusable functions to interact with the data in the database.

在此文件中，我们将具有可重用的功能来与数据库中的数据进行交互。

CRUD comes from: Create, Read, Update, and Delete.

CRUD 来自：C 创建、R 读取、U 更新、D 删除。

…although in this example we are only creating and reading.

…… 尽管在此示例中，我们仅创建和阅读。

Read data

读取数据

Import Session from sqlalchemy.orm, this will allow you to declare the type of the db parameters and have better type checks and completion in your functions.

从 sqlalchemy.orm 导入 Session，这将允许您声明 db 参数的类型，并在函数中进行更好的类型检查和完成。

Import models (the SQLAlchemy models) and schemas (the Pydantic models / schemas).

导入 models（SQLAlchemy 模型）和 schemas（Pydantic 模型 / 模式）。

Create utility functions to:

创建实用程序函数以：

Read a single user by ID and by email.

通过 ID 和电子邮件读取单个用户。
Read multiple users.

读取多个用户。
Read a single item.

阅读单个项目。

from sqlalchemy.orm import Session

from . import models, schemas


def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()


def get_user_by_email(db: Session, email: str):
    return db.query(models.User).filter(models.User.email == email).first()


def get_users(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.User).offset(skip).limit(limit).all()


def create_user(db: Session, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def get_items(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Item).offset(skip).limit(limit).all()


def create_user_item(db: Session, item: schemas.ItemCreate, user_id: int):
    db_item = models.Item(**item.dict(), owner_id=user_id)
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Tip

提示

By creating functions that are only dedicated to interacting with the database (get a user or an item) independent of your path operation function, you can more easily reuse them in multiple parts and also add unit tests for them.

通过创建仅用于与路径操作函数无关的与数据库交互（获取用户或项目）的函数，您可以更轻松地在多个部分重用它们，并为其添加单元测试。

Create data

创建数据

Now create utility functions to create data.

现在创建实用程序函数来创建数据。

The steps are:

这些步骤是：

Create a SQLAlchemy model instance with your data.

使用您的数据创建一个 SQLAlchemy 模型实例。
add that instance object to your database session.

将实例对象添加到数据库会话中。
commit the changes to the database (so that they are saved).

将更改提交到数据库（以便将其保存）。
refresh your instance (so that it contains any new data from the database, like the generated ID).

刷新实例（以便它包含数据库中的任何新数据，例如生成的 ID）。

from sqlalchemy.orm import Session

from . import models, schemas


def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()


def get_user_by_email(db: Session, email: str):
    return db.query(models.User).filter(models.User.email == email).first()


def get_users(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.User).offset(skip).limit(limit).all()


def create_user(db: Session, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def get_items(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Item).offset(skip).limit(limit).all()


def create_user_item(db: Session, item: schemas.ItemCreate, user_id: int):
    db_item = models.Item(**item.dict(), owner_id=user_id)
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Tip

提示

The SQLAlchemy model for User contains a hashed_password that should contain a secure hashed version of the password.

User 的 SQLAlchemy 模型包含一个 hashed_password ，该密码应包含密码的安全哈希版本。

But as what the API client provides is the original password, you need to extract it and generate the hashed password in your application.

但是，由于 API 客户端提供的是原始密码，因此您需要提取原始密码并在应用程序中生成哈希密码。

And then pass the hashed_password argument with the value to save.

然后将值 hashed_password 保存。

Warning

警告

This example is not secure, the password is not hashed.

此示例不安全，未对密码进行哈希处理。

In a real life application you would need to hash the password and never save them in plaintext.

在现实生活中的应用程序中，您将需要对密码进行哈希处理，并且永远不要将其以明文形式保存。

For more details, go back to the Security section in the tutorial.

有关更多详细信息，请返回教程中的安全性部分。

Here we are focusing only on the tools and mechanics of databases.

在这里，我们仅关注数据库的工具和机制。

Tip

提示

Instead of passing each of the keyword arguments to Item and reading each one of them from the Pydantic model, we are generating a dict with the Pydantic model’s data with:

我们没有将每个关键字参数传递给 Item 并从 Pydantic 模型中读取每个参数，而是使用 Pydantic 模型的数据生成了一个 dict，其内容为：
item.dict()
and then we are passing the dict's key-value pairs as the keyword arguments to the SQLAlchemy Item, with:

然后我们将 dict 的键值对作为关键字参数传递给 SQLAlchemy Item，并带有：
Item(**item.dict())
And then we pass the extra keyword argument owner_id that is not provided by the Pydantic model, with:

然后，我们将 Pydantic 模型未提供的额外关键字参数 owner_id 传递给：
Item(**item.dict(), owner_id=user_id)

Main FastAPI app

主 FastAPI 应用

And now in the file sql_app/main.py let’s integrate and use all the other parts we created before.

现在，在文件 sql_app/main.py 中，我们可以集成和使用我们之前创建的所有其他部分。

Create the database tables

创建数据库表

In a very simplistic way create the database tables:

以一种非常简单的方式创建数据库表：

from typing import List

from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()


# Dependency
def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=List[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=List[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Alembic Note

Alembic 注意

Normally you would probably initialize your database (create tables, etc) with Alembic.

通常，您可能会使用 Alembic 初始化数据库（创建表等）。

And you would also use Alembic for “migrations” (that’s its main job).

而且您还将 Alembic 用于『迁移』（这是它的主要工作）。

A “migration” is the set of steps needed whenever you change the structure of your SQLAlchemy models, add a new attribute, etc. to replicate those changes in the database, add a new column, a new table, etc.

『迁移』是每当您更改 SQLAlchemy 模型的结构，添加新属性等以在数据库中复制这些更改，添加新列，新表等时所需的一组步骤。

Create a dependency

创建依赖

Info

信息

For this to work, you need to use Python 3.7 or above, or in Python 3.6, install the “backports”:

为此，您需要使用 Python 3.7 或更高版本，或者在 Python 3.6 中，安装『backports』：
pip install async-exit-stack async-generator
This installs async-exit-stack and async-generator.

这会安装 async-exit-stack 和 async-generator。

You can also use the alternative method with a “middleware” explained at the end.

您还可以将替代方法与最后说明的『中间件』一起使用。

Now use the SessionLocal class we created in the sql_app/databases.py file to create a dependency.

现在，使用我们在 sql_app/databases.py 文件中创建的 SessionLocal 类来创建依赖项。

We need to have an independent database session/connection (SessionLocal) per request, use the same session through all the request and then close it after the request is finished.

我们需要每个请求有一个独立的数据库会话 / 连接（SessionLocal），在所有请求中使用相同的会话，然后在请求完成后关闭它。

And then a new session will be created for the next request.

然后将为下一个请求创建一个新会话。

For that, we will create a new dependency with yield, as explained before in the section about Dependencies with yield.

为此，我们将创建一个带有 yield 的新依赖项，如之前在带有 yield 的依赖项部分中所述。

Our dependency will create a new SQLAlchemy SessionLocal that will be used in a single request, and then close it once the request is finished.

我们的依赖项将创建一个新的 SQLAlchemy SessionLocal，它将在单个请求中使用，然后在请求完成后将其关闭。

from typing import List

from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()


# Dependency
def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=List[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=List[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Info

信息

We put the creation of the SessionLocal() and handling of the requests in a try block.

我们将创建 SessionLocal() 和处理请求放入 try 块中。

And then we close it in the finally block.

然后在 finally 块中将其关闭。

This way we make sure the database session is always closed after the request. Even if there was an exception while processing the request.

这样，我们确保在请求后数据库会话始终关闭。即使在处理请求时出现异常。

But you can’t raise another exception from the exit code (after yield). See more in Dependencies with yield and HTTPException

但是您不能从退出代码中引发另一个异常（在 yield 之后）。请参阅带有 yield 和 HTTPException 的依赖项

And then, when using the dependency in a path operation function, we declare it with the type Session we imported directly from SQLAlchemy.

然后，在路径操作函数中使用依赖项时，我们使用直接从 SQLAlchemy 导入的 Session 类型声明它。

This will then give us better editor support inside the path operation function, because the editor will know that the db parameter is of type Session:

然后，这将为我们在路径操作函数中提供更好的编辑器支持，因为编辑器将知道 db 参数为 Session 类型：

from typing import List

from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()


# Dependency
def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=List[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=List[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Technical Details

技术细节

The parameter db is actually of type SessionLocal, but this class (created with sessionmaker()) is a “proxy” of a SQLAlchemy Session, so, the editor doesn’t really know what methods are provided.

参数 db 实际上是 SessionLocal 类型，但是此类（使用 sessionmaker() 创建）是SQLAlchemy Session 的『代理』，因此，编辑器并不真正知道哪种方法是提供。

But by declaring the type as Session, the editor now can know the available methods (.add(), .query(), .commit(), etc) and can provide better support (like completion). The type declaration doesn’t affect the actual object.

但通过将类型声明为 Session，编辑器现在可以知道可用的方法（.add()、.query()、.commit()等），并且可以提供更好的支持（例如自动补全）。类型声明不影响实际对象。

Create your FastAPI path operations

创建 FastAPI 路径操作

Now, finally, here’s the standard FastAPI path operations code.

现在，最后是标准的 FastAPI 路径操作代码。

from typing import List

from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()


# Dependency
def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=List[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=List[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

We are creating the database session before each request in the dependency with yield, and then closing it afterwards.

我们将在依赖项中的每个请求前使用 yield 创建数据库会话，然后在之后关闭它。

And then we can create the required dependency in the path operation function, to get that session directly.

然后，我们可以在路径操作函数中创建所需的依赖关系，以直接获取该会话。

With that, we can just call crud.get_user directly from inside of the path operation function and use that session.

这样，我们可以直接从路径操作函数内部调用 crud.get_user 并使用该会话。

Tip

提示

Notice that the values you return are SQLAlchemy models, or lists of SQLAlchemy models.

请注意，您返回的值是 SQLAlchemy 模型或 SQLAlchemy 模型列表。

But as all the path operations have a response_model with Pydantic models / schemas using orm_mode, the data declared in your Pydantic models will be extracted from them and returned to the client, with all the normal filtering and validation.

但是，由于所有路径操作都具有一个使用 orm_mode 的 Pydantic 模型 / 模式的 response_model，因此，您将从 Pydantic 模型中声明的数据提取出来并返回给客户端，并进行所有常规过滤和验证。

Tip

提示

Also notice that there are response_models that have standard Python types like List[schemas.Item].

还请注意，有一些具有标准 Python 类型的 response_models，如 List[schemas.Item]。

But as the content/parameter of that List is a Pydantic model with orm_mode, the data will be retrieved and returned to the client as normally, without problems.

但是，由于该 List 的内容 / 参数是带有 orm_mode 的 Pydantic 模型，因此将正常检索数据并将其返回给客户端，而不会出现问题。

About `def` vs `async def`

关于 `def` 与 `sync def`

Here we are using SQLAlchemy code inside of the path operation function and in the dependency, and, in turn, it will go and communicate with an external database.

在这里，我们在路径操作函数内部和依赖项中使用 SQLAlchemy 代码，然后它将与外部数据库进行通信。

That could potentially require some “waiting”.

这可能需要一些『等待』。

But as SQLAlchemy doesn’t have compatibility for using await directly, as would be with something like:

但是由于 SQLAlchemy 不兼容直接使用 await，因此类似：

user = await db.query(User).first()

…and instead we are using:

…… 会被代替为使用：

user = db.query(User).first()

Then we should declare the path operation functions and the dependency without async def, just with a normal def, as:

然后，我们应该声明路径操作函数和不带 async def 的依赖项，而仅使用普通的 def，如下所示：

@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    ...

Very Technical Details

深度技术细节

If you are curious and have a deep technical knowledge, you can check the very technical details of how this async def vs def is handled in the Async docs.

如果您好奇并且具有深厚的技术知识，可以在 Async 中查看有关如何处理 async def 和 def 的深度技术细节文档。

Migrations

迁移

Because we are using SQLAlchemy directly and we don’t require any kind of plug-in for it to work with FastAPI, we could integrate database migrations with Alembic directly.

由于我们直接使用 SQLAlchemy，并且不需要任何插件即可与 FastAPI 配合使用，因此我们可以将数据库迁移与Alembic 直接集成在一起。

And as the code related to SQLAlchemy and the SQLAlchemy models lives in separate independent files, you would even be able to perform the migrations with Alembic without having to install FastAPI, Pydantic, or anything else.

而且，由于与 SQLAlchemy 和 SQLAlchemy 模型相关的代码位于单独的独立文件中，您甚至可以使用 Alembic 执行迁移，而无需安装 FastAPI、Pydantic 或其他任何工具。

The same way, you would be able to use the same SQLAlchemy models and utilities in other parts of your code that are not related to FastAPI.

以同样的方式，您将能够在与 FastAPI 无关的代码的其他部分中使用相同的 SQLAlchemy 模型和实用程序。

For example, in a background task worker with Celery, RQ, or ARQ.

例如，在具有 Celery、RQ 或者 ARQ 的后台任务。

Review all the files

查看所有文件

Remember you should have a directory named my_super_project that contains a sub-directory called sql_app.

请记住，您应该有一个名为 my_super_project 的目录，其中包含一个名为 sql_app 的子目录。

sql_app should have the following files:

sql_app 应该具有以下文件：

sql_app/__init__.py: is an empty file.

sql_app/__init__.py：是一个空文件。
sql_app/database.py:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

sql_app/models.py:

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    items = relationship("Item", back_populates="owner")


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="items")

sql_app/schemas.py:

from typing import List

from pydantic import BaseModel


class ItemBase(BaseModel):
    title: str
    description: str = None


class ItemCreate(ItemBase):
    pass


class Item(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True


class UserBase(BaseModel):
    email: str


class UserCreate(UserBase):
    password: str


class User(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []

    class Config:
        orm_mode = True

sql_app/crud.py:

from sqlalchemy.orm import Session

from . import models, schemas


def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()


def get_user_by_email(db: Session, email: str):
    return db.query(models.User).filter(models.User.email == email).first()


def get_users(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.User).offset(skip).limit(limit).all()


def create_user(db: Session, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def get_items(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Item).offset(skip).limit(limit).all()


def create_user_item(db: Session, item: schemas.ItemCreate, user_id: int):
    db_item = models.Item(**item.dict(), owner_id=user_id)
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

sql_app/main.py:

from typing import List

from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()


# Dependency
def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=List[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=List[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Check it

检查

You can copy this code and use it as is.

您可以复制此代码并按原样使用。

Info

信息

In fact, the code shown here is part of the tests. As most of the code in these docs.

实际上，此处显示的代码是测试的一部分。就像这些文档中的大多数代码一样。

Then you can run it with Uvicorn:

然后，您可以使用 Uvicorn 运行它：

uvicorn sql_app.main:app --reload

And then, you can open your browser at http://127.0.0.1:8000/docs.

然后，您可以在 http://127.0.0.1:8000/docs 中打开浏览器。

And you will be able to interact with your FastAPI application, reading data from a real database:

您将能够与 FastAPI 应用程序进行交互，并从真实数据库中读取数据：

在这里插入图片描述

Interact with the database directly

直接与数据库进行交互

If you want to explore the SQLite database (file) directly, independently of FastAPI, to debug its contents, add tables, columns, records, modify data, etc. you can use DB Browser for SQLite.

如果要独立于 FastAPI 直接浏览 SQLite 数据库（文件）以调试其内容，添加表、列、记录、修改数据等，则可以使用 DB Browser for SQLite。

It will look like this:

它看起来像这样：

在这里插入图片描述

You can also use an online SQLite browser like SQLite Viewer or ExtendsClass.

您还可以使用在线SQLite浏览器，例如 SQLite Viewer 或者 ExtendsClass。

Alternative DB session with middleware

包含中间件的替代数据库会话

If you can’t use dependencies with yield – for example, if you are not using Python 3.7 and can’t install the “backports” mentioned above for Python 3.6 – you can set up the session in a “middleware” in a similar way.

如果您不能将依赖项与 yield 配合使用——例如，如果您未使用 Python 3.7，并且无法安装上面为 Python 3.6 提及的『backports』——您可以设置以类似的方式在『中间件』中建立会话。

A “middleware” is basically a function that is always executed for each request, with some code executed before, and some code executed after the endpoint function.

『中间件』基本上是始终针对每个请求执行的功能，其中某些代码在端点功能之前执行，而某些代码在端点功能之后执行。

Create a middleware

创建中间件

The middleware we’ll add (just a function) will create a new SQLAlchemy SessionLocal for each request, add it to the request and then close it once the request is finished.

我们将添加的中间件（只是一个函数）将为每个请求创建一个新的 SQLAlchemy SessionLocal，将其添加到请求中，然后在请求完成后将其关闭。

from typing import List

from fastapi import Depends, FastAPI, HTTPException, Request, Response
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()


@app.middleware("http")
async def db_session_middleware(request: Request, call_next):
    response = Response("Internal server error", status_code=500)
    try:
        request.state.db = SessionLocal()
        response = await call_next(request)
    finally:
        request.state.db.close()
    return response


# Dependency
def get_db(request: Request):
    return request.state.db


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=List[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=List[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Info

信息

We put the creation of the SessionLocal() and handling of the requests in a try block.

我们将创建 SessionLocal() 和处理请求放入 try 块中。

And then we close it in the finally block.

然后我们在 finally 块中将其关闭。

This way we make sure the database session is always closed after the request. Even if there was an exception while processing the request.

这样，我们确保在请求后数据库会话始终关闭。即使在处理请求时出现异常。

About `request.state`

关于 `request.state`

request.state is a property of each Request object. It is there to store arbitrary objects attached to the request itself, like the database session in this case. You can read more about it in Starlette’s docs about Request state.

request.state 是每个 Request 对象的属性。它可以存储附加到请求本身的任意对象，例如本例中的数据库会话。您可以在 Starlette’s docs about Request state 中了解更多相关信息。

For us in this case, it helps us ensure a single database session is used through all the request, and then closed afterwards (in the middleware).

在这种情况下，对于我们来说，这有助于我们确保在所有请求中使用单个数据库会话，然后再关闭（在中间件中）。

Dependencies with `yield` or middleware

与 `yield` 或中间件的依赖项

Adding a middleware here is similar to what a dependency with yield does, with some differences:

在此处添加中间件类似于带有 yield 的依赖项，但有一些区别：

It requires more code and is a bit more complex.

它需要更多代码，并且稍微复杂一些。
The middleware has to be an async function.

中间件必须是 async 功能。
- If there is code in it that has to “wait” for the network, it could “block” your application there and degrade performance a bit.
  
  如果其中包含必须『等待』网络的代码，则可能会『阻止』您的应用程序，从而降低性能。
- Although it’s probably not very problematic here with the way SQLAlchemy works.
  
  尽管在这里 SQLAlchemy 的工作方式可能不是很成问题。
- But if you added more code to the middleware that had a lot of I/O waiting, it could then be problematic.
  
  但是，如果您向具有大量 I/O 等待的中间件添加了更多代码，则可能会出现问题。
A middleware is run for every request.

中间件针对每个请求运行。
- So, a connection will be created for every request.
  
  因此，将为每个请求创建一个连接。
- Even when the path operation that handles that request didn’t need the DB.
  
  即使处理该请求的路径操作不需要 DB。

Tip

提示

It’s probably better to use dependencies with yield when they are enough for the use case.

当依赖项足以满足用例时，最好将依赖关系与 yield 一起使用。

Info

信息

Dependencies with yield were added recently to FastAPI.

最近，在 FastAPI 中添加了带有 yield 的依赖项。

A previous version of this tutorial only had the examples with a middleware and there are probably several applications using the middleware for database session management.

本教程的先前版本仅包含带有中间件的示例，并且可能有多个应用程序使用中间件进行数据库会话管理。
te’s docs about Request state](https://www.starlette.io/requests/#other-state) 中了解更多相关信息。

北望天狼

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
3
评论
FastAPI 教程翻译 - 用户指南 29 - SQL（关系）数据库

FastAPI 教程翻译 - 用户指南 29 - SQL（关系）数据库FastAPI Tutorial - User Guide - SQL (Relational) DatabasesFastAPI doesn’t require you to use a SQL (relational) database.FastAPI 不需要您使用 SQL（关系）数据库。But you can use any relational database that you want.但是您可以使用所需的任何关系数
复制链接

扫一扫