sqlite和postgres部分索引支持使用sqlalchemy继承参数

yzuktlbb 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(356)

我正在尝试创建一个具有以下唯一组合的表：只能有一个条目具有 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'))

这是我的实际table：

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: Cannot compile Column object until its 'name' is assigned. 有人能帮我实现我想要的功能而不需要 GenericClass ，（我同时使用postgresql和sqlite）？

sql postgresql python sqlite sqlalchemy

来源：https://stackoverflow.com/questions/62907689/sqlite-and-postgres-partial-index-support-with-inherited-parameter-using-sqlalch

1条答案

按热度按时间

hgb9j2n61#

我相信通过将索引移到表定义之外，我成功地克服了这个问题。请参见下面的代码。相关部分突出显示。

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;

赞(0）回复(0）举报 2021-07-26

我来回答

sqlite和postgres部分索引支持使用sqlalchemy继承参数

1条答案

相关问题

热门标签

最新问答