与SQLalchemy中不同表的关系 [英] Relationship to different tables in SQLalchemy

问题描述

我有多个表.所有表都有多个列，这些列在逻辑上无法存储在一个合并表中.

I have multiple tables. All tables have multiple columns that could not logically be stored in one merged table.

class Foo(Model):
    foo_id = Column(Integer(unsigned=True), primary_key=True, nullable=False)
    foo_data = Column(...)

class Bar(Model):
    bar_id = Column(Integer(unsigned=True), primary_key=True, nullable=False)
    bar_info = Column(...)

class Baz(Model):
    baz_id = Column(Integer(unsigned=True), primary_key=True, nullable=False)
    baz_content = Column(...)

现在，我要将更改保存在历史记录表中:

Now I want to save changes in a history table:

class Diff(Model):
    diff_id = Column(...)
    foreign_id = Column(Integer(unsigned=True), index=True, nullable=False)
    foreign_table = Column(Char(16, collation='ascii_bin'), index=True, nullable=False)

class SingleDiff(Model):
     ...
     diff = relationship(Diff, backref=backref('changes', uselist=True, ...))

现在我的问题是:当插入新的 Foo ， Bar 时，如何在同一提交中填充 Diff.foreign_id 或 Baz ?

Now my problem is: How can I populate Diff.foreign_id in the same commit when inserting a new Foo, Bar, or Baz?

这可行，但是如果插入 Diff 会出现问题，那么回滚对 foo 的更改为时已晚:

This works, but if there should be a problem to insert the Diff then it is too late to rollback the changes to foo:

foo = Foo(foo_data='TODO: changes table names')
try:
    session.add(foo)
    session.commit()  # first commit
except ...:
    ....
else:
    try:
         diff = Diff(changes=[...])
         diff.foreign_id = foo.foo_id
         diff.foerein_table = 'foo'
         session.add(diff)
         session.commit()  # second commit
     except ...:
         raise Exception('Cannot rollback commit #1 anymore :-(')

对于正常关系，将自动插入ID:

For normal relationships the id is inserted automagically:

class FooDiff(Model):
    diff_id = Column(...)
    foo_id = Column(Integer(unsigned=True), ForeignKey(...), ...)
    foo = relationship(Foo)

但是我没有 Diff.foo ，因为 Diff.foreign_id 可以指向许多不同的表.

But I don't have Diff.foo, because Diff.foreign_id could point to many different tables.

推荐答案

我想出的解决方案与@van 已链接: ORM示例:通用关联.

The solution I came up with is almost the same as that what @van has linked: ORM Examples: Generic Associations.

def _foreign_id_rel(Cls):
    return relationship(
        Cls,
        uselist=False,
        lazy=False,  # This is just my use case, not needed
        primaryjoin=lambda: and_(
            Diff.foreign_table == Cls.__tablename__,
            foreign(Diff.foreign_id) == Cls.id,
        ),
        backref=backref(
            'diffs',
            uselist=True,
            lazy=True,  # This is just my use case, not needed
            primaryjoin=lambda: and_(
                Diff.foreign_table == Cls.__tablename__,
                foreign(Diff.foreign_id) == Cls.id,
            ),
        ),
    )


class Diff(Model):
    ...

    foo = _foreign_id_rel(Foo)
    bar = _foreign_id_rel(Bar)
    baz = _foreign_id_rel(Baz)

    @property
    def foreign(self):
        if self.foreign_table:
            return getattr(self, self.foreign_table)

    @foreign.setter
    def foreign(self, value):
        if value is None:
            self.foreign_table = None
            self.foreign_id = None
        else:
            tbl_name = value.__tablename__
            self.foreign_table = tbl_name
            setattr(self, tbl_name, value)

这篇关于与SQLalchemy中不同表的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！