当前位置：首页 > news >正文

mysql转达梦的python脚本

news 来源：原创 2024/5/20 11:39:36

mysql_ddl：

CREATE TABLE `ops_app_import` (`id` char(32) NOT NULL COMMENT '主键ID',`pkg_name` varchar(255) NOT NULL DEFAULT '' COMMENT '导入包名称',`pkg_md5` varchar(32) NOT NULL COMMENT '导入包md5值',`backup_pkg_name` varchar(255) DEFAULT '' COMMENT '备份包名称',`backup_pkg_md5` varchar(32) DEFAULT '' COMMENT '备份包md5值',`process_status` varchar(32) DEFAULT '' COMMENT '处理状态',`comment` varchar(255) NOT NULL DEFAULT '' COMMENT '描述',`creator_id` char(32) DEFAULT '0' COMMENT '创建者ID',`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='应用导入包';

dameng_ddl：

CREATE TABLE "ops_app_import" ("id" char(32) NOT NULL COMMENT '主键ID',"pkg_name" varchar(255) NOT NULL DEFAULT '' COMMENT '导入包名称',"pkg_md5" varchar(32) NOT NULL COMMENT '导入包md5值',"backup_pkg_name" varchar(255) DEFAULT '' COMMENT '备份包名称',"backup_pkg_md5" varchar(32) DEFAULT '' COMMENT '备份包md5值',"process_status" varchar(32) DEFAULT '' COMMENT '处理状态',"comment" varchar(255) NOT NULL DEFAULT '' COMMENT '描述',"creator_id" char(32) DEFAULT '0' COMMENT '创建者ID',"gmt_create" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',"gmt_modified" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '修改时间',PRIMARY KEY ("id")
);

脚本：

index_sql_list = []def replaceKey(line, currTable):indexColumnStart = line.index("(")indexColumnEnd = line.index(")")if (line.lstrip().upper().startswith("KEY")):indexSql = f"CREATE INDEX {line.lstrip()[4: indexColumnStart - 2]} ON {currTable}({line[indexColumnStart + 1: indexColumnEnd]});"else:indexSql = f"CREATE UNIQUE INDEX {line.lstrip()[11: indexColumnStart - 2]} ON {currTable}({line[indexColumnStart + 1: indexColumnEnd]});"index_sql_list.append(indexSql)def getTable(line):indexCreate = 0if line.startswith("CREATE TABLE IF NOT EXISTS"):indexCreate = len("CREATE TABLE IF NOT EXISTS")elif line.startswith("CREATE TABLE"):indexCreate = len("CREATE TABLE")indexEnd = line.index("(")return line[indexCreate:indexEnd].rstrip("(").strip()if __name__ == "__main__":result = []currTable = ''with open('mysql_ddl.sql', 'r') as file:line = file.readline()while line:# 替换`x`为"x"line = line.replace("`", "\"").replace("\n", "").replace("unsigned", "")# longtext替换line = line.replace("longtext", "text").replace("LONGTEXT", "TEXT")# mediumblob 替换line = line.replace("mediumblob", "blob").replace("MEDIUMBLOB", "BLOB")lTrimUpperLine = line.lstrip().upper()# 替换DEFAULT CURRENT_TIMESTAMP ON UPDATEif lTrimUpperLine.count("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") > 0:line = line.replace("ON UPDATE CURRENT_TIMESTAMP", "") \.replace("on UPDATE CURRENT_TIMESTAMP", "") \.replace("on update CURRENT_TIMESTAMP", "") \.replace("ON update CURRENT_TIMESTAMP", "")# 替换ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENTif lTrimUpperLine.count(") ENGINE=INNODB") > 0:line = ");"# 查询当前所属表if lTrimUpperLine.startswith("CREATE TABLE "):currTable = getTable(line)# 移除bigint、int、tinyint的显示占位符if line.count(" bigint(") > 0:indexBigint = line.find("bigint(")right = line[indexBigint + 6:]right = right[right.find(")") + 1:]line = line[:indexBigint + 6] + rightif line.count(" int(") > 0:indexBigint = line.find("int(")right = line[indexBigint + 3:]right = right[right.find(")") + 1:]line = line[:indexBigint + 3] + rightif line.count(" tinyint(") > 0:indexBigint = line.find("tinyint(")right = line[indexBigint + 7:]right = right[right.find(")") + 1:]line = line[:indexBigint + 7] + right# 唯一索引替换if lTrimUpperLine.startswith("UNIQUE "):column = line[line.index("("):line.index(")") + 1]line = f"UNIQUE {column},"# 记录索引if lTrimUpperLine.startswith("KEY "):replaceKey(line, currTable)# 检测到结尾，移除上一行最后一个逗号elif line.count(");") > 0:lastLine = result[-1:][0]if lastLine.rstrip().endswith(","):lastLine = lastLine.rstrip(",")result.pop()result.append(lastLine)result.append(line)elif (lTrimUpperLine.strip() is Noneor len(lTrimUpperLine.strip()) == 0or lTrimUpperLine.startswith("/*")or lTrimUpperLine.startswith("LOCK TABLES")or lTrimUpperLine.startswith("UNLOCK TABLES;")or lTrimUpperLine.startswith("--")or lTrimUpperLine.strip() is None):passelse:result.append(line)line = file.readline()for x in index_sql_list:result.append(x)with open("dameng_ddl.sql", "w") as f:for r in result:f.writelines(r + "\n")

如果想直接转化为大写，则不用加""：

index_sql_list = []def replaceKey(line, currTable):indexColumnStart = line.index("(")indexColumnEnd = line.index(")")if (line.lstrip().upper().startswith("KEY")):indexSql = f"CREATE INDEX {line.lstrip()[4: indexColumnStart - 2]} ON {currTable}({line[indexColumnStart + 1: indexColumnEnd]});"else:indexSql = f"CREATE UNIQUE INDEX {line.lstrip()[11: indexColumnStart - 2]} ON {currTable}({line[indexColumnStart + 1: indexColumnEnd]});"index_sql_list.append(indexSql)def getTable(line):indexCreate = 0if line.startswith("CREATE TABLE IF NOT EXISTS"):indexCreate = len("CREATE TABLE IF NOT EXISTS")elif line.startswith("CREATE TABLE"):indexCreate = len("CREATE TABLE")indexEnd = line.index("(")return line[indexCreate:indexEnd].rstrip("(").strip()if __name__ == "__main__":result = []currTable = ''with open('mysql_ddl.sql', 'r') as file:line = file.readline()while line:line = line.replace("\n", "")lTrimUpperLine = line.lstrip().upper()if lTrimUpperLine.startswith("COMMENT") or lTrimUpperLine.startswith("`COMMENT`"):# 替换`x`为"x"line = line.replace("`", "\"")else:# 移除``line = line.replace("`", "")# unsigned 替换line = line.replace("unsigned", "").replace("UNSIGNED", "")# longtext替换line = line.replace("longtext", "text").replace("LONGTEXT", "TEXT")# mediumblob 替换line = line.replace("mediumblob", "blob").replace("MEDIUMBLOB", "BLOB")# 替换DEFAULT CURRENT_TIMESTAMP ON UPDATEif lTrimUpperLine.count("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") > 0:line = line.replace("ON UPDATE CURRENT_TIMESTAMP", "") \.replace("on UPDATE CURRENT_TIMESTAMP", "") \.replace("on update CURRENT_TIMESTAMP", "") \.replace("ON update CURRENT_TIMESTAMP", "")# 替换ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENTif lTrimUpperLine.count(") ENGINE=INNODB") > 0:line = ");"# 查询当前所属表if lTrimUpperLine.startswith("CREATE TABLE "):currTable = getTable(line)# 移除bigint、int、tinyint的显示占位符if line.count(" bigint(") > 0:indexBigint = line.find("bigint(")right = line[indexBigint + 6:]right = right[right.find(")") + 1:]line = line[:indexBigint + 6] + rightif line.count(" int(") > 0:indexBigint = line.find("int(")right = line[indexBigint + 3:]right = right[right.find(")") + 1:]line = line[:indexBigint + 3] + rightif line.count(" tinyint(") > 0:indexBigint = line.find("tinyint(")right = line[indexBigint + 7:]right = right[right.find(")") + 1:]line = line[:indexBigint + 7] + right# 唯一索引替换if lTrimUpperLine.startswith("UNIQUE "):column = line[line.index("("):line.index(")") + 1]line = f"UNIQUE {column},"# 记录索引if lTrimUpperLine.startswith("KEY "):replaceKey(line, currTable)# 检测到结尾，移除上一行最后一个逗号elif line.count(");") > 0:lastLine = result[-1:][0]if lastLine.rstrip().endswith(","):lastLine = lastLine.rstrip(",")result.pop()result.append(lastLine)result.append(line)elif (lTrimUpperLine.strip() is Noneor len(lTrimUpperLine.strip()) == 0or lTrimUpperLine.startswith("/*")or lTrimUpperLine.startswith("LOCK TABLES")or lTrimUpperLine.startswith("UNLOCK TABLES;")or lTrimUpperLine.startswith("--")or lTrimUpperLine.strip() is None):passelse:result.append(line)line = file.readline()for x in index_sql_list:result.append(x)with open("dameng_ddl.sql", "w") as f:for r in result:f.writelines(r + "\n")