python sqlalchemy 查詢結果轉化為 Json格式

      最近，給自己開發的軟體平台開發第三方調用的API，如果返回結果集是json格式，其他語言開發就相對方便一些，網上找了好多資料沒有找到特別合適的，最後下決心根據網上的資料改變自己寫一個通用的。

此方法，主要應用情境是，Python 資料庫架構 sqlalchemy 查詢結果，轉化成json格式。

# -*- coding: utf-8 -*-import osimport jsonimport threading from xml.dom import minidomfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker, scoped_sessionfrom sqlalchemy import (create_engine,MetaData,Column,Integer,String)from sqlalchemy import funcfrom sqlalchemy.sql.expression import and_, or_from sqlalchemy import Table, Column, Integer, String, Text, DateTime, MetaData, ForeignKey, Booleanfrom datetime import datetimeimport time,uuid,reBase = declarative_base()#主機模型class VirtualMachineContainer(Base):    __tablename__ = 'virtual_machine_containers'    id = Column(Integer, primary_key = True)    hostname = Column(String(255))    uuid = Column(String(255))    address = Column(String(255))    port = Column(Integer)    status = Column(String(255))    owner_id = Column(Integer)    group_id = Column(Integer)    cluster_id = Column(Integer)    running_time = Column(Integer)    cpu_type = Column(String(255))    cpu_num = Column(String(255))    cpu_usage = Column(String(255))    mem_total = Column(String(255))    mem_free = Column(String(255))    disk_device = Column(String(255))    disk_total = Column(String(255))    disk_free = Column(String(255))    nics_num = Column(Integer)    net_ifname = Column(String(255))    net_tx = Column(String(255))    net_rx = Column(String(255))    vm_name = Column(String(255))    vm_state = Column(String(255))    vcpu_usage = Column(String(255))    vmem_total = Column(String(255))    vmem_free = Column(String(255))    vdisk_read = Column(String(255))    vdisk_write = Column(String(255))    vif_tx = Column(String(255))    vif_rx = Column(String(255))    field_metadata = Column('metadata', String(255))    created_at = Column(DateTime, default=datetime.now)    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)    vm_uuid = Column(Text)    capability = Column(String(255))    vdisk_names = Column(String(255))    vif_names = Column(String(255))    all_pair_ping = Column(Text)    vnc_port = Column(String(255))    vm_cnt = Column(Integer)    respool_cnt = Column(String(255))    reserved = Column(String(255))    host_desc = Column(Text)    host_vendor_name = Column(String(255))    host_type = Column(String(255))    oper_system_vendor_name = Column(String(255))    oper_system_name = Column(String(255))    uuid_bios = Column(String(255))    dns = Column(String(255))    cpu_core_num = Column(String(255))    cpu_thread_num = Column(String(255))    diskarray_num = Column(String(255))    datacenter_id = Column(Integer, ForeignKey('data_centers.id'))    user_name = Column(String(255))    password = Column(String(255))    status_flag = Column(Integer(1))    hardware_id = Column(String(16))def get_Session():    #engine = create_engine('mysql://root:root@172.30.36.7/ivcs_portal?charset=utf8')    engine = create_engine('mysql://root:@172.30.36.7/ivcs_portal?charset=utf8')    Base.metadata.create_all(engine)    Session =sessionmaker( autocommit=False, autoflush=False, bind=engine)    return Sessionfrom sqlalchemy.ext.declarative import DeclarativeMetaclass 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:                    if isinstance(data, datetime):                         data=data.strftime('%Y-%m-%d %H:%M:%S')                     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)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']:                    data = obj.__getattribute__(field)                    try:                        if isinstance(data, datetime):                             data=data.strftime('%Y-%m-%d %H:%M:%S')                         json.dumps(data) # this will fail on non-encodable values, like other classes                        fields[field] = data                    except TypeError:                        fields[field] = None                return fields            return json.JSONEncoder.default(self, obj)    return AlchemyEncoderdef testjson():    session=get_Session()()    vmcs = session.query(VirtualMachineContainer).all()    try:                Hosts =[]        for vmc in vmcs:            #print json.dumps(vmc, cls=AlchemyEncoder)            Hosts.append(vmc)        print json.dumps(Hosts, cls=new_alchemy_encoder(), check_circular=False)    except Exception, e:        print e  if __name__ == '__main__':    testjson()