Convert the query result of Python sqlalchemy to JSON format

Recently, a third-party called API has been developed for a self-developed software platform. If the returned result set is in JSON format, it is easier to develop it in other languages, I found a lot of information on the Internet and didn't find it very suitable. Finally, I decided to change my mind to write a general one based on the information on the Internet.

The main application scenario of this method is to convert the query results of the python database framework sqlalchemy into JSON format.

#-*-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 () # host model 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 (in Teger) 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 (stri Ng (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) h Ost_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 _ cente Rs. 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 into T typeerror: fields [field] = none # a json-encodable dict return fields return JSON. jsonencoder. default (self, OBJ) def new_alchemy_encoder (): _ visited_objs = [] class alche Myencoder (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 completion t 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) expect t exception, E: Print e if _ name _ = '_ main _': testjson ()