格式资料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()
文章结束给大家分享下程序员的一些笑话语录：
人脑与电脑的相同点和不同点，人脑会记忆数字，电脑也会记忆数字；人脑会记忆程序，电脑也会记忆程序，但是人脑具有感知能力，这种能力电脑无法模仿，人的记忆会影响到人做任何事情，但是电脑只有程序软件。比尔还表示，人脑与电脑之间最重要的一个差别就是潜意识。对于人脑存储记忆的特别之处，比尔表示，人脑并不大，但是人脑重要的功能是联络，人脑会把同样的记忆存储在不同的地方，因此记忆读取的速度就不相同，而这种速度取决于使用的频率和知识的重要性。人脑的记忆存储能力会随着年龄增长而退化，同时记忆的质量也会随着年龄退化。经典语录网
