python读取数据库生成JSON

# -*- coding: utf-8 -*-
"""
表结构：

CREATE TABLE `javahost_apptree` (
  `id` int(4) NOT NULL auto_increment,
  `appID` varchar(32) default NULL,
  `appName` varchar(128) default NULL,
  `parentID` varchar(32) default NULL,
  `isLeaf` smallint(6) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
"""
import sqlite3,json,MySQLdb,sys
conn=sqlite3.connect('data.db')
cu = conn.cursor()
"""
判断当前应用节点下面是否还有子节点
@return: true表示还有
"""
def hasChild(appId):
    sql = "select count(*) from javahost_apptree where parentID=%d"%int(appId)
    cu.execute(sql)
    rs = cu.fetchone()
    return int(rs[0]) > 0
"""判断当前这个应用ID是不是叶节点"""
def isleaf(appid):
    sql = "select count(*) from javahost_apptree where appID=%d and isleaf=1"%int(appid)
    cu.execute(sql)
    rs = cu.fetchone()
    return int(rs[0]) > 0
"""提取当前指定应用下面的全部的子节点。返回指定节点其下面的子节点列表
[
    {
        id:1,
        text:33,
        value:01
        complete:true,
        hasChileren:true,
    }
]
"""
def toJsonString(appId):
    resl= []
    sql = "select appID,appName,parentID,isLeaf from javahost_apptree where parentID=%d" % int(appId)
    cu.execute(sql)
    rs = cu.fetchall()
    for row in rs:
        result = {}
        result['complete']=True
        if hasChild(row[0]):
            result['hasChildren']=True
            result['ChildNodes'] = toJsonString(row[0])
        else:
            result['hasChildren']=False
        result['id']=row[0]
        result['text']=row[1]
        if isleaf(row[0]):
            result['value']='0'+row[0]
        else:
            result['value']=row[0]
        resl.append(result)
    return resl

def generateInitTreeString():
    """获取根节点数据不断迭代生成树"""
    jsonString = []
    cu.execute('select appID,appName,parentID,isLeaf from javahost_apptree where parentID=0 and id!=1046')
    rs = cu.fetchall()
    for row in rs:
        result = {}
        result['complete']=True
        if hasChild(row[0]):
            result['hasChildren']=True
            result['ChildNodes'] = toJsonString(row[0])
        else:
            result['hasChildren']=False
            result['ChildNodes'] =None

        result['id']=row[0]
        result['text']=row[1]
        if isleaf(row[0]):
            result['value']='0'+row[0]
        else:
            result['value']=row[0]
        jsonString.append(result)
    return json.dumps(jsonString,ensure_ascii=True)
if __name__ == '__main__':
    result = "var treedata = "
    result  = result + generateInitTreeString()
    file_object = open('tree.js', 'w')
    file_object.write(result)
    file_object.close()

前端JS的代码