python - SQLAlchemy 映射将表的列连接到一个对象

Question

我有三个表:UserTypeMapper、User 和 SystemAdmin。在我的 get_user 方法中，根据 UserTypeMapper.is_admin 行，我然后查询 User 或 SystemAdmin 表。 user_id 行与 User 和 SystemAdmin 表中的主键 id 相关联。

class UserTypeMapper(Base):
    __tablename__ = 'user_type_mapper'

    id = Column(BigInteger, primary_key=True)
    is_admin = Column(Boolean, default=False)
    user_id = Column(BigInteger, nullable=False)

class SystemAdmin(Base):
    __tablename__ = 'system_admin'

    id = Column(BigInteger, primary_key=True)
    name = Column(Unicode)
    email = Column(Unicode)

class User(Base):
    __tablename__ = 'user'

    id = Column(BigInteger, primary_key=True)
    name = Column(Unicode)
    email = Column(Unicode)

我希望能够从一个查询中获取任何用户(系统管理员或普通用户)，因此我根据 User 或 SystemAdmin 进行连接，具体取决于is_admin 行。例如:

DBSession.query(UserTypeMapper, SystemAdmin).join(SystemAdmin, UserTypeMapper.user_id==SystemAdmin.id).first()

和

DBSession.query(UserTypeMapper, User).join(User, UserTypeMapper.user_id==User.id).first()

这很好用；但是，我想像这样访问这些:

>>> my_admin_obj.is_admin
True
>>> my_admin_obj.name
Bob Smith

对比

>>> my_user_obj.is_admin
False
>>> my_user_obj.name
Bob Stevens

目前，我必须指定:my_user_obj.UserTypeMapper.is_admin 和 my_user_obj.User.name。根据我一直在阅读的内容，我需要映射表，这样我就不需要指定属性属于哪个表。我的问题是我不明白如何指定它，因为我有两个潜在的表，例如 name 属性可能来自。

这是我指的例子:Mapping a Class against Multiple Tables

我怎样才能做到这一点？谢谢。

Answer 1

您已经发现为什么“双重用途外键”是一个 antipattern .

您还没有完全指出与此相关的问题；无法使用外键约束来强制数据处于有效状态。您希望确保 UserTypeMapper 中的每一行都恰好有一个 something，但“something”不是任何一个表。正式地，你想要对

的功能依赖

user_type_mapper → (system_admin× 1) ∪ (user× 0)

但是大多数 sql 数据库不允许你写一个外键约束来表达它。

它看起来很复杂，因为它很复杂。

相反，让我们考虑一下我们真正想说的话； “每个 system_admin 都应该是一个 user；或者

system_admin → user

在sql中，会这样写:

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    email VARCHAR
);

CREATE TABLE system_admin (
    user_id INTEGER PRIMARY KEY REFERENCES user(id)
);

或者，在 sqlalchemy 声明式风格中

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

class SystemAdmin(Base):
    __tablename__ = 'system_admin'
    user_id = Column(ForeignKey(User.id), primary_key=True)

这个模式允许我们问什么样的问题？

• “有没有叫‘john doe’的系统管理员”？

>>> print session.query(User).join(SystemAdmin).filter(User.name == 'john doe').exists()

EXISTS (SELECT 1 
FROM "user" JOIN system_admin ON "user".id = system_admin.user_id 
WHERE "user".name = :name_1)

• “有多少用户？有多少系统管理员？”

>>> print session.query(func.count(User.id), func.count(SystemAdmin.user_id)).outerjoin(SystemAdmin)

SELECT count("user".id) AS count_1, count(system_admin.user_id) AS count_2 
FROM "user" LEFT OUTER JOIN system_admin ON "user".id = system_admin.user_id

---

我希望您能明白为什么上述设计优于您在问题中描述的设计；但在你别无选择的情况下(只有在这种情况下，如果你仍然觉得你得到的更好，请改进你的问题)，你仍然可以填充该数据通过提供到表的替代映射，将其转换为单个 python 对象，这将很难使用；特别是遵循第一个等式中的粗略结构的一个。

我们需要提及 UserTypeMapper 两次，联合的每一侧各一次，为此，我们需要提供别名。

>>> from sqlalchemy.orm import aliased
>>> utm1 = aliased(UserTypeMapper)
>>> utm2 = aliased(UserTypeMapper)

对于联合主体，将每个别名连接到适当的表:由于 SystemAdmin 和 User 具有相同顺序的相同列，我们不需要描述它们详细说明，但如果它们完全不同，我们需要通过明确提及每一列来使它们“联合兼容”；这留作练习。

>>> utm_sa = Query([utm1, SystemAdmin]).join(SystemAdmin, (utm1.user_id == SystemAdmin.id) & (utm1.is_admin == True))
>>> utm_u = Query([utm2, User]).join(User, (utm2.user_id == User.id) & (utm2.is_admin == False))

然后我们把它们连在一起...

>>> print utm_sa.union(utm_u)
SELECT anon_1.user_type_mapper_1_id AS anon_1_user_type_mapper_1_id, anon_1.user_type_mapper_1_is_admin AS anon_1_user_type_mapper_1_is_admin, anon_1.user_type_mapper_1_user_id AS anon_1_user_type_mapper_1_user_id, anon_1.system_admin_id AS anon_1_system_admin_id, anon_1.system_admin_name AS anon_1_system_admin_name, anon_1.system_admin_email AS anon_1_system_admin_email 
FROM (SELECT user_type_mapper_1.id AS user_type_mapper_1_id, user_type_mapper_1.is_admin AS user_type_mapper_1_is_admin, user_type_mapper_1.user_id AS user_type_mapper_1_user_id, system_admin.id AS system_admin_id, system_admin.name AS system_admin_name, system_admin.email AS system_admin_email 
FROM user_type_mapper AS user_type_mapper_1 JOIN system_admin ON user_type_mapper_1.user_id = system_admin.id AND user_type_mapper_1.is_admin = 1 UNION SELECT user_type_mapper_2.id AS user_type_mapper_2_id, user_type_mapper_2.is_admin AS user_type_mapper_2_is_admin, user_type_mapper_2.user_id AS user_type_mapper_2_user_id, "user".id AS user_id, "user".name AS user_name, "user".email AS user_email 
FROM user_type_mapper AS user_type_mapper_2 JOIN "user" ON user_type_mapper_2.user_id = "user".id AND user_type_mapper_2.is_admin = 0) AS anon_1

虽然理论上可以将这一切包装到一个看起来有点像标准 sqlalchemy orm 东西的 python 类中，但我肯定不会那样做。使用非表映射，尤其是当它们不仅仅是简单的连接(这是一个联合)时，需要大量工作才能获得零返回。