getdatafrom2tabletojsonapi.py

getdatafrom2tabletojsonapi.py

#!flask/bin/python
# coding=utf-8
from flask import Flask, jsonify
import json
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)

try:
    connection = mysql.connector.connect(host='127.0.0.1',database='bms_1',user='root',password='xxxxxx')
    if connection.is_connected():
       # ======object result======#
        object_sql = "select id,guid,tag,path,name,node_type,device_type,point_type,unit,link, alarm_level,alarm_type,percentage,abs_value,time from object;;"
        object_cursor = connection.cursor()
        object_cursor.execute(object_sql)
        object_result_list = object_cursor.fetchall()  # return sql result
        print("fields fetch result-->", object_result_list)  # is s list type, need to be a dict
        object_fields_list = object_cursor.description  # sql key name
        print("fields result -->", object_fields_list)
        object_cursor.close()

    # main part
    object_column_list = []
    for i in object_fields_list:
        object_column_list.append(i[0])
    print("object_colume_list-->",object_column_list)

    object_jsonData_list = []
    for objectrow in object_result_list:
        object_data_dict = {}
        for i in range(len(object_column_list)):
            object_data_dict[object_column_list[i]] = objectrow[i]
            #print("data_disc-->", data_dict)
        object_jsonData_list.append(object_data_dict)

    # ======space result======#
        space_sql = "select id,guid,tag,path,name,node_type,space_type,parent_guid,time from space;"
        space_cursor = connection.cursor()
        space_cursor.execute(space_sql)
        space_result_list = space_cursor.fetchall()
        space_fields_list = space_cursor.description
        space_cursor.close()

    space_column_list = []
    for space_header in space_fields_list:
        space_column_list.append(space_header[0])
    print("space clolume list -->",space_column_list)

    space_jsonData_list = []
    for spacerow in space_result_list:
        space_data_dict = {}
        for space_header in range(len(space_column_list)):
            space_data_dict[space_column_list[space_header]] = spacerow[space_header]
        print("space_data_dict-->", space_data_dict)
        space_jsonData_list.append(space_data_dict)


    @app.route('/north/getAll', methods=['GET'])
    def get_all():
        return jsonify({'space': space_jsonData_list, 'object': object_jsonData_list})


    @app.route('/north/getSpace', methods=['GET'])
    def get_space():
        return jsonify({'space': space_jsonData_list})

    @app.route('/north/getObject', methods=['GET'])
    def get_object():
        return jsonify({'object': object_jsonData_list})

    if __name__ == '__main__':
        app.run(host='127.0.0.1', port=5001, debug=True)


except Error as e:
    print("Error while connection to Mysql", e)
finally:
    connection.close()
    print "==== mysql closed==="

➜ var git:(master) ✗ curl -i http://127.0.0.1:5001/north/getAll
HTTP/1.0 200 OK
Content-Type: application/json
Content-Length: 1580
Server: Werkzeug/0.15.2 Python/2.7.10
Date: Thu, 06 Jun 2019 06:21:01 GMT

{
"object": [
{
"abs_value": "",
"alarm_level": null,
"alarm_type": null,
"device_type": "UPS",
"guid": "6F9619FF-8B86-D011-B42D-00C04FC91211",
"id": 1,
"link": "/10301_1/10301_2/",
"name": "UPS-1",
"node_type": 2,
"path": "/ET3/A/A2-1/",
"percentage": null,
"point_type": null,
"tag": "10501_1",
"time": "Thu, 06 Jun 2019 11:48:37 GMT",
"unit": null
},
{
"abs_value": "",
"alarm_level": 2,
"alarm_type": 2,
"device_type": "PDU",
"guid": "6F9619FF-8B86-D011-B42D-00C04FC91211",
"id": 2,
"link": "",
"name": "PDU_U1",
"node_type": 3,
"path": "/ET3/A/A2-1/10501_1/",
"percentage": 30.0,
"point_type": 1,
"tag": "1001",
"time": "Thu, 06 Jun 2019 11:48:49 GMT",
"unit": "V"
}
],
"space": [
{
"guid": "6F9619FF-8B86-D011-B42D-00C04FC91220",
"id": 1,
"name": "A2-2 datahall",
"node_type": 2,
"parent_guid": "6F9619FF-8B86-D011-B42D-00C04FC91100",
"path": "/ET3/A/",
"space_type": "datahall",
"tag": "A2-2",
"time": "Wed, 05 Jun 2019 14:59:29 GMT"
},
{
"guid": "6F9619FF-8B86-D011-B42F-00C04FC91220",
"id": 2,
"name": "A2-3 datahall",
"node_type": 1,
"parent_guid": "6F9619FF-8B86-D011-B42D-00C04FC91100",
"path": "/ET3/A/",
"space_type": "datahall",
"tag": "A2-3",
"time": "Tue, 04 Jun 2019 16:06:17 GMT"
}
]
}``