之前一直对SQLAlchemy中的关系加载很模糊
一次百度在SQLAlchemy中如何加载关联数据，给出的答案是selectinload，发现蛮好用，就一直使用了，没再继续了解其他的关系API，一次偶然的技术需要到这些了，赶紧来复习一下
官方文档：
关系加载技术：https://www.osgeo.cn/sqlalchemy/orm/loading_relationships.html
关系加载器API：https://www.osgeo.cn/sqlalchemy/orm/loading_relationships.html?highlight=selectinload#relationship-loader-api
案例
以下使用在模型中定义lazy属性来加载，同options加载方式一致。
模型1，默认
from sqlalchemy.orm import relationship
from db.db_base import BaseModel
from sqlalchemy import Column, String, Boolean, Integer, ForeignKey, Textclass VadminIssueCategory(BaseModel):__tablename__ = "vadmin_help_issue_category"__table_args__ = ({'comment': '常见问题类别表'})name = Column(String(50), index=True, nullable=False, comment="类别名称")platform = Column(String(8), index=True, nullable=False, comment="展示平台")is_active = Column(Boolean, default=True, comment="是否可见")issues = relationship("VadminIssue", back_populates='category')user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)class VadminIssue(BaseModel):__tablename__ = "vadmin_help_issue"__table_args__ = ({'comment': '常见问题记录表'})category_id = Column(ForeignKey("vadmin_help_issue_category.id", ondelete='CASCADE'), comment="类别")category = relationship("VadminIssueCategory", foreign_keys=category_id, back_populates='issues')title = Column(String(255), index=True, nullable=False, comment="标题")content = Column(Text, comment="内容")view_number = Column(Integer, default=0, comment="查看次数")is_active = Column(Boolean, default=True, comment="是否可见")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)
查询
class IssueCategoryDal(DalBase):def __init__(self, db: AsyncSession):super(IssueCategoryDal, self).__init__(db, models.VadminIssueCategory, schemas.IssueCategorySimpleOut)async def test(self):"""测试关系"""sql = select(self.model)print(sql)queryset = await self.db.execute(sql)datas = queryset.scalars().all()for data in datas:print(data)print(data.issues)
使用外键报错
模型2，subquery
#!/usr/bin/python
# -*- coding: utf-8 -*-
# @version : 1.0
# @Creaet Time : 2022/7/7 13:41
# @File : issue.py
# @IDE : PyCharm
# @desc : 常见问题from sqlalchemy.orm import relationship
from db.db_base import BaseModel
from sqlalchemy import Column, String, Boolean, Integer, ForeignKey, Textclass VadminIssueCategory(BaseModel):__tablename__ = "vadmin_help_issue_category"__table_args__ = ({'comment': '常见问题类别表'})name = Column(String(50), index=True, nullable=False, comment="类别名称")platform = Column(String(8), index=True, nullable=False, comment="展示平台")is_active = Column(Boolean, default=True, comment="是否可见")issues = relationship("VadminIssue", back_populates='category', lazy="subquery")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id, lazy="subquery")class VadminIssue(BaseModel):__tablename__ = "vadmin_help_issue"__table_args__ = ({'comment': '常见问题记录表'})category_id = Column(ForeignKey("vadmin_help_issue_category.id", ondelete='CASCADE'), comment="类别")category = relationship("VadminIssueCategory", foreign_keys=category_id, back_populates='issues')title = Column(String(255), index=True, nullable=False, comment="标题")content = Column(Text, comment="内容")view_number = Column(Integer, default=0, comment="查看次数")is_active = Column(Boolean, default=True, comment="是否可见")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)
查询
class IssueCategoryDal(DalBase):def __init__(self, db: AsyncSession):super(IssueCategoryDal, self).__init__(db, models.VadminIssueCategory, schemas.IssueCategorySimpleOut)async def test(self):"""测试关系"""sql = select(self.model)print(sql)queryset = await self.db.execute(sql)datas = queryset.scalars().all()for data in datas:print(data)print(data.user)print(data.issues)
查询结果：
SELECT vadmin_help_issue_category.id, vadmin_help_issue_category.create_datetime, vadmin_help_issue_category.update_datetime, vadmin_help_issue_category.delete_datetime, vadmin_help_issue_category.name, vadmin_help_issue_category.platform, vadmin_help_issue_category.is_active, vadmin_help_issue_category.user_id
FROM vadmin_help_issue_category
2023-02-17 22:49:31,205 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-02-17 22:49:31,205 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 22:49:31,231 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-02-17 22:49:31,231 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 22:49:31,244 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-02-17 22:49:31,244 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 22:49:31,269 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-17 22:49:31,270 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue_category.id, vadmin_help_issue_category.create_datetime, vadmin_help_issue_category.update_datetime, vadmin_help_issue_category.delete_datetime, vadmin_help_issue_category.name, vadmin_help_issue_category.platform, vadmin_help_issue_category.is_active, vadmin_help_issue_category.user_id
FROM vadmin_help_issue_category
2023-02-17 22:49:31,270 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ()
2023-02-17 22:49:31,287 INFO sqlalchemy.engine.Engine SELECT vadmin_auth_user.id AS vadmin_auth_user_id, vadmin_auth_user.create_datetime AS vadmin_auth_user_create_datetime, vadmin_auth_user.update_datetime AS vadmin_auth_user_update_datetime, vadmin_auth_user.delete_datetime AS vadmin_auth_user_delete_datetime, vadmin_auth_user.avatar AS vadmin_auth_user_avatar, vadmin_auth_user.telephone AS vadmin_auth_user_telephone, vadmin_auth_user.name AS vadmin_auth_user_name, vadmin_auth_user.nickname AS vadmin_auth_user_nickname, vadmin_auth_user.password AS vadmin_auth_user_password, vadmin_auth_user.gender AS vadmin_auth_user_gender, vadmin_auth_user.is_active AS vadmin_auth_user_is_active, vadmin_auth_user.is_cancel AS vadmin_auth_user_is_cancel, vadmin_auth_user.is_reset_password AS vadmin_auth_user_is_reset_password, vadmin_auth_user.last_ip AS vadmin_auth_user_last_ip, vadmin_auth_user.last_login AS vadmin_auth_user_last_login, vadmin_auth_user.is_staff AS vadmin_auth_user_is_staff, vadmin_auth_user.wx_reserve_openid AS vadmin_auth_user_wx_reserve_openid, vadmin_auth_user.is_wx_reserve_openid AS vadmin_auth_user_is_wx_reserve_openid, anon_1.vadmin_help_issue_category_user_id AS anon_1_vadmin_help_issue_category_user_id
FROM (SELECT DISTINCT vadmin_help_issue_category.user_id AS vadmin_help_issue_category_user_id
FROM vadmin_help_issue_category) AS anon_1 INNER JOIN vadmin_auth_user ON vadmin_auth_user.id = anon_1.vadmin_help_issue_category_user_id
2023-02-17 22:49:31,287 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ()
2023-02-17 22:49:31,302 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id, anon_1.vadmin_help_issue_category_id AS anon_1_vadmin_help_issue_category_id
FROM (SELECT vadmin_help_issue_category.id AS vadmin_help_issue_category_id
FROM vadmin_help_issue_category) AS anon_1 INNER JOIN vadmin_help_issue ON anon_1.vadmin_help_issue_category_id = vadmin_help_issue.category_id
2023-02-17 22:49:31,302 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ()
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000021DECA08E50>
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000021DECA09C90>
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000021DECA09E40>
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000021DECA0A650>
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000021DECA430D0>
INFO: 127.0.0.1:57513 - "GET /vadmin/help/issue/categorys/platform/2/ HTTP/1.1" 200 OK
2023-02-17 22:49:31,318 INFO sqlalchemy.engine.Engine COMMIT
是否触发外键，执行时都会去先加载出来
通过内连接查询
模型3，selectin
#!/usr/bin/python
# -*- coding: utf-8 -*-
# @version : 1.0
# @Creaet Time : 2022/7/7 13:41
# @File : issue.py
# @IDE : PyCharm
# @desc : 常见问题from sqlalchemy.orm import relationship
from db.db_base import BaseModel
from sqlalchemy import Column, String, Boolean, Integer, ForeignKey, Textclass VadminIssueCategory(BaseModel):__tablename__ = "vadmin_help_issue_category"__table_args__ = ({'comment': '常见问题类别表'})name = Column(String(50), index=True, nullable=False, comment="类别名称")platform = Column(String(8), index=True, nullable=False, comment="展示平台")is_active = Column(Boolean, default=True, comment="是否可见")issues = relationship("VadminIssue", back_populates='category', lazy="selectin")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id, lazy="selectin")class VadminIssue(BaseModel):__tablename__ = "vadmin_help_issue"__table_args__ = ({'comment': '常见问题记录表'})category_id = Column(ForeignKey("vadmin_help_issue_category.id", ondelete='CASCADE'), comment="类别")category = relationship("VadminIssueCategory", foreign_keys=category_id, back_populates='issues')title = Column(String(255), index=True, nullable=False, comment="标题")content = Column(Text, comment="内容")view_number = Column(Integer, default=0, comment="查看次数")is_active = Column(Boolean, default=True, comment="是否可见")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)
查询
class IssueCategoryDal(DalBase):def __init__(self, db: AsyncSession):super(IssueCategoryDal, self).__init__(db, models.VadminIssueCategory, schemas.IssueCategorySimpleOut)async def test(self):"""测试关系"""sql = select(self.model)print(sql)queryset = await self.db.execute(sql)datas = queryset.scalars().all()for data in datas:print(data)print(data.user)print(data.issues)
查询结果：
SELECT vadmin_help_issue_category.id, vadmin_help_issue_category.create_datetime, vadmin_help_issue_category.update_datetime, vadmin_help_issue_category.delete_datetime, vadmin_help_issue_category.name, vadmin_help_issue_category.platform, vadmin_help_issue_category.is_active, vadmin_help_issue_category.user_id
FROM vadmin_help_issue_category
2023-02-17 23:02:57,891 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-02-17 23:02:57,891 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 23:02:57,925 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-02-17 23:02:57,925 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 23:02:57,942 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-02-17 23:02:57,942 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 23:02:57,976 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-17 23:02:57,977 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue_category.id, vadmin_help_issue_category.create_datetime, vadmin_help_issue_category.update_datetime, vadmin_help_issue_category.delete_datetime, vadmin_help_issue_category.name, vadmin_help_issue_category.platform, vadmin_help_issue_category.is_active, vadmin_help_issue_category.user_id
FROM vadmin_help_issue_category
2023-02-17 23:02:57,977 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ()
2023-02-17 23:02:57,995 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id
FROM vadmin_help_issue
WHERE vadmin_help_issue.category_id IN (%s, %s, %s, %s, %s)
2023-02-17 23:02:57,996 INFO sqlalchemy.engine.Engine [generated in 0.00019s] (1, 2, 3, 4, 5)
2023-02-17 23:02:58,015 INFO sqlalchemy.engine.Engine SELECT vadmin_auth_user.id AS vadmin_auth_user_id, vadmin_auth_user.create_datetime AS vadmin_auth_user_create_datetime, vadmin_auth_user.update_datetime AS vadmin_auth_user_update_datetime, vadmin_auth_user.delete_datetime AS vadmin_auth_user_delete_datetime, vadmin_auth_user.avatar AS vadmin_auth_user_avatar, vadmin_auth_user.telephone AS vadmin_auth_user_telephone, vadmin_auth_user.name AS vadmin_auth_user_name, vadmin_auth_user.nickname AS vadmin_auth_user_nickname, vadmin_auth_user.password AS vadmin_auth_user_password, vadmin_auth_user.gender AS vadmin_auth_user_gender, vadmin_auth_user.is_active AS vadmin_auth_user_is_active, vadmin_auth_user.is_cancel AS vadmin_auth_user_is_cancel, vadmin_auth_user.is_reset_password AS vadmin_auth_user_is_reset_password, vadmin_auth_user.last_ip AS vadmin_auth_user_last_ip, vadmin_auth_user.last_login AS vadmin_auth_user_last_login, vadmin_auth_user.is_staff AS vadmin_auth_user_is_staff, vadmin_auth_user.wx_reserve_openid AS vadmin_auth_user_wx_reserve_openid, vadmin_auth_user.is_wx_reserve_openid AS vadmin_auth_user_is_wx_reserve_openid
FROM vadmin_auth_user
WHERE vadmin_auth_user.id IN (%s)
2023-02-17 23:02:58,015 INFO sqlalchemy.engine.Engine [generated in 0.00019s] (1,)
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000025359A6DA80>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000025359A8EF80>
[<apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C490>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C580>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C5B0>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C940>]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000025359A6DAE0>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000025359A8EF80>
[]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000025359A6DB40>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000025359A8EF80>
[]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000025359A6DBA0>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000025359A8EF80>
[<apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C640>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C700>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C7C0>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8C880>]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000025359A6DC00>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000025359A8EF80>
[<apps.vadmin.help.models.issue.VadminIssue object at 0x0000025359A8CA00>]
INFO: 127.0.0.1:64399 - "GET /vadmin/help/issue/categorys/platform/2/ HTTP/1.1" 200 OK
2023-02-17 23:02:58,034 INFO sqlalchemy.engine.Engine COMMIT
是否触发外键，执行时都会去先加载出来，只是和subquery查询的方式不一样，这个是查询的关联表
模型4，joined
#!/usr/bin/python
# -*- coding: utf-8 -*-
# @version : 1.0
# @Creaet Time : 2022/7/7 13:41
# @File : issue.py
# @IDE : PyCharm
# @desc : 常见问题from sqlalchemy.orm import relationship
from db.db_base import BaseModel
from sqlalchemy import Column, String, Boolean, Integer, ForeignKey, Textclass VadminIssueCategory(BaseModel):__tablename__ = "vadmin_help_issue_category"__table_args__ = ({'comment': '常见问题类别表'})name = Column(String(50), index=True, nullable=False, comment="类别名称")platform = Column(String(8), index=True, nullable=False, comment="展示平台")is_active = Column(Boolean, default=True, comment="是否可见")issues = relationship("VadminIssue", back_populates='category', lazy="joined")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id, lazy="joined")class VadminIssue(BaseModel):__tablename__ = "vadmin_help_issue"__table_args__ = ({'comment': '常见问题记录表'})category_id = Column(ForeignKey("vadmin_help_issue_category.id", ondelete='CASCADE'), comment="类别")category = relationship("VadminIssueCategory", foreign_keys=category_id, back_populates='issues')title = Column(String(255), index=True, nullable=False, comment="标题")content = Column(Text, comment="内容")view_number = Column(Integer, default=0, comment="查看次数")is_active = Column(Boolean, default=True, comment="是否可见")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)
查询
class IssueCategoryDal(DalBase):def __init__(self, db: AsyncSession):super(IssueCategoryDal, self).__init__(db, models.VadminIssueCategory, schemas.IssueCategorySimpleOut)async def test(self):"""测试关系"""sql = select(self.model)print(sql)queryset = await self.db.execute(sql)datas = queryset.scalars().all()for data in datas:print(data)print(data.user)print(data.issues)
查询结果，一条SQL查询
SELECTvadmin_help_issue_category.id,vadmin_help_issue_category.create_datetime,vadmin_help_issue_category.update_datetime,vadmin_help_issue_category.delete_datetime,vadmin_help_issue_category.NAME,vadmin_help_issue_category.platform,vadmin_help_issue_category.is_active,vadmin_help_issue_category.user_id,vadmin_help_issue_1.id AS id_1,vadmin_help_issue_1.create_datetime AS create_datetime_1,vadmin_help_issue_1.update_datetime AS update_datetime_1,vadmin_help_issue_1.delete_datetime AS delete_datetime_1,vadmin_help_issue_1.category_id,vadmin_help_issue_1.title,vadmin_help_issue_1.content,vadmin_help_issue_1.view_number,vadmin_help_issue_1.is_active AS is_active_1,vadmin_help_issue_1.user_id AS user_id_1,vadmin_auth_user_1.id AS id_2,vadmin_auth_user_1.create_datetime AS create_datetime_2,vadmin_auth_user_1.update_datetime AS update_datetime_2,vadmin_auth_user_1.delete_datetime AS delete_datetime_2,vadmin_auth_user_1.avatar,vadmin_auth_user_1.telephone,vadmin_auth_user_1.NAME AS name_1,vadmin_auth_user_1.nickname,vadmin_auth_user_1.PASSWORD,vadmin_auth_user_1.gender,vadmin_auth_user_1.is_active AS is_active_2,vadmin_auth_user_1.is_cancel,vadmin_auth_user_1.is_reset_password,vadmin_auth_user_1.last_ip,vadmin_auth_user_1.last_login,vadmin_auth_user_1.is_staff,vadmin_auth_user_1.wx_reserve_openid,vadmin_auth_user_1.is_wx_reserve_openid
FROMvadmin_help_issue_categoryLEFT OUTER JOIN vadmin_help_issue AS vadmin_help_issue_1 ON vadmin_help_issue_category.id = vadmin_help_issue_1.category_idLEFT OUTER JOIN vadmin_auth_user AS vadmin_auth_user_1 ON vadmin_auth_user_1.id = vadmin_help_issue_category.user_id
是否触发外键，执行时都会去先加载出来
查询方式使用左连接查询
模型5，select
#!/usr/bin/python
# -*- coding: utf-8 -*-
# @version : 1.0
# @Creaet Time : 2022/7/7 13:41
# @File : issue.py
# @IDE : PyCharm
# @desc : 常见问题from sqlalchemy.orm import relationship
from db.db_base import BaseModel
from sqlalchemy import Column, String, Boolean, Integer, ForeignKey, Textclass VadminIssueCategory(BaseModel):__tablename__ = "vadmin_help_issue_category"__table_args__ = ({'comment': '常见问题类别表'})name = Column(String(50), index=True, nullable=False, comment="类别名称")platform = Column(String(8), index=True, nullable=False, comment="展示平台")is_active = Column(Boolean, default=True, comment="是否可见")issues = relationship("VadminIssue", back_populates='category', lazy="select")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id, lazy="select")class VadminIssue(BaseModel):__tablename__ = "vadmin_help_issue"__table_args__ = ({'comment': '常见问题记录表'})category_id = Column(ForeignKey("vadmin_help_issue_category.id", ondelete='CASCADE'), comment="类别")category = relationship("VadminIssueCategory", foreign_keys=category_id, back_populates='issues')title = Column(String(255), index=True, nullable=False, comment="标题")content = Column(Text, comment="内容")view_number = Column(Integer, default=0, comment="查看次数")is_active = Column(Boolean, default=True, comment="是否可见")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)
查询
class IssueCategoryDal(DalBase):def __init__(self, db: AsyncSession):super(IssueCategoryDal, self).__init__(db, models.VadminIssueCategory, schemas.IssueCategorySimpleOut)async def test(self):"""测试关系"""sql = select(self.model)print(sql)queryset = await self.db.execute(sql)datas = queryset.scalars().all()for data in datas:print(data)print(data.user)print(data.issues)
使用外键报错
模型6，immediate
#!/usr/bin/python
# -*- coding: utf-8 -*-
# @version : 1.0
# @Creaet Time : 2022/7/7 13:41
# @File : issue.py
# @IDE : PyCharm
# @desc : 常见问题from sqlalchemy.orm import relationship
from db.db_base import BaseModel
from sqlalchemy import Column, String, Boolean, Integer, ForeignKey, Textclass VadminIssueCategory(BaseModel):__tablename__ = "vadmin_help_issue_category"__table_args__ = ({'comment': '常见问题类别表'})name = Column(String(50), index=True, nullable=False, comment="类别名称")platform = Column(String(8), index=True, nullable=False, comment="展示平台")is_active = Column(Boolean, default=True, comment="是否可见")issues = relationship("VadminIssue", back_populates='category', lazy="immediate")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id, lazy="immediate")class VadminIssue(BaseModel):__tablename__ = "vadmin_help_issue"__table_args__ = ({'comment': '常见问题记录表'})category_id = Column(ForeignKey("vadmin_help_issue_category.id", ondelete='CASCADE'), comment="类别")category = relationship("VadminIssueCategory", foreign_keys=category_id, back_populates='issues')title = Column(String(255), index=True, nullable=False, comment="标题")content = Column(Text, comment="内容")view_number = Column(Integer, default=0, comment="查看次数")is_active = Column(Boolean, default=True, comment="是否可见")user_id = Column(ForeignKey("vadmin_auth_user.id", ondelete='CASCADE'), comment="创建人")user = relationship("VadminUser", foreign_keys=user_id)
查询
class IssueCategoryDal(DalBase):def __init__(self, db: AsyncSession):super(IssueCategoryDal, self).__init__(db, models.VadminIssueCategory, schemas.IssueCategorySimpleOut)async def test(self):"""测试关系"""sql = select(self.model)print(sql)queryset = await self.db.execute(sql)datas = queryset.scalars().all()for data in datas:print(data)print(data.user)print(data.issues)
查询结果
SELECT vadmin_help_issue_category.id, vadmin_help_issue_category.create_datetime, vadmin_help_issue_category.update_datetime, vadmin_help_issue_category.delete_datetime, vadmin_help_issue_category.name, vadmin_help_issue_category.platform, vadmin_help_issue_category.is_active, vadmin_help_issue_category.user_id
FROM vadmin_help_issue_category
2023-02-17 23:26:18,696 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-02-17 23:26:18,696 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 23:26:18,722 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-02-17 23:26:18,722 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 23:26:18,735 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-02-17 23:26:18,735 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-17 23:26:18,760 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-17 23:26:18,761 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue_category.id, vadmin_help_issue_category.create_datetime, vadmin_help_issue_category.update_datetime, vadmin_help_issue_category.delete_datetime, vadmin_help_issue_category.name, vadmin_help_issue_category.platform, vadmin_help_issue_category.is_active, vadmin_help_issue_category.user_id
FROM vadmin_help_issue_category
2023-02-17 23:26:18,761 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ()
2023-02-17 23:26:18,776 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id
FROM vadmin_help_issue
WHERE %s = vadmin_help_issue.category_id
2023-02-17 23:26:18,776 INFO sqlalchemy.engine.Engine [generated in 0.00017s] (1,)
2023-02-17 23:26:18,792 INFO sqlalchemy.engine.Engine SELECT vadmin_auth_user.id AS vadmin_auth_user_id, vadmin_auth_user.create_datetime AS vadmin_auth_user_create_datetime, vadmin_auth_user.update_datetime AS vadmin_auth_user_update_datetime, vadmin_auth_user.delete_datetime AS vadmin_auth_user_delete_datetime, vadmin_auth_user.avatar AS vadmin_auth_user_avatar, vadmin_auth_user.telephone AS vadmin_auth_user_telephone, vadmin_auth_user.name AS vadmin_auth_user_name, vadmin_auth_user.nickname AS vadmin_auth_user_nickname, vadmin_auth_user.password AS vadmin_auth_user_password, vadmin_auth_user.gender AS vadmin_auth_user_gender, vadmin_auth_user.is_active AS vadmin_auth_user_is_active, vadmin_auth_user.is_cancel AS vadmin_auth_user_is_cancel, vadmin_auth_user.is_reset_password AS vadmin_auth_user_is_reset_password, vadmin_auth_user.last_ip AS vadmin_auth_user_last_ip, vadmin_auth_user.last_login AS vadmin_auth_user_last_login, vadmin_auth_user.is_staff AS vadmin_auth_user_is_staff, vadmin_auth_user.wx_reserve_openid AS vadmin_auth_user_wx_reserve_openid, vadmin_auth_user.is_wx_reserve_openid AS vadmin_auth_user_is_wx_reserve_openid
FROM vadmin_auth_user
WHERE vadmin_auth_user.id = %s
2023-02-17 23:26:18,792 INFO sqlalchemy.engine.Engine [generated in 0.00020s] (1,)
2023-02-17 23:26:18,806 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id
FROM vadmin_help_issue
WHERE %s = vadmin_help_issue.category_id
2023-02-17 23:26:18,806 INFO sqlalchemy.engine.Engine [cached since 0.03102s ago] (2,)
2023-02-17 23:26:18,820 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id
FROM vadmin_help_issue
WHERE %s = vadmin_help_issue.category_id
2023-02-17 23:26:18,821 INFO sqlalchemy.engine.Engine [cached since 0.04498s ago] (3,)
2023-02-17 23:26:18,834 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id
FROM vadmin_help_issue
WHERE %s = vadmin_help_issue.category_id
2023-02-17 23:26:18,834 INFO sqlalchemy.engine.Engine [cached since 0.05871s ago] (4,)
2023-02-17 23:26:18,848 INFO sqlalchemy.engine.Engine SELECT vadmin_help_issue.id AS vadmin_help_issue_id, vadmin_help_issue.create_datetime AS vadmin_help_issue_create_datetime, vadmin_help_issue.update_datetime AS vadmin_help_issue_update_datetime, vadmin_help_issue.delete_datetime AS vadmin_help_issue_delete_datetime, vadmin_help_issue.category_id AS vadmin_help_issue_category_id, vadmin_help_issue.title AS vadmin_help_issue_title, vadmin_help_issue.content AS vadmin_help_issue_content, vadmin_help_issue.view_number AS vadmin_help_issue_view_number, vadmin_help_issue.is_active AS vadmin_help_issue_is_active, vadmin_help_issue.user_id AS vadmin_help_issue_user_id
FROM vadmin_help_issue
WHERE %s = vadmin_help_issue.category_id
2023-02-17 23:26:18,848 INFO sqlalchemy.engine.Engine [cached since 0.07242s ago] (5,)
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000020AB36E8DC0>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000020AB36EA620>
[<apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB36EBB50>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB36EBAC0>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB36EBBE0>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB36EBC40>]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000020AB36BD000>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000020AB36EA620>
[]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000020AB36E9C60>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000020AB36EA620>
[]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000020AB36EA8F0>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000020AB36EA620>
[<apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB370E6E0>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB370E650>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB370E770>, <apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB370E7D0>]
<apps.vadmin.help.models.issue.VadminIssueCategory object at 0x0000020AB36EA1D0>
<apps.vadmin.auth.models.user.VadminUser object at 0x0000020AB36EA620>
[<apps.vadmin.help.models.issue.VadminIssue object at 0x0000020AB370EA10>]
INFO: 127.0.0.1:56143 - "GET /vadmin/help/issue/categorys/platform/2/ HTTP/1.1" 200 OK
2023-02-17 23:26:18,864 INFO sqlalchemy.engine.Engine COMMIT
