Skip to main content
 首页 » 编程设计

python之与 SQLalchemy 中不同表的关系

2024年10月24日10freeliver54

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

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(...) 

现在我想在历史表中保存更改:

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?

这行得通,但是如果插入 Diff 时出现问题,那么回滚到 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 会自动插入:

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

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

请您参考如下方法:

我想出的解决方案与@van 几乎相同已链接: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)