mysql转达梦的python脚本

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='应用导入包';
1
2
3
4
5
6
7
8
9
10
11
12
13

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")
);
1
2
3
4
5
6
7
8
9
10
11
12
13

脚本：

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 = 0
    if 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 UPDATE
            if 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 COMMENT
            if 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] + right
            if line.count(" int(") > 0:
                indexBigint = line.find("int(")
                right = line[indexBigint + 3:]
                right = right[right.find(")") + 1:]
                line = line[:indexBigint + 3] + right
            if 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 None
                  or len(lTrimUpperLine.strip()) == 0
                  or lTrimUpperLine.startswith("/*")
                  or lTrimUpperLine.startswith("LOCK TABLES")
                  or lTrimUpperLine.startswith("UNLOCK TABLES;")
                  or lTrimUpperLine.startswith("--")
                  or lTrimUpperLine.strip() is None):
                pass
            else:
                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")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

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

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 = 0
    if 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 UPDATE
            if 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 COMMENT
            if 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] + right
            if line.count(" int(") > 0:
                indexBigint = line.find("int(")
                right = line[indexBigint + 3:]
                right = right[right.find(")") + 1:]
                line = line[:indexBigint + 3] + right
            if 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 None
                  or len(lTrimUpperLine.strip()) == 0
                  or lTrimUpperLine.startswith("/*")
                  or lTrimUpperLine.startswith("LOCK TABLES")
                  or lTrimUpperLine.startswith("UNLOCK TABLES;")
                  or lTrimUpperLine.startswith("--")
                  or lTrimUpperLine.strip() is None):
                pass
            else:
                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")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103