1

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?

Community
  • 1
  • 1
rhaskett
  • 1,864
  • 3
  • 29
  • 48
  • 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 Feb 24 '13 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 Feb 25 '13 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 Feb 25 '13 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](http://techspot.zzzeek.org/2011/01/14/the-enum-recipe/), using simple many-to-one. then making it look like the "succinct" version you'd have to figure something out. – zzzeek Feb 25 '13 at 19:58

1 Answers1

1

If I'm not mistaken, doing:

type = Column(EmployeeType.db_type())

instead of

type = Column(DeclEnumType(EmployeeType))

should do it.

Carlos
  • 2,222
  • 12
  • 21
  • This will allow Cito's original example to work but while Employee will be persisted, there won't be an EmployeeType table. – rhaskett Feb 25 '13 at 15:42