时间戳

首页 > 解决方案 > sqlalchemy + Pymysql - 生成的查询将标识符连接到列

python - sqlalchemy + Pymysql - 生成的查询将标识符连接到列

问题描述

当尝试通过多对多关系排序或过滤时，生成的查询会将标识符作为辅助表的前缀，而列名本身保持不变。下面是生成的查询。

SELECT roles.id AS roles_id, roles.name AS roles_name, roles.description AS roles_description,
roles.created_at AS roles_created_at, roles.updated_at AS roles_updated_at,
permissions_1.id AS permissions_1_id, permissions_1.name AS permissions_1_name,
permissions_1.slug AS permissions_1_slug, permissions_1.created_at AS permissions_1_created_at,
permissions_1.updated_at AS permissions_1_updated_at FROM roles LEFT OUTER JOIN
(role_permissions AS role_permissions_1 INNER JOIN permissions AS permissions_1 ON
permissions_1.id = role_permissions_1.permission_id) ON roles.id = role_permissions_1.role_id
// This is the part that causes the issue
ORDER BY permissions.slug DESC

通过 sqlalchemy 调用。

db_session.query(Role).join(Role.permissions).order_by(Permission.id.desc())

关联表。

role_permissions_table = Table(
    'role_permissions',
    db.Model.metadata,
    Column(
        'role_id',
        Integer,
        ForeignKey('roles.id'),
        nullable=False
    ),
    Column(
        'permission_id',
        Integer,
        ForeignKey('permissions.id'),
        nullable=False
    ),
    Column(
        'created_at',
        DateTime,
        default=datetime.utcnow
    ),
    Column(
        'updated_at',
        DateTime,
        onupdate=datetime.utcnow
    )
)

好榜样

    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True)
    name = Column(String(120))
    description = Column(String(120))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, onupdate=datetime.utcnow)
    permissions = relationship(
        'Permission',
        secondary='role_permissions',
        lazy='joined'
    )

权限模型

    __tablename__ = 'permissions'


    id = Column(Integer, primary_key=True)
    name = Column(String(120))
    slug = Column(String(150))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, onupdate=datetime.utcnow)

我想知道如何防止标识符被加前缀，或者它们是否是使这项工作的正确方法，一直在阅读 sqlalchemy 文档，在多对多关系过滤/排序上没有找到任何东西。提前致谢。

标签： pythonmysqlsqlalchemypymysql

解决方案

没关系，修复它，我错过了对查询对象的赋值操作，上述操作应该按预期工作，因为我实际上是基于外部参数构建查询。我现在踢自己，向那些浪费时间试图解决这个问题的人道歉，哈哈。

推荐阅读