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

Answer 1

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

Answer 2

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)]

Answer 3

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.keys() which gives you a nice, clean list:

['id', 'desc']

Answer 4

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

Answer 5

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)

Answer 6

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

Answer 7

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 :)

Answer 8

While row._asdict() worked for most of the cases, I needed some approach that also works after object creation process (db.session.add etc.). The idea is to create a method to_dict accessing columns on the table object and use standard getattr.

class Inventory(db.Model):
    __tablename__ = 'inventory'

    id = db.Column('id', db.Integer(), primary_key=True)
    date = db.Column('date', db.DateTime, nullable=False, default=datetime.utcnow)
    item = db.Column('item', db.String(100))

    def to_dict(self):
        return {
            column.name: getattr(self, column.name, None)
            for column in Inventory.__table__.columns
        }



record = Inventory(item="gloves")
db.session.add(record)
db.session.commit()

# print(record._asdict()) # << that doesn't work
print(record.to_dict()) # << that works as intended

This solution will produce dict with columns only - no meta attributes or anything that you will have to manually clean after the next major update (if any).

PS. I use flask-sqlalchemy but it does not change the idea

Answer 9

I want to get the data of a particular instance of Model dynamically. I used this code.

def to_json(instance):
    # get columns data
    data = {}
    columns = list(instance.__table__.columns)
    for column in columns:
        data[column.name] = instance.__dict__[column.name]
    return data

Answer 10

To map a model from sqlalchemy to a json, taking into account relationships, I use this code

from sqlalchemy.orm import class_mapper
from sqlalchemy.ext.declarative import DeclarativeMeta
from sqlalchemy.orm import ColumnProperty
from sqlalchemy.orm import RelationshipProperty


class BaseMixin(object):
    """BaseMixin"""

    __repr_hide = ["created_at", "updated_at"]
    __insert_hide = []

    @property
    def _repr_hide(self):
        return self.__repr_hide

    @_repr_hide.setter
    def _repr_hide(self, k):
        self.__repr_hide.append(k)

    @property
    def _insert_hide(self):
        return self.__insert_hide

    @_insert_hide.setter
    def _insert_hide(self, k):
        self.__insert_hide.append(k)

    def serialize(self, obj):
        """serialize from json"""
        for k, v in obj.items():
            if k in self.__repr_hide:
                continue
            if k in self.__insert_hide:
                continue
            if k in self.__table__.c.keys():
                setattr(self, k, v)
        return self

    def deserialize(self, backref=None):
        """deserialize to json"""
        res = dict()

        for prop in class_mapper(self.__class__).iterate_properties:
            if prop.key in self.__repr_hide:
                continue
            if isinstance(prop, ColumnProperty):
                res[prop.key] = getattr(self, prop.key)

        for prop in class_mapper(self.__class__).iterate_properties:
            if prop.key in self.__repr_hide:
                continue
            if isinstance(prop, RelationshipProperty):
                if prop.key == str(backref):
                    continue
                key, value = prop.key, getattr(self, prop.key)
                if value is None:
                    res[key] = None
                elif isinstance(value.__class__, DeclarativeMeta):
                    res[key] = value.deserialize(backref=self.__table__)
                else:
                    res[key] = [i.deserialize(backref=self.__table__) for i in value]
        return res

    def __iter__(self):
        return iter(self.deserialize().items())

    def __repr__(self):
        vals = ", ".join(
            "%s=%r" % (n, getattr(self, n))
            for n in self.__table__.c.keys()
            if n not in self._repr_hide
        )

        return "<%s={%s}>" % (self.__class__.__name__, vals)

Answer 11

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))