method of iterating over sqlalchemy model's defined columns?

Question

I've been trying to figure out how to iterate over the list of columns defined in a SqlAlchemy model. I want it for writing some serialization and copy methods to a couple of models. I can't just iterate over the obj.dict since it contains a lot of SA specific items.

Anyone know of a way to just get the id, and desc names from the following?

class JobStatus(Base):
    __tablename__ = 'jobstatus'

    id = Column(Integer, primary_key=True)
    desc = Column(Unicode(20))

In this small case I could easily create a:

def logme(self):
    return {'id': self.id, 'desc': self.desc}

but I'd prefer something that was auto generating for larger objects.

Thanks for the assistance.

Answer 1

You can get the list of defined properties from the mapper. For your case you're interested in only ColumnProperty objects.

def attribute_names(cls):
    return [prop.key for prop in class_mapper(Cls).iterate_properties
        if isinstance(prop, sqlalchemy.orm.ColumnProperty)]

Answer 2

You could use the following function:

def __unicode__(self):
    return "[%s(%s)]" % (self.__class__.__name__, ', '.join('%s=%s' % (k, self.__dict__[k]) for k in sorted(self.__dict__) if '_sa_' != k[:4]))

It will exclude SA magic attributes, but will not exclude the relations. So basically it might load the dependencies, parents, children etc, which is definitely not desirable.

But it is actually much easier because if you inherit from Base, you have a __table__ attribute, so that you can do:

for c in JobStatus.__table__.columns:
    print c

for c in JobStatus.__table__.foreign_keys:
    print c

See How to discover table properties from SQLAlchemy mapped object - similar question.