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 auto-generates the dict (for larger objects).

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.

Edit by Mike: Please see functions such as Mapper.c and Mapper.mapped_table. If using 0.8 and higher also see Mapper.attrs and related functions.

Example for Mapper.attrs:

from sqlalchemy import inspect
mapper = inspect(JobStatus)
for column in mapper.attrs:
    print column.key

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

from sqlalchemy.orm import class_mapper
import sqlalchemy

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

I realise that this is an old question, but I’ve just come across the same requirement and would like to offer an alternative solution to future readers.

As Josh notes, full SQL field names will be returned by JobStatus.__table__.columns, so rather than the original field name id, you will get jobstatus.id. Not as useful as it could be.

The solution to obtaining a list of field names as they were originally defined is to look the _data attribute on the column object, which contains the full data. If we look at JobStatus.__table__.columns._data, it looks like this:

{'desc': Column('desc', Unicode(length=20), table=<jobstatus>),
 'id': Column('id', Integer(), table=<jobstatus>, primary_key=True, nullable=False)}

From here you can simply call JobStatus.__table__.columns._data.keys() which gives you a nice, clean list:

['id', 'desc']

self.__table__.columns will “only” give you the columns defined in that particular class, i.e. without inherited ones. if you need all, use self.__mapper__.columns. in your example i’d probably use something like this:

class JobStatus(Base):

    ...

    def __iter__(self):
        values = vars(self)
        for attr in self.__mapper__.columns.keys():
            if attr in values:
                yield attr, values[attr]

    def logme(self):
        return dict(self)

Assuming you’re using SQLAlchemy’s declarative mapping, you can use __mapper__ attribute to get at the class mapper. To get all mapped attributes (including relationships):

obj.__mapper__.attrs.keys()

If you want strictly column names, use obj.__mapper__.column_attrs.keys(). See the documentation for other views.

https://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.attrs

To get an as_dict method on all of my classes I used a Mixin class which uses the technics described by Ants Aasma.

class BaseMixin(object):                                                                                                                                                                             
    def as_dict(self):                                                                                                                                                                               
        result = {}                                                                                                                                                                                  
        for prop in class_mapper(self.__class__).iterate_properties:                                                                                                                                 
            if isinstance(prop, ColumnProperty):                                                                                                                                                     
                result[prop.key] = getattr(self, prop.key)                                                                                                                                           
        return result

And then use it like this in your classes

class MyClass(BaseMixin, Base):
    pass

That way you can invoke the following on an instance of MyClass.

> myclass = MyClass()
> myclass.as_dict()

Hope this helps.

I’ve played arround with this a bit further, I actually needed to render my instances as dict as the form of a HAL object with it’s links to related objects. So I’ve added this little magic down here, which will crawl over all properties of the class same as the above, with the difference that I will crawl deeper into Relaionship properties and generate links for these automatically.

Please note that this will only work for relationships have a single primary key

from sqlalchemy.orm import class_mapper, ColumnProperty
from functools import reduce


def deepgetattr(obj, attr):
    """Recurses through an attribute chain to get the ultimate value."""
    return reduce(getattr, attr.split('.'), obj)


class BaseMixin(object):
    def as_dict(self):
        IgnoreInstrumented = (
            InstrumentedList, InstrumentedDict, InstrumentedSet
        )
        result = {}
        for prop in class_mapper(self.__class__).iterate_properties:
            if isinstance(getattr(self, prop.key), IgnoreInstrumented):
                # All reverse relations are assigned to each related instances
                # we don't need to link these, so we skip
                continue
            if isinstance(prop, ColumnProperty):
                # Add simple property to the dictionary with its value
                result[prop.key] = getattr(self, prop.key)
            if isinstance(prop, RelationshipProperty):
                # Construct links relaions
                if 'links' not in result:
                    result['links'] = {}

                # Get value using nested class keys
                value = (
                    deepgetattr(
                        self, prop.key + "." + prop.mapper.primary_key[0].key
                    )
                )
                result['links'][prop.key] = {}
                result['links'][prop.key]['href'] = (
                    "/{}/{}".format(prop.key, value)
                )
        return result

self.__dict__

returns a dict where keys are attribute names and values the values of the object.

/!\ there is a supplementary attribute: ‘_sa_instance_state’ but you can handle it :)

I know this is an old question, but what about:

class JobStatus(Base):

    ...

    def columns(self):
        return [col for col in dir(self) if isinstance(col, db.Column)]

Then, to get column names: jobStatus.columns()

That would return ['id', 'desc']

Then you can loop through, and do stuff with the columns and values:

for col in jobStatus.colums():
    doStuff(getattr(jobStatus, col))