#!/usr/bin/env python#-*- encoding: utf8 -*-import mysql.connectorimport sysimport reimport datetimeimport xlrd   # 定义要导出文件路径   my_dump_dir = "/data1/lgj/temp2/"# 配置信息文件，对比的时候，同一行源端和目标端要一一对应，对比的时候同一行作对比。# 源端   ： 第一列是IP， 第二列是账号， 第三列是DB， 第四列是密码， 第五列是端口号。# 目标端 ： 第七列是IP， 第八列是账号， 第九列是DB， 第十列是密码， 第十一列是端口号。dbinfo = "/data1/lgj/temp2/dbinfo.xlsx"# 生成create table 语句文件create_table_sql_file = my_dump_dir+"create_table_output_" + datetime.datetime.now().strftime('%Y_%m_%d')+".sql"# 生成alter table modify 语句文件alter_table_modify_sql_file = my_dump_dir+"alter_table_modify_output_" + datetime.datetime.now().strftime('%Y_%m_%d')+".sql"# 生成alter table add 语句文件alter_table_add_sql_file = my_dump_dir+"alter_table_add_output_" + datetime.datetime.now().strftime('%Y_%m_%d')+".sql"class DiffTable:    # fromdbconfig 参数是一个列表，是源端数据库， todbconfig 参数也是一个列表，是目标端数据库（也就是要修改的数据库），第一列是IP，第二列是DB，第三列是账号，第四列是密码，第五列是端口号。    def __init__(self, fromdbconfig,todbconfig):        self.fromdbconfig = fromdbconfig        self.todbconfig = todbconfig          def _get_sql_info(self, sql, host, db, user, password, port):        cnn = mysql.connector.connect(host=host, db=db, user=user, password=password,port=port)        cursor = cnn.cursor()        cursor.execute(sql)        return cursor.fetchall()     # 获取指定数据库source_list 里面存在，但是target_list不存在的对象，获取这些表，用于生成create table 语句。    def generate_create_statement(self):        select_source_sql = "select table_name from information_schema.tables where table_schema='%s' order by table_name;" %(self.fromdbconfig[1])        select_target_sql = "select table_name from information_schema.tables where table_schema='%s' order by table_name;" %(self.todbconfig[1])		        return_get_source_table_name = self._get_sql_info(select_source_sql, self.fromdbconfig[0], self.fromdbconfig[1], self.fromdbconfig[2], self.fromdbconfig[3], self.fromdbconfig[4])               return_get_target_table_name = self._get_sql_info(select_target_sql, self.todbconfig[0], self.todbconfig[1], self.todbconfig[2], self.todbconfig[3], self.todbconfig[4])        create_table_ret_list = []        for i in range(len(return_get_source_table_name)):            if return_get_source_table_name[i] not in return_get_target_table_name:                create_table_ret_list.append(return_get_source_table_name[i])        cnn = mysql.connector.connect(host=self.fromdbconfig[0], db=self.fromdbconfig[1], user=self.fromdbconfig[2], password=self.fromdbconfig[3],port=self.fromdbconfig[4])         cursor = cnn.cursor()        for item in range(len(create_table_ret_list)):             show_create_table_sql = "show create table %s;" % (create_table_ret_list[item][0])             cursor.execute(show_create_table_sql)            for i  in cursor:                create_table_name = i[1] + i[1].join(u';')                create_table_name_statement = "\r\nuse  %s;\r\n%s \r\n"  %(self.todbconfig[1],create_table_name)                               with open(create_table_sql_file,"aw") as f:                    f.write(create_table_name_statement.encode('utf-8'))  		            # 获取指定数据库source_list 里面存在，但是 get_create_table_list 不存在的对象，获取这些表，用于生成 alter table  语句，获取的内容中既有可能有alter table add ，也有可能有alter table modify 。    def get_alter_table_list(self):        select_source_sql = "select table_name from information_schema.tables where table_schema='%s' order by table_name;" %(self.fromdbconfig[1])        select_target_sql = "select table_name from information_schema.tables where table_schema='%s' order by table_name;" %(self.todbconfig[1])		        return_get_source_table_name = self._get_sql_info(select_source_sql, self.fromdbconfig[0], self.fromdbconfig[1], self.fromdbconfig[2], self.fromdbconfig[3], self.fromdbconfig[4])               return_get_target_table_name = self._get_sql_info(select_target_sql, self.todbconfig[0], self.todbconfig[1], self.todbconfig[2], self.todbconfig[3], self.todbconfig[4])            alter_table_ret_list = []                for i in range(len(return_get_source_table_name)):            if return_get_source_table_name[i] in return_get_target_table_name:                if return_get_source_table_name[i] not in  alter_table_ret_list:                               alter_table_ret_list.append(return_get_source_table_name[i])         return alter_table_ret_list    def get_alter_statement(self, host, db, user, password, port ,table_name):        result_tables={}                cnn = mysql.connector.connect(host=host, db=db, user=user, password=password,port=port)        cursor = cnn.cursor()        for item in range(len(table_name)):             result_columns=[]                    show_create_table_sql = "show create table %s;" % (table_name[item][0])            cursor.execute(show_create_table_sql)                  	    for j in cursor:                j_1 = re.split('CREATE TABLE',j[1])[1]                j_2 = re.split('` \(\n',j_1,1)[1]                j_3 = re.split('\n\) ENGINE',j_2)[0]                  for j_4 in re.split(',\n',j_3):                                if j_4.startswith('  KEY') or j_4.startswith('  UNIQUE KEY') or j_4.startswith('  KEY'):                                                              pass                    else:                        if j_4 not in result_columns:                            result_columns.append(j_4)                                                    result_tables[table_name[item][0]]=result_columns        return result_tables        			# 获取生成alter table的相关语句，并输出到文件    def generate_alter_statement(self):         source_alter_statement = self.get_alter_statement(self.fromdbconfig[0], self.fromdbconfig[1], self.fromdbconfig[2], self.fromdbconfig[3], self.fromdbconfig[4], self.get_alter_table_list())               target_alter_statement = self.get_alter_statement(self.todbconfig[0], self.todbconfig[1], self.todbconfig[2], self.todbconfig[3], self.todbconfig[4], self.get_alter_table_list())                #保存目标端列，键是列名，值是 target_column_info 用于后续判断源端列是否存在于源端表中。        target_column_info_dict = {}        		        #保存需要生成alter alter add 语句的列，键是列名，值是列，这部分数据来源于源端表。        source_alter_add_column = {}        #保存需要生成alter alter modify 语句的列，键是列名，值是列，这部分数据来源于源端表        source_alter_modify_column = {}        for key in source_alter_statement:            #保存目标端列，用于后续判断源端列是否存在于源端表中。            target_column_info = []	            #保存目标端列名，用于后续判断源端列名是否存在于源端表中。                       target_column_append = []                #保存需要生成alter alter modify 语句的列，这部分数据来源于源端表。            source_alter_add_name = []	            #保存需要生成alter alter modify 语句的列，这部分数据来源于源端表。                     source_alter_modify_name = []                          for j in range(len(target_alter_statement[key])):                if re.split(r'`',target_alter_statement[key][j])[1] not in 	target_column_append:                    target_column_append.append(re.split(r'`',target_alter_statement[key][j])[1])                if target_alter_statement[key][j] not in target_column_info:                     target_column_info.append(target_alter_statement[key][j])					            for i in range(len(source_alter_statement[key])):                # 判断源端的列名在目标端是否存在，如果不存在，就保存在 source_alter_add_column 字典中。                if re.split(r'`',source_alter_statement[key][i])[1] not in target_column_append:                    if re.sub(r',$','',source_alter_statement[key][i]) not in source_alter_add_name:                        source_alter_add_name.append(re.sub(r',$','',source_alter_statement[key][i]))                                    # 如果存在，再比较列信息是否一致，这部分信息保存在 source_alter_modify_name 字典中。				                else:                    if source_alter_statement[key][i] not in target_column_info:                        if re.sub(r',$','',source_alter_statement[key][i]) not in source_alter_modify_name:                                source_alter_modify_name.append(re.sub(r',$','',source_alter_statement[key][i]))                                    source_alter_add_column[key] = source_alter_add_name            source_alter_modify_column[key] = source_alter_modify_name        # 生成alter table add语句到指定的文件         for key in source_alter_add_column: 	            for item in range(len(source_alter_add_column[key])):                alter_table_add = source_alter_add_column[key][item] + source_alter_add_column[key][item].join(u';')                alter_table_add_statement = "\r\nuse  %s;\r\n alter table %s add %s \r\n"  %(self.fromdbconfig[1],key,alter_table_add)                                   with open(alter_table_add_sql_file,"aw") as f:                    f.write(alter_table_add_statement.encode('utf-8'))                			        # 生成alter table add语句到指定的文件        for key in source_alter_modify_column: 	            for item in range(len(source_alter_modify_column[key])):                alter_table_modify = source_alter_modify_column[key][item] + source_alter_modify_column[key][item].join(u';')                alter_table_modify_statement = "\r\nuse  %s;\r\n alter table %s modify %s \r\n"  %(self.fromdbconfig[1],key,alter_table_modify)                with open(alter_table_modify_sql_file,"aw") as f:                    f.write(alter_table_modify_statement.encode('utf-8'))					class GetConn:      def __init__(self,fromfile):        self.fromfile = fromfile    def get_csv(self):        csv_file = xlrd.open_workbook(self.fromfile,'rb')        csv_sheet = csv_file.sheet_by_index(0)        nrows = csv_sheet.nrows        csv_data = []        for i in range(nrows):            csv_row = csv_sheet.row_values(i)            csv_data.append(csv_row)        return csv_data                   checkdbconfig = GetConn(dbinfo).get_csv()         for i in range(len(checkdbconfig)):    fromdbconfig_from = [checkdbconfig[i][0],checkdbconfig[i][2],checkdbconfig[i][1],checkdbconfig[i][3],int(checkdbconfig[i][4])]    todbconfig_to = [checkdbconfig[i][6],checkdbconfig[i][8],checkdbconfig[i][7],checkdbconfig[i][9],int(checkdbconfig[i][10])]    create_table_result = DiffTable(fromdbconfig_from,todbconfig_to).generate_create_statement()    alter_table_result  = DiffTable(fromdbconfig_from,todbconfig_to).generate_alter_statement()