SQLAlchemy Enum type field with integer value

Question

There is an events model in which I want to add a memebers_count field to indicate the number of participants. The user will make a single selection from the select form. I am using Enum type for this. The model looks like this

class MembersQuantity(enum.Enum):
    two = 2
    three = 3
    four = 4
    five = 5
    six = 6
    ...
    nineteen = 19
    twenty = 20
    thirty = 30
    forty = 40
    fifty = 50
    unlimited = 1000

events = Table(
    "events",
    metadata,
    Column("id", Integer(), primary_key=True),
    ...
    Column("members_count", Enum(MembersQuantity, values_callable=lambda obj: [e.value for e in obj]),
           nullable=False,
           default=MembersQuantity.two.value,
           server_default=MembersQuantity.two.value),
    ....

An error appears in the console when this entry is made

  File "/home/jekson/Projects/own/wplay/./models/events.py", line 64, in <module>
    Column("members_count", Enum(MembersQuantity, values_callable=lambda obj: [e.value for e in obj]),
  File "<string>", line 2, in __init__
  File "/home/jekson/virtualenvs/wplay/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 139, in warned
    return fn(*args, **kwargs)
  File "/home/jekson/virtualenvs/wplay/lib/python3.9/site-packages/sqlalchemy/sql/sqltypes.py", line 1383, in __init__
    self._enum_init(enums, kw)
  File "/home/jekson/virtualenvs/wplay/lib/python3.9/site-packages/sqlalchemy/sql/sqltypes.py", line 1425, in _enum_init
    length = max(len(x) for x in self.enums)
  File "/home/jekson/virtualenvs/wplay/lib/python3.9/site-packages/sqlalchemy/sql/sqltypes.py", line 1425, in <genexpr>
    length = max(len(x) for x in self.enums)
TypeError: object of type 'int' has no len()

I assumed the problem was that [e.value for e in obj] should be str instead of int and replaced it with [str(e.value) for e in obj] but got another error

sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be of the type '<class 'str'>' or '<class 'sqlalchemy.sql.elements.ClauseElement'>' or '<class 'sqlalchemy.sql.elements.TextClause'>', got '<class 'int'>'

What's wrong?

jorzel · Accepted Answer · 2022-02-08T19:59:30.450

The question is what do you want to persist? two or 2 or <MembersQuantity.two: 2>?

SQLAlchemy docs states:

Above, the string names of each element, e.g. “one”, “two”, “three”, are persisted to the database; 
the values of the Python Enum, here indicated as integers, are not used;
the value of each enum can therefore be any kind of Python object whether or not it is persistable.

So if you have integer values of enum, you probably should have something like this:

    Column(
       "members_count", 
       Enum(MembersQuantity),
       nullable=False,
       default=MembersQuantity.two.name,
       server_default=MembersQuantity.two.name
    )

The name attribute is two, while value attribute is 2 in your case. But you persist only left-hand side strings. You should use values_callable only if you want to persist values instead of names, but docs states also: value_callables - a callable which will be passed the PEP-435 compliant enumerated type, which should then return a list of string values to be persisted. So you can persist value only if it is a string.

Here is a topic that can be helpful: How to use Enum with SQLAlchemy and Alembic?

Same result as in my question `sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type '' or '' or '', got '` So, easiest way is set values as string I think. I have made sure that using `int` in `enum` is inappropriate — Jekson, Feb 09 '22 at 11:05
Here you can find a hack for `int`: https://michaelcho.me/article/using-python-enums-in-sqlalchemy-models — jorzel, Feb 09 '22 at 12:05
You gave me great advice, found a solution that works for me in this answer https://stackoverflow.com/a/38786737/9262339 — Jekson, Feb 09 '22 at 13:55

SQLAlchemy Enum type field with integer value

1 Answers1