#!/usr/bin/env python
#-*- encoding: utf8 -*-
import mysql.connector
# 定义要导出文件目录
work_dir="/data/python/"
class DiffTable:
# fromdbconfig 参数是一个列表，是源端数据库， todbconfig 参数也是一个列表，是目标端数据库（也就是要修改的数据库），第一列是IP，第二列是DB，第三列是账号，第四列是密码，第五列是端口号。
def __init__(self, fromdbconfig,todbconfig):
self.fromdbconfig = fromdbconfig
self.todbconfig = todbconfig
# 获取源端数据库表名，输出结果为列表
def get_source_table_name(self):
select_tables_sql = "select table_name from information_schema.tables where table_schema='%s' order by table_name;" %(self.fromdbconfig[1])
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()
cursor.execute(select_tables_sql)
source_table_result = cursor.fetchall()
return source_table_result
# 获取目标端源端数据库表名，输出结果为列表
def get_target_table_name(self):
select_tables_sql = "select table_name from information_schema.tables where table_schema='%s' order by table_name;" %(self.todbconfig[1])
cnn = mysql.connector.connect(host=self.todbconfig[0], db=self.todbconfig[1], user=self.todbconfig[2], password=self.todbconfig[3],port=self.todbconfig[4])
cursor = cnn.cursor()
cursor.execute(select_tables_sql)
target_table_result = cursor.fetchall()
return target_table_result
# 获取指定数据库source_list 里面存在，但是target_list不存在的对象，获取这些表，用于生成create table 语句。
def get_create_table_list(self):
create_table_ret_list = []
for i in range(len(self.get_source_table_name())):
if self.get_source_table_name()[i] not in self.get_target_table_name():
create_table_ret_list.append(self.get_source_table_name()[i])
return create_table_ret_list
# 获取指定数据库source_list 里面存在，但是 get_create_table_list 不存在的对象，获取这些表，用于生成alter table 语句。
def get_alter_table_list(self):
alter_table_ret_list = []
for i in range(len(self.get_source_table_name())):
if self.get_source_table_name()[i] in self.get_target_table_name():
alter_table_ret_list.append(self.get_source_table_name()[i])
return alter_table_ret_list
# 获取数据库表的create table 信息，输出结果为字典，表名是key，对应表create table语句为 value ，用于创建目标端没有的表。
def generate_create_statement(self):
from_table_name_list = []
from_table_name_dict = {}
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(self.get_create_table_list())):
show_create_table_sql = "show create table %s;" % (self.get_create_table_list()[item][0])
cursor.execute(show_create_table_sql)
result = cursor.fetchall()
from_table_name_list.append(result)
for i in from_table_name_list:
from_table_name_dict[i[0][0]] = i[0][1]+i[0][1].join(';')
return from_table_name_dict
# 获取源端数据库表信息，输出结果为字典，表名为key，表信息为value，用于后续对比源端和目标端表结构是否异同。
def get_source_column_info(self):
source_table_name_list = []
source_ret_dict = {}
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(self.get_alter_table_list())):
select_columns_sql = "select TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,COLUMN_COMMENT from information_schema.columns where table_schema='%s' and table_name='%s' order by table_name;" %(self.fromdbconfig[1],self.get_alter_table_list()[item][0])
cursor.execute(select_columns_sql)
result = cursor.fetchall()
source_table_name_list.append(result)
for i in range(len(source_table_name_list)):
source_ret_dict[source_table_name_list[i][0][0]] = source_table_name_list[i]
return source_ret_dict
# 获取目标数据库表信息，输出结果为字典，表名为key，表信息为value，用于后续对比源端和目标端表结构是否异同。
def get_target_column_info(self):
target_table_name_list = []
target_ret_dict = {}
target_null_list = []
cnn = mysql.connector.connect(host=self.todbconfig[0], db=self.todbconfig[1], user=self.todbconfig[2], password=self.todbconfig[3],port=self.todbconfig[4])
cursor = cnn.cursor()
for item in range(len(self.get_alter_table_list())):
select_columns_sql = "select TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,COLUMN_COMMENT from information_schema.columns where table_schema='%s' and table_name='%s' order by table_name;" %(self.fromdbconfig[1],self.get_alter_table_list()[item][0])
cursor.execute(select_columns_sql)
result = cursor.fetchall()
target_table_name_list.append(result)
while target_null_list in target_table_name_list:
target_table_name_list.remove(target_null_list)
for i in range(len(target_table_name_list)):
target_ret_dict[target_table_name_list[i][0][0]] = target_table_name_list[i]
return target_ret_dict
# 从 get_source_column_info 函数和 get_target_column_info 函数获取结果集，比较两边表，得出差异的部分，后续再对此部分进行生成alter table语句。
def generate_alter_statement(self):
source_column_list = []
source_alter_table_midify = []
source_alter_table_add = []
alter_statement_dict = {}
alter_statement_all = {}
alter_statement_modify = []
alter_statement_add = []
for key,value in self.get_source_column_info().viewitems():
for i in self.get_source_column_info()[key]:
for j in self.get_target_column_info()[key]:
if j[1] not in source_column_list:
source_column_list.append(j[1])
if i[0] == j[0]:
if i[1] == j[1]:
if i[2] == j[2] and i[3] == j[3] and i[4] == j[4]:
pass
else:
# 这部分数据用于生成 alter table modify 语句
if i not in source_alter_table_midify:
source_alter_table_midify.append(i)
else:
# 这部分数据用于生成 alter table add 语句
if i[1] not in source_column_list:
if i not in source_alter_table_add:
source_alter_table_add.append(i)
for i in range(len(source_alter_table_midify)):
alter_statement_modify.append("alter table %s modify %s %s DEFAULT '%s' COMMENT '%s';" %(source_alter_table_midify[i][0],source_alter_table_midify[i][1],source_alter_table_midify[i][2],source_alter_table_midify[i][3],source_alter_table_midify[i][4]))
for i in range(len(source_alter_table_add)):
alter_statement_add.append("alter table %s add %s %s DEFAULT '%s' COMMENT '%s';" %(source_alter_table_add[i][0],source_alter_table_add[i][1],source_alter_table_add[i][2],source_alter_table_add[i][3],source_alter_table_add[i][4]))
alter_statement_all['alter_modify'] = alter_statement_modify
alter_statement_all['alter_add'] = alter_statement_add
return alter_statement_all
# 此函数用来打印输出SQL文件
def out_result(create_table_dict,alter_table_dict):
def print_result(table_dict,sql_name):
sql_file = work_dir+'sql_'+sql_name+'_diff_mysql'+'.sql'
with open(sql_file,"aw") as f:
f.write(table_dict+"\n")
for key,value in create_table_dict.viewitems():
print_result(create_table_dict[key],"create_table")
for key,value in alter_table_dict.viewitems():
for i in range(len(alter_table_dict[key])):
print_result(alter_table_dict[key][i],"alter_table")
fromdbconfig_105 = ['192.168.56.105', 'liangdb', 'root', '123456', '3306']
todbconfig_106 = ['192.168.56.106', 'liangdb', 'root', '123456', '3306']
alter_table_result = DiffTable(fromdbconfig_105,todbconfig_106).generate_create_statement()
create_table_result = DiffTable(fromdbconfig_105,todbconfig_106).generate_alter_statement()
print_sql = (alter_table_result,create_table_result)
