sql表数据到图结构转换服务

项目框架:

        flask+vis.js

效果图：

服务demo：

        1、从sql表格数据中获取人物信息

        2、将人物关键字段作为扩展关系进行图结构化

        3、生成图结构化数据

        4、将结构化的图数据作为demo服务的数据使用:框架使用flask

bug修改：

        因后续图操作进行数据合并时，需要节点id唯一，所以不能按照节点顺序生成id。

        1、修改节点id,根据节点的分类和name生成唯一id

                生成方式：

                hashlib.md5(node.encode(encoding='utf-8')).hexdigest()

# D:\Program Files\Python36
# -*- coding: utf-8 -*-
# @Time    : 2022/7/20 14:56
# @Author  : wangjing
# @File    :yjzx //mysql_graph_show.py

import pymysql
import hashlib
import pandas as pd
from flask import Flask, request,jsonify
from flask_cors import *

app = Flask(__name__)
DEBUG = False
PORT = 8000
HOST = '0.0.0.0'
CORS(app, supports_credentials=True) #支持跨域访问

class GraphShow():
    def __init__(self):
        pass

    def create_page_front(self, relaPairs):
        nodes = []
        for relaPair in relaPairs:
            nodes.append(relaPair[0])
            nodes.append(relaPair[-1])
        nodes = list(set(nodes))
        node_dict = {node: hashlib.md5(node.encode(encoding='utf-8')).hexdigest() for index, node in enumerate(nodes)}
        print('===节点总数',len(nodes),nodes[:5])

        data_nodes = []
        data_edges = []
        for node, id in node_dict.items():
            data = {}
            data["id"] = id
            data["name"] = node.split('-')[0]
            data["category"] = node.split('-')[1]
            data_nodes.append(data)

        for edge in relaPairs:
            data = {}
            data['source'] = node_dict.get(edge[0])
            data['value'] = edge[1]
            data['target'] = node_dict.get(edge[-1])
            data_edges.append(data)
        return data_nodes, data_edges

class Sqltb_Proc():
    def __init__(self):
        self.cnx = pymysql.connect(host='127.0.0.1',user='root', password='******', db='db_name', charset='utf8mb4')
        self.table = 'persons'

    def read_tb(self,param_ids):
        with self.cnx.cursor() as cursor:
            table  =self.table
            cursor.execute(f'show full columns from {table}')  # 运行SOL语句
            comment_selects = cursor.fetchall() 
            print('======表头信息', [f'{i[0]}---{i[-1]}' for i in comment_selects])
            # Comment 为注释列
            table_columns = [f'{i[0]}-{i[-1]}' for i in comment_selects]
            sql = f'''select * from {table} where id in {tuple(param_ids)}'''
            sql = str(sql).replace(',)',')')
            print(sql)
            cursor.execute(sql)  # 查询表数据
            selects = cursor.fetchall()
            selects = list(selects)
            data = pd.DataFrame(selects, columns=[i[0] for i in comment_selects]).fillna('')
            print('===读取表格行数',len(data))
            return data,table_columns

    def proc_tb_with_param(self,param_ids):
        graph_show = GraphShow()
        relaPairs, filter_relaPairs = [], []
        df, table_columns = self.read_tb(param_ids)
        for i in (range(len(df))):
            row = df[df.index == i].to_dict()
            name = str(row['name'][i]).split('(')[0].strip()
            for col_idx in table_columns:
                col_comment = col_idx.split('-')[-1].strip()
                col = col_idx.split('-')[0]
                if col in ['work', 'company', 'school', 'department','birthplace','city','province']:
                    col_value = str(row[col][i]).strip()
                    values = []
                    for v in col_value.split(','):
                        values += [i.strip() for i in v.split('，') if len(i.strip())]
                    for pair in values:
                        idx = f'{name}-{pair}'
                        if idx not in filter_relaPairs:
                            filter_relaPairs.append(idx)
                            if col in ['birthplace','city','province']:
                                col = '地点'
                            relaPairs.append(["{}-{}".format(name,'领导'), col_comment, "{}-{}".format(pair,col_comment)])
        data_nodes, data_edges = graph_show.create_page_front(relaPairs)
        cates = []
        cates_list = list(set([i['category'] for i in data_nodes]))
        for i in cates_list:
            total = len([n for n in data_nodes if n['category'] == i])
            cates.append({'name':i,'total':total})
        return data_nodes, data_edges,cates

@app.route('/persons_graph/', methods=('GET', 'POST'))
def personGraph():
    resp_dict = {}
    graph = {}
    if request.method == 'POST':
        param_ids = request.json['ids']
        print(type(param_ids),param_ids,tuple(param_ids))
        handler = Sqltb_Proc()
        if len(param_ids):
            data_nodes, data_edges,category = handler.proc_tb_with_param(param_ids)
            graph['nodes'] = data_nodes
            graph['links'] = data_edges
            graph['category'] = category
            resp_dict['status'] = '200'
            resp_dict['message'] = '请求成功！'
            resp_dict['data'] = graph
        else:
            graph['nodes'] = []
            graph['links'] = []
            graph['category'] = []
            resp_dict['status'] = '202'
            resp_dict['message'] = '参数获取失败！'
            resp_dict['data'] = graph
    else:
        resp_dict['status'] = '202'
        resp_dict['message'] = '请求方式错误！！！'
        resp_dict['data'] = graph
    return jsonify(resp_dict)


if __name__ == '__main__':
    app.run(host=HOST,port=PORT,threaded=True)