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

问题:sqlalchemy模型的已定义列上迭代的方法?

我一直在尝试找出如何遍历SQLAlchemy模型中定义的列列表。我希望它为一些模型编写一些序列化和复制方法。我不能仅对其进行迭代,obj.__dict__因为它包含许多SA特定项。

有人知道一种从以下项中获取iddesc名称的方法吗?

class JobStatus(Base):
    __tablename__ = 'jobstatus'

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

在这种情况下,我可以轻松创建一个:

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

但我更喜欢自动生成dict(对于较大的对象)的东西。

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

您可以使用以下功能:

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

它将排除SA 魔术属性,但不会排除关系。因此,基本上它可能会加载依赖项,父项,子项等,这绝对是不可取的。

但这实际上要容易得多,因为如果继承自Base,则具有__table__属性,因此您可以执行以下操作:

for c in JobStatus.__table__.columns:
    print c

for c in JobStatus.__table__.foreign_keys:
    print c

请参阅如何从SQLAlchemy映射的对象中发现表属性 -类似的问题。

迈克(Mike)编辑:请参见Mapper.cMapper.mapped_table之类的函数。如果使用0.8或更高版本,还请参见Mapper.attrs和相关函数。

Mapper.attrs的示例:

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

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

您可以从映射器获取已定义属性的列表。对于您的情况,您仅对ColumnProperty对象感兴趣。

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

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

我意识到这是一个古老的问题,但是我遇到了相同的要求,并希望为未来的读者提供替代解决方案。

如Josh所述,完整的SQL字段名称将由返回JobStatus.__table__.columns,因此您将获得jobstatus.id而不是原始的字段名称 id。没有那么有用。

获取最初定义的字段名称列表的解决方案是_data在包含完整数据的列对象上查找属性。如果我们看一下JobStatus.__table__.columns._data,它看起来像这样:

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

从这里您可以简单地调用JobStatus.__table__.columns._data.keys()给您一个干净的清单:

['id', 'desc']

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将“仅”为您提供在该特定类中定义的列,即没有继承的列。如果需要全部,请使用self.__mapper__.columns。在您的示例中,我可能会使用以下内容:

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)

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

假设您正在使用SQLAlchemy的声明性映射,则可以使用__mapper__attribute来获取类映射器。要获取所有映射的属性(包括关系):

obj.__mapper__.attrs.keys()

如果需要严格的列名,请使用obj.__mapper__.column_attrs.keys()。有关其他视图,请参见文档。

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

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


回答 5

为了获得as_dict我所有Class的方法,我使用了一个Mixin使用Ants Aasma描述的技术的Class

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

然后在课堂上像这样使用它

class MyClass(BaseMixin, Base):
    pass

这样,您可以在的实例上调用以下内容MyClass

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

希望这可以帮助。


我对此进行了进一步的研究,实际上我需要将实例渲染为HAL对象dict的形式,并带有指向相关对象的链接。因此,我在这里添加了这个小技巧,它将覆盖与上述相同的类的所有属性,不同之处在于,我将更深入地搜索属性并自动生成这些属性。Relaionshiplinks

请注意,这仅适用于具有单个主键的关系

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

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

回答 6

self.__dict__

返回一个dict,其中键是属性名称,其值是对象的值。

/!\有一个补充属性:’_sa_instance_state’,但是您可以处理它:)

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


回答 7

我知道这是一个古老的问题,但是:

class JobStatus(Base):

    ...

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

然后,获取列名: jobStatus.columns()

那会回来 ['id', 'desc']

然后,您可以遍历,并对列和值进行处理:

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

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