python sqlalchemy 查询结果转化为 Json格式

      最近，给自己开发的软件平台开发第三方调用的API，如果返回结果集是json格式，其他语言开发就相对方便一些，网上找了好多资料没有找到特别合适的，最后下决心根据网上的资料改变自己写一个通用的。

此方法，主要应用场景是，Python 数据库框架 sqlalchemy 查询结果，转化成json格式。

# -*- coding: utf-8 -*-
import os
import json
import threading
 
from xml.dom import minidom
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy import (create_engine,MetaData,Column,Integer,String)
from sqlalchemy import func
from sqlalchemy.sql.expression import and_, or_
from sqlalchemy import Table, Column, Integer, String, Text, DateTime, MetaData, ForeignKey, Boolean
from datetime import datetime
import time,uuid,re

Base = 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 Session
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:
                    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 AlchemyEncoder

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