python - 使用 SQLAlchemy 继承参数的 SQLite 和 Postgres 部分索引支持

Question

我正在尝试创建一个具有以下独特组合的表格:只能有一个条目的 account_id 设置为 active，但同一个 account_id 的多个条目其中 active 设置为 False，这意味着这是一种可能性:

id |  account_id  | active | timestamp
0  |  12          |  False | 2020-07-15 04:10:48.380781
1  |  12          |  True  | 2020-07-15 04:10:48.380781
2  |  12          |  False |2020-07-15 04:10:48.380781

但这不能:

id |  account_id  | active | timestamp
0  |  12          |  False |2020-07-15 04:10:48.380781
1  |  12          |  True  |2020-07-15 04:10:48.380781
2  |  12          |  True  |2020-07-15 04:10:48.380781

我试图使用部分索引来获得此功能，但需要注意的是 account_id 和 active 都是从通用类继承的。这些类如下所示:

class GenericClass:
    id = Column(Integer, primary_key=True)
    active = Column(Boolean, nullable=False,
                    doc='Whether this is active or not.'
    @declared_attr        # declared_attr decorator makes it like classmethod
    def account_id(cls):
        return Column(Integer, ForeignKey('account.account_id'))

这是我的实际表格:

class AdvancedTable(Some.Model, GenericClass):
    __versioned__ = {}
    __table_args__ = ( 
        Index('active_accid_index', 'account_id',
              unique=True,
              sqlite_where= GenericClass.active,
              postgresql_where= GenericClass.active),
        )
    timestamp = Column(
        DateTime, nullable=True, doc='DateTime this info was activated.')

但是我收到以下错误:sqlalchemy.exc.CompileError:在分配“名称”之前无法编译 Column 对象。

谁能帮我实现我想要的功能而无需获取 GenericClass，(我同时使用 postgresql 和 sqlite)？

Answer 1

我相信我通过将索引移到表定义之外设法解决了这个问题。请参阅下面的代码。相关部分已突出显示。

Base = declarative_base()

class GenericClass(object):
    @declared_attr
    def account_id(cls):
        return Column('account_id', ForeignKey('account.id'))

    @declared_attr
    def account(cls):
        return relationship("Account")

    active = Column(Boolean, nullable=False, doc='Whether this is active or not.') 

class Account(Base):
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    name = String(length=32)

class AdvancedTable(GenericClass, Base):
    __versioned__ = {}
    __tablename__ = "advanced"
    id = Column(Integer, primary_key=True)

    timestamp = Column(
        DateTime, nullable=True, doc='DateTime this info was activated.')

my_index = Index('active_accid_index', AdvancedTable.account_id, unique = True, 
                 sqlite_where = AdvancedTable.active )
# ===== This is the key part =====

engine = create_engine('sqlite:///advanced.db')
Base.metadata.create_all(engine)

对于 sqlite，生成的模式是:

CREATE TABLE account (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id)
);
CREATE TABLE advanced (
    active BOOLEAN NOT NULL, 
    id INTEGER NOT NULL, 
    timestamp DATETIME, 
    account_id INTEGER, 
    PRIMARY KEY (id), 
    CHECK (active IN (0, 1)), 
    FOREIGN KEY(account_id) REFERENCES account (id)
);
CREATE UNIQUE INDEX active_accid_index ON advanced (account_id) WHERE active;