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

最新推荐文章于 2024-02-01 19:35:30 发布
最新推荐文章于 2024-02-01 19:35:30 发布
阅读量426 收藏
点赞数
分类专栏： python 文章标签： chatgpt sqlalchemy python 关系加载
原文链接：https://www.osgeo.cn/sqlalchemy/orm/loading_relationships.html
版权

之前一直对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
ktianc
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Sqlalchemy 组合查询(union和union_all)
weixin_40123451的博客
11-25 2472
组合查询 # 组合 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() #数据重复，只留一条 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filt
SQLAlchemy相关（连接池、在eventlet中使用、backref/back_populates、lazy/eager loading）
u012324798的博客
01-11 1799
SQLAlchemy相关连接池在eventlet中使用SQLAlchemy创建表关系完整例子参考文档 连接池 在使用create_engine(url)创建引擎时，SQLAlchemy会默认绑定一个容量（pool_size）为5，上溢（max_overflow）为10的连接池（QueuePool对象） from sqlalchemy import create_engine engine =...
sqlalchemy中 .join() 和 .options() 方法的使用
ktianc的博客
03-22 870
在SQLAlchemy中，.join()和.options()都是用于指定查询的关联对象的方法，但是它们的作用和用法略有不同。
Flask中使用flask-sqlalchemy时急切加载(预加载，类似django中的select_related和prefetch_related方法)减少sql对其优化
qq_38977315的博客
09-02 400
回想一下，我们之前演示了延迟加载操作，当我们访问 a 的User.addresses集合User并发出 SQL 时。如果你想减少查询的数量（在很多情况下是显着的），我们可以对查询操作应用一个急切的负载。这三个通常通过称为查询选项的函数调用，这些函数Query通过该方法为我们希望如何加载各种属性提供额外的说明Query.options()。加载一组对象及其相关集合的一个不错的选择是selectinload()选项，它发出第二个 SELECT 语句，完全加载与刚刚加载的结果关联的集合。
s1C#语言和SQL Server
weixin_47568202的博客
12-15 149
C#语言和SQL Server dyg第一个c#程序 2. C#语法快速热身 3.使用属性输入mybank 4. 深入C#的String类 5. 程序数据集散地：数据库 6. 用表组织数据 7. 用SQL语句操作数据 8. 数据查询基础 9. 模糊查询和聚合函数 10. 连接查询和分组查询 11. 使用ADO.NET访问数据库 12. ADO.NET查询和操作数据 ...
sqla_yaml_fixtures:加载用于SQLAlchemy的YAML数据固定装置
05-17
该软件包允许您在YAML中定义一些数据并将其加载到数据库中。 yaml数据应对应于SQLAlchemy声明性映射器。 例子： - User : - __key__ : joey username : joey email : joey@example.com profile : name : ...
研究Python的ORM框架中的SQLAlchemy库的映射关系
12-23
这种联系在数据库理论中是典型的1-N (一对多)关系，用户表某一用户对应N条电子邮件记录。 之前我们的用户表称为users，现在我们再建立一张被称为addresses的表用于存储电子邮件地址，通过Declarative系统，我们可以...
Python ORM框架SQLAlchemy学习笔记之关系映射实例
01-20
昨天简单介绍了SQLAlchemy的使用，但是没有能够涉及其最精彩的ORM部分，今天我将简单说明一下，当然主要还是讲解官方文档的内容，由于是学习笔记，有可能存在精简或者自己理解的部分，不做权威依据。 当我们开始使用...
基于Flask 3.0.2的Web学习项目源码，整合SQLAlchemy与前端技术
最新发布
03-25
项目简介：基于最新的Flask 3.0.2版本，本项目是一个集成了SQLAlchemy的Web学习平台，旨在帮助理解Flask框架与数据库操作。项目采用Python作为主要编程语言，并融合了CSS、HTML、JavaScript和TypeScript等前端技术。...
Python的Flask框架中使用Flask-SQLAlchemy管理数据库的教程
01-20
SQLAlchemy是一个强大的关系数据库框架，支持一些数据库后端。提供高级的ORM和底层访问数据库的本地SQL功能。 和其他扩展一样，通过pip安装Flask-SQLAlchemy： (venv) $ pip install flask-sqlalchemy 在Flask-...
geoalchemy2:SQLAlchemy的地理空间扩展
05-02
地质炼金术2 GeoAlchemy 2是用于处理空间数据库的Python工具箱。 它基于华丽的 。 有关文档，请阅读文档： : 。
How to Use selectinload in SQLAlchemy
yuguo_im的博客
02-01 394
#SQLAlchemy #selectinload
sqlalchemy的异步连表查询用例
qq_42713497的博客
01-28 928
一对多和多对一完整demo 直接可以运行
SQLAlchemy 教程 —— 基础入门篇
weixin_33713707的博客
02-11 1000
SQLAlchemy 教程 —— 基础入门篇 一、课程简介 1.1 实验内容 本课程带领大家使用 SQLAlchemy 连接 MySQL 数据库，创建一个博客应用所需要的数据表，并介绍了使用 SQLAlchemy 进行简单了 CURD 操作及使用 Faker 生成测试数据。 1.2课程知识点 学会用 SQLALchemy 连接数据库(MySQL, SQLite, PostgreSQL), 创建数...
（四）SQLAlchemy相关对象和装载机策略
一夜奈何梁山
08-25 451
目录一： 数据准备：二： 持久化与加载关系2.1： relationship的作用：2.2： 加载关系：2.3： N+1问题：三： 装载机策略3.1： selectinload装载机：3.2： joinedload装载机3.3： raiseload装载机： 一： 数据准备： 创建两个表： user和address from sqlalchemy.orm import registry, relationship from sqlalchemy import Column, Integer, Foreig
sqlalchemy学习笔记
chise_
03-11 369
注意 入门 连接数据库 连接SQLite数据库（内存模式） from sqlalchemy import create_engine engine=create_engine('sqlite:///:memory:', echo=True) echo:启用它，我们将看到所有生成的SQL engine:一个实例Engine，标识数据库核心接口，主要用于链接数据库。 声明一个映射 声明一个映射，用于...
在python中用sql创建表_Python SQLAlchemy 高级教程——使用SQLAlchemy创建/反射表结构、增删改数据...
weixin_39615219的博客
12-06 703
建表连接一个数据库>>> from sqlalchemy import create_engine>>> engine = create_engine('sqlite:///:memory:', echo=True)这个 Engine 是任何SQLAlchemy应用程序的起点。Dialect 定义特定数据库的DBAPI行为，能为不同的数据库转换SQL语法。Poo...
python 数据库查询序列化_如何通过带有联接的数据库查询生成嵌套的JSON？使用Python / SQLAlchemy...
weixin_33186395的博客
02-09 159
小编典典我强烈建议您不要将序列化直接烘焙到模型中，因为您最终将有两个服务请求相同的数据，但是以不同的方式进行序列化(例如，包括更少或更多的嵌套关系以提高性能)，您要么最终选择要么(1)测试套件会遗漏许多错误，除非您要逐字检查每个字段，或者(2)序列化的数据量超过所需数量，并且随着应用程序的复杂性扩展，您将遇到性能问题。使用marshmallow-sqlalchemy，您需要为要序列化的每个模型定义...
sqlalchemy中文文档
02-28
它提供了一种使用SQL语言与数据库进行交互的方式，并且可以将数据库中的表映射为Python类，使得开发者可以使用面向对象的方式来操作数据库。 关于SQLAlchemy的中文文档，你可以在CSDN开发的"C知道"中找到相关的内容...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值