迭代sqlalchemy模型定义列的方法?

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

#0

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

#1

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

#2

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

#3

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)

#4

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

#5

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

#6

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

推荐文章

显示带有静态标签的滚动网格

显示带有静态标签的滚动网格

推荐文章

重新打开使用ShowDialog()显示的已关闭窗体

重新打开使用ShowDialog()显示的已关闭窗体

推荐文章

注入字符串的二进制数据

注入字符串的二进制数据

推荐文章

无法发布到heroku

无法发布到heroku

推荐文章

程序集“”中的类型“”未标记为可序列化。linq到sql

程序集“”中的类型“”未标记为可序列化。linq到sql

推荐文章

SQLite3 FTS4、MATCH和Android

SQLite3 FTS4、MATCH和Android

推荐文章

软键盘不允许在同一个键上使用keyOutputText和keyIcon

软键盘不允许在同一个键上使用keyOutputText和keyIcon

推荐文章

应用程序引擎-检查Expando类中是否存在属性

应用程序引擎-检查Expando类中是否存在属性

推荐文章

远程选择性git忽略?

远程选择性git忽略?

推荐文章

将mySQL行复制到具有不同名称的完全相同的表?

将mySQL行复制到具有不同名称的完全相同的表?

推荐文章

维护Android应用程序版本的最佳实践

维护Android应用程序版本的最佳实践

推荐文章

通过TFS 2008发送合并请求

通过TFS 2008发送合并请求

推荐文章

向主视图添加标签

向主视图添加标签

推荐文章

BerkleyDb JE DbDump异常内存使用率

BerkleyDb JE DbDump异常内存使用率

推荐文章

PHP中的邮政编码定位器

PHP中的邮政编码定位器

推荐文章

如何将java连接到Ms Access 2010?

如何将java连接到Ms Access 2010?