8、fastApi-使用数据库-sqlmodel

官方推荐的是: sqlmodel, 它是基于SQLAlchemy的, 这篇文章是对官方文档的一个汇总整理和自己的学习笔记记录

一、安装三方库

pip install sqlmodel
pip install pymysql

二、单表

1、创建单表

这里特别说明下， 除了Id字段外, 其他字段都有一个index属性, 起到索引作用， 这样可以提高查询速度

1）新建一个users.py用于存放要新建的表结构

from sqlmodel import Field, SQLModel


class Users(SQLModel, table=True):
    userId: int = Field(primary_key=True)   # 主键
    userName: str = Field(index=True)
    userAge: int | None = Field(default=None, index=True)

2）新建一个sql.py用于sql操作

sqlurl连接格式应该是: mysql+pymysql://数据库用户名:用户登录数据库的密码@数据库ip:数据库端口/数据库名

from server.utils import envUtils
from sqlmodel import SQLModel, create_engine, Session, text
from models import users    # 这是创建的users表

class sql:
    def __init__(self):
        self.sqlUrl = f"mysql+pymysql://{envUtils.get_env('SQL_USER')}:{envUtils.get_env('SQL_PASSWORD')}@{envUtils.get_env('SQL_IP')}:{envUtils.get_env('SQL_PORT')}/{envUtils.get_env('SQL_DB')}"


    @property   # 创建一个数据库引擎
    def engine(self):
        return create_engine(self.sqlUrl, echo=True)

    @property   # 创建一个数据库会话
    def session(self):
        return Session(self.engine)

    # 创建数据库表: 只在初始化环境的时候执行一次
    def create_all(self):
        # 如果数据库不存在则创建一个新的数据库
        sqlCheckUrl = f'{"/".join(self.sqlUrl.split("/")[:-1])}/'
        temp_engine = create_engine(sqlCheckUrl)
        with temp_engine.connect() as conn:
            conn.execute(text(f"CREATE DATABASE IF NOT EXISTS {envUtils.get_env('SQL_DB')}"))
            conn.commit()  # 提交事务

        temp_engine.dispose()  # 关闭临时连接

        # 创建表
        SQLModel.metadata.create_all(self.engine)


# 创建数据库表
sql().create_all()

3）执行sql.py

执行过程:

执行结果：

2、表内添加数据

# 第一种创建方式
user = users.Users(userName='张三', userAge=18)  # 由于id设置了int主键，所以创建的时候可以不传，数据库会自动生成
session = sql().session
session.add(user)
session.commit()
session.close()  # 不在with内使用session这里就必须关闭会话


# 第二种创建方式
with sql().session as session:
    user = users.Users(userName='李四')  # 由于userAge设置了可空, 所以页可以不传
    session.add(user)
    session.commit()

3、查询

进行查询前需要先保持数据跟下图一致

1）普通查询：

from sqlmodel import SQLModel, create_engine, Session, text, select, or_, col

cmd = select(users.Users)
result = sql().session.exec(cmd).all()  # 查询全部
print(f'查询结果: {result}')
result = sql().session.exec(cmd).first()  # 查询第一条， 如果没有则返回None
print(f'查询结果: {result}')
result = sql().session.exec(cmd).one()  # 查询一条， 如果没有则返回错误， 如果不止一行也会报错
print(f'查询结果: {result}')

查询结果如下：

2）范围查询

from sqlmodel import SQLModel, create_engine, Session, text, select, or_, col

# 读取范围数据
cmd = select(users.Users).limit(2)  # 只获取前两个结果
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')

# 偏移查询: 利用偏移查询, 可以实现数据分页查询的功能
cmd = select(users.Users).offset(0).limit(2)    # 只取两个结果
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')
cmd = select(users.Users).offset(1).limit(2)    # 只取两个结果: 偏移量为1，也就是只取第2个和第3个结果
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')
cmd = select(users.Users).offset(2).limit(2)    # 只取两个结果: 偏移量为2，也就是只取第3个和第4个结果
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')
cmd = select(users.Users).offset(3).limit(2)    # 只取两个结果: 偏移量为3，也就是只取第4个和第5个结果, 没有第五条数据, 所以只返回第四条数据
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')

查询结果：

3）条件查询:

这里需要加col包裹的查询, 我在实际尝试过程中不加col并没有出现报错。不知道是不是因为版本的问题，不过这里先记录下

from sqlmodel import SQLModel, create_engine, Session, text, select, or_, col


# where 查询

# 单条件
cmd = select(users.Users).where(users.Users.userAge == 18)
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')  # 这里一定要遍历

## 由于age字段是可为None的int字段类型, 所以在查询的时候可能会出现报错, 如果出现报错则使用col包裹
cmd = select(users.Users).where(col(users.Users.userAge) == 18)
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')  # 这里一定要遍历

# 多条件的第一种方式：关系且
cmd = select(users.Users).where(users.Users.userAge > 14, users.Users.userAge < 30)
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')  # 这里一定要遍历

# 多条件的第二种方式：关系且
cmd = select(users.Users).where(users.Users.userAge > 14).where(users.Users.userAge < 30)
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')  # 这里一定要遍历

# 多条件: 关系或
cmd = select(users.Users).where(or_(users.Users.userAge > 14, users.Users.userAge < 30))
result = sql().session.exec(cmd)
print(f'查询结果: {result.all()}')  # 这里一定要遍历

查询结果:

如果要查询的是某个表的主键, 可以使用get方法, 来简化代码:

with sql().session as session:
    result = session.get(users.Users, 2)    # 获取userId为2的用户信息
    print(result)

上述代码等价于:

with sql().session as session:
    cmd = select(users.Users).where(users.Users.userId == 2)
    result = session.exec(cmd).first()
    print(result)

4、更新数据

# 更新数据
with sql().session as session:
    cmd = select(users.Users).where(users.Users.userId == 1)
    result = session.exec(cmd).one()
    print(f'原始数据: {result}')
    result.userAge = 20
    session.add(result)
    session.commit()

    cmd = select(users.Users).where(users.Users.userId == 1)
    result = session.exec(cmd).one()
    print(f'更新后的数据: {result}')

执行结果:

5、删除数据

# 删除数据
with sql().session as session:
    # 查看原始数据
    cmd = select(users.Users)
    result = session.exec(cmd).all()
    print(f'原始数据: {result}')

    # 进行删除操作
    cmd = select(users.Users).where(users.Users.userId == 1)
    result = session.exec(cmd).one()
    session.delete(result)
    session.commit()

    # 查看删除后的数据
    cmd = select(users.Users)
    result = session.exec(cmd).all()
    print(f'删除后的数据: {result}')

执行结果:

三、多表

1、连接表

1）创建表

创建一个teams.py

from sqlmodel import Field, SQLModel

class Teams(SQLModel, table=True):
    teamId: int = Field(primary_key=True)   # 主键
    teamName: str = Field(index=True)

创建一个users.py

用户的temId需要跟teams表做关联

from sqlmodel import Field, SQLModel
from .teams import Teams

class Users(SQLModel, table=True):
    userId: int = Field(primary_key=True)   # 主键
    userName: str = Field(index=True)
    userAge: int | None = Field(default=None, index=True)
    
    userTeamId: int | None = Field(default=None, foreign_key='teams.teamId')    # 表示 userTeamId 关联 Teams 表中的id

创建表:

# 创建数据库表
from models import users, teams

sql().create_all()

创建结果:

创建好表之后自行添加数据， 方便后续: teames表数据:

users表数据：

2）连接表数据查询

关联查询:

with sql().session as session:
    # 使用where查询
    cmd = select(users.Users.userName, teams.Teams.teamName).where(users.Users.userTeamId == teams.Teams.teamId)
    result = session.exec(cmd).all()
    print(f'查询结果: {result}')

    # 使用join查询
    cmd = select(users.Users.userName, teams.Teams.teamName).join(teams.Teams)
    result = session.exec(cmd).all()
    print(f'查询结果: {result}')

查询结果:

上述where查询和join查询结果一致

上述结果只输出了张三和李四, 由于张龙没有分组, 所以结果里就被过滤掉了, 如果希望输出张龙的记过, 需要用到isouter

with sql().session as session:
    # 使用where查询
    cmd = select(users.Users.userName, teams.Teams.teamName).where(users.Users.userTeamId == teams.Teams.teamId)
    result = session.exec(cmd).all()
    print(f'查询结果: {result}')

    # 使用join查询
    cmd = select(users.Users.userName, teams.Teams.teamName).join(teams.Teams, isouter=True)
    result = session.exec(cmd).all()
    print(f'查询结果: {result}')

此时, 查询结果如下:

如果想要在查询过程再次进行过滤：

with sql().session as session:
    # 使用join查询
    cmd = select(users.Users.userName, teams.Teams.teamName).join(teams.Teams, isouter=True).where(users.Users.userId > 1)
    result = session.exec(cmd).all()
    print(f'查询结果: {result}')

查询结果:

3、关系表

先说下我对关系表的理解: 大体上跟连接表区别不大, 唯一的区别在数据填充上:

打比方有这样一个场景, 新增一个用户, 该用户属于一个新的team团队, 如果用过往的方式进行填充, 则需要先添加一个team, 然后在数据库中提交数据, 然后再添加一个所属于新team的用户，然后再提交, 需要进行两次提交.

但是如果用关系表的方式, 则只需要一次提交即可。无疑, 不管是对于代码管理来讲, 还是对操作效率来讲, 使用关系表都是比较高效的

1）创建关系表和数据

新建一个team.py内容如下:

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, List, Optional

if TYPE_CHECKING:
    from .user import User

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    users: list['User'] = Relationship(back_populates='team') # 这里的users对应Team表的back_populates="users", 

新建一个user.py内容如下:

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, Optional

if TYPE_CHECKING:
    from .team import Team

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: Optional[int] = Field(default=None, index=True)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    team: Optional["Team"] = Relationship(back_populates="users")  # 这里的team对应User表的back_populates='team'

最后创建表和数据:

# 创建数据: 创建一个包含了新团队的用户
with sql().session as session:
    team1 = team.Team(name='牛马组')
    user1 = user.User(name='张三', age=18, team=team1)
    session.add(user1)
    session.commit()

# 创建数据: 创建一个包含了新用户的新团队
with sql().session as session:
    user1 = user.User(name='张龙', age=6)
    user2 = user.User(name='赵虎', age=40)
    team1 = team.Team(name='摸鱼组', users=[user1, user2])

    session.add(team1)
    session.commit()

# 创建数据: 只新增团队
with sql().session as session:
    team1 = team.Team(name='社畜组')
    session.add(team1)
    session.commit()

# 创建数据: 只新增用户
with sql().session as session:
    user1 = user.User(name='李四', age=25)
    session.add(user1)
    session.commit()

最后生成的表和数据结果为:

上述示例中：不管是【创建一个包含了新团队的新用户】，还是【创建一个包含了新用户的新团队】都是只进行了一次提交, 这个就是关系表在创建上的方便之处。

这里需要注意: 如果model表不分开存储, 可以使用官网示例，但是如果要分开存储的话，就涉及到了循环引用的问题, 此时需要引入TYPE_CHECKING来进行处理, 参考官网示例

2）数据查询

with sql().session as session:
    # 旧方式: 使用连接表的查询方式
    cmd = select(user.User, team.Team).join(team.Team).where(user.User.id == 1)
    result = session.exec(cmd).one()
    print(result)

    # 新方式: 使用关系表的查询方式
    cmd = select(user.User).where(user.User.id == 1)
    result = session.exec(cmd).one()
    print(result, result.team)   # 查询用户所在team

查询结果:

当然，也可以通过team表去查询在某一个team中的所有用户

3）数据删除

cascade_delete=True:

该字段需要配置在team表的users字段中，表示当删除了某一个team的时候, 同步删除属于这个team用户的操作

特别注意： 官方说这个配置只在python操作数据库的时候生效, 如果有人直接使用sql进行操作则不会生效

配置如下:

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, List, Optional

if TYPE_CHECKING:
    from .user import User

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    users: list['User'] = Relationship(back_populates='team', cascade_delete=True)

代码删除操作:

# 删除数据
with sql().session as session:
    # 删除前的数据查询
    cmd = select(user.User, team.Team).join(team.Team, isouter=True)
    result = session.exec(cmd).all()
    print('删除前的数据查询结果: ')
    [print(item) for item in result]

    # 删除id=2的团队
    cmd = select(team.Team).where(team.Team.id == 2)
    result = session.exec(cmd).one()
    session.delete(result)
    session.commit()

    # 删除后的数据查询
    cmd = select(user.User, team.Team).join(team.Team, isouter=True)
    result = session.exec(cmd).all()
    print('删除后的数据查询结果: ')
    [print(item) for item in result]

查看结果:

数据库直接操作删除:

ondelete

该配置只对直接操作数据库有效

额外配置:

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, Optional

if TYPE_CHECKING:
    from .team import Team

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: Optional[int] = Field(default=None, index=True)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id", ondelete="CASCADE")
    team: Optional["Team"] = Relationship(back_populates="users")

删除后的效果跟之前相符 ondelete参数：

• CASCADE：在数据库直接删除团队时, 会把属于这个团队的角色数据全部删除，等同于在代码中配置cascade_delete=True后，然后在代码中删除数据。
• SET NULL：在数据库直接删除团队时，会将属于这个团队的用户的团队的值设置为Null。
• RESTRICT：在数据库直接删除团队时, 会报错。

passive_deletes="all"

ondelete有参数可以控制当团队被删除时， 团队成员删除还是只把对应成员的团队值设置为Null, 这个参数只针对数据库操作才会生效, 那么代码中如果想要实现当删除团队时, 不删除属于这个团队的成员而只是将这个值变为Null应该怎么做呢？使用这个参数来解决

配置方式:

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, List, Optional

if TYPE_CHECKING:
    from .user import User

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    users: list['User'] = Relationship(back_populates='team', passive_deletes="all")

4、多对多的数据模型

再来假设一个场景: 打比方张三能力很强，同时在两个团队中任职，此时要如何设计数据模型？sql中不支持list类型的字段。

这里就需要用到多对多的数据模型: 一张用户表, 一张团队表, 一张用户团队关联表

1）创建表和数据

team表:

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, List, Optional
from .userTeamLink import UserTeamLink

if TYPE_CHECKING:
    from .user import User

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    users: list['User'] = Relationship(back_populates='teams', link_model=UserTeamLink)

user表：

from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
from typing import TYPE_CHECKING, Optional, List
from .userTeamLink import UserTeamLink
if TYPE_CHECKING:
    from .team import Team

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: Optional[int] = Field(default=None, index=True)

    teams: List["Team"] = Relationship(back_populates="users", link_model=UserTeamLink)

新建一个userTeamLink表：

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
from typing import TYPE_CHECKING, Optional
if TYPE_CHECKING:
    from .team import Team
    from .user import User
class UserTeamLink(SQLModel, table=True):
    team_id: Optional[int] = Field(default=None, foreign_key="team.id", primary_key=True)
    user_id: Optional[int] = Field(default=None, foreign_key="user.id", primary_key=True)

最后新建表和添加数据

# 创建数据库表
sql().create_all()

# 创建数据
with sql().session as session:
    team1 = team.Team(name='牛马组')
    team2 = team.Team(name='摸鱼组')

    user1 = user.User(name='张三', age=18, teams=[team1, team2])
    user2 = user.User(name='李四', age=12, teams=[team1])

    session.add(user1)
    session.add(user2)

    session.commit()

添加完之后的结果：

2）查询数据

# 查询数据·
with sql().session as session:
    cmd = select(user.User).where(user.User.name == '张三')
    result = session.exec(cmd).one()
    print(result, result.teams)

查询结果:

四、数据库迁移

为什么要做数据库迁移: 随着开发不断深入，数据表可能面临修改、调整。如果纯粹手动在数据库中执行命令，容易出错，因此可以使用数据库迁移功能来完成。

1、安装 alembic

pip install alembic

2、初始化

alembic init alembic

执行完命令后，会在执行命令所在的路径生成一个名为alembic的文件夹, 所以请在你期望的目录去创建初始化文件, 我自己是放在sql目录下去管理的

3、修改配置：

1）修改根目录下alembic.ini文件中数据库地址sqlalchemy.url

2）修改alembic/script.py.mako

这个是用于同步表结构的脚本模板, 默认是没有导入sqlmodel的，这里需要手动导入一下，否则更新表结构的时候可能会报: 找不到sqlmodel模块的错误

3）修改alembic/env.py

这里采用动态导入, 不然每次修改一个库都需要重新导入还挺麻烦的

4、创建数据库迁移脚本

alembic revision --autogenerate -m "数据库迁移"

5、进行数据库迁移操作

alembic upgrade head

至此，sqlmode数据迁移流程已结束

6、小技巧: 配置fastAPI服务器启动后自动进行数据库迁移操作

fastAPI有生命周期函数, 可以将创建数据迁移脚本和进行数据库迁移的步骤放到生命周期函数中，查看生命周期

from fastapi import FastAPI
from contextlib import asynccontextmanager

# 定义生命周期函数
@asynccontextmanager
async def lifespan(app: FastAPI):
    # Startup 逻辑（原 on_event("startup") 的内容）
    print("启动初始化...")
    # 例如：初始化数据库连接
    # await connect_db()
    
    yield  # 这里分隔启动和关闭逻辑
    
    # Shutdown 逻辑（原 on_event("shutdown") 的内容，可选）
    print("关闭清理...")
    # 例如：关闭数据库连接
    # await close_db()

# 创建 FastAPI 实例时传入 lifespan
app = FastAPI(lifespan=lifespan)