common_dao.py

'''
@Description: 通用数据操作类
@Author: guohb65
@Email: guohb65@chinaunicom.cn
@Date: 2019-12-10 09:15:07
@LastEditTime : 2019-12-18 14:30:58
@LastEditors  : guohb65
'''

import json
import uuid

from .common_func import CommonFunc
from .const import ConstGen

COUNTS = "counts"


class CommonDAO(object):

    def simple_add(self, unique_data, others_data, table_name, primary_key="uid", primary_value=None):
        """
        新增
        :param primary_key:
        :param table_name:
        :param others_data:
        :param unique_data:
        :return:
        """
        if self.data_is_exist(unique_data, table_name):
            return CommonFunc.gen_inner_resp_data(ConstGen.FAIL_CODE, "名称重复")
        else:
            insert_data = dict(unique_data, **others_data)
            trans_data = self.gen_insert_sql(
                insert_data, table_name, primary_key, primary_value)
            print(trans_data)
            ret = CommonFunc().request_trans_service(
                ConstGen.TRANS_SQL_URL, trans_data)
            return self.simple_handle_ret_data(ret)

    def simple_alter(self, update_data, update_condition, table_name):
        """
        修改
        :param update_condition:
        :param update_data:
        :param table_name:
        :return:
        """
        if ConstGen.UNIQUE_STR in update_data:
            unique_data = update_data[ConstGen.UNIQUE_STR]
            update_data.pop(ConstGen.UNIQUE_STR)
            update_data = dict(update_data, **unique_data)
            if self.data_is_exist_except_self(unique_data, update_condition, table_name):
                return CommonFunc.gen_inner_resp_data(ConstGen.FAIL_CODE, "名称重复")
        if ConstGen.OTHERS_STR in update_data:
            others_data = update_data[ConstGen.OTHERS_STR]
            update_data.pop(ConstGen.OTHERS_STR)
            update_data = dict(update_data, **others_data)
        trans_data = self.gen_update_sql(update_data, update_condition, table_name)
        if trans_data is None:
            return CommonFunc.gen_inner_resp_data(ConstGen.FAIL_CODE, "更新参数传输异常")
        else:
            ret = CommonFunc().request_trans_service(ConstGen.TRANS_SQL_URL, trans_data)
            return self.simple_handle_ret_data(ret)

    def simple_delete(self, delete_condition, table_name):
        """
        删除
        :param delete_condition:
        :param table_name:
        :return:
        """
        trans_data = self.gen_delete_sql(delete_condition, table_name)
        if trans_data is None:
            return CommonFunc.gen_inner_resp_data(ConstGen.FAIL_CODE, "删除数据参数传输异常")
        else:
            ret = CommonFunc().request_trans_service(
                ConstGen.TRANS_SQL_URL, trans_data)
            return self.simple_handle_ret_data(ret)

    def simple_query(self, request, table_name):
        """
        信息查询
        :param table_name:
        :param request:
        :return:
        """
        data_params = CommonFunc().request_param2dict(request)
        query_condition = data_params.get(ConstGen.CONDITION_STR)
        trans_data = self.gen_query_all_sql(query_condition, None, table_name)
        if trans_data is None:
            return CommonFunc.gen_inner_resp_data(ConstGen.FAIL_CODE, "查询条件不存在")
        else:
            ret_data = CommonFunc().request_trans_service(
                ConstGen.STANDER_SQL_URL, trans_data)
            condition = json.loads(query_condition)
            page_num = None
            page_size = None
            if ConstGen.PAGE_STR in condition:
                page_info = condition.get(ConstGen.PAGE_STR)
                if ConstGen.PAGE_NUM_STR in page_info:
                    page_num = page_info[ConstGen.PAGE_NUM_STR]
                if ConstGen.PAGE_SIZE_STR in page_info:
                    page_size = page_info[ConstGen.PAGE_SIZE_STR]
            if page_num is not None and page_size is not None:
                ret_data = json.loads(ret_data)
                start_index = (int(page_num) - 1) * int(page_size)
                if int(ret_data[ConstGen.ROWCOUNT_STR]) < start_index:
                    ret_data.pop(ConstGen.DATA_ROWS_STR)
                    ret_data[ConstGen.PAGE_COUNT_STR] = "0"
                else:
                    if ConstGen.DATA_ROWS_STR in ret_data:
                        all_data_rows = ret_data[ConstGen.DATA_ROWS_STR]
                        data_rows = all_data_rows[start_index: start_index +
                                                               int(page_size)]
                        ret_data[ConstGen.DATA_ROWS_STR] = data_rows
                        ret_data[ConstGen.PAGE_COUNT_STR] = len(data_rows)
                    else:
                        ret_data[ConstGen.PAGE_COUNT_STR] = '0'
            return ret_data

    @staticmethod
    def simple_handle_ret_data(ret):
        """
        返回结果处理
        :param ret:
        :return:
        """
        if ret[ConstGen.RET_CODE] is ConstGen.SUCCESS_CODE:
            ret.pop(ConstGen.DATA_ROWS_STR)
        return ret

    @staticmethod
    def package_sql(sql_key, sql_str):
        """
        封装传输的sql数据格式
        :param sql_str:
        :param sql_key:
        :return:
        """
        trans_sql_data = {}
        trans_data = {}
        if sql_key == ConstGen.TRANS_SQL_STR:
            trans_sql_data[ConstGen.TRANS_SQL_STR] = sql_str
            trans_data[ConstGen.TRANS_JSON_ARRAY_STR] = json.dumps(
                [trans_sql_data])
        if sql_key == ConstGen.SQL_STR:
            trans_data[ConstGen.SQL_STR] = sql_str
        return trans_data

    @staticmethod
    def package_write_sql_list(sql_list):
        """
        封装传输的sql数据格式
        :param sql_str:
        :param sql_key:
        :return:
        """
        return {ConstGen.TRANS_JSON_ARRAY_STR: json.dumps(sql_list)}

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

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

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

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

        if update_condition:
            update_sql = "update " + table_name + " set "
            for k in update_data:
                update_sql += k + "='" + self.reverse_injection_conversion(update_data[k]) + "',"
            update_sql = update_sql[:-1]
            update_sql += " where "
            for k in update_condition:
                update_sql += k + "='" + self.reverse_injection_conversion(update_condition[k]) + "' and "
            update_sql += "1=1 "
            print(update_sql)
            return self.package_sql(ConstGen.TRANS_SQL_STR, update_sql)
        else:
            return None

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

        if update_condition:
            update_sql = "update " + table_name + " set "
            for k in update_data:
                update_sql += k + "='" + self.reverse_injection_conversion(update_data[k]) + "',"
            update_sql = update_sql[:-1]
            update_sql += " where "
            for k in update_condition:
                update_sql += k + "='" + self.reverse_injection_conversion(update_condition[k]) + "' and "
            update_sql += "1=1 "
            print(update_sql)
            trans_sql_data = {ConstGen.TRANS_SQL_STR: update_sql}
            return trans_sql_data
        else:
            return None

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

    def gen_delete_transsql(self, delete_condition, table_name):
        """
        数据更新的sql语句生成
        :param delete_condition:
        :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 "
            print(delete_sql)
            trans_sql_data = {ConstGen.TRANS_SQL_STR: delete_sql}
            return trans_sql_data
        else:
            return None

    def gen_where_sql_substring(self, sql_str, query_condition, order_dict):
        """
        生成where后的sql语句
        :return:
        """
        if query_condition is not None:
            sql_str += " where "
            if ConstGen.EXACT_STR in query_condition:
                exact_condition = query_condition.get(ConstGen.EXACT_STR)
                for k in exact_condition:
                    sql_str += k + "='" + self.reverse_injection_conversion(exact_condition[k]) + "' and "
            if ConstGen.FUZZY_STR in query_condition:
                fuzzy_condition = query_condition.get(ConstGen.FUZZY_STR)
                for j in fuzzy_condition:
                    sql_str += j + " like '%" + \
                               self.reverse_injection_conversion(fuzzy_condition[j]) + "%' and "
            sql_str += "1=1 "
            if order_dict is not None:
                sql_str += "order by "
                for order_key in order_dict:
                    sql_str += order_key + " " + self.reverse_injection_conversion(order_dict[order_key]) + ","
                sql_str += "1"
        return sql_str

    def gen_query_all_sql(self, query_condition, order_key, table_name):
        """
        数据查询所有字段的sql语句生成
        :param order_key:
        :param query_condition:
        :param table_name:
        :return:
        """
        select_all_sql = "select * from " + table_name
        final_sql = self.gen_where_sql_substring(
            select_all_sql, query_condition, order_key)
        return final_sql

    def gen_query_sql(self, query_condition, order_dict, table_name, query_key_list=None):
        """
        数据查询的sql语句生成
        :param query_condition:
        :param order_dict:
        :param table_name:
        :param query_key_list:
        :return:
        """
        if query_key_list is None:
            return self.gen_query_all_sql(query_condition, order_dict, table_name)
        else:
            select_all_sql = "select "
            for key in query_key_list:
                select_all_sql += key + ","
            select_all_sql = select_all_sql[0:-1] + " from " + table_name
        final_sql = self.gen_where_sql_substring(
            select_all_sql, query_condition, order_dict)
        return final_sql

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

        select_sql = "select count(*) as " + COUNTS + \
                     " from " + table_name + " where "
        for k in data:
            select_sql += k + "='" + self.reverse_injection_conversion(data[k]) + "' and "
        select_sql += "1=1 "
        trans_data = self.package_sql(ConstGen.SQL_STR, select_sql)
        ret_dict = CommonFunc().request_trans_service(
            ConstGen.STANDER_SQL_URL, trans_data)
        if ret_dict[ConstGen.RET_CODE] is ConstGen.SUCCESS_CODE:
            counts = int(ret_dict[ConstGen.DATA_ROWS_STR][0][COUNTS])
            if counts <= 0:
                return False
            else:
                return True
        else:
            return None

    def data_is_exist_except_self(self, data, except_data, table_name):
        """
        判断数据表中是否存在该数据
        :param data:
        :param table_name:
        :return:
        """

        select_sql = "select count(*) as " + COUNTS + \
                     " from " + table_name + " where "
        for k in data:
            select_sql += k + "='" + self.reverse_injection_conversion(data[k]) + "' and "
        for k in except_data:
            select_sql += k + "!='" + self.reverse_injection_conversion(except_data[k]) + "' and "
        select_sql += "1=1 "
        trans_data = self.package_sql(ConstGen.SQL_STR, select_sql)
        print(trans_data)
        ret_dict = CommonFunc().request_trans_service(
            ConstGen.STANDER_SQL_URL, trans_data)
        if ret_dict[ConstGen.RET_CODE] is ConstGen.SUCCESS_CODE:
            counts = int(ret_dict[ConstGen.DATA_ROWS_STR][0][COUNTS])
            if counts <= 0:
                return False
            else:
                return True
        else:
            return None

    @staticmethod
    def reverse_injection_conversion(params):
        return params.replace("\\", "\\\\").replace("'", "\\'")