CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
filimonic
filimonic

Reputation: 4634

How can I map multiple Boolean properties into single SQLAlchemy column of binary type?

How can I map multiple Boolean properties into single SQLAlchemy column?

I have a class kind of flags :

class Thing(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    isEnabled = db.Column(db.Boolean, nullable=False, default=False)
    isAdministrator = db.Column(db.Boolean, nullable=False, default=False)
    isPasswordExpired = db.Column(db.Boolean, nullable=False, default=False)
    isEmailConfirmed = db.Column(db.Boolean, nullable=False, default=False)
    isPhoneConfirmed = db.Column(db.Boolean, nullable=False, default=False)
    # ... many more booleans #

I'd like to store them into single db.Column(db.Integer) or something like that small field.

Could you help me with example of how should I do this?

Please don't try to get logic of those fields or help me to store password expiration mark better - this is an example.

Upvotes: 0

Views: 256

Answers (1)

ljmc
ljmc

Reputation: 5264

You can use an enum.IntFlag to store all those flags in a single Integer field.

from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.orm import declarative_base, Session
from enum import IntFlag, auto

class UserFlag(IntFlag):
    ENABLED = auto()
    ADMINISTRATOR = auto()
    PASSWORD_EXPIRED = auto()
    EMAIL_CONFIRMED = auto()
    PHONE_CONFIRMED = auto()

Base = declarative_base()

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    user_flags = Column(Integer, nullable=False)

engine = create_engine("sqlite://", future=True, echo=True)

Base.metadata.create_all(engine)

session = Session(bind=engine)

admin = User(user_flags = UserFlag.ENABLED | UserFlag.ADMINISTRATOR)
ljmc = User(user_flags = UserFlag.ENABLED | UserFlag.PASSWORD_EXPIRED)

session.add(admin)
session.add(ljmc)
session.commit()

bool(admin.user_flags & UserFlag.ADMINISTRATOR)  # True
bool(ljmc.user_flags & UserFlag.ADMINISTRATOR)  # False

You can also set a helper method to check the flags:

    def is_user_flag_set(self, flag: UserFlag) -> bool:
        return self.user_flags & flag != 0

usage

admin.is_user_flag_set(UserFlag.ADMINISTRATOR)  # True

Upvotes: 1

Related Questions