【Python】批量修改数据库表的字段类型脚本

下面是批量修改数据库表字段类型，如批量把clob改成nclob：

import cx_Oracle

# 设置数据库连接信息
username = 'db_username'
password = 'your_password'  # 请替换为实际的数据库密码
dsn = 'your_dsn'  # 请替换为实际的数据库DSN

# 创建数据库连接
connection = cx_Oracle.connect(username, password, dsn)

# 定义要修改的字段类型
oldType = 'CLOB'
newType = 'NCLOB'

# 查询包含CLOB字段的表信息
cursor = connection.cursor()
sql = f"""SELECT OWNER,
                 TABLE_NAME,
                 COLUMN_NAME,
                 DATA_TYPE,
                 DATA_LENGTH AS COLUMN_LENGTH
          FROM ALL_TAB_COLUMNS
          WHERE OWNER = UPPER('{username}') AND DATA_TYPE = '{oldType}'"""
cursor.execute(sql)
result = cursor.fetchall()

if not result:
    print(f"不存在包含类型【{oldType}】的表字段！")
else:
    for row in result:
        tablename = row[1]
        columnname = row[2]
        ret = updateColumn(connection, tablename, columnname, oldType, newType)
        
        if ret == 1:
            print(f"更新【{tablename}】->【{columnname}】成功->{ret}")
        else:
            print(f"更新【{tablename}】->【{columnname}】失败->{ret}")
        
        print('\r\n')

print("*******end********")

# 修改字段类型的函数
def updateColumn(connection, tablename, columnname, oldType, newType):
    cursor = connection.cursor()
    try:
        # 重命名旧字段
        rename_sql = f"ALTER TABLE {tablename} RENAME COLUMN {columnname} TO {columnname}_0"
        cursor.execute(rename_sql)
        
        # 添加新字段
        add_column_sql = f"ALTER TABLE {tablename} ADD {columnname} {newType}"
        cursor.execute(add_column_sql)
        
        # 更新原来的值
        update_sql = f"UPDATE {tablename} SET {columnname} = {columnname}_0"
        cursor.execute(update_sql)
        
        # 删除旧字段
        drop_sql = f"ALTER TABLE {tablename} DROP COLUMN {columnname}_0"
        cursor.execute(drop_sql)
        
        connection.commit()
        return 1
    except cx_Oracle.Error as e:
        print(f"更新【{tablename}】->【{columnname}】失败->{e}")
        connection.rollback()
        return 0
    finally:
        cursor.close()