SQLAlchemy Declarative: Adding a static text attribute to a column

Question

I am using: SQLAlchemy 0.7.9 and Python 2.7.3 with Bottle 0.11.4. I am an amateur at python.

I have a class (with many columns) derived from declarative base like this:

class Base(object):

     @declared_attr
     def __tablename__(cls):
             return cls.__name__.lower()

     id = Column(Integer, primary_key = True)

     def to_dict(self):
             serialized = dict((column_name, getattr(self, column_name))
                     for column_name in self.__table__.c.keys())
             return serialized

Base = declarative_base(cls=Base)


class Case(Base):

     version                 = Column(Integer)
     title                   = Column(String(32))
     plausible_dd            = Column(Text)
     frame                   = Column(Text)
     primary_task            = Column(Text)
     secondary_task          = Column(Text)
     eval_objectives         = Column(Text)
     ...

I am currently using this 'route' in Bottle to dump out a row/class in json like this:

@app.route('/<name>/:record')
def default(name, record, db):
    myClass = getattr(sys.modules[__name__], name)
    parms = db.query(myClass).filter(myClass.id == record)
    result = json.dumps(([parm.to_dict() for parm in parms]))
    return result

My first question is: How can I have each column have some static text that I can use as a proper name such that I can iterate over the columns and get their values AND proper names? For example:

class Case(Base):
     version    = Column(Integer)
     version.pn = "Version Number" 

My second question is: Does the following do what I am looking for? I have seen examples of this, but I don't understand the explanation.

Example from sqlalchemy.org:

      id = Column("some_table_id", Integer)

My interpretation of the example:

      version   = Column("Version Number", Integer)

Obviously I don't want a table column to be created. I just want the column to have an "attribute" in the generic sense. Thank you in advance.

Answer 1

info dictionary could be used for that. In your model class define it like this:

class Case(Base):
    version = Column(Integer, info={'description': 'Version Number'})

Then it can accessed as the table column property:

desc = Case.__table__.c.version.info.get('description', '<no description>')

---

Update

Here's one way to iterate through all the columns in the table and get their names, values and descriptions. This example uses dict comprehension, which is available since Python 2.7.

class Case(Base):
    # Column definitions go here...

    def as_dict(self):
        return {c.name: (getattr(self, c.name), c.info.get('description'))
                for c in self.__table__.c}