How can I use "class" as an enum value in Python/SQLAlchemy?

Question

I have a model in SQLAlchemy of which one column is an enum. Wanting to stay as close to vanilla SQLAlchemy and the Python3 stdlib as possible, I've defined the enum against the stdlib's enum.Enum class, and then fed that to SQLAlchemy using its sqlalchemy.Enum class (as recommended somewhere in the SQLAlchemy documentation.)

class TaxonRank(enum.Enum):

    domain = "domain"
    kingdom = "kingdom"
    phylum = "phylum"
    class_ = "class"
    order = "order"
    family = "family"
    genus = "genus"
    species = "species"

And in the model:

rank = sqlalchemy.Column(sqlalchemy.Enum(TaxonRank), name = "rank", nullable = False)

This works well, except for forcing me to use class_ instead of class for one of the enum values (naturally to avoid conflict with the Python keyword; it's illegal syntax to attempt to access TaxonRank.class.)

I don't really mind using class_, but the issue I'm having is that class_ is the value that ends up getting stored in the database. This, in turn, is causing me issues with my CRUD API, wherein I allow the user to do things like "filter on rank where rank ends with ss." Naturally this doesn't match anything because the value actually ends with ss_!

For record display I've been putting in some hacky case-by-case translation to always show the user class in place of class_. Doing something similar with sorting and filtering, however, is more tricky because I do both of those at the SQL level.

So my question: is there a good way around this mild annoyance? I don't really care about accessing TaxonRank.class_ in my Python, but perhaps there's a way to subclass the stdlib's enum.Enum to force the string representation of the class_ attribute (and thus the value that actually gets stored in the database) to the desired class?

Does using this value cause errors in SQLAlchemy or other library code? You can use TaxonRank['class'] if it suits you. — Sergey Shubin
– Sergey Shubin, Commented Feb 20, 2017 at 12:17
TaxonRank["class"] would be fine; the problem is in the definition, specifically, class_ = "class". It's the attribute name (class_) not the attribute value (class) that gets put into the database. — snoopy91
– snoopy91, Commented Feb 20, 2017 at 13:09
Try to use this form of usage. If you define TaxonClass as Enum('TaxonClass', ['domain', 'kingdom', 'phylum', 'class']) you can avoid class_ attribute name. — Sergey Shubin
– Sergey Shubin, Commented Feb 20, 2017 at 14:03
Thank you! This form worked: Enum('TaxonClass', [("class", "class")]), to preserve the value of class in the database (as opposed to the default auto-incrementing integer.) — snoopy91
– snoopy91, Commented Feb 20, 2017 at 15:04

Community · Accepted Answer · 2017-05-23 12:09:23Z

2

Thanks to Sergey Shubin for pointing out to me an alternative form for defining an enum.Enum.

TaxonRank = enum.Enum("TaxonRank", [
    ("domain", "domain"),
    ("kingdom", "kingdom"),
    ("phylum", "phylum"),
    ("class", "class"),
    ("order", "order"),
    ("family", "family"),
    ("genus", "genus"),
    ("species", "species")
])

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Feb 20, 2017 at 15:08

snoopy91

3473 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edward Charles Eberle · Accepted Answer · 2019-03-16 19:46:26Z

I have been working on an interface for a Russian and English database. I am using postgresql, but it will probably work for any brand X enumeration. This is the solution solution:

In mymodel.py:

from sqlalchemy.dialects.postgresql import ENUM
from .meta import Base
from enum import Enum

class NounVar(Enum):
    abstract = 1
    proper = 2
    concrete = 3
    collective = 4,
    compound = 5

class Nouns(Base):
    __tablename__ = 'nouns'
    id = Column(Integer, primary_key=True)
    name = Column(Text) 
    runame = Column(Text)
    variety = Column("variety", ENUM(NounVar, name='variety_enum'))

And then further in default.py:

from .models.mymodel import Nouns

class somecontainer():
    def somecallable():
        page = Nouns(
            name="word", 
            runame="слово",
            variety=NounVar().concrete))
        self.request.dbsession.add(page)

I hope it works for you.

Collectives™ on Stack Overflow

How can I use "class" as an enum value in Python/SQLAlchemy?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related