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

问题描述

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

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屋！