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