datahandle_utility.py

'''
@Description: 数据处理工具类
@Author: guohb65
@Email: guohb65@chinaunicom.cn
@Date: 2019-01-21 20:12:02
@LastEditTime: 2019-07-04 09:37:54
@LastEditors: Please set LastEditors
'''

import json
import re
import uuid

from cucc_common_pkg import httptrans
from .const import ConstGen, ConstTableName, ConstColumn


class DatahandleUtility(object):

    def package_sql(self, sql_key, sql_str):
        """
        封装传输的sql数据格式
        :param sqlKey:
        :param sqlStr:
        :return:
        """
        trans_data = {}
        transsql_data = {}
        if sql_key == ConstGen.TRANSSQL_STR:
            transsql_data = self.trans_sql(sql_str)
            trans_data[ConstGen.TRANSJSONARRAY_STR] = json.dumps(
                [transsql_data])
        if sql_key == ConstGen.SQL_STR:
            trans_data[ConstGen.SQL_STR] = sql_str
        return trans_data

    def package_trans_list(self, trans_sql_list):
        """
        打包多个transsql
        :param sql_list:
        :return:
        """
        trans_data = {}
        transsql_data_list = []
        for sql in trans_sql_list:
            transsql_data = self.trans_sql(sql)
            transsql_data_list.append(transsql_data)
        trans_data[ConstGen.TRANSJSONARRAY_STR] = json.dumps(
            transsql_data_list)
        return trans_data

    def trans_sql(self, sql_str):
        """
        简单封装insert语句数据格式
        :param sqlKey:
        :param sqlStr:
        :return:
        """
        transsql_data = {}
        transsql_data[ConstGen.TRANSSQL_STR] = sql_str
        return transsql_data

    def gen_insert_sql(self, dict_data, table_name, uid=None):
        """
        参数字典转insertsql语句
        :param dict_data:
        :param table_name:
        :return:
        """
        if uid is None:
            uid = uuid.uuid1().__str__().replace('-', '')
        insert_sql = "insert into " + table_name + "(uid," \
                     + list(dict_data.keys()).__str__()[1:-1].replace('\'', '') \
                     + ")values('" + uid + "'," \
                     + list(dict_data.values()).__str__()[1:-1] + ")"

        return insert_sql
        # return self.package_sql(ConstGen.TRANSSQL_STR, insert_sql)

    def request_param2dict(self, request):
        """
        把请求参数封装到一个字典中
        :param request:
        :return:
        """
        arg_params = {}
        print(request.values)
        for k in request.values:
            arg_params[k] = request.values.get(k)
            print(arg_params)

        return arg_params

    def request_trans_service(self, url, params):
        """
        封装请求传输
        :param url:
        :param params:
        :return:
        """
        trans = httptrans.HttpTrans(None)
        try:
            ret = trans.post_encodedata(url, params, None)
        except BaseException as err:
            print('【' + url + '】请求异常:' + err)
            ret = {"RetCode": "0", "RetVal": err}
            return json.dumps(ret, ensure_ascii=False)
        else:
            return ret

    def data_is_exist(self, data, table_name, uid_data=None):
        """
        判断数据表中是否存在该数据
        :param data:
        :param table_name:
        :return:
        """

        select_sql = "select count(*) as counts from " + table_name + " where "
        for k in data:
            select_sql += k + "='" + data[k] + "' and "
        if uid_data is not None:
            for key in uid_data:
                select_sql += key + "!='" + uid_data[key] + "' and "
        select_sql += "1=1 "
        print(select_sql)
        trans_data = self.package_sql(ConstGen.SQL_STR, select_sql)
        print(trans_data)
        ret = self.request_trans_service(ConstGen.STANDERSQL_URL, trans_data)
        print(ret)
        ret_dict = json.loads(ret)
        print(ret_dict["DataRows"])
        counts = int(ret_dict["DataRows"][0]["counts"])
        print(counts)
        if counts <= 0:
            return False
        else:
            return True

    def gen_update_sql(self, update_data, update_condition, table_name):
        """
        数据更新的sql语句生成
        :param updateData:
        :param updateCondition:
        :param table_name:
        :return:
        """

        if update_condition:
            unique_data = update_data[ConstGen.UNIQUE_STR]
            others_data = update_data[ConstGen.OTHERS_STR]
            data_array = dict(unique_data, **others_data)
            update_sql = "update " + table_name + " set "
            for k in data_array:
                update_sql += k + "='" + data_array[k] + "',"
            update_sql = update_sql[:-1]
            update_sql += " where "
            for k in update_condition:
                update_sql += k + "='" + update_condition[k] + "' and "
            update_sql += "1=1 "
            return update_sql
        else:
            return None

    def gen_delete_sql(self, delete_condition, table_name):
        """
        数据更新的sql语句生成
        :param deleteCondition:
        :param table_name:
        :return:
        """
        if delete_condition:
            delete_sql = "delete from " + table_name + " where "
            for k in delete_condition:
                delete_sql += k + "='" + delete_condition[k] + "' and "
            delete_sql += "1=1 "
            return delete_sql
        else:
            return None

    def gen_select_all_sql(self, selet_condition, table_name):
        """
        数据查询的sql语句生成
        :param seletCondition:
        :param table_name:
        :return:
        """
        if ConstGen.CONDITION_STR in selet_condition.keys():
            condition = json.loads(selet_condition.get(ConstGen.CONDITION_STR))

            select_all_sql = "select * from " + table_name + " where "
            if ConstGen.EXACT_STR in condition:
                exact_condition = condition.get(ConstGen.EXACT_STR)
                for k in exact_condition:
                    select_all_sql += k + "='" + exact_condition[k] + "' and "
            if ConstGen.FUZZY_STR in condition:
                fuzzy_condition = condition.get(ConstGen.FUZZY_STR)
                for j in fuzzy_condition:
                    select_all_sql += j + " like '%" + fuzzy_condition[j] + "%' and "
            select_all_sql += "1=1 "
            if ConstGen.SORT_STR in condition:
                sort_condition = condition.get(ConstGen.SORT_STR)
                select_all_sql += " order by "
                for i in sort_condition:
                    select_all_sql += i + " " + \
                                      ConstGen.SORT_DICT[sort_condition[i]] + ", "
                select_all_sql += " 1 "

            print(select_all_sql)
            return self.package_sql(ConstGen.SQL_STR, select_all_sql)
        else:
            return None

    def exec_query_sql(self, sql_string):
        """
        执行sql查询语句
        :param seletCondition:
        :param table_name:
        :return:
        """
        print(sql_string)
        trans_data = self.package_sql(
            ConstGen.SQL_STR, sql_string)
        print(trans_data)
        return self.request_trans_service(
            ConstGen.STANDERSQL_URL, trans_data)

    def exec_alter_sql(self, sql_string):
        """
        执行sql写类型语句
        :param seletCondition:
        :param table_name:
        :return:
        """
        print(sql_string)
        transsql_data = {}
        trans_data = {}
        transsql_data[ConstGen.TRANSSQL_STR] = sql_string
        trans_data[ConstGen.TRANSJSONARRAY_STR] = json.dumps([transsql_data])
        return self.request_trans_service(
            ConstGen.TRANSSQL_URL, trans_data)

    def query_k8s_info(self, k8s_platform_uid):
        """
        查询kubernetes信息
        :param seletCondition:
        :param table_name:
        :return:
        """
        query_sql = "select platform_ip,platform_port,platform_uri from " + \
                    ConstTableName.TB_INFRA_PLATFORM + " where uid='" + k8s_platform_uid + "'"
        return self.exec_query_sql(query_sql)

    def query_cluster_info(self, cluster_uid):
        """
        查询集群信息
        :param seletCondition:
        :param table_name:
        :return:
        """
        query_sql = "SELECT  platform_uid,cluster_name, kube_token, kube_ca_crt, kube_client_crt, kube_client_key from " + \
                    ConstTableName.TB_INFRA_PLATFORM_CLUSTER + " where uid='" + cluster_uid + "'"
        ret_val = json.loads(self.exec_query_sql(query_sql))
        if ret_val.get(ConstGen.RETCODE) != '0' and ret_val.get(ConstGen.ROWCOUNT_STR) != '0':
            cluster_info = ret_val.get(ConstGen.DATAROWS_STR)[0]
            return cluster_info
        else:
            return None

    def get_cluster_name(self, cluster_uid):
        """
        获取集群的名称
        :param seletCondition:
        :param table_name:
        :return:
        """
        cluster_info = self.query_cluster_info(cluster_uid)
        if cluster_info is not None:
            return cluster_info.get(ConstColumn.COL_CLUSTER_NAME)

    def get_cluster_token(self, cluster_uid):
        """
        获取集群的token
        :param seletCondition:
        :param table_name:
        :return:
        """
        cluster_info = self.query_cluster_info(cluster_uid)
        if cluster_info is not None:
            return cluster_info.get(ConstColumn.COL_KUBE_TOKEN)

    def get_platform_uid(self, cluster_uid):
        """
        获取集群的平台UID
        :param seletCondition:
        :param table_name:
        :return:
        """
        cluster_info = self.query_cluster_info(cluster_uid)
        if cluster_info is not None:
            return cluster_info.get(ConstColumn.COL_PLATFORM_UID)

    def get_k8s_server_ip(self, k8s_platform_uid):
        """
        获取k8s的IP地址
        :param seletCondition:
        :param table_name:
        :return:
        """
        k8s_info = json.loads(self.query_k8s_info(
            k8s_platform_uid)).get(ConstGen.DATAROWS_STR)[0]
        return k8s_info.get(ConstColumn.COL_PLATFORM_IP)

    def get_k8s_server_port(self, k8s_platform_uid):
        """
        获取k8s的端口
        :param seletCondition:
        :param table_name:
        :return:
        """
        k8s_info = json.loads(self.query_k8s_info(
            k8s_platform_uid)).get(ConstGen.DATAROWS_STR)[0]
        return k8s_info.get(ConstColumn.COL_PLATFORM_PORT)

    def get_k8s_server_uri(self, k8s_platform_uid):
        """
        获取k8s的Uri
        :param seletCondition:
        :param table_name:
        :return:
        """
        k8s_info = json.loads(self.query_k8s_info(
            k8s_platform_uid)).get(ConstGen.DATAROWS_STR)[0]
        return k8s_info.get(ConstColumn.COL_PLATFORM_URI)

    def get_k8s_api_server_addr(self, k8s_platform_uid):
        """
        获取k8s的Api地址
        :param seletCondition:
        :param table_name:
        :return:
        """
        ret_val = json.loads(self.query_k8s_info(k8s_platform_uid))
        if ret_val.get(ConstGen.ROWCOUNT_STR) != '0':
            k8s_info = ret_val.get(ConstGen.DATAROWS_STR)[0]
            k8s_ip = k8s_info.get(ConstColumn.COL_PLATFORM_IP)
            if re.match(r"^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$",
                        k8s_ip):
                if k8s_ip is not None:
                    api_server_addr = ConstGen.HTTPS_STR + k8s_ip
                    k8s_port = k8s_info.get(ConstColumn.COL_PLATFORM_PORT)
                    if k8s_port is not None:
                        k8s_port = ":" + k8s_port
                        api_server_addr = api_server_addr + k8s_port
                    k8s_uri = k8s_info.get(ConstColumn.COL_PLATFORM_URI)
                    if k8s_uri is not None and k8s_uri != "":
                        k8s_uri = ConstGen.BACKSLASH + k8s_uri
                        api_server_addr = api_server_addr + k8s_uri
                    return api_server_addr
                else:
                    return None
            else:
                return None
        else:
            return None

    def get_json_from_jsonpath(self, jsonpath_list):
        """
        根据jsonpath生成json数据格式的封装服务
        :param seletCondition:
        :param table_name:
        :return:
        """
        trans_data = {}
        trans_data[ConstGen.TRANSJSONARRAY_STR] = jsonpath_list
        ret_data = self.request_trans_service(
            ConstGen.JSONPATH_TO_JSON_URL, trans_data)
        json_data = json.loads(ret_data)
        if json_data[ConstGen.RETCODE] != '0':
            if ConstGen.RETVAL in json_data:
                data = json_data[ConstGen.RETVAL]
                return data
            else:
                return json_data
        else:
            return json_data