python批量修改mysql中某个字段的长度

突然被告知DB中某个关键字段长度要增大，涉及到N张表，改起来超麻烦，想着用代码改，比较少写这种增删表或者改变表结构的代码，记录下。

import pymysql


def modifyFieldVarcharLen(config, new_column_length):

    connection = pymysql.connect(**config)
    try:
        with connection.cursor() as cursor:

            # 获取所有表的名字
            cursor.execute("SHOW TABLES")
            tables = cursor.fetchall()
            table_fields = {}

            for table in tables:
                table_name = table[0]

                # 获取表的字段信息
                cursor.execute(f"DESCRIBE `{table_name}`")
                fields = cursor.fetchall()

                def_fields = ("field1", "field2", "field3")

                for field in fields:
                    field_l = field[0].lower()
                    if field_l in def_fields:
                        if table_name in table_fields:
                            table_fields[table_name].append(field[0])
                        else:
                            table_fields[table_name] = [field[0]]

            for table_name in table_fields:
                for column_name in table_fields[table_name]:
                    # 查询原字段的注释和默认值
                    cursor.execute(f"SHOW FULL COLUMNS FROM `{table_name}` WHERE Field = %s;", (column_name,))
                    column_info = cursor.fetchone()

                    collation = column_info[2]
                    is_null = column_info[3]
                    default_value = column_info[5]
                    comment = column_info[8]
                    # print(column_info)
                    # print(collation, is_null, comment, default_value, default_value is None)

                    if is_null == "YES" and default_value is None:
                        null_str = "NULL DEFAULT NULL"
                    elif is_null == "YES" and default_value is not None:
                        null_str = f"NULL DEFAULT '{default_value}'"
                    elif is_null == "NO" and default_value is not None:
                        null_str = f"NOT NULL DEFAULT '{default_value}'"
                    else:
                        null_str = "NOT NULL"

                    sql = f"ALTER TABLE `{table_name}` MODIFY COLUMN `{column_name}` VARCHAR({new_column_length}) {null_str} COMMENT '{comment}' COLLATE '{collation}';"
                    cursor.execute(sql)
                    print(sql)

                    connection.commit()
    finally:
        connection.close()


def batchModifyColumns():
    length = 50
    configs = [{"host": "localhost", "user": "root", "password": "", "database": "test2"}]
    table_fields = [
        {
            "table1": ["field1"],
            "table2": ["field1"],
            "table3": ["field1", "field2"],
            "table4": ["field2"],
            "table5": ["field2"],
        }
    ]

    length_c = len(configs)
    for i in range(length_c):
        modifyFieldVarcharLen(config=configs[i], new_column_length=length)


batchModifyColumns()

可以实现批量修改某些varchar字段的长度，保留原字段是否为null、默认值、注释、编码等信息