CODEWITHSUNDEEP
pythonenumssqlalchemy
snoopy91
snoopy91

Reputation: 347

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

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?

Upvotes: 3

Views: 5381

Answers (2)

Edward Charles Eberle
Edward Charles Eberle

Reputation: 21

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.

Upvotes: 2

snoopy91
snoopy91

Reputation: 347

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")
])

Upvotes: 2

Related Questions