SQLAlchemy: How can I add common mapping logic to my custom base class (using declarative style mapping)?

Question

I have a project where every table has some common fields, e.g., status, and I'd like to alias all of them. Is it possible to do this without manually adding the alias to each class? E.g., here's what I have now:

from core import foo_table, bar_table, Status 

Base = declarative_base()

def CustomBase(object):
   @property
   def status(self):
      return Status(self._status)
  ...

def Foo(Base, CustomBase):
   __table__ = foo_table
   _status = foo_table.c.status
   ...


def Bar(Base, CustomBase):
   __table__ = bar_table
   _status = bar_table.c.status
   ...

Ideally, I'd like to be able to set up my _status alias on CustomBase instead of in Foo and Bar, or set up my project so that the alias is added whenever a class extending CustomBase is loaded. Is this possible or am I trying to accomplish this in the wrong way? I know I can make it work if I rename the status field in my db or rename the status property in the CustomBase, but I'd prefer to avoid this if possible since they're both representations of the same thing, and there's no need to directly access in the enum value through the code.

Thanks!

What exactly are you trying to achieve? Do you just want every declarative model to have a status field? Or do you mean something special by "alias", for example that all those status fields should refer to a common set of objects? — Julian Gonggrijp
– Julian Gonggrijp, Commented Jul 25, 2014 at 0:13
Each of my DB tables have an enum field called status, but in the code I want to interact with it through my Status class. I only need the original status value to initialize my Status object, which is why I'm not sure that this is even the right approach. It might be that I can hook into some event somewhere, or do some custom thing with column types, but I'm very new to SQLAlchemy and the solution in the code sample is the only one I could get working. I don't like copy-pasting the alias ("_status = bar_table.c.status") into each class though, so I'm wondering if there's a better way. — mmitchell
– mmitchell, Commented Jul 25, 2014 at 0:29

Julian Gonggrijp · Accepted Answer · 2014-07-25 11:18:28Z

Your best bet is probably to create a custom Column type that adapts Enum to translate to and from your own Status class. See here for a full reference. Below is a draft for your core module, the precise code depends a bit on your situation.

# core module

import sqlalchemy.types as types

class DBStatus (types.TypeDecorator):

    impl = types.Enum

    # what should happen with Status objects on the way into the table
    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        return str(value)  # if Status has a __str__ or __repr__ method

    # what should happen with Enum objects on the way out of the table
    def process_result_value(self, value, dialect):
        if value is None:
            return value
        return Status(value)

foo_table = Table(
    'foo',
    MetaData(),
    Column('status', DBStatus('OK', 'Error')),
    # ...
)

After this you don't have to do anything special anymore in the module with the mappings:

# module with the mappings

Base = declarative_base()

class Foo (Base):
    __table__ = foo_table
    # ...

In fact it's so straightforward you might just as well use full declarative mapping, as far as the Status columns are concerned.

# everything in one module

class DBStatus (types.TypeDecorator):

    # same as above

Base = declarative_base()

class Foo (Base):
    status = Column(DBStatus('OK', 'Error'))
    # ...

Collectives™ on Stack Overflow

SQLAlchemy: How can I add common mapping logic to my custom base class (using declarative style mapping)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related