SQLAlchemy执行原生MySQL SELECT如何自动映射绑定Panel ORM对象

解决方案 #

有两种SQLAlchemy原生支持的方式可以直接实现需求，无需手动逐列赋值，返回的对象会自动绑定到当前session，和普通ORM查询得到的Panel对象完全一致：

方案1：使用from_statement封装原生SQL（兼容性最好，全版本支持） #

这是最常用的方案，直接把原生查询嵌入ORM查询逻辑中，SQLAlchemy会自动完成字段到实体的映射：

from sqlalchemy import text

# 注意不要用f字符串拼接SQL参数，用:参数名的方式占位避免SQL注入
query = text("""
    SELECT PANEL.* FROM PANEL 
    LEFT JOIN PANEL_BOT_DONE ON PANEL.PANE_ID = PANEL_BOT_DONE.PANE_ID AND PANEL_BOT_DONE.BOT_ID = :bot_id 
    ORDER BY PANE_BOT_USES ASC, PANE_GOODSCORE DESC
""")

# 直接返回绑定到session的Panel对象
panel: Panel = self.database.session.query(Panel)\
    .from_statement(query)\
    .params(bot_id=self.id)\
    .first()

方案2：execute时指定ORM实体映射（适合SQLAlchemy 1.4/2.0+版本） #

如果你习惯直接调用execute接口，可以通过参数指定返回实体类型：

from sqlalchemy import text

result = self.database.session.execute(
    text("""
        SELECT PANEL.* FROM PANEL 
        LEFT JOIN PANEL_BOT_DONE ON PANEL.PANE_ID = PANEL_BOT_DONE.PANE_ID AND PANEL_BOT_DONE.BOT_ID = :bot_id 
        ORDER BY PANE_BOT_USES ASC, PANE_GOODSCORE DESC
    """),
    {"bot_id": self.id},
    execution_options={"orm_load_entities": [Panel]}
)

# 直接取首个实体对象
panel = result.scalars().first()

注意：两种方案都要求原生SQL的查询结果必须包含Panel表的全部主键字段，否则SQLAlchemy无法完成实体映射和session绑定。

内容的提问来源于stack exchange，提问作者Joseph