diff_mysql_table_exec.py

#!/usr/bin/env python
#-*- encoding: utf8 -*-

import mysql.connector
import sys
import re
import datetime
import 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 = "
use  %s;
%s 
"  %(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('` (
',j_1,1)[1]
                j_3 = re.split('
) ENGINE',j_2)[0]  
                for j_4 in re.split(',
',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 = "
use  %s;
 alter table %s add %s 
"  %(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 = "
use  %s;
 alter table %s modify %s 
"  %(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()