#!/usr/bin/env python
#-*- encoding: utf8 -*-
import mysql.connector
# 定義要導(dǎo)出文件目錄
work_dir="/data/python/"
class DiffTable:
# fromdbconfig 參數(shù)是一個列表，是源端數(shù)據(jù)庫， todbconfig 參數(shù)也是一個列表，是目標(biāo)端數(shù)據(jù)庫（也就是要修改的數(shù)據(jù)庫），第一列是IP，第二列是DB，第三列是賬號，第四列是密碼，第五列是端口號。
def __init__(self, fromdbconfig,todbconfig):
self.fromdbconfig = fromdbconfig
self.todbconfig = todbconfig
# 獲取源端數(shù)據(jù)庫表名，輸出結(jié)果為列表
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
# 獲取目標(biāo)端源端數(shù)據(jù)庫表名，輸出結(jié)果為列表
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
# 獲取指定數(shù)據(jù)庫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
# 獲取指定數(shù)據(jù)庫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
# 獲取數(shù)據(jù)庫表的create table 信息，輸出結(jié)果為字典，表名是key，對應(yīng)表create table語句為 value ，用于創(chuàng)建目標(biāo)端沒有的表。
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
# 獲取源端數(shù)據(jù)庫表信息，輸出結(jié)果為字典，表名為key，表信息為value，用于后續(xù)對比源端和目標(biāo)端表結(jié)構(gòu)是否異同。
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
# 獲取目標(biāo)數(shù)據(jù)庫表信息，輸出結(jié)果為字典，表名為key，表信息為value，用于后續(xù)對比源端和目標(biāo)端表結(jié)構(gòu)是否異同。
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 函數(shù)和 get_target_column_info 函數(shù)獲取結(jié)果集，比較兩邊表，得出差異的部分，后續(xù)再對此部分進(jìn)行生成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:
# 這部分?jǐn)?shù)據(jù)用于生成 alter table modify 語句
if i not in source_alter_table_midify:
source_alter_table_midify.append(i)
else:
# 這部分?jǐn)?shù)據(jù)用于生成 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
# 此函數(shù)用來打印輸出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)
