method of iterating over sqlalchemy model's defined columns?

小开

您可以从映射器获得已定义属性的列表。

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

小开

最佳答案

您可以使用以下函数:

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 映射对象中发现表属性-类似的问题。

编辑迈克: 请参阅地图和 Map _ table等函数。如果使用0.8或更高，还可以看到 Mapper.Attrs和相关函数。

Mapper.Attrs的例子:

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

小开

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

Josh 指出，完整的 SQL 字段名将由 JobStatus.__table__.columns返回，因此不是原来的字段名 身份证，而是 工作状态 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']

小开

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)

小开

为了在我的所有类上获得一个 as_dict方法，我使用了一个 Mixin类，它使用了蚂蚁 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

然后像这样用在你的课堂上

class MyClass(BaseMixin, Base):
pass

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

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

希望这个能帮上忙。

I've played arround with this a bit further, I actually needed to render my instances as dict as the form of a HAL 物体 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.

请注意，这只适用于只有一个主键的关系

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

小开

我知道这是一个老问题，但是关于:

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

小开

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

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

小开

self.__dict__

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

/! 有一个补充属性: “ _ sa _ instance _ state” 但是你能处理好:)

小开

我想动态地获取 Model 的特定实例的数据。

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

小开

考虑到关系，我使用下面的代码将一个模型从 sql 炼金术映射到一个 json

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)

小开

虽然 row._asdict()在大多数情况下都可以工作，但是我需要一些在对象创建过程(db.session.add等)之后也可以工作的方法。其思想是创建一个方法 to_dict访问表对象上的列，并使用标准的 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

这个解决方案将只生成包含列的 dict ——没有元属性或者任何在下一次主要更新(如果有的话)之后需要手动清理的东西。

另外，我使用了烧瓶炼金术，但是它并没有改变我的想法