[Flask][SQLAlchemy] Implementing a database column for a Multiselect: How to use ArrayOfEnum with Enum python classes in flask-sqlalchemy?

Viewed 57

I did a bunch of research but I did not get through a sophisticated example helping me out to use ArrayOfEnum() to store a class-specific Enum in a PostgreSQL database. I do use Flask, Flask-SQLAlchemy, as well SQLAlchemy.

My example originates by a WTForm using a MultiSelect which I want to store in the database:

genres = SelectMultipleField(
        'genres', validators=[DataRequired()], choices=[
            ('Alternative', 'Alternative'),
            ('Blues', 'Blues'),
            ...
            ])

I would like to define an enum in python like:

class Genres(enum.Enum):
  alternative ='Alternative'
  blues ='Blues'
  classical = 'Classical'

And use it as an authorized data type in the model, like this:

class Artist(db.Model):
    __tablename__ = 'Artist'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    city = db.Column(db.String(120))
    genres = db.Column(pg.ArrayOfEnum(Genres))

My general setup is: requirements.txt:

alembic==1.7.7
Babel==2.9.0
click==8.1.2
colorama==0.4.4
Flask==2.1.1
Flask-Migrate==3.1.0
Flask-Moment==0.11.0
Flask-SQLAlchemy==2.4.4
Flask-WTF==0.14.3
greenlet==1.1.2
importlib-metadata==4.11.3
itsdangerous==2.1.2
Jinja2==3.0.3
Mako==1.2.0
MarkupSafe==2.1.1
postgres==4.0
psycopg2-binary==2.9.3
psycopg2-pool==1.1
python-dateutil==2.6.0
pytz==2022.1
six==1.16.0
SQLAlchemy==1.4.35
Werkzeug==2.0.0
WTForms==3.0.1
zipp==3.8.0

In this question I do specifically ask for the solution using the ENUM with ARRAY, but I'm also open to use other solutions that might fit.

0 Answers
Related