SQLite and Postgres Partial index support with inherited parameter using SQLAlchemy

user_5273594 0
Viewed 236

I'm trying to have a table that has the following unique combination: There could only be one entry with an account_id that's is set to active, but multiple entries of that same account_id in which active is set to False, meaning this is a possibility:

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

But this cannot:

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

I'm tying to use partial index to get this functionality but the caveat is that both account_id and active are inherited from a generic class. The classes look as follows:

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'))

Here is my actual 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.')

But I am getting the following error: sqlalchemy.exc.CompileError: Cannot compile Column object until its 'name' is assigned.

Can anyone help me achieve the functionality I would like to have without getting of the GenericClass, (I’m using both postgresql and sqlite) ?

sqlalchemy postgresql sqlite sql python
1 Answers

I believe I managed to overcome the issue by moving the index outside the table definition. See the code below. The relevant piece is highlighted.

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)

The resulting schema, for sqlite, is:

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;
user_11055600
Related