将SqlAlchemy ORM结果转换为dict [英] Convert SqlAlchemy orm result to dict

问题描述

如何将SQLAlchemy orm对象结果转换为JSON格式?

How to convert SQLAlchemy orm object result to JSON format?

当前，我正在使用sqlalchemy反射来反射数据库中的表. 考虑一下，我有一个用户表和一个地址表，我正在从数据库中反映出来. 用户实体与地址实体具有一对一的关系. 下面是用于反映数据库中的表并使用mapper类映射关系的代码.

Currently I am using sqlalchemy reflection to reflect tables from the DB. Consider I have a User table and a Address table I am reflecting fro the DB. The user entity has one to one relationship with the address entity. Below is the code to reflect the table from the DB and use the mapper class to map the relationship.

from sqlalchemy import Table
from sqlalchemy.orm import mapper, relationship
user_reflection = Table('user', metadata, autoload=True, autoload_with=engine)
class User(object):
    def __init__(self, id, name, dob):
        self.id = id
        self.name = name
        self.dob = dob
address_reflection = Table('address', metadata, autoload=True, autoload_with=engine)
mapper(User,
       user_reflection,
       properties={
           'address': relationship(SourceAddress, uselist=False)
       }
)

现在，当我使用sqlalchemy orm查询对象

Now when I query the object using sqlalchemy orm

user = session.query(User).first()
user_dict = object_to_dict(user)

现在，当我要将用户对象转换为字典时 我使用以下方法

Now, when I want to convert the user object to dict I use the below method

def object_to_dict(obj):
    columns = [column.key for column in class_mapper(obj.__class__).columns]
    get_key_value = lambda c: (c, getattr(obj, c).isoformat()) if isinstance(getattr(obj, c), datetime) else (c, getattr(obj, c))
    return dict(map(get_key_value, columns))

但是，如果返回的用户对象与另一个表没有关系，则object_to_dict方法可以正常工作并返回有效的dic对象. 如果用户对象具有关系，则object_to_dict方法不会自动展开关系对象并将其转换为dict.

However, the object_to_dict methods works fine and returns a valid dic object if the returned user object that did not have a relationship with another table. If the user object has a relationship the object_to_dict method doesn't auto-expand relations object and convert it to dict.

任何人都可以建议我如何自动确定返回的用户对象是否具有关联关系，并将该关系对象扩展为字典，如果该对象具有任意数量的子对象等等.

Could anyone suggest me how I could automatically determine if the returned user object has a relationship and expand the relationship object to a dict if it has one and so on for any number of child objects.

推荐答案

您可以使用映射器的Relationships属性.代码选择取决于您要如何映射数据以及关系的外观.如果您有很多递归关系，则可能要使用max_depth计数器.下面的示例使用一组关系来防止递归循环.如果您只打算深入一遍，则可以完全消除递归，但是您确实说了依此类推".

You can use the relationships property of the mapper. The code choices depend on how you want to map your data and how your relationships look. If you have a lot of recursive relationships, you may want to use a max_depth counter. My example below uses a set of relationships to prevent a recursive loop. You could eliminate the recursion entirely if you only plan to go down one in depth, but you did say "and so on".

def object_to_dict(obj, found=None):
    if found is None:
        found = set()
    mapper = class_mapper(obj.__class__)
    columns = [column.key for column in mapper.columns]
    get_key_value = lambda c: (c, getattr(obj, c).isoformat()) if isinstance(getattr(obj, c), datetime) else (c, getattr(obj, c))
    out = dict(map(get_key_value, columns))
    for name, relation in mapper.relationships.items():
        if relation not in found:
            found.add(relation)
            related_obj = getattr(obj, name)
            if related_obj is not None:
                if relation.uselist:
                    out[name] = [object_to_dict(child, found) for child in related_obj]
                else:
                    out[name] = object_to_dict(related_obj, found)
    return out

此外，请注意，还有一些性能问题需要考虑.您可能需要使用诸如joinedload或subqueryload之类的选项，以防止执行过多的SQL查询.

Also, be aware that there are performance issues to consider. You may want to use options such as joinedload or subqueryload in order to prevent executing an excessive number of SQL queries.

这篇关于将SqlAlchemy ORM结果转换为dict的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！