python mysql表结构转oracle

# -*- encoding: utf-8 -*-
"""
@File:        oracle_sql_generate.py
@Author:      Little duo
@Time:        2023/3/16 17:55
@Contact:     1049041957@qq.com
@License:     (C)Copyright 2021-2022, Little duo
"""

from work import powersi

mysql_to_oracle_map = {
    'int': 'NUMBER',
    'bigint': 'NUMBER',
    'float': 'NUMBER',
    'double': 'NUMBER',
    'decimal': 'NUMBER',
    'varchar': 'VARCHAR2',
    'longtext': 'VARCHAR2',
    'mediumtext': 'VARCHAR2',
    'char': 'CHAR',
    'text': 'VARCHAR2',
    'blob': 'BLOB',
    'datetime': 'DATE',
    'timestamp': 'DATE',
    'date': 'DATE'
}

table_info = powersi.query(sql="""
    SELECT 
        C.TABLE_SCHEMA,
        C.TABLE_NAME,
        T.TABLE_COMMENT, 
        C.COLUMN_NAME,
        C.COLUMN_COMMENT,
        C.ORDINAL_POSITION,
        C.COLUMN_DEFAULT,
        C.IS_NULLABLE,
        C.DATA_TYPE,
        C.CHARACTER_MAXIMUM_LENGTH,
        C.CHARACTER_OCTET_LENGTH,
        C.NUMERIC_PRECISION,
        C.NUMERIC_SCALE
    FROM information_schema.TABLES T
    JOIN information_schema.COLUMNS C 
        ON T.TABLE_NAME = C.TABLE_NAME AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
    WHERE T.TABLE_SCHEMA  NOT IN ('sys','performance_schema','information_schema')
    AND T.TABLE_SCHEMA  IN ('powersi')
    ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
""")

create_table_sql = ''
table_columns = {}
table_column_comment_infos = []
table_comment_infos = {}
for result in table_info:
    table_schema, table_name, table_comment, column_name, column_comment, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale = result
    table_comment_infos[table_name] = table_comment
    table_column_comment_infos.append([table_name, column_name, column_comment])

    if table_name not in table_columns:
        table_columns[table_name] = []

    column_sql = '\t{} {}'.format(column_name, mysql_to_oracle_map[data_type])

    if character_octet_length:
        character_octet_length = 4000 if character_octet_length >= 4000 else character_octet_length
        column_sql += '({})'.format(character_octet_length)
    elif numeric_precision:
        column_sql += '({},{})'.format(numeric_precision, numeric_scale)

    if is_nullable == 'NO':
        column_sql += ' NOT NULL'
    table_columns[table_name].append(column_sql)

    # if column_default is not None:
    #     column_sql += ' DEFAULT {}'.format(column_default)


for key, columns in table_columns.items():
    create_table_sql += 'CREATE TABLE {} (\n'.format(key)
    create_table_sql += ',\n'.join(columns)
    create_table_sql += '\n);\n\n'

    table_comment = table_comment_infos[key]
    if table_comment:
        create_table_sql += "COMMENT ON TABLE {} IS '{}';\n\n".format(key, table_comment)

    for table_name, column_name, column_comment in table_column_comment_infos:
        if table_name == key and column_comment:
            create_table_sql += "COMMENT ON COLUMN {}.{} IS '{}';\n".format(table_name, column_name, column_comment)


print(create_table_sql.upper())