SQLAlchemy Enums Code Representation _and_ DB Representation

Question

This is a follow up to the very interesting answers of best-way-to-do-enum-in-sqlalchemy. Cito extends Zzzeeks answer to include ordering which is very nice. Cito also leaves a tantalizing bit of code near the end.

class DeclEnumType(SchemaType, TypeDecorator):
    """DeclEnum augmented so that it can persist to the database."""

This is exactly what I am attempting to do build a Python Enum that is represented in its own table in the db. Where the EmployeeType.full_time is usable in Python code and has its own table in the DB (for this simple example just a idx and name).

However, I'm not sure I understand how to use Cito's DeclEnumType example as the following doesn't create a EmployeeType table in the database.

class EmployeeType(DeclEnum):
    # order will be as stated: full_time, part_time, contractor
    full_time = EnumSymbol("Full Time")
    part_time = EnumSymbol("Part Time")
    contractor = EnumSymbol("Contractor")

class Employee(Base):
    __tablename__ = 'employee'

    id = Column(Integer, primary_key=True)
    name = Column(String(60), nullable=False)
    type = Column(DeclEnumType(EmployeeType))

Any ideas on how to get this dual representation?

well you'd need to reverse the approach and go back to the original approach, where you make a new mapped class EmployeeType, then each EnumSymbol() above is in fact an instance of EmployeeType. To make it "look" like the more succinct approaches, you'd need to use metaclasses and various proxy objects and such to hide what's really going on, since those EmployeeType instances are now persisted, and are unique only to a Session, etc. - doable but intricate. The link from Employee to EmployeeType would be via relationship(). — zzzeek
– zzzeek, Commented Feb 24, 2013 at 21:23
Interesting Zzzeek. I'm fairly new to SQLAlchemy but there appears to be some good examples of metaclasses in the documentation and your example. I'll hammer away at it. — rhaskett
– rhaskett, Commented Feb 25, 2013 at 15:39
I'm a little confused with the above Zzzeek. Isn't that a self-reference with EmployeeType having an EnumSymbol which is an EmployeeType? — rhaskett
– rhaskett, Commented Feb 25, 2013 at 17:13
The way we've been talking about enums here really doesn't look that way when you use two distinct tables. To make it look like the above, but then be mapped, it really has to be a whole facade on top of mapped objects. I'd start with doing a mapping that matches the beginning part of my enum post, using simple many-to-one. then making it look like the "succinct" version you'd have to figure something out. — zzzeek
– zzzeek, Commented Feb 25, 2013 at 19:58

Carlos · Accepted Answer · 2013-02-22 23:38:46Z

1

If I'm not mistaken, doing:

type = Column(EmployeeType.db_type())

instead of

type = Column(DeclEnumType(EmployeeType))

should do it.

answered Feb 22, 2013 at 23:38

Carlos

2,23212 silver badges21 bronze badges

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

1 Comment

rhaskett Over a year ago

This will allow Cito's original example to work but while Employee will be persisted, there won't be an EmployeeType table.

Collectives™ on Stack Overflow

SQLAlchemy Enums Code Representation _and_ DB Representation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related