Python操作Oracle数据库：CLOB转VARCHAR2及避免数据导入时列自动转为CLOB的方法问询

Let's break this down into two clear parts: fixing your existing table, and stopping this auto-conversion from happening in future imports.

1. Convert Existing CLOB Columns to VARCHAR2 #

First, you can adjust your existing table to switch the CLOB columns to VARCHAR2. Just make sure the data in each CLOB column doesn’t exceed the length you specify (Oracle’s VARCHAR2 max is 4000 bytes by default; in 12c+, you can extend this to 32767 if your database is configured for it).

First, verify the maximum length of data in each CLOB column to avoid truncation errors:

SELECT 
    MAX(DBMS_LOB.GETLENGTH(updated_date)) AS max_updated_date_len,
    MAX(DBMS_LOB.GETLENGTH(id_1)) AS max_id1_len,
    MAX(DBMS_LOB.GETLENGTH(id_2)) AS max_id2_len,
    MAX(DBMS_LOB.GETLENGTH(type)) AS max_type_len,
    MAX(DBMS_LOB.GETLENGTH(lvl)) AS max_lvl_len,
    MAX(DBMS_LOB.GETLENGTH(sgmt)) AS max_sgmt_len
FROM customers_test;

If the lengths fit within your target VARCHAR2 limits, run this ALTER TABLE statement:

ALTER TABLE customers_test
MODIFY (
    updated_date VARCHAR2(100) NOT NULL,
    id_1 VARCHAR2(100) NOT NULL,
    id_2 VARCHAR2(100) NOT NULL,
    type VARCHAR2(10) NOT NULL,
    lvl VARCHAR2(10) NOT NULL,
    sgmt VARCHAR2(10)
);

2. Prevent Auto-Conversion to CLOB During Data Import #

The core issue here is that pandas’ default object dtype gets mapped to Oracle’s CLOB by SQLAlchemy—since object can hold variable-length strings of any size. Here are two reliable fixes:

Option 1: Specify Explicit Data Types in to_sql #

Use SQLAlchemy’s type classes to explicitly define each column’s Oracle type when calling to_sql:

First, import the necessary types:

from sqlalchemy import types

Then define a dtype mapping and pass it to to_sql:

dtype_mapping = {
    'updated_date': types.VARCHAR(length=100),
    'id_1': types.VARCHAR(length=100),
    'id_2': types.VARCHAR(length=100),
    'type': types.VARCHAR(length=10),
    'lvl': types.VARCHAR(length=10),
    'sgmt': types.VARCHAR(length=10),
    'score': types.FLOAT()
}

test.to_sql(
    'CUSTOMERS_TEST', 
    engine, 
    schema=schema, 
    if_exists='append', 
    index=False,
    dtype=dtype_mapping
)

This tells SQLAlchemy exactly which Oracle types to use, overriding the default mapping from pandas dtypes.

Option 2: Use Pandas’ String Dtype #

For pandas 1.0+, use pd.StringDtype() instead of str when reading your CSV. This is a dedicated string type (instead of the generic object dtype), which SQLAlchemy will map to VARCHAR2 instead of CLOB:

test = pd.read_csv(
    r"template.csv",
    dtype={
        'updated_date': pd.StringDtype(),
        'id_1': pd.StringDtype(),
        'id_2': pd.StringDtype(),
        'type': pd.StringDtype(),
        'lvl': pd.StringDtype(),
        'sgmt': pd.StringDtype(),
        'score': float
    }
)

Combine this with the dtype parameter in to_sql for extra safety, especially if you want to enforce specific column lengths.

Bonus: Let to_sql Create the Table Correctly #

If you don’t need to manually create the table first, you can let to_sql handle table creation with the correct types by setting if_exists='replace' (or 'fail' if you want to avoid overwriting) and using the dtype mapping. This skips the manual CREATE TABLE step entirely and ensures the table structure matches your desired types from the start.

内容的提问来源于stack exchange，提问作者Long_NgV