chatGPT说明SQLAlchemy中的关系加载技术，joinedload加载方式，并与selectinload的不同之处

转载 于 2023-02-20 11:32:52 发布 · 1.7k 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：https://www.osgeo.cn/sqlalchemy/orm/loading_relationships.html
文章标签：

#chatgpt #sqlalchemy #python #关系加载

文章介绍了SQLAlchemy中不同关系加载策略的使用，包括默认的lazy加载，以及subquery、selectin、joined等加载方式。通过示例展示了这些策略如何影响查询语句和性能，特别是在处理关联数据时的差异。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

之前一直对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, Text


class 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, Text


class 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, Text


class 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, Text


class 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查询

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,
	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 
FROM
	vadmin_help_issue_category
	LEFT OUTER JOIN vadmin_help_issue AS vadmin_help_issue_1 ON vadmin_help_issue_category.id = vadmin_help_issue_1.category_id
	LEFT 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, Text


class 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, Text


class 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
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值