database - SQLAlchemy - 将类映射到多个表，其中一个是只读的

Question

我有一个 foobar 概念，它由“foobar”和“foobar_data_cache”表表示，是数据库，“Foobar”类是 python。“foobar”表代表关于概念的一些数据，“foobar_data_cache”表代表关于概念的一些其他数据，这些数据源自数据库中的许多其他信息，并由数据库触发器计算得出。为了保持一致性，INSERT、UPDATE 和 DELETE 权限已从“foobar_data_cache”表中撤销。

使用 SQLAlchemy，我想通过连接将“Foobar”类映射到“foobar”和“foobar_data_cache”这两个表。没有理由使用另一个类来表示“foobar_data_cache”表中的数据并在这两个类之间建立关系，因为两个表中的数据密切相关。确实，从数据库的角度来看，两个表之间是一对一的关系，保证:
- 'foobar_data_cache' 的主键也是引用 'foobar' 主键的外键
- 以及一个触发器，用于检查“foobar”中的每一行是否在“foobar_data_cache”中有对应的行

我的问题是，当我尝试使用 SQLAlchemy ORM 保留一个新的 Foobar 对象时，它会尝试为“foobar_data_cache”表插入一些行，我想阻止这种情况。

那么，是否可以以将“foobar_data_cache”表视为只读的方式配置 SQLAlchemy？如果是，怎么做？

这是解释我的问题的代码:

from sqlalchemy import (
    Table,
    Column,
    Integer,
    String,
    ForeignKeyConstraint,
    join,
    create_engine,
    )
from sqlalchemy.orm import (
    column_property,
    sessionmaker,
    )
from sqlalchemy.schema import (
    FetchedValue,
    )
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

_foobar_table = Table('foobar', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('some_data', String),
    )

_foobar_data_cache_table = Table('foobar_data_cache', Base.metadata,
        Column('foobar_id', Integer, primary_key=True),
        Column('computed_data', String, server_default=FetchedValue()),
        ForeignKeyConstraint(['foobar_id'], ['foobar.id']),
    )

class Foobar(Base):
    __table__ = _foobar_table.join(_foobar_data_cache_table)
    _id = column_property(_foobar_table.c.id, _foobar_data_cache_table.c.foobar_id)

    def __init__(self, some_data):
        self.some_data = some_data

if __name__ == '__main__':
    engine = create_engine('postgresql://tester@localhost:5432/mytestdb')
    Session = sessionmaker(bind=engine)
    session = Session()

    my_foobar = Foobar('Dummy data')
    session.add(my_foobar)
    session.commit()

这是创建两个表的 SQL 命令:

CREATE TABLE foobar (
  id int NOT NULL DEFAULT -2147483648,
  some_data varchar NOT NULL,
  CONSTRAINT pk_foobar PRIMARY KEY (id)
);

CREATE TABLE foobar_data_cache (
  foobar_id int NOT NULL,
  computed_data varchar NOT NULL,
  CONSTRAINT pk_foobar_data_cache PRIMARY KEY (foobar_id),
  CONSTRAINT fk_foobar_data_cache_foobar_1 FOREIGN KEY (foobar_id)
    REFERENCES foobar (id) MATCH FULL
    ON UPDATE CASCADE ON DELETE CASCADE
);  

注意:
考虑到它们之间是一对一的关系，有些人可能想知道为什么我将数据拆分在两个不同的表中。通过在计算列上使用单个表和 FetchedValue 构造(请参阅 How to persist only a subset of the mapped columns on SQLAlchemy?)，可以轻松解决该问题。好吧，这有点复杂，但我会尽力解释。首先，上面没有解释的其他事情是:
- 我使用 PostgreSQL 8.4，除其他外，它不能有可延迟的 UNIQUE 约束
- 我的所有专栏都不接受 NULL 值
- “foobar_data_cache”中的某些列具有唯一约束(不可延迟)
- 计算“foobar_data_cache”中数据的触发器被推迟到事务结束。事实上，这是因为它从其他表中获取信息，由于外键约束，这些信息只能在插入“foobar”之后插入。

就是说，在使用单个表的情况下，这意味着由于 NOT-NULL 约束，我将不得不为我的计算列使用临时虚拟值。我的触发器最终会在交易结束时覆盖它。问题在于并发性。事实上，新事务 Tx 试图在另一个事务 T1 正在执行时插入新的“foobar”，将会失败，因为唯一列的默认虚拟值已经存在于与执行事务 T1 对应的行中。我可以为具有唯一约束的列生成随机虚拟值，但我不喜欢这种做事方式。

Answer 1

如果没有可用的表主键，ORM 将跳过任何 INSERT/UPDATE/DELETE。在这种情况下，exclude_properties 将实现此目的，还要注意复合“PK”列已消失，因为我们不关心“foobar_id”的值:

from sqlalchemy import (
    Table,
    Column,
    Integer,
    String,
    ForeignKeyConstraint,
    join,
    create_engine,
    )
from sqlalchemy.orm import (
    column_property,
    sessionmaker,
    )
from sqlalchemy.schema import (
    FetchedValue,
    )
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

_foobar_table = Table('foobar', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('some_data', String),
    )

_foobar_data_cache_table = Table('foobar_data_cache', Base.metadata,
        Column('foobar_id', Integer, primary_key=True),
        Column('computed_data', String, server_default=FetchedValue()),
        ForeignKeyConstraint(['foobar_id'], ['foobar.id']),
    )

class Foobar(Base):
    __table__ = _foobar_table.join(_foobar_data_cache_table)
    _id = _foobar_table.c.id

    def __init__(self, some_data):
        self.some_data = some_data

    __mapper_args__ = {"exclude_properties": [_foobar_data_cache_table.c.foobar_id]}

if __name__ == '__main__':
    engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()

    my_foobar = Foobar('Dummy data')
    session.add(my_foobar)

    # simulate your trigger...
    session.flush()
    assert session.scalar(_foobar_data_cache_table.count()) == 0
    session.execute(
        _foobar_data_cache_table.insert(),
        params=dict(
                foobar_id=my_foobar._id,
                computed_data="some computed data"
                )
    )

    session.commit()

    obj = session.query(Foobar).first()
    assert obj.computed_data == "some computed data"

就是说，如果您使用传统的 relationship() 作为 _foobar 和 之间的链接，整个映射将方式更简单>_foobar_data_cache_table，而不是组合成映射的 join()。