python实现mysql转人大金仓sql转换

在使用人大金仓的数据迁移工具的时候发现有有些字段不能直接的同步，即使修改映射规则也还是出现异常

mysql数据库配置

import pymysql

conn = pymysql.connect(
    host="xxxx",
    user="xxxx",
    password="xxx",
    database="xxx",
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

获取mysql的所有表

# 创建游标
cursor = conn.cursor()

# 执行sql语句
cursor.execute("SHOW TABLES")

# 获取查询结果
tables = cursor.fetchall()

获取字段类型

# 字段转译 --- 人大金仓不支持 设置精度
def getType(type):
    if 'int' in str(type):
        type = 'int'
    if 'bigint' in type:
        type = 'bigint'
    if 'datetime' in type:
        type = 'datetime'
    if 'longblob' in type:
        type = 'BLOB'
    if 'year' in type:
        type = 'int'
    if 'longtext' in type:
        type = 'text'
    if 'varbinary' in type:
        type = 'BLOB'
    if 'double' in type:
        type = 'double'
    if 'mediumtext' in type:
        type = 'text'
    if 'unsigned' in type:
        type = type.replace("unsigned", '')
    return type

字段转译 — 关键字在人大金仓中不支持设为字段

def getField(field):
    if 'LEVEL' == field or 'level' == field:
        field = '_' + field
    if 'YEAR' == field or 'year' == field:
        field = '_' + field
    if 'group' == field or 'GROUP' == field:
        field = '_' + field
    return field

查询索引信息

def get_index_list(table_name):
    query = f'show index from {table_name}'
    cursor.execute(query)
    results = cursor.fetchall()
    return results

更具索引信息转sql执行脚本

def get_index(tale_name):
    query = f'show index from {tale_name}'
    cursor.execute(query)
    index_script = ""
    index_map = {}
    results = cursor.fetchall()
    for index in results:
        table_name = index['Table']
        key_name = index['Key_name']
        if key_name not in index_map:  # 聚合索引
            index_map[key_name] = []
        index_map[key_name].append(index)
    for key_name, indexes in index_map.items():
        if len(indexes) > 1:  # 聚合索引sql拼接
            if key_name != 'PRIMARY':  # 一般索引
                index_script += f"CREATE INDEX if not exists {key_name} ON {table_name} ("
                for idx, index in enumerate(indexes):
                    column_name = index['Column_name']
                    seq_in_index = index['Seq_in_index']
                    if idx > 0:
                        index_script += ", "
                    index_script += f"{column_name} " if seq_in_index == 1 else f"{column_name} "
                index_script += ");\n"
            else:  # 主键索引生成
                index_script += f"alter table tcjprojectuat.{table_name} add PRIMARY key ("
                for idx, index in enumerate(indexes):
                    column_name = index['Column_name']
                    seq_in_index = index['Seq_in_index']
                    if idx > 0:
                        index_script += ", "
                    index_script += f"{column_name} " if seq_in_index == 1 else f"{column_name} "
                index_script += ");\n"

        else:  # 单字段索引
            index = indexes[0]
            table_name = index['Table']
            column_name = index['Column_name']
            seq_in_index = index['Seq_in_index']
            if key_name == 'PRIMARY':
                index_script += f'alter table tcjprojectuat.{table_name} add PRIMARY key ({column_name}'
            else:
                index_script += f"CREATE INDEX if not exists {key_name} ON {table_name} ({column_name} " if seq_in_index == 1 else f"CREATE INDEX  if not exists {key_name} ON {table_name} ({column_name}"
            index_script += ");\n"
    print(index_script)
    return index_script

主函数

def run():
    with open('./kingbase.sql', 'w') as f:
        for table in tables:
            table_name = table['Tables_in_tcjprojectuat']
            query = f"SHOW FULL COLUMNS from {table_name}"
            cursor.execute(query)
            results = cursor.fetchall()
            fields = ''
            comments = ''
            for row in results:
                type = getType(row['Type'])
                field = getField(row['Field'], table_name)
                collation = row['Collation']
                if collation == None:
                    collation = ''
                else:
                    collation = f" COLLATE {collation}"
                null = ''
                if row['Null'] == 'YES':
                    null = 'NULL'
                else:
                    null = "NOT NULL"
                key = row['Key']
                default = row['Default']
                if default == None or default == '':
                    default = None
                else:
                    if 'varchar' in type:
                        default = f'default \'{default}\''
                    else:
                        default = f'default {default}'
                comment = row['Comment']
                comment = comment.replace("'", '')
                comment = f'comment on column {table_name}.{field} is \'{comment}\';\n'
                comments = comments + comment
                if default != None:
                    fields = fields + f'\t{field} {type}  {null} {default},\n'
                else:
                    fields = fields + f'\t{field} {type}  {null} ,\n'
            fields = fields.rstrip(',\n') + "\n"
            drop_table = f'drop table if  exists {table_name};'
            index_script = get_index(table_name)
            # print(index_script)
            create_table = f'create table {table_name} (\n{fields});\n{comments}\n{index_script}'
            # print(create_table)
            f.write(drop_table + '\n' + create_table + "\n")
            print(create_table)


if __name__ == '__main__':
    run()

cursor.close()
conn.close()