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.