diff_mysql_table_exec.py
mysql Table py exec Diff
2023-09-27 14:26:42 时间
#!/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 = "\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()
相关文章
- Mysql误删了root用户怎么办
- JAVAEE之-----MySQL分页技术(带搜索)
- mysql服务器参数
- MySQL无法启动报 Error: could not open single-table tablespace file ./mysql/innodb_table_sta
- RDBMS SQL 编辑器 | MySQL、Oracle、MariaDB、SQLsever、SQLite、PostgreSQL
- 数千台MySQL数据库遭黑客比特币勒索,该怎么破?
- MySQL: 表连接与where
- MySQL: Common Table Expression (CTE)
- mysql出现“ You can't specify target table '表名' for update in FROM clause”解决方法
- 修复MySQL的MyISAM表命令check table用法
- How can you determine how much disk space a particular MySQL table is taking up?
- 安装mysql中遇到的问题1
- MySQL查看触发器
- mysql Waiting for table flush
- 基于JSP+MySQL实现(Web)校园网上订餐系统【100010323】
- mysql更新字段值提示You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode
- 转 mysql的恢复过程非常慢
- mysql 1093 - You can't specify target table 'xx表' for update in FROM clause
- 【转】Linux学习---CentOS 7编译安装MySQL 8.0
- MySQL具体解释(13)------------事务
- The total number of locks exceeds the lock table size,mysql update报错
- mysql5.5无法启动,Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 问题的解决方法
- Mysql error.log报错:Error: Table “mysql”.“innodb_table_stats” not found
- MySQL管理员须知的两大漏洞与修复方法
- MySql 数据备份
- MySQL DELETE语句和TRUNCATE TABLE语句的区别
- 关于mysql与sslv3的一些记录
- MySQL基本SQL语句4(DCL)
- 利用Mysql存储过程造百万级数据