python通过查询mysql生成省市区json树结构

def queryAllsql(code,cursor,tree):
    # "code": "110000",
    # "name": "北京市",
    # "level": 1,
    # "selected": false,
    # "subdata":
    sub_list = []
    sql = " select  level,code,name,parentid FROM  t_region  WHERE parentid=(" + code + ") "
    cursor.execute(sql)
    result = cursor.fetchall()  # 执行sql语句，返回sql查询成功的记录数目
    if len(result) != 0:
        for row in result:
            d = collections.OrderedDict()
            d["code"] = row.get("code")
            d["name"] = row.get("name")
            d["level"] = row.get("level")
            d["selected"] = False
            d["subdata"] =  queryAllsql(row.get("code"), cursor, d)
          
            sub_list.append(d)
            d["parentid"] = row.get("parentid")
    
        return sub_list
    else:
        return sub_list


def test_sub_menu_list():
    # 生成js
    # 1.连接
    conn = pymysql.connect(host=host, port=port, user=user,
                           password=password, db=db,
                           charset='utf8')

    # 2.创建游标   cursor = pymysql.cursors.DictCursor 指定返回的数据是字典
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = " select  level,code,name,parentid FROM  t_region  WHERE level=1"
    cursor.execute(sql)
    result = cursor.fetchall()  # 执行sql语句，返回sql查询成功的记录数目
    for row in result:
        d = collections.OrderedDict()
        d["code"] = row.get("code")
        d["name"] = row.get("name")
        d["level"] = row.get("level")
        d["selected"] = False
        d["subdata"] = queryAllsql(row.get("code"), cursor, d)
        d["parentid"] = row.get("parentid")
        objects_list.append(d)
    #格式化json
    jsonArr = json.dumps(objects_list,indent=4, ensure_ascii=False)
    print(jsonArr)
    file = open('regionutil.js', 'w', encoding='utf-8')
    file.write("var REGION = "+jsonArr)

    with open('temp.js','r',encoding='utf-8') as file_read:
        content = file_read.read()
        file.write(content)
    file.close()
    file_read.close()
    conn.commit()
    # 关闭连接，游标和连接都要关闭
    cursor.close()
    conn.close()


if __name__ == "__main__":
   
    test_sub_menu_list()