sqlalchemy2.0 新特性使用

一、前言

 sqlalchemy2.0及2.0+ 版本出的很多新特性，和之前的使用方法不太一样，并且删除了大部分已弃用的元素，将剩余的元素（主要是Query）降级为长期“遗留”状态。

 官方文档：点此链接

二、配置SQLALchemy

1、创建一个 plugin/plugin_sqlalchemy.py 文件，用来初始化 SQLalchemy 引擎

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import os


SQLALCHEMY_DATABASE_URL = "mysql+pymysql://root:123456@localhost:3306/fastapi?charset=utf8mb4"
POOL_SIZE = 20
# SQLALCHEMY_DATABASE_URL = "postgresql://root:123456@postgresserver/db"


engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    echo=True,  # echo 设为 True 会打印出实际执行的 sql，调试的时候更方便
    future=True,  # 使用 SQLAlchemy 2.0 API，向后兼容
    pool_size=POOL_SIZE, # 连接池的大小默认为 5 个，设置为 0 时表示连接无限制
    pool_recycle=3600, # 设置时间以限制数据库自动断开
)

# SessionLocal该类的每个实例将是一个数据库会话。该类本身还不是数据库会话。
# 一旦我们创建了SessionLocal该类的实例，该实例将成为实际的数据库会话。
# 要创建SessionLocal类，请使用函数sessionmaker，sessionmaker是一个工厂函数，返回一个配置好的类
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
db = SessionLocal()

2、在models层创建 baseModel.py 文件，定义一个基础数据表模型类。映射从一个基类开始，称为Base，它是通过对DeclarativeBase类进行简单的子类化创建的，baseModel继承Base类，之后的业务数据表模型都继承 baseModel 类。通过添加包含特殊类型注释的属性来声明表中的一部分列，该注释称为 Mapped。对于所有需要更具体自定义的基于列的属性，使用 mapped_column() 指令。

from sqlalchemy.orm import DeclarativeBase
from sqlalchemy import Column,Integer,DATETIME
from datetime import datetime

class Base(DeclarativeBase):
    pass


class baseModel(Base):
    # 定义为抽象类
    __abstract__ = True
    # 默认字段
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    create_user: Mapped[int] = mapped_column(Integer, default=0, comment="创建人")
    create_time: Mapped[datetime] = mapped_column(DATETIME, default=datetime.now, comment="创建时间")
    update_user: Mapped[int] = mapped_column(Integer, default=0, comment="更新人")
    update_time: Mapped[datetime] = mapped_column(DATETIME, default=datetime.now, comment="更新时间")
    is_delete: Mapped[int] = mapped_column(Integer, default=0, comment="删除标识：0-正常 1-已删除")

3、创建业务数据表模型 models/cms/user.py

from models.baseModel import baseModel
from sqlalchemy import String
from sqlalchemy.orm import Mapped,mapped_column

class User(baseModel):

    __tablename__ = "user" # 数据库表名
    username: Mapped[str] = mapped_column(String(20), nullable=False, unique=True, comment="用户姓名")
    password: Mapped[str] = mapped_column(String(20), nullable=False, comment="密码")
    nickname: Mapped[str] = mapped_column(String(50),nullable=True, comment="昵称")
    email: Mapped[str] = mapped_column(String(50), nullable=True,  comment="电子邮箱")

4、models/__init__.py 自己封装一个方法，使用create_all() 创建数据库表。 

from plugin.pulgin_sqlalchamy import engine
from models.baseModel import Base


def register_database():
    # 预先创建数据表
    from .cms import user

    Base.metadata.create_all(bind=engine)

三、外键和relationship

通过 ForeignKey 设置子表user的group_id字段作为主表group的外键。relationship，这个类可以定义属性，定义一对多关系，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。

from models.baseModel import baseModel
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship,Mapped,mapped_column

class User(baseModel):

    __tablename__ = "user" # 数据库表名
    username: Mapped[str] = mapped_column(String(20), nullable=False, unique=True, comment="用户姓名")
    password: Mapped[str] = mapped_column(String(20), nullable=False, comment="密码")
    nickname: Mapped[str] = mapped_column(String(50),nullable=True, comment="昵称")
    email: Mapped[str] = mapped_column(String(50), nullable=True,  comment="电子邮箱")
    # # 设置外键约束
    group_id: Mapped[int] = mapped_column(Integer, ForeignKey('group.id',onupdate='RESTRICT'), nullable=False)
    # 声明式relationship
    group: Mapped['Group'] = relationship(back_populates = 'user')


class Group(baseModel):

    __tablename__ = "group" # 用户组
    name: Mapped[str] = mapped_column(String(20), nullable=False,unique=True, comment='用户组')
    info: Mapped[str] = mapped_column(String(20), nullable=True,comment='描述')
    level: Mapped[int] = mapped_column(Integer,nullable=True,comment='分组级别')
    user: Mapped[list['User']] = relationship(back_populates = 'group')

通过关联属性来访问相关联对象：

# 查询
# 通过user表查询到group的组名
db_user = db.scalars(select(User)).first()
groupName = db_user.group.name

# 添加
db_group = Group(name = '研发中心')
# 方法一
User(username = 'lucy',password = '123456',group = db_group)
# 方法二：使用append方法
# db_user = User(username = 'lucy',password = '123456')
# db_group.user.append(db_user)
db.add(db_group)
db.commit()

 四、CRUD

1、和 1.x API 不同，2.0 API 中不再使用 query，而是使用 select 来查询数据。

from sqlalchemy import select
# 查询
res = db.execute(select(User).where(User.name == "spongebob"))
# 查询一条
# row = db.execute(select(User).order_by(User.id)).first()
# 使用scalars()，返回一个list[User实例]
# res = db.scalars(select(User).order_by(User.id)).all()
# 查询指定字段
# res = db.execute(select(User.name).order_by(User.id)).all()
for row in res：
    print(f'{row.id}{row.name}'

# 从第5条记录开始（即跳过前4条），获取接下来的10条记录  
res = db.execute(select(User).offset(4).limit(10)).all() 
      
# 连表查询
select(User, Group).join(User.group).order_by(User.id, Group.id)
# 链表查询指定ON
select(User).join(Group, User.group_id == Group.id)
# 链表查询， relationship() 绑定的属性
select(User).join(Group, User.group)
# 查询指定字段
select(User.name, Group.name).join(User.group).order_by(User.id, group.id)
          
# 给表起别名
from sqlalchemy.orm import aliased
user_cls = aliased(User, name="user_cls")
group_cls = aliased(Group, name="group_cls")
stmt = (select(user_cls, group_cls).join(user_cls.addresses.of_type(group_cls)).order_by(user_cls.id, group_cls.id))
row = db.execute(stmt).first()
print(f"{row.user_cls.name} {row.group_cls.name}")

2、INSERT数据库使用 insert() 替代 select()，批量添加数据：

from sqlalchemy import insert
# 批量add
db.execute(
    insert(User),
    [
        {"name": "spongebob", "nickname": "Spongebob Squarepants"},
        {"name": "sandy", "nickname": "Sandy Cheeks"},
        {"name": "patrick", "nickname": "Patrick Star"},
        {"name": "squidward", "nickname": "Squidward Tentacles"},
        {"name": "ehkrabs", "nickname": "Eugene H. Krabs"},
    ],
)
db.commit()

3、UPDATE数据库使用 update() ，批量修改：

from sqlalchemy import update
# 批量update
db.execute(
    update(User),
    [
        {"id": 1, "nickname": "Spongebob Squarepants"},
        {"id": 3, "nickname": "Patrick Star"},
        {"id": 5, "nickname": "Eugene H. Krabs"},
    ],
)

# 加上where条件
from sqlalchemy import update
stmt = (
    update(User)
    .where(User.name.in_(["squidward", "sandy"]))
    .values(nickname="Name starts with S")
)
db.execute(stmt)

# 同时更新两个scheeks，eugene
db.execute(
    update(Manager),
    [
        {
            "id": 1,
            "name": "scheeks",
            "manager_name": "Sandy Cheeks, President",
        },
        {
            "id": 2,
            "name": "eugene",
            "manager_name": "Eugene H. Krabs, VP Marketing",
        },
    ],
)
db.commit()

4、DELETE数据库使用 delete() ，批量删除：

# 删除多个
from sqlalchemy import delete
stmt = delete(User).where(User.name.in_(["squidward", "sandy"]))
db.execute(stmt)
# 删除单个
from sqlalchemy import delete
db.execute(delete(User).where(User.id == 1))
db.execute(delete(Group).where(Group.id == 1))
db.commit()