如何将 SqlAlchemy 结果序列化为 JSON? [英] How to serialize SqlAlchemy result to JSON?

问题描述

Django 有一些很好的自动序列化从 DB 返回的 ORM 模型到 JSON 格式.

Django has some good automatic serialization of ORM models returned from DB to JSON format.

如何将 SQLAlchemy 查询结果序列化为 JSON 格式?

How to serialize SQLAlchemy query result to JSON format?

我尝试了 jsonpickle.encode 但它对查询对象本身进行编码.我试过 json.dumps(items) 但它返回

I tried jsonpickle.encode but it encodes query object itself. I tried json.dumps(items) but it returns

TypeError: <Product('3', 'some name', 'some desc')> is not JSON serializable

将 SQLAlchemy ORM 对象序列化为 JSON/XML 真的那么难吗?它没有任何默认的序列化程序吗?现在序列化 ORM 查询结果是很常见的任务.

Is it really so hard to serialize SQLAlchemy ORM objects to JSON /XML? Isn't there any default serializer for it? It's very common task to serialize ORM query results nowadays.

我需要的只是返回 SQLAlchemy 查询结果的 JSON 或 XML 数据表示.

What I need is just to return JSON or XML data representation of SQLAlchemy query result.

javascript datagird 中需要使用 JSON/XML 格式的 SQLAlchemy 对象查询结果 (JQGrid http://www.trirand.com/blog/)

SQLAlchemy objects query result in JSON/XML format is needed to be used in javascript datagird (JQGrid http://www.trirand.com/blog/)

推荐答案

扁平化实现

你可以这样使用:

A flat implementation

You could use something like this:

from sqlalchemy.ext.declarative import DeclarativeMeta

class AlchemyEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj.__class__, DeclarativeMeta):
            # an SQLAlchemy class
            fields = {}
            for field in [x for x in dir(obj) if not x.startswith('_') and x != 'metadata']:
                data = obj.__getattribute__(field)
                try:
                    json.dumps(data) # this will fail on non-encodable values, like other classes
                    fields[field] = data
                except TypeError:
                    fields[field] = None
            # a json-encodable dict
            return fields

        return json.JSONEncoder.default(self, obj)

然后使用以下方法转换为 JSON:

and then convert to JSON using:

c = YourAlchemyClass()
print json.dumps(c, cls=AlchemyEncoder)

它将忽略不可编码的字段(将它们设置为无").

It will ignore fields that are not encodable (set them to 'None').

它不会自动扩展关系(因为这可能导致自我引用，并永远循环).

It doesn't auto-expand relations (since this could lead to self-references, and loop forever).

但是，如果您希望永远循环，您可以使用:

If, however, you'd rather loop forever, you could use:

from sqlalchemy.ext.declarative import DeclarativeMeta

def new_alchemy_encoder():
    _visited_objs = []

    class AlchemyEncoder(json.JSONEncoder):
        def default(self, obj):
            if isinstance(obj.__class__, DeclarativeMeta):
                # don't re-visit self
                if obj in _visited_objs:
                    return None
                _visited_objs.append(obj)

                # an SQLAlchemy class
                fields = {}
                for field in [x for x in dir(obj) if not x.startswith('_') and x != 'metadata']:
                    fields[field] = obj.__getattribute__(field)
                # a json-encodable dict
                return fields

            return json.JSONEncoder.default(self, obj)

    return AlchemyEncoder

然后使用以下方法对对象进行编码:

And then encode objects using:

print json.dumps(e, cls=new_alchemy_encoder(), check_circular=False)

这将编码所有的孩子，他们所有的孩子，还有他们所有的孩子......基本上可以对整个数据库进行编码.当它到达之前编码过的东西时，它会将其编码为无".

This would encode all children, and all their children, and all their children... Potentially encode your entire database, basically. When it reaches something its encoded before, it will encode it as 'None'.

另一种可能更好的替代方法是能够指定要扩展的字段:

Another alternative, probably better, is to be able to specify the fields you want to expand:

def new_alchemy_encoder(revisit_self = False, fields_to_expand = []):
    _visited_objs = []

    class AlchemyEncoder(json.JSONEncoder):
        def default(self, obj):
            if isinstance(obj.__class__, DeclarativeMeta):
                # don't re-visit self
                if revisit_self:
                    if obj in _visited_objs:
                        return None
                    _visited_objs.append(obj)

                # go through each field in this SQLalchemy class
                fields = {}
                for field in [x for x in dir(obj) if not x.startswith('_') and x != 'metadata']:
                    val = obj.__getattribute__(field)

                    # is this field another SQLalchemy object, or a list of SQLalchemy objects?
                    if isinstance(val.__class__, DeclarativeMeta) or (isinstance(val, list) and len(val) > 0 and isinstance(val[0].__class__, DeclarativeMeta)):
                        # unless we're expanding this field, stop here
                        if field not in fields_to_expand:
                            # not expanding this field: set it to None and continue
                            fields[field] = None
                            continue

                    fields[field] = val
                # a json-encodable dict
                return fields

            return json.JSONEncoder.default(self, obj)

    return AlchemyEncoder

您现在可以通过以下方式调用它:

You can now call it with:

print json.dumps(e, cls=new_alchemy_encoder(False, ['parents']), check_circular=False)

例如，仅扩展名为parents"的 SQLAlchemy 字段.

To only expand SQLAlchemy fields called 'parents', for example.

这篇关于如何将 SqlAlchemy 结果序列化为 JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！